이 리포트는 특정 날짜 범위에 걸쳐 일일 활성 사용자(DAU) 대 월간 활성 사용자(MAU) 비율에 대한 상세 분석을 제공합니다. DAU/MAU 비율은 하루의 고유 활성 사용자 수를 30일간의 고유 활성 사용자 수와 비교하여 사용자 참여도를 측정하는 핵심 성과 지표(KPI)입니다.
-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01
WITH date_series AS (
SELECT generate_series(:start_date::date, :end_date::date, '1 day'::interval) AS date
),
DAU AS (
SELECT date(date) AS day, COUNT(DISTINCT user_id) AS dau_count
FROM user_visits
JOIN date_series ON date(user_visits.visited_at) = date_series.date
GROUP BY date
),
MAU AS (
SELECT date_series.date, COUNT(DISTINCT user_visits.user_id) AS mau_count
FROM user_visits
JOIN date_series ON date(user_visits.visited_at) >= date_series.date - INTERVAL '30 days'
AND date(user_visits.visited_at) <= date_series.date
GROUP BY date_series.date
)
SELECT
DAU.day,
(DAU.dau_count::FLOAT / NULLIF(MAU.mau_count, 0)) * 100 AS "DAU/MAU",
DAU.dau_count,
MAU.mau_count
FROM DAU
JOIN MAU ON DAU.day = MAU.date
ORDER BY DAU.day
SQL 쿼리 설명
해당 SQL 쿼리는 공통 테이블 표현식(CTE)을 사용하여 두 개의 데이터셋(DAU용과 MAU용)을 생성합니다. 그런 다음 이 데이터셋들을 결합하여 지정된 날짜 범위 내 각 날짜에 대한 DAU/MAU 비율을 계산합니다.
매개변수
쿼리는 :start_date와 :end_date라는 두 개의 매개변수를 받아 사용자가 리포트의 날짜 범위를 지정할 수 있도록 합니다. 두 날짜 매개변수 모두 YYYY-MM-DD 형식의 날짜를 받습니다.
CTE
date_series: date_series CTE는 :start_date부터 :end_date까지, 각 날짜 사이에 하루 간격을 두며 일련의 날짜를 생성합니다. 이 시리즈는 사용자가 활동하지 않은 날이 있더라도 지정된 범위 내의 모든 날짜가 리포트에 포함되도록 보장하는 데 사용됩니다.
DAU: DAU CTE는 각 날짜에 사이트를 방문한 고유 사용자 수(dau_count)를 계산합니다. 이를 위해 user_visits 테이블을 date_series CTE와 조인하며, 방문 날짜가 시리즈의 날짜와 일치하는 조건을 사용합니다. 결과는 날짜별로 그룹화되어 각 날짜의 고유 사용자 수를 얻습니다.
MAU: MAU CTE는 date_series의 각 날짜 이전 30일 동안 사이트를 방문한 고유 사용자 수를 계산합니다. 이를 위해 user_visits 테이블을 date_series CTE와 조인하며, 방문 날짜가 시리즈의 날짜 30일 전부터 해당 날짜까지(해당 날짜 포함)인 경우를 조건으로 사용합니다. 결과는 날짜별로 그룹화되어 각 날짜 이전 30일 기간의 고유 사용자 수를 얻습니다.
최종 선택
쿼리의 마지막 부분은 DAU 수를 MAU 수로 나눈 후 100을 곱하여 백분율로 변환함으로써 각 날짜의 DAU/MAU 비율을 계산합니다. 참고를 위해 DAU와 MAU의 개수도 함께 선택합니다. NULLIF 함수는 특정 날에 MAU가 없을 경우 발생하는 0으로 나누기 오류를 방지하기 위해 사용됩니다. 결과는 날짜 순으로 정렬되어 사용자 참여도의 시간적 흐름을 보여줍니다.
혹시 도움이 될 수 있을까 싶어, 이 보고서의 수정 버전 중 MAU (Monthly Active Users) 통계만 표시하는 부분을 여기에 추가합니다:
MAU (Monthly Active Users)
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
WITH date_series AS (
SELECT generate_series(:start_date::date, :end_date::date, '1 month'::interval) AS month_start
),
MAU AS (
SELECT
date_trunc('month', date_series.month_start) AS month,
COUNT(DISTINCT user_visits.user_id) AS mau_count
FROM user_visits
JOIN date_series
ON date(user_visits.visited_at) >= date_series.month_start - INTERVAL '1 month'
AND date(user_visits.visited_at) < date_series.month_start + INTERVAL '1 month'
GROUP BY date_trunc('month', date_series.month_start)
)
SELECT
month::date AS "Month",
mau_count AS "Monthly Active Users"
FROM MAU
WHERE month <= CURRENT_DATE
ORDER BY month
감사합니다, @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 = 스태프만