This post helps you with loading your data from MailChimp to MS SQL Server. If you are looking to get analytics-ready data without the manual hassle, you can integrate MailChimp to MS SQL Server with RudderStack, so you can focus on what matters, getting value out of your Subscription data.
Extract your MailChimp’s Data
First of all, Mailchimp updated its API to v3 recently, so ensure 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 stand-alone 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 have figured out (hopefully) the optimal model for its operations. Still, 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 in, 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 in order to load them on your data warehouse.
Extract your user data using 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 should do the following.
First, we must 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: