E-commerce Conversion Rates Historic Analysis with Google Data Studio

or

Why a Little Doubt Is Good for You (and Your Client)

We had a client come to us a little bit panicky, visibly stressed, and totally fixated: 

“Our one-page checkout is screwed! Our conversion rates are dropping no matter what we do. Please make it go away!”

It was true. Their total conversion rate had dropped from 1.8% to 0.6% in the last year. That’s a lot. One could even wonder how is it possible that they didn’t get any help sooner. But maybe that’s not so strange for a small company in an emerging industry, for which e-commerce is just a side business. 

So we rolled up our sleeves and started with the low-hanging fruit. We set up Hotjar, double checked tracking, did some testing, and did a general assessment. But we found nothing of value—no insights, nothing. 

An idea crept in. How sure are we that the client knows where the issue lies?

We knew conversion rates had fallen—that was obvious. What was not obvious was what role was being played by the one-page checkout step. How could we observe what was wrong with this client’s funnel? Also, the reason for the client’s late discovery of this alarming trend was that revenue was not falling at all—even more reason to do a double take on the situation.

What was needed was a chart where we could observe all of the e-commerce conversion values over time. Googling produced a couple of valuable resources. Most notable were Charles Farina’s blog and a piece by Michelle Kiss on analyticsdemistyfied.com. Just a little bit of tweaking and modification was enough to get a super important insight into this particular client, which had a huge influence on the client’s marketing team focus.

Conversion rates betweehn e-commerce checkout stages

Outline of the Solution

The steps in Data Studio:

    1) Adding fields to the Analytics data source
    2) Creating a Datatable for Each Shopping Stage
    3) Blending the Datatables
    4) Adding conversion rate calculations

All data in this blog is taken from Google Merchandise Store.

Step 1: Adding fields to the Analytics data source

I’ll assume you all know how to add a Google Analytics data source to a Data Studio report (if not, read this). 

After adding, go to Resource → Manage added data sources.

Select your source, and click edit. 

Press “ADD A FIELD” in the top right of the screen.

Name the field “Shopping Stage_formatted” and insert this formula:

CASE

  WHEN REGEXP_MATCH (Shopping Stage, “ALL_VISITS”) THEN “1) All Sessions”

  WHEN REGEXP_MATCH (Shopping Stage, “PRODUCT_VIEW”) THEN “2) Sessions with Product Views”

  WHEN REGEXP_MATCH (Shopping Stage, “ADD_TO_CART”) THEN “3) Sessions with Add to Cart”

  WHEN REGEXP_MATCH (Shopping Stage, “CHECKOUT”) THEN “4) Sessions with Check-Out”

  WHEN REGEXP_MATCH (Shopping Stage, “TRANSACTION”) THEN “5) Sessions with Transactions”

END

Snapshot of Google Data Studio formula for better view of e-commerce shopping stages.

After saving, return to the report. 

Step 2: Creating a datatable for each shopping stage

Insert a datatable and set it up this way:

Google Data Studio table preparation for e-commerce shopping stage funnel overview

Now copy this table four times. We will now apply filters to each of these five tables so that each table shows session counts for its respective stage.

The first filter could be something like this:

View of Google Data Studio filter for e-commerce shopping funnel table comparison

The others will follow. I highly recommend using the Resource → Manage filters menu for this work. With this tool, you can easily duplicate and modify the filters. Of course, you can also do all the filters by hand.

I also recommend renaming the “Sessions” metric name in each of the tables to the value of its filter to make it more descriptive of its content. This makes further data manipulation easier and less error prone.

At the end of this step, you should have five tables, each filtered to a lower stage in the funnel, which usually means lower numbers. You’ll also get neatly renamed “Sessions” columns:

View of multiple Google Data Studio tables with renamed columns

Step 3: Blending the datatables

This is easy. Select all the tables, one by one, starting with the table containing the “All Sessions” column, and continuing with the tables down the checkout funnel. Then right click and press “Blend data”:

Blending multiple GOogle Data Studio tables together

A table will be created. You’ll have to set up a date range and sort again to get something like this:

Blended table in Google Data Studio after being sorted again

Step 4: Adding conversion rate calculations

The last step!

Here we will create calculated fields between pairs of columns.

Example: To get CR of All Sessions → Product views, create a calculated field with this setting:

Adding a column - calculated metric - to a bkended Google Data Studio table

Do this for all of the checkout funnel stage pairs. You can now observe the trends to see which conversion rates are increasing and which are decreasing.

In our example of the Google Merchandise store, we can see that the conversion rate between sessions with product views and sessions with Add to Cart is decreasing. On the other hand, the conversion rate between sessions with transactions and sessions with checkout is increasing.

These kinds of insights could be very valuable!

The resulting Google Data Studio table after blending and adding calculated metrics

The Aftermath: How This Helped Our Client

Remember when our client was panicking about the one-page checkout? Well, take a look at the funnel by month table for their case:

A table with visible fall in conversion rates between two stages of e-commerce funnel

It is obvious that the issue is not in conversion between the check-out page and the actual transaction. That number stayed relatively stable at around 32%. The issue was in the falling conversion of visitors making product views. Well, it turns out that in this particular client’s industry, a huge trend was happening. Look at this Google Trends five-year chart for one of the related keywords:

A Google Trends chart with an obvious trend in search for a certain kexword

This increase in search made the client’s blog rank very high. There was lots of organic traffic. Unfortunately, the blog never had any CRO done on it. CTAs were not optimal, and just a small portion of organic visitors ended up on the product pages, which, of course, caused a drop in total CR.

So the conclusion: 

The client thought they had a problem with one-page checkout.

We analysed trends in conversion rates between shopping stages.

A drop in conversion to sessions with product vies was observed.

We then found a substantial increase in organic traffic to their blog.

And the blog was not well optimized to lead visitors to products.

The aftermath: CRO was done on the blog + the blog got its deserved spot as one of the channels with the most potential.

Room for Improvement

This is just a simple e-commerce funnel, not enhanced. It does not distinguish between sessions that came from Add to Cart to checkout and sessions that started directly with the checkout.

To build the enhanced version, I absolutely encourage a thorough reading of Charles’ Farinas blog, where an abundance of hints for the task can be found.

Hi there!

Let’s start a project, schedule a call, or just say hello.

stay informed!

Subscribe to receive exclusive content and notifications

Success!

Thank you for connecting with us. We will get back to you soon!