ダッシュボードレポート - DAU/MAU

データとレポート
, ,
1

これはDAU/MAUのダッシュボードレポートのSQLバージョンです。

このレポートは、指定された期間における日次アクティブユーザー（DAU）と月次アクティブユーザー（MAU）の比率の詳細な分析を提供します。DAU/MAU比率は、1日のユニークアクティブユーザー数と30日間のユニークアクティブユーザー数を比較することで、ユーザーエンゲージメントを測定する主要業績評価指標（KPI）です。

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

WITH date_series AS (
  SELECT generate_series(:start_date::date, :end_date::date, '1 day'::interval) AS date
),

DAU AS (
  SELECT date(date) AS day, COUNT(DISTINCT user_id) AS dau_count
  FROM user_visits
  JOIN date_series ON date(user_visits.visited_at) = date_series.date
  GROUP BY date
),
MAU AS (
  SELECT date_series.date, COUNT(DISTINCT user_visits.user_id) AS mau_count
  FROM user_visits
  JOIN date_series ON date(user_visits.visited_at) >= date_series.date - INTERVAL '30 days'
  AND date(user_visits.visited_at) <= date_series.date
  GROUP BY date_series.date
)
SELECT
  DAU.day,
  (DAU.dau_count::FLOAT / NULLIF(MAU.mau_count, 0)) * 100 AS "DAU/MAU",
  DAU.dau_count,
  MAU.mau_count
FROM DAU
JOIN MAU ON DAU.day = MAU.date
ORDER BY DAU.day

SQLクエリの説明

SQLクエリは、共通テーブル式（CTE）を使用して、DAU用とMAU用の2つのデータセットを作成します。次に、これらのデータセットを結合して、指定された日付範囲内の各日のDAU/MAU比率を計算します。

パラメータ

クエリは、:start_dateと:end_dateの2つのパラメータを受け入れ、ユーザーがレポートの日付範囲を指定できるようにします。両方のdateパラメータは、YYYY-MM-DDの日付形式を受け入れます。

CTE

  • date_series: date_series CTEは、:start_dateから:end_dateまでの日付のシリーズを、各日付の間に1日の間隔で生成します。このシリーズは、一部の日付にユーザーアクティビティがない場合でも、レポートに指定された範囲内のすべての日が含まれるようにするために使用されます。
  • DAU: DAU CTEは、各日（dau_count）にサイトを訪問したユニークユーザー数を計算します。これは、user_visitsテーブルとdate_series CTEを、訪問日がシリーズの日付と一致するという条件で結合することによって行われます。結果は日付ごとにグループ化され、各日のユニークユーザー数を取得します。
  • MAU: MAU CTEは、date_series内の各日付に先行する30日間にサイトを訪問したユニークユーザー数を計算します。これは、user_visitsテーブルとdate_series CTEを結合し、訪問日がシリーズの日付から30日前から、その日付まで（その日付を含む）の間にあることによって行われます。結果は日付ごとにグループ化され、各30日間の期間のユニークユーザー数を取得します。

最終選択

クエリの最後の部分は、DAU数をMAU数で割り、100を掛けてパーセンテージを取得することで、各日のDAU/MAU比率を計算します。参照用にDAUとMAUのカウントも選択します。NULLIF関数は、ある日付のMAUがゼロの場合に発生するゼロ除算を回避するために使用されます。結果は日付で並べ替えられ、ユーザーエンゲージメントの時系列ビューを提供します。

結果例

day DAU/MAU dau_count mau_count
2023-11-19 15.3191489 360 2350
2023-11-20 23.9222316 566 2366
2023-11-21 23.5564854 563 2390
2023-11-22 23.0992937 556 2407
2023-11-23 19.3949441 468 2413
… … … …
「いいね！」 2
2

こちらに、どなたかの参考になるかもしれない、MAU（月間アクティブユーザー数）の統計のみを表示する修正版レポートを追加します。

MAU（月間アクティブユーザー数）

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01

WITH date_series AS (
  SELECT generate_series(:start_date::date, :end_date::date, '1 month'::interval) AS month_start
),

MAU AS (
  SELECT
    date_trunc('month', date_series.month_start) AS month,
    COUNT(DISTINCT user_visits.user_id) AS mau_count
  FROM user_visits
  JOIN date_series
    ON date(user_visits.visited_at) >= date_series.month_start - INTERVAL '1 month'
   AND date(user_visits.visited_at) < date_series.month_start + INTERVAL '1 month'
  GROUP BY date_trunc('month', date_series.month_start)
)
SELECT
  month::date AS "月",
  mau_count AS "月間アクティブユーザー数"
FROM MAU
WHERE month <= CURRENT_DATE
ORDER BY month

結果例

月 月間アクティブユーザー数
2024-07-01 2
2024-08-01 3
… …
「いいね！」 3
3

ありがとうございます、@SaraDev。これは私たちにとって非常に役立ちます。スタッフが一般ユーザーよりもはるかにアクティブな小規模コミュニティであるため、私（というか、私とChatGPT）は、一般ユーザーとスタッフユーザーを個別に、またはまとめて実行できるようにクエリを変更しました。そして、それをグラフ化したいので、そのための変更も加えました。

最近、@tyler.lamparterさんのこちらのヒントでデータベーススキーマをダウンロードする方法を知り、セッションの最初にそれをChatGPTにアップロードしました。これにより、チャッティに staff を分離するように依頼するのが容易になります。例えば。

-- [params]
-- int :months_ago = 3
-- int :include_staff = 1


WITH staff_users AS (
  SELECT user_id FROM group_users WHERE group_id = 3
),
daily_users AS (
  SELECT
    date_trunc('day', visited_at)::DATE AS day,
    COUNT(DISTINCT user_id) AS dau
  FROM user_visits
  WHERE visited_at >= CURRENT_DATE - INTERVAL ':months_ago months'
  AND (
    (:include_staff = 1) -- すべてのユーザー
    OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- スタッフ以外のみ
    OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- スタッフのみ
  )
  GROUP BY day
),
monthly_users AS (
  SELECT
    date_trunc('month', visited_at)::DATE AS month,
    COUNT(DISTINCT user_id) AS mau
  FROM user_visits
  WHERE visited_at >= CURRENT_DATE - INTERVAL ':months_ago months'
  AND (
    (:include_staff = 1) -- すべてのユーザー
    OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- スタッフ以外のみ
    OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- スタッフのみ
  )
  GROUP BY month
)
SELECT
  d.day::DATE AS date,  -- ✅ グラフのX軸
  ROUND((d.dau::numeric / NULLIF(m.mau, 0)::numeric) * 100, 1)::FLOAT AS dau_mau_ratio  -- ✅ グラフのY軸
FROM daily_users d
JOIN monthly_users m ON date_trunc('month', d.day) = m.month
ORDER BY date
-- 0 = スタッフ以外のみ, 1 = すべてのユーザー, 2 = スタッフのみ
「いいね！」 1