퍼널 분석 심화: 전환율과 이탈 지점을 SQL로 찾는 법

어디서 사용자가 이탈하는지 SQL로 정확히 파악하기. 단계별 전환율 계산의 모든 것.

TL;DR

• 퍼널 분석: 사용자가 목표까지 도달하는 단계별 전환율 측정
• 이탈 지점: 전환율이 급격히 떨어지는 단계 = 개선 포인트
• SQL로 구현: 이벤트 순서, 시간 제한, 세션 기반 분석 가능
• 핵심: 어디서 이탈하는지 알아야 어디를 고쳐야 할지 알 수 있다

1. 퍼널 분석이란?

기본 개념

방문 (1000명)
   ↓ 60%
회원가입 (600명)
   ↓ 50%
장바구니 담기 (300명)
   ↓ 33%
결제 완료 (100명)

전체 전환율: 10% (100/1000)

왜 중요한가?

• 전체 전환율 10%만 보면 어디가 문제인지 모름
• 단계별로 보면 "장바구니 → 결제" 구간(33%)이 병목
• 이 구간을 개선하면 전체 전환율 크게 향상

2. 기본 테이블 구조

-- 이벤트 로그 테이블
CREATE TABLE events (
    event_id BIGINT PRIMARY KEY,
    user_id INT,
    session_id VARCHAR(100),
    event_name VARCHAR(100),
    event_timestamp TIMESTAMP,
    page_url VARCHAR(500),
    properties JSONB
);

-- 주요 이벤트 예시
-- 'page_view', 'signup_start', 'signup_complete',
-- 'add_to_cart', 'checkout_start', 'purchase_complete'

3. 기본 퍼널: 단계별 사용자 수

가장 단순한 퍼널 쿼리

WITH funnel_steps AS (
    SELECT
        'step1_visit' as step,
        COUNT(DISTINCT user_id) as users
    FROM events
    WHERE event_name = 'page_view'
      AND event_timestamp >= '2024-01-01'
      AND event_timestamp < '2024-02-01'

    UNION ALL

    SELECT
        'step2_signup' as step,
        COUNT(DISTINCT user_id) as users
    FROM events
    WHERE event_name = 'signup_complete'
      AND event_timestamp >= '2024-01-01'
      AND event_timestamp < '2024-02-01'

    UNION ALL

    SELECT
        'step3_add_cart' as step,
        COUNT(DISTINCT user_id) as users
    FROM events
    WHERE event_name = 'add_to_cart'
      AND event_timestamp >= '2024-01-01'
      AND event_timestamp < '2024-02-01'

    UNION ALL

    SELECT
        'step4_purchase' as step,
        COUNT(DISTINCT user_id) as users
    FROM events
    WHERE event_name = 'purchase_complete'
      AND event_timestamp >= '2024-01-01'
      AND event_timestamp < '2024-02-01'
)
SELECT
    step,
    users,
    FIRST_VALUE(users) OVER (ORDER BY step) as total_users,
    ROUND(users * 100.0 / FIRST_VALUE(users) OVER (ORDER BY step), 2) as conversion_rate,
    LAG(users) OVER (ORDER BY step) as prev_step_users,
    ROUND(users * 100.0 / NULLIF(LAG(users) OVER (ORDER BY step), 0), 2) as step_conversion
FROM funnel_steps
ORDER BY step;

결과:

4. 순차 퍼널: 순서가 중요할 때

이전 단계를 거친 사용자만 카운트

WITH step1 AS (
    -- Step 1: 방문
    SELECT DISTINCT user_id, MIN(event_timestamp) as step1_time
    FROM events
    WHERE event_name = 'page_view'
      AND event_timestamp >= '2024-01-01'
    GROUP BY user_id
),
step2 AS (
    -- Step 2: 회원가입 (방문 이후)
    SELECT DISTINCT s1.user_id, MIN(e.event_timestamp) as step2_time
    FROM step1 s1
    JOIN events e ON s1.user_id = e.user_id
    WHERE e.event_name = 'signup_complete'
      AND e.event_timestamp > s1.step1_time
    GROUP BY s1.user_id
),
step3 AS (
    -- Step 3: 장바구니 (회원가입 이후)
    SELECT DISTINCT s2.user_id, MIN(e.event_timestamp) as step3_time
    FROM step2 s2
    JOIN events e ON s2.user_id = e.user_id
    WHERE e.event_name = 'add_to_cart'
      AND e.event_timestamp > s2.step2_time
    GROUP BY s2.user_id
),
step4 AS (
    -- Step 4: 구매 (장바구니 이후)
    SELECT DISTINCT s3.user_id, MIN(e.event_timestamp) as step4_time
    FROM step3 s3
    JOIN events e ON s3.user_id = e.user_id
    WHERE e.event_name = 'purchase_complete'
      AND e.event_timestamp > s3.step3_time
    GROUP BY s3.user_id
)
SELECT
    'Step 1: Visit' as step,
    COUNT(*) as users,
    100.0 as conversion_rate
FROM step1

UNION ALL

SELECT
    'Step 2: Signup',
    COUNT(*),
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM step1), 2)
FROM step2

UNION ALL

SELECT
    'Step 3: Add to Cart',
    COUNT(*),
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM step1), 2)
FROM step3

UNION ALL

SELECT
    'Step 4: Purchase',
    COUNT(*),
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM step1), 2)
FROM step4;

5. 시간 제한 퍼널

7일 내에 구매까지 완료한 사용자

WITH funnel AS (
    SELECT
        user_id,
        MIN(CASE WHEN event_name = 'page_view' THEN event_timestamp END) as visit_time,
        MIN(CASE WHEN event_name = 'signup_complete' THEN event_timestamp END) as signup_time,
        MIN(CASE WHEN event_name = 'add_to_cart' THEN event_timestamp END) as cart_time,
        MIN(CASE WHEN event_name = 'purchase_complete' THEN event_timestamp END) as purchase_time
    FROM events
    WHERE event_timestamp >= '2024-01-01'
    GROUP BY user_id
),
ordered_funnel AS (
    SELECT
        user_id,
        visit_time,
        -- 방문 후 회원가입
        CASE WHEN signup_time > visit_time THEN signup_time END as signup_time,
        -- 회원가입 후 장바구니
        CASE WHEN cart_time > signup_time AND signup_time > visit_time THEN cart_time END as cart_time,
        -- 장바구니 후 구매 (7일 이내)
        CASE WHEN purchase_time > cart_time
             AND cart_time > signup_time
             AND purchase_time - visit_time <= INTERVAL '7 days'
             THEN purchase_time END as purchase_time
    FROM funnel
)
SELECT
    COUNT(*) as total_visitors,
    COUNT(signup_time) as signed_up,
    COUNT(cart_time) as added_to_cart,
    COUNT(purchase_time) as purchased_within_7_days,
    ROUND(COUNT(signup_time) * 100.0 / COUNT(*), 2) as signup_rate,
    ROUND(COUNT(cart_time) * 100.0 / COUNT(*), 2) as cart_rate,
    ROUND(COUNT(purchase_time) * 100.0 / COUNT(*), 2) as purchase_rate_7d
FROM ordered_funnel
WHERE visit_time IS NOT NULL;

6. 세션 기반 퍼널

같은 세션 내에서 전환한 사용자

WITH session_funnel AS (
    SELECT
        session_id,
        user_id,
        MAX(CASE WHEN event_name = 'page_view' THEN 1 ELSE 0 END) as visited,
        MAX(CASE WHEN event_name = 'product_view' THEN 1 ELSE 0 END) as viewed_product,
        MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) as added_cart,
        MAX(CASE WHEN event_name = 'checkout_start' THEN 1 ELSE 0 END) as started_checkout,
        MAX(CASE WHEN event_name = 'purchase_complete' THEN 1 ELSE 0 END) as purchased
    FROM events
    WHERE event_timestamp >= '2024-01-01'
    GROUP BY session_id, user_id
)
SELECT
    SUM(visited) as sessions,
    SUM(viewed_product) as viewed_product,
    SUM(added_cart) as added_cart,
    SUM(started_checkout) as started_checkout,
    SUM(purchased) as purchased,
    ROUND(SUM(viewed_product) * 100.0 / NULLIF(SUM(visited), 0), 2) as view_rate,
    ROUND(SUM(added_cart) * 100.0 / NULLIF(SUM(viewed_product), 0), 2) as cart_rate,
    ROUND(SUM(started_checkout) * 100.0 / NULLIF(SUM(added_cart), 0), 2) as checkout_rate,
    ROUND(SUM(purchased) * 100.0 / NULLIF(SUM(started_checkout), 0), 2) as purchase_rate
FROM session_funnel;

7. 이탈 분석: 어디서 떠나는가?

각 단계에서 이탈한 사용자 수

WITH user_furthest_step AS (
    SELECT
        user_id,
        CASE
            WHEN MAX(CASE WHEN event_name = 'purchase_complete' THEN 1 ELSE 0 END) = 1 THEN 'purchased'
            WHEN MAX(CASE WHEN event_name = 'checkout_start' THEN 1 ELSE 0 END) = 1 THEN 'checkout'
            WHEN MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) = 1 THEN 'cart'
            WHEN MAX(CASE WHEN event_name = 'product_view' THEN 1 ELSE 0 END) = 1 THEN 'product_view'
            WHEN MAX(CASE WHEN event_name = 'page_view' THEN 1 ELSE 0 END) = 1 THEN 'visit_only'
            ELSE 'unknown'
        END as furthest_step
    FROM events
    WHERE event_timestamp >= '2024-01-01'
    GROUP BY user_id
)
SELECT
    furthest_step as dropped_at,
    COUNT(*) as users,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) as percentage
FROM user_furthest_step
GROUP BY furthest_step
ORDER BY
    CASE furthest_step
        WHEN 'visit_only' THEN 1
        WHEN 'product_view' THEN 2
        WHEN 'cart' THEN 3
        WHEN 'checkout' THEN 4
        WHEN 'purchased' THEN 5
        ELSE 6
    END;

결과:

→ 50%가 방문만 하고 이탈! 상품 노출이 문제일 수 있음.

8. 소요 시간 분석

각 단계 사이의 평균 소요 시간

WITH step_times AS (
    SELECT
        user_id,
        MIN(CASE WHEN event_name = 'page_view' THEN event_timestamp END) as visit_time,
        MIN(CASE WHEN event_name = 'signup_complete' THEN event_timestamp END) as signup_time,
        MIN(CASE WHEN event_name = 'add_to_cart' THEN event_timestamp END) as cart_time,
        MIN(CASE WHEN event_name = 'purchase_complete' THEN event_timestamp END) as purchase_time
    FROM events
    GROUP BY user_id
),
time_diffs AS (
    SELECT
        user_id,
        EXTRACT(EPOCH FROM (signup_time - visit_time)) / 60 as visit_to_signup_min,
        EXTRACT(EPOCH FROM (cart_time - signup_time)) / 60 as signup_to_cart_min,
        EXTRACT(EPOCH FROM (purchase_time - cart_time)) / 60 as cart_to_purchase_min,
        EXTRACT(EPOCH FROM (purchase_time - visit_time)) / 60 as total_time_min
    FROM step_times
    WHERE purchase_time IS NOT NULL
)
SELECT
    'Visit → Signup' as transition,
    ROUND(AVG(visit_to_signup_min), 2) as avg_minutes,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY visit_to_signup_min), 2) as median_minutes
FROM time_diffs
WHERE visit_to_signup_min > 0

UNION ALL

SELECT
    'Signup → Cart',
    ROUND(AVG(signup_to_cart_min), 2),
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY signup_to_cart_min), 2)
FROM time_diffs
WHERE signup_to_cart_min > 0

UNION ALL

SELECT
    'Cart → Purchase',
    ROUND(AVG(cart_to_purchase_min), 2),
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cart_to_purchase_min), 2)
FROM time_diffs
WHERE cart_to_purchase_min > 0;

9. 세그먼트별 퍼널 비교

채널별 전환율 비교

WITH first_touch AS (
    SELECT
        user_id,
        FIRST_VALUE(properties->>'utm_source') OVER (
            PARTITION BY user_id ORDER BY event_timestamp
        ) as channel
    FROM events
    WHERE event_timestamp >= '2024-01-01'
),
user_funnel AS (
    SELECT
        e.user_id,
        ft.channel,
        MAX(CASE WHEN e.event_name = 'page_view' THEN 1 ELSE 0 END) as visited,
        MAX(CASE WHEN e.event_name = 'signup_complete' THEN 1 ELSE 0 END) as signed_up,
        MAX(CASE WHEN e.event_name = 'purchase_complete' THEN 1 ELSE 0 END) as purchased
    FROM events e
    JOIN first_touch ft ON e.user_id = ft.user_id
    WHERE e.event_timestamp >= '2024-01-01'
    GROUP BY e.user_id, ft.channel
)
SELECT
    COALESCE(channel, 'direct') as channel,
    SUM(visited) as visitors,
    SUM(signed_up) as signups,
    SUM(purchased) as purchases,
    ROUND(SUM(signed_up) * 100.0 / NULLIF(SUM(visited), 0), 2) as signup_rate,
    ROUND(SUM(purchased) * 100.0 / NULLIF(SUM(visited), 0), 2) as purchase_rate
FROM user_funnel
GROUP BY channel
ORDER BY visitors DESC;

결과:

→ Direct 유입이 가장 전환율 높음!

10. 동적 퍼널 쿼리

이벤트 목록을 파라미터로 받아서 퍼널 생성

-- PostgreSQL: 배열로 퍼널 단계 정의
WITH funnel_events AS (
    SELECT unnest(ARRAY[
        'page_view',
        'product_view',
        'add_to_cart',
        'checkout_start',
        'purchase_complete'
    ]) as event_name,
    generate_series(1, 5) as step_order
),
user_steps AS (
    SELECT
        e.user_id,
        fe.event_name,
        fe.step_order,
        MIN(e.event_timestamp) as first_occurrence
    FROM events e
    JOIN funnel_events fe ON e.event_name = fe.event_name
    WHERE e.event_timestamp >= '2024-01-01'
    GROUP BY e.user_id, fe.event_name, fe.step_order
),
sequential_funnel AS (
    SELECT
        us.user_id,
        us.step_order,
        us.event_name,
        us.first_occurrence,
        LAG(us.first_occurrence) OVER (
            PARTITION BY us.user_id ORDER BY us.step_order
        ) as prev_step_time
    FROM user_steps us
)
SELECT
    step_order,
    event_name,
    COUNT(DISTINCT user_id) as users,
    ROUND(
        COUNT(DISTINCT user_id) * 100.0 /
        FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (ORDER BY step_order),
        2
    ) as conversion_rate
FROM sequential_funnel
WHERE step_order = 1 OR first_occurrence > prev_step_time
GROUP BY step_order, event_name
ORDER BY step_order;

11. 실전 팁

1. 전환율 vs 단계 전환율

-- 전체 전환율: step1 대비
conversion_rate = step_n_users / step1_users

-- 단계 전환율: 이전 단계 대비
step_conversion = step_n_users / step_(n-1)_users

둘 다 중요! 단계 전환율로 병목 파악.

2. 통계적 유의성 고려

-- 작은 샘플 조심
WHERE cohort_size >= 100

3. 시간 창(Time Window) 설정

-- 너무 긴 시간은 의미 없음
AND purchase_time - visit_time <= INTERVAL '30 days'

결론

퍼널 분석 핵심:

• 전체 전환율보다 단계별 전환율이 중요
• 이탈이 심한 구간 = 개선 우선순위
• 세그먼트별 비교로 최적화 포인트 발견

References

1. Amplitude - Funnel Analysis Guide
2. Mixpanel - Understanding Conversion Funnels
3. Google Analytics - Goal Funnel Visualization