Анализ поведения когорт: LTV и активность

После расчета базового retention следующий уровень когортного анализа — изучение экономического поведения когорт. Ключевая метрика здесь — LTV (Lifetime Value) или пожизненная ценность пользователя, которая показывает, сколько денег в среднем приносит пользователь за все время взаимодействия с продуктом. Анализ LTV по когортам позволяет выявить наиболее ценные сегменты и оптимизировать маркетинговые расходы.

Расчет LTV по когортам

LTV рассчитывается как сумма всех доходов от пользователя с момента регистрации. Для когортного анализа важно отслеживать, как накапливается LTV с течением времени:

WITH cohorts AS (
    SELECT 
        user_id,
        DATE_TRUNC('month', registration_date) AS cohort_month
    FROM users
),
cohort_revenue AS (
    SELECT 
        c.cohort_month,
        c.user_id,
        EXTRACT(YEAR FROM AGE(p.payment_date, c.cohort_month)) * 12 + 
        EXTRACT(MONTH FROM AGE(p.payment_date, c.cohort_month)) AS month_number,
        SUM(p.amount) AS revenue
    FROM cohorts c
    JOIN payments p ON c.user_id = p.user_id
    WHERE p.payment_date >= c.cohort_month
    GROUP BY c.cohort_month, c.user_id, month_number
)
SELECT 
    cohort_month,
    month_number,
    COUNT(DISTINCT user_id) AS paying_users,
    SUM(revenue) AS total_revenue,
    ROUND(AVG(revenue), 2) AS avg_revenue_per_user,
    -- Кумулятивный LTV (накопленный доход на пользователя)
    ROUND(SUM(SUM(revenue)) OVER (
        PARTITION BY cohort_month 
        ORDER BY month_number
    ) / COUNT(DISTINCT user_id) OVER (PARTITION BY cohort_month), 2) AS cumulative_ltv
FROM cohort_revenue
GROUP BY cohort_month, month_number
ORDER BY cohort_month, month_number;

Этот запрос показывает, как растет средний доход на пользователя когорты с каждым месяцем. Оконная функция SUM() OVER используется для расчета кумулятивного LTV.

Сравнение когорт по LTV

Критически важно сравнивать LTV разных когорт на одинаковом возрасте. Например, сравнивать LTV за первые 3 месяца жизни:

WITH cohort_ltv AS (
    -- предыдущий запрос
    ...
)
SELECT 
    cohort_month,
    MAX(CASE WHEN month_number <= 2 THEN cumulative_ltv END) AS ltv_3months,
    MAX(CASE WHEN month_number <= 5 THEN cumulative_ltv END) AS ltv_6months,
    MAX(cumulative_ltv) AS ltv_total
FROM cohort_ltv
GROUP BY cohort_month
ORDER BY cohort_month;

Такое сравнение показывает, улучшается ли монетизация новых когорт по сравнению со старыми.

Анализ активности когорт

Помимо денежных метрик, важно отслеживать поведенческие паттерны — частоту использования продукта, количество сессий, глубину взаимодействия:

WITH cohorts AS (
    SELECT 
        user_id,
        DATE_TRUNC('month', registration_date) AS cohort_month
    FROM users
),
cohort_activity AS (
    SELECT 
        c.cohort_month,
        c.user_id,
        EXTRACT(YEAR FROM AGE(s.session_date, c.cohort_month)) * 12 + 
        EXTRACT(MONTH FROM AGE(s.session_date, c.cohort_month)) AS month_number,
        COUNT(s.session_id) AS sessions_count,
        SUM(s.duration_minutes) AS total_duration
    FROM cohorts c
    JOIN sessions s ON c.user_id = s.user_id
    WHERE s.session_date >= c.cohort_month
    GROUP BY c.cohort_month, c.user_id, month_number
)
SELECT 
    cohort_month,
    month_number,
    COUNT(DISTINCT user_id) AS active_users,
    ROUND(AVG(sessions_count), 2) AS avg_sessions_per_user,
    ROUND(AVG(total_duration), 2) AS avg_duration_per_user
FROM cohort_activity
GROUP BY cohort_month, month_number
ORDER BY cohort_month, month_number;

Этот анализ показывает, как меняется интенсивность использования продукта пользователями когорты с течением времени.

Сегментация когорт по поведению

Мощный подход — разделить когорту на сегменты по уровню активности или ценности:

WITH cohort_metrics AS (
    SELECT 
        c.cohort_month,
        c.user_id,
        COUNT(DISTINCT DATE_TRUNC('month', e.event_date)) AS active_months,
        COALESCE(SUM(p.amount), 0) AS total_spent
    FROM cohorts c
    LEFT JOIN events e ON c.user_id = e.user_id 
        AND e.event_date >= c.cohort_month 
        AND e.event_date < c.cohort_month + INTERVAL '6 months'
    LEFT JOIN payments p ON c.user_id = p.user_id
        AND p.payment_date >= c.cohort_month
        AND p.payment_date < c.cohort_month + INTERVAL '6 months'
    GROUP BY c.cohort_month, c.user_id
)
SELECT 
    cohort_month,
    CASE 
        WHEN active_months >= 5 THEN 'Высокая активность'
        WHEN active_months >= 3 THEN 'Средняя активность'
        WHEN active_months >= 1 THEN 'Низкая активность'
        ELSE 'Неактивные'
    END AS activity_segment,
    CASE
        WHEN total_spent >= 10000 THEN 'VIP'
        WHEN total_spent >= 1000 THEN 'Платящие'
        WHEN total_spent > 0 THEN 'Мелкие покупки'
        ELSE 'Не платили'
    END AS revenue_segment,
    COUNT(*) AS users_count,
    ROUND(AVG(total_spent), 2) AS avg_ltv
FROM cohort_metrics
GROUP BY cohort_month, activity_segment, revenue_segment
ORDER BY cohort_month, activity_segment, revenue_segment;

Такая сегментация позволяет понять структуру когорты: сколько в ней высокоценных пользователей, сколько активных неплатящих, сколько быстро ушедших.

Комплексная когортная панель

Для полноценного анализа объединяют несколько метрик в одном представлении:

WITH cohort_summary AS (
    SELECT 
        c.cohort_month,
        COUNT(DISTINCT c.user_id) AS cohort_size,
        COUNT(DISTINCT CASE WHEN e.event_date >= c.cohort_month + INTERVAL '1 month' 
            THEN c.user_id END) AS retained_month1,
        COUNT(DISTINCT CASE WHEN p.payment_date IS NOT NULL 
            THEN c.user_id END) AS paying_users,
        COALESCE(SUM(p.amount), 0) AS total_revenue,
        COALESCE(AVG(sessions_per_user.sessions), 0) AS avg_sessions
    FROM cohorts c
    LEFT JOIN events e ON c.user_id = e.user_id
    LEFT JOIN payments p ON c.user_id = p.user_id
    LEFT JOIN (
        SELECT user_id, COUNT(*) AS sessions 
        FROM sessions 
        GROUP BY user_id
    ) sessions_per_user ON c.user_id = sessions_per_user.user_id
    GROUP BY c.cohort_month
)
SELECT 
    cohort_month,
    cohort_size,
    ROUND(100.0 * retained_month1 / cohort_size, 2) AS retention_month1,
    ROUND(100.0 * paying_users / cohort_size, 2) AS conversion_rate,
    ROUND(total_revenue / cohort_size, 2) AS ltv_per_user,
    ROUND(avg_sessions, 2) AS avg_sessions_per_user
FROM cohort_summary
ORDER BY cohort_month;

Эта сводная таблица дает полное представление о качестве каждой когорты по ключевым измерениям.

Ключевой вывод: Анализ LTV и поведенческих метрик по когортам превращает простое отслеживание retention в глубокое понимание экономики продукта. Сравнение когорт по множеству измерений — активности, монетизации, структуре пользователей — позволяет выявлять наиболее ценные сегменты, оценивать эффективность изменений продукта и принимать обоснованные решения о распределении ресурсов на привлечение и удержание пользователей.