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.
Understanding Mailchimp
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
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 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:
- Loading the data to Amazon S3
- Copying the data from S3 to Redshift.
- Upload the data files to an Amazon S3 Bucket
- Log in to S3 and create a bucket
- Sign in to the AWS Management Console
- Open the Amazon S3 Console
https://console.aws.amazon.com/s3/ . - Click Create Bucket
- Enter a name for the bucket.
- Select a region.
- Click Create.
- Create a folder within the new bucket
- Click the bucket you just created.
- Select Actions > Create Folder
- Name the new folder (Eg: “load”)
Note: Amazon will charge you for storing items in the bucket. For pricing info, see here.
- Upload the files to the new folder
- Click the new folder.
- Click Upload
The Upload – Select Files Wizard will appear. - Click Add Files
This will provide a file selection dialog box. - Select the CSV files.
- 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
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.
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.
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.
With expertise in freelance writing, Vernon specializes in concepts related to data integration and data analysis, offering comprehensive insights for audiences keen on solving problems related to data industry.