تقرير لوحة القيادة - 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) -- 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)