How to import MailChimp Data into Google Data Studio

June 15, 2017 - Written by

***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.

Comments 75 Replies to “How to import MailChimp Data into Google Data Studio”
  1. 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?

  2. 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”

  3. 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

  4. 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?

  5. 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.

  6. 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

  7. 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

  8. 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?

  9. 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

  10. still having TypeError: Impossible d’appeler la méthode “clear” de null. (ligne 44, fichier “Code”)Ignorer

  11. 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 Minh,
      you need to modify the var section (e.g. var campaign_title), the var report section and the columns name (sheet.appendRow).
      Thanks

  12. 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

  13. 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

    • Hi Mia,
      you need to update loop2 with a line like
      sheet.setFrozenRows(1);
      sheet.sort(1, false);
      Thanks

  14. 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

  15. 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!

  16. 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

  17. 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!

  18. 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

  19. 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?

  20. 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!

        • 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!

Leave a Reply

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

  • Analytics Boosters
    Italy

    Vicolo Buranelli, 2
    31100 Treviso
    Italy


  • Analytics Boosters
    United Kingdom

    41 Devonshire Street
    London
    W1G 7AJ
    United Kingdom


  • Analytics Boosters
    United States

    80 S.W. 8th Street
    33130 - Miami
    Florida
    United States of America


CONTACTS

Contact us