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.

https://gist.github.com/gane5h/6ea5e34027be3aace7de369c701ab8e2

The Schema

You'll have a CSV file with the following fields:

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.

Next Generation SQL Data Analysis Write SQL, visualize data, and share your results. Onboarding forward-thinking teams now:

No spam, ever! Unsubscribe any time. Learn more about the product.