SaaS Subscription Analytics data model
Kimball warehouse for SaaS metrics: the canonical per-account monthly MRR snapshot tagged with movement category, an accumulating subscription snapshot, product usage events, invoices/payments, and support tickets.
Subscription-business analytics for a B2B SaaS: monthly recurring revenue movement (new / expansion / contraction / churn), product usage, billing, and support — modeled as a Kimball star schema so MRR, ARR, NRR, retention cohorts, and product engagement all share conformed account, user, plan, and date dimensions. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 14
- tables
- 124
- columns
- 21
- relationships
- 4
- 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
Per-account monthly MRR snapshot with change category
The standard way to compute MRR/ARR movement and NRR is one row per account per month classified as new/expansion/contraction/churn.
Accumulating snapshot for subscription lifecycle
Subscriptions have milestone dates (trial end, start, cancel) best modeled as an accumulating snapshot updated in place.
Tables in the SaaS Subscription 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 (6)
fct_subscription
Grain: one row per subscription (accumulating snapshot)
Accumulating snapshot — milestones update in place.
| Column | Type | Keys |
|---|---|---|
| subscription_key | bigint | PK |
| account_key | bigint | |
| plan_key | bigint | |
| status_key | bigint | |
| start_date_key | bigint | |
| end_date_key | bigint | |
| trial_end_date_key | bigint | |
| subscription_number | varchar | |
| seats | integer | |
| mrr | decimal | |
| arr | decimal | |
| billing_period | varchar | |
| is_active | boolean |
fct_mrr_snapshot
Grain: one row per account per month (periodic snapshot)
The canonical SaaS MRR-movement snapshot.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| account_key | bigint | |
| plan_key | bigint | |
| mrr_change_category | varchar | |
| mrr | decimal | |
| arr | decimal | |
| mrr_delta | decimal | |
| seats | integer | |
| is_active | boolean |
fct_usage_events
Grain: one row per product usage event
| Column | Type | Keys |
|---|---|---|
| usage_key | bigint | PK |
| date_key | bigint | |
| account_key | bigint | |
| user_key | bigint | |
| feature_key | bigint | |
| event_type | varchar | |
| event_count | integer | |
| event_timestamp | timestamp | |
| session_id | varchar |
fct_invoices
Grain: one row per invoice
| Column | Type | Keys |
|---|---|---|
| invoice_key | bigint | PK |
| date_key | bigint | |
| account_key | bigint | |
| plan_key | bigint | |
| invoice_number | varchar | |
| due_date_key | bigint | |
| subtotal | decimal | |
| tax_amount | decimal | |
| total_amount | decimal | |
| amount_paid | decimal | |
| invoice_status | varchar |
fct_payments
Grain: one row per payment
| Column | Type | Keys |
|---|---|---|
| payment_key | bigint | PK |
| date_key | bigint | |
| account_key | bigint | |
| invoice_number | varchar | |
| amount | decimal | |
| payment_method | varchar | |
| payment_status | varchar |
fct_support_tickets
Grain: one row per support ticket
| Column | Type | Keys |
|---|---|---|
| ticket_key | bigint | PK |
| created_date_key | bigint | |
| resolved_date_key | bigint | |
| account_key | bigint | |
| user_key | bigint | |
| agent_key | bigint | |
| ticket_number | varchar | |
| priority | varchar | |
| category | varchar | |
| ticket_status | varchar | |
| csat_score | smallint | |
| first_response_minutes | integer | |
| resolution_minutes | 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 | |
| month_number | smallint | |
| month_name | varchar | |
| quarter | smallint | |
| year | smallint | |
| is_month_end | boolean | |
| fiscal_period | varchar |
dim_account
Grain: one row per account per SCD2 version
Type-2 SCD: plan tier and health score change over the lifecycle.
SCD type 2
| Column | Type | Keys |
|---|---|---|
| account_key | bigint | PK |
| account_id | varchar | BK |
| account_name | varchar | |
| industry | varchar | |
| employee_band | varchar | |
| country | varchar | |
| region | varchar | |
| plan_tier | varchar | |
| billing_currency | varchar | |
| csm_name | varchar | PII |
| acquisition_channel | varchar | |
| signup_date | date | |
| health_score | smallint | |
| is_active | 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 |
| account_key | bigint | |
| varchar | PII | |
| full_name | varchar | PII |
| role | varchar | |
| seat_type | varchar | |
| is_admin | boolean | |
| signup_date | date | |
| last_login_date | date | |
| is_active | boolean |
dim_plan
Grain: one row per pricing plan
| Column | Type | Keys |
|---|---|---|
| plan_key | bigint | PK |
| plan_id | varchar | BK |
| plan_name | varchar | |
| plan_family | varchar | |
| billing_period | varchar | |
| list_price | decimal | |
| is_seat_based | boolean | |
| feature_tier | varchar |
dim_feature
Grain: one row per product feature
| Column | Type | Keys |
|---|---|---|
| feature_key | bigint | PK |
| feature_code | varchar | BK |
| feature_name | varchar | |
| module | varchar | |
| is_premium | boolean |
dim_support_agent
Grain: one row per support agent
| Column | Type | Keys |
|---|---|---|
| agent_key | bigint | PK |
| agent_id | varchar | BK |
| agent_name | varchar | PII |
| team | varchar | |
| tier | varchar |
dim_campaign
Grain: one row per marketing campaign
| Column | Type | Keys |
|---|---|---|
| campaign_key | bigint | PK |
| campaign_id | varchar | BK |
| campaign_name | varchar | |
| channel | varchar | |
| start_date | date | |
| end_date | date |
Lookup tables (1)
dim_subscription_status
Grain: one row per status
| Column | Type | Keys |
|---|---|---|
| status_key | bigint | PK |
| status_code | varchar | BK |
| status_name | varchar | |
| is_paying | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- dim_user.account_key → dim_account (many-to-one)
- fct_subscription.account_key → dim_account (many-to-one)
- fct_subscription.plan_key → dim_plan (many-to-one)
- fct_subscription.status_key → dim_subscription_status (many-to-one)
- fct_subscription.start_date_key → dim_date (many-to-one)
- fct_mrr_snapshot.date_key → dim_date (many-to-one)
- fct_mrr_snapshot.account_key → dim_account (many-to-one)
- fct_mrr_snapshot.plan_key → dim_plan (many-to-one)
- fct_usage_events.date_key → dim_date (many-to-one)
- fct_usage_events.account_key → dim_account (many-to-one)
- fct_usage_events.user_key → dim_user (many-to-one)
- fct_usage_events.feature_key → dim_feature (many-to-one)
- fct_invoices.date_key → dim_date (many-to-one)
- fct_invoices.account_key → dim_account (many-to-one)
- fct_invoices.plan_key → dim_plan (many-to-one)
- fct_payments.date_key → dim_date (many-to-one)
- fct_payments.account_key → dim_account (many-to-one)
- fct_support_tickets.created_date_key → dim_date (many-to-one)
- fct_support_tickets.account_key → dim_account (many-to-one)
- fct_support_tickets.user_key → dim_user (many-to-one)
- fct_support_tickets.agent_key → dim_support_agent (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.