Google 애널리틱스 4 성과를 창출하는 분석 개선 기술

192 첫 방문 시 매체와 랜딩 페이지별 LTV 확인하기첫 방문의 매체와 랜딩 페이지별 사용자 수 및 LTV를 가져오는 SQL 문입니다. WITHltv AS( SELECTuser_pseudo_id, MAX(user_ltv.revenue) ASltv, MAX(traffic_source.medium) ASfirst_media FROM`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202101*` GROUPBYuser_pseudo_id) , flp AS( SELECTuser_pseudo_id , (SELECTvalue.string_value FROMUNNEST(event_params) WHEREkey= ‘page_location’) ASpage_location FROM`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` WHEREevent_name = “first_visit”) SELECTfirst_media, first_landing_page, COUNT(DISTINCTuser_pseudo_id) ASusers, SUM(ltv) ASltv , SUM(ltv)/COUNT(DISTINCTuser_pseudo_id) ASavg_ltv FROM( SELECTltv.first_media, flp.page_location…

191 첫 방문 후 N일 이내의 LTV로 첫 방문 획득을 최적화하기“첫 방문 후 30일 이내의 LTV” 사용자의 첫 번째 매체를 평가하는 SQL 문입니다. WITHmaster AS(   SELECTuser_pseudo_id   , MAX(user_first_touch_date) ASuser_first_touch_date   , MAX(user_first_medium) ASuser_first_medium   , SUM(revenue) ASrevenue FROM( SELECTuser_pseudo_id   , CAST(DATETIME_TRUNC(DATETIME(timestamp_micros(user_first_touch_timestamp),’America/Los_Angeles’), day) ASdate) ASuser_first_touch_date   , PARSE_DATE(“”%Y%m%d””,event_date) ASevent_date   , traffic_source.medium ASuser_first_medium   , ecommerce.purchase_revenue_in_usd ASrevenue FROM`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_2020*` WHEREevent_name IN(‘first_visit’,’purchase’) )…

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*`)…

189 애트리뷰션 개념을 콘텐츠 분석에 적용하기Google 애널리틱스 4의 데모 계정에서 “purchase” 이벤트를 전환으로 간주하여, 페이지별 전환 기여도를 애트리뷰션 모델별로 구하는 SQL 문입니다. WITHcv_users AS( SELECT user_pseudo_id , MAX(IF(event_name = ‘purchase’, 1, 0)) AScv_flag , MIN(IF(event_name = ‘purchase’, event_timestamp, NULL)) ASmin_cv_timestamp FROM`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202101*` GROUPBYuser_pseudo_id HAVINGcv_flag = 1 ) , pageviews AS(   SELECT* FROM(   SELECTuser_pseudo_id, event_name , event_timestamp…

188 평균 이외의 지표로 세그먼트의 진정한 평가를 수행하기국가별 사용자 참여도를 평균값, 중앙값, 표준편차로 구하는 SQL 문입니다. WITHmaster AS( SELECTuser_pseudo_id , MAX(geo.country) AScountry , SUM((SELECTvalue.int_value FROMUNNEST(event_params) WHEREkey= ‘engagement_time_msec’)) / 1000 ASengagement_time FROM`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131` GROUPBYuser_pseudo_id) , a AS( SELECTcountry, COUNT(DISTINCTuser_pseudo_id) ASusers , ROUND(AVG(engagement_time), 1) ASaverage_engagement_time FROMmaster GROUPBYcountry) , m AS( SELECTDISTINCTcountry , ROUND(PERCENTILE_CONT(engagement_time, 0.5) OVER(PARTITION BYcountry), 1) ASmedian_engagement_time…

187 지표 “페이지의 가치”로 페이지 평가하기유니버설 애널리틱스에 존재했던, 페이지의 전환 기여도를 시각화하는 지표인 “페이지의 가치”를 구하는 SQL 문입니다. WITHpage as(   SELECTga_session_id, page_title   FROM(   SELECT (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*`   ORDERBY1)   WHEREpage_title NOTIN(‘Shopping Cart’, ‘Checkout Your Information’, ‘Payment Method’, ‘Checkout Confirmation’, ‘Checkout Review’)   GROUPBYga_session_id, page_title) , revenue AS(   SELECTga_session_id, SUM(revenue)…

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…

185 유니버설 애널리틱스와 동일한 정의의 이탈률을 구하기유니버설 애널리틱스의 “이탈률” (1 페이지뷰만 발생한 세션의 비율)을 구하는 SQL 문입니다. WITHmaster AS( SELECTevent_date, ga_session_id, COUNT(*) ASpv_by_session FROM( SELECT event_date , (SELECTvalue.int_value FROMUNNEST(event_params) WHEREkey= ‘ga_session_id’) ASga_session_id FROM`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202011*` WHEREevent_name = ‘page_view’ ) GROUPBYevent_date, ga_session_id ) SELECTevent_date, COUNT(DISTINCTga_session_id) ASsessions, SUM(bounce) ASbounces , ROUND(SUM(bounce) / COUNT(DISTINCTga_session_id) * 100, 2) ASbounce_rate_percent FROM( SELECT…

183 BigQuery를 통한 고급 분석의 전제를 이해하기 FROM 절에 다음과 같은 내용이 있는 경우, Google 애널리틱스 4의 데모 계정 데이터를 참조하고 있음을 나타냅니다. `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_XXXXXXXX` 자사 데이터를 사용하여 분석을 수행하려면, FROM 절의 AAA 부분에 자사 BigQuery의 프로젝트 ID를, BBB 부분에 GA4의 프로퍼티 ID를, YYYYMMDD 부분에 분석하려는 날짜를 입력한 후 SQL 문을 실행하세요. FROM `AAA.analytics_BBB.events_YYYYMMDD`

028 추가된 태그의 동작을 BigQuery에서 확인하기 특정 시각 이후에 발생한 scroll 이벤트만을 대상으로 하여, 시계열 순으로 정렬한 후 “scroll” 이벤트가 발생한 “page_title”과 “percent_scrolled”를 목록으로 확인할 수 있습니다. SELECT event_timestamp, user_pseudo_id, event_name , (SELECT value.string_value FROM UNNEST(event_params) WHERE key = ‘page_title’) AS page_title , (SELECT value.int_value FROM UNNEST(event_params) WHERE key = ‘percent_scrolled’) AS percent_scrolled FROM ‘bigquerytableauoct.analytics_254394192.events_20220911’…