How to connect mailchimp to google sheets

Get access to 20 Applets, multiple actions, and other advanced features.

Índice Show

How to create a Google Sheets and Mailchimp integration to get campaign data
Google Sheets Mailchimp integration setup
Getting data back from the API
Outputting the data into the Google Sheet
Preparing the data to chart
Creating the campaign chart
See monthly list growth from the Google Sheets and Mailchimp integration

Start 7-day trial

In this post, we’re going to see how to setup a Google Sheets and Mailchimp integration, using Apps Script to access the Mailchimp API.

The end goal is to import campaign and list data into Google Sheets so we can analyze our Mailchimp data and create visualizations, like this one:

Mailchimp is a popular email service provider for small businesses. Google Sheets is popular with small businesses, digital marketers and other online folks. So let’s connect the two to build a Mailchimp data analysis tool in Google Sheets!

Once you have the data from Mailchimp in a Google Sheet, you can do all sorts of customized reporting, thereby saving you time in the long run.

I use Mailchimp myself to manage my own email list and send out campaigns, such as this beginner API guide (Interested?), so I was keen to create this Mailchimp integration so I can include Mailchimp KPI’s and visualizations in my business dashboards.

For this tutorial I collaborated with another data-obsessed marketer, Julian from Measure School, to create a video lesson. High quality video tutorials are hard to create but thankfully Julian is a master, so I hope you enjoy this one:

(Be sure to check out Julian’s YouTube channel for lots more data-driven marketing videos.)

If you’re new to APIs, you may want to check out my starter guide, and if you’re completely new to Apps Script, start here.

Otherwise let’s begin.

How to create a Google Sheets and Mailchimp integration to get campaign data

In the GIF above, you can see how I’ve added a custom menu to my Google Sheet and Mailchimp integration tool to retrieve campaign data. When I click the menu option, it fires the function to call the Mailchimp API, which sends back a packet of data containing my campaign results. I parse this data to extract just the information I want, and then add it to the data table you can see in the background. The chart in the foreground updates automatically to show the latest results.

Google Sheets Mailchimp integration setup

The Google Sheet setup is super simple for this example, since all we need initially is a row of headings like so:

Change the name of this sheet to Campaign Analysis (so it matches line 67 of our code below in the “Outputting the data into the Google Sheet” section).

Next, open up the Script Editor (Tools > Script editor...), clear out the boiler plate code and paste in the following code:

var API_KEY = 'Insert your API key here'; var LIST_ID = 'Insert your List ID here';

Your script editor window should look like this:

We’ll jump over to Mailchimp now and find our API key & email list ID, before we write any of the script.

You create your new API key under your Profile > Extras > API keys, which will look like this:

Click on Create A Key to create an API key and rename it if you wish in the Label column. Now copy this API key value (the long string of letters and numbers) into the API_KEY variable in your Apps Script code, where it says Insert your API key here above.

The other variable we need before we get started is the id of the email list you want to analyze. This can be found under Lists > Settings > List name and defaults as shown here:

As with the API key, copy this list ID into the LIST_ID variable in your code, where it says Insert your List ID here.

Note, for both the API key and the List ID, make sure you leave the quote marks in place in your code, so the two variables are colored red in your script editor.

Getting data back from the API

Add this code to the script editor and change the us11 half-way across line 8 to match the last 4 digits of the API key that you got hold of above (it may well be us11 in which case you can just leave as it. This is the identification of the data centre for your account – read more.):

Note: the first time you run this function you’ll need to grant it permissions – like this example here.

When the script has finished running, open the logs (View > Logs) to see what data it returned:

If your logs show data like this then that’s great news! In this example, I extracted the number of campaigns (line 29) and their subject lines (lines 32 to 34).

You’re receiving data from the Mailchimp API, so the next step is to output that into a Google Sheet to complete the Mailchimp integration.

One other thing to note with this code is the query parameter on line 9:

'campaigns?count=100';

By default, the Mailchimp API returns only 10 results, so I’ve added this query parameter ?count=100 to return up to 100 results (more than enough for my situation). You can modify this value to suit your needs.

Outputting the data into the Google Sheet

Next, update the code to print the results to your spreadsheet:

The full code is here on GitHub.

When you run this again, you’ll be prompted to grant access permission to the Spreadsheet Service this time.

The output in our Google Sheet Mailchimp integration looks something like this:

You may notice the words “undefined” in the campaign subject line. These were campaigns where I A/B-tested the headlines, so it’d require a little more digging into the API data to return these ones. However, since I didn’t need these subject lines to create my chart in this example, I was happy to leave them as they are.

Preparing the data to chart

You may notice that some rows in your dataset have no data, likely because they were draft or deleted campaigns, so we don’t really want to include them in our chart. Also, we only need the date, the open rate and the click-through rate for our chart, so we can make our life easier by creating a staging table with just those metrics.

So, in cell M3, next to the data table, add the following formula which will extract these columns:

=QUERY($B$3:$J,

"select B, avg(I), avg(J) where F > 0 group by B

label avg(I) 'Open Rate', avg(J) 'Click Rate'"

,
1)

You may observe that the inner workings of this formula look a lot like SQL (Structured Query Language, how we talk to databases). Well, you’re right, it’s Google’s Visualization API Query Language, which uses a SQL-like syntax to manipulate data.

For a comprehensive introduction to the QUERY function and its SQL-esque code, check out this post.

I’m using a where filter to remove any rows of data that did not send any emails, and I’m using the group by to take an average open/click rate on days where I sent multiple campaigns.

The nice thing about this QUERY formula is that it will always include all of our Mailchimp data, even when our script fetches new data. Therefore our chart will always be up-to-date.

I’ve used array formulas to add an average open rate and average click rate in the next two columns, so I can add these to my chart. Again, the array formula is perfectly suited here because it will create new formulas whenever any new Mailchimp data is pulled in.

The formula in cell P4 for open rate is:

=ArrayFormula(IF(ISBLANK(N7:N),"",AVERAGE(N7:N)))

And the formula in cell Q4 for the click rate is:

=ArrayFormula(IF(ISBLANK(O7:O),"",AVERAGE(O7:O)))

This gives us a chart data table as follows:

Creating the campaign chart

This is the easiest step of all. Highlight your data table and Insert > Chart...

You can format it however you wish. I went for the smooth lines:

Here’s a link to the Google Sheet template. Feel free to make your own copy.

See monthly list growth from the Google Sheets and Mailchimp integration

Again, we start with a super simple Google Sheet (add a new tab next to your campaign analysis tab in your Mailchimp integration sheet):

Make sure your sheet is named List Growth (so it matches our code on line 127).

Underneath the existing code, add the following function into your script editor:

// call the Mailchimip API to get monthly list growth function mailchimpListGrowth() { // URL and params for the Mailchimp API var root = 'https://us11.api.mailchimp.com/3.0/'; var endpoint = 'lists/' + LIST_ID + '/growth-history?count=100'; var params = { 'method': 'GET', 'muteHttpExceptions': true, 'headers': { 'Authorization': 'apikey ' + API_KEY } }; try { // call the Mailchimp API var response = UrlFetchApp.fetch(root+endpoint, params); var data = response.getContentText(); var json = JSON.parse(data); // get just list history data var listGrowth = json['history']; // blank array to hold the list growth data for Sheet var monthlyGrowth = []; // Add the list growth data to the array listGrowth.forEach(function(el) { monthlyGrowth.push([el.month, el.existing, el.optins, el.imports]); }); // Log the monthlyGrowth array Logger.log(monthlyGrowth); // select the list growth output sheet var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('List Growth'); // calculate the number of rows and columns needed var numRows = monthlyGrowth.length; var numCols = monthlyGrowth[0].length; // output the numbers to the sheet sheet.getRange(4,1,numRows,numCols).setValues(monthlyGrowth.reverse()); // adds formulas for absolute and relative growth for (var i = 0; i < numRows; i++) { sheet.getRange(4+i,5).setFormulaR1C1('=iferror(R[0]C[-3] - R[-1]C[-3],0)'); // absolute monthly change in list sheet.getRange(4+i,6).setFormulaR1C1('=iferror((R[0]C[-4] - R[-1]C[-4])/R[-1]C[-4],0)').setNumberFormat("0.00%"); // rate of change in list } } catch (error) { // deal with any errors Logger.log(error); }; }

The full code is here on GitHub.

Run this new function and you should find list growth data populating your Google Sheet:

Lastly, you can create a pretty chart to illustrate this list growth over time:

Here’s a link to the Google Sheet template. Feel free to make your own copy.

Resources

Getting started with the Mailchimp API official guide

Mailchimp API reference guide

Google Apps Script official documentation on the UrlFetchApp Class

A new post from Eivind Savio, showing another Apps Script example for pulling Mailchimp data into a Google Sheet, with a screenshot of the final Data Studio Mailchimp dashboard! 🙂

That’s all folks! I hope you enjoyed this tutorial. As always, feel free to leave a comment and share this with anyone you know who might be interested.