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

Зачем ARR Growth

В SaaS ARR Growth — главная top-level метрика. Investor смотрит: ARR YoY +60% — здоровый. +20% — средне. +10% — слабо. ARR Growth декомпозируется на 4 компонента: new + expansion - churn - contraction.

Формула

ARR_end = ARR_start + New ARR + Expansion ARR - Churn ARR - Contraction ARR
ARR Growth (%) = (ARR_end - ARR_start) / ARR_start × 100%

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

WITH arr_snapshots AS (
    SELECT
        DATE_TRUNC('quarter', DATE) AS quarter,
        SUM(mrr) * 12 AS arr
    FROM subscriptions
    WHERE status = 'active'
    GROUP BY 1
)
SELECT
    quarter,
    arr,
    LAG(arr) OVER (ORDER BY quarter) AS prev_arr,
    (arr::NUMERIC / NULLIF(LAG(arr) OVER (ORDER BY quarter), 0) - 1) * 100 AS qoq_growth_pct,
    (arr::NUMERIC / NULLIF(LAG(arr, 4) OVER (ORDER BY quarter), 0) - 1) * 100 AS yoy_growth_pct
FROM arr_snapshots
ORDER BY quarter;

Components: new, expansion, churn, contraction

SELECT
    DATE_TRUNC('quarter', event_date) AS quarter,
    SUM(CASE WHEN type = 'new' THEN amount END) * 12 AS new_arr,
    SUM(CASE WHEN type = 'expansion' THEN amount END) * 12 AS expansion_arr,
    SUM(CASE WHEN type = 'churn' THEN amount END) * 12 AS churn_arr,
    SUM(CASE WHEN type = 'contraction' THEN amount END) * 12 AS contraction_arr,
    (SUM(CASE WHEN type = 'new' THEN amount END)
     + SUM(CASE WHEN type = 'expansion' THEN amount END)
     - SUM(CASE WHEN type = 'churn' THEN amount END)
     - SUM(CASE WHEN type = 'contraction' THEN amount END)) * 12 AS net_new_arr
FROM mrr_movements
GROUP BY 1
ORDER BY 1;

Net New ARR

Net New ARR — главный sales-метрика SaaS. Сколько ARR команда заработала net (с учётом churn).

SELECT
    DATE_TRUNC('quarter', event_date) AS quarter,
    SUM(CASE WHEN type IN ('new', 'expansion') THEN amount END) * 12 AS added,
    SUM(CASE WHEN type IN ('churn', 'contraction') THEN amount END) * 12 AS lost,
    (SUM(CASE WHEN type IN ('new', 'expansion') THEN amount END)
     - SUM(CASE WHEN type IN ('churn', 'contraction') THEN amount END)) * 12 AS net_new_arr
FROM mrr_movements
GROUP BY 1
ORDER BY 1;

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

Ошибка 1. ARR vs Booking. Booking — total value подписки (3-year contract = 3 × annual). ARR — annualized current MRR. Не путайте.

Ошибка 2. Multi-currency. ARR в долларах vs рублях — нужна conversion на single FX rate.

Ошибка 3. Discount handling. Подписка на $100/мес со скидкой $20 → ARR $960, не $1200.

Ошибка 4. Включать non-recurring. One-time fees, setup fees — не часть ARR. Только recurring.

Ошибка 5. ARR snapshot timing. ARR на конец квартала vs середину vs ARR average — разные числа.

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

• Как посчитать MRR в SQL
• Как посчитать MRR Churn в SQL
• Как посчитать NRR в SQL
• Как посчитать Rule of 40 в SQL

FAQ

ARR Growth 30% — норма?

Pre-PMF — 100%+ YoY. Series A — 100-300%. Series B+ — 50-100%. Public SaaS — 20-50%.

ARR Growth slowing — плохо?

Закон больших чисел: чем больше base, тем труднее % growth. Watch absolute Net New ARR.

Net New ARR vs Gross New ARR?

Gross New — только new + expansion. Net New — minus churn / contraction.

Negative Net New ARR?

Churn > new. Очень плохой сигнал.

ARR Growth quarterly vs annual?

Annual — фокус инвесторов. Quarterly — operational.