Unified Marketing Analytics (Cross-Source) data model
The capstone marketing mart: unifies web, ads, CRM, and email sources (joined on UTM/source-medium and resolved identity) into a touch fact, a channel-spend fact, a multi-touch attribution fact, and a full-funnel daily snapshot. A source-platform dimension keeps every metric traceable to its origin. Single-source detail lives in the GA4, cross-channel-ad, HubSpot/Salesforce, and email templates; this one blends them.
A cross-source marketing data warehouse that blends web analytics (GA4), paid-media spend (Meta/Google/LinkedIn/…), CRM (HubSpot/Salesforce), and email/automation into one conformed model: a cross-channel touch fact, a daily channel-spend fact, a multi-touch conversion-attribution fact, and a daily full-funnel snapshot over conformed channel, source/medium, campaign, customer, platform, and attribution-model dimensions for blended ROAS, CAC, multi-touch attribution, and full-funnel reporting. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 11
- tables
- 93
- columns
- 20
- relationships
- 1
- 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
A unified touch stream conformed across sources
Multi-touch attribution needs every interaction — ad clicks, web visits, email opens, form fills, sales meetings — in one fact at a common grain; conforming them on channel/source-medium/campaign is what makes the customer journey reconstructable end-to-end.
Spend and funnel kept as periodic snapshots, attribution as allocation
Cost (spend) and stage counts are daily aggregates, while attributed revenue is a fractional allocation per model; separating them lets CAC/ROAS be computed from spend+funnel and compared against any attribution model without recomputing the base facts.
Source-platform dimension on every fact
Blending GA4, ad platforms, CRM, and ESP data risks losing lineage; a platform dimension keeps each row traceable to its origin system for reconciliation and trust.
Identity-resolved customer dimension
Joining anonymous web behavior to known CRM contacts requires an identity-resolution step; the customer dimension carries the resolved key plus lifecycle stage so anonymous and known activity attribute to one person/account.
Tables in the Unified Marketing Analytics (Cross-Source) 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_marketing_touch
Grain: one row per marketing touchpoint across all channels (the unified touch stream)
Cross-channel touchpoint fact — the unified path-to-conversion stream blending web, ads, email, and CRM.
| Column | Type | Keys |
|---|---|---|
| marketing_touch_key | bigint | PK |
| date_key | bigint | |
| channel_key | bigint | |
| source_medium_key | bigint | |
| campaign_key | bigint | |
| customer_key | bigint | |
| platform_key | bigint | |
| touch_id | varchar | |
| touch_type | varchar | |
| is_first_touch | boolean | |
| is_last_touch | boolean | |
| is_conversion_touch | boolean | |
| touch_count | smallint |
fct_channel_spend_daily
Grain: one row per channel/campaign/platform per day (the cost side)
Daily unified spend by channel/campaign — the cost denominator for blended ROAS and CAC.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| channel_key | bigint | |
| campaign_key | bigint | |
| platform_key | bigint | |
| impressions | bigint | |
| clicks | integer | |
| spend | decimal | |
| sessions | integer | |
| leads_generated | integer |
fct_conversion_attribution
Grain: one row per (conversion, channel, attribution model) credit allocation
Multi-touch attribution fact — allocates conversion revenue across channels per model.
| Column | Type | Keys |
|---|---|---|
| conversion_attribution_key | bigint | PK |
| date_key | bigint | |
| channel_key | bigint | |
| campaign_key | bigint | |
| customer_key | bigint | |
| attribution_model_key | bigint | |
| platform_key | bigint | |
| conversion_id | varchar | |
| conversion_type | varchar | |
| touch_position | varchar | |
| credit_pct | decimal | |
| attributed_revenue | decimal | |
| attributed_conversions | decimal |
fct_funnel_daily
Grain: one row per channel/campaign/platform per day (full-funnel snapshot)
Daily full-funnel snapshot per channel for blended CAC, ROAS, and stage-conversion analysis.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| channel_key | bigint | |
| campaign_key | bigint | |
| platform_key | bigint | |
| impressions | bigint | |
| clicks | integer | |
| sessions | integer | |
| new_visitors | integer | |
| leads | integer | |
| mqls | integer | |
| sqls | integer | |
| opportunities | integer | |
| customers | integer | |
| spend | decimal | |
| revenue | decimal | |
| cac | decimal | |
| roas | decimal |
Dimension tables (3)
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 | |
| fiscal_quarter | varchar | |
| is_business_day | boolean |
dim_campaign
Grain: one row per campaign (conformed) per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| campaign_key | bigint | PK |
| campaign_id | varchar | BK |
| campaign_name | varchar | |
| channel | varchar | |
| objective | varchar | |
| owning_platform | varchar | |
| start_date | date | |
| end_date | date |
dim_customer
Grain: one row per person/account (identity-resolved) per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| customer_key | bigint | PK |
| customer_id | varchar | BK |
| entity_type | varchar | |
| varchar | PII | |
| company_name | varchar | |
| lifecycle_stage | varchar | |
| acquisition_channel | varchar | |
| acquisition_source | varchar | |
| country | varchar | |
| is_customer | boolean |
Lookup tables (4)
dim_channel
Grain: one row per marketing channel
| Column | Type | Keys |
|---|---|---|
| channel_key | bigint | PK |
| channel_name | varchar | BK |
| channel_group | varchar | |
| is_paid | boolean |
dim_source_medium
Grain: one row per source/medium (UTM)
| Column | Type | Keys |
|---|---|---|
| source_medium_key | bigint | PK |
| utm_source | varchar | |
| utm_medium | varchar | |
| source_medium | varchar | BK |
| referrer_category | varchar |
dim_platform
Grain: one row per source platform/system
| Column | Type | Keys |
|---|---|---|
| platform_key | bigint | PK |
| platform_name | varchar | BK |
| platform_category | varchar |
dim_attribution_model
Grain: one row per attribution model
| Column | Type | Keys |
|---|---|---|
| attribution_model_key | bigint | PK |
| model_name | varchar | BK |
| model_description | varchar | |
| is_data_driven | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_marketing_touch.date_key → dim_date (many-to-one)
- fct_marketing_touch.channel_key → dim_channel (many-to-one)
- fct_marketing_touch.source_medium_key → dim_source_medium (many-to-one)
- fct_marketing_touch.campaign_key → dim_campaign (many-to-one)
- fct_marketing_touch.customer_key → dim_customer (many-to-one)
- fct_marketing_touch.platform_key → dim_platform (many-to-one)
- fct_channel_spend_daily.date_key → dim_date (many-to-one)
- fct_channel_spend_daily.channel_key → dim_channel (many-to-one)
- fct_channel_spend_daily.campaign_key → dim_campaign (many-to-one)
- fct_channel_spend_daily.platform_key → dim_platform (many-to-one)
- fct_conversion_attribution.date_key → dim_date (many-to-one)
- fct_conversion_attribution.channel_key → dim_channel (many-to-one)
- fct_conversion_attribution.campaign_key → dim_campaign (many-to-one)
- fct_conversion_attribution.customer_key → dim_customer (many-to-one)
- fct_conversion_attribution.attribution_model_key → dim_attribution_model (many-to-one)
- fct_conversion_attribution.platform_key → dim_platform (many-to-one)
- fct_funnel_daily.date_key → dim_date (many-to-one)
- fct_funnel_daily.channel_key → dim_channel (many-to-one)
- fct_funnel_daily.campaign_key → dim_campaign (many-to-one)
- fct_funnel_daily.platform_key → dim_platform (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.