Search
Close this search box.

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. 

GA4 For Beginners

Subscribe & Get our FREE GA4 Course for Beginners

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