186 랜딩 페이지와 두 번째 페이지의 전환율 확인하기

186 랜딩 페이지와 두 번째 페이지의 전환율 확인하기
유니버설 애널리틱스와 동일한 “랜딩 페이지”와 “두 번째 페이지”를 구하는 SQL 문입니다.

WITHcv_sessions AS

( SELECTDISTINCTga_session_id, 1 AScv

FROM( SELECT

(SELECTvalue.int_value FROMUNNEST(event_params) WHEREkey= 'ga_session_id') ASga_session_id

FROM`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202101*`

WHEREevent_name = 'purchase'

))

, pages AS(SELECTga_session_id, MAX(landing_page) ASlanding_page

, MAX(IF(second_page = landing_page, third_page, second_page)) ASsecond_page

FROM(

  SELECTga_session_id

  , FIRST_VALUE(page_title) OVER (PARTITION BYga_session_id ORDERBYevent_timestamp) ASlanding_page

  , NTH_VALUE(page_title,2) OVER (PARTITION BYga_session_id ORDERBYevent_timestamp) ASsecond_page

  , NTH_VALUE(page_title,3) OVER (PARTITION BYga_session_id ORDERBYevent_timestamp) ASthird_page

  FROM(

  SELECT

  event_timestamp

, (SELECTvalue.int_value FROMUNNEST(event_params) WHEREkey= 'ga_session_id') ASga_session_id

, (SELECTvalue.string_value FROMUNNEST(event_params) WHEREkey= 'page_title') ASpage_title

FROM`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202101*`

WHEREevent_name = 'page_view'

))

GROUPBYga_session_id)

SELECTlanding_page, second_page, COUNT(DISTINCTga_session_id) ASsessions, SUM(cv) AScvs

, ROUND(SUM(cv) / COUNT(DISTINCTga_session_id) * 100 , 2) AScvr_percent

FROM(

SELECTp.ga_session_id , p.landing_page , p.second_page , COALESCE(cvs.cv,0) AScv FROMpages ASp

LEFTJOINcv_sessions AScvs

ONp.ga_session_id = cvs.ga_session_id

)

--WHERE second_page is not null

GROUPBYlanding_page, second_page

ORDERBY3 DESC