190 조회 시 CVR이 높아지는 페이지를 포괄적으로 탐색하기

190 조회 시 CVR이 높아지는 페이지를 포괄적으로 탐색하기
여러 콘텐츠에 대해 “표시한 사용자”, “표시하고 전환한 사용자”, “표시하지 않은 사용자”, “표시하지 않았지만 전환한 사용자”를 시각화하여, 어떤 콘텐츠를 표시했을 때 사용자 단위 전환율이 어떻게 변화하는지 포괄적으로 시각화하는 SQL 문입니다.

WITHmaster AS(

SELECTDISTINCTuser_pseudo_id AScid

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

), pvu AS(

SELECT* FROM(

SELECTuser_pseudo_id AScid

, (SELECTvalue.string_value FROMUNNEST(event_params) WHEREkey= "page_title") ASpage

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

WHEREREGEXP_CONTAINS(page, r"^(Home|Men's\s/\sUnisex\s\|\sApparel|Sale\s\|\sG|New\s\|\sG|Small\sGoods\s\|\sLifestyle|YouTube\s\|\sS|Bags\s\|)") IStrue

groupbycid, page

), cvu AS(

SELECTDISTINCTuser_pseudo_id AScid, 1 AScv

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

WHEREevent_name = "purchase")

SELECT*, ROUND(view_user_cvr / non_view_user_cvr, 1) ASdifference_ratio

FROM(

SELECT*

, ROUND(non_view_cv_users / non_view_users, 4) ASnon_view_user_cvr

FROM(

SELECT*, ROUND(view_cv_users / view_users, 4) ASview_user_cvr

, (SELECTCOUNT(DISTINCTcid) FROMmaster) - view_users ASnon_view_users

, (SELECTSUM(cv) FROMcvu) - view_cv_users ASnon_view_cv_users

FROM(

SELECTpage, COUNT(DISTINCTview_users) ASview_users, SUM(cv) ASview_cv_users

FROM(

SELECTmaster.cid ASall_users, pvu.cid ASview_users, pvu.page, cvu.cv

FROMmaster

LEFTJOINpvu

USING (cid)

LEFTJOINcvu

USING (cid)

WHEREpage ISNOTNULL

) GROUPBYpage)))

ORDERBY2 DESC