How to Pull Mailchimp Data into Google Sheets with Google Apps Script

MailChimp collects valuable tracking data for your email campaigns—but this data is useless if you don’t know how to interpret and analyze it to gain insights. 

One solution to this problem is to add MailChimp data to Google Sheets, where you can visualize your tracking information using charts and graphs. 

In this guide, we’ll learn how to build an AppScript that connects the MailChimp API to Google Sheets, then pulls the campaign information into a spreadsheet so we can analyze it. 

An overview of what we’ll cover:

So let’s get started!

Analyzing Data from Various Tools

Collecting all your tracking data from various sources into a single tool is convenient for analysis. That said, the process of moving all that data into one place can be time-consuming and challenging. 

Google Sheets can pull data from different tools for you and analyze that data regularly. Additionally, for some tools, you can automate this process to import data directly into Google Sheets (like from Google Analytics using Supermetrics).

For Mailchimp, however, this isn’t the case.

Tools that support and do not support importing data to Google Sheets

This is where Google Apps Script can help you. It’s similar to VBA for Excel where you can write JavaScript code to pull data from various sources.

Lately, I’ve heard a lot of marketers ask, “How can you pull data from MailChimp into Google Spreadsheets?” We’ll answer this question here. 

So let’s get started!

Setting up Your Google Sheets Columns

First, we’ll need to set up Google Sheets columns. 

We can start by adding all the headings in the sheet that will help us analyze our data. Open a blank sheet (and make sure to name it something relevant).

The column headings will be the details we want to get from the API, such as Campaign Sent Date, Campaign Title, Campaign Subject line, number of Recipients, number of Clicks, and so on.

We can even create headings for data that will be calculated from other parameters. For example, metrics like Open Rate and the Click Rate are good indicators of our email campaign performance.

Adding headings to the Google Sheets to analyze data

Next, we’ll write the code in the Script editor and access the API key from MailChimp. 

Example 1: Pulling Campaign Metrics

To open the editor, click on ToolsScript editor

Opening Script editor from the Tools in Google Sheets

Remove all the default code in the Script editor. (You should also rename the Script to something more relevant to your task.)

Next, we’ll create the necessary API Key and List ID that we’ll need to run our code.  

var API_KEY = ' '; 
var LIST_ID = ' '; 
Creating the API Key and List ID in the Script editor

We can retrieve these variables from our MailChimp account. 

Get API Keys

In a new tab, open MailChimp and log into your account. Go to your Profile and under the Extras tab, select the API keys.

Selecting the API Keys under Extras to create a new key in Mailchimp

Scroll down and click on Create A Key.

Creating a new API in MailChimp

The latest API Key will be the one on top of the list. We can click on its Label and rename it.

Renaming the API Key Label in the MailChimp account

Our API Key is now ready to use. Copy it from your MailChimp account and paste it into the Script editor between the two single quotes after API_KEY. The API key should be in the form of a text string. 

Pasting the API Key to the code in the Script editor of Google Sheets

🚨 Note: The API Key shouldn’t be shared with anyone else as it can be misused. With your API key, anyone can infiltrate your email list and access your subscriber list. They can delete or add subscribers or even send spam campaigns!

Next, let’s go ahead and retrieve the List ID. 

Building MailChimp List

We’ll go back to our MailChimp account and from the menu, click on the Lists option. 

Selecting Lists under the menu tab to get the List ID in MailChimp

Click on the list from which you want to import the data. 

Selecting List to get the List ID in MailChimp

On your List, go to SettingsList name and defaults

Selecting the List name and defaults under Settings of the List in MailChimp

Once it is open, you will see your List ID. Copy this ID. 

Copying the List ID from List name and defaults in MailChimp

Then, paste it in the Script editor in Google Sheets between the single quotes after LIST_ID.

Pasting the List ID in the Script editor

Now that we’ve created the variables, we’ll write a function that retrieves data from the API. 

Copying and Editing Code Details

Click on View and select Compact controls to get a little more space to work with.

Selecting Compact controls under the View option in the Script editor

Next, we’ll add some code to create a function called mailchimpCampaign(). In this code, we’ve specified the roots and the endpoints of the API that we want to access. 

We’ve also defined some parameters in the code. The most important one is the API_KEY. It passes your API Key to MailChimp so that it knows that this is a trusted application, allowing you to access your data.

You can find the entire code in our GitHub repository

Defining roots and endpoints of the API and passing the API Key in the code in Script editor

Next, we’ll write a code to call the MailChimp API and pass the received data. The Logger function will then display this data. 

Calling the MailChimp API in Script editor to display the received data

To run this function, first click on the Save button and go to Run → mailchimpCampaign.

Running the mailchimpCampaign Script

Google will ask you to review some permissions, so just click through and confirm the account that you want to authorize to deploy the script. This will connect your Google account to external services.

Go to the View option and click on Logs.

Viewing the data Logs returned by MailChimp account

You’ll see the data received from MailChimp API. The data in this form is difficult to comprehend, but you’ll get an idea of what kind of data is sent.

For example, you can see the send_time parameter returned with a value.  

Data returned by MailChimp in the Logs in Script editor

Next, let’s fetch some campaign data. We’ll write a code to log the number of the campaigns and also extract all the subject lines from these campaigns. 

Code to display the number of campaigns and the subject lines of these campaigns in the Script editor

Once this is done, we’ll run the campaign. Click on Run, and select the mailchimpCampaign.

Running the mailchimpCampaign script

And then go to View → Logs to see all the received data. 

Viewing Logs from the Google Sheets script editor

If our script is running correctly, we’ll see all the data including the number of campaigns and the subject lines of different campaigns. 

Number of campaigns and subject lines returned from the MailChimp account

Lastly, we’ll put this entire code in a try function and write a catch function to catch any errors and display them. So, if we fail at any point in fetching the API, we’ll know the exact error. 

Catching and displaying errors with the catch function in the Script editor

You can find the entire code in our GitHub repository. However, it can be overwhelming for some people to work with such a long code.

To make things easier, let’s discuss each component of the code to understand it better.

How the Script Works

We’ve created a blank array to store the campaign data and then used a loop to add data to the array.

// blank array to hold the campaign data for Sheet
var campaignData = [];
// Add the campaign data to the array
for (var i = 0; i < campaigns.length; i++) {
Creating a blank array to hold the campaign data for sheet in the Script editor

We’ll use this array to collect different data that we want to analyze such as title, subject_line, recipient_count, emails_sent, and so on. 

// put the campaign data into a double array for Google Sheets
if (campaigns[i]["emails_sent"] != 0) {
campaignData.push([
i,
campaigns[i]["send_time"].substr(0,10),
campaigns[i]["settings"]["title"],
campaigns[i]["settings"]["subject_line"],
campaigns[i]["recipients"]["recipient_count"],
campaigns[i]["emails_sent"],
(campaigns[i]["report_summary"]) ? campaigns[i]["report_summary"]["unique_opens"] : 0,
(campaigns[i]["report_summary"]) ? campaigns[i]["report_summary"]["subscriber_clicks"] : 0
]);
Fetching campaign data such as title, subject-line, emails_sent from the MailChimp account

Lastly, if we have some campaigns that were never sent (i.e., in the draft phase), we’ll still capture them. We’ll use the Not sent notation to do that.

else {
campaignData.push([
i,
"Not sent",
campaigns[i]["settings"]["title"],
campaigns[i]["settings"]["subject_line"],
campaigns[i]["recipients"]["recipient_count"],
campaigns[i]["emails_sent"],
"N/a",
"N/a"
]);
}
Adding “Not sent” notation in the code to capture campaigns in draft phase

After adding the campaign data to the array, we can display it using the Logger function and save the file. 

// Log the campaignData array
Logger.log(campaignData);
Adding a Logger function to display campaign data

When we run this script, we’ll see an array of data in the Logs. It will include data such as the name of the campaign, the date, the subject line, how many people it was sent to, how many people opened it, etc. for each of the campaigns. 

Campaign Data fetched from MailChimp account as displayed in Logs in the Script editor

Once we know that we’re fetching the correct data from our MailChimp account, we can paste this data into Google Sheets. We’ll add the code below the Logger function.

// select the campaign output sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Campaign Analysis');
// calculate the number of rows and columns needed
var numRows = campaignData.length;
var numCols = campaignData[0].length;
// output the numbers to the sheet
sheet.getRange(4,1,numRows,numCols).setValues(campaignData);

Make sure you’ve entered the correct sheet name in the code. If not, you can rename your Google Sheet.

This code will also check the rows and columns of data we have so we can add all of it to the spreadsheet.

Renaming the Google Sheet 

When you click on Run, Google will again ask you to review some permissions, so just click through and confirm the account that you want to authorize to deploy the script.

And that’s it! After the function runs, you’ll have all your MailChimp data populated in Google Sheets. 

MailChimp data added in Google Sheets

Let’s say that we’re also interested in some of the performance metrics like Open Rate and Click Rate. So let’s add a code to calculate this data. 

Applying Formulas

This code will run a loop over all the rows and calculate Open Rate and Click Rate for each row.

// adds formulas to calculate open rate and click rate
for (var i = 0; i < numRows; i++) {
sheet.getRange(4+i,9).setFormulaR1C1('=iferror(R[0]C[-2]/R[0]C[-3]*100,"N/a")');
sheet.getRange(4+i,10).setFormulaR1C1('=iferror(R[0]C[-2]/R[0]C[-4]*100,"N/a")');
}
Formulas for Open Rate and the Click Rate in the Script editor

Running this script will populate your Google Sheet with the percentage rates for Open Rate and Click Rate.

Open Rate and the Click Rate added to the sheet Campaign Analysis

So that’s how we can add the MailChimp data into Google Sheets with the help of API.

Using MailChimp API Documentation

There is a lot more you can do with the help of the MailChimp API. To understand it better, let’s take a look at its documentation.

The MailChimp API documentation lists different endpoints that you can use. For example, you can automate workflow using this API. 

Lists of the endpoints from the MailChimp API Documentation

Let’s take a quick look at another example of how you can use the MailChimp API to analyze data. 

Example 2: Measuring List Growth

We’ll open a new sheet and rename it. Next, we’ll add some headings in the sheet for the data that we want to analyze. 

Creating a Google Sheet to fetch List Growth data from MailChimp

In the Script editor, you can paste the code from our Github repository and save it.

Pulling Data

Next, we’ll run the script mailchimpListGrowth. 

Selecting the mailchimpListGrowth in the Script editor

This script will add the List Growth Data to our Google Sheet. It includes Month-on-month growth, Month-on-month growth rate, etc.

List Growth Data added in Google Sheets

After fetching this data into Google Sheets, it is important to visualize it for better interpretation. A great way to do that is with charts. 

Creating Charts

The first step towards any data visualization is data cleaning and formatting. You can use the MailChimp API <> Google Sheets template to view your data clearly.

MailChimp Campaign data template for Google Sheets

Once our data is organized, we can easily create charts from it. For example, we can use these columns to create a graphical analysis that shows the performance of campaigns over time. 

A graph like this will show us how the campaigns are doing over a time period. You can also see a blue line that depicts the Average Open Rate, a yellow line that shows the Click Rate, and so on. 

Showing the Campaign Analysis of Open Rates and Click Rates in the chart form

Another example of graphical analysis is from the MailChimp API List Growth Data. This graph shows the monthly variations of the performance of our campaign. 

Showing the Email list monthly growth in the chart form in Google Sheets

Showing the Email list monthly growth in the chart form in Google Sheets

Summary

That’s it! This is how you can use the MailChimp API to pull data directly into your Google Sheets using Google AppScript.

The method is quite useful when you want to analyze your email campaign data from your MailChimp account. You can not only import the data into Google Sheets, but also create meaningful visualizations using the Google Sheets functionalities. 

You can also import data from Google Analytics and analyze the data on Google Sheets. Similarly, you can import data from various other tools into Google Sheets and use some must-know formulas on Google Sheets.

And if you want to take your data visualization to the next level, you can even connect your Sheet to Google Data Studio to make your MailChimp reports even more powerful.

Do you use Google Sheets to analyze your Mailchimp data? Which data parameters do you find most insightful for email campaigns? Let us know in the comments below! 

JOIN US!

Master Data & Analytics with Measuremasters

Exclusive Courses & Workshops | Ongoing Troubleshooting | Support Resources, Tools & much more
guest
0 Comments
Inline Feedbacks
View all comments

Blog Categories

Join 30,000+ marketers getting exclusive resources and our latest content!

now it's time to

Start measuring like a master

Itching to jump into the world of MeasureMasters? This is what you have to look forward to.