Web & Product Analytics data model
Kimball warehouse for digital analytics: a high-volume clickstream event fact, a session-grain fact, and a daily-active-users periodic snapshot over conformed user/page/device/geo/campaign/feature dimensions on BigQuery. User email is hashed and marketing consent is modelled.
Web and product analytics covering atomic clickstream events, sessions, and daily active users over a conformed SCD2 user dimension with date, page, device, geo, campaign, and feature dimensions for funnel, engagement, retention, and feature-adoption analytics. Tracking-consent and user identifiers are governed under GDPR/ePrivacy. 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
- 99
- columns
- 16
- relationships
- 2
- 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
Atomic event fact plus a derived session fact
The event grain supports any future funnel definition, while a pre-aggregated session fact keeps common engagement and bounce queries fast over very high event volumes.
Daily-active-users periodic snapshot
DAU/WAU/MAU, stickiness, and retention cohorts require a dense one-row-per-active-user-per-day grain that the event fact cannot answer efficiently.
SCD2 on user with anonymous-to-known stitching
User plan, lifecycle stage, and acquisition change over time and pre-login activity must reconcile to the known user as-of each event.
Consent flag and hashed identifiers under GDPR/ePrivacy
Behavioural tracking requires lawful consent; has_marketing_consent is modelled and email/anonymous ids are masked.
Tables in the Web & Product 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 (3)
fct_event
Grain: one row per tracked clickstream event
Clickstream transaction fact — the atomic event grain for funnel and behaviour analytics.
| Column | Type | Keys |
|---|---|---|
| event_key | bigint | PK |
| date_key | bigint | |
| user_key | bigint | |
| page_key | bigint | |
| device_key | bigint | |
| geo_key | bigint | |
| campaign_key | bigint | |
| feature_key | bigint | |
| session_id | varchar | |
| event_id | varchar | |
| event_name | varchar | |
| event_category | varchar | |
| event_timestamp | timestamp | |
| event_count | smallint | |
| event_value | decimal | |
| is_conversion | boolean |
fct_session
Grain: one row per user session
Session-grain fact aggregating events for engagement, bounce, and conversion analytics.
| Column | Type | Keys |
|---|---|---|
| session_key | bigint | PK |
| date_key | bigint | |
| user_key | bigint | |
| device_key | bigint | |
| geo_key | bigint | |
| campaign_key | bigint | |
| session_id | varchar | |
| landing_page_id | varchar | |
| exit_page_id | varchar | |
| referrer_host | varchar | |
| session_start_timestamp | timestamp | |
| duration_seconds | integer | |
| page_view_count | smallint | |
| event_count | smallint | |
| is_bounce | boolean | |
| is_converted | boolean | |
| is_authenticated | boolean |
fct_daily_active_users
Grain: one row per active user per day (periodic snapshot)
Daily activity snapshot — one row per active user per day for DAU/WAU/MAU, stickiness, and retention cohorts.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| user_key | bigint | |
| device_key | bigint | |
| geo_key | bigint | |
| session_count | smallint | |
| event_count | integer | |
| active_minutes | integer | |
| feature_adoption_count | smallint | |
| is_active | boolean | |
| is_new_user | boolean | |
| is_returning_user | boolean | |
| days_since_signup | integer |
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_number | smallint | |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint | |
| is_weekend | boolean |
dim_user
Grain: one row per user per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| user_key | bigint | PK |
| user_id | varchar | BK |
| anonymous_id | varchar | PII |
| varchar | PII | |
| account_type | varchar | |
| plan_tier | varchar | |
| signup_date | date | |
| acquisition_channel | varchar | |
| lifecycle_stage | varchar | |
| is_internal | boolean | |
| has_marketing_consent | boolean |
dim_page
Grain: one row per page/screen
| Column | Type | Keys |
|---|---|---|
| page_key | bigint | PK |
| page_id | varchar | BK |
| page_path | varchar | |
| page_title | varchar | |
| page_type | varchar | |
| section | varchar | |
| is_conversion_page | boolean |
dim_device
Grain: one row per device/browser profile
| Column | Type | Keys |
|---|---|---|
| device_key | bigint | PK |
| device_code | varchar | BK |
| device_type | varchar | |
| operating_system | varchar | |
| os_version | varchar | |
| browser | varchar | |
| browser_version | varchar | |
| app_version | varchar |
dim_geo
Grain: one row per geographic location
| Column | Type | Keys |
|---|---|---|
| geo_key | bigint | PK |
| geo_code | varchar | BK |
| country | varchar | |
| country_code | varchar | |
| region | varchar | |
| city | varchar | |
| timezone | varchar |
dim_campaign
Grain: one row per acquisition campaign
| Column | Type | Keys |
|---|---|---|
| campaign_key | bigint | PK |
| campaign_id | varchar | BK |
| campaign_name | varchar | |
| utm_source | varchar | |
| utm_medium | varchar | |
| utm_campaign | varchar | |
| channel_group | varchar |
dim_feature
Grain: one row per product feature
| Column | Type | Keys |
|---|---|---|
| feature_key | bigint | PK |
| feature_id | varchar | BK |
| feature_name | varchar | |
| feature_area | varchar | |
| release_stage | varchar | |
| is_premium | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_event.date_key → dim_date (many-to-one)
- fct_event.user_key → dim_user (many-to-one)
- fct_event.page_key → dim_page (many-to-one)
- fct_event.device_key → dim_device (many-to-one)
- fct_event.geo_key → dim_geo (many-to-one)
- fct_event.campaign_key → dim_campaign (many-to-one)
- fct_event.feature_key → dim_feature (many-to-one)
- fct_session.date_key → dim_date (many-to-one)
- fct_session.user_key → dim_user (many-to-one)
- fct_session.device_key → dim_device (many-to-one)
- fct_session.geo_key → dim_geo (many-to-one)
- fct_session.campaign_key → dim_campaign (many-to-one)
- fct_daily_active_users.date_key → dim_date (many-to-one)
- fct_daily_active_users.user_key → dim_user (many-to-one)
- fct_daily_active_users.device_key → dim_device (many-to-one)
- fct_daily_active_users.geo_key → dim_geo (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.