Mailchimp reports in Google Sheets

How to create a custom function to connect Google Sheets + Mailchimp API

How did it all start?

At Touts.com.br every week starts with a meeting. On Monday we revisit last week's Key Performance Indicators (KPIs) and focus on gathering insightful information to work on this week.

One of our key metrics is Mailchimp newsletter subscribers. That's important to understand if our campaigns are working. So every week I'd open Mailchimp to get this single piece of information. Not cool, right?

So I decided to give a shot on Google Spreadsheets and to try to automate this single process by using the Mailchimp API. More specifically, I'm using the */lists/members* method to get the total number of subscribers.

I'll get into the details. For now, let's create our blank spreadsheet.

Custom formulas in Google Sheets

In this part I'll show you how to create a formula to get the total number of your Mailchimp subscribers. You'll be able to use it like:

=mailchimpSubscribersCount(YOUR_API_KEY, YOUR_LIST_ID)

To get started, just:

  • Open a new (or existing) spreadsheet in Google Sheets
  • Go to Tools > Script editor…
Opening the script editor in Google Sheets
  • Paste the code below
/**
* Get the number of subscribers of a Mailchimp's list
*/
function getSubscribersCount(api_key, list_id) {
var API_KEY = api_key;
var LIST_ID = list_id;

var dc = API_KEY.split('-')[1];
var api = 'https://'+ dc +'.api.mailchimp.com/2.0';
var membersPath = '/lists/members.json';

// MC api-specific parameters
var payload = {
"apikey": API_KEY,
"id": LIST_ID
};

// GAS specific parameters:
var params = {
"method": "POST",
"muteHttpExceptions": true,
"payload": payload
};

var apiCall = function(endpoint){
var apiResponse = UrlFetchApp.fetch(api+endpoint, params);
var json = JSON.parse(apiResponse);
return json;
};
var members = apiCall(membersPath);
return members.total;
}
  • Save, and go back to your spreadsheet
  • Write your Mailchimp API Key inside the cell B1
  • Write your Mailchimp List ID inside the cell C1
  • Use your formula in the cell A1 to get the subscribers count
=mailchimpSubscribersCount(B1, C1)

You should now be able to have your total numbers of subscribers!

Now, that's just the tip of the iceberg. If you wonder what else you could do with the API and Google Sheets, just keep reading the example below :)

Fully automated reports via Mailchimp API

Ok! You must be anxious to try this one. It's a bit tricky, but should work for you. Just pay attention to the beginning of the function below and replace 'YOUR-API-KEY' with your own key from Mailchimp.

Here is the code! Copy and paste it inside the script editor.

// 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 mailchimpReport() {
var API_KEY = 'YOUR-API-KEY';
var REPORT_START_DATE;
var REPORT_END_DATE;

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

var dc = API_KEY.split('-')[1];
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;

sheet.appendRow(['send_time', 'subject', 'emails_sent', 'opens', 'unique_opens', 'clicks', 'unique_clicks', 'open_rate', 'click_rate']);

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

If you used the proper API key, you should be able to run this code and check out the results on your spreadsheet.

Anyways, since we're already here, let's make it even better. The code snippet below will add a Menu to your Google Sheets navbar everytime you open the spreadsheet. That way you don't need to open the script editor every time you want to run your reports. Makes sense? Go ahead!

/**
* A special function that runs when the spreadsheet is open, used to add a
* custom menu to the spreadsheet.
*/
function onOpen() {
var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [
{name: 'Mailchimp report', functionName: 'mailchimpReport'}
];
spreadsheet.addMenu('Reports', menuItems);
}

Beautiful, right?

Are you into productivity hot tips for web development? I’m a Brazilian full-stack developer, living and working in Paris, and I regularly share articles like the one you’ve just read. Follow me on twitter to stay tuned.

Brazilian full-stack developer. Living and working in Paris. Sharing productivity hot tips for web development. https://twitter.com/whosantelo

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store