Cardinal Path’s response to COVID-19 Cardinal Path is sharing all we know to help marketers during COVID-19.  Learn more.

Editors note: Back in March, Stéphan Hamel started a series on his blog about the math behind web analytics. Then in April, he joined us at Cardinal Path and began posting on our blog. Over the next few months we plan to release several posts on the math behind web analytics. We continue today with the second post from his blog.

In the second installment of this series, we will leverage Excel to take over where Google Analytics left us.

1. The math behind web analytics: the basics

Basic charting in Excel



Figure a: simple Excel chart

There are already some striking things: peaks & valleys corresponding to weekdays and weekends, and a week apparently performing better than others. Now we can easily apply some basic statistics on our time series.



Figure b: time series (visits)

Mean

The mean [wikipedia] is often referred to as the “average”, which, in reality, is the “arithmetic mean”. This is very simple math: add all the numbers and divide by the number of data points.

Look at Figure c – what can you tell about the red line crossing the whole graph? In a time series like daily visits for a month, honestly… we can’t tell much! Yet, only averages are reported by most web analytics tools – so please, don’t even bother saying “the average number of visits this month was X”!



Figure c: showing mean, trend, min & max and control limits.

Learning point: The average is rarely a good indicator in a time series such as those found in web analytics because it is influenced by extreme values (known as outliers [wikipedia]). At best, in the case above, one might want to calculate the mean for weekdays and the mean for weekends. As a rule of thumb, if you have less than 30 data points, use the median.


Figure d: descriptive statistics

Median and mode

The median [wikipedia] is the middle value. The mode [wikipedia], on the other end, is the value appearing the most frequently. Again, in a time series, where the spread of values (the standard deviation explained below) is large, those descriptive statistics [wikipedia] (Figure d) are usually of little interest.

Min & max

The min and max values are… well.. the maximum and minimum values in a time series. Those could be qualified as “anecdotes” – we could be thrilled we’ve got so much traffic on a single day, or deceived by a poorly performing day, but knowing that has absolutely no value if we can’t explain why.

In the time series used in this example, the min value is 93 visits on Saturday, January 1st. What can we tell about that? Obviously, people were busy doing something else than visiting my blog. What happened during the 4th week, around January 25? I shared my views about our little web analytics community and recounted my contributions. In both cases, we have very plausible explanations and the min & max values were useful only because they made us ask “why?”.

Trend

To me, the linear trend [wikipedia] (shown as a dotted line in Figure b) is one of the interesting modeling stats because it marks the begining of our regression analysis [wikipedia] capabilities – our ability to explain the why’s and “this, therefore that”. Basically, it can help us do some predictive analytics (albeit very simple). Remember y = mx + b? That is, the position of a point on the y axis (the visits) depends on a factor of x (the day) plus a starting baseline. I can tell, based on historical data, that I should get approximately 350 visits next Tuesday.

Standard deviation

If we do max – min we get the range [wikipedia], another descriptive statistic. Interesting at best. What’s much more interesting is the standard deviation [wikipedia] – the variability of the data. As we’ve seen, the average isn’t of much use because it is largely influenced by outliers. Standard deviation gives an appreciation of the spread of values around the mean, or if you prefer, the variation in a distribution of values.

Why is this important?


Figure e: control limits at +/- 1.5 sigma

First because standard deviation will be used to set control limits [wikipedia] (Figure e) – which in turn will be useful to define our tolerance and targets (covered in a later post). While control limits are typically set to +/- 3 times the standard deviation from the mean – I have found +/- 1.5 times (for a total of 3) to provide a better and easier indicator of values going below or above our historical track record (shown as the grayed area in Figure b). Basically, it gives us an easy way to set alerts when our metric might be going out of whack!

Secondly, a large variation is an indication of an unstable process (think conversion rate), or low reproductibitiliy (anecdotal campaign success), or if you prefer, a larger standard deviation reduces our ability to predict the value of Y given a certain X. Basically, as analysts, we want to explain the past, but we also want to provide insight on how to fix issues and seize opportunities – we eventually want to be able to predict outcomes of our recommendations.

Coming up: normal distribution, histogram and box-plots

In the next installment we’ll look at what is an histogram as well as normal distribution and their impact on our analysis. Also, although nifty spinning 3D-shadowed-shiny-Flash graphs are impressive… we’ll look at box plots elegant simplicity yet powerful and under-used visualization tool.

What do you think of this series so far? What would you like to see discussed or any examples you would like to see?

Sharing is caring!

Popular

COVID-19 Crisis Navigator​

In partnership with Dentsu, Cardinal Path helps you distill the overwhelming news and information into a bi-weekly report highlighting emerging trends and insights during the pandemic.

EXPLORE THE REPORT

Analytics Rising Logo

FREE VIRTUAL EVENT

October 14

Practical Strategies Delivered By Leaders of Digital Marketing

Register for #AnalyticsRising and get a Starbuck’s Gift Card when you share the news.

Message Sent

Thank you.

Click here to download access the tool.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you.

Message Sent

Thank you.

Message Sent

Thank you

Message Sent

Thank you

Message Sent

Thank you.

Message Sent

Thank you

Message Sent

Thank you.

Message Sent

Success!
Your message was received.

Thank you.

Message Sent

Thank you for registering.

Thank you for submitting the form.

Thank you for your submission.

Thank you for your submission.

Thank you for your submission.

Message Sent

Thank you for registering.

Thank you for your submission.

Message Sent

Thank you for registering.

Thank you for registering.​

Paid media spend by Government websites increased a whopping 139% YoY in 2020.

2020 Online Behavior Live Dashboard

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

2020 Online Behavior Live Dashboard

Message Sent

Thank you for registering.

Thank you for submitting the form.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you for your submission.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Thank you for registering.

Cardinal Path is continuing with its series of free training. Next we are conducting training on Google Data Studio. Check it out here.
Cardinal Path hosted a live session to connect with you and answer all your questions on Google Analytics.
Get all the expertise and none of the consultancy fees in this not-to-be-missed, rapid-fire virtual event.

Message Sent

Success! Thank you
for reaching out.