3-7. 리텐션 쿼리 작성해보기(Weekly, Montly)

• Weekly 리텐션 분석

#Weekly 리텐션
with base as (
select
  distinct
  user_id,
  user_pseudo_id,
  event_name,
  date(datetime(timestamp_micros(event_timestamp), "Asia/Seoul")) as event_date,
  datetime(timestamp_micros(event_timestamp), "Asia/Seoul") as event_datetime,
from `advanced.app_logs`
where
  event_date between "2022-08-01" and "2022-11-03"
),

retain_base as (
select
  user_pseudo_id,
  event_week,
  first_week,
  date_diff(event_week, first_week, week) as diff_of_week
from (
  select
    distinct
    user_pseudo_id,
    date_trunc(event_date, week(monday)) as event_week,
    date_trunc(min(event_date) over(partition by user_pseudo_id),week(monday)) as first_week, -- 유저의 첫 접속 주차
  from base
  )
)

select
  diff_of_week,
  user_cnt,
  first_value(user_cnt) over(order by diff_of_week) as first_user_cnt
from (
  select
    diff_of_week,
    count(distinct user_pseudo_id) as user_cnt
  from retain_base
  group by
    diff_of_week
  )
order by
  diff_of_week

• Montly 리텐션 분석

#Montly 리텐션
with base as (
select
  distinct
  user_id,
  user_pseudo_id,
  event_name,
  date(datetime(timestamp_micros(event_timestamp), "Asia/Seoul")) as event_date,
  datetime(timestamp_micros(event_timestamp), "Asia/Seoul") as event_datetime,
from `advanced.app_logs`
where
  event_date between "2022-08-01" and "2022-11-03"
),

retain_base as (
select
  user_pseudo_id,
  event_month,
  first_month,
  date_diff(event_month, first_month, month) as diff_of_month
from (
  select
    distinct
    user_pseudo_id,
    date_trunc(event_date, month) as event_month,
    date_trunc(min(event_date) over(partition by user_pseudo_id),month) as first_month, -- 유저의 첫 접속 주차
  from base
  )
),

month_retain as (
select
  diff_of_month,
  user_cnt,
  first_value(user_cnt) over(order by diff_of_month) as first_user_cnt
from (
  select
    diff_of_month,
    count(distinct user_pseudo_id) as user_cnt
  from retain_base
  group by
    diff_of_month
  ) 
)

select
  diff_of_month,
  user_cnt,
  first_user_cnt,
  round(safe_divide(user_cnt, first_user_cnt),3) as retention_rate
from month_retain
order by
  diff_of_month

위클리랑 먼슬리 리텐션 쿼리 작성했습니다~