ダッシュボードレポート - 新規コントリビューター

データとレポート
, ,
1

これは、新規投稿者のためのダッシュボードレポートのSQLバージョンです。

このSQLダッシュボードレポートは、管理者が指定された期間内に各日最初に投稿したユーザーの数を特定することにより、Discourseフォーラムでのユーザーエンゲージメントを明確に把握できるように設計されています。

-- [params]
-- date :start_date = 2023-12-15
-- date :end_date = 2024-01-16

SELECT
  date_trunc('day', p.created_at)::date AS day,
  COUNT(DISTINCT p.user_id) AS new_contributors
FROM
  posts p
INNER JOIN (
  SELECT
    user_id,
    MIN(created_at) as first_post_date
  FROM
    posts
  WHERE deleted_at IS NULL
  GROUP BY
    user_id
) fp ON p.user_id = fp.user_id
WHERE
  p.created_at = fp.first_post_date
  AND p.created_at BETWEEN :start_date AND (:end_date::date + 1)
GROUP BY
  day
ORDER BY
  day

SQLクエリの説明

このレポートは、指定された期間内に各日最初に投稿したユニークユーザーの数を取得します。これは、次の手順を実行することによって行われます。

パラメータ:

  • クエリは、レポートの日付範囲を定義する2つのパラメータ、:start_dateと:end_dateを受け入れます。両方のdateパラメータはYYYY-MM-DDの日付形式を受け入れます。

内部クエリ：各ユーザーの最初の投稿日を決定する

このレポートには、postsテーブルからuser_idと最も早いcreated_atタイムスタンプ（エイリアスfirst_post_date）の2つの列を選択するサブクエリが含まれています。最も早いcreated_atタイムスタンプは、ユーザーの最初の投稿を表します。このサブクエリには、削除されていない投稿のみを考慮するWHERE条件（deleted_at IS NULL）が含まれています。最後に、各ユーザーの最初の投稿のみを確認するために、結果をuser_idでグループ化します。

メインクエリ：最初の投稿を行ったユーザーのカウント

メインクエリは次の操作を実行します。

  • JOIN: メインのpostsテーブル（エイリアスp）は、サブクエリの結果（エイリアスfp）とuser_idで結合され、各投稿と対応するユーザーの最初の投稿が一致します。
  • 日付によるフィルタリング: WHERE句には2つの条件が含まれています。各投稿のcreated_atタイムスタンプをサブクエリのfirst_post_dateと比較して、最初の投稿のみを扱っていることを確認し、created_atタイムスタンプが指定された日付範囲内にあることを確認します（end_dateを完全に含めるために+1日）。
  • 集計: 次に、投稿は日付でグループ化され、時間コンポーネントなしの日付に切り捨てられます（date_trunc('day', p.created_at)::date）。これにより、各日に初めて投稿したユニークユーザーをカウントできます。
  • カウント: COUNT(DISTINCT p.user_id)を使用して、各日に最初に投稿したユニークユーザーの数を取得します。
  • 並べ替え: 結果は日ごとに昇順で並べ替えられます（ORDER BY day）。これにより、ユーザーエンゲージメントの時系列概要が得られます。

最終出力

最終レポートは次の2つの列で構成されます。

  • day: 時間コンポーネントのないユーザーエンゲージメントの日付。
  • new_contributors: 各日のフォーラムで最初の投稿を行ったユニークユーザーの数。

結果例

day new_contributors
2023-12-15 16
2023-12-16 8
2023-12-17 7
2023-12-18 19
2023-12-19 15
「いいね！」 2