Отчет по когортному анализу — Ежемесячная активность пользователей

[SaraDev (Sara Devlaeminck)]

Это SQL-версия отчёта о когортном анализе активности пользователей для использования в плагине Data Explorer.

Отчёт о когортном анализе предназначен для предоставления администраторам данных об вовлечённости пользователей во времени. Анализируя активность пользователей, сгруппированных по месяцу регистрации (когорты), этот отчёт отслеживает количество активных пользователей каждый месяц после регистрации, которые соответствуют минимальному критерию активности по публикациям.

Этот отчёт может быть ценным ресурсом для понимания удержания пользователей, тенденций вовлечённости, оценки здоровья сообщества и определения эффективности стратегий роста сообщества.

Отчёт о когортном анализе — Активные пользователи в месяц

--[params]
-- date :start_date = 2023-01-01
-- int :min_posts_per_month = 1

WITH user_cohorts AS (
    SELECT
        id AS user_id,
        DATE_TRUNC('month', created_at) AS cohort,
        COUNT(id) OVER (PARTITION BY DATE_TRUNC('month', created_at)) AS users_signed_up
    FROM users
    WHERE created_at >= :start_date -- Используйте параметр start_date для фильтрации пользователей
),
posts_activity AS (
    SELECT
        p.user_id,
        EXTRACT(YEAR FROM AGE(p.created_at, u.created_at)) * 12 + EXTRACT(MONTH FROM AGE(p.created_at, u.created_at)) AS months_after_registration,
        DATE_TRUNC('month', u.created_at) AS cohort
    FROM posts p
    JOIN users u ON p.user_id = u.id
    WHERE p.created_at >= u.created_at
),
activity_counts AS (
    SELECT
        cohort,
        months_after_registration,
        COUNT(user_id) AS posts_count,
        user_id
    FROM posts_activity
    GROUP BY cohort, months_after_registration, user_id
    HAVING COUNT(user_id) >= :min_posts_per_month -- Фильтр пользователей по минимальному количеству постов в месяц
),
active_users AS (
    SELECT
        cohort,
        months_after_registration,
        COUNT(DISTINCT user_id) AS active_users
    FROM activity_counts
    GROUP BY cohort, months_after_registration
),
cohorts_series AS (
    SELECT generate_series AS months_after_registration
    FROM generate_series(0, 11)
),
cohorts AS (
    SELECT
        cohort,
        MAX(users_signed_up) AS users_signed_up -- Агрегация для получения общего числа зарегистрированных пользователей в каждой когорте
    FROM user_cohorts
    GROUP BY cohort
),
cross_join AS (
    SELECT
        c.cohort,
        c.users_signed_up,
        cs.months_after_registration
    FROM cohorts c
    CROSS JOIN cohorts_series cs
),
final_counts AS (
    SELECT
        cj.cohort,
        cj.users_signed_up,
        cj.months_after_registration,
        COALESCE(au.active_users, 0) AS active_users
    FROM cross_join cj
    LEFT JOIN active_users au ON au.cohort = cj.cohort AND au.months_after_registration = cj.months_after_registration
)
SELECT
    TO_CHAR(cohort, 'Mon YYYY') AS "Присоединились в", -- Включить год в столбец "Присоединились в"
    users_signed_up AS "Зарегистрировалось пользователей",
    MAX(CASE WHEN months_after_registration = 0 THEN active_users END) AS "Месяц 1",
    MAX(CASE WHEN months_after_registration = 1 THEN active_users END) AS "Месяц 2",
    MAX(CASE WHEN months_after_registration = 2 THEN active_users END) AS "Месяц 3",
    MAX(CASE WHEN months_after_registration = 3 THEN active_users END) AS "Месяц 4",
    MAX(CASE WHEN months_after_registration = 4 THEN active_users END) AS "Месяц 5",
    MAX(CASE WHEN months_after_registration = 5 THEN active_users END) AS "Месяц 6",
    MAX(CASE WHEN months_after_registration = 6 THEN active_users END) AS "Месяц 7",
    MAX(CASE WHEN months_after_registration = 7 THEN active_users END) AS "Месяц 8",
    MAX(CASE WHEN months_after_registration = 8 THEN active_users END) AS "Месяц 9",
    MAX(CASE WHEN months_after_registration = 9 THEN active_users END) AS "Месяц 10",
    MAX(CASE WHEN months_after_registration = 10 THEN active_users END) AS "Месяц 11",
    MAX(CASE WHEN months_after_registration = 11 THEN active_users END) AS "Месяц 12"
FROM final_counts
GROUP BY cohort, users_signed_up
ORDER BY cohort

Пояснение к SQL-запросу

Отчёт работает путём сегментации пользователей на когорты на основе месяца их присоединения. Затем он отслеживает эти когорты, чтобы увидеть, сколько пользователей остаются активными в последующие месяцы, исходя из заданного минимального количества публикаций в месяц.

Параметры

Этот отчёт имеет два параметра:

• start_date: Начальная дата, с которой пользователи учитываются для когортного анализа. В отчёт включаются пользователи, зарегистрировавшиеся после этой даты.
• min_posts_per_month: Минимальное количество публикаций, которое пользователь должен сделать в месяц, чтобы считаться активным в этом месяце.

CTE (Общие табличные выражения)

Отчёт о когортном анализе использует несколько общих табличных выражений (CTE) для организации и обработки данных для анализа. Каждое CTE выполняет конкретную задачу в общем запросе, опираясь на предыдущие, чтобы в конечном итоге сформировать итоговый отчёт. Ниже приведено описание работы каждого CTE:

1. user_cohorts

Это CTE определяет когорты на основе месяца присоединения пользователей. Для каждого пользователя вычисляется когорта, к которой он принадлежит, путём усечения временной метки created_at до месяца. Также подсчитывается количество пользователей, зарегистрировавшихся в каждой когорте.

• Ключевые операции:
  • DATE_TRUNC('month', created_at) AS cohort: Усечение временной метки created_at до месячной точности, что фактически группирует пользователей по месяцу регистрации.
  • COUNT(id) OVER (PARTITION BY DATE_TRUNC('month', created_at)): Подсчёт количества пользователей в каждой когорте.

2. posts_activity

Это CTE отслеживает активность публикаций пользователей относительно даты их регистрации. Оно соединяет таблицы posts и users, чтобы связать каждую публикацию с пользователем, который её создал, и вычисляет, сколько месяцев прошло с момента регистрации пользователя на момент каждой публикации.

• Ключевые операции:
  • EXTRACT(YEAR FROM AGE(p.created_at, u.created_at)) * 12 + EXTRACT(MONTH FROM AGE(p.created_at, u.created_at)): Вычисление количества месяцев, прошедших с момента регистрации пользователя для каждой публикации.
  • DATE_TRUNC('month', u.created_at) AS cohort: Определение когорты пользователя на основе месяца регистрации.

3. activity_counts

Это CTE агрегирует данные о публикациях из posts_activity, чтобы подсчитать количество публикаций, сделанных каждым пользователем в каждый месяц после регистрации. Оно фильтрует эти подсчёты, включая только тех пользователей, которые соответствуют минимальной активности по публикациям, указанной параметром min_posts_per_month.

• Ключевые операции:
  • GROUP BY cohort, months_after_registration, user_id: Группировка данных по когорте, месяцам после регистрации и ID пользователя для подготовки к подсчёту публикаций.
  • HAVING COUNT(user_id) >= :min_posts_per_month: Фильтрация сгруппированных данных, чтобы включить только тех пользователей, которые сделали как минимум минимальное количество публикаций в месяц.

4. active_users

Это CTE дополнительно агрегирует данные из activity_counts, чтобы подсчитать количество уникальных активных пользователей в каждой когорте для каждого месяца после регистрации.

• Ключевые операции:
  • COUNT(DISTINCT user_id) AS active_users: Подсчёт количества уникальных активных пользователей в каждой когорте для каждого месяца после регистрации.

5. cohorts_series

Это CTE генерирует последовательность целых чисел от 0 до 11, представляющую месяцы после регистрации. Эта последовательность используется для обеспечения того, чтобы итоговый отчёт включал все месяцы до 12 для каждой когорты, даже если для некоторых месяцев нет данных об активности.

• Ключевые операции:
  • generate_series(0, 11): Генерация последовательности целых чисел от 0 до 11.

6. cohorts

Это CTE агрегирует данные из user_cohorts, чтобы получить общее количество зарегистрированных пользователей для каждой когорты.

• Ключевые операции:
  • MAX(users_signed_up) AS users_signed_up: Агрегация общего количества зарегистрированных пользователей для каждой когорты.

7. cross_join

Это CTE выполняет перекрёстное соединение между cohorts и cohorts_series, чтобы создать сетку всех возможных комбинаций когорт и месяцев после регистрации. Это гарантирует, что итоговый отчёт будет содержать строки для каждого месяца каждой когорты, что облегчает расчёт активных пользователей в месяц.

8. final_counts

Это CTE объединяет данные из cross_join и active_users, чтобы подсчитать итоговое количество активных пользователей для каждой когорты в каждый месяц после регистрации. Оно использует левое соединение (left join), чтобы гарантировать включение всех комбинаций когорт и месяцев, даже если для некоторых нет активных пользователей.

• Ключевые операции:
  • COALESCE(au.active_users, 0) AS active_users: Обеспечивает отображение 0 активных пользователей для комбинаций без какой-либо активности, вместо того чтобы оставлять их пустыми.

Итоговый оператор SELECT вне CTE форматирует и представляет эти данные, показывая количество зарегистрированных пользователей и количество активных пользователей для каждого месяца после регистрации для каждой когорты.

Результаты

Отчёт генерирует таблицу со следующими столбцами:

• Присоединились в: Месяц и год создания когорты, указывающие, когда эти пользователи зарегистрировались.
• Зарегистрировалось пользователей: Общее количество пользователей, зарегистрировавшихся в этой когорте.
• Месяц 1 — Месяц 12: Каждый из этих столбцов представляет количество активных пользователей для когорты в каждый последующий месяц после присоединения, до 12 месяцев. Активным пользователем считается тот, кто сделал как минимум минимальное количество публикаций, указанное параметром min_posts_per_month.

Пример результатов

Присоединились в	Зарегистрировалось пользователей	Месяц 1	Месяц 2	Месяц 3	Месяц 4	Месяц 5	Месяц 6	Месяц 7	Месяц 8	Месяц 9	Месяц 10	Месяц 11	Месяц 12
Янв 2023	120	40	8	4	3	3	3	4	3	2	1	1	4
Фев 2023	119	40	7	5	3	2	2	7	2	2	2	1	1
…	…	…	…	…	…	…	…	…	…	…	…	…	…

Полные результаты отчёта будут содержать год данных после даты start_date.