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 integrate Mailchimp and Google Sheets and import Mailchimp data to Google Sheets. Then we can analyse and monitor the campaigns more effectively and gain better insights which then bring better results.
A sample of Mailchimp data imported into Google Sheets.
So let us learn how to use Mailchimp API to integrate Mailchimp with Google Sheets. And then we can sync Mailchimp and Google Sheets. We’ll do this using a data integration and automation tool called Amigo.
The following are the steps to import Mailchimp data to Google Sheets.
- Install Amigo Data extension in Google Sheets
- Create an API key
- Select a data endpoint
- Import the data to Google Sheets
How to import Mailchimp data to Google Sheets
Follow the steps below to create a Mailchimp Google Sheets integration and import Mailchimp data to Google Sheets.
Step 1: Install the Amigo Data extension
Install Amigo to create a Mailchimp-Google Sheets integration and pull Mailchimp data to Google Sheets.
You can get the extension from the Google Workspace Marketplace by clicking the link provided below.
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 a Mailchimp API key
Log in to your Mailchimp account and navigate to the account section.
Click the Extras menu and select API keys.
Click on the Create A Key button to generate your Mailchimp API key.
Upon clicking the button, an API key will be generated. Note the key and keep it safe and secret. This key will be used to authenticate API requests to Mailchimp API.
Now that we’ve got the Mailchimp API key, we can move to the next step, which is getting the Mailchimp API endpoint URL to import Mailchimp data to Google Sheets.
Step 3: Get the Mailchimp API endpoint URL
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.
On clicking the API heading, all available endpoints within that will be listed below. We can click on any specific endpoint to get the Mailchimp API URL and the query parameters.
Click on the Mailchimp API endpoint of your choice to expand it, say, Get list info for information about all your Mailchimp lists or audience.
On expanding the Mailchimp API endpoint, we’ll see the generic Mailchimp API URL as well as the available query parameters on the left.
The default Mailchimp API endpoint URL appends all the parameters without their values. We can fill them with corresponding values mentioned under Query Parameters.
For the endpoint we’ve selected, the Mailchimp API URL is as given under.
https://${dc}.api.mailchimp.com/3.0/lists?fields=<SOME_ARRAY_VALUE>&exclude_fields=<SOME_ARRAY_VALUE>&count=10&offset=0&before_date_created=<SOME_STRING_VALUE>&since_date_created=<SOME_STRING_VALUE>&before_campaign_last_sent=<SOME_STRING_VALUE>&since_campaign_last_sent=<SOME_STRING_VALUE>&email=<SOME_STRING_VALUE>&sort_field=<SOME_STRING_VALUE>&sort_dir=<SOME_STRING_VALUE>&has_ecommerce_store=<SOME_BOOLEAN_VALUE>&include_total_contacts=<SOME_BOOLEAN_VALUE>The ${dc} is the data centre ID of your Mailchimp account. This value is mandatory. The query parameters are not compulsory but they can be used to filter the results. What they are and what values they take are given on the left.
We’ll remove the filters, ie remove the query parameters and enter the data centre ID in the Mailchimp API URL to fetch all data unfiltered.
The data centre ID can be found in the URL of our dashboard. For example: If the URL for my account is https://us21.admin.mailchimp.com/ then us21 would be my data centre ID. Enter that value in the URL.
So the modified Mailchimp API URL would be as follow.
https://us21.api.mailchimp.com/3.0/listsThis is the Mailchimp API URL we’ll use to import Mailchimp data to Google Sheets.
Step 4: Import the Mailchimp data to Google Sheets
Open Google Sheets and launch Amigo Data to connect Mailchimp to Google Sheets. To launch Amigo Data, click Extensions→Amigo Data→Connect.
Then select Custom API from the list of sources.
Paste the Mailchimp API endpoint URL in the API URL field.
Then expand the Details tab and enter the following as a header:
Name: Authorization, Value: Bearer YOUR_API_KEY
Click the PREVIEW button. Flatten and expand the nested datasets and delete the columns that are not required. Then click IMPORT to pull data from Mailchimp to Google Sheets.
The Mailchimp data will be imported into Google Sheets in a few moments.
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. Learn how to set a refresh schedule and set alerts so that you and your team are alerted whenever changes occur.
Some commonly asked questions
How can I set up automatic updates of my Mailchimp data in Google Sheets?
To set automatic updates of Mailchimp data imported into Google Sheets, set a refresh schedule specifying the time and period using the “Refresh Schedule” option in Amigo. The Mailchimp campaign data and other data that you’ve imported will be updated automatically.
How can I import my Mailchimp subscriber list into a Google Sheets spreadsheet?
To import a Mailchimp subscriber list into Google Sheets, go to the Marketing API>Lists/Audiences>Members>List members info. Then specify your Mailchimp data centre ID and the ID of the list you want to import. Then use Amigo to import the Mailchimp subscriber list to Google Sheets.
What data from Mailchimp can I import into Google Sheets?
You can import all sorts of data from Mailchimp into Google Sheets such as subscribers list, audiences, campaigns data, account data, surveys, and customer journeys among others. For other types of data that you can import to Google Sheets from Mailchimp, visit the Mailchimp Marketing API documentation.
Can I use Google Sheets add-ons or scripts to import Mailchimp data?
You can use add-ons as well as scripts to import Mailchimp data to Google Sheets. However, using scripts to retrieve Mailchimp data is a tedious task and, moreover, it’s inefficient and error-prone. A better solution is to use add-ons such as Amigo that you can use to easily integrate Mailchimp with Google Sheets without any coding and pull data effortlessly.
Some recommended articles:
Import Eventbrite Data to Google Sheets