By Rudderstack Team

How to load data from MailChimp to Snowflake

This post helps you with loading your data from MailChimp to Snowflake. If you are looking to get analytics-ready data without the manual hassle, you can integrate MailChimp to Snowflake with RudderStack, so you can focus on what matters, getting value out of your business data.

Extract your data from MailChimp

First of all, Mailchimp updated its API to v3 recently, so make sure that anything you do will be with this version as the previous are all deprecated although still supported. For more information, visit MailChimp API v3.0 documentation. MailChimp was always a promoter of APIs and encouraged integration with other systems. It has a rich API that exposes a large number of endpoints for interacting with the resources of the applications. More specifically, there are endpoints for the following resources:

  • Automations – offers functionality related to automated tasks that we define on MailChimp
  • Batch operations – for managing batch processes on our MailChimp account
  • Campaign folders – helps you organize your campaigns into groups
  • Campaigns – for managing your campaigns
  • Conversations – helps you track threads of emails with specific users
  • File manager files – for managing your static assets like images
  • File manager folders – for creating folders to organize your assets
  • Lists CRUD operations on lists of users
  • Reports – for accessing reports with statistics on your campaigns
  • Template folders – operations on creating folders for organizing your templates for your emails
  • Templates – operations on templates for your emails

Something interesting to note about the MailChimp API is that we don’t see a root-level resource for users anywhere. This makes sense of course, if we take into consideration that MailChimp is all about mail campaigns so users or to put in the correct context, subscribers, are not a standalone resource but instead they exist only inside the lists we manage.

It is important to understand that every service perceives the world from a different perspective which of course it’s relevant to the value it offers, so even if what we care about is information about our users, it makes complete sense for someone like MailChimp to organize everything around lists and campaigns.

If we check the model of another service, like Intercom, for example, we’ll notice that the user is at the top resources, which again makes sense because Intercom is all about one to one communication with users.

MailChimp and any other service that you might be using has figured out (hopefully) the optimal model for its operations, but when we fetch data from them, we usually want to answer questions or do things that are not part of the context that these services operate, something that makes these models sub-optimal for your analytics needs.

For this reason, we should always keep in mind that when we work with data coming from external services, we need to remodel it and bring it to the right form for our needs.

Interacting with the MailChimp REST API can be done by using tools like CURL or Postman or by using HTTP clients for your favorite language or framework. A few suggestions:

  • Apache HttpClient for Java
  • Spray-client for Scala
  • Hyper for Rust
  • Ruby rest-client
  • Python http-client

The MailChimp REST API supports OAuth 2.0 authentication, more information can be found in the Authorised apps section of the API documentation. After you successfully authenticate with the REST API, you have to start interacting with its resources and start fetching data from it in order to load them on your warehouse.

Extract your user data from the MailChimp API

So let’s assume that we want to get all the information we got on MailChimp for our users in order to enrich our user records inside our data warehouse. To do that, we need to do the following.

First, we need to fetch all the lists that we have created on MailChimp, we can do this by performing a GET request to the appropriate endpoint.

curl --request GET
--url 'https://usX.api.mailchimp.com/3.0/lists'
--user 'anystring:apikey'
—include

We should get back a response like the following: