SQL을 활용한 마케팅 데이터 추출과 분석: 쿼리 하나로 발견한 전환율의 진짜 원인

WITH
  -- 1. 분석 기간 내 세션 기본 정보 집계
  session_summary AS (
    SELECT
      session_id,
      user_id,
      device_type,
      is_new_user, -- 실제로는 user_id의 첫번째 세션인지 등으로 이 시점에서 판단
      MIN(event_timestamp) AS session_start_time -- 세션 시작 시간
    FROM
      session_logs
    WHERE
      event_timestamp >= '2023-10-01 00:00:00' -- 분석 시작일
      AND event_timestamp < '2023-10-08 00:00:00' -- 분석 종료일 +1일
    GROUP BY
      1, 2, 3, 4
  ),
  -- 2. 분석 기간 내 발생한 구매 세션 정보
  purchase_sessions AS (
    SELECT
      DISTINCT session_id AS purchase_session_id
    FROM
      purchase_logs
    WHERE
      purchase_timestamp >= '2023-10-01 00:00:00' -- 분석 시작일
      AND purchase_timestamp < '2023-10-08 00:00:00' -- 분석 종료일 +1일
  )
-- 3. 세션 정보와 구매 정보를 결합하여 세그먼트별 전환율 계산
SELECT
  ss.is_new_user,
  ss.device_type,
  COUNT(DISTINCT ss.session_id) AS total_sessions,                 -- 전체 세션 수
  COUNT(DISTINCT ps.purchase_session_id) AS converted_sessions,   -- 구매 전환된 세션 수
  (
    COUNT(DISTINCT ps.purchase_session_id) * 100.0 / COUNT(DISTINCT ss.session_id)
  ) AS conversion_rate_percent                                    -- 전환율 (%)
FROM
  session_summary ss
LEFT JOIN
  purchase_sessions ps ON ss.session_id = ps.purchase_session_id
GROUP BY
  1, 2
ORDER BY
  1, 2;