데이터리안 - SQL 데이터 분석캠프 실전반- 3주차: 퍼널분석 실습

본 내용은 데이터리안 'SQL 데이터 분석 캠프 실전반'을 수강하며 작성한 내용입니다.

Step 1. 입문반 페이지를 본 세션 수 구하기

SELECT COUNT(DISTINCT CONCAT(user_pseudo_id, ga_session_id))
FROM ga
WHERE page_title = '백문이불여일타 SQL 캠프 입문반'
  AND event_name = 'page_view'

배운점

• 빅쿼리에서 여러개 컬럼을 한 계정으로 묶어 카운팅하는 게 안 된다고 한다. 그래서 CONCAT함수 활용.
• CONCATE(): CONCAT 함수는 문자열을 결합하는 데 사용되는 함수입니다. 이 함수는 두 개 이상의 문자열을 결합하여 하나의 문자열로 만들 수 있습니다.

Step 2. 입문반 페이지 뷰에서 스크롤까지 전환된 세션의 비율 구하기

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'
)

SELECT COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) AS pv
     , COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id) AS scroll_after_pv
     , COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id) / COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) AS pv_scroll_rate
FROM pv
  LEFT JOIN scroll ON pv.user_pseudo_id = scroll.user_pseudo_id
                   AND pv.ga_session_id = scroll.ga_session_id
                   AND pv.pv_at <= scroll.scroll_at

Step 3. 입문반 페이지뷰, 스크롤을 거쳐 입문반 신청 버튼 클릭까지의 전환율 구하기

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 IN ('SQL_basic_form_click', 'SQL_basic_1day_form_click', 'SQL_package_form_click')
)

SELECT COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) AS pv
     , COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id) AS scroll
     , COUNT(DISTINCT click.user_pseudo_id, click.ga_session_id) AS click
     , COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id) / COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) AS pv_scroll_rate
     , COUNT(DISTINCT click.user_pseudo_id, click.ga_session_id) / COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id) as scroll_click_rate
     , COUNT(DISTINCT click.user_pseudo_id, click.ga_session_id) / COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) AS pv_click_rate
FROM pv
  LEFT JOIN scroll ON pv.user_pseudo_id = scroll.user_pseudo_id
                   AND pv.ga_session_id = scroll.ga_session_id
                   AND pv.pv_at <= scroll.scroll_at
  LEFT JOIN click ON scroll.user_pseudo_id = click.user_pseudo_id
                  AND scroll.ga_session_id = click.ga_session_id
                  AND scroll.scroll_at <= click.click_at