The Hol­i­days are an excit­ing time of year, espe­cially for web ana­lyt­ics. Dur­ing the Black Fri­day week­end and beyond, crit­i­cal deci­sions are made based on the real-time data that Site­Cat­a­lyst pro­vides. These deci­sions can have a huge impact on the suc­cess or fail­ure of the most prof­itable period for retail­ers. Most retail orga­ni­za­tions have sophis­ti­cated con­tin­gency plans set up, so they can adjust their pro­mo­tional cal­en­dar, email deliv­ery sched­ule, and demand gen­er­a­tion efforts based on these hour-by-hour reports.

But why do they care? Aren’t more orders bet­ter? Not nec­es­sar­ily. Tak­ing too many online orders could be a prob­lem for retail­ers. If a retailer goes too far above the fore­cast for orders, then that could cause seri­ous issues with ful­fill­ment, which could result in poor cus­tomer expe­ri­ences and mil­lions of dol­lars in expe­dited ship­ping costs if the ware­house is not pre­pared. There­fore, retail­ers work hard to smooth out the peaks while still max­i­miz­ing sales over the hol­i­day period. It’s also a mat­ter of bal­anc­ing dis­counts with that demand.

In this blog post, I will review some of the ways you can set up reports to mon­i­tor the health of the busi­ness to get this crit­i­cal infor­ma­tion in the hands of the decision-makers quickly and effi­ciently. These report exam­ples can be used not only for the crit­i­cal hol­i­day period, but also through­out the year.

Before I get into the spe­cific exam­ples, I want to review one crit­i­cal KPI for retail­ers: Comparable-store sales or “comp sales.” Most retail­ers care most about the per­for­mance of a given period com­pared to the pre­vi­ous year. This met­ric is usu­ally reflected as per­cent­age dif­fer­ence such as +15%. The actual dol­lar amounts don’t mat­ter as much as the comp. When pre­sent­ing data back to the execs we should focus on the comp.

It’s also impor­tant to note that retail sales are highly sea­sonal and pre­dictable. There­fore, we can cal­cu­late a daily rev­enue comp, com­par­ing today vs. same day last year, at the same hour.

Exam­ple 1: Hourly Auto­mated Report

One valu­able ser­vice you can pro­vide your exec­u­tives is quick and easy way to access the crit­i­cal KPIs. One method is a cus­tomized hourly (or every few hours) text email. Exec­u­tives are often away from their desks dur­ing the hol­i­day and there­fore can’t open a huge Excel file. They just need to know the comp for the day. Here is one method to pro­vide that data:

  1. Cre­ate an Excel sheet with Report­Builder that pulls  n the hourly rev­enue, vis­its, units and orders for “today” and the same day last year. Make for­mu­las that cal­cu­late the run­ning cumu­la­tive totals for each column.
  2. Using Excel for­mu­las, detect the hour for the most recent data. For exam­ple, 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 com­pare the rev­enue, order and unit totals UP TO THE SAME HOUR last year. For exam­ple, last year may have had $100k in sales, but only $50k by noon. By com­par­ing the same time frame last year, you are more likely com­par­ing apples to apples. BONUS: You can also fore­cast the final rev­enue num­bers for this year, based on last year’s trajectory.
  4. Now take the high level sum­mary for the data—what is the rev­enue comp? Ref­er­ence that comp fig­ure on a new sheet that just con­tains the sum­mary data.
  5. With Report­Builder, you have the option of sched­ul­ing 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 Report­Builder to export only the first sheet of the work­book as a TXT file, which makes it much eas­ier to read on a smart­phone and smaller too.

The hourly data blocks:

The final email to execs may look some­thing like this:

 

Exam­ple 2: Cus­tomized Excel Dashboard

The first exam­ple showed how you can sim­plify and dis­till the KPIs and auto­mate the dis­tri­b­u­tion. This sec­ond exam­ple could be used in par­al­lel if you need more data and graphs to ana­lyze and trend the results. The cus­tomized Excel dash­board could build on the first exam­ple, but adding detailed charts to show not only the rev­enue trends and comps, but also the upcom­ing fore­cast, com­pared to last year.

In this exam­ple, instead of pulling a sin­gle day, we pulled a rolling 8 day win­dow, where we can see the cumu­la­tive demand for the last 4 days, plus a look for­ward to the next 4 days. This is very help­ful to show the expected hourly spikes in demand.

This graph is rel­a­tively easy to cre­ate by set­ting a dynamic data block for a rolling 8 day win­dow for this year and last. Then we sim­ply over­lay the 2 time peri­ods, which allows you to com­pare the hourly trends and also see 4 days out.

This type of graph could be eas­ily repli­cated for rev­enue, page views, vis­its and even con­ver­sion. How­ever, you may want to see the “run­ning total”. To smooth out the peaks and val­leys, you can also cal­cu­late a cumu­la­tive total for the time period and show those comps.

Cumu­la­tive (4 day run­ning total) rev­enue graph with comp to last year:

This graph can also be set up to be auto­mat­i­cally emailed every few hours via Report­Builder. Using dynamic date ranges in Report­Builder instead of fixed date ranges allows for auto­mated deliv­ery. Of course, this could also be man­u­ally updated if hourly just isn’t fre­quent enough!

Tips and Tricks

  1. Adobe Engi­neer­ing Ser­vices also pro­vides very sophis­ti­cated cus­tom dash­board­ing solu­tions. They have the capa­bil­ity to repli­cate the above process and deliver a cus­tom Adobe Air dash­board appli­ca­tion that can run on a desk­top or mobile device. They can also pull in data from other sources besides Site­Cat­a­lyst for fore­cast­ing and tar­gets. Some of their cus­tomers use this dash­board to dis­play real-time results on a big screen in the Exec­u­tive offices. It’s very slick.
  2. To cal­cu­late hourly con­ver­sion, use “hourly unique vis­i­tors” instead of vis­its (since vis­its is not avail­able at the hourly granularity).
  3. Using third-party automa­tion soft­ware, you could poten­tially pull many dash­boards like this into an inter­nal intranet site, rather than using email to dis­trib­ute them. Dash­boards can be deliv­ered to any FTP site instead of email. This could reduce the worry about clog­ging up an executive’s inbox with hourly dashboards.

Con­clu­sion

These reports and dash­boards pro­vide a glimpse into the pos­si­bil­i­ties of report automa­tion, which allows the busi­ness users to quickly move from analy­sis to action, and from action to opti­miza­tion. These hourly hol­i­day reports are a great exam­ple of get­ting the right data to the right peo­ple at the right time, one of the key goals of every ana­lyst.  Happy Black Fri­day and Cyber Mon­day (and Happy Thanks­giv­ing 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 cov­er­ing tips from our retail experts in Adobe Con­sult­ing related to Hol­i­day ecom­merce analytics.

 

0 comments