How to Leverage Google Analytics API to Monitor Ecommerce Health

Revenue – arguably not the best KPI but it’s the metric that most business owners obsess about on a daily basis “How much money have we made yesterday?”. As a result, digital analysts obsess about it too. It’s a handy barometer that measures how different levers in the business interact with each other on any given day. As an analyst, that’s what I care about most — the underlying interaction between all the moving parts of which revenue is simply a byproduct. Understanding and optimising it.

Mapping out this lever system conceptually is straightforward to some extent (business-specific levers must also be factored in and that is never straightforward). Carrying out regular healthchecks of the entire system, however, is not.

Yo, analytics, tell me why my revenue dropped

If revenue goes up or down I want to know why without having to spend 2 hours or more digging for the answer. I want the answer as soon as possible because often that’s just the trigger for a more complex process:

  • Is the change significant?
  • Does it represent a problem or an opportunity?
  • Does the business care about it?
  • What would happen if the business did something about it? (the hypothesis)
  • What can we do about it? (the experiment)
  • What would the outcome be? (the measure)
  • What will we learn from it? (the lessons)

So, is there a way to spend less time “digging for the answer” when the lever system must be monitored regularly (daily or weekly)? There is, to some extent.

Google Analytics Intelligence Events – The good and the bad

I love Google’s Intelligence Alerts. They allow you to set thresholds for your metrics. If these go up or down beyond that threshold, then you get an email. I love it, but I have several issues with it:

No emails for automatic intelligence events

The automatic alerts are based on statistically significant variations in metrics. That’s good. What’s bad is that you don’t receive emails. Instead, you have to check the Intelligence Report. We’re busy people, that doesn’t happen regularly even when the intention is there.


Custom alerts are based on fixed thresholds

The custom alerts send you emails but they require that you enter a trigger level manually. Result? You may get alerts when the fluctuations are not that unusual, if your fixed threshold is too low. Or you may not get an alert when the fluctuation is unusual, if your fixed threshold is too high. You either get too many emails leading to “alert email blindness” or too few emails.


Comparing against ‘Same day last week’ not good enough

I would rather it showed differences compared to an “average monday” based on the last x weeks worth of data to counteract natural week-on-week fluctuations.


Intelligence Events report too busy

I personally find the reports overloaded with information. It’s just too easy to miss some fairly significant spikes or drops in all that noise. It also makes working out the key drivers for change difficult unless “major contributors” is also showing (which only happens sometimes).


No alerts for time units shorter than a day

Was % Add to Basket significanly lower this Monday morning than on an average Monday morning? This would be hugely valuable for monitoring potentially costly technical glitches.


Time consuming to set up and maintain

I know most analysts instinctively know the value of using the Intelligence Events but it takes time to set up and maintain an alert system.


That said, using Intelligence Reports in Google Analytics is hugely beneficial. Using it is better than not using it.

The magic of Google Analytics API for monitoring revenue and funnel health

I’ve been doing a lot of work recently with the Google Analytics API and Google Visualisation API. If you haven’t used the API before, do look into it. See this step by step tutorial on using Google Analytics API to automate reports with Google Spreadsheets.

Inspired by Google Analytics’s own Intelligence Events, Tim Leighton-Boyce’s excellent Instant Checkout Health-check post, I’ve been building a revenue and funnel healthcheck tool to use with my ecommerce clients. I’d like to share it with you and hear your thoughts. If there’s enough interest I may create a plugplay version for public release. Let me know if you’re interested by leaving your email at the end of this post.

Step 1. Work out the key metrics which drive revenue fluctuations

Essentially, Revenue (as a metric) is a byproduct of 3 other metrics:

  • Visits
  • Conversion Rate
  • Order Value

You often see the strongest correlation between Revenue and Visits (a drop in visits often leads to a similar drop in Orders) but fluctuations in Conversion Rate and Average Order Value may also play a significant part.

When there’s a significant change in revenue my first point of call is to check:

  • Which of these 3 metrics have changed
  • By how much
  • Does the change in these 3 metrics hold any clues about what may have happened

Step 2. Use simple statistics to work out whether changes were unusual

I take 6 weeks worth of data with the following dimensions and metrics (may take fewer if I have a trend emerging):

  • Date
  • Visits
  • Transactions
  • Revenue

I use PowerPivot to aggregate the data in pivot tables and use its wonderfully versatile DAX language to create these next custom measures for Visits, Conversion Rate and Average Order Value:

  • Daily averages (for specific day of the week, i.e. Monday)
  • Standard deviation
  • Absolute change between actual value and the daily average
  • Convert those change to z-scores

The maths behind it is quite straightforward and based on simple statistics. In essence, z scores tell you by how many standard deviations has your metric gone up or down compared to the average.

As a rule of thumb for web analytics data:

  • z-scores lower than –1.5 or greater than +1.5 show somewhat unusual changes
  • z-scores lower than –3 or greater than +3 show highly unusual changes

For more information on control limits and standard deviations, read this great post written by Stephane Hamel on The Math Behind Web Analytics: Control Limits, Histograms and Objectives

The benefit of converting the changes into z-scores is that you can do like-for-like comparison in changes for metrics with different measurements units (i.e. Revenue with Conversion Rate).

NOTE: The daily averages and standard deviations here are calculated by day type e.g. Average Revenue for a Monday. I would love to hear other people’s thoughts on the pros and cons of taking x number of weeks into calculation versus simply comparing against “same day last week”. Would also like to know how you deal with seasonality (i.e. Bank Holiday Mondays).

You then come up with this:
powerpivot-standard-deviations-ecommerce-monitor

Interesting but it’s a bit tricky to visualise the “story” so let’s transpose the data:

powerpivot-standard-deviations-ecommerce-monitor-transposed

How would you read this?

On Monday, 24th June you’ve had a drop in revenue of £5,934, a somewhat significant change compared to a regular Monday (-1.59 standard deviations from the mean). Why did revenue drop?

We can see a similar spike in Visits (+1.56 standard deviations) which was counterbalanced by an even more significant drop in Conversion Rate (-1.98 standard deviations). It looks like you’ve had an influx of non-converting visitors compared to a regular Monday. At this stage, though, we only have an inkling of what happened.

What happened to the conversion rate? Why the significant drop? Which point in the ecommerce funnel was most responsible? The basket? The checkout? The product page?

Step 3. Calculate unusual changes inside the ecommerce funnel

Let’s apply similar calculations to the metrics representing progression rates inside the ecommerce funnel. Hopefully this will pinpoint which steps were most responsible for that change. We get raw data for the following segments:

  • Site success – Visits which included Product pages
  • Product success – Visits which included Product pages AND proceeded to Basket
  • Basket success – Visits which included Basket AND proceeded to Checkout
  • Checkout success – Visits which included Checkout AND completed the Order

We then calculate the success rates for each step. Success rates measure how many people have reached a particular step and proceeded to the next step.

  • % Site Success – Product Visits/All Visits
  • % Product Success – Product Successes/Product Visits
  • % Basket Success – Basket Successes/Basket Visits
  • % Checkout Success – Checkout Successes/Checkout Visits

We then calculate the same measures as above for these segments: daily averages, standard deviations, z-scores etc. These serve as additional context around the drop in conversion rate:

purchase-cycle-fluctuations-ecommerce-monitor

It looks like this influx of visitors was indeed unqualified — they included fewer visits to product pages and the add to basket rate was also significantly lower than what you’d expect on an average Monday.
But what’s worrying is the drop in checkout success at the last step. Remember, this measures the percentage of people who start checkout and successfully complete it. These are people who demonstrated strong intent to purchase so a drop of nearly 2 standard deviations below what we’d expect on a regular Monday is worrying.

Step 4. Create a visual summary of the changes and their context with Google Visualisation API

While I love PowerPivot, I find that reading the story and finding clues in pivot tables is not the easiest thing in the world. Enter Google Analytics API, Google Apps Scripts and Google Visualisation API and we have this:
watch-my-funnel-demo

On the left — revenue with its three key driving metrics. On the right — funnel with progression rates at every step. This is a fully dynamic report, connected to live Google Analytics data via the API. It updates every day and thanks to the versatile Google Apps Scripts it also fires off an email alert when one or more of these metrics fluctuate beyond what’s expected. Your interpretation of what counts as significant may vary, I use +/- 1.5 standard deviations.

But is this actionable?

Is this the whole story? Of course not. It’s just an interconected system of flags to help me start piecing that story together. It also reveals some interesting dynamics between the characters of the story.

On that Monday, revenue dropped by 1.59 std deviations (therefore significant enough to trigger an alert email). Visits and Conversion Rate have also both fluctuated rather significantly but in opposite directions. I may not care that I’ve had an influx of non-converting visitors (unless it’s paid traffic) but when some of those finally commit to the purchase and abandon at the last step in greater numbers than expected, to me that warrants some further investigation.

Yo, analytics, I still don’t know why my revenue dropped

My next question is “why” any of this happened. What were the key drivers? I believe this is where the value of this sort of report lies — creating a “major contributors” section similar to Google’s very own but I dare say, easier to read.

This involves working out the averages, standard deviation and z-scores for important segments and likely culprits:

  • Customer segments – Existing Customers, Prospects
  • Traffic sources and campaigns
  • Events – errors, form validation, etc
  • Browsers (taking into account with device category and version)
  • Geographical locations

For example, I would calculate the average Site Success rate for Google organic non-branded on a regular Monday and see how Monday 24th June changed compared to that.

The algorithm then involves “looping” through each segment to work out the most important movers and shakers in both volume and significance of the change, often cross-referencing with other dimensions. I would also calculate whether there has been a change in the distribution of traffic by certain dimensions.

Getting this done using the API and communicating it clearly in my report is very much a work in progress. It turns out that while it’s relatively easy to create this logic in PowerPivot, performing these calculations in Google Spreadsheets requires some serious acrobatics given the volume of data involved. But thinking beyond that, I’m thinking scorecards would work well:

fluctuation-drivers-ecommerce-health-monitor

What I think is missing from the “Watch my Funnel!”

Quantifying significant changes into actual £ or number of orders lost or gained

I believe this would be hugely valuable in communicating urgency of action or investigation. But this is not straightforward to do because you have:

  • Individual contributions from individual metrics
  • Joint influences between these various metrics

Approximate losses or gains using Revenue per Visit

One simple way to approximate how much £££ you’ve lost or gained is based on Revenue per Visit. For example, we can easily work out how much a Basket Success visit is worth to us on average. If you’ve had a spike in % Basket Success, then you can calculate the excess in Basket successes due to this change only. You then multiply this number with how much a Basket Success visit is worth to you.

Create time of day or hourly alerts

Wouldn’t it be great if I could get an email if my Basket to Checkout rate for Internet Explorer users has suddenly dropped this Monday morning compared to a regular Monday morning? This kind of alert would be great for potentially costly technical glitches.

This level of granularity would also be useful for large ecommerce sites where conversion rate varies significantly during working hours compared to evenings.

Known changes, experiments and issues

With a multi-functional team it’s often difficult to keep track of every tactical change made or experiment run. I might take a look at this report and guess that there’s been an influx of unqualified visitors. What if this report also pulled in annotations from the marketing team? If the annotation confirms a new Twitter campaign with a limited number of voucher codes then I wouldn’t have to dig for the root cause. It would just be staring me in the face.

Too complicated? All someone would have to do is fire off an email when the change goes live and that change gets logged in Google Spreadsheet.

Your thoughts

What is your own workflow for regular funnel monitoring? Do you leverage Google Intelligence Alerts or other alert systems? Do you suffer from “alert email blindness”? What actions do you take based on day-to-day monitoring?

Would you find this sort of report useful? If there’s enough interest, then I may work out a public version (that doesn’t require any coding) to connect your own Google Analytics data to a Google Visualisation powered “Watch my funnel” report via Google Spreadsheets. Please register your interest below and let me know your thoughts in the comments.

Comments

  1. Alex says

    Just came across this excellent post! Did you ever create the “public version”? Has this evolved to incorporate Google Data Studio?

Leave a Reply

Your email address will not be published. Required fields are marked *