Mailchimp is an email marketing platform to send emails and automated messages, create targeted campaigns, facilitate reporting and analytics, and sell online.
|Capture Deletes||check||We capture deletes for non-incremental tables:
|Custom Data||check||All tables|
|dbt Package||check||Get the package|
Follow our step-by-step Mailchimp setup guide to connect Mailchimp with your destination using Fivetran connectors.
The Mailchimp API's are extensive, highly duplicative, and have extreme differences in query performance. Fivetran's connector has been optimized for incremental updates without data integrity issues.
The sync strategy ensures that every table has the most recent data, and that your historical email activity fills in over time:
- Sync everything except email activities by iteratively querying the Mailchimp API 3.0.
- Prioritize the email activity exports based on the amount of email activity that has yet to be synced.
- Export the highest priority email activities using Batch Operations.
- After 8 hours, finish and record how far back historical email activities are sync'ed to.
The dashboard has the "Email Activities Back To" field showing how far back the email activities are sync'ed. The length of time it will take for the full historical email activity data to sync is largely determined by the total number of email activity exports for campaigns and automation emails. Mailchimp can complete between 15 - 75 email activity export Batch Operations per day, depending on the number of recipients.
Mailchimp's schema follows Fivetran's standard API schema rules.
This schema applies to Mailchimp connectors set up after August 2017.
When you delete a list member in Mailchimp, their activity (sent, open, click, bounce) is deleted as well. Because of this, the data Fivetran delivers will sum to slightly less than the aggregate reporting in Mailchimp's dashboard.
list_member.status field has six values:
- pending - has yet to opt-in
- subscribed - opt-ed in
- unsubscribed - have opt-ed out
- cleaned - emails bounced too many times, removed
- transactional - part of Mandrill automation
- archived - removed, but can be added back
Unsubscribes are also captured in the
unsubscribe table, which indicates the campaign or automation email which triggered the list member to unsubscribe. The
campaign_id is a foreign key reference to the
id field of both the
campaign.type field has five values:
Multi-variate campaigns (Mailchimp PRO feature) can be identified by having type of
variate. They have two parts:
- The multi-variate combinations are run until a winner is chosen.
- Then, the winning combination is spawned as a new campaign.
The combinations can be analyzed using the
combination_id field on the
The winning campaign is identified by the
campaign id in
winning_campaign_id field, and has the
type field value of
If enabled, the
segment_member table is only updated once per day.