The Holidays are an exciting time of year, especially for web analytics. During the Black Friday weekend and beyond, critical decisions are made based on the real-time data that SiteCatalyst provides. These decisions can have a huge impact on the success or failure of the most profitable period for retailers. Most retail organizations have sophisticated contingency plans set up, so they can adjust their promotional calendar, email delivery schedule, and demand generation efforts based on these hour-by-hour reports.

But why do they care? Aren’t more orders better? Not necessarily. Taking too many online orders could be a problem for retailers. If a retailer goes too far above the forecast for orders, then that could cause serious issues with fulfillment, which could result in poor customer experiences and millions of dollars in expedited shipping costs if the warehouse is not prepared. Therefore, retailers work hard to smooth out the peaks while still maximizing sales over the holiday period. It’s also a matter of balancing discounts with that demand.

In this blog post, I will review some of the ways you can set up reports to monitor the health of the business to get this critical information in the hands of the decision-makers quickly and efficiently. These report examples can be used not only for the critical holiday period, but also throughout the year.

Before I get into the specific examples, I want to review one critical KPI for retailers: Comparable-store sales or “comp sales.” Most retailers care most about the performance of a given period compared to the previous year. This metric is usually reflected as percentage difference such as +15%. The actual dollar amounts don’t matter as much as the comp. When presenting data back to the execs we should focus on the comp.

It’s also important to note that retail sales are highly seasonal and predictable. Therefore, we can calculate a daily revenue comp, comparing today vs. same day last year, at the same hour.

Example 1: Hourly Automated Report

One valuable service you can provide your executives is quick and easy way to access the critical KPIs. One method is a customized hourly (or every few hours) text email. Executives are often away from their desks during the holiday and therefore can’t open a huge Excel file. They just need to know the comp for the day. Here is one method to provide that data:

  1. Create an Excel sheet with ReportBuilder that pulls  n the hourly revenue, visits, units and orders for “today” and the same day last year. Make formulas that calculate the running cumulative totals for each column.
  2. Using Excel formulas, detect the hour for the most recent data. For example, if there is no demand in the 2pm hour slot, then you know that the data is updated to at least 12pm.
  3. We can then compare the revenue, order and unit totals UP TO THE SAME HOUR last year. For example, last year may have had $100k in sales, but only $50k by noon. By comparing the same time frame last year, you are more likely comparing apples to apples. BONUS: You can also forecast the final revenue numbers for this year, based on last year’s trajectory.
  4. Now take the high level summary for the data—what is the revenue comp? Reference that comp figure on a new sheet that just contains the summary data.
  5. With ReportBuilder, you have the option of scheduling this report to be emailed every X hours. Do your execs want it 3 times a day? Every hour may be overkill.
  6. Lastly you can set ReportBuilder to export only the first sheet of the workbook as a TXT file, which makes it much easier to read on a smartphone and smaller too.

The hourly data blocks:

The final email to execs may look something like this:

 

Example 2: Customized Excel Dashboard

The first example showed how you can simplify and distill the KPIs and automate the distribution. This second example could be used in parallel if you need more data and graphs to analyze and trend the results. The customized Excel dashboard could build on the first example, but adding detailed charts to show not only the revenue trends and comps, but also the upcoming forecast, compared to last year.

In this example, instead of pulling a single day, we pulled a rolling 8 day window, where we can see the cumulative demand for the last 4 days, plus a look forward to the next 4 days. This is very helpful to show the expected hourly spikes in demand.

This graph is relatively easy to create by setting a dynamic data block for a rolling 8 day window for this year and last. Then we simply overlay the 2 time periods, which allows you to compare the hourly trends and also see 4 days out.

This type of graph could be easily replicated for revenue, page views, visits and even conversion. However, you may want to see the “running total”. To smooth out the peaks and valleys, you can also calculate a cumulative total for the time period and show those comps.

Cumulative (4 day running total) revenue graph with comp to last year:

This graph can also be set up to be automatically emailed every few hours via ReportBuilder. Using dynamic date ranges in ReportBuilder instead of fixed date ranges allows for automated delivery. Of course, this could also be manually updated if hourly just isn’t frequent enough!

Tips and Tricks

  1. Adobe Engineering Services also provides very sophisticated custom dashboarding solutions. They have the capability to replicate the above process and deliver a custom Adobe Air dashboard application that can run on a desktop or mobile device. They can also pull in data from other sources besides SiteCatalyst for forecasting and targets. Some of their customers use this dashboard to display real-time results on a big screen in the Executive offices. It’s very slick.
  2. To calculate hourly conversion, use “hourly unique visitors” instead of visits (since visits is not available at the hourly granularity).
  3. Using third-party automation software, you could potentially pull many dashboards like this into an internal intranet site, rather than using email to distribute them. Dashboards can be delivered to any FTP site instead of email. This could reduce the worry about clogging up an executive’s inbox with hourly dashboards.

Conclusion

These reports and dashboards provide a glimpse into the possibilities of report automation, which allows the business users to quickly move from analysis to action, and from action to optimization. These hourly holiday reports are a great example of getting the right data to the right people at the right time, one of the key goals of every analyst.  Happy Black Friday and Cyber Monday (and Happy Thanksgiving too!)

 

David Yoakum is a con­sul­tant in Adobe Con­sult­ing, focused on dig­i­tal strat­egy, ana­lyt­ics & opti­miza­tion for retail & travel clients. This is one of a series of posts covering tips from our retail experts in Adobe Consulting related to Holiday ecommerce analytics.

 

0 comments