Mailchimp to Redshift: 2 Easy Methods

on Tutorials, Email Analytics, Mailchimp, Marketing, Marketing Analytics • September 13th, 2021 • Write for Hevo

While Mailchimp does provide its own native tools for data analysis and visualization, migrating marketing campaign data from Mailchimp to Redshift opens up several new possibilities: You can combine data from other marketing data sources and obtain meaningful insights to optimize your email marketing efforts and have complete control over your email marketing data.

In this article, we will briefly discuss Mailchimp and Redshift. We will discuss in-depth two methods that you can use to move your data from Mailchimp to Redshift.

Table of Contents

Understanding Mailchimp

Mailchimp logo
Image Source: amoCRM

Mailchimp is a marketing automation platform that is particularly known for its email marketing service. It is currently one of the most popular online marketing tools, sending out over 10 billion emails each month. Mailchimp allows its users to initiate, execute and manage their online marketing campaigns from a central dashboard. Users are provided with an array of tools to carry out their marketing campaigns from assistance in crafting emails and newsletters to help designing landing pages and scheduling automation.

Understanding Redshift

Redshift logo
Image Source: NightingaleHQ

Redshift is an analytics and data warehousing service developed by Amazon. It’s a part of the Amazon Web Services (AWS) platform. The very same platform that Amazon.com itself is built upon. Redshift was developed to house a wide scale of data volumes in order to cater to a wide range of data warehousing needs. The service is also built on a relational database paradigm, giving users the ability to run SQL-like queries and searches on vast troves of data with quick turnaround times on results. Redshift has also been engineered to integrate with a number of other applications via different methods to facilitate data migrations from their native systems in order to take advantage of Redshift’s abilities.

Methods to Move data from Mailchimp to Redshift

Your data can be moved in the following ways:

Method 1: Manually Load the Data from Mailchimp to Redshift Using the Mailchimp API

This method will require the services of at least one member of your team who is proficient in developing code and has experience in working with APIs. This can be time and effort-intensive.

Method 2: Using Hevo to Load Data from Mailchimp to Redshift

Hevo can help you export data from Mailchimp to Redshift in real-time free of cost, without having to write a single line of code.

Get Started with Hevo for Free

With the help of Hevo’s pre-built integrations with 100+ sources such as Mailchimp, you can set up the whole data pipeline without any technical intervention and load data to Snowflake or a destination of your choice with ease.

Get started with Hevo today! Explore a 14-day free trial here!

Methods to Load Data from Mailchimp to Redshift

Method 1: Manually Load the Data from Mailchimp to Redshift Using the Mailchimp API

Steps in Migrating Data:

In order to manually migrate data from Mailchimp to Redshift, you will have to manually execute the three stages of data migration, otherwise known as ETL (Extract, Transfer, Load):

Step 1: Extract

In order to get data from Mailchimp, you’ll have to interact with Mailchimp’s API. The Mailchimp API provides endpoints for retrieving, modifying, and otherwise manipulating data that pertains to your marketing and email campaigns. 

Mailchimp uses a RESTful API, which means that you can interact with it by using tools such as Postman or Curl to make HTTP requests to the API’s endpoints. For instance, to retrieve data on a particular campaign, run the following: 

GET https://<dc>.api.mailchimp.com/3.0/campaigns/{campaign ID}

However, while applications like Postman will allow you to run and test various requests on the many endpoints, your team will have to develop executable scripts or cron jobs to automate the data retrieval. Thankfully, the Mailchimp Developer Documentation is a handy resource that will provide your developer with all of the info they require to build an effective script to automatically pull the data.

Step 2: Transform

Being a RESTful API, the data returned from Mailchimp will be in the JSON format, as displayed in the following example of campaign data (returned from the earlier GET request example):

{

  "id": "5496235ed846",

  "type": "regular",

  "create_time": "2020-03-15T14:40:36+00:00",

  "archive_url": "http://eepurl.com/xxxx",

  "status": "save",

  "emails_sent": 0,

  "send_time": "",

  "content_type": "template",

  "recipients": {

    "list_id": "57afe96172",

    "segment_text": ""

  },

  "settings": {

    "subject_line": "Do I have a Deal for you!",

    "title": "Bobby’s Snake Farm",

    "from_name": "Bobby",

    "reply_to": "bobby@bobbysfarm.com",

    "use_conversation": false,

    "to_name": "",

    "folder_id": 0,

    "authenticate": true,

    "auto_footer": false,

    "inline_css": false,

    "auto_tweet": false,

    "fb_comments": false,

    "timewarp": false,

    "template_id": 100,

    "drag_and_drop": true

  },

  "tracking": {

    "opens": true,

    "html_clicks": true,

    "text_clicks": false,

    "goal_tracking": true,

    "ecomm360": true,

    "google_analytics": true,

    "clicktale": ""

  },

  "delivery_status": {

    "enabled": false

  },

  "_links": [

    {

      "rel": "parent",

      "href": "https://usX.api.mailchimp.com/3.0/campaigns",

      "method": "GET",

      "targetSchema": "https://api.mailchimp.com/schema/3.0/Campaigns/Collection.json",

      "schema": "https://api.mailchimp.com/schema/3.0/CollectionLinks/Campaigns.json"

    },

    {

      "rel": "self",

      "href": "https://usX.api.mailchimp.com/3.0/campaigns/42694e9e57",

      "method": "GET",

      "targetSchema": "https://api.mailchimp.com/schema/3.0/Campaigns/Instance.json"

    },

    {

      "rel": "delete",

      "href": "https://usX.api.mailchimp.com/3.0/campaigns/42694e9e57",

      "method": "DELETE"

    },

    {

      "rel": "cancel_send",

      "href": "https://usX.api.mailchimp.com/3.0/campaigns/42694e9e57/actions/cancel-send",

      "method": "POST"

    },

    {

      "rel": "feedback",

      "href": "https://usX.api.mailchimp.com/3.0/campaigns/42694e9e57/feedback",

      "method": "GET",

      "targetSchema": "https://api.mailchimp.com/schema/3.0/Campaigns/Feedback/Collection.json"

    }

  ]

}

However, Redshift is based on an RDBS (Relational Database System) architecture and, as such, will not accept data in the JSON format as it is incompatible. Consequently, data returned from Mailchimp will have to be converted to a format that is readily accessible by Redshift. We would recommend the humble CSV (Comma Separated Values) format. Fortunately, there are numerous applications and solutions available for this task.

Step 3: Load

In order to load the data from Mailchimp to Redshift, it has to be done in two stages:

  1. Loading the data to Amazon S3
  2. Copying the data from S3 to Redshift.
  • Upload the data files to an Amazon S3 Bucket
  1. Log in to S3 and create a bucket
    1. Sign in to the AWS Management Console
    2. Open the Amazon S3 Console
      https://console.aws.amazon.com/s3/ .
    3. Click Create Bucket
    4. Enter a name for the bucket.
    5. Select a region.
    6. Click Create.
  2. Create a folder within the new bucket
    1. Click the bucket you just created.
    2. Select Actions > Create Folder
    3. Name the new folder (Eg: “load”)
      Note:
      Amazon will charge you for storing items in the bucket. For pricing info, see here.
  3. Upload the files to the new folder
    1. Click the new folder.
    2. Click Upload
      The
      Upload – Select Files Wizard will appear.
    3. Click Add Files
      This will provide a file selection dialog box.
    4. Select the CSV files.
    5. Click Start Upload.
  • Copy the Data from S3 to Redshift
  • To load the data from the files uploaded to S3 into the Redshift database, use the COPY command as shown.
COPY table_name FROM 's3://<your-bucket-name>/load/file_name.csv' credentials  'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' CSV; 

You will need to ensure that tables have been prepared in Redshift that matches that of the incoming data. If the CSV file has a different layout or arrangement then this may trigger errors. The following code illustrates the command to replicate the column layout.

COPY table_name col1, col2, col3, col4) FROM 's3://<your-bucket-name>/load/file_name.csv' 
credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' CSV;


If you wish, you may ignore the file header rows in the CSV files, but this must be specified in the COPY command using the IGNOREHEADER1 specifier. Eg:

COPY table_name col1, col2, col3, col4) FROM 's3://<your-bucket-name>/load/file_name.csv' 
credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' CSV
 IGNOREHEADER1;

If properly executed, your data should now be accessible in your Redshift database.

Limitations with Using the Mailchimp API for Data Migration from Mailchimp to Redshift

Given that your team does consist of at least one individual with the necessary skillset to produce the necessary scripts and carry out the required steps, developing code requires substantial investments of time and effort. Apart from merely being written, the code must be tested, debugged, and maintained for the entirety of its lifespan in order to preserve its proper function. 

Once completed Hevo will immediately begin migrating data from Mailchimp to your Redshift table, allowing you access to your data in no time. Hevo ensures that all your data from Mailchimp is automatically mapped to its relevant tables in Redshift. Sign up for a free, zero-risk 14 day trial with Hevo to experience a smooth and hassle-free data load.

Method 2: Using Hevo to Load Data from Mailchimp to Redshift

Use Hevo Data to move your data
Image Source: Self

Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Sign up here for a 14-Day Free Trial!

It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. It allows you to focus on key business needs and perform insightful analysis using various BI tools such as Power BI, Tableau, etc. 

Hevo focuses on two simple steps to move your data from Mailchimp to Redshift:

  • Configure Source: Connect Hevo Data with Mailchimp by providing a unique name for your Pipeline, along with details about your authorized Mailchimp account. You can also choose the historical sync duration for your Mailchimp data.
Mailchimp to Redshift using Hevo: Configuring Mailchimp
Image Source: Self
  • Integrate Data: Complete Mailchimp to Redshift migration by providing your destination name, account name, region of your account, database username and password, database and schema name, and the Data Warehouse name.
Mailchimp to Redshift using Hevo: Configuring Redshift
Image Source: Self

Check Out What Makes Hevo Amazing:

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
  • Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
  • Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.

Conclusion

While Mailchimp does provide native data and performance analysis tools, they do not allow combining this data with data from your other marketing efforts. Nor does it allow for complex SQL queries. Redshift, on the other hand, provides these missing abilities. This provides for greater scope, deeper insights, and higher levels of business intelligence. By ensuring a seamless and secure data migration, Hevo can play a significant role in helping you derive timely insights from your Mailchimp Data.

Visit our Website to Explore Hevo

Hevo is a No-code Data Pipeline and has awesome 100+ pre-built integrations that you can choose from. Hevo can help you integrate your data from numerous sources and load them into a destination to analyze real-time data with a BI tool such as Tableau. It will make your life easier and data migration hassle-free. It is user-friendly, reliable, and secure. Check out the pricing details here. Try signing up for a 14-day free trial and see the difference! Experience the power of Hevo first hand. Watch this short overview video to get a sense of how Hevo works:

Simplify your data analysis with Hevo today! Sign Up here for a 14-day free trial!

What is your preferred approach to move data from Mailchimp to Redshift? Do let us know in the comments section below. 

No-code Data Pipeline for Redshift