Get MailChimp Subscribers (Member Count) in Google Sheets

One MailChimp number you really want to have in a dashboard, are the number of MailChimp Subscribers (or Member Count as it’s called in the MailChimp API 3.0 documentation). So here is a quick blog post about how to get this number, plus some other stats about your MailChimp Subscriber List.

The script will output the following data to a Google Sheet from the Lists part of the MailChimp API 3.0 (Read method):

Subscribers, Unsubscribe Count, Cleaned Count, Member Count Since Send, Unsubscribe Count Since Send, Cleaned Count Since Send, Campaign Count, Campaign Last Sent, Merge Var Count, Average Subscription Rate, Average Unsubscription Rate, Average Target Subscription Rate, Open Rate, Click Rate, Date of Last List Subscribe, Date of Last List Unsubscribe, List Rating

Create the Google Sheet

Go to Google Sheets, and Start a new spreadsheet
Rename Google Sheets Tab to MemberData
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)
MailChimp List ID (LIST_ID)

function chimpSubscribers() {
var API_KEY = 'XXXX-us5'; // MailChimp API Key
var LIST_ID = 'YYYYY'; // MailChimp List ID
  
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("MemberData");
  
var dc = API_KEY.split('-')[1];
var api = 'https://'+ dc +'.api.mailchimp.com/3.0';
  
var memberList = '/lists/'+LIST_ID
	options = {"headers": {"authorization": 'apikey '+API_KEY}};
	var apiCall = function(endpoint){
		apiResponseMembers = UrlFetchApp.fetch(api+endpoint,options);
		json = JSON.parse(apiResponseMembers);
		return json
	}

var members = apiCall(memberList);
if (members) {
  var member_count = members.stats.member_count;
  var unsubscribe_count = members.stats.unsubscribe_count;
  var cleaned_count = members.stats.cleaned_count;
  var member_count_since_send = members.stats.member_count_since_send;
  var unsubscribe_count_since_send = members.stats.unsubscribe_count_since_send;
  var cleaned_count_since_send = members.stats.cleaned_count_since_send;
  var campaign_count = members.stats.campaign_count;
  var campaign_last_sent = members.stats.campaign_last_sent;
  var merge_field_count = members.stats.merge_field_count;
  var avg_sub_rate = members.stats.avg_sub_rate;
  var avg_unsub_rate = members.stats.avg_unsub_rate;
  var target_sub_rate = members.stats.target_sub_rate;
  var open_rate = members.stats.open_rate;
  var click_rate = members.stats.click_rate;
  var last_sub_date = members.stats.last_sub_date;
  var last_unsub_date = members.stats.last_unsub_date;
  var list_rating = members.list_rating;

  var report = [member_count, unsubscribe_count, cleaned_count, member_count_since_send, unsubscribe_count_since_send, cleaned_count_since_send, campaign_count, campaign_last_sent, merge_field_count, avg_sub_rate, avg_unsub_rate, target_sub_rate, open_rate, click_rate, last_sub_date, last_unsub_date, list_rating];
	  
Logger.log(report);
  
// Clear MailChimp data in Spreadsheet
   sheet.clear();
// Append MailChimp data to Spreadsheet
   sheet.appendRow(["Subscribers", "Unsubscribe Count", "Cleaned Count", "Member Count Since Send", "Unsubscribe Count Since Send", "Cleaned Count Since Send", "Campaign Count", "Campaign Last Sent", "Merge Var Count", "Average Subscription Rate", "Average Unsubscription Rate", "Average Target Subscription Rate", "Open Rate", "Click Rate", "Date of Last List Subscribe", "Date of Last List Unsubscribe", "List Rating"]);
   sheet.appendRow(report);
 }  
}

function chimpSubscribers() {

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

var LIST_ID = 'YYYYY'; // MailChimp List ID

var ss = SpreadsheetApp.getActiveSpreadsheet();

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

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

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

var memberList = '/lists/'+LIST_ID

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

var apiCall = function(endpoint){

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

json = JSON.parse(apiResponseMembers);

return json

}

var members = apiCall(memberList);

if (members) {

var member_count = members.stats.member_count;

var unsubscribe_count = members.stats.unsubscribe_count;

var cleaned_count = members.stats.cleaned_count;

var member_count_since_send = members.stats.member_count_since_send;

var unsubscribe_count_since_send = members.stats.unsubscribe_count_since_send;

var cleaned_count_since_send = members.stats.cleaned_count_since_send;

var campaign_count = members.stats.campaign_count;

var campaign_last_sent = members.stats.campaign_last_sent;

var merge_field_count = members.stats.merge_field_count;

var avg_sub_rate = members.stats.avg_sub_rate;

var avg_unsub_rate = members.stats.avg_unsub_rate;

var target_sub_rate = members.stats.target_sub_rate;

var open_rate = members.stats.open_rate;

var click_rate = members.stats.click_rate;

var last_sub_date = members.stats.last_sub_date;

var last_unsub_date = members.stats.last_unsub_date;

var list_rating = members.list_rating;

var report = [member_count, unsubscribe_count, cleaned_count, member_count_since_send, unsubscribe_count_since_send, cleaned_count_since_send, campaign_count, campaign_last_sent, merge_field_count, avg_sub_rate, avg_unsub_rate, target_sub_rate, open_rate, click_rate, last_sub_date, last_unsub_date, list_rating];

Logger.log(report);

// Clear MailChimp data in Spreadsheet

sheet.clear();

// Append MailChimp data to Spreadsheet

sheet.appendRow(["Subscribers", "Unsubscribe Count", "Cleaned Count", "Member Count Since Send", "Unsubscribe Count Since Send", "Cleaned Count Since Send", "Campaign Count", "Campaign Last Sent", "Merge Var Count", "Average Subscription Rate", "Average Unsubscription Rate", "Average Target Subscription Rate", "Open Rate", "Click Rate", "Date of Last List Subscribe", "Date of Last List Unsubscribe", "List Rating"]);

sheet.appendRow(report);

}

Since I in a previous blog post called Get MailChimp API 3.0 Campaign Data in Google Sheets covered how to auto-update data from MailChimp to Google Sheets, I will not cover that in this blog post.

Reporting MailChimp Subscribers in Google Data Studio

Although you can view these MailChimp data directly in Google Sheets and create beautiful reports and dashboard there, the reason I wrote this script is that I wanted this information in Google Data Studio. Since Google Data Studio don’t have a connector to the MailChimp API, you will first have to get the data into Google Sheets, and then you will connect Google Data Studio to that sheet.

Now go and create a MailChimp Dashboard that (hopefully) shows that your email marketing is not bananas!

6 Comments on "Get MailChimp Subscribers (Member Count) in Google Sheets"

Lana | August 31, 2017 at 12:44 pm | Reply

Would it be possible to customize this to fetch stats for a segment within the list? Thanks
- Savio | September 7, 2017 at 7:39 pm | Reply
  
  Hi Lana
  
  It is probably possible, but with the new lauch of “Community Connectors” in Google Data Studio, the way forward isn’t using this Google Sheet method:
  https://analytics.googleblog.com/2017/09/google-data-studio-quicker-and-broader.html
  
  Supermetrics has (for now) a free connector for MailChimp. See their entire lists of connectors here:
  https://try.supermetrics.com/all-data-studio-connectors/
  
  Ben Collins have also mentioned that he is working on a connector for MailChimp, and he mentions in this blog post that he plans to publish it soon:
  https://www.benlcollins.com/data-studio/community-connector/
Leonardo | September 2, 2018 at 5:00 am | Reply

How can I record monthly statistics using the script? I currently set it but I am only receiving a single record. It will be awesome to look at the evolution of this numbers.

Thanks in advance
Darren S | October 15, 2018 at 3:53 pm | Reply

I am also interested in to learn if you can get monthly statistics to see the evolution
Florent | April 22, 2019 at 12:20 pm | Reply

Wonderful !
Your script is so simple and helpful that I’m wondering why using connectors.
I haven’t found free ones. Somebody know where I could find it ?
Supermetrics Mailchimp Connecter doest not appear free anymore https://supermetrics.com/pricing/data-studio.

I changed a little bit the code for more readability and maintainability :
https://pastebin.com/Zy1wJxgs
(I prefer using an Object key:value instead of get two array, if you want to add or remove a value, it is easier)
Thank you very much++!
- Eivind Savio | April 29, 2019 at 7:56 pm | Reply
  
  Thanks for your comment Florent.
  
  Object key:value is a nice improvement.
  
  Regards,
  Eivind