How to import MailChimp Data into Google Data Studio

***Quick Update***

I just rewrite the code for the MailChimp Import in order to sort the stats based on campaign date. Thanks to “Tubetto” for the hint!

***End Update***

How to pull data from MailChimp campaigns (Open Rates, Clicks, and so on) into Google Data Studio? The question arised while creating a dashboard to report the results of email campaigns. The needings were to have all the data in one place, breaking data silos. But at the moment there are no connectors between MailChimp software and Data studio. The manual process to extract data and analyze it could be definitely time consuming. The solution is to find out a workaround using MailChimp’s APIs.

The “bridge” between MailChimp and Google Data Studio

Let’s think about the process of data extraction from Google Analytics. The key to read and manipulate data is to feed an Excel file or a Google spreadsheet. We choose Google spreadsheet to simplify and speed up things. And that’s exactly the solution to the problem.

Pull MailChimp Data into Google Spreadsheet

To include MailChimp Data into external tools you have to access MaiChimp account and generate the MailChimp API Key.

The API Key can be obtained following the path: MailChimp Login > Account > Extras > API Keys > Create a Key. Now you can see a screen similar to the one below:

Copy, paste and save somewhere your API Key. Now you have to work on the Google spreadsheet following the points below:

• Open a new Google spreadsheet
• Rename the tab CampaignData
• Click on Tools > Script Editor
• Download the following “Frankenstein File” (assembling different sources): MailChimp_Campaigns_Data
• Open the file and replace row 2 “YOUR API KEY” with the real value of the API Key
• Copy and paste the file into Google Spreadsheet Editor and click on Save, then on Run (play symbol).
• Accept all the authentication requests.

Now your Google spreadsheet will include all the variables selected from the file. Obviously you can edit the array adding new data from MailChimp or remove it.

Don’t forget to set the data auto-update. Go to the script editor and follow the steps below:

• Menu > Modify > Current Project Trigger
• Add Trigger
• Choose when you want to set the auto-update and save.

Import Data from Google Spreadsheet to Google Data Studio

Now we can pull data into Google Data Studio into a new report or in a report you have already in place, simply following visual instructions.

From now on you can create graphs and tables including MailChimp data and exploit all the features Data Studio offers. This is of great help to monitor and keep under control your mail marketing campaign’s KPIs all in one place.

It goes without saying that your MailChimp data are presented as standalone and are not integrated with Google Analytics. To reach this ultimate goal – even if with time restrictions- you will have to:

• Set the campaign title inside mailChimp as a utm_campaign parameter in Google Analytics
• Import MailChimp data as well as Google Analytics data into Big Query
• Use as a primary key utm_campaign and campaign_title to merge data
• Import Big Query data into Data Studio and run the report.

Bonus tip

In addition to campaigns’ data you cal also easily pull data from MailChimp lists (e.g. number of subscribers, unsubcribers, etc.):

• Download the second “Frankenstein File”: MailChimp_UserUpdate
• Include the API Keys and also the ID of the list you want to update (List Name > Settings > List name and campaign defaults)
• Rename the tab into the new Google spreasheet as “ListUpdate”
• Follow the steps for the data import in Google Spreadsheet and Google Data Studio.

Believe me, it is simpler to do it than explain it 🙂 Any questions? Feel free to ask me.