Loading Data From Mailchimp Into Amazon Redshift / PostgreSQL
Beyond the data exports that Mailchimp provides via their interface – you'll want the raw subscriber data to perform deeper analysis.
Mailchimp assumes you are interested in statistics on a per-campaign basis. If you send out emails couple of times every week, you are more interested in subscriber level statistics. Just because a subscriber doesn't open a couple of emails, it doesn't mean that they are un-engaged.
Step 1: Extract your Mailchimp data
Here's a simple Python script that exports your data from Mailchimp.
You'll have a CSV file with the following fields:
|12abcd||2017-03-20T15:00:00+00:00||🙏 Your best customers in SQL||email_1||2||1|
|12abcd||2017-03-20T15:00:00+00:00||🙏 Your best customers in SQL||email_2||4||0|
|12wxyz||2017-03-27T15:00:00+00:00||📊 Histogram Summaries with SQL||email_1||0||0|
|12wxyz||2017-03-27T15:00:00+00:00||📊 Histogram Summaries with SQL||email_2||1||0|
Step 2: Load data into your database
CREATE TABLE stats ( campaign_id varchar(16), sent_time timestamp, subject varchar(256), email varchar(64), open_count integer, click_count integer );
We can then use PostgreSQL's
\copy command to copy the CSV file into the table we just created:
\copy stats from 'mailchimp.csv' CSV;
Step 3: Segmenting and Lead scoring your email list
We have a complete tutorial on analyzing your mailchimp data.
No spam, ever! Unsubscribe any time. Learn more about the product.