T사 SQL 쿼리테스트 대비3: 퍼널 분석 (Funnel Analysis)

1. 퍼널 분석 (Funnel Analysis)

[가상 문제] 송금 퍼널 단계별 전환율 산출

유저의 앱 활동 로그(AppLogs)를 바탕으로 '홈 방문 → 송금 버튼 클릭 → 송금 완료'로 이어지는 퍼널의 각 단계별 유저 수와 이전 단계 대비 전환율을 구하십시오.

• 단계: home_view -> transfer_click -> transfer_complete
• 테이블: AppLogs (컬럼: user_id, event_name, timestamp)

 

퍼널 분석의 핵심은 '동일 유저'가 '순서대로' 단계를 밟았는지 확인하는 것입니다. 각 유저별로 각 단계의 도달 여부를 플래그(0 또는 1)로 만든 뒤 집계하는 'Pivot' 방식이 가장 효율적입니다.

 

WITH UserSteps AS (
    SELECT 
        user_id,
        MAX(CASE WHEN event_name = 'home_view' THEN 1 ELSE 0 END) AS step1,
        MAX(CASE WHEN event_name = 'transfer_click' THEN 1 ELSE 0 END) AS step2,
        MAX(CASE WHEN event_name = 'transfer_complete' THEN 1 ELSE 0 END) AS step3
    FROM AppLogs
    GROUP BY user_id
)
SELECT 
    SUM(step1) AS home_users,
    SUM(step2) AS click_users,
    SUM(step3) AS complete_users,
    -- 단계별 전환율 (분모가 0인 경우 처리)
    ROUND(SUM(step2) / SUM(step1) * 100, 2) AS s1_to_s2_pct,
    ROUND(SUM(step3) / SUM(step2) * 100, 2) AS s2_to_s3_pct
FROM UserSteps;

 

2. 풀이 방법

• CASE WHEN: 단계별로 COUNT를 따로 해서 나누는 경우, 데이터 정합성이 깨집니다. 반드시 **유저 단위로 각 단계를 밟았는지 확인(CASE WHEN)**한 뒤 집계해야 "A단계를 거치지 않고 B단계로 바로 간 유저" 등을 필터링하거나 포함하는 비즈니스 의사결정을 할 수 있습니다.
• MAX 함수: 한 유저가 홈을 10번 방문했어도, 우리는 그 유저가 **'홈 방문이라는 단계를 통과했는가'**만 알면 됩니다. MAX를 쓰면 0과 1 중 가장 큰 값인 1을 가져오게 되죠. CTE(UserSteps)의 실행 결과는 아래와 같이 유저당 딱 한 줄로 압축됩니다.

user_id	step1 (홈)	step2 (클릭)	step3 (완료)
A	1	1	1
B	1	1	0
C	1	0	0

 

3. 인사이트

 

• 분모 확보: SUM(step1)을 하면 전체 홈 방문자 수가 나옵니다.
• 이탈 지점 파악: step2는 1인데 step3가 0인 유저들을 모으면, "버튼은 눌렀는데 왜 송금을 안 마쳤지?"라는 날카로운 질문을 던질 수 있습니다.