1日あたりの読者とアクティブユーザー

データとレポート
1

このSQLレポートは、指定された期間における読者（投稿のみを行うユーザー）とアクティブユーザー（その日に少なくとも1つの投稿を作成したユーザー）の毎日のカウントを提供し、オプションでカテゴリIDによるフィルタリングも可能です。

このレポートは、管理者がコミュニティの活動性を、コンテンツ作成だけでなくコンテンツ消費の観点からも理解するのに役立ちます。この二重の視点により、ユーザーエンゲージメントのより詳細な理解が可能になり、読書活動や投稿活動が多い日をハイライトします。

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-02-01
-- int :reader_post_read_count = 10
-- null int :category_id

WITH posts_by_user AS (
    SELECT
        p.user_id,
        p.created_at::date AS post_date,
        p.topic_id,
        COUNT(*) AS posts
    FROM posts p
    WHERE p.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY p.user_id, post_date, p.topic_id
), 

posts_read_by_user AS (
    SELECT
        uv.user_id,
        uv.visited_at::date AS visit_date,
        SUM(uv.posts_read) AS posts_read
    FROM user_visits uv
    LEFT JOIN group_users gu ON gu.user_id = uv.user_id
    LEFT JOIN groups g ON g.id = gu.group_id
    LEFT JOIN posts p ON p.user_id = uv.user_id AND p.created_at BETWEEN :start_date AND :end_date
    LEFT JOIN topics t ON t.id = p.topic_id AND t.category_id = :category_id
    WHERE uv.visited_at BETWEEN :start_date AND :end_date
    AND (:category_id IS NULL OR t.category_id IS NOT NULL)
    GROUP BY uv.user_id, visit_date
),

active_users_per_day AS (
    SELECT
        pbu.post_date,
        COUNT(DISTINCT pbu.user_id) AS active_user_count
    FROM posts_by_user pbu
    JOIN topics t ON pbu.topic_id = t.id
    WHERE posts > 0
    AND (:category_id IS NULL OR t.category_id = :category_id)
    GROUP BY post_date
),

readers_per_day AS (
    SELECT
        prbu.visit_date,
        COUNT(DISTINCT prbu.user_id) AS reader_count
    FROM posts_read_by_user prbu
    LEFT JOIN posts_by_user pbu ON prbu.user_id = pbu.user_id AND prbu.visit_date = pbu.post_date
    WHERE pbu.posts IS NULL
    AND prbu.posts_read > :reader_post_read_count
    GROUP BY prbu.visit_date
)

SELECT
    rpd.visit_date,
    rpd.reader_count,
    COALESCE(aupd.active_user_count, 0) AS active_user_count
FROM readers_per_day rpd
LEFT JOIN active_users_per_day aupd ON rpd.visit_date = aupd.post_date
ORDER BY rpd.visit_date

SQLクエリの説明

パラメータ

  • start_date および end_date: 分析の期間を定義します。
  • reader_post_read_count: 誰かを「読者」と見なすためのしきい値を、読んだ投稿数に基づいて設定します。
  • category_id: オプション。指定された場合、レポートは単一のカテゴリに焦点を当て、より的を絞った分析を可能にします。

CTE（共通テーブル式）

  1. posts_by_user: このCTEは、指定された期間内に各ユーザーが各日に作成した投稿数を特定します。これは、どのユーザーがコンテンツに貢献しているか、およびそのアクティビティが時間とともにどのように分散しているかを理解するために重要です。
  2. posts_read_by_user: このCTEは、指定された期間内に各ユーザーが各日に読んだ投稿数を計算します。ユーザーの訪問データを利用してコンテンツ消費を推定し、エンゲージメントの重要な指標となります。
  3. active_users_per_day: posts_by_user CTEに基づいて、このCTEは1日あたり少なくとも1回投稿したユニークユーザー数をカウントします。これは、アクティブなコンテンツ作成者の毎日のスナップショットを提供します。
  4. readers_per_day: posts_read_by_user CTEを使用して、このCTEは、投稿せずに指定された数の投稿（reader_post_read_count）以上を読んだユーザーを特定します。これは、読むことによってエンゲージするサイレントマジョリティを浮き彫りにします。

結果

最終的な出力は、ユーザーエンゲージメントの毎日の概要を示し、各日の読者数とアクティブユーザー数（投稿したユーザー）を詳述します。

結果例

visit_date reader_count active_user_count
2024-01-02 150 25
2024-01-03 175 30
2024-01-04 160 20
2024-01-05 180 22
… … …
「いいね！」 4