2024-08-19T06:15:28.019Z

1

상세하게 답변주셔서 감사합니다! 😃

1. Resurrected가 늘어나는 부분은 저도 흥미롭긴 했는데.. 추가로 살펴보고 공유드리겠습니다!

2. 특정 코호트인 유저 vs 아닌 유저의 경우에는 쿼리를 여러 코호트랑 한 번에 비교하는 쿼리를 어떻게 작성해야할지 몰라서 약간 코호트 하나하나씩 봤던 것 같아요ㅠ 일단 아래 처럼 작성했었습니다..!

    

#과제3. 리텐션이 높은 유저 그룹 찾는 쿼리 작성하기

WITH base AS (
SELECT
  TIMESTAMP_ADD(TIMESTAMP_MICROS(event_timestamp), INTERVAL 540 MINUTE) AS event_timestamp, -- 한국 시간 기준으로 변경
  event_name,
  user_id,
  user_pseudo_id,
  platform
FROM `advanced.app_logs`
WHERE
  1=1
  AND event_name = "screen_view" -- screen_view 이벤트가 있으면 활동한 유저 
),

-- 1. 유저 그룹 쿼리
usergroup_base AS (
SELECT
  user_pseudo_id,
  event_week,
  LAG(event_week) OVER(PARTITION BY user_pseudo_id ORDER BY event_week ASC) AS prior_event_week,
  MIN(event_week) OVER(PARTITION BY user_pseudo_id ORDER BY event_week ASC) AS first_event_week
FROM (
  SELECT
    DISTINCT
    user_pseudo_id,
    event_name,
    DATE_TRUNC(PARSE_DATE('%Y-%W', FORMAT_TIMESTAMP('%Y-%W', event_timestamp)), WEEK(MONDAY)) AS event_week,
  FROM base
  )
),

usergroup AS (
SELECT
  user_pseudo_id,
  event_week,
  DATE_DIFF(event_week, prior_event_week, WEEK(MONDAY)) AS diff_of_prior_week, 
  DATE_DIFF(event_week, first_event_week, WEEK(MONDAY)) AS diff_of_first_week,
  CASE
    WHEN prior_event_week IS NULL THEN "New" -- 첫 접속 주차와 동일하 경우 New로 정의
    WHEN DATE_DIFF(event_week, prior_event_week, WEEK(MONDAY)) = 1 THEN "Current" -- 주 평균 1.6회 배달앱 사용하므로 일주일 내로 접속 시 Current 유저로 정의
    WHEN DATE_DIFF(event_week, prior_event_week, WEEK(MONDAY)) > 1 THEN "Resurrected" -- 나머지는 복귀 유저로 정의
    WHEN DATE_DIFF(event_week, prior_event_week, WEEK(MONDAY)) > 5 THEN "Dormant" -- 보통 배달 앱의 경우 한 달 단위로 멤버십 변경되기 때문에 5주(한 달) 초과하여 재접속한 경우 휴먼 유저로 정의
  END AS user_group
FROM usergroup_base
),

cohortusergroup AS (
SELECT
  DISTINCT
  user_pseudo_id
FROM usergroup
WHERE
  1=1
  AND user_group = "Resurrected"
  AND FORMAT_DATE("%Y-%m", event_week) = "2022-10"
),

-- 2. 위클리 리텐션 기본 쿼리
retain_base AS (
SELECT
  user_pseudo_id,
  event_name,
  event_date,
  MIN(event_date) OVER(PARTITION BY user_pseudo_id ORDER BY event_date ASC) AS first_date,
  cohort
FROM (
  SELECT
    DISTINCT
    DATE(b.event_timestamp) AS event_date,
    b.event_name,
    b.user_pseudo_id,
    CASE
      WHEN c.user_pseudo_id IS NULL THEN "Not"
      ELSE "Cohort"
    END AS cohort -- 코호트 그룹과 아닌 그룹과 비교할 값 추가하기
  FROM base AS b
  LEFT JOIN cohortusergroup AS c
    ON b.user_pseudo_id = c.user_pseudo_id
  )
),

weekly_retention AS (
SELECT
  diff_of_week,
  cohort,
  COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM (
SELECT
  user_pseudo_id,
  event_name,
  event_date,
  first_date,
  cohort,
  DATE_DIFF(event_date, first_date, WEEK(MONDAY)) AS diff_of_week
FROM retain_base
  )
GROUP BY ALL
)

SELECT
  diff_of_week,
  cohort,
  user_cnt,
  first_week_user_cnt,
  ROUND(SAFE_DIVIDE(user_cnt, first_week_user_cnt),3) AS retention_rate
FROM (
  SELECT
    diff_of_week,
    cohort,
    user_cnt,
    FIRST_VALUE(user_cnt) OVER(PARTITION BY cohort ORDER BY diff_of_week) AS first_week_user_cnt
  FROM weekly_retention
)
ORDER BY
  1, 2

3. 코어 이벤트 관련해서도 시간날 때 보고 과제 업데이트해보겠습니다~!