Netto omzetretentie (NRR): formule en voorbeeld.

WITH per_customer AS (
  SELECT customer_id,
         SUM(mrr) FILTER (WHERE started_at <= current_date - interval '1 year'
           AND (ended_at IS NULL OR ended_at > current_date - interval '1 year')) AS start_mrr,
         SUM(mrr) FILTER (WHERE started_at <= current_date
           AND (ended_at IS NULL OR ended_at > current_date)) AS end_mrr
  FROM subscriptions
  GROUP BY customer_id
)
SELECT SUM(start_mrr) AS start_mrr,
       SUM(COALESCE(end_mrr, 0)) AS mrr_same_customers_now,
       ROUND(100.0 * SUM(COALESCE(end_mrr, 0)) / NULLIF(SUM(start_mrr), 0), 1) AS nrr_pct
FROM per_customer
WHERE start_mrr > 0;

NRR (12 months) % =
VAR StartDate = EDATE(MAX('Date'[Date]), -12)
VAR Window = DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)
VAR Cohort =
    CALCULATETABLE(VALUES(MrrMovements[customer_id]), REMOVEFILTERS('Date'), MrrMovements[month] <= StartDate)
VAR StartMRR = CALCULATE([MRR], DATEADD('Date'[Date], -12, MONTH))
VAR Expansion =
    CALCULATE(SUM(MrrMovements[amount]), Window, Cohort, MrrMovements[movement_type] = "expansion")
VAR Lost =
    CALCULATE(SUM(MrrMovements[amount]), Window, Cohort, MrrMovements[movement_type] IN {"contraction", "churn"})
RETURN
    DIVIDE(StartMRR + Expansion - Lost, StartMRR)