Design a subscription business model

A subscription analytics model uses a monthly periodic snapshot fact table cross-joined against a calendar date spine to measure Monthly Recurring Revenue (MRR), upgrades, and churn. Subscription lifecycle changes are recorded in an event fact table to capture plan transitions and proration adjustments with precise timestamps.

Subscription lifecycle and MRR snapshots

Tracking recurring revenue requires evaluating subscription states at regular month-end boundaries:

fact_subscription_events (transaction grain: one row per lifecycle change)
event_id
subscription_id (degenerate dimension)
customer_key
plan_key
event_date_key
event_type (new_signup, renewal, upgrade, downgrade, cancellation, reactivation)
delta_mrr_amount
prorated_charge_amount

fact_subscription_monthly_snapshot (grain: one row per active subscription per month)
subscription_id
customer_key
plan_key
month_date_key
mrr_amount
arr_amount
subscription_status (new, retained, upgraded, downgraded, churned)

Key mechanics behind the monthly snapshot:

• fact_subscription_events records discrete transactions with financial delta amounts and proration credits resulting from mid-cycle tier changes.
• fact_subscription_monthly_snapshot normalizes annual, quarterly, and monthly billing intervals into a standardized monthly recurring revenue metric.
• Revenue movements are categorized into expansion MRR from upgrades, contraction MRR from downgrades, new MRR from acquisitions, and churned MRR.
• Annual contracts require dividing total contract value by twelve to reflect true monthly accounting run rates rather than booking cash spikes as recurring revenue.

Churn detection with a date spine

Calculating churn requires identifying subscriptions that generated revenue in the prior month but produced zero revenue in the current month. Because cancelled subscriptions stop generating source transactions, they disappear from naive query joins. To solve this, construct a complete date spine:

• Cross join the list of unique subscriptions against a calendar table of months (dim_month_spine) spanning the active subscription horizon.
• Left join historical event states onto the spine to forward-fill subscription standing for every calendar month.
• Apply window functions comparing the current month to the previous month: if a subscription had mrr > 0 in month M-1 and mrr = 0 in month M, classify the record as churned revenue.
• Differentiate between voluntary churn where a customer explicitly cancels and involuntary churn caused by credit card payment gateway failures during automated billing cycles.

Proration and plan changes

When customers upgrade from a twenty-dollar plan to a fifty-dollar plan halfway through a billing cycle, calculate both the immediate cash proration and the ongoing MRR adjustment. The event table logs the partial billing delta for financial ledger reconciliation, while the monthly snapshot records the newly established fifty-dollar run rate for forward-looking ARR forecasts. If a subscriber pauses their membership for several months, record a subscription status transition event and pause monthly snapshot generation to prevent artificial churn spikes in cohort retention curves.