대시보드 보고서 - DAU/MAU

커뮤니티 구축 데이터 및 보고
, ,
3

감사합니다, @SaraDev. 우리에게 유용한 정보네요. 저희는 작은 커뮤니티이고 스태프가 일반 사용자보다 훨씬 활동적이기 때문에, 저(그리고 ChatGPT)는 쿼리를 수정하여 일반 사용자와 스태프를 개별적으로 또는 함께 실행할 수 있도록 했습니다. 그리고 그래프로 보고 싶어해서 그래프 작성을 가능하도록 수정했습니다.

최근 @tyler.lamparter의 이 팁에서 데이터베이스 스키마를 다운로드하는 방법을 알게 되었고, 이러한 세션이 시작될 때 ChatGPT에 해당 스키마를 업로드합니다. 이렇게 하면 staff를 분리하도록 ChatGPT에게 요청하는 것이 더 쉬워집니다.

-- [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 = 스태프만