Stitch Documentation
has moved!

Please update your bookmarks to https://www.stitchdata.com/docs

If you're not automatically redirected after 5 seconds, click here.

Expected MailChimp Data

We're currently doing some private testing on our MailChimp integration. Want to help out? Get in touch with us.

Now that MailChimp is successfully connected to your Stitch account, what comes next? In this article, we'll cover:

Replicating MailChimp Data

Historical Sync

By default, a historical sync of MailChimp data goes back one year from the Stitch connection date.

Frequency

Stitch will replicate MailChimp data based on the frequency you define. The default setting is every 24 hours, but you can change it to something more frequent based on your needs.

Replication Method

When Stitch replicates MailChimp data, it does so using Full Table Replication. This means that every time MailChimp data is queued for replication, all rows - including existing, new, and updated - will be replicated to your data warehouse.

Important!
This integration can pull in quite a bit of data due to how it's structured and replicated. When defining the Replication Frequency, keep the following in mind:

  • All tables in this integration use Full Table Replication.
  • Some MailChimp endpoints used nested data structures, which Stitch will de-nest and create subtables for. One record in MailChimp doesn't necessarily equal one Stitch-replicated row, which can lead to a higher row count.
  • The mailchimp_email_activity table and its subtables can contain large numbers of rows due to how MailChimp creates records for email activities. A single email address can have many actions associated with a single campaign, each of which can be its own row.

For more tips on how to reduce your usage, check out this doc.

MailChimp Tables in Your Data Warehouse

After the MailChimp setup is complete, the following tables will be created in your data warehouse. Because MailChimp uses nested arrays to structure data in some of its API's endpoints, Stitch will de-nest these arrays and create subtables. Refer to the Handling of Nested Data Structures doc for more info.

To see a list of attributes of each table, refer to MailChimp's API documentation - click the table name to go there.

mailchimp_campaigns
Primary Key:
Campaign ID (id)

The mailchimp_campaigns table records information about the campaign settings and content in your account. Columns include the campaign ID (id), type, status, total emails sent, date sent info, content type, list settings info, A/B testing settings, tracking info, RSS options, report summary, delivery status, and creation info.

  • mailchimp_campaigns__links - This subtable contains campaign link data. Attributes include the link URL, target, and method.

    Unique rows are identified by a composite key containing the email id (_sdc_source_key_email_id) and the _sdc_level_0_id columns.

mailchimp_email_activity
Primary Key:
campaign_id:email_id

The mailchimp_email_activity table contains information about member activity for a specific campaign. Attributes include the campaign ID, email ID, email address, and list ID.

Subtables

The composite keys for the following tables - made up of the campaign ID, email ID, and row ID columns - can be used to tie these subtables back to their parent, mailchimp_email_activity: _sdc_source_key_campaign_id: _sdc_source_key_email_id: _sdc_level_0_id

  • mailchimp_email_activity__activity - This subtable contains activity data. Attributes include the action, timestamp, URL, and IP address.
  • mailchimp_email_activity__links - This subtable contains email activity link data. Attributes include the link URL, target, and method.

mailchimp_lists
Primary Key:
 _id

The mailchimp_lists table includes attributes about the subscriber lists in your account, including the list ID (id), name, contact info, permission reminder, campaign default info, subscription activity notification email addresses, date created, rating, privacy setting, list stats, and creation info.

  • mailchimp_lists__links - This subtable contains list link data. Attributes include the link URL, target, and method.

    Unique rows are identified by a composite key containing the email id (_sdc_source_key_email_id) and the _sdc_level_0_id columns.

mailchimp_members
Primary Key:
Member ID (id):list_id

The mailchimp_members table records data about the members of your campaigns. Attributes include the ID (lower-case hash version of the list member’s email address), subscriber email address, requested email type, status, interests, stats, language, signup info, email client, location, and list ID.

  • mailchimp_members__links - This subtable contains member link data. Attributes include the link URL, target, and method.

    Unique rows are identified by a composite key containing the member id (_sdc_source_key_id) and the _sdc_level_0_id columns.

mailchimp_sent_to
Primary Key:
campaign_id:email_id

The mailchimp_sent_to table provides information about campaign recipients, including the campaign ID and recipient info.

  • mailchimp_sent_to__links - This subtable contains member link data. Attributes include the link URL, target, and method.

    Unique rows are identified by a composite key containing the email ID (_sdc_source_key_email_id), campaign ID (_sdc_source_key_campaign_id), and the _sdc_level_0_id columns.

mailchimp_unsubscribes
Primary Key:
campaign_id:email_id

The mailchimp_unsubscribes table records data about those who have unsubscribed from your email campaigns. This table includes the unsubscriber’s info and the campaign ID.

  • mailchimp_unsubscribes__links - This subtable contains unsubscribe link data. Attributes include the link URL, target, and method.

    Unique rows are identified by a composite key containing the email id (_sdc_source_key_email_id), campaign id (_sdc_source_key_campaign_id), and the _sdc_level_0_id columns.

RELATED

Was this article helpful?
0 out of 0 found this helpful

Comments

Questions or suggestions? If something in our documentation is unclear, let us know in the comments!