Automate MailChimp Reporting with Google Spreadsheets

I made it! I am finally a nerd. At the top of the MailChimp API there is a clear disclaimer: “Woah, Nerds Only!” Undeterred, I scrolled on to test my nerdy medal. The following is my account of navigating the well-charted waters of the MailChimp API and a tutorial on how to automate a “BARF” report using Google Apps Scripts and Google Spreadsheets.

First, let me tell you how this started. Reporting in digital marketing can be a lot of work. Automate it. Ok done explaining.

The MailChimp API is a Friendly API

MailChimp’s API is nice and clean with great documentation. It has well-defined endpoints and returns JSON objects. Google Apps Scripts is great for parsing JSON objects thanks to the JSON class made specifically for that.

The problem as I defined it was as follows:

1. MailChimp API has the data I want.
2. I have to make a call to API using Apps Script’s UrlFetchApp
3.  The call has to be authenticated by passing along API key.
4. First I have to make a call to get all the campaigns/list endpoint to get a list of campaigns’ name/id info.
5. Then I have to iterate through all the selected campaigns and pass their id to the reports/[whichever-stat-I-want] endpoints to get back each campaign’s stats.
6. Format that data into an array and write it to the spreadsheet.

The Google Apps Script

[ultx]
Yup, so that’s exactly what I did. The interesting part for me was getting a better understanding of the UrlFetchApp class. I had used it before for getting data from APIs or web pages but only the simple way:

UrlFetchApp.fetch(“whateversiteiwant.com/data-from”)

But Mailchimp’s API asks for more. It asks that all requests are made as POST requests and it asks that the “apikey” and additional parameters be passed as JSON objects rather than a query string like: www.site.com/api?param=value.

This meant using the additional “param” parameter in the UrlFetchApp.fetch() method. I am happy that I got it to work. It’s clear in the code how it is used but I still need to know exactly why and how it works because it’s pretty cool. Expect a blog post on that.

Beyond that, the code is pretty self-explanatory. I commented parts that would be dodgy for Codecademy level folks. (I know. I was there not too long ago.)

[/ultx]

Writing the report to a Google Spreadsheet

Check it out. It is explained in the comments.

// campaigns list api endpoint docs: apidocs.mailchimp.com/api/2.0/campaigns/list.php
// reports api endpoint: apidocs.mailchimp.com/api/2.0/reports/summary.php
// GAS docs: developers.google.com/apps-script/reference/url-fetch/url-fetch-app#fetch(String,Object)
// mailchimp open tracking calculations: kb.mailchimp.com/article/about-open-tracking
  
// your api key can be found at: admin.mailchimp.com/account/api/
// standard "24 hour format in GMT, eg "2013-12-30 20:30:00" - if this is invalid the whole call fails"
// add formated values for start and end date like: var REPORT_START_DATE = "2013-12-30 20:30:00"

function chimpReport() {
  
  var API_KEY = 'yourapikeygoeshere';
  var REPORT_START_DATE;
  var REPORT_END_DATE;
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();  
  
  var dc = API_KEY.slice(-3);
  var api = 'https://'+ dc +'.api.mailchimp.com/2.0';
  var campaignList = '/campaigns/list.json';
  var reports = '/reports/summary.json';
  
  // MC api-specific parameters
  var payload = {
    "apikey": API_KEY,
    "sendtime_start": REPORT_START_DATE,
    "sendtime_end": REPORT_END_DATE
  }; 
  
  // GAS specific parameters: 
  var params = {
    "method": "POST",
    "muteHttpExceptions": true,
    "payload": payload
  };
  
  var apiCall = function(endpoint,cid){
    if(cid){
      payload.cid = cid;
    }
    var apiResponse = UrlFetchApp.fetch(api+endpoint, params);
    var json = JSON.parse(apiResponse);
    return json;
    };
  
  var campaigns = apiCall(campaignList);
  var total = campaigns.total;
  var campaignData = campaigns.data;
  
  for (var 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;
    
    // send_time values are only present for campaigns that have been sent. otherwise set to null.
    // this if statement will only call for report data and write to the spreadsheet, data from sent campaigns.
    
    if (send_time){
      
      var r = apiCall(reports,cid);
      var emails_sent = r.emails_sent;
      var opens = r.opens;
      var unique_opens = r.unique_opens;
      var clicks = r.clicks;
      var unique_clicks = r.unique_clicks;
      var open_rate = (unique_opens / emails_sent).toFixed(4);
      var click_rate = (unique_clicks / emails_sent).toFixed(4);
      
      // the report array is how each row will appear on the spreadsheet
      var report = [send_time, subject, emails_sent, opens, unique_opens, clicks, unique_clicks, open_rate, click_rate];
      
      Logger.log(report);
      
      // note that this method will append to the bottom of the spread sheet wherever that is.
      // to overwrite a specific range use setValues()
      
      sheet.appendRow(report)
    }    
  }
}

All set!? Next step: make a Google Spreadsheet dashboard with Google Analytics data and Google Spreadsheets Sparklines.