Adding Historical and Statistical Context to Your Trended Reports

Traffic and conversion numbers go up and down every day. When looking at trended data, it can be difficult to know when an increase or decrease is truly significant. Sometimes our stakeholders can unnecessarily panic about a dip, or overly congratulate themselves about a spike. 

This post shows how to add historical and statistical context to trended data using a simple standard deviation calculation in Excel. There are also tips for how to visualize this data to make the statistical concepts very simple for the report recipients to read and understand.

Why this is helpful… 

  • Provides context for trended data
  • Accounts for seasonality
  • Removes the guesswork from deciding whether an increase or decrease requires action
  • Especially useful in post-launch scorecards to help stakeholders decide whether or not to roll back changes

How to do it… 

For a given metric, pull historical data as far back as possible. For example, if you’re analyzing weekly homepage visits, pull historical weekly data back at least 53 weeks, but ideally 2-5 years if you have the data to support it. The more historical data you have, the better.

In Excel, use the STDEV function to find the standard deviation across all the historical values for that metric. This calculation will yield a number that gives the normal range of variance for that set of values:
stdev

 

Using the standard deviation in conjunction with the data points from the prior year, it is possible to create a series of contextual bands on your chart. Then when the current year’s data is overlaid on top of those bands, it makes it very clear whether or not this year’s performance is within the normal range of variance:
stdev-graph

  • 1 standard deviation above or below = acceptable
  • 2 standard deviations above or below = outperforming/underperforming
  • 3 standard deviations above or below = requires attention

To make the banded chart, you will need a summary table that looks something like this:Summary Data Table

  • Prior Year – Data point from the previous year for the same week.
  • This Year – Data point from this year which is the central point of this report.
  • Attention Required – A formula subtracting two standard deviations below the prior year data point for that week. This will be the bottom threshold for the banded chart.
  • Underperforming / Outperforming / Great – The standard deviation value. We will use this value to make a stacked area chart.
  • Acceptable – Also used for the stacked area chart, but because the acceptable band has one standard deviation above and another standard deviation below the prior year value this value needs to be the standard deviation value multiplied by 2. 

When configuring the chart in Excel,  the prior year and current year should be line chart types. In order to create the bands, configure all the standard deviation-related data points as stacked area charts. All the data series should remain on the same primary axis.
excel-series-chart-types

 

What to do with the information…  

If a key performance indicator dips into the “Attention Required” zone, that means performance has been very negatively affected and it should be investigated and addressed immediately. In the case of a site or page overhaul or campaign launch, the team should consider rolling back.