Retention

Purpose

Provide a standardized retention metrics view, translating MRR movements and customer churn into commonly used SaaS retention KPIs.

This model is designed as a diagnostic reporting layer, enabling:

Gross Revenue Retention and Net Revenue Retention analysis
Separation of revenue churn vs logo churn
Comparable retention metrics across periods and scenarios

It aligns revenue-based and quantity-based retention perspectives in a single table.

Business Logic

Start from MRR Aggregated
Join with Customer Count View
Calculate retention metrics using current-period MRR as the baseline
Express all outputs as ratios (percent-ready)
Produce one row per date and value type

Output Contract

Definitions

Column	Description
date	Date of the snapshot (`YYYY-MM-DD`)
value_type	Indicator such as `actual`, `budget`
gross_revenue_retention	Revenue retained excluding upsell
net_revenue_retention	Revenue retained including upsell
churn_value	Revenue churn rate
churn_qty	Customer churn rate
logo_retention	Logo retention rate

Sample & Implementation

Revenue and logo retention metrics derived from MRR and customer counts.
date	value_type	gross_revenue_retention	net_revenue_retention	churn_value	churn_qty	logo_retention
2025-01-01	budget	0.9916	0.9929	0.0083	0.0083	0.9917
2025-02-01	budget	0.9957	0.997	0.0043	0.0043	0.9957
2025-03-01	budget	0.9957	0.997	0.0042	0.0042	0.9958
2025-04-01	budget	0.9958	0.9971	0.0042	0.0042	0.9958
2025-05-01	budget	0.9958	0.9971	0.0041	0.0041	0.9959
2025-06-01	budget	0.9959	0.9971	0.004	0.004	0.996
2025-07-01	budget	0.9957	0.9969	0.0043	0.0043	0.9957
2025-08-01	budget	0.9957	0.9969	0.0042	0.0042	0.9958
2025-09-01	budget	0.9958	0.997	0.0042	0.0042	0.9958
2025-10-01	budget	0.9958	0.997	0.0041	0.0041	0.9959

{{config(materialized='view')}}


with mrr_aggregated as (
    select * from {{ ref('mrr_aggregated') }}
),

customer_count_view as (
    select * from {{ref('customer_count_view')}}
)

select
    mrr.date,
    mrr.value_type,
    (mrr.current_base - mrr.downsell - mrr.churn) / mrr.current_base as gross_revenue_retention,
    (mrr.current_base - mrr.downsell - mrr.churn + mrr.upsell) / mrr.current_base as net_revenue_retention,
    mrr.churn / mrr.current_base as churn_value,
    ccv.churn / ccv.current_base as churn_qty,
    - (ccv.churn / ccv.current_base) + 1 as logo_retention
from mrr_aggregated mrr
left join customer_count_view ccv on ccv.date = mrr.date and ccv.value_type = mrr.value_type

Dependencies

Transformations: MRR Aggregated and Customer Count View