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

Зачем Active Cohort

Cohort может быть «зарегистрирована» но неактивна. Active Cohort показывает фактически активных. Если active cohort 30% — большинство юзеров не используют продукт после регистрации.

Формула

Active Cohort (m, period) = unique users from cohort m, active in period

«Active» = совершил минимум одно key action.

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

WITH cohort AS (
    SELECT
        user_id,
        DATE_TRUNC('month', signup_date) AS cohort_month
    FROM users
),
activity AS (
    SELECT
        user_id,
        DATE_TRUNC('month', event_date) AS active_month
    FROM events
    WHERE event_type IN ('key_action_1', 'key_action_2')  -- key actions
    GROUP BY user_id, DATE_TRUNC('month', event_date)
)
SELECT
    c.cohort_month,
    a.active_month,
    COUNT(DISTINCT c.user_id) AS active_users
FROM cohort c
JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort_month, a.active_month
ORDER BY c.cohort_month, a.active_month;

Сравнение cohorts

WITH cohort_size AS (
    SELECT
        DATE_TRUNC('month', signup_date) AS cohort_month,
        COUNT(*) AS total_signups
    FROM users
    GROUP BY 1
),
cohort_active AS (
    SELECT
        c.cohort_month,
        EXTRACT(YEAR FROM AGE(DATE_TRUNC('month', e.event_date), c.cohort_month)) * 12
            + EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', e.event_date), c.cohort_month)) AS m_offset,
        COUNT(DISTINCT c.user_id) AS active
    FROM (
        SELECT user_id, DATE_TRUNC('month', signup_date) AS cohort_month FROM users
    ) c
    JOIN events e ON e.user_id = c.user_id
    GROUP BY 1, 2
)
SELECT
    ca.cohort_month,
    ca.m_offset,
    ca.active,
    cs.total_signups,
    ca.active::NUMERIC * 100 / NULLIF(cs.total_signups, 0) AS active_rate_pct
FROM cohort_active ca
JOIN cohort_size cs ON cs.cohort_month = ca.cohort_month
ORDER BY ca.cohort_month, ca.m_offset;

Если active rate cohort 2026-03 ниже than cohort 2026-01 на M3 — что-то изменилось в активации.

Тренды

SELECT
    cohort_month,
    MAX(CASE WHEN m_offset = 1 THEN active_rate_pct END) AS m1_active,
    MAX(CASE WHEN m_offset = 3 THEN active_rate_pct END) AS m3_active,
    MAX(CASE WHEN m_offset = 6 THEN active_rate_pct END) AS m6_active
FROM (
    -- subquery from above
) x
GROUP BY cohort_month
ORDER BY cohort_month;

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

Ошибка 1. Define «active» вагуально. «Открыл app» vs «совершил purchase» — разные. Зафиксируйте в команде.

Ошибка 2. Не учитывать lag в data. Cohort 2026-03 в M2 ещё нет full data (если data lag 1 неделя).

Ошибка 3. Comparing immature cohorts. Cohort января 2026 имеет M3 data. Cohort февраля — нет. Compare same window.

Ошибка 4. Бот activity. Боты «активны». Inflate.

Ошибка 5. Cross-device. Юзер active на двух devices = 1 user (если unified). Без unification — overcount.

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

• Как посчитать cohort retention в SQL
• Как посчитать DAU в SQL
• Как посчитать MAU в SQL
• Как посчитать activation rate в SQL

FAQ

Active Cohort vs Retention?

Retention — fraction still active. Active Cohort — absolute count.

Какой «active» в SaaS?

Login + 1 key action. Не просто login (passive open).

Active Cohort растёт MoM — хорошо?

Может быть просто из-за роста cohort size. Watch active rate %.

Compare cohorts разной зрелости?

Не cross-cohort на разной зрелости. Use same M-offset.

Cohort выпадает из active — re-activated?

Юзер был active в M1, не был в M2, был в M3. В стандартной логике re-engaged.