[퍼널 분석] solvesql | 난이도 5 | SQL 데이터 분석 캠프 실전반 전환율

SQL 데이터 분석 캠프 실전반 전환율

 

실전반 페이지를 조회하는 것 부터 실전반 신청하기 버튼 클릭까지 이어지는 퍼널은 다음과 같이 구성한다.

 

[실전반 페이지뷰 -> 스크롤 -> 실전반 신청하기 버튼 클릭]

 

실전반 페이지를 본 이후에 스크롤을 내려본 세션은 얼마나 되는지, 또 스크롤을 내려 실전반 신청하기 버튼까지 클릭한 세션은 얼마나 되는지를 조회하는 쿼리를 작성할 것.

 

문제 조건

1. 실전반 페이지를 본 세션은 아래 조건으로 찾는다

• page_title = “백문이불여일타 SQL 캠프 실전반”
• event_name = “page_view”

2. 실전반 페이지 내에서 스크롤을 한 세션은 아래 조건으로 찾는다

• page_title = “백문이불여일타 SQL 캠프 실전반”
• event_name = “scroll”

3. 실전반 신청하기 버튼을 클릭한 세션은 아래 조건으로 찾는다

• event_name = “SQL_advanced_form_click”

Solution

WITH pv AS (
  SELECT user_pseudo_id
       , ga_session_id
       , event_timestamp_kst AS pv_at
  FROM ga
  WHERE page_title = '백문이불여일타 SQL 캠프 실전반'
    AND event_name = 'page_view'

), scroll AS (
  SELECT user_pseudo_id
       , ga_session_id
       , event_timestamp_kst AS scroll_at
  FROM ga
  WHERE page_title = '백문이불여일타 SQL 캠프 실전반'
    AND event_name = 'scroll'

), click AS (
  SELECT user_pseudo_id
       , ga_session_id
       , event_timestamp_kst AS click_at
  FROM ga
  WHERE page_title = '백문이불여일타 SQL 캠프 실전반'
    AND event_name = 'SQL_advanced_form_click'
)
SELECT COUNT(DISTINCT p.user_pseudo_id, p.ga_session_id) AS pv
     , COUNT(DISTINCT s.user_pseudo_id, s.ga_session_id) AS scroll_after_pv
     , COUNT(DISTINCT c.user_pseudo_id, c.ga_session_id) AS click_after_scroll
     , ROUND(COUNT(DISTINCT s.user_pseudo_id, s.ga_session_id) / COUNT(DISTINCT p.user_pseudo_id, p.ga_session_id), 3) AS pv_scroll_rate
     , ROUND(COUNT(DISTINCT c.user_pseudo_id, c.ga_session_id) / COUNT(DISTINCT p.user_pseudo_id, p.ga_session_id), 3) AS pv_click_rate
     , ROUND(COUNT(DISTINCT c.user_pseudo_id, c.ga_session_id) / COUNT(DISTINCT s.user_pseudo_id, s.ga_session_id), 3) AS scroll_click_rate
FROM pv p 
LEFT JOIN scroll s ON p.user_pseudo_id = s.user_pseudo_id
                  AND p.ga_session_id = s.ga_session_id
                  AND p.pv_at <= s.scroll_at
LEFT JOIN click c ON s.user_pseudo_id = c.user_pseudo_id
                 AND s.ga_session_id = c.ga_session_id
                 AND s.scroll_at <= c.click_at

 

✅ pv CTE:

실전반 페이지에 방문한 세션의 테이블을 정의한다

 

✅ scroll CTE:

실전반 페이지에 방문한 이후에 스크롤 이벤트를 발생시킨 세션의 테이블을 정의한다

 

✅ click CTE:

실전 페이지에서 스크롤을 내려 신청하기 버튼까지 클릭한 세션의 테이블을 정의한다

 

✅ 최종 SELECT절:

• 실전반 페이지를 본 전체 세션 수를 집계해야 하기 때문에 INNER JOIN이 아닌 LEFT JOIN을 수행
• 동일한 개별 세션에 대해 pv, scroll, click 테이블을 조인
• 세 이벤트 발생 시각 간 선후관계를 고려하는 조건까지 추가

✔️ 실전반 페이지를 본 세션 수

✔️ 실전반 페이지를 본 후 스크롤을 내린 세션 수

✔️ 실전반 페이지를 본 후 스크롤을 내려 실전반 신청하기 버튼을 클릭한 세션 수

✔️ 실전반 페이지를 본 세션 중 스크롤을 내린 세션의 비율

✔️ 실전반 페이지를 본 세션 중 스크롤을 내리고 버튼까지 클릭한 세션의 비율

✔️ 실전반 페이지에서 스크롤을 내린 세션 중 버튼까지 클릭한 세션의 비율