SQL·제품 분석 도구 실습 — 이벤트 로그로 퍼널과 코호트를 직접 뽑아 보기

"지난주 가입자 중 첫 결제까지 간 비율이 얼마예요?"
"온보딩 3단계에서 사람들이 빠진다는데 정말인가요?"
"8월 신규 가입자는 한 달 뒤에도 남아 있나요? 7월보다 나아졌나요?"

CREATE TABLE events (
  event_id    BIGINT PRIMARY KEY,     -- 이벤트 고유 식별자(중복 제거 키)
  user_id     VARCHAR(64) NOT NULL,   -- 로그인 사용자 식별자
  anonymous_id VARCHAR(64),           -- 로그인 전 기기 식별자
  event_name  VARCHAR(64) NOT NULL,   -- 'signup_completed', 'checkout_started' …
  event_time  TIMESTAMP NOT NULL,     -- UTC 로 저장하는 것이 원칙
  session_id  VARCHAR(64),            -- 세션 단위 묶음
  platform    VARCHAR(16),            -- 'ios' | 'android' | 'web'
  app_version VARCHAR(16),
  properties  JSONB                   -- 이벤트별 부가 속성
);
 
CREATE TABLE users (
  user_id     VARCHAR(64) PRIMARY KEY,
  signup_time TIMESTAMP NOT NULL,
  channel     VARCHAR(32),            -- 유입 채널
  is_internal BOOLEAN DEFAULT FALSE   -- 사내·테스트 계정 표시
);

WITH base AS (
  SELECT e.user_id, e.event_name, e.event_time
  FROM events e
  JOIN users u ON u.user_id = e.user_id
  WHERE e.event_time >= TIMESTAMP '2026-09-01 00:00:00'
    AND e.event_time  < TIMESTAMP '2026-09-08 00:00:00'
    AND u.is_internal = FALSE
    AND e.event_name IN ('cart_viewed','checkout_started','payment_selected','payment_succeeded')
),
step1 AS (                                   -- 퍼널 진입 시각 = 각 사용자의 첫 cart_viewed
  SELECT user_id, MIN(event_time) AS t1
  FROM base WHERE event_name = 'cart_viewed'
  GROUP BY user_id
),
step2 AS (
  SELECT s.user_id, MIN(b.event_time) AS t2
  FROM step1 s
  JOIN base b ON b.user_id = s.user_id
             AND b.event_name = 'checkout_started'
             AND b.event_time >= s.t1
             AND b.event_time  < s.t1 + INTERVAL '1 hour'   -- 전환 윈도
  GROUP BY s.user_id
),
step3 AS (   -- step2 와 같은 모양: 앞 단계 step2, 이벤트 'payment_selected'
  SELECT s.user_id, MIN(b.event_time) AS t3
  FROM step2 s
  JOIN base b ON b.user_id = s.user_id AND b.event_name = 'payment_selected'
             AND b.event_time >= s.t2 AND b.event_time < s.t2 + INTERVAL '1 hour'
  GROUP BY s.user_id
),
step4 AS (   -- 앞 단계 step3, 이벤트 'payment_succeeded'
  SELECT s.user_id, MIN(b.event_time) AS t4
  FROM step3 s
  JOIN base b ON b.user_id = s.user_id AND b.event_name = 'payment_succeeded'
             AND b.event_time >= s.t3 AND b.event_time < s.t3 + INTERVAL '1 hour'
  GROUP BY s.user_id
)
SELECT
  (SELECT COUNT(*) FROM step1) AS s1_cart,
  (SELECT COUNT(*) FROM step2) AS s2_checkout,
  (SELECT COUNT(*) FROM step3) AS s3_payment_selected,
  (SELECT COUNT(*) FROM step4) AS s4_paid;

WITH base AS ( /* 위 쿼리의 base 와 동일 — 기간·내부계정·이벤트 목록 필터 */ ),
ranked AS (
  SELECT user_id, event_time,
    CASE event_name WHEN 'cart_viewed' THEN 1 WHEN 'checkout_started' THEN 2
                    WHEN 'payment_selected' THEN 3 WHEN 'payment_succeeded' THEN 4 END AS step_no
  FROM base
),
progress AS (
  SELECT user_id,
    MAX(step_no) OVER (
      PARTITION BY user_id ORDER BY event_time
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS reached_so_far
  FROM ranked
),
max_step AS (
  SELECT user_id, MAX(reached_so_far) AS deepest FROM progress GROUP BY user_id
)
SELECT s.step_no, COUNT(*) FILTER (WHERE m.deepest >= s.step_no) AS reached_users
FROM max_step m CROSS JOIN (VALUES (1),(2),(3),(4)) AS s(step_no)
GROUP BY s.step_no ORDER BY s.step_no;

SELECT step_no, step_name, users,
  ROUND(100.0 * users / LAG(users)         OVER (ORDER BY step_no), 1) AS step_conv_pct,
  ROUND(100.0 * users / FIRST_VALUE(users) OVER (ORDER BY step_no), 1) AS cum_conv_pct,
  LAG(users) OVER (ORDER BY step_no) - users                           AS dropped
FROM funnel_counts   -- 위 퍼널 쿼리의 결과를 (step_no, step_name, users) 로 펼친 것
ORDER BY step_no;

step  name                users    step_conv  cum_conv  dropped
1     cart_viewed         100,000        —      100.0%        —
2     checkout_started     42,000     42.0%      42.0%    58,000   ← 가장 큰 이탈
3     payment_selected     38,600     91.9%      38.6%     3,400
4     payment_succeeded    33,400     86.5%      33.4%     5,200   ← 전주 90.9%

WITH cohort AS (   -- 사용자별 코호트 주차
  SELECT u.user_id,
         CAST(date_trunc('week', u.signup_time AT TIME ZONE 'Asia/Seoul') AS DATE) AS cohort_week
  FROM users u
  WHERE u.is_internal = FALSE AND u.signup_time >= TIMESTAMP '2026-08-03'
),
activity AS (      -- 사용자별 활동 주차(중복 제거)
  SELECT DISTINCT e.user_id,
         CAST(date_trunc('week', e.event_time AT TIME ZONE 'Asia/Seoul') AS DATE) AS active_week
  FROM events e WHERE e.event_name = 'app_opened'
),
joined AS (
  SELECT c.cohort_week, c.user_id,
         CAST((a.active_week - c.cohort_week) / 7 AS INT) AS week_no
  FROM cohort c
  LEFT JOIN activity a ON a.user_id = c.user_id AND a.active_week >= c.cohort_week
)
SELECT
  cohort_week,
  COUNT(DISTINCT user_id) FILTER (WHERE week_no = 0) AS w0,
  COUNT(DISTINCT user_id) FILTER (WHERE week_no = 1) AS w1,
  COUNT(DISTINCT user_id) FILTER (WHERE week_no = 2) AS w2,
  COUNT(DISTINCT user_id) FILTER (WHERE week_no = 3) AS w3,
  COUNT(DISTINCT user_id) FILTER (WHERE week_no = 4) AS w4
FROM joined
GROUP BY cohort_week
ORDER BY cohort_week;

[주차별 코호트 리텐션 — 분모는 각 코호트의 W0]
cohort      W0       W1      W2      W3      W4
2026-08-03  12,400  41.2%   29.8%   24.1%   21.6%
2026-08-10  13,100  40.6%   29.1%   23.7%   21.0%
2026-08-17  12,900  38.2%   26.4%   21.3%      —      ← W1 부터 꺾임
2026-08-24  13,500  37.9%   26.1%      —       —
2026-08-31  14,200  38.4%      —      —       —

SELECT event_name, COUNT(*) AS rows_total, COUNT(DISTINCT event_id) AS rows_unique,
       COUNT(*) - COUNT(DISTINCT event_id) AS dup_rows
FROM events WHERE event_time >= TIMESTAMP '2026-09-01'
GROUP BY event_name ORDER BY dup_rows DESC;

SELECT user_id, event_time,
  CASE WHEN COALESCE(event_time - LAG(event_time) OVER w, INTERVAL '999 days')
            > INTERVAL '30 minutes' THEN 1 ELSE 0 END AS is_session_start
FROM events
WINDOW w AS (PARTITION BY user_id ORDER BY event_time);