인프런 커뮤니티 질문&답변

붕어빵님의 프로필 이미지

작성한 질문수

BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)

3-7. 리텐션 SQL 쿼리 작성하기 : Weekly Retention

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

해결된 질문

24.08.15 11:03 작성

·

62

0

 

  • 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

 

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

 

답변 1

1

카일스쿨님의 프로필 이미지
카일스쿨
지식공유자

2024. 08. 15. 11:14

붕어빵님 안녕하세요!

오 제가 생각하는 형태의 쿼리와 동일하게 작성해주셨어요! 고생하셨네요

조금 더 도움이 되고자 피드백을 드리면

  • SQL의 기본 문법의 경우 대문자로 쓰는 것을 권장하곤 합니다(입문편의 가독성 있는 쿼리 부분에 나와있어요)

    • Reserved Words라고 해서 미리 정의된 것을 우리가 변수로 작성하는 것과 구분하기 위함입니다

    • 성능에 이슈가 있는 것은 아니고, 가독성을 챙기는 관점에서 신경쓰곤 합니다. 이 부분을 평소부터 습관으로 만들어야 나중에도 잘 사용할 수 있더라구요

    • 물론 회사마다 달라서 어떤 회사에서는 소문자로 쓸 수도 있을 것 같기도 하네요(제가 본 회사들은 대문자로 쓰거나, 아예 신경을 안쓰거나가 더 많긴 했어요)

    • 참고 문서 : https://docs.telemetry.mozilla.org/concepts/sql_style#reserved-words

  • 위클리, 먼슬리 리텐션을 작성하셨으면 이제 이 쿼리 결과를 해석해보시면 좋겠어요

    • 데이터 기간을 짧게 설정하고 쿼리를 작성했으니, 이제 조금 더 길게 설정하고 데이터를 시각화한 후에 해석을 해보시면 어떨까요? 이 부분도 다양한 생각이 나올 수 있는데, 이런 연습을 해보는 것이 더 좋더라구요. 제 신입 시절에 리텐션을 학습할 때 쿼리만 작성하고 넘어갔는데, 나중에 회사에서 해석하는 것이 매우 어려웠어요(해보질 않았으니) 강의에서 나온 내용들을 참고해서 어떻게 해석할지도 한번 써보시면 어떨까 싶어요

연휴에 학습하시느라 넘 고생하시네요. 계속 파이팅입니다!