Get MailChimp API 3.0 Campaign Data in Google Sheets

Posted By: Eivind Savio March 29, 2017

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

Go to Google Sheets, and Start a new spreadsheet
Rename Google Sheets Tab to CampaignData
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:

MailChimp API Key (API_KEY)
Report Start Date (REPORT_START_DATE)

function chimpCampaigns() {
var API_KEY = 'XXX-us5'; // MailChimp API Key
var REPORT_START_DATE = '2013-12-30 20:30:00'; // Report Start Date (ex. when you sent your first MailChimp Newsletter)
  
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("CampaignData");
  
var dc = API_KEY.split('-')[1];
var api = 'https://'+ dc +'.api.mailchimp.com/3.0';
var count = 100; // Max rows to return
  
var campaignList = '/campaigns?&count='+count+'&since_send_time='+REPORT_START_DATE
var options = {"headers": {"authorization": 'apikey '+API_KEY}};
	var apiCall = function(endpoint){
		apiResponseCampaigns = UrlFetchApp.fetch(api+endpoint,options);
		json = JSON.parse(apiResponseCampaigns);
		return json
	}

var campaigns = apiCall(campaignList);
var total = campaigns.total_items;
var campaignData = campaigns.campaigns;
  
 if (campaignData) {
    sheet.clear(); // Clear MailChimp data in Spreadsheet
   // Append Column Headers
    sheet.appendRow(["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", "Last Click"]);
  }  

for (i=0; i< campaignData.length; i++){
    var c = campaignData[i];
    var cid = c.id;
    var title = c.title;
    var subject = c.subject;
    var send_time = c.send_time;
  
	if (send_time){
		apiResponseReports = UrlFetchApp.fetch('https://'+ dc +'.api.mailchimp.com/3.0/reports/'+cid,options);
		reports = JSON.parse(apiResponseReports);
		reportsSendTime = reports.send_time;
		if(reportsSendTime){
			
          var campaign_title = c.settings.title;
          var subject_line = c.settings.subject_line;
          var emails_sent = reports.emails_sent;
          var abuse_reports = reports.abuse_reports;
          var unsubscribed = reports.unsubscribed;
          var unsubscribe_rate = unsubscribed/emails_sent;
          var hard_bounces = reports.bounces.hard_bounces;
          var soft_bounces = reports.bounces.soft_bounces;
          var bounces = hard_bounces+soft_bounces;
          var syntax_errors = reports.bounces.syntax_errors;
          var forwards_count = reports.forwards.forwards_count;
          var forwards_opens = reports.forwards.forwards_opens;
          var opens_total = reports.opens.opens_total;
          var unique_opens = reports.opens.unique_opens;
          var open_rate = reports.opens.open_rate;
          var last_open = reports.opens.last_open;
          var clicks_total = reports.clicks.clicks_total;
          var unique_clicks = reports.clicks.unique_clicks;
          var unique_subscriber_clicks = reports.clicks.unique_subscriber_clicks;
          var click_rate = reports.clicks.click_rate;
          var last_click = reports.clicks.last_click;
			
          // the report array is how each row will appear on the spreadsheet
          var report = [send_time, cid, campaign_title, subject_line, emails_sent, abuse_reports, unsubscribed, unsubscribe_rate, hard_bounces, soft_bounces, bounces, syntax_errors, forwards_count, forwards_opens, opens_total, unique_opens, open_rate, last_open, clicks_total, unique_clicks, unique_subscriber_clicks, click_rate, last_click];

      sheet.appendRow(report);
      }
    }
  }
}

function chimpCampaigns() {

var API_KEY = 'XXX-us5'; // MailChimp API Key

var REPORT_START_DATE = '2013-12-30 20:30:00'; // Report Start Date (ex. when you sent your first MailChimp Newsletter)

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName("CampaignData");

var dc = API_KEY.split('-')[1];

var api = 'https://'+ dc +'.api.mailchimp.com/3.0';

var count = 100; // Max rows to return

var campaignList = '/campaigns?&count='+count+'&since_send_time='+REPORT_START_DATE

var options = {"headers": {"authorization": 'apikey '+API_KEY}};

var apiCall = function(endpoint){

apiResponseCampaigns = UrlFetchApp.fetch(api+endpoint,options);

json = JSON.parse(apiResponseCampaigns);

return json

}

var campaigns = apiCall(campaignList);

var total = campaigns.total_items;

var campaignData = campaigns.campaigns;

if (campaignData) {

sheet.clear(); // Clear MailChimp data in Spreadsheet

// Append Column Headers

sheet.appendRow(["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", "Last Click"]);

}

for (i=0; i< campaignData.length; i++){

var c = campaignData[i];

var cid = c.id;

var title = c.title;

var subject = c.subject;

var send_time = c.send_time;

if (send_time){

apiResponseReports = UrlFetchApp.fetch('https://'+ dc +'.api.mailchimp.com/3.0/reports/'+cid,options);

reports = JSON.parse(apiResponseReports);

reportsSendTime = reports.send_time;

if(reportsSendTime){

var campaign_title = c.settings.title;

var subject_line = c.settings.subject_line;

var emails_sent = reports.emails_sent;

var abuse_reports = reports.abuse_reports;

var unsubscribed = reports.unsubscribed;

var unsubscribe_rate = unsubscribed/emails_sent;

var hard_bounces = reports.bounces.hard_bounces;

var soft_bounces = reports.bounces.soft_bounces;

var bounces = hard_bounces+soft_bounces;

var syntax_errors = reports.bounces.syntax_errors;

var forwards_count = reports.forwards.forwards_count;

var forwards_opens = reports.forwards.forwards_opens;

var opens_total = reports.opens.opens_total;

var unique_opens = reports.opens.unique_opens;

var open_rate = reports.opens.open_rate;

var last_open = reports.opens.last_open;

var clicks_total = reports.clicks.clicks_total;

var unique_clicks = reports.clicks.unique_clicks;

var unique_subscriber_clicks = reports.clicks.unique_subscriber_clicks;

var click_rate = reports.clicks.click_rate;

var last_click = reports.clicks.last_click;

// the report array is how each row will appear on the spreadsheet

var report = [send_time, cid, campaign_title, subject_line, emails_sent, abuse_reports, unsubscribed, unsubscribe_rate, hard_bounces, soft_bounces, bounces, syntax_errors, forwards_count, forwards_opens, opens_total, unique_opens, open_rate, last_open, clicks_total, unique_clicks, unique_subscriber_clicks, click_rate, last_click];

sheet.appendRow(report);

}

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

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!

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

Rjc | March 31, 2017 at 5:22 pm | Reply

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.
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?
- Savio | October 11, 2017 at 8:53 pm | Reply
  
  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.
Pedro | January 28, 2018 at 7:34 am | Reply

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
- Eivind Savio | January 28, 2018 at 8:37 pm | Reply
  
  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(?).
Eric M | January 31, 2018 at 6:05 pm | Reply

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.
- Eivind Savio | January 31, 2018 at 9:15 pm | Reply
  
  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.
Leonardo | September 2, 2018 at 4:14 am | Reply

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?
Joe O'Neill | February 22, 2019 at 11:12 pm | Reply

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.
- Darryl Mabee | April 21, 2019 at 3:35 pm | Reply
  
  THAT fixed mine! 😀
Alexandre | March 21, 2019 at 1:26 pm | Reply

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
Emily | April 5, 2019 at 5:29 pm | Reply

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
- Eivind Savio | April 8, 2019 at 8:17 pm | Reply
  
  Hi Emily
  
  “mailchimpListGrowth” is not my script. You find it in this blog post:
  https://www.benlcollins.com/apps-script/mailchimp-integration/
  
  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
Emily | April 5, 2019 at 5:54 pm | Reply

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!
Ron | May 9, 2019 at 4:36 am | Reply

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!
Ron | May 9, 2019 at 4:58 am | Reply

I forgot to ask, but could you also share that dashboard you have in the picture? That is a well thought out dashboard!!
Marc | May 23, 2019 at 4:42 pm | Reply

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.
- Marc | June 26, 2019 at 9:03 am | Reply
  
  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.
Matteo | June 25, 2019 at 6:56 pm | Reply

Hi, how is possibile to add a collumn with list id?
Asop | December 19, 2019 at 5:34 pm | Reply

We were able to get data from our campaigns and automations transferred to Google Sheet. All of the information is correct, except the total clicks and unique clicks. Do you have any idea why it wouldn’t be pulling in the correct click information?
Sam | December 21, 2019 at 12:50 pm | Reply

Thank you, works like a charm! Is it also possible that the script collect the a/b versions of the campaigns?
David | February 17, 2020 at 7:37 pm | Reply

How can we just download the list of members? We are looking to compare our spreadsheet list to Mailchimp’s list for any missing and ones that our internal email address is incorrect.
- Eivind Savio | February 26, 2020 at 9:57 pm | Reply
  
  Have you seen this help text from MailChimp?
  https://mailchimp.com/help/view-export-contacts/
  
  Regards,
  Eivind