Get MailChimp API 3.0 Campaign Data in Google Sheets

MailChimp report in Google Data Studio

Since Google Data Studio doesn’t have a connector to the MailChimp API, you first have to get the data into Google Sheets, and then you have to connect Google Data Studio to that sheet.

Since I (yesterday) couldn’t find any scripts that did this, I “hacked” together the solution below (I’m using the word “hacked” since I’m not a Google Apps Script expert, and the solution stands on the shoulders of others).

Create the Google Sheet

  1. Go to Google Sheets, and Start a new spreadsheet
  2. Rename Google Sheets Tab to CampaignData
  3. Via the menu, go to the Script Editor
    (Tools -> Script editor)

Google Apps Script for MailChimp API 3.0

Paste the script below into the Script editor. You should only have to replace 2 things in this script:

  1. MailChimp API Key (API_KEY)
  2. Report Start Date (REPORT_START_DATE)

The script will output the following data to the Google Sheet from the Reports part of the MailChimp API 3.0:

Sent Time, Campaign ID, Campaign Title, Subject Line, Emails Sent, Abuse Reports, Unsubscribed, Unsubscribe Rate, Hard Bounces, Soft Bounces, Bounces Total, Syntax Errors, Forwards Count, Forwards Opens, Opens Total, Unique Opens, Open Rate, Last Open, Clicks Total, Unique Clicks, Unique Subscriber Clicks, Click Rate and Last Click.

But, you can get even more data from MailChimp if you want that, but then you have to tweak the script to grab more of the data from the Reports part of the MailChimp API.

Auto-update MailChimp data

To always have the newest MailChimp Campaign Data in our sheet, we need to add some auto-updating.

  • From the Script editor menu, go to Edit -> Project’s triggers
  • Add your trigger as shown in the image below (I have chosen every hour, but that is maybe overkill).

Google Sheet - Project's triggers for MailChimp

Some final words

Did I write this script completely on my own? No, I’m not an Google Apps Script expert, so I’m standing on the shoulders to Chip Oglesby here. The basis from this script comes from a Google Apps Script that pushes data to BigQuery.

If you wonder where data in the header image showing Subscribers and Month-on-month Growth Rate is coming from, head over to Ben Collins blog. He has also published a different approach to getting MailChimp Campaign data from the MailChimp API 3.0 into Google Sheets. There you also find some in-depth explanations of how to find the MailChimp API Key etc.

Now go and connect this Google Sheet to Google Data Studio, and create a MailChimp Dashboard that (hopefully) shows that email marketing isn’t monkey business!

21 Comments on "Get MailChimp API 3.0 Campaign Data in Google Sheets"

  1. Awesome, but when I attempted to run the script I received the error “Cannot call method clear of null (line 25, file code). Any advice on what the error on line 25 could be?

    Thanks

    • Eivind Savio | March 31, 2017 at 6:02 pm | Reply

      You have named your sheet for “CampaignData”? Because the code on line 25 clears the sheet before it appends data. If no sheet with that name exist, this would throw such an error.

  2. Consuelo Sartori | October 4, 2017 at 1:24 pm | Reply

    Hello Eivind, I get the same error as above “Cannot call method clear of null (line 25, file code), even is I tried to name the sheet CampaignData. Or campaignData. Do you know why this happens?

    • Sorry for the late answer. Have you been able to solve the problem? I’ve only seen this error if the Sheet Tab Name isn’t CampaignData.

  3. Hi Savio. Thank you so much for the script. Great work!

    However it’s only returning campaigns that are one-off and I would love if I could generate also reports for emails that are part of workflows (automations). Each email has a unique identifier and is, in essence, one campaign, but I’ve tried everything and can’t figure out how to get data for those e as as well.

    Any ideas?
    Thanks again

    • Unfortunately I don’t have any workflows to check against.

      However, in the report you should be able to differentiate between the different types of emails if you add “type” to the report (reports.type):
      “The type of campaign (regular, plain-text, ab_split, rss, automation, variate, or auto).”

      Since automation is mentioned there, it seeems that these type of emails also should be returned(?).

  4. Hey there, I am receiving:

    Request failed for https://us2.api.mailchimp.com/3.0/reports/0295fbca4a returned code 500. Truncated server

    Any ideas? It sent back nine campaigns before it failed.

    • Sorry, no idea. Do you also get the error if you run the script manually?

      However, I do now and then get a timeout error.

  5. Thanks for this great tool Eivind Savio, Do you know how can I add a column to the report including a list ID for each email sent?

  6. Hey all, I think the issue re: “Cannot call method clear of null (line 25, file code)” is that the code is looking for the specific sheet (represented by tabs on the bottom of the page, not the workbook name in the top left) called “CampaignData” within the active workbook called whatever you want. The function just looks for whatever the active workbook is then the specific sheet within it by name “CampaignData”.

    I was having the same problem until I changed that, works great now.

  7. THanks for the script, but its not returning all my campaigns. I have 150+ campaigns emails and its returning only 89 of them. I tried adding more rows to return but its not changing anything. ANy idea why its doing this?

    • Eivind Savio | April 2, 2019 at 9:23 pm | Reply

      Hi Alexandre

      Not sure, but check if it returns everything from the date you have chosen. The date must be in a ISO String format, if not the date can perhaps be misinterpreted.

      I’m planning to relase a Google Sheet with some improvements to this solution, but just need to find the time to write the blog post.

      Regards,
      Eivind

  8. Hello, I am following along with your tutorial to set up this Google Sheet data. For the trigger, I only see “chimpCampaigns”, but I do not see the option to add “mailchimpListGrowth”. Do you have any idea why this option is not showing up for me?

    Thanks in advance for your help!

    Best,
    Emily

  9. I am also trying to figure out how to add active subscribers or “members” to this list of data. Any idea what I will need to input to also input the sum of subscribed members?

    Thanks!

  10. With that said, I plan to release a better Google Sheet solution, where you don’t have to add the scripts, but just fill out some settings.

    Stay tuned.

    Regards,
    Eivind

    Any updates on this Eivind? I love the knowledge you are sharing with the community and do appreciate it!

    I tried using Supermetrics but ran into a bunch of issues with getting subscriber list growth to pass through to datastudio. I was reading through your blog and it seems like your solution might work best!

  11. I forgot to ask, but could you also share that dashboard you have in the picture? That is a well thought out dashboard!!

  12. Thank you. Works fine!
    Do you know how we can add the campaign link (eepurl) to the script and reports in Google Sheets? I’m confused by the Mailchimp documentation.

    • Found:
      add this to line 36:
      var archive_url = c.archive_url;

      Add the column name of your choice to line 27 and add archive_url to line 66.

  13. Hi, how is possibile to add a collumn with list id?

Leave a comment

Your email address will not be published.


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.