퍼널 분석 SQL: 이탈 지점을 쿼리로 계산하는 방법

퍼널 분석 SQL이란?
퍼널 분석 SQL은 사용자 이벤트 로그에서 단계별 도달 인원과 전환율을 집계해 어디서 이탈이 일어나는지 쿼리로 계산하는 방법이다.

"결제 전환율이 떨어졌다"는 보고는 왔는데, 어디서 떨어졌는지는 아무도 모른다. 회원가입인지, 장바구니인지, 결제 수단 선택 화면인지. 이 질문에 답하려면 단계별 인원을 세야 하고, 세려면 쿼리를 짜야 한다.

그런데 퍼널 SQL은 겉보기보다 함정이 많다. 이벤트를 그냥 COUNT 하면 한 사람이 장바구니를 세 번 담은 것이 3명으로 잡힌다. 순서를 강제하지 않으면 결제부터 하고 상품을 본 사용자가 정상 전환으로 계산된다. 전환 윈도우를 안 걸면 3개월 전에 가입한 사람이 오늘 결제한 것까지 같은 퍼널에 들어온다. 여기서는 그 함정을 하나씩 막아가는 순서로 쿼리를 세운다.

퍼널 SQL에서 먼저 잡아야 할 개념 세 가지

1. 집계 단위는 이벤트가 아니라 사용자다

퍼널의 각 단계는 "해당 행동을 한 고유 사용자 수"다. 이벤트 건수로 세면 재방문과 재시도가 많은 단계일수록 인원이 부풀고, 전환율이 100%를 넘는 기괴한 표가 나온다. 모든 단계는 COUNT(DISTINCT user_id) 기준으로 통일한다.

2. 순서 없는 퍼널은 퍼널이 아니다

A 단계와 B 단계를 각각 따로 세어 나눈 값은 퍼널이 아니라 두 개의 별도 지표다. 진짜 퍼널은 "A를 한 뒤에 B를 한 사람"을 센다. SQL에서는 각 단계의 최초 발생 시각을 구한 다음 시각 순서를 조건으로 거는 방식이 가장 안전하다.

3. 전환 윈도우가 숫자를 만든다

같은 데이터라도 윈도우를 24시간으로 잡느냐 7일로 잡느냐에 따라 전환율이 크게 달라진다. 윈도우는 분석가가 임의로 정하는 값이 아니라 제품의 의사결정 주기에서 나온다. 즉시 소비형 서비스는 세션 단위, 고관여 상품은 7일 이상이 현실에 가깝다. 어떤 값을 쓰든 리포트 상단에 명시한다.

단계별 실전 절차: 이탈 지점을 쿼리로 계산하기

STEP 1. 퍼널 정의를 문서로 먼저 확정한다

쿼리를 열기 전에 네 가지를 적는다. 대상 이벤트 목록(3~5개), 단계 순서, 전환 윈도우, 분석 기간. 이 네 줄이 없으면 리뷰 때마다 숫자가 바뀐다.

• 이벤트: view_item → add_to_cart → begin_checkout → purchase
• 순서: 위 순서를 강제, 건너뛰기 불허
• 윈도우: 최초 view_item 이후 7일
• 기간: 2026-07-01 ~ 2026-07-31 (진입 기준일)

STEP 2. 이벤트 로그를 표준 형태로 정리한다

퍼널 쿼리의 입력은 user_id, event_name, event_time 세 컬럼이면 충분하다. GA4를 BigQuery로 내보낸 테이블이라면 중첩 구조를 먼저 평평하게 편다.

WITH events AS (
  SELECT
    user_pseudo_id                          AS user_id,
    event_name,
    TIMESTAMP_MICROS(event_timestamp)       AS event_time
  FROM `project.analytics_123456.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260701' AND '20260807'
    AND event_name IN ('view_item','add_to_cart','begin_checkout','purchase')
)

기간을 분석 기간보다 7일 넓게 잡은 이유는 7월 31일에 진입한 사용자의 전환 윈도우가 8월 7일까지 열려 있기 때문이다. 이 여유분을 안 두면 월말 진입자의 전환이 통째로 누락되어 마지막 주 전환율이 항상 낮게 나온다.

STEP 3. 사용자별 단계 최초 도달 시각을 구한다

, first_touch AS (
  SELECT
    user_id,
    MIN(IF(event_name='view_item',       event_time, NULL)) AS t1_view,
    MIN(IF(event_name='add_to_cart',     event_time, NULL)) AS t2_cart,
    MIN(IF(event_name='begin_checkout',  event_time, NULL)) AS t3_checkout,
    MIN(IF(event_name='purchase',        event_time, NULL)) AS t4_purchase
  FROM events
  GROUP BY user_id
)

조건부 MIN 하나로 중복 이벤트 문제가 사라진다. 사용자당 한 행, 단계당 한 시각이 남는다.

STEP 4. 순서와 전환 윈도우를 조건으로 건다

, funnel AS (
  SELECT
    user_id,
    t1_view IS NOT NULL AS step1,
    (t2_cart     > t1_view  AND t2_cart     <= TIMESTAMP_ADD(t1_view, INTERVAL 7 DAY)) AS step2,
    (t3_checkout > t2_cart  AND t3_checkout <= TIMESTAMP_ADD(t1_view, INTERVAL 7 DAY)) AS step3,
    (t4_purchase > t3_checkout AND t4_purchase <= TIMESTAMP_ADD(t1_view, INTERVAL 7 DAY)) AS step4
  FROM first_touch
  WHERE DATE(t1_view) BETWEEN '2026-07-01' AND '2026-07-31'
)

부등호가 핵심이다. > 로 순서를 강제하고, <= 와 INTERVAL로 윈도우를 닫는다. NULL 비교는 자동으로 FALSE가 되므로 미도달 사용자는 별도 처리 없이 걸러진다.

STEP 5. 단계별 인원과 전환율을 집계한다

SELECT
  COUNTIF(step1) AS s1,
  COUNTIF(step1 AND step2) AS s2,
  COUNTIF(step1 AND step2 AND step3) AS s3,
  COUNTIF(step1 AND step2 AND step3 AND step4) AS s4,
  ROUND(SAFE_DIVIDE(COUNTIF(step1 AND step2), COUNTIF(step1))*100, 1) AS cvr_1_2,
  ROUND(SAFE_DIVIDE(COUNTIF(step1 AND step2 AND step3),
                    COUNTIF(step1 AND step2))*100, 1)                 AS cvr_2_3,
  ROUND(SAFE_DIVIDE(COUNTIF(step1 AND step2 AND step3 AND step4),
                    COUNTIF(step1 AND step2 AND step3))*100, 1)       AS cvr_3_4
FROM funnel

누적 조건(step1 AND step2 AND …)을 그대로 유지해야 한다. step3만 단독으로 세면 앞 단계를 건너뛴 사용자가 섞인다. 나눗셈은 SAFE_DIVIDE로 감싸 분모 0인 세그먼트에서 쿼리가 죽지 않게 한다.

STEP 6. 이탈 지점을 판정한다

전환율이 가장 낮은 단계가 항상 개선 1순위는 아니다. 두 가지를 같이 본다.

전환율 12%짜리 단계에 100명이 서 있고, 전환율 60%짜리 단계에 8만 명이 서 있다면 후자를 3%p 올리는 쪽이 매출 기여가 크다. 이탈 지점은 비율과 규모를 곱해서 고른다.

STEP 7. 세그먼트로 쪼갠다

전체 퍼널은 평균이라 진짜 문제를 가린다. STEP 4의 funnel CTE에 디바이스·유입 채널·신규 여부를 컬럼으로 붙이고 GROUP BY를 추가하면 같은 쿼리로 세그먼트 퍼널이 나온다. 전체 결제 전환율은 정상인데 안드로이드 신규 사용자만 절반이라는 사실도 여기서야 드러난다.

STEP 8. 숫자를 믿기 전에 검증한다

• 타임존: event_time이 UTC인지 KST인지 확인. 하루 밀리면 일자별 퍼널이 통째로 어긋난다
• 봇과 내부 트래픽: 사내 IP, QA 계정, 크롤러 user_id 제외
• 비로그인 식별자: user_pseudo_id는 기기 단위라 앱 재설치나 기기 교체 시 다른 사람으로 잡힌다
• 이벤트 누락: 특정 단계 인원이 갑자기 0에 가까우면 개선 대상이 아니라 트래킹 사고를 의심한다
• 합계 검증: 각 단계 인원이 앞 단계보다 크면 순서 조건이 빠진 것이다

퍼널 SQL 실행 체크리스트

1. 퍼널 정의 4줄(이벤트·순서·윈도우·기간)을 문서로 확정했는가
2. 집계 단위를 COUNT(DISTINCT user_id)로 통일했는가
3. 조회 기간을 전환 윈도우만큼 뒤로 넓혔는가
4. 단계별 최초 도달 시각을 조건부 MIN으로 구했는가
5. 부등호로 단계 순서를 강제했는가
6. 전환 윈도우를 INTERVAL 조건으로 닫았는가
7. 누적 조건(AND 체인)으로 단계 인원을 셌는가
8. 나눗셈을 SAFE_DIVIDE로 감쌌는가
9. 전환율과 절대 이탈 인원을 함께 봤는가
10. 디바이스·채널·신규 여부로 세그먼트를 쪼갰는가
11. 타임존, 봇, 식별자, 이벤트 누락을 점검했는가
12. 단계 인원이 단조 감소하는지 확인했는가

시나리오: 국내 이커머스 팀의 결제 퍼널 진단

아래는 실제 기업 사례가 아니라 위 절차를 그대로 적용했을 때 나오는 전형적인 형태를 재구성한 가상 시나리오다.

눈에 먼저 들어오는 건 26.0%인 첫 구간이다. 그러나 이커머스에서 조회 대비 장바구니 비율이 낮은 것은 구조적 특성에 가깝다. 실행 우선순위는 결제 시작에서 완료로 넘어가는 구간이다. 이미 카드 정보를 꺼낸 사용자 8,800명이 사라지고, 이 구간 소요 시간 중앙값이 4분 20초로 다른 구간의 세 배였다면 결제 수단 로딩이나 인증 실패를 먼저 본다.

세그먼트를 쪼개면 이야기가 한 번 더 갈린다. 같은 구간을 iOS와 안드로이드로 나눴을 때 한쪽만 30%p 낮게 나온다면 이건 UX 문제가 아니라 특정 SDK 버전의 결제 모듈 오류다. 전체 평균만 보면 절대 안 보인다.

쿼리로 갈 것인가, 도구로 갈 것인가

SQL 퍼널의 장점은 명확하다. 정의를 완전히 통제하고, 원본 데이터를 그대로 쓰고, 어떤 조건이든 조합한다. 대신 비용도 명확하다. 위 쿼리 하나를 세그먼트별로 돌리려면 매번 CTE를 고쳐야 하고, 그 요청은 전부 분석가 한 명에게 쌓인다. PM이 "안드로이드 신규만 다시 봐줄 수 있어요?"라고 물으면 대기열에 하루가 더 붙는다.

2026년 8월 기준으로 GA4 같은 웹 분석 도구에도 퍼널 리포트가 있지만, 탐색 리포트의 샘플링, 24~48시간 수준의 데이터 처리 지연, BigQuery 연동 없이는 로데이터에 닿지 못하는 구조 때문에 정밀한 이탈 추적에는 한계가 있다고 알려져 있다. 프로덕트 애널리틱스 도구는 이 지점을 다르게 푼다. 이벤트 로그를 사용자 단위로 보관하고, 전환 윈도우·단계 순서·세그먼트를 화면에서 바꿀 수 있게 해 위 STEP 4~7을 클릭으로 대체한다. Mixpanel 공식 문서(docs.mixpanel.com)에서 퍼널의 전환 기준과 카운팅 방식을 확인해두면 SQL로 짠 정의와 도구 정의가 왜 다른 숫자를 내는지 대조하기 쉽다.

판단 기준은 기능 개수가 아니라 질문을 던지는 사람과 답을 만드는 사람이 같아지는가다. 분석가에게 쿼리를 요청하고 사흘을 기다리는 구조에서는 가설이 식는다. PM이 직접 퍼널을 열고 마케터가 코호트를 만드는 팀은 같은 데이터로 훨씬 많은 결정을 내린다. 도구 선택을 앞두고 있다면 프로덕트 애널리틱스란?에서 개념을 먼저 정리하고, 사용자 분석 툴 비교로 각 도구가 퍼널을 어떻게 정의하는지 확인해보길 권한다.

자주 묻는 질문

Q1. 단계를 건너뛴 사용자는 어떻게 처리하나?

기본은 제외다. 위 쿼리처럼 순서를 강제하면 건너뛴 사용자는 자동으로 빠진다. 다만 건너뛰기가 정상 동선인 제품도 있다. 원클릭 구매나 딥링크 진입이 그렇다. 이럴 때는 순서 조건을 완화한 별도 퍼널을 하나 더 만들어 두 값을 나란히 보고, 어느 정의를 공식 지표로 쓸지 팀 합의로 못 박는다.

Q2. 전환 윈도우는 며칠로 잡는 게 맞나?

정답은 데이터가 알려준다. 전환에 성공한 사용자의 첫 단계와 마지막 단계 사이 시간 차를 뽑아 백분위수를 본다. 90 백분위가 3일이면 7일 윈도우는 과하고 3일이면 충분하다. 윈도우를 늘릴수록 전환율은 올라가지만 개선 효과를 측정하는 감도는 떨어진다.

Q3. GA4 퍼널 리포트로는 왜 부족한가?

GA4는 트래픽 소스와 광고 성과를 보는 데 최적화된 도구다. 제품 안에서 사용자가 왜 멈췄는지를 파고들려면 샘플링 없는 로데이터, 즉시 반영되는 이벤트, 자유로운 세그먼트 조합이 필요한데 이 조건이 GA4에서는 BigQuery 연동과 추가 설정을 전제로 한다. SQL을 직접 쓰거나 프로덕트 애널리틱스 도구를 쓰는 이유가 여기 있다. 도구 도입을 검토 중이라면 믹스패널 완벽 입문 가이드에서 이벤트 설계부터 확인하면 된다.

쿼리는 답이 아니라 질문을 좁히는 도구다

퍼널 SQL이 알려주는 건 "여기서 8,800명이 사라진다"까지다. 왜 사라지는지는 쿼리가 답하지 못한다. 그다음은 세션 리플레이를 보거나, 이탈 직전 이벤트를 역추적하거나, 그 사용자들에게 직접 묻는 일이다. 좋은 퍼널 쿼리의 목적은 숫자를 만드는 게 아니라 팀이 들여다볼 화면 한 개를 정확히 지목하는 것이다.

오늘 만든 쿼리를 저장해두고, 다음 배포 뒤에 같은 정의로 다시 돌려보자. 정의가 고정된 퍼널만이 개선 효과를 증명한다. 이벤트 설계와 코호트, 리텐션 분석까지 이어서 정리하고 싶다면 Nitrox 블로그에서 관련 가이드 더 보기.