해결된 질문
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
위클리, 먼슬리 리텐션을 작성하셨으면 이제 이 쿼리 결과를 해석해보시면 좋겠어요
데이터 기간을 짧게 설정하고 쿼리를 작성했으니, 이제 조금 더 길게 설정하고 데이터를 시각화한 후에 해석을 해보시면 어떨까요? 이 부분도 다양한 생각이 나올 수 있는데, 이런 연습을 해보는 것이 더 좋더라구요. 제 신입 시절에 리텐션을 학습할 때 쿼리만 작성하고 넘어갔는데, 나중에 회사에서 해석하는 것이 매우 어려웠어요(해보질 않았으니) 강의에서 나온 내용들을 참고해서 어떻게 해석할지도 한번 써보시면 어떨까 싶어요
연휴에 학습하시느라 넘 고생하시네요. 계속 파이팅입니다!