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!

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

Leave a comment

Your email address will not be published.