Introduction

For an overview of how dbt powers advanced transformations, and information about our other dbt packages, take a look at this recent blog post.

The Fivetran dbt package for Mailchimp adds reporting tables, so you can easily analyze more meaningful data with your preferred BI or visualization tool. Dbt packages offer powerful transformations within your data warehouse on Snowflake, Amazon Redshift or Google BigQuery, and you can re-use the logic for other target destinations as well. The resulting tables are easier to join with tables from other applications, offering a more comprehensive view of your business.

The transformations in this dbt package make use of the full, normalized Mailchimp schema that Fivetran creates in order to:

• Enrich the list, campaign, member and segment tables with metrics from campaign and automation activities such as clicks, opens and unsubscribes
• Create an activities table that paints a complete picture of activities motivated by a campaign, including dimensions such as elapsed time between campaign launch and specific actions
• (Optional) Tie email engagement actions to automations if you’re using the Mailchimp automation feature

All of these transformations utilize data you've already paid for to clarify the impact of your Mailchimp campaigns.

You can use this package to directly enrich your Mailchimp data in the warehouse, as well as tie these output tables to other growth marketing sources, such as Campaign Manager or Iterable, and event tracking tools like AppsFlyer, Braze and Pendo.

Challenges of the Mailchimp API

Mailchimp’s API (documentation) allows you to programmatically extract data, but it doesn’t tie the available granular items to each activity and campaign. The value of Mailchimp data comes primarily from the activity, recipient and unsubscribe tables. It’s burdensome to re-model those tables for each granular item (list, member, segment, campaign, automation). The focal point of all campaigns are the recipients, which are determined on the campaign level. However, recipient information isn’t available through API endpoints such as segments. This dbt package neatly consolidates this data and puts the focus on the recipient. Finally, if you’re using Automations in Mailchimp, numerous joins are required to tie this information back to lists, campaigns, members and segments.

How Fivetran helps

Fivetran abstracts away the initial problem of Mailchimp data loading by providing code-free data ingestion. This involves normalizing the JSON response from the API and creating and continually managing schemas in your destination of choice. Fivetran handles historical data, updated information and new rows alike.

Fivetran dbt Mailchimp package results:

• Try the package‍

‍