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.
Leave a Reply
-
Analytics Boosters
ItalyVicolo Buranelli, 2
31100 Treviso
Italy
-
Analytics Boosters
United Kingdom41 Devonshire Street
London
W1G 7AJ
United Kingdom
-
Analytics Boosters
United States80 S.W. 8th Street
33130 - Miami
Florida
United States of America
Is this solution still working? I can’t get anything populated in the spreadsheet 🙁
Hi Meduzz,
we are working on the new Mailchimp API. Stay tuned
Hi Enrico,
Great article!
I tried to implement the userupdate, but got the following error:
TypeError: Cannot read property ‘clear’ of null
chimpSubscribers @ Code.gs:44
Can you tell me how to adjust the code properly?
Hi Pim
the error could be due to the new Mailchimp’s API.
We are working on it!
thanks
Thank you so much! I got my data into the spreadsheet even though I’m really bad at working with code.
Unfortunately I get an error in my Google Sheet concerning “Open Rate” and “Click Rate”: it says “Formula parse error”
Something strange.. I need to investigate on that error
Hi there
I’m having the same issue that a lot of other people had/have which is TypeError: Cannot call method “clear” of null. (line 25, file “Code”) Can you please help me with the above issue?
Thanks
I will check it
Hi !
I don’t have revenue in the spreadsheet, all data are correct but missing this one.
How can I add it to data
Hi Rodolphe,
you need to set the revenue as a variable inside the code.
Thanks
Enrico
Hi,
Thank you for the cool hack! I’m having an issue though – only some of my campaign data is coming into the spreadsheet; some records are being omitted. Example, if I send 5 campaigns per month, only 2/3 are coming up… Can you help?
Hi Claire,
this is a little bit weird 🙂
Could you write me back with some more details?
Thanks
Enrico
Is there a way to have this report populate the number of successful delieveries from the campaign or to calculate that number? I know I can pull the number of bounces but want to automatically report the delivery rate.
Hi Will,
you can modify the code setting the field “emails_sent” MC’s API.
Thanks
Enrico
Hi!
Is it possible to import to Data Studio number of subscriber to a List?
Regards
Hi Maria,
do you mean: import the number of total subscribers for a single list or the number of all subscribers for all your lists?
Thanks
Hi, it’s great script, thanks a lot
But I have an issue – limits in script running. I need to import near 2000 campaigns to sheets.
Can you help me please? Can I do partly this action by var REPORT_END_DATE like https://i.imgur.com/HY6zOGT.png ?
I tried this but the script works only with REPORT_START_DATE and have no limits?
Hi Miranda,
Try to modify the var count=500 (to 2.000 or greater) instead of Report_End_Date.
Let me know if it works properly.
Thanks
Enrico
Hey everyone,
Thanks for sharing 🙂 I can see all the campaigns, but unfortunately not the campaign variants resulting from A/B tests. Is there a way to see the corresponding children in addition to the parent campaigns?
Hi anna,
to import the AB test data you need to modify the API calls. You can do it following the MailChimp’s API guide.
thanks
Hi Enrico,
Could you please tell me more about this? I can’t find out how to fix it. Thanks!
Here the API Guide: you could find variate_settings as API object https://mailchimp.com/developer/reference/campaigns/
Hi there! I’m having a little trouble getting more than 900 rows of data to return when I run the script, and I keep getting the error message: “Exceeded maximum execution time.”
Is it just because I’m trying to get too much data at once? I have quite a bit of data I’d like to pull in, well over 1,000 rows … so I can see how that might be the case.
Hi Hannah,
correct, you’re exceeding the max API calls.
Try to reduce the time period and do more than one export
Thanks
still having TypeError: Impossible d’appeler la méthode “clear” de null. (ligne 44, fichier “Code”)Ignorer
sorry all fine
You’re welcome! 🙂
Hi all!
Here you could find an update regarding on how to importo MailChimp Automations’ data into Google Data Studio!
https://www.analyticsboosters.com/data-viz/import-mailchimp-automations-in-google-data-studio/
Enjoy
Thank you so much !
Do you know how can I do the same by segmented list ?
This is also something I’d like to do. However – I’ve read that the Segment parameter is “only handled as POST actions, not GET actions, so it doesn’t appear to be fetch-able data for us from MailChimp.”
I’d love if this were not true
https://support.supermetrics.com/support/discussions/topics/19000021532
Hi!
I’ve implemented the script as stated but I only see campaigns till last month and not the current month. Am I doing something wrong? Or do I have to make changes to the script?
Thanks in advance!
Hi Chantal,
This is strange.. I saw all my campaigns..
could you please drop me a line in order to understand the issue?
Thanks
Can the problem be that it is a custom flow that I want to measure? Or shouldn’t that be a problem?
What can I send you so you can help me? Perhaps you can e-mail me directly?
Sure, I’ll send you a DM.
Thanks
Sure! I’ll send you a DM
Thanks
Very helpful article. Can you show me how to modify the column for more ( or less) data?
Hi Minh,
you need to modify the var section (e.g. var campaign_title), the var report section and the columns name (sheet.appendRow).
Thanks
Hi! How can i import locations report into the sheet?
Thanks!
Hi George,
do you mean a merge tag with location info or the Location under a contact info?
Thanks
locations of opens would be helpful- awesome resource
Hi, Sadly I’m getting the following error “TypeError: Cannot call method “clear” of null. (line 44, file “Code”)” when trying to run MailChimp_UserUpdate script. The first script works fine
Fixed. Inside the script, the sheet was called the incorrect name.
Perfect. Happy that it works!
Hi –
I followed the steps as you have outlined. Once I ran the script I get a message that says “Running function my function” but nothing in the excel file populates. What did I miss?
Hi Melissa,
try to change line 6 with this var sheet = ss.getSheetByName(“CampaignData”); and rename your spreadsheet CampaignData, then launch the script.
Let me know if it works
Thanks
this is excellent…. thanks…. saved me paying for a third party to pull this data in!
😀 Thanks James!
Salve!
what syntax do I need to add to get the mailchimp list by date?
Hi Mia,
you need to update loop2 with a line like
sheet.setFrozenRows(1);
sheet.sort(1, false);
Thanks
Hi! Thank you for such a helpful script. I am not a coder and is just following details I see online. My question is that, I want to include another column which is the ‘List’ (list of subscribers). Now, my Mailchimp data has more than 10 lists (different client lists. For example, HKG for all clients in HKG, SIN for all clients based in SIN, TPE for all clients based in Taiwan). How can I show the exact list names (HKG, SIN, TPE) instead of ‘undefined’. Thank you!
Hi Maddie,
I have to take a look closely to the MailChimp API and find something that could help you.
Thanks
Hello,
Could you importa orders and revenue information from mailchimp to google studio as well?
Hi Lisseth,
yes you can. You only need to modify the file and set new variables.
Here the correct call for MailChimp API: https://developer.mailchimp.com/documentation/mailchimp/reference/reports/ecommerce-product-activity/
Thanks
The same issue as everyone else – the code won’t run because there’s a error on line 25. Please help!
Hi Morgan,
could you please specify the error?
We run the codes and there are no bugs
Thanks
Hello, just wanted to share with you an edit I did to the script: I’ve added after the campaign data FOR LOOP 2 lines to sort the stats based on campaign date (which is the first column)
sheet.setFrozenRows(1);
sheet.sort(1, false);
Thanks for the great script!
Yeah, good catch!
I will update the script with your lines!
Thanks!
After running the script. i am not receiving any data.
Used the same file name also but then too it’s not working.
Appreciate your help
Hi Nidhi,
the script still works in our spreadsheets.
Do set the correct API Key and variables?
Thanks
Hi there. Do you think it’s possible to add a variable for the *|ARCHIVE|* link for a campaign?
Hi Michael,
yes, you could add Archive.
Follow this link for the correct api call: https://developer.mailchimp.com/documentation/mailchimp/reference/campaigns/content/
Thanks
I had the same issue and I named the sheet MaiChimpCampaigns and the tab CampagneMailChimp and it works great!
Hi, Also in the bonus tips the problem is the same, you have to rename the tab and the sheet UpdateLista end you should be fine 🙂
Hi there!
I’m trying to setup this, but I’m having some issues, for example this error:
Falha no pedido de código https://us15.api.mailchimp.com/3.0/campaigns?&count=100&since_send_time=2014-11-30%2020:30:00 devolvido 401. Resposta do servidor truncado: {“type”:”http://developer.mailchimp.com/documentation/mailchimp/guides/error-glossary/”,”title”:”API Key Missing”,”status”:401,”detail”:”Your reque… (utilize a opção muteHttpExceptions para examinar a resposta completa) (linha 15, ficheiro “Código”)
Please, do you know what is the problem?
Thank you!
Hello
This is fantastic! Thank you.
I am trying to use this for a SaaS business and for it to include our automated, on-boarding emails. This data doesn’t come through – just one off campaigns.
Can you please advise on how to do this?
Cheers
Paul
Did anyone ever answer this regarding ongoing automated campaigns? I’m curious as well
I downloaded this and didn’t encounter any errors, but nothing populates the Google Sheet file after running the script? Does the fact that some of this code is in Italian impact English-using systems?
Hello, I downloaded the Frankenstein file but it gives me the following error:
TypeError: Cannot call method “clear” of null. (line 25, file “Code”)
Sorry, I am not a coder, I don’t know how this can be fixed. Could you please help?
nevermind, I found the error, I haven’t named the sheet properly. I actually focused on the document and not on the sheet!
Good to know you solved the problem. Hope you’ll find the file useful!
I get the same error. Both the tab and the sheet are named MaiChimpCampaigns. Did you rename something in the script?
Name the tab CampagneMailChimp and you should be fine;)
Hy! I’m having the same issue and renamed the sheet to CampagneMailChimp, but still get the same error code. Any new ideas on what might be wrong?
Hello! I’m having the same issue and renamed the sheet, tab and scripteditor to CampagneMailChimp, but still get the same error code in the 25 linha. I need to edit something in the script?
Hi Renato,
I have updated the code with a new script.
Could you please replicate every step listed in the post and try again?
Thank you!