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

データとレポート
, ,
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