Break data silos with Google Data Studio and Supermetrics

Imagine the following scenario: a customer purchases something in your brick and mortar store. While paying, the customer enters a competition in exchange of his email address. Weeks later, this customer clicks on a link in the marketing email you have sent to all participants you collected the emails from. However, no conversion is made but later that week a social media post related to the same product earns you another click. Yet it takes a third interaction, via a re-targeted paid ad, for the purchase to be made.

Based on this customer journey, which channel should be rewarded in this case? How can you analyse the conversion patterns from the initial engagement (marketing email) to the transaction in your e-shop?

In this article, we’ll show you how to build a dashboard with Google Data Studio and Supermetrics to break data silos by pulling together email engagement data and e-commerce revenue that is directly generated or assisted by your email campaigns.

Let’s get back to the digital journey of your customer. Here is how it could look like in your web analytics:

interactions before purchasing in omnichannel

By only giving credits to the last interaction (paid ad) you would not value the fact that the customer has heard of your great product from your email. It’s the email channel that brought him online for the first time since he filled-in his contact details at your store and your “offline to online contest” campaign should be granted some credits for this!

We are showing you the principle, but coming up with a suitable attribution model (how much credit has to be assigned to each channel that contributed to the purchase) is going to be your task though.

Let’s see how this concrete scenario can be visualized in Google Data Studio by bringing together engagement data from Mailchimp (a quite popular newsletter tool) and Multichannel Funnel (MCF) data from Google Analytics with the help of Google Sheets and Supermetrics.

The insights you will be able to get

Here is a goodie for you to keep reading: once implemented, here are the amazing insights you could get from this simple dashboard.

Break data silos: Google Data Studio Dashboard to merge Mailchimp and Google Analytics Ecommerce Data

What the data says

  • On Friday, August 12th you sent out the “online to offline” campaign to all your contest participants
  • The campaign has
    • assisted a revenue of 58.4k and directly generated 19.5k
    • a low open rate (7%) compared to the average (9.82%)
    • a quite a good click rate (4.15%) compared to the average (3.96%)
  • Emails sent on Thursdays and Fridays were better at assisting conversions
  • The ones sent on Saturdays were better at generating direct conversions

What your smart analyst should report to your CMO

Your offline to online campaign happened to be 3 times more successful in assisting revenue (58.4k vs. 19.5k) but the low open rate suggests a lot of missed opportunities most likely due to a not very catchy email subject. However, the content of the email was probably good since the click rate was above average. Sending the email on a Saturday might have changed the conversion patterns and could be worth an A/B testing experiment.

Did we wake up your interest? Let’s get our hands dirty!

If you are a business user and don’t care too much about the technical details you can skip the instructions and sign up for our monthly newsletter to stay up to date!

Subscribe to our monthly newsletter

Setting up the Google Data Studio dashboard

Before you start, make sure you are correctly generating your data. The mapping between Mailchimp and Google Analytics Multichannel Funnel Data must be done over the campaign name.

Pre-Conditions

  • You have Mailchimp and you have a Google Analytics account (read access is enough).
  • You have connected Mailchimp to Google Analytics as explained here.

1. Tagging your Campaigns in Mailchimp

When you let Mailchimp generate your utm parameters, you only have to set a campaign name.

setting the google analytics campaign name in mailchimp

If your campaign was named “OnlineToOfflineContest”, the following would get appended to any url in the email

  • utm_source=YourListName
  • utm_campaign=a5ab8a0d32-OnlineToOfflineContest
  • utm_medium=email
  • utm_content=a-very-long-id…

Where a5ab8a0d32 is the “internal” campaign id that Mailchimp generates for every email and that will allow us to map Mailchimp and Google Analytics data.

2. Collecting the Data with Supermetrics & Google Sheets

Unfortunately, Google Data Studio does not (yet?) allow pulling different data sources together in a common data model to plot their data in the same chart. The workaround to this is therefore to get the data in a Google Spreadsheet and connect to it with Data Studio.

Thanks to the Supermetrics Google Sheet add-on, you’ll be able to easily import Mailchimp campaign data and Google Analytics MCF data and map them together based on the campaign id set by Mailchimp.

What you need to do is to import our template, connect it to your accounts and adapt the Mailchimp query and the GA query filter to match your Mailchimp list names.

Here is how to do it

  • Create a new Google Spreadsheet
  • Enable the Supermetrics add-on: Add-ons > Get add-ons > search for “Supermetrics” > Click on “+”
  • Import our template: Add-ons > Supermetrics > Template gallery > Custom template
  • Paste the following URL in the form
    https://docs.google.com/spreadsheets/d/1m8iTjZ5DgwQCYFdIYLcotTcoLhUEVFDzWZLcvo8oEKA/
  • Connect the sheet to your Google Analytics and Mailchimp accounts by following the Supermetrics wizard. You will have to select the GA view and the Mailchimp lists you want to connect to. For Mailchimp just remember to select the lists ending by “All campaigns”.
  • The queries will automatically refresh at the end of the wizard.
  • You still need to replace the Google Analytics query filter to match your Mailchimp traffic. The filter should select only the traffic coming from the emails generated with Mailchimp, which means that you need to set Source = YourListName. In our template the check is made Channel grouping = Email, just to make sure you’ll get some data, but it will for sure generate errors in the lookup column until you don’t update it to your needs. Do the following: remove current filter and add yours, as showed in the picture.

    SuperMetrics how to update the GA query filter
  • Now update the query by clicking on “Apply changes”
  • The 2 sheets have yellow and white cells. The yellow cells contain formulas to support up to 500 campaigns. If you need more, just drag and drop the lines to extend the range.
  • Under Add-ons > Supermetrics > Schedule refresh & emailing you can set the report to refresh automatically hourly, daily, weekly or monthly.

3. Connecting Google Data Studio to the data

You now need to copy our Google Data Studio template and connect it to the Google Spreadsheet you just created and your Google Analytics account to display the chart in the top right area of the report.

Start by copying our Google Data Studio template.

Get our Mailchimp Email Campaign Conversion Value template for Google Data Studio

Clone the Google Data Studio Report by clicking on “Make a copy of this report”

You will be prompted to map the current data sources to the ones you have access to. Replace the first data source by your newly created Google Spreadsheet.

Select the first data source and click on “Create New Data Source”

Select the first data source

Select the “MailchimpData” Worksheet from your Spreadsheet

Select the "MailchimpData" Worksheet

Now click “Connect” and the following fields should appear. Most important, the “Campaign sent” should have been recognized as a date. If not, check that the spreadsheet date column is formatted as date. On the Data Studio support page, you can read more about date and time handling in Google Data Studio.

fields list in Google Data Studio

All fields coming from the spreadsheet will be recognized, but calculated fields need to be recreated. You therefore need to manually recreate the following calculated fields by using the following field names, formulas and formats.

  • Field Name: Weekday
    Formula: WEEKDAY(Campaign sent)
    Format: leave as-is
  • Field Name: Click Rate
    Formula: Unique subscriber clicks / Emails delivered
    Format: Percent
  • Field Name: Open Rate
    Formula: Opens / Emails delivered
    Format: Percent
  • Field Name: Assisted CR
    Formula: Assisted conversions / Emails delivered
    Format: Percent
  • Field Name: Last Int. CR
    Formula: Last interaction conversions / Emails delivered
    Format: Percent

Click on “Add to Report” once you have created all the fields.

Fields list in Google Data Studio

Now map the second data source to your Google Analytics view (the same you used to in the Google Spreadsheet). You won’t need to create any custom fields.

mapping data source to Google Analytics view in Google Data Studio

Finally click on “Create Report”.

4. Fixing the charts

Don’t get scared, your report will have several broken charts, but only because you need to re-associate the calculated fields to them.

Fix each numbered chart by clicking on it and by setting the right fields as listed below this picture.

Google data studio chart before recreating calculated fields

  1. Dimension: Weekday
  2. Metric: Open Rate
  3. Metric: Click Rate
  4. Dimension: Weekday
    Metrics: Assisted CR, Last Int. CR
  5. Dimensions: Campaign sent, Campaign name, Weekday
    Metrics: Open Rate, Click Rate, Assisted Conversion Value, Assisted CR, Last interaction conversion value, Last Int. CR

Your report is almost ready. The last thing you need to do is to change the Weekday field format in order to display the week days names instead of their number.

Click on the Email Conversion Rate by Sent Day of the Week chart and then on the pencil sign to edit its Data Source.

change the Weekday field format in Google Data Studio

Locate the Weekday field you created and change its format to Date & Time > Day of Week (D)

Confirm you want to change the format to Date and you are all set!

What is in the dashboard?

Data Groups

The Revenue Breakdown by Channel over Time chart (top right of the report) comes straight from your Google Analytics data. All other charts come from the Spreadsheet and “work together” if you apply common filters to slice the data.

Report Filters

Date Range

Due to the previously mentioned limitation of Google Data Studio, not allowing to build a common data model, the only common filter between charts coming from different data sources is the Date (top right of the report).

Day of the Week

Slicing the data by campaign sent day of the week is something that applies only to the charts coming from the spreadsheet. The Revenue Breakdown by Channel over Time chart won’t therefore be impacted if you select a value.

Revenue Breakdown by Channel over Time

The Revenue Breakdown by Channel over Time chart (top right of the report) comes straight from your Google Analytics data and has the sole purpose of “giving context” to your email engagement and generated revenue data. It allows you to see how the other channels were performing over time, but the metric that is plotted is the revenue, which is not calculated in the same way of assisted- and last interaction- conversion value. In fact, MCF and non-MFC metrics treat direct traffic differently, hence the difference between the values. Explaining the difference would require another article, so if you are not aware of it we suggest you taking a look at this old but good post explaining MCF metrics.

mailchimp and revenue data in google data studio: REVENUE BREAKDOWN BY CHANNEL OVER TIME

Engagement to Conversion Funnel

This chart will allow you to visualize how many emails were sent, what percentage of the delivered ones was opened / clicked and how much revenue was directly generated and assisted. These numbers adapt when selecting a different day of the week or a date range.

mailchimp and revenue data in google data studio: Engagement to Conversion Funnel

Email Conversion Rate by Sent Day of the Week

This chart shows you which “email send days” tend to generate more assisted conversions vs. direct conversions. The displayed metrics are conversion rates, which are a better indicator since they are independent of the amount of sent emails on a given day. However, you might want to plot a similar graph by using the conversion values or even normalize the values by the amount of sent emails on a given day by creating a new a calculated metric.

mailchimp and revenue data in google data studio: Email Conversion Rate by Sent Day of the Week

Email Campaigns Overview

This table provides you all relevant engagement and conversion metrics per sent campaign and allows you to sort by any of the columns to better understand which of them was more efficient from different perspectives.

mailchimp and revenue data in google data studio: Email Campaigns Overview

Conclusions

As partially explained in the beginning of this article, this dashboard breaks data silos between email and web analytics metrics by allowing you to visualize email marketing dimensions (sent date, campaign name, etc.), engagement data (open rate, click rate, etc.) and generated revenue (last interaction and assisted conversion rate and conversion value). This is something you could hardly do by looking at Google Analytics or Mailchimp reports alone.

Beside the already set up charts, you can create your own by using other available measures (like unsubscriptions, delivered emails, etc.) or creating new calculated fields (e.g. delivery rate, conversions per click, etc.) and slice the data by other dimensions.

Furthermore, Supermetrics gives you many other options, among which turning your Google Data Studio dashboard into a social media & ad words cost data overview report. The limit is your creativity!

Supermetrics Google Data Studio: social media & ad words cost data overview report
Source: supermetrics.com

Meanwhile, we hope that Google will keep improving his product to fill the gaps that it still has compared to other BI tools. New functionalities are getting released 3-5 times per month, so we’ll keep watching their feature release page 😉

Do you see a business case for such an implementation? Contact our digital marketing crew and let’s find out how we can help you!

First Name

Last Name

E-Mail

Message

Enter this code: captcha

Summary
Break data silos with Google Data Studio and Supermetrics
Article Name
Break data silos with Google Data Studio and Supermetrics
Description
How much revenue does your email marketing generate? Break data silos between email engagement and generated revenue with our Google Data Studio dashboard!
Author
Publisher Name
Atos Consulting CH
Publisher Logo

Leave a Reply

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