Email Marketing & Automation Analytics data model
Kimball warehouse for email/marketing-automation: a transaction-grain email-event fact, an accumulating-snapshot per-recipient send fact, a list subscription-event fact, and an email-attributed revenue fact over conformed subscriber/campaign/flow/message/list dimensions. Campaign and flow keys are nullable so broadcast campaigns and automation flows share the same facts.
Email service providers and marketing-automation platforms (Klaviyo, Mailchimp, Marketo, Braze, Iterable) migrated to a cloud warehouse: email engagement events, per-recipient sends, list subscription changes, and email-attributed revenue over a conformed SCD2 subscriber dimension plus campaign, automation-flow, message/variant, and list dimensions for deliverability, engagement, list growth/churn, flow performance, and revenue analytics. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 10
- tables
- 93
- columns
- 17
- relationships
- 3
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Campaign and flow as parallel nullable dimensions
Every send originates from either a one-off campaign or an automation flow; carrying both as nullable FKs on the send and event facts lets broadcast and triggered email share one conformed model instead of two divergent schemas.
Event grain separate from per-recipient send grain
ESPs emit many events (open/click/bounce) per recipient; an accumulating send fact gives clean open/click/conversion rates per recipient, while the event fact preserves the full raw engagement stream for funnels.
Subscription events modelled explicitly
List growth, unsubscribe rate, and consent are core deliverability and compliance metrics; a dedicated subscription-event fact tracks subscribed/unsubscribed/cleaned transitions the profile snapshot loses.
Email-attributed revenue with an explicit attribution window
Klaviyo/Iterable attribute orders to emails within a conversion window; storing the window and attribution type on the revenue fact makes per-flow ROI and revenue-per-recipient auditable.
Tables in the Email Marketing & Automation Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (4)
fct_email_event
Grain: one row per email engagement event
Transaction-grain email event fact for deliverability and engagement funnels.
| Column | Type | Keys |
|---|---|---|
| email_event_key | bigint | PK |
| date_key | bigint | |
| subscriber_key | bigint | |
| email_campaign_key | bigint | |
| flow_key | bigint | |
| message_key | bigint | |
| email_event_id | varchar | |
| event_type | varchar | |
| is_unique | boolean | |
| device_type | varchar | |
| link_url | varchar | |
| event_count | smallint |
fct_email_send
Grain: one row per email sent to a subscriber (accumulating snapshot)
Accumulating-snapshot send fact (one row per recipient) for open/click/conversion rates.
| Column | Type | Keys |
|---|---|---|
| email_send_key | bigint | PK |
| subscriber_key | bigint | |
| email_campaign_key | bigint | |
| flow_key | bigint | |
| message_key | bigint | |
| send_date_key | bigint | |
| send_id | varchar | |
| flow_step_number | smallint | |
| first_open_date_key | bigint | |
| first_click_date_key | bigint | |
| open_count | smallint | |
| click_count | smallint | |
| is_delivered | boolean | |
| is_bounced | boolean | |
| is_unsubscribed | boolean | |
| is_converted | boolean | |
| attributed_revenue | decimal | |
| send_count | smallint |
fct_subscription_event
Grain: one row per list subscription change
Transaction-grain subscription fact for list growth, churn, and consent tracking.
| Column | Type | Keys |
|---|---|---|
| subscription_event_key | bigint | PK |
| date_key | bigint | |
| subscriber_key | bigint | |
| list_key | bigint | |
| subscription_event_id | varchar | |
| event_type | varchar | |
| source | varchar | |
| event_count | smallint |
fct_email_revenue
Grain: one row per email-attributed order/conversion
Email-attributed revenue fact for per-campaign/flow ROI and revenue-per-recipient.
| Column | Type | Keys |
|---|---|---|
| email_revenue_key | bigint | PK |
| date_key | bigint | |
| subscriber_key | bigint | |
| email_campaign_key | bigint | |
| flow_key | bigint | |
| order_id | varchar | |
| revenue | decimal | |
| attribution_type | varchar | |
| attribution_window_hours | smallint | |
| items_count | smallint | |
| revenue_count | smallint |
Dimension tables (4)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint | |
| day_of_week | smallint | |
| is_weekend | boolean |
dim_subscriber
Grain: one row per email subscriber per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| subscriber_key | bigint | PK |
| subscriber_id | varchar | BK |
| varchar | PII | |
| first_name | varchar | PII |
| last_name | varchar | PII |
| country | varchar | |
| consent_status | varchar | |
| acquisition_source | varchar | |
| lifetime_value_band | varchar | |
| is_suppressed | boolean |
dim_email_campaign
Grain: one row per email campaign / broadcast
| Column | Type | Keys |
|---|---|---|
| email_campaign_key | bigint | PK |
| campaign_id | varchar | BK |
| campaign_name | varchar | |
| subject_line | varchar | |
| campaign_type | varchar | |
| from_name | varchar | |
| from_email | varchar | |
| send_date | date | |
| audience_size | integer |
dim_flow
Grain: one row per automation flow / journey
| Column | Type | Keys |
|---|---|---|
| flow_key | bigint | PK |
| flow_id | varchar | BK |
| flow_name | varchar | |
| flow_type | varchar | |
| trigger_type | varchar | |
| status | varchar | |
| num_steps | smallint |
Lookup tables (2)
dim_message
Grain: one row per email message / variant
| Column | Type | Keys |
|---|---|---|
| message_key | bigint | PK |
| message_id | varchar | BK |
| message_name | varchar | |
| variant | varchar | |
| subject_line | varchar | |
| channel | varchar |
dim_list
Grain: one row per list / segment
| Column | Type | Keys |
|---|---|---|
| list_key | bigint | PK |
| list_id | varchar | BK |
| list_name | varchar | |
| list_type | varchar | |
| is_dynamic | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_email_event.date_key → dim_date (many-to-one)
- fct_email_event.subscriber_key → dim_subscriber (many-to-one)
- fct_email_event.email_campaign_key → dim_email_campaign (many-to-one)
- fct_email_event.flow_key → dim_flow (many-to-one)
- fct_email_event.message_key → dim_message (many-to-one)
- fct_email_send.subscriber_key → dim_subscriber (many-to-one)
- fct_email_send.email_campaign_key → dim_email_campaign (many-to-one)
- fct_email_send.flow_key → dim_flow (many-to-one)
- fct_email_send.message_key → dim_message (many-to-one)
- fct_email_send.send_date_key → dim_date (many-to-one)
- fct_subscription_event.date_key → dim_date (many-to-one)
- fct_subscription_event.subscriber_key → dim_subscriber (many-to-one)
- fct_subscription_event.list_key → dim_list (many-to-one)
- fct_email_revenue.date_key → dim_date (many-to-one)
- fct_email_revenue.subscriber_key → dim_subscriber (many-to-one)
- fct_email_revenue.email_campaign_key → dim_email_campaign (many-to-one)
- fct_email_revenue.flow_key → dim_flow (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.