191 첫 방문 후 N일 이내의 LTV로 첫 방문 획득을 최적화하기

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

)

WHEREDATE_DIFF(event_date, user_first_touch_date, day) <=30 ANDuser_first_touch_date >= '2020-11-01'

GROUPBYuser_pseudo_id

)

SELECTuser_first_medium, COUNT(DISTINCTuser_pseudo_id) ASusers

  , SUM(revenue) ASltv_within_30days_from_first_visit

  , ROUND(SUM(revenue)/COUNT(DISTINCTuser_pseudo_id),2) ASavg_ltv_within_30days_from_first_visit

FROMmaster

GROUPBYuser_first_medium

ORDERBY2 DESC