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

Зачем Cohort Revenue

Customer retention = 70%. Звучит ok. Cohort revenue retention = 130% — значит retained customers платят больше со временем (expansion). Это negative net churn, SaaS-золото.

Формула

Cohort Revenue (m, period) = SUM(revenue) пользователей cohort m, активных в период

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

Данные: users(user_id, signup_date), transactions(user_id, amount, paid_at).

WITH cohort AS (
    SELECT
        user_id,
        DATE_TRUNC('month', signup_date) AS cohort_month
    FROM users
),
period_revenue AS (
    SELECT
        c.cohort_month,
        DATE_TRUNC('month', t.paid_at) AS period,
        SUM(t.amount) AS revenue
    FROM cohort c
    JOIN transactions t ON t.user_id = c.user_id
    WHERE t.status = 'success'
    GROUP BY c.cohort_month, DATE_TRUNC('month', t.paid_at)
)
SELECT
    cohort_month,
    period,
    EXTRACT(YEAR FROM AGE(period, cohort_month)) * 12
        + EXTRACT(MONTH FROM AGE(period, cohort_month)) AS month_offset,
    revenue
FROM period_revenue
ORDER BY cohort_month, period;

Revenue retention curves

WITH cohort_initial AS (
    SELECT
        c.cohort_month,
        c.user_id,
        SUM(t.amount) FILTER (WHERE DATE_TRUNC('month', t.paid_at) = c.cohort_month) AS m0_revenue
    FROM (
        SELECT user_id, DATE_TRUNC('month', signup_date) AS cohort_month FROM users
    ) c
    JOIN transactions t ON t.user_id = c.user_id
    WHERE t.status = 'success'
    GROUP BY c.cohort_month, c.user_id
),
period_revenue AS (
    SELECT
        c.cohort_month,
        DATE_TRUNC('month', t.paid_at) AS period,
        SUM(t.amount) AS revenue
    FROM (
        SELECT user_id, DATE_TRUNC('month', signup_date) AS cohort_month FROM users
    ) c
    JOIN transactions t ON t.user_id = c.user_id
    WHERE t.status = 'success'
    GROUP BY c.cohort_month, DATE_TRUNC('month', t.paid_at)
)
SELECT
    p.cohort_month,
    p.period,
    EXTRACT(YEAR FROM AGE(p.period, p.cohort_month)) * 12
        + EXTRACT(MONTH FROM AGE(p.period, p.cohort_month)) AS m_offset,
    p.revenue,
    p.revenue * 100.0 / NULLIF(SUM(ci.m0_revenue), 0) AS pct_of_initial
FROM period_revenue p
LEFT JOIN cohort_initial ci ON ci.cohort_month = p.cohort_month
GROUP BY p.cohort_month, p.period, p.revenue;

Если pct_of_initial > 100% on M6 — expansion overcomes churn. Awesome.

По cohort × period

Pivot для дашборда:

WITH cohorts AS (
    SELECT
        DATE_TRUNC('month', u.signup_date) AS cohort_month,
        EXTRACT(YEAR FROM AGE(DATE_TRUNC('month', t.paid_at), DATE_TRUNC('month', u.signup_date))) * 12
        + EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', t.paid_at), DATE_TRUNC('month', u.signup_date)))::INT AS m_offset,
        SUM(t.amount) AS revenue
    FROM users u
    JOIN transactions t ON t.user_id = u.user_id AND t.status = 'success'
    GROUP BY 1, 2
)
SELECT
    cohort_month,
    SUM(CASE WHEN m_offset = 0 THEN revenue END) AS m0,
    SUM(CASE WHEN m_offset = 1 THEN revenue END) AS m1,
    SUM(CASE WHEN m_offset = 3 THEN revenue END) AS m3,
    SUM(CASE WHEN m_offset = 6 THEN revenue END) AS m6,
    SUM(CASE WHEN m_offset = 12 THEN revenue END) AS m12
FROM cohorts
GROUP BY cohort_month
ORDER BY cohort_month;

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

Ошибка 1. EXTRACT(MONTH FROM AGE) без YEAR×12. Возвращает 0-11. Используйте YEAR*12 + MONTH.

Ошибка 2. Cohort с малым sample. n=10 cohort даёт волатильность. Considerate threshold.

Ошибка 3. Compare partial cohorts. Cohort февраля 2026 ещё не имеет M12. Filter period <= CURRENT_DATE - INTERVAL '12 months'.

Ошибка 4. Average revenue per user vs total. ARPU per cohort vs total revenue per cohort — разные метрики.

Ошибка 5. Скрытое expansion vs churn. Total cohort revenue может расти потому что existing users pay больше, или потому что non-customers становятся customers (delayed activation). Distinguish.

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

• Как посчитать cohort retention в SQL
• Как посчитать MRR в SQL
• Как посчитать NRR в SQL
• Как посчитать LTV в SQL

FAQ

Cohort revenue vs NRR?

NRR — обычно ratio (M-current / M-0). Cohort revenue — absolute numbers.

Что значит cohort revenue растёт?

Либо больше users заплатили, либо те же users заплатили больше. Distinguish.

Cohort window — какой?

Cohort by month — стандарт. По cohort × month_offset показывает evolution.

Cohort revenue падает — что делать?

Декомпозиция: churn rate? Decreased AOV? Меньше paying users in cohort?

Cohort revenue в e-com vs SaaS?

В e-com — про repeat purchases. В SaaS — про subscription longevity + upsell.