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

Last Modified on March 25, 2024

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:

• Analyzing data from various tools
• Setting up your Google Sheets columns
• Example 1: Pulling campaign metrics
• Using MailChimp API documentation
• Example 2: Measuring list growth

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.

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.

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 Tools → Script editor. 

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 = ' '; 

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.

Scroll down and click on Create A Key.

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

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. 

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

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

On your List, go to Settings → List name and defaults. 

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

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

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.

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. 

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

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

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.

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.  

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. 

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

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

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

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. 

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++) {

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
]);

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"
]);
}

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

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. 

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.

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. 

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")');
}

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

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. 

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. 

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. 

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

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.

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. 

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

FAQ

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!