Как посчитать GRR в SQL

Зачем GRR

GRR (Gross Revenue Retention) = MRR retained, без expansion. Показывает «pure» retention — сколько денег осталось от existing base после churn + contraction. GRR < NRR всегда. GRR > 90% — healthy SaaS.

Формула

GRR = (starting_MRR - churn_MRR - contraction_MRR) / starting_MRR × 100%

Никогда > 100% (в отличие от NRR).

GRR vs NRR

GRR clean signal от churn / downgrade quality. NRR overall health (с upsell).

Базовый расчёт

WITH start AS (
    SELECT SUM(monthly_mrr) AS start_mrr
    FROM customers
    WHERE active_on = '2026-04-01'
),
churn_mrr AS (
    SELECT SUM(monthly_mrr) AS churned
    FROM customers
    WHERE active_on = '2026-04-01'
      AND churned_at BETWEEN '2026-04-01' AND '2026-05-01'
),
contraction AS (
    SELECT SUM(old_mrr - new_mrr) AS contracted_mrr
    FROM plan_changes
    WHERE change_at BETWEEN '2026-04-01' AND '2026-05-01'
      AND new_mrr < old_mrr
)
SELECT
    s.start_mrr,
    c.churned,
    co.contracted_mrr,
    (s.start_mrr - c.churned - co.contracted_mrr)::NUMERIC * 100 / NULLIF(s.start_mrr, 0) AS grr_pct
FROM start s, churn_mrr c, contraction co;

По cohorts

WITH cohort_start AS (
    SELECT
        DATE_TRUNC('month', first_paid_at) AS cohort,
        user_id,
        monthly_mrr AS start_mrr
    FROM customers
    WHERE first_paid_at >= '2025-06-01' AND first_paid_at < '2025-07-01'
),
cohort_now AS (
    SELECT
        cs.user_id,
        cs.cohort,
        cs.start_mrr,
        COALESCE(c.monthly_mrr, 0) AS current_mrr,
        CASE WHEN c.churned_at IS NOT NULL THEN cs.start_mrr ELSE 0 END AS churned_mrr,
        GREATEST(cs.start_mrr - COALESCE(c.monthly_mrr, 0), 0) - CASE WHEN c.churned_at IS NOT NULL THEN cs.start_mrr ELSE 0 END AS contraction_mrr
    FROM cohort_start cs
    LEFT JOIN customers c USING (user_id)
)
SELECT
    cohort,
    SUM(start_mrr) AS starting_mrr,
    SUM(churned_mrr) AS churn_mrr,
    SUM(contraction_mrr) AS contraction_mrr,
    (SUM(start_mrr) - SUM(churned_mrr) - SUM(contraction_mrr)) * 100.0 / SUM(start_mrr) AS grr_pct
FROM cohort_now
GROUP BY cohort;

Trend GRR

WITH monthly AS (
    SELECT
        date_month,
        starting_mrr,
        churn_mrr,
        contraction_mrr,
        (starting_mrr - churn_mrr - contraction_mrr) * 100.0 / NULLIF(starting_mrr, 0) AS grr
    FROM mrr_snapshot
    WHERE date_month >= CURRENT_DATE - INTERVAL '12 months'
)
SELECT
    date_month,
    grr,
    AVG(grr) OVER (ORDER BY date_month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS grr_3m_avg
FROM monthly
ORDER BY date_month;

Частые ошибки

Ошибка 1. GRR > 100%. Mathematically impossible. Bug somewhere (likely включил expansion).

Ошибка 2. Window inconsistent. Monthly GRR ≠ annualized. Define one.

Ошибка 3. Cohort vs period. Cohort GRR — single cohort over time. Period GRR — all customers in given month.

Ошибка 4. Contraction definition. Plan tier down vs seat count down vs discount applied. Define.

Ошибка 5. Pause / freeze counted as churn. Customer paused subscription. Counts? Treat consistently.

Связанные темы

• Как посчитать NRR в SQL
• Как посчитать MRR в SQL
• Как посчитать MRR churn в SQL
• Что такое NRR (Net Revenue Retention)

FAQ

Какой GRR ok?

GRR vs NRR — какая важнее?

GRR — pure retention. NRR — overall growth including expansion. Both critical.

GRR падает — что делать?

Investigate churn reasons. Cohort analysis. Customer success investment.

Annualized GRR?

Monthly GRR^12. Or annual GRR = (start - churn - contraction) / start across 12-month window.

Pause/freeze handling?

Pause-then-resume = not churn. Pause permanent — same as churn.