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:
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.
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.
- 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.
If your campaign was named “OnlineToOfflineContest”, the following would get appended to any url in the email
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
- 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.
- 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 “MailchimpData” Worksheet from your Spreadsheet
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.
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
- Field Name: Open Rate
Formula: Opens / Emails delivered
- Field Name: Assisted CR
Formula: Assisted conversions / Emails delivered
- Field Name: Last Int. CR
Formula: Last interaction conversions / Emails delivered
Click on “Add to Report” once you have created all the fields.
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.
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.
- Dimension: Weekday
- Metric: Open Rate
- Metric: Click Rate
- Dimension: Weekday
Metrics: Assisted CR, Last Int. CR
- 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.
Confirm you want to change the format to Date and you are all set!
What is in the dashboard?
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.
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.
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.
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.
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.
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!
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!