Cohort Analysis Report - 月間ユーザーアクティビティ（投稿・解決策別）

SaraDev · 2024 年 3 月 28 日午後 8:19

これは、Data Explorer プラグイン内で使用するための、ユーザーアクティビティのコホート分析レポートの SQL バージョンです。

このレポートには、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", -- Include the year in the Joined In column
    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 クエリの説明

パラメータ

このレポートでは 3 つのパラメータを使用します。

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 は、cohorts CTE と cohorts_series CTE の間でクロス結合を実行します。これにより、各コホートがサインアップ後の各月で表されるようになり、最終ステップでの各月の実際のアクティブユーザーの計算が容易になります。

final_counts

この CTE は、前のすべての CTE を結合して、各コホートのサインアップ後の各月のアクティブユーザーの最終カウントを計算します。左結合を使用して、active_users CTE からのアクティブユーザーを、cross_join CTE で生成されたコホートと月に一致させます。これにより、各コホートと月のペアにアクティブユーザーのカウントが設定され、そのペアのアクティブユーザーがいない場合は 0 にデフォルト設定されます。

最終 SELECT

クエリの最終 SELECT ステートメントは、ユーザーをサインアップ月と年を基準にコホートにグループ化し、サインアップ後最大 1 年間の各月のアクティブユーザー数を計算します。これは、変換と条件付き集計の組み合わせによって行われ、データがピボットされ、各行がコホートに対応し、各列が「Month 1」から「Month 12」までのサインアップ後の各月の実際のアクティブユーザー数を示す形式になります。

結果

レポートは、次の列を持つテーブルを出力します。

Joined In: コホートの月と年（ユーザーがサインアップした時期）。
Users Signed Up: そのコホートでサインアップしたユーザーの総数。
Month 1 から Month 12: サインアップ後最大 12 か月間の各月の実際のアクティブユーザー数。

結果例

Joined In	Users Signed Up	Month 1	Month 2	Month 3	Month 4	Month 5	Month 6	Month 7	Month 8	Month 9	Month 10	Month 11	Month 12
Jan 2023	50	40	8	4	3	3	3	4	3	2	1	1	4
Feb 2023	63	40	7	5	3	2	2	7	2	2	2	1	1
…	…	…	…	…	…	…	…	…	…	…	…	…	…

レポートの完全な結果は、start_date の 1 年間のデータを出力します。

トピック		返信	表示
Cohort Analysis Report - Monthly User Activity Data & reporting sql-query	0	302	2024 年 3 月 28 日
Users who’ve become active Data & reporting sql-query	0	126	2019 年 7 月 12 日
How many active users last month? Data & reporting	15	4709	2024 年 6 月 8 日
Active users for specific months Data & reporting sql-query	6	1612	2024 年 6 月 8 日
Retrospectively pulling number of users each calendar month Data & reporting	9	177	2024 年 11 月 12 日

Cohort Analysis Report - 月間ユーザーアクティビティ（投稿・解決策別）

SQL クエリの説明

パラメータ

CTE

結果

結果例

関連トピック