[퍼널 분석] solvesql | 난이도 5 | 유입 채널별 실전반 전환율

유입 채널별 실전반 전환율

 

바로 전 게시글에서 다루었던 문제와 동일한 분석이며, 단지 유입 채널별로 값을 집계하는 부분에서 차이가 있다.

 

유입 채널은 유저가 특정 서비스에 방문할 수 있게 되는 경로로써 페이스북, 유튜브, 블로그 등 다양하게 존재한다. 어떤 채널로 들어오는 유저들의 전환율이 좋은지 등을 분석하고 채널의 특성과 운영 리소스를 고려하여 효율적인 전략을 세울 수 있다.

 

실전반 페이지를 본 이후에 스크롤을 내려본 세션은 얼마나 되는지, 또 스크롤을 내려 실전반 신청하기 버튼까지 클릭한 세션은 얼마나 되는지를 유입 채널별로 나누어 조회하는 쿼리를 작성해 보자.

 

문제 조건

 

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

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

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

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

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

• event_name = “SQL_advanced_form_click”

4. page_view 이벤트가 발생한 시점의 유입 채널 값을 기준으로 집계할 것

 

Solution

WITH pv AS (
  SELECT user_pseudo_id
       , ga_session_id
       , event_timestamp_kst AS pv_at
       , source
       , medium 
  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 p.source, p.medium
     , 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 -- 같은 개별 세션에 대해 JOIN (user_pseudo_id, ga_session_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
GROUP BY p.source, p.medium
ORDER BY pv DESC

 

✅ pv CTE:

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

 

✅ scroll CTE:

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

 

✅ click CTE:

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

 

✅ 최종 SELECT절:

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

 

✔️ 트래픽 획득 소스

✔️ 트래픽 획득 매체

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

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

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

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

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

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