仪表盘报告 - DAU/MAU

数据与报告
, ,
3

谢谢，@SaraDev，这对我们很有帮助。由于我们是一个小型社区，工作人员比普通用户活跃得多，我（以及 ChatGPT）修改了查询，以便能够分别或合并运行普通用户和工作人员用户。而且我喜欢看到它被图形化，所以进行了修改以启用它。
我最近在 @tyler.lamparter 的这个技巧中看到了如何下载数据库模式，并在这些会话开始时将其上传到 ChatGPT；这使得更容易要求 chatty 分开 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) -- All users
    OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- Non-staff only
    OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- Staff only
  )
  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) -- All users
    OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- Non-staff only
    OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- Staff only
  )
  GROUP BY month
)
SELECT
  d.day::DATE AS date,  -- ✅ X-axis for graphing
  ROUND((d.dau::numeric / NULLIF(m.mau, 0)::numeric) * 100, 1)::FLOAT AS dau_mau_ratio  -- ✅ Y-axis for graphing
FROM daily_users d
JOIN monthly_users m ON date_trunc('month', d.day) = m.month
ORDER BY date
-- 0 = Non-Staff Only, 1 = All Users, 2 = Staff Only
1 个赞