SaaS Product-Led Growth & Retention Cohort Analytics data model
Kimball warehouse for SaaS PLG: four facts (signup-activation accumulating snapshot, ARR-movement event, monthly MRR/ARR periodic snapshot, daily product-usage periodic snapshot) over conformed date, month, user (SCD2), account (SCD2), plan, acquisition channel, movement type, customer segment, and feature dimensions. Drives NRR by signup cohort, GRR, ARR waterfall, activation rate, time-to-value, and feature adoption reports.
SaaS product-led growth analytics — signup → activation → expansion → retention funnel modelled as a Kimball mart on Snowflake. Cohorts anchored on signup month flow into monthly MRR/ARR composition snapshots, ARR movement events (new / expansion / contraction / churn / reactivation) for the ARR waterfall, and daily product-usage facts feeding adoption depth and customer-health scoring. SCD2 account and user dimensions preserve as-was segment, plan, and CSM assignment for comparable NRR / GRR / logo-retention reporting; a plan role-play on the movement fact captures upgrade / downgrade direction. 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
- 124
- columns
- 19
- 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
Accumulating snapshot for the signup → activation → paid funnel
The PLG funnel has a small, well-defined set of milestones per signup (signup, first-value, paying conversion); an accumulating snapshot fact with three date role-plays lets a single row track elapsed time-to-activate, time-to-first-value, and time-to-paid for activation-rate and TTV reporting.
Event-grain ARR-movement fact for the ARR waterfall
Modeling new / expansion / contraction / churn / reactivation as individual movement events with arr_amount_before/after and arr_delta makes the ARR waterfall a simple sum-by-movement-type and supports per-account churn-and-reactivation reconstruction.
Periodic monthly MRR snapshot for NRR / GRR cohort retention
NRR and GRR are computed as ratios of cohort MRR across months; a month-end periodic snapshot at the account×month grain gives an additive base for cohort retention curves and trends without rebuilding from movement events.
Daily product-usage fact for adoption depth and health scoring
Customer-health scoring blends frequency, depth, and feature breadth; a daily account×feature usage fact carries the additive session, event, and time-in-app measures that roll up to weekly / monthly adoption and feed the health-score model on the MRR fact.
SCD2 on account and user; previous-plan role-play on movement
Segment, plan, CSM, and account status drift over time; SCD2 on dim_account and dim_user preserves as-was attributes for comparable cohort reporting, and a role-played previous_plan_key on fct_subscription_movement makes upgrade vs downgrade direction explicit without parsing surrogate history.
Tables in the SaaS Product-Led Growth & Retention Cohort 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_signup_activation
Grain: one row per signup — accumulating snapshot of activation journey
Accumulating snapshot — activation funnel from signup to paying conversion.
| Column | Type | Keys |
|---|---|---|
| signup_activation_key | bigint | PK |
| signup_date_key | bigint | |
| first_value_event_date_key | bigint | |
| paying_conversion_date_key | bigint | |
| user_key | bigint | |
| account_key | bigint | |
| plan_key | bigint | |
| acquisition_channel_key | bigint | |
| signup_id | varchar | |
| time_to_activate_minutes | integer | |
| time_to_first_value_minutes | integer | |
| time_to_paid_days | integer | |
| sessions_in_first_week | integer | |
| currency | varchar | |
| activated_flag | smallint | |
| converted_to_paid_flag | smallint | |
| is_self_serve_signup | boolean | |
| signup_source | varchar |
fct_subscription_movement
Grain: one row per ARR movement event (new / expansion / contraction / churn / reactivation)
Event-grain ARR movement fact — powers the ARR waterfall.
| Column | Type | Keys |
|---|---|---|
| subscription_movement_key | bigint | PK |
| event_date_key | bigint | |
| account_key | bigint | |
| plan_key | bigint | |
| previous_plan_key | bigint | |
| movement_type_key | bigint | |
| subscription_id | varchar | |
| arr_amount_before | decimal | |
| arr_amount_after | decimal | |
| arr_delta | decimal | |
| mrr_delta | decimal | |
| seats_before | integer | |
| seats_after | integer | |
| seats_delta | integer | |
| currency | varchar | |
| movement_type | varchar | |
| is_downgrade | boolean |
fct_monthly_recurring_revenue
Grain: one row per account per month — periodic snapshot of MRR / ARR composition
Periodic month-end snapshot — drives NRR / GRR cohort retention curves.
| Column | Type | Keys |
|---|---|---|
| monthly_recurring_revenue_key | bigint | PK |
| month_key | bigint | |
| account_key | bigint | |
| plan_key | bigint | |
| customer_segment_key | bigint | |
| mrr_amount | decimal | |
| arr_amount | decimal | |
| seats_count | integer | |
| active_users_count | integer | |
| monthly_active_users | integer | |
| expansion_arr_mtd | decimal | |
| contraction_arr_mtd | decimal | |
| churn_arr_mtd | decimal | |
| health_score | decimal | |
| currency | varchar | |
| is_paying | boolean | |
| is_at_risk | boolean | |
| is_expanding | boolean |
fct_product_usage_daily
Grain: one row per account × feature × day — periodic product-usage snapshot
Daily product-usage fact — feeds adoption, depth, and health scoring.
| Column | Type | Keys |
|---|---|---|
| product_usage_daily_key | bigint | PK |
| usage_date_key | bigint | |
| account_key | bigint | |
| feature_key | bigint | |
| unique_active_users | integer | |
| sessions_count | integer | |
| events_count | integer | |
| time_in_app_seconds | integer | |
| features_adopted | integer | |
| depth_score | decimal | |
| adoption_pct | decimal |
Dimension tables (9)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_month | smallint | |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint | |
| week_of_year | smallint | |
| is_business_day | boolean |
dim_month
Grain: one row per calendar month (cohort / MRR snapshot grain)
| Column | Type | Keys |
|---|---|---|
| month_key | bigint | PK |
| month_label | varchar | BK |
| year | smallint | |
| quarter | smallint | |
| fiscal_period | varchar |
dim_user
Grain: one row per product user per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| user_key | bigint | PK |
| user_id | varchar | BK |
| user_email | varchar | PII |
| user_role | varchar | |
| account_id | varchar | |
| signup_date | date | |
| first_login_date | date | |
| last_login_date | date | |
| is_active | boolean |
dim_account
Grain: one row per customer account per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| account_key | bigint | PK |
| account_id | varchar | BK |
| account_name | varchar | |
| industry | varchar | |
| employee_size_band | varchar | |
| country | varchar | |
| signup_cohort_month | varchar | |
| customer_segment | varchar | |
| acquisition_channel | varchar | |
| csm_id | varchar | |
| account_status | varchar |
dim_plan
Grain: one row per subscription plan
| Column | Type | Keys |
|---|---|---|
| plan_key | bigint | PK |
| plan_id | varchar | BK |
| plan_name | varchar | |
| plan_tier | varchar | |
| billing_frequency | varchar | |
| list_price_mrr | decimal | |
| seat_based | boolean | |
| is_active | boolean |
dim_acquisition_channel
Grain: one row per acquisition channel
| Column | Type | Keys |
|---|---|---|
| acquisition_channel_key | bigint | PK |
| channel_id | varchar | BK |
| channel_name | varchar | |
| channel_category | varchar |
dim_movement_type
Grain: one row per ARR movement type
| Column | Type | Keys |
|---|---|---|
| movement_type_key | bigint | PK |
| movement_type | varchar | BK |
| description | varchar | |
| is_positive_arr_impact | boolean |
dim_customer_segment
Grain: one row per customer segment / health tier
| Column | Type | Keys |
|---|---|---|
| customer_segment_key | bigint | PK |
| segment_id | varchar | BK |
| segment_name | varchar | |
| ltv_band | varchar | |
| health_segment | varchar |
dim_feature
Grain: one row per product feature
| Column | Type | Keys |
|---|---|---|
| feature_key | bigint | PK |
| feature_id | varchar | BK |
| feature_name | varchar | |
| feature_category | varchar | |
| is_premium | boolean | |
| launched_date | date |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_signup_activation.signup_date_key → dim_date (many-to-one)
- fct_signup_activation.first_value_event_date_key → dim_date (many-to-one)
- fct_signup_activation.paying_conversion_date_key → dim_date (many-to-one)
- fct_signup_activation.user_key → dim_user (many-to-one)
- fct_signup_activation.account_key → dim_account (many-to-one)
- fct_signup_activation.plan_key → dim_plan (many-to-one)
- fct_signup_activation.acquisition_channel_key → dim_acquisition_channel (many-to-one)
- fct_subscription_movement.event_date_key → dim_date (many-to-one)
- fct_subscription_movement.account_key → dim_account (many-to-one)
- fct_subscription_movement.plan_key → dim_plan (many-to-one)
- fct_subscription_movement.previous_plan_key → dim_plan (many-to-one)
- fct_subscription_movement.movement_type_key → dim_movement_type (many-to-one)
- fct_monthly_recurring_revenue.month_key → dim_month (many-to-one)
- fct_monthly_recurring_revenue.account_key → dim_account (many-to-one)
- fct_monthly_recurring_revenue.plan_key → dim_plan (many-to-one)
- fct_monthly_recurring_revenue.customer_segment_key → dim_customer_segment (many-to-one)
- fct_product_usage_daily.usage_date_key → dim_date (many-to-one)
- fct_product_usage_daily.account_key → dim_account (many-to-one)
- fct_product_usage_daily.feature_key → dim_feature (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.