Import Mailchimp Data to Google Sheets – Step by Step Guide

Import Mailchimp Data to Google Sheets – Step by Step Guide

Last Updated On May 30, 2019
You are here:
< Back

In this guide, we’ll walk through how to pull data from the Mailchimp API directly into Google Sheets, using the free API Connector add-on. We’ll first get an API key from Mailchimp, and then set up a request to pull in email campaign details from your Mailchimp account to your spreadsheet.

PART 1: GET YOUR MAILCHIMP API KEY

  1. Log into your Mailchimp account and navigate to the API Keys section via this link: https://us1.admin.mailchimp.com/account/api/
  2. Click the Create A Key button
    mailchimp-api-img1
  3. You’ll now see your API key listed in a section titled “Your API keys”
    mailchimp-api-img2
  4. Congrats, that’s it! You now have access to the Mailchimp API, and can start pulling Mailchimp data into Google Sheets.

 

PART 2: CREATE YOUR API REQUEST

We’re going to follow the Mailchimp API documentation to retrieve information about members in an email list.

Mailchimp base URL: https://us{data_center_ID}.api.mailchimp.com/3.0
Example: https://us20.api.mailchimp.com/3.0/

Endpoint: /lists/{list_id}/members
Example: /lists/32d8e317e4/members

Putting it all together, we get the full API Request URL:
https://us20.api.mailchimp.com/3.0/lists/32d8e317e4/members

Of course, you’ll need to substitute in your own values for {data_center} and {list_id}, which you can find as follows:
* Find your {data_center_ID} by checking the URL when you’re logged in to your Mailchimp account
mailchimp-api-img4
* Find your {list_id} by navigating to Settings > Audience Name and Defaults. Once you click this, you’ll see your list ID (which they’ve now begun calling an “Audience ID”).
mailchimp-api-img3

We’re now ready to enter all our values into API Connector to start importing Mailchimp data into Google Sheets.

  1. Open up Google Sheets and click Add-ons > API Connector >  Create New API Request
  2. In the Create Request interface, enter the Request URL we just created
    mailchimp-api-img5
  3. Under Headers, enter a key-value pair like this. Make sure to use your own API key (the API key in this tutorial has already been deleted):
    Authorizationapikey {your API key}

    It should look like this:
    mailchimp-api-img6

  4. Create a new tab. You can call it whatever you like, but here we’ll call it ‘Mailchimp’. While still in that tab, click ‘Set’ to use that tab as your data destination.
  5. Name your request. Again we’ll call it ‘Mailchimp’
  6. Click Run and a moment later you’ll see your Mailchimp data populate the Mailchimp tab in your Google Sheet:
    mailchimp-api-img7
  7. If you want to see other data, like a list of campaigns, you’d just change the endpoint. For example, https://us20.api.mailchimp.com/3.0/campaigns will give you a list of your campaigns. The Mailchimp API Playground is a great resource to test out different API calls.

Following the above steps, you will now be able to import data from the Mailchimp API directly into Google Sheets. By setting Google Sheets as a data source, you can also use this method to create Mailchimp data visualizations and dashboards in Google Data Studio.

Comments:2

    1. The Mailchimp API documentation says:
      Paginate your API requests to limit response results and make them easier to work with. We use offset and count in the URL query string to paginate because it provides greater control over how you view your data.

      Offset defaults to 0, so if you use offset=1, you will miss the first element in the dataset. Count defaults to 10. For example, this URL includes query string parameters for pagination: https://usX.api.mailchimp.com/3.0/campaigns?offset=0&count=10

      What this means is that by default, Mailchimp only provides 10 records at a time. To get more, change the parameters at the end of your URL, e.g.:
      https://us20.api.mailchimp.com/3.0/campaigns?count=50

      Then to get the next 50 results, you’d enter:
      https://us20.api.mailchimp.com/3.0/campaigns?offset=50&count=50

Leave a Reply

Your email address will not be published.