Import Mailchimp Data To Google Sheets

Import Mailchimp data to Google Sheets
Reading Time: 5 minutes

Mailchimp is one of the most useful tools for an effective email marketing campaign and for managing clients and customers. We can broaden its utilities using its API which we can use to import Mailchimp data to Google Sheets. And then we can analyse and monitor the campaigns more effectively and gain better insights which then brings better results.

That’s what we’ll learn in this article. Following are the steps to import Mailchimp data to Google Sheets.

  1. Install Amigo Data extension in Google Sheets
  2. Create an API key
  3. Select a data endpoint
  4. Import the data to Google Sheets

How to import Mailchimp data to Google Sheets

Step 1: Install the Amigo Data extension

We’ll use the Amigo Data extension in Google Sheets to import Mailchimp data to Google Sheets. You can get the extension from the Google Workspace Marketplace by clicking the link provided below.

Amigo Data in Google Workspace Marketplace

Click here to install.

Alternatively, you can get it from Google Sheets.

  • Open a new sheet
  • Go to Extensions→Add-ons→Get add-ons
  • Search for Amigo Data and install

Step 2: Create an API key

Log in to your Mailchimp account and navigate to the account section.

Navigating to the account section in Maichimp

Click the Extras menu and select API keys.

Maichimp profile section

Click on the Create A Key button.

Creating a key

Upon clicking the button, an API key will be generated. Note the key and keep it safe and secret.

Mailchimp API Key

Now that we’ve got the key, we can move to the next step, which is selecting the type of data to import.

Step 3: Select the endpoint of the data to import

Navigate to the Marketing API page. You can also go there by clicking Read The API Documentation and clicking the relevant link.

On the left of the page under API reference is a list of available endpoints. Click on the one you want to retrieve the information, eg, Lists/Audiences for data related to your contacts.

Endpoints list

On clicking the API heading, all available endpoints within that will be listed below. We can click on any specific endpoint to get the URL and the query parameters.

Available endpoints under Lists/Audiences category

The endpoint URL as well as the headers are shown on the right side of the page under Code samples.

The endpoint URL and headers

This sample URL will return all the surveys associated with your account.

Click on the endpoint of your choice to expand it, say, Get list info for information about all your Mailchimp lists or audience.

Expanding the desired endpoint

On expanding the endpoint, we’ll see the generic endpoint URL as well as the available query parameters.

Endpoint URL and query parameters

The default URL appends all the parameters without their values. We can fill them with corresponding values mentioned under Query Parameters.

However, we need only copy the URL up to lists and replace ${dc} in the URL with the data centre for our account and omit the query parameters; this will return all values unfiltered.

Copy this URL. This will be used to fetch the data.

Step 4: Import the data to Google Sheets

Open Google Sheets and launch Amigo Data from the sheet into which you want to fill the data. To connect to Amigo Data, click Extensions→Amigo Data→Connect.

Launch Amigo Data in Google Sheets

Then select Custom API from the list of sources.

List of sources to connect to with Amigo Data

Paste the endpoint URL in the API URL field.

Entering the endpoint in Amigo Data

Then expand the Details tab and enter the following as a header:

Name: Authorization, Value: Bearer YOUR_API_KEY

The header names and values

Click the PREVIEW button. Flatten and expand the nested datasets and delete the columns that are not required. Then click IMPORT.

Preview of the data to be imported to Google Sheets

The data will be imported into Google Sheets in a few moments.

The imported data

If you want to filter the results and/or get more information, you can specify the parameters in the URL (as mentioned earlier) or in Amigo Data.

In the Details tab, add the query parameters by clicking the “+QUERY” button. Eg, to increase the count limit, which by default is 10, we’d specify it as under.

Key: count, value: 1000 (This will return 1000 values.)

Likewise, we can add any number of query parameters.

If you want to or need to have these data updated automatically, you can do so by setting a refresh schedule in Amigo Data, and the data will be fetched automatically. Click on this link to learn how to set a refresh schedule and receive the same in your inbox.

Some recommended articles:
Import Eventbrite Data to Google Sheets

Import SurveyMonkey Data To Google Sheets

Import Holidays List From Holiday API To Google Sheets