Google Sheets and Mailchimp integration using Apps Script

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:

Google Sheets and Mailchimp integration for campaign data analysis

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

Analyze Mailchimp campaign data in Google Sheets

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:

Google Sheet Setup

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:

Your script editor window should look like this:

Apps script editor setup for Mailchimp integration

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:

mailchimp api keys

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:

Mailchimp list ID

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:

Data from Google Sheets Mailchimp integration

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:

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:

Google Sheets and Mailchimp integration showing campaign data

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:

Campaign Chart Data table

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:

Google Sheets and Mailchimp integration for campaign data analysis

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

Mailchimp list growth analysis

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:

The full code is here on GitHub.

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

List growth data from Mailchimp API

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

Mailchimp list growth data from API

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.

12 thoughts on “Google Sheets and Mailchimp integration using Apps Script”

  1. Thanks for sharing.

    I “hacked together” my own script yesterday to extract campaign data, and I chose a different approach, which means that I don’t get undefined if the newsletter was an A/B-test.

    From what I can see you are getting your data by returning “campaign” (http://developer.mailchimp.com/documentation/mailchimp/reference/campaigns/), but if you instead returns “reports” (http://developer.mailchimp.com/documentation/mailchimp/reference/reports/), you get more campaign data.

    I was thinking about digging into “List Growth” today, but you saved me from that. 🙂

    1. Great suggestion, thanks for commenting! Does it return the winning subject line in an A/B test then?

      The API is extensive so there’s loads of good information in there to explore. It’d be an interesting project to build a Mailchimp dashboard that auto-updated with all the latest data… some day!

  2. I didn’t include A/B variants in my data (but you can I see from the API). This means that in my report A and B are grouped together, and are reported as 1 campaign with 1 subject line (when the A/B-test was subject line).

    If the A/B-test was design, then it’s reported as 2 campaigns, but subject and campaign name is the same.

    If you are able to build that type of dashboard, you deserve a medal. 🙂

  3. Great tutorial! But when I copy the code and run it, I get the error ‘unexpected token’. What is the problem of that error?

    1. Did it have anything else in the error message? Difficult to know what caused that, but it often means a syntax error so worth checking all your code is correct and you have no typos (surprisingly hard to spot!). Also, if you copied the code from my blog post direct into your script editor check that no characters have come out funny from the copying… Hope that helps!

      Cheers,Ben

  4. This is great, but when doing nothing more than pasting in your code and changing the API key and list ID, I get an error when trying to run the List Growth function (the campaign functions works as expected). I get this:

    TypeError: Cannot call method “forEach” of undefined.

    Sorry to get all “support desk” on you, but I’d love to get this working. Thank you so much for the amazing resource!

    1. Hey Reid,

      🤔 It means that the listGrowth variable is undefined. Check that you’ve got the correct root for the list growth:

      var root = 'https://us11.api.mailchimp.com/3.0/';

      In this root variable, make sure that you swap the us11 for the last 4 digits of your api key (see here for more). And check for the correct endpoint:

      var endpoint = 'lists/' + LIST_ID + '/growth-history?count=100';

      Next, try adding two Logger lines just after the JSON.parse line and the listGrowth line, as follows:

      var json = JSON.parse(data);
      Logger.log(json);

      // get just list history data
      var listGrowth = json['history'];
      Logger.log(listGrowth);

      Hopefully this will illustrate where the issue is, and you can see where the data is not being returned correctly or not being parsed correctly.

      Let me know how you go.

      Cheers,
      Ben

      1. AH. Yes, that did it. I had changed the root variable in the first function, but not in the second one. Sorry for the bother, and thanks for your help! Now to spread this article wide and far (well, as wide and far as I can).

      2. To avoid this issue I think you can create a generic code that will handle dynamic “root”.

        Something like this:
        var API_KEY = 'XXXX-us5'; // MailChimp API Key
        var LIST_ID = ''; // MailChimp List ID
        var dc = API_KEY.split('-')[1];

        // URL and params for the Mailchimp API
        var root = 'https://'+dc+'.api.mailchimp.com/3.0/';

        Found this solution from some old MC 2.0 API code.

Leave a Reply

Your email address will not be published. Required fields are marked *