189 애트리뷰션 개념을 콘텐츠 분석에 적용하기

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

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

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

WHEREevent_name IN('page_view', 'purchase')

  ) WHEREpage_title NOTIN('Shopping Cart', 'Checkout Your Information', 'Payment Method', 'Checkout Confirmation', 'Checkout Review')

)

, master AS(

  SELECT* FROM(

SELECTpv.user_pseudo_id

, pv.event_name

, pv.page_title

, pv.event_timestamp

, cv.min_cv_timestamp

, ROW_NUMBER() OVER(PARTITION BYpv.user_pseudo_id ORDERBYpv.event_timestamp) ASrow_num

FROMpageviews ASpv

INNERJOINcv_users AScv

ONpv.user_pseudo_id = cv.user_pseudo_id

  ) WHEREevent_timestamp < min_cv_timestamp

)

SELECTpage_title

, SUM(first_touch) ASfirst_touch

, SUM(last_touch) ASlast_touch

, SUM(linear) ASlinear

, SUM(touch_point) AStouch_point

FROM(

SELECT*

, IF(row_num = MIN(row_num) OVER(PARTITION BYuser_pseudo_id), 1, 0) ASfirst_touch

, IF(row_num = MAX(row_num) OVER(PARTITION BYuser_pseudo_id), 1, 0) ASlast_touch

, 1 / MAX(row_num) OVER(PARTITION BYuser_pseudo_id) ASlinear

, CASE

WHENMAX(row_num) OVER(PARTITION BYuser_pseudo_id) = 1 THEN1

WHENMAX(row_num) OVER(PARTITION BYuser_pseudo_id) = 2 THEN0.5

ELSE

  CASE

WHENrow_num = MIN(row_num) OVER(PARTITION BYuser_pseudo_id) THEN0.4

WHENrow_num = MAX(row_num) OVER(PARTITION BYuser_pseudo_id) THEN0.4

ELSE0.2 / (MAX(row_num) OVER(PARTITION BYuser_pseudo_id) - 2)

END

ENDAStouch_point

  FROMmaster

) GROUPBYpage_title

ORDERBY2 DESC