13 мая 2026 г.·2 мин чтения

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

Закрепи формулу renewal rate в Карьернике
Запомнить надолго — 5 коротких сессий с задачами на эту тему. Бесплатно
Тренировать renewal rate в Telegram

Содержание:

Зачем Renewal Rate

В B2B SaaS большинство контрактов — annual. Renewal Rate — критическая метрика: 90%+ — sticky продукт. 70% — churn problem.

Формула

Customer Renewal Rate = customers_renewed / customers_eligible_to_renew × 100%
Revenue Renewal Rate = revenue_renewed / revenue_eligible × 100%

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

Данные: contracts(customer_id, start_date, end_date, renewed, renewal_amount).

WITH eligible AS (
    SELECT *
    FROM contracts
    WHERE end_date BETWEEN '2026-01-01' AND '2026-03-31'  -- Q1 expirations
)
SELECT
    COUNT(*) AS eligible_to_renew,
    COUNT(*) FILTER (WHERE renewed) AS renewed,
    COUNT(*) FILTER (WHERE renewed)::NUMERIC * 100 / NULLIF(COUNT(*), 0) AS renewal_rate_pct
FROM eligible;

Gross vs Net Renewal

Gross Renewal Rate (GRR) — пользователи renewed без downgrade. Net Renewal Rate (NRR) — учитывает expansion.

WITH eligible AS (
    SELECT
        customer_id,
        amount AS prev_amount,
        renewal_amount,
        renewed
    FROM contracts
    WHERE end_date BETWEEN '2026-01-01' AND '2026-03-31'
)
SELECT
    SUM(prev_amount) AS total_eligible_revenue,
    SUM(renewal_amount) FILTER (WHERE renewed) AS renewed_revenue,
    SUM(renewal_amount) FILTER (WHERE renewed AND renewal_amount >= prev_amount) AS gross_renewed,
    SUM(prev_amount) FILTER (WHERE renewed AND renewal_amount < prev_amount) AS downgraded_eligible,
    SUM(renewal_amount) FILTER (WHERE renewed)::NUMERIC * 100 / NULLIF(SUM(prev_amount), 0) AS nrr_pct,
    SUM(LEAST(renewal_amount, prev_amount)) FILTER (WHERE renewed)::NUMERIC * 100
        / NULLIF(SUM(prev_amount), 0) AS grr_pct
FROM eligible;

GRR ≤ NRR. Если NRR > 100% — expansion overcomes churn.

Закрепи формулу renewal rate в Карьернике
Запомнить надолго — 5 коротких сессий с задачами на эту тему. Бесплатно
Тренировать renewal rate в Telegram

По сегментам

SELECT
    c.segment,
    COUNT(*) AS contracts_expiring,
    COUNT(*) FILTER (WHERE c.renewed) AS renewed,
    COUNT(*) FILTER (WHERE c.renewed)::NUMERIC * 100 / NULLIF(COUNT(*), 0) AS renewal_rate
FROM contracts c
WHERE c.end_date BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY c.segment
ORDER BY renewal_rate DESC;

Enterprise обычно высший renewal. SMB — самый волатильный.

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

Ошибка 1. Customer vs Revenue Renewal. Юзеры renewed 90% — но downgraded. Revenue renewal 70%.

Ошибка 2. Включать non-eligible. Customer с контрактом expire в Q2 — не часть Q1 cohort. Filter.

Ошибка 3. Auto-renewal. Auto-renewed sans confirmation — counts as renewal. Но real engagement может быть low.

Ошибка 4. Multi-product contracts. Юзер renewed Product A, churned Product B. По customer — renewed? По product — partial.

Ошибка 5. Sample timing. Q1 renewal cohort vs Q3 — может быть different (sezonalitet).

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

FAQ

Какой Renewal Rate ok?

SaaS B2B: 90%+ — good. 85% — acceptable. <80% — issue.

GRR vs NRR — что важнее?

GRR — про retention pure. NRR — про total revenue (включая expansion). VC focus on NRR > 110%.

Renewal Rate vs Retention?

Renewal — discrete (annual contract). Retention — continuous (monthly active).

Auto-renewal effect?

Inflates renewal rate. Track separately «active renewals» (customer-confirmed).

Renewal Rate падает — что делать?

  1. CSM engagement increase. 2) Product feedback collection. 3) Pricing review. 4) Onboarding improvements (long-term).

Ещё по теме