How to Get Member Data from Mailchimp

Mailchimp is an email marketing tool that people either love or hate. A big reason that people dislike Mailchimp is that it makes seemingly simple things much more difficult than they need to be.

One of these things that should be simple, is getting data about all of your Mailchimp audience members. But thankfully, since Mailchimp has a comprehensive API, there are lots of ways to get around the limitations in their default interface.

In this article, we will look at three quick ways that you can get and analyze member data from Mailchimp. These are:

• The default way - exporting a CSV with audience members from Mailchimp
• The manual way - importing your Mailchimp audience members into Google Sheets
• The easy way - using a no-code connector to link all of your Mailchimp data to Google Looker Studio

Before we get into the details, let’s think about some of the reasons you might want to get all of your member data from Mailchimp. One reason is you might want to know how engaged your members are depending on their country or what lists they are subscribed to. This could then help you tailor emails better to those folks or try to make changes to engage others.

Let's see how we can do an analysis like that with each of the 3 ways we can get our member data.

Exporting a CSV of Member Data from Mailchimp

The first option is to go into our Mailchimp dashboard and manually export our members in a CSV file. As we will see, this is not the ideal way and it has several limitations. But first, let’s see how to do it.

We will go to the Audience section of our Mailchimp dashboard and click on All Contacts.

Then from there, we will see a list of our contacts where we can press Export Audience.

This creates a CSV file that we can then download by clicking Export as CSV.

We can then open up that CSV in either Excel or Google Sheets. Once we do that, we will see a spreadsheet with data for the following fields:

• Email Address
• First Name
• Last Name
• Address
• Phone Number
• Zip
• MEMBER_RATING
• OPTIN_TIME
• OPTIN_IP
• CONFIRM_TIME
• CONFIRM_IP
• LATITUDE
• LONGITUDE
• GMTOFF
• DSTOFF
• TIMEZONE
• CC (country)
• REGION
• LAST_CHANGED
• LEID
• EUID
• NOTES
• TAGS

To sum these up, we have basic contact details and merge fields. Then we have geographic information related to the audience member's IP address. We also have some timestamps of when the user signed up and changed last, as well as some internal Mailchimp ID's and any tags we've assigned. Finally, we have Mailchimp Rating which is a 1-5 ranking of how engaged an audience member is according to Mailchimp.

The main issue that we have now is that we are not able to choose which fields are included in this file. We do have Member Rating which is useful, but we don’t have additional fields that could be useful such as Avg. Open Rate or Avg. Click Rate. We do have a country field (CC), but we don’t have any idea what lists our members are signed up to.

So with this very manual approach, we could at least do a minimal analysis of Member Rating by Country, but not much more beyond that.

Importing Mailchimp Members to Google Sheets

The next approach we will take is slightly more sophisticated but still spreadsheet based. Instead of manually exporting our members to a CSV file, we are going to import our Mailchimp audience automatically into Google Sheets using the Mailchimp API.

We have written a guide previously, about how you can integrate Mailchimp with Google Sheets. In that guide, we built a Google Sheets template that could fetch Campaign data from the Mailchimp API. With a little bit of modification, we can use the same Google Sheet script to fetch Mailchimp member data.

Essentially, we can use the same script, but with a different API endpoint. So we will change:

https://[SERVER-LOCATION].api.mailchimp.com/3.0/reports

to:

https://[SERVER-LOCATION].api.mailchimp.com/3.0/lists/[LIST ID]/members.

One of the major limitations with this approach is that Mailchimp limits you to 1,000 members at a time that you can pull data for. You could alter the script to cycle through multiple API requests to pull down data for all of your members. We won’t get into that here, as it’s a bit more complicated, but it is possible, although it will become slow and cumbersome with larger lists.

The plus side of this approach is that you have full customization of which fields you are using. So you can pick only the fields you want, like Avg. Click Rate, Avg. Open Rate and Country. (you can see all member fields available in the Mailchimp API docs) The downside is that you are pulling down a lot of data to do your summary calculations. Also, you are still not able to see engagement by list.

This is because Interest Groups (how Mailchimp refers to multiple newsletters under the same audience) are not easily parsed through the API. We need something that is more advanced for that.

Pulling Mailchimp Members into Google Looker Studio with a Connector

Finally, the most simple way of all is also the most powerful. Using a no-code connector like our Mailchimp Connector for Looker Studio, you can pull all of your member data into Google Looker Studio and easily conduct your analysis there.

We won’t talk about configuration for the connector, since we already have a guide for that, but it takes about 5 minutes. After that is done, you can simply select the Members data view and you will see all of your audience members available in Looker Studio.

Then you can simply create a table by country and see the Avg. Open Rate, Avg. Click Rate or Mailchimp Membership Rating for each one using this configuration:

That looks like this:

You can even see what individual newsletters (interest groups) drive the most engagement simply by swapping the Country dimension for Interest Groups and keeping the same engagement metrics.

Although it requires a custom connector, this option gives you a lot of options for analysis and saves you lots of time editing spreadsheets. Not only that, but your Looker Studio data will be updated in real-time, while your spreadsheet data will soon become out of date.

Conclusion

We hope you enjoyed this quick rundown of the 3 different ways you can get audience member data from Mailchimp. We saw how you can do things manually with a CSV, semi-automated with Google sheets, and fully automated in real-time with our Mailchimp Looker Studio Connector. If you want to try our connector, it has a 14 day, no credit card free trial so you can explore the possibilities with your own Mailchimp member data.