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!

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


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

Leave a comment

Your email address will not be published.


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