Отчет по когортному анализу: месячная активность пользователей по публикациям и решениям

Создание сообщества Данные и отчетность
,
1

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

:discourse: Для работы этого отчета должен быть включен плагин Discourse Solved.

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

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

Активные пользователи в месяц после регистрации: минимум постов + решений (параметры)

--[params]
-- date :start_date = 2023-01-01
-- int :min_posts_per_month = 1
-- int :min_solutions_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 
),
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
),
solutions_counts AS (
    SELECT
        p.user_id,
        COUNT(p.user_id) as solutions_count,
        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 discourse_solved_solved_topics dsst
    INNER JOIN posts p ON p.id = dsst.answer_post_id
    JOIN topics t ON t.id = p.topic_id
    JOIN users u ON p.user_id = u.id
    WHERE p.created_at >= u.created_at
    GROUP BY months_after_registration, cohort, p.user_id
    HAVING COUNT(p.user_id) >= :min_solutions_per_month
),
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
        sc.cohort,
        sc.months_after_registration,
        COUNT(DISTINCT ac.user_id) AS active_users
    FROM solutions_counts sc
    FULL JOIN activity_counts ac ON sc.user_id = ac.user_id
    AND sc.months_after_registration = ac.months_after_registration
    AND sc.cohort = ac.cohort
    GROUP BY sc.cohort, sc.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 "Joined In", -- Включить год в колонку "Присоединились в"
    users_signed_up AS "Users Signed Up",
    MAX(CASE WHEN months_after_registration = 0 THEN active_users END) AS "Month 1",
    MAX(CASE WHEN months_after_registration = 1 THEN active_users END) AS "Month 2",
    MAX(CASE WHEN months_after_registration = 2 THEN active_users END) AS "Month 3",
    MAX(CASE WHEN months_after_registration = 3 THEN active_users END) AS "Month 4",
    MAX(CASE WHEN months_after_registration = 4 THEN active_users END) AS "Month 5",
    MAX(CASE WHEN months_after_registration = 5 THEN active_users END) AS "Month 6",
    MAX(CASE WHEN months_after_registration = 6 THEN active_users END) AS "Month 7",
    MAX(CASE WHEN months_after_registration = 7 THEN active_users END) AS "Month 8",
    MAX(CASE WHEN months_after_registration = 8 THEN active_users END) AS "Month 9",
    MAX(CASE WHEN months_after_registration = 9 THEN active_users END) AS "Month 10",
    MAX(CASE WHEN months_after_registration = 10 THEN active_users END) AS "Month 11",
    MAX(CASE WHEN months_after_registration = 11 THEN active_users END) AS "Month 12"
FROM final_counts
GROUP BY cohort, users_signed_up
ORDER BY cohort

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

Параметры

Этот отчет использует три параметра:

  • start_date: Дата, с которой начинается отслеживание новых регистраций пользователей.
  • min_posts_per_month: Минимальное количество постов, которое пользователь должен создать за месяц, чтобы считаться активным.
  • min_solutions_per_month: Минимальное количество решений (принятых ответов), которое пользователь должен предоставить за месяц, чтобы считаться активным.

Пользователь должен соответствовать обоим требованиям (min_posts_per_month и min_solutions_per_month), чтобы считаться активным в течение месяца.

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

Предоставленный SQL-запрос использует несколько общих табличных выражений (CTE) для разбивки процесса подсчета активных пользователей в месяц после регистрации на основе критериев минимального количества постов и решений. Ниже приведено пояснение для каждого CTE:

user_cohorts

Это CTE определяет когорты пользователей на основе месяца их регистрации. Для каждого пользователя вычисляется месяц регистрации (cohort) и подсчитывается общее количество пользователей, зарегистрировавшихся в том же месяце. Это помогает понять первоначальный размер каждой когорты.

posts_activity

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

solutions_counts

Это CTE фокусируется на подсчете решений (принятых ответов), предоставленных пользователями. Оно фильтрует посты, помеченные как решения, и подсчитывает их для каждого пользователя, учитывая только посты, созданные после регистрации пользователя. Также вычисляется, сколько месяцев прошло с момента регистрации пользователя для каждого решения. В этот подсчет включаются только пользователи, достигшие или превысившие указанное минимальное количество решений в месяц.

activity_counts

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

active_users

Это CTE объединяет данные из solutions_counts и activity_counts, чтобы выявить активных пользователей — тех, кто соответствует обоим критериям (посты и решения). Оно подсчитывает уникальных пользователей, активных согласно установленным критериям, с группировкой по когорте регистрации и количеству месяцев с момента регистрации.

cohorts_series

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

cohorts

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

cross_join

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

final_counts

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

Финальный SELECT

Финальный оператор SELECT в запросе группирует пользователей в когорты на основе месяца и года их регистрации, а затем вычисляет количество активных пользователей для каждого месяца до года после регистрации. Это делается с помощью комбинации преобразований и условных агрегаций, которые трансформируют данные в формат, где каждая строка соответствует когорте, а каждый столбец представляет количество активных пользователей для каждого месяца после регистрации, от «Месяц 1» до «Месяц 12».

Результаты

Отчет выводит таблицу со следующими столбцами:

  • Присоединились в (Joined In): Месяц и год когорты (когда пользователи зарегистрировались).
  • Зарегистрированные пользователи (Users Signed Up): Общее количество пользователей, зарегистрировавшихся в этой когорте.
  • Месяц 1 — Месяц 12: Количество активных пользователей для каждого месяца после регистрации, до 12 месяцев.

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

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

Полные результаты отчета выведут год данных после start_date

6 лайков