[3] SQL로 퍼널 분석하기

이번 시간에는 퍼널 분석을 통해 사용자의 흐름을 파악하고자 합니다. 특히, 유입 데이터를 분석하여 채널별 성과를 진단하고자 합니다.

이 때, 정규표현식과 윈도우 함수를 활용한 고급 SQL 기법도 사용할 예정이에요. 실무 분석에 한걸음 더 가까워지고 있습니다!

---

1️⃣ 퍼널 분석 (Funnel Analysis)

퍼널 분석은 사용자가 어떤 단계까지 도달했는지를 분석하는 기법이에요. 예를 들어, 방문 → 상품 조회 → 장바구니 담기 → 결제까지의 단계별 전환율을 살펴보며 이탈 지점을 파악할 수 있죠.

📌 퍼널 단계별 유저 수 조회 예시

SELECT
  step,
  COUNT(DISTINCT user_id) AS users
FROM user_actions
WHERE event_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY step
ORDER BY step;

---

2️⃣ 유입 데이터 분석

🔎 2-1. UTM 파라미터란?

UTM 파라미터는 마케팅 캠페인의 유입 경로를 추적하기 위해 URL에 붙이는 쿼리 문자열입니다.

예:

https://example.com?utm_source=instagram&utm_medium=cpc&utm_campaign=spring_sale

이 정보를 통해 어떤 채널이 유입과 전환에 효과적인지 분석할 수 있습니다.

📊 2-2. 유입 채널별 전환율 분석

SELECT
  utm_source,
  COUNT(DISTINCT user_id) AS visits,
  COUNT(DISTINCT CASE WHEN converted = TRUE THEN user_id END) AS conversions,
  ROUND(
    COUNT(DISTINCT CASE WHEN converted = TRUE THEN user_id END) * 100.0 /
    COUNT(DISTINCT user_id), 2
  ) AS conversion_rate
FROM marketing_utm_logs
GROUP BY utm_source;

→ 각 채널별 방문 수, 전환 수, 전환율을 한 눈에 확인할 수 있습니다.

---

3️⃣ 정규표현식 (Regular Expression)

SQL에서도 복잡한 문자열 처리를 할 수 있도록 정규표현식을 지원합니다. 예를 들어, 특정 키워드가 포함된 UTM 캠페인만 필터링할 수 있어요.

SELECT *
FROM marketing_utm_logs
WHERE REGEXP_CONTAINS(utm_campaign, r'(blackfriday|bf2024)');

→ 캠페인 이름에 blackfriday 또는 bf2024가 포함된 로그만 조회합니다.

---

4️⃣ 윈도우 함수 워밍업

윈도우 함수는 행 간의 관계를 쉽게 표현할 수 있는 강력한 도구입니다. 이번 주에는 LEAD(), LAG(), ROW_NUMBER()를 실습했어요.

🔁 4-1. LEAD()와 LAG() 예시

SELECT
  user_id,
  event_date,
  LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS prev_event,
  LEAD(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS next_event
FROM user_actions;

→ 같은 유저의 이전/다음 행동을 쉽게 비교할 수 있어요.

🔢 4-2. ROW_NUMBER()로 첫 행동 추출

SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_date) AS rn
  FROM user_actions
) t
WHERE rn = 1;

→ 유저별 첫 번째 행동만 필터링할 수 있습니다.

---

✅ 마무리

이번 시간에는 퍼널 분석을 통해 사용자의 흐름을 이해하고, 유입 채널을 정밀하게 분석해보았어요.

더불어, 정규표현식과 윈도우 함수로 SQL에 대한 이해도 넓어졌어요.