コホート分析レポート - 月次ユーザーアクティビティ

データとレポート
1

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

コホート分析レポートは、管理者に時間の経過に伴うユーザーエンゲージメントに関する洞察を提供することを目的としています。登録月（コホート）別にグループ化されたユーザーのアクティビティを分析することにより、このレポートは、登録後の各月で、最低限の投稿アクティビティ基準を満たすアクティブユーザー数を追跡します。

このレポートは、ユーザー保持率、エンゲージメントの傾向、コミュニティの健全性の評価、およびコミュニティ成長戦略の有効性を特定するための貴重なリソースとなり得ます。

コホート分析レポート - 月別アクティブユーザー数

--[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 -- 開始日パラメータを使用してユーザーをフィルタリングします
),
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 "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 クエリの説明

このレポートは、ユーザーを参加月別にコホートにセグメント化することによって機能します。次に、これらのコホートを追跡して、月あたりの最低投稿数に基づいて、後続の月で何人のユーザーがアクティブであり続けるかを確認します。

パラメータ

このレポートには 2 つのパラメータがあります。

  • start_date: コホート分析の対象となるユーザーの開始日。この日以降に参加したユーザーがレポートに含まれます。
  • min_posts_per_month: その月のアクティブユーザーと見なされるために、ユーザーがその月に投稿する必要がある最低投稿数。

CTE

コホート分析レポートは、分析のためのデータを整理および処理するために、いくつかの共通テーブル式 (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 からのデータを結合して、登録後の各月で各コホートのアクティブユーザーの最終的な数を計算します。左結合を使用して、アクティブユーザーがいない組み合わせでもすべての組み合わせが含まれるようにします。

  • 主な操作:
    • COALESCE(au.active_users, 0) AS active_users: アクティビティがない組み合わせに対して、レポートに空白ではなく 0 のアクティブユーザー数が表示されるようにします。

CTE 外の最終的な SELECT ステートメントは、このデータをフォーマットして表示し、各コホートの登録後の各月のアクティブユーザー数とサインアップしたユーザー数を示します。

結果

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

  • Joined In: コホートが作成された月と年。これらのユーザーがいつサインアップしたかを示します。
  • Users Signed Up: そのコホートにサインアップしたユーザーの総数。
  • Month 1 から Month 12: これらの各列は、参加後の各月におけるコホートのアクティブユーザー数を示し、最大 12 か月までです。アクティブユーザーとは、min_posts_per_month パラメータで指定された最低投稿数を満たしているユーザーと定義されます。

結果例

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 120 40 8 4 3 3 3 4 3 2 1 1 4
Feb 2023 119 40 7 5 3 2 2 7 2 2 2 1 1
… … … … … … … … … … … … … …

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

「いいね！」 3