[SQL] 클릭부터 구매까지, 고객 행동을 퍼널 분석하기

유저 행동 데이터를 분석하다 보면 단순히 "구매 수가 몇 건이다" 같은 수치만으로는 부족할 때가 많습니다.
그보다는 유저가 어떤 경로를 통해 구매에 도달했는지, 그리고 어디서 이탈했는지를 파악하는 쪽이 더 실질적인 인사이트로 이어질 수 있습니다.

이를 위해 흔히 사용하는 방법 중 하나가 퍼널(Funnel) 분석입니다.

---

❓퍼널 분석이란?

퍼널은 유저가 어떤 목표 행동(ex> 구매)에 이르기까지 거치는 단계를 순서대로 나눈 구조를 말합니다.
각 단계에서 일부 유저가 이탈하기 때문에, 전체 흐름을 살펴보면 어디에서 전환이 줄어들고 있는지, 어떤 구간에 병목이 있는지를 확인할 수 있습니다.

예를 들어, 아래와 같은 행동 흐름이 있을 수 있습니다.

visit → view → cart → purchase

이 글에서는 위와 같은 행동 흐름을 SQL로 정리해서, 단계별 전환율을 계산해보려고 합니다.

---

📄 데이터 구조

아래와 같은 로그 테이블을 사용한다고 가정합니다.

user_event

컬럼명	설명
user_id	유저 ID
event_name	행동 유형 (visit, view, cart, purchase)
event_time	행동 발생 시각 (timestamp)

-- 예시 데이터 일부
user_id | event_name   | event_time
--------|--------------|---------------------
1001    | visit        | 2025-06-01 09:02:01
1001    | view         | 2025-06-01 09:04:21
1001    | cart         | 2025-06-01 09:07:55
1001    | purchase     | 2025-06-01 09:10:02
1002    | visit        | 2025-06-01 10:10:00
1002    | view         | 2025-06-01 10:11:00
1003    | visit        | 2025-06-01 11:00:00

---

🎯 분석 목표

• 하루 단위로 유저가 각 단계를 밟았는지 정리
• 각 단계별 전환율(visit → view → cart → purchase) 계산
• 이탈이 일어난 구간을 파악해보기

---

SQL 로직 설명

하루 단위로 유저별 행동을 정리하고, 해당 날짜에 특정 행동을 했는지 여부를 체크하는 방식으로 진행합니다.
동일한 유저가 여러 번 같은 행동을 했더라도, 하루에 한 번이라도 해당 행동을 했으면 1로 간주합니다.

이를 위해 CASE WHEN과 MAX()를 사용합니다.

---

🖥️ 퍼널 분석 SQL 예 (PostgreSQL 기준)

WITH funnel_base AS (
    SELECT
          user_id
        , DATE(event_time) AS activity_date
        , MAX(CASE WHEN event_name = 'visit' THEN 1 ELSE 0 END) AS visited
        , MAX(CASE WHEN event_name = 'view' THEN 1 ELSE 0 END) AS viewed
        , MAX(CASE WHEN event_name = 'cart' THEN 1 ELSE 0 END) AS carted
        , MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS purchased
    FROM user_event
    GROUP BY user_id
    	   , DATE(event_time)
)

SELECT
      activity_date
    , COUNT(DISTINCT CASE WHEN visited = 1 THEN user_id END) AS visit_users
    , COUNT(DISTINCT CASE WHEN viewed = 1 THEN user_id END) AS view_users
    , COUNT(DISTINCT CASE WHEN carted = 1 THEN user_id END) AS cart_users
    , COUNT(DISTINCT CASE WHEN purchased = 1 THEN user_id END) AS purchase_users
    , ROUND(
        COUNT(DISTINCT CASE WHEN viewed = 1 THEN user_id END) * 1.0 /
        NULLIF(COUNT(DISTINCT CASE WHEN visited = 1 THEN user_id END), 0), 4
      ) AS view_rate
    , ROUND(
        COUNT(DISTINCT CASE WHEN carted = 1 THEN user_id END) * 1.0 /
        NULLIF(COUNT(DISTINCT CASE WHEN viewed = 1 THEN user_id END), 0), 4
      ) AS cart_rate
    , ROUND(
        COUNT(DISTINCT CASE WHEN purchased = 1 THEN user_id END) * 1.0 /
        NULLIF(COUNT(DISTINCT CASE WHEN carted = 1 THEN user_id END), 0), 4
      ) AS purchase_rate
FROM funnel_base
GROUP BY activity_date
ORDER BY activity_date;

---

👁️ 쿼리 결과 예시

activity_date	visit_users	view_users	cart_users	purchase_users	view_rate	cart_rate	purchase_rate
2025-06-01	300	250	150	90	0.8333	0.6000	0.6000

---

🧐 실무에서 이렇게 활용할 수 있어요

• 특정 날짜에 전환율이 급격히 떨어졌다면, UI 변경, 서버 이슈, 유입 품질 등을 확인해볼 수 있습니다.
• view_rate는 높은데 cart_rate가 낮다면, 상품 상세 페이지나 가격 요인을 점검해볼 수 있습니다.
• 분석 기준에 채널, 캠페인, 디바이스 등을 추가하면 더 심도있는 분석이 가능합니다.