Relatório do Painel - DAU/MAU

Dados e relatórios
, ,
3

Obrigado, @SaraDev, isso é útil para nós. Como somos uma comunidade pequena com funcionários muito mais ativos do que usuários regulares, eu (bem, eu e o ChatGPT) modificamos a consulta para poder executar usuários regulares e usuários da equipe separadamente ou combinados. E eu gosto de ver isso em um gráfico, então modifiquei para habilitar isso.
Recentemente, vi como baixar o esquema do banco de dados nesta dica (https://meta.discourse.org/t/is-there-a-document-introducing-the-database-schema-design/171729/4?u=niceoldguy) de @tyler.lamparter, e eu faço upload disso para o ChatGPT no início dessas sessões; torna mais fácil pedir ao chatty para separar staff, por exemplo.

-- [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 curtida