Back to Blog

Mailchimp to Google Sheets: How to Connect Mailchimp API Without Coding

Every Mailchimp user knows how they can export their data. It’s a plain process that includes a few button clicks and download of a ZIP file to your device. However, this manual option can be quite burdensome if you need to export your data recurrently. The alternative solution is to connect to Mailchimp API and get your data from it. It’s doable without coding if you use the right tool. Moreover, you can get your data directly to Google Sheets and automate the export on a schedule. Read on to learn how you can do this.

Why you may need to integrate Mailchimp and Google Sheets

Here are the top reasons why you may need to sync Mailchimp with Google Sheets:

  • Reports – build custom reports that can be easily shared with stakeholders
  • Templates – create templates based on the Mailchimp data
  • Dashboards – visualize your Mailchimp records in the form of a dashboard in Google Sheets or more advanced tools, such as Data Studio or Tableau
  • Backup copy – store your data outside of Mailchimp to have an easily accessible backup copy 

Whichever reason you have, exporting data from Mailchimp manually is a no-go. We do advise you to set up a Mailchimp to Google Sheets integration and benefit from automatic data exports.

How to connect Mailchimp to Google Sheets

You’ll need to complete two steps:

  • Get a Mailchimp API key
  • Integrate Mailchimp with Google Sheets

Get a Mailchimp API key to sync Mailchimp with Google Sheets

You’ll need the API key for authorizing your requests to the Mailchimp API. To get the key:

  • Click your profile icon and choose Account.
  • Go to the Extras menu and select API keys.
  • Click “Create A Key” => you’ll see your newly created key on the list.

That’s it. Now we can move on to the next step.

Set up a Mailchimp integration with Google Sheets

To connect Mailchimp to Google Sheets without coding, we’ll use Coupler.io. It’s a web app that allows you to integrate your data source, be it an app or an online published file with a Google Sheets, Excel, or BigQuery. Coupler.io provides a number of ready-to-use sources, such as Airtable, Clockify, and Hubspot. At the same time, you can connect to the JSON API of the app, which is not on this list yet. This is what we’re going to do to sync Mailchimp API with Google Sheets.

Sign up to Coupler.io, click “Add new importer“, name it whatever you want, and take the following steps:

Set up source (JSON)

  • Select JSON as the source application.
  • Specify the following JSON URL:
https://{server-prefix}.api.mailchimp.com/3.0/

You can learn your {server-prefix} from the beginning of your Mailchimp account web address. 

Note: To export different data from Mailchimp, you’ll need to use specific endpoints added to the basic URL, as follows:

https://{server-prefix}.api.mailchimp.com/3.0/{endpoint}

We’ll focus on this in the block Use case of the Mailchimp Google Sheets integration.

  • Add the following authorization string with your API key to the HTTP headers field:
Authorization: Basic {your-api-key}

Now you can jump to the destination settings.

Set up destination (Google Sheets)

  • Choose Google Sheets as the destination app.
  • Connect your Google account.
  • Pick a Google Sheets file on your Google Drive and select a sheet to sync with Mailchimp, or you can create a new sheet by entering a new name.

That’s it. You can also benefit from optional parameters, such as Cell address and Import mode. Read more about it in the Coupler.io knowledge base. Now you can click “Save and Run” and check out the data in your spreadsheet.

We’ve got many columns, but columns Y and Z are the most important since they contain the endpoints and JSON URLs for specific Mailchimp records.

For example, to get a list of Mailchimp campaigns, you need to use the following JSON URL:

https://us1.api.mailchimp.com/3.0/campaigns

We’ll check those soon. But first, let’s discover how you can automate getting data from Mailchimp to Google Sheets on a schedule.

Mailchimp automation with Google Sheets

You have probably noticed the Schedule section when setting up source and destination.

It allows you to schedule data refresh every hour, every day, etc. For this, you need to toggle the Automatic data refresh and configure the schedule you want.

Mailchimp data you can manage in Google Sheets

Take a look at a short introduction to the data entities you can export from Mailchimp to Google Sheets.

Data entity Endpoint for the JSON URL
List of campaigns + summary .../campaigns
Information about a specific campaign.../campaigns/{campaign_id}
List of reports + summary.../reports
Report of a specific campaign.../reports/{campaign_id}
List of Facebook ads reports + summary.../reporting/facebook-ads
Report of a Facebook ad..../reporting/facebook-ads/{outreach_id}
Breakdown of product activity for an outreach
.../reporting/facebook-ads/{outreach_id}/ecommerce-product-activity
Information about all lists in the account.../lists
Information about a specific list.../lists/{list_id}
List of conversations for the account + summary.../conversations
Information about a specific conversation.../conversations/{conversation_id}
Summary of classic automations for the account.../automations
List of available templates for the account..../templates
Information about orders for the account.../ecommerce/orders
Information about all stores in the account.../ecommerce/stores
Information about customers of a specific store
.../ecommerce/stores/{store_id}/customers

For more information about the data you can retrieve from the Mailchimp API, read the Mailchimp Marketing API documentation

Mailchimp delete rows in Google Sheets

For some data entities, you can apply query parameters to filter the rows to be returned from Mailchimp. For example, the count parameter allows you to increase the number of imported rows up to 1,000 whereas the default value is 10. But you can also benefit from other parameters: before_create_time, since_send_time, status, etc.

Attach the query parameters to the JSON URL as follows:

https://{server-prefix}.api.mailchimp.com/3.0/{endpoint}?{parameter}={value}

If you want to use multiple parameters, separate them with ampersand (&) like this:

https://{server-prefix}.api.mailchimp.com/3.0/{endpoint}?{parameter1}={value1}&{parameter2}={value2}

Here is an example of what this may look like in Coupler.io:

In the use case below, we’ll cover the details.

Mailchimp delete columns in Google Sheets

Most Mailchimp data entities provide the exclude_fields and fields query parameters to filter the columns you want to export. In addition, Coupler.io provides a separate block called Fields to specify which columns you want to export. 

Now let’s check out the Mailchimp Google Sheets integration in action.

Use cases of the Mailchimp Google Sheets integration

Mailchimp reports to Google Sheets

To get reports from Mailchimp, we need the following source parameters:

JSON URL:

https://{server-prefix}.api.mailchimp.com/3.0/reports

HTTP headers:

Authorization: Basic {your-api-key}

Optionally, you can use the following query parameters to be attached to the JSON URL:

  • fields – specify the fields to import. Separate them with commas.
  • exclude_fields – specify the fields to exclude. Separate them with commas.
  • count – specify the number of records to return up to 1,000. The default value is 10. 
  • type – specify the campaign type: regular, plaintext, absplit, rss, or variate.
  • before_send_time/since_send_time – filter the reports to return based on the specified time in the format 2021-11-22T13:21:46+00:00 

Here is an example of how the JSON URL may look in Coupler.io:

Click “Save and Run” and welcome information about Mailchimp reports in Google Sheets.

Note: feel free to use our template based on the preset JSON importers to import a list of reports and information about a specific report. 

Automate Mailchimp list export to Google Sheets

The list in Mailchimp is also known as your audience. You can easily automate the retrieval of this data to Google Sheets. To do this, set up an importer using the following parameters and toggle on the Automatic data refresh. 

JSON URL:

https://{server-prefix}.api.mailchimp.com/3.0/lists/{list-id}

HTTP headers:

Authorization: Basic {your-api-key}

Optionally, you can use the following query parameters to be attached to the JSON URL:

  • fields – specify the fields to import. Separate them with commas
  • exclude_fields – specify the fields to exclude. Separate them with commas
  • include_total_contacts – specify TRUE to return the total_contacts field, which contains an approximate count of all contacts in any state.

Here is an example of how the JSON URL may look in Coupler.io:

If you were looking for how to export other lists from Mailchimp, such as lists of campaigns, reports, stores, orders, templates, and many more, you can also do this with Coupler.io.

Get Mailchimp store subscribers in Google Sheets

You can get information about subscribers in a specific Mailchimp list. For this, use the following parameters:

JSON URL:

https://{server-prefix}.api.mailchimp.com/3.0/lists/{list-id}/members

HTTP headers:

Authorization: Basic {your-api-key}

Optionally, you can use many query parameters to filter the return. For example, here is how you can filter the subscribers by status “pending” and the time when their information changed:

How to connect Mailchimp metrics to Google Sheets

With Coupler.io, you can export a bunch of Mailchimp reports, including email activity, domain performance, click reports, etc. You can find the endpoints to be used in the JSON URL here

If those are not enough, you can export other raw data from Mailchimp and calculate the eCommerce metrics you need based on this data. With Coupler.io, you are able to connect Mailchimp to Google Sheets and get your raw data on a schedule. Link this data to your calculation formulas and you’ll have the up-to-date figures.

Is it better to use Excel instead of Google Sheets with Mailchimp?

Coupler.io lets you sync Mailchimp with each of these destinations:

  • Google Sheets file on Google Drive
  • Excel workbook on OneDrive

To make the best choice, define the goals of your data manipulation. If you are going to perform complex calculations with tons of raw data, Excel may be a good option. At the same time, Google Sheets is a perfect solution for dealing with medium-size datasets, and it’s a good fit for reporting purposes. 

If you are in doubt which spreadsheet app is best for you to manage Mailchimp data, check out our comparison blog post: Google Sheets vs. Excel

Bonus: Getting Mailchimp data into Google Sheets in a native way

It’s time to wrap up our journey, and we decided to do this by showing how it works to get data from Mailchimp in the regular way.

  • Click your profile icon and choose Account.
  • Go to the Settings tab and select Manage my data.
  • Choose the data entity to export, as well as the period to cover. Click “Export Data“.
  • Wait till your data is packaged and a downloadable link appears. It may take a few minutes or up to 24 hours depending on the data volume. After that, you’ll be able to download a ZIP file that contains the CSV file(s).

Then you can open those files in Excel or upload them to Google Sheets. You’ll have to repeat this flow every time you want to update your Mailchimp data repository in spreadsheets. Is it worth your time if you can automate this with Coupler.io? Have a think about it, and good luck with your data!

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free