Marketing & Web Analytics data model
Source-agnostic Kimball starter for marketing analytics: session and pageview clickstream, conversions with attribution, daily ad-spend snapshots, and email engagement over conformed visitor, campaign, channel, page, device, and geo dimensions. For platform-specific depth use the GA4, Cross-Channel Paid-Media, Email Marketing, HubSpot, or Unified Marketing templates.
Digital marketing and web analytics: clickstream sessions and pageviews, multi-touch conversions, paid-ad spend, and email engagement — a Kimball star schema with conformed visitor, campaign, channel, and page dimensions supporting attribution, funnel, ROAS, and CAC analysis. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 13
- tables
- 101
- columns
- 23
- 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: BigQuery). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Session and pageview as separate fact grains
Sessions support attribution and bounce analysis; pageviews support content and scroll-depth analysis — different grains, different facts.
Daily ad-spend snapshot
Spend, impressions, and clicks are reported per campaign/creative per day; a snapshot fact powers ROAS and CAC without event explosion.
Visitor email hashed until identified
Anonymous visitors are tracked by cookie; email is captured only on identification and is hashed for privacy compliance.
Tables in the Marketing & Web 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 (5)
fct_sessions
Grain: one row per web/app session
Session-grain fact for engagement + attribution.
| Column | Type | Keys |
|---|---|---|
| session_key | bigint | PK |
| date_key | bigint | |
| visitor_key | bigint | |
| channel_key | bigint | |
| campaign_key | bigint | |
| device_key | bigint | |
| geo_key | bigint | |
| session_id | varchar | |
| session_start_timestamp | timestamp | |
| duration_seconds | integer | |
| pageview_count | integer | |
| is_bounce | boolean | |
| is_converted | boolean | |
| landing_page_key | bigint |
fct_pageviews
Grain: one row per page view
| Column | Type | Keys |
|---|---|---|
| pageview_key | bigint | PK |
| date_key | bigint | |
| visitor_key | bigint | |
| page_key | bigint | |
| device_key | bigint | |
| session_id | varchar | |
| view_timestamp | timestamp | |
| time_on_page_seconds | integer | |
| scroll_depth_pct | smallint | |
| is_entrance | boolean | |
| is_exit | boolean |
fct_conversions
Grain: one row per conversion event
| Column | Type | Keys |
|---|---|---|
| conversion_key | bigint | PK |
| date_key | bigint | |
| visitor_key | bigint | |
| channel_key | bigint | |
| campaign_key | bigint | |
| session_id | varchar | |
| conversion_type | varchar | |
| conversion_value | decimal | |
| order_id | varchar | |
| attribution_model | varchar |
fct_ad_spend
Grain: one row per campaign per creative per day (snapshot)
Daily ad-spend snapshot for ROAS / CAC.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| campaign_key | bigint | |
| channel_key | bigint | |
| ad_creative_key | bigint | |
| impressions | bigint | |
| clicks | integer | |
| spend | decimal | |
| conversions | integer | |
| conversion_value | decimal |
fct_email_events
Grain: one row per email engagement event
| Column | Type | Keys |
|---|---|---|
| email_event_key | bigint | PK |
| date_key | bigint | |
| visitor_key | bigint | |
| campaign_key | bigint | |
| email_id | varchar | |
| event_type | varchar | |
| event_timestamp | timestamp | |
| link_url | varchar |
Dimension tables (7)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_week | smallint | |
| week_of_year | smallint | |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint | |
| is_weekend | boolean |
dim_visitor
Grain: one row per visitor per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| visitor_key | bigint | PK |
| visitor_id | varchar | BK |
| anonymous_id | varchar | |
| varchar | PII | |
| is_identified | boolean | |
| first_seen_date | date | |
| country | varchar | |
| lifecycle_stage | varchar |
dim_campaign
Grain: one row per marketing campaign
| Column | Type | Keys |
|---|---|---|
| campaign_key | bigint | PK |
| campaign_id | varchar | BK |
| campaign_name | varchar | |
| source | varchar | |
| medium | varchar | |
| campaign_type | varchar | |
| start_date | date | |
| end_date | date | |
| budget | decimal |
dim_page
Grain: one row per page
| Column | Type | Keys |
|---|---|---|
| page_key | bigint | PK |
| page_path | varchar | BK |
| page_title | varchar | |
| page_type | varchar | |
| site_section | varchar |
dim_device
Grain: one row per device profile
| Column | Type | Keys |
|---|---|---|
| device_key | bigint | PK |
| device_type | varchar | |
| operating_system | varchar | |
| browser | varchar | |
| form_factor | varchar |
dim_geo
Grain: one row per geography
| Column | Type | Keys |
|---|---|---|
| geo_key | bigint | PK |
| country | varchar | BK |
| region | varchar | |
| city | varchar | |
| metro_area | varchar |
dim_ad_creative
Grain: one row per ad creative
| Column | Type | Keys |
|---|---|---|
| ad_creative_key | bigint | PK |
| creative_id | varchar | BK |
| creative_name | varchar | |
| ad_format | varchar | |
| campaign_key | bigint |
Lookup tables (1)
dim_channel
Grain: one row per acquisition channel
| Column | Type | Keys |
|---|---|---|
| channel_key | bigint | PK |
| channel_code | varchar | BK |
| channel_name | varchar | |
| channel_group | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- dim_ad_creative.campaign_key → dim_campaign (many-to-one)
- fct_sessions.date_key → dim_date (many-to-one)
- fct_sessions.visitor_key → dim_visitor (many-to-one)
- fct_sessions.channel_key → dim_channel (many-to-one)
- fct_sessions.campaign_key → dim_campaign (many-to-one)
- fct_sessions.device_key → dim_device (many-to-one)
- fct_sessions.geo_key → dim_geo (many-to-one)
- fct_sessions.landing_page_key → dim_page (many-to-one)
- fct_pageviews.date_key → dim_date (many-to-one)
- fct_pageviews.visitor_key → dim_visitor (many-to-one)
- fct_pageviews.page_key → dim_page (many-to-one)
- fct_pageviews.device_key → dim_device (many-to-one)
- fct_conversions.date_key → dim_date (many-to-one)
- fct_conversions.visitor_key → dim_visitor (many-to-one)
- fct_conversions.channel_key → dim_channel (many-to-one)
- fct_conversions.campaign_key → dim_campaign (many-to-one)
- fct_ad_spend.date_key → dim_date (many-to-one)
- fct_ad_spend.campaign_key → dim_campaign (many-to-one)
- fct_ad_spend.channel_key → dim_channel (many-to-one)
- fct_ad_spend.ad_creative_key → dim_ad_creative (many-to-one)
- fct_email_events.date_key → dim_date (many-to-one)
- fct_email_events.visitor_key → dim_visitor (many-to-one)
- fct_email_events.campaign_key → dim_campaign (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.