[빠짝스터디 3주차 과제] 리텐션 과제

Q1. Weekly Retention을 구하는 쿼리를 바닥부터 스스로 작성해 보세요.

WITH base AS(
  SELECT
    DISTINCT
      user_id,
      event_name,
      DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,
      DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
      user_pseudo_id
  FROM advanced.app_logs
), first_week_and_diff AS(
  SELECT
    *,
    DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week
  FROM (
    SELECT
      DISTINCT
        user_pseudo_id,
        DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week,
        DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week
    FROM base
  )
), user_counts AS(
  SELECT
    diff_of_week,
    COUNT(DISTINCT user_pseudo_id) AS user_cnt
  FROM first_week_and_diff
  GROUP BY diff_of_week
)

SELECT
  *,
  ROUND(SAFE_DIVIDE(user_cnt, first_week_user_cnt), 3) AS retention_ratio
FROM (
  SELECT
    diff_of_week,
    user_cnt,
    FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week ASC) AS first_week_user_cnt
  FROM user_counts
)

Q2. Retain User를 New + Current + Resurrected + Dormant User로 나누는 쿼리를 작성해 보세요.

일단 이 앱로그가 배달 주문하는 앱이라는 점을 가장 먼저 고려해야한다.
기준 일자를 정해야 하는데 이때 휴먼 유저 ( 앱을 사용하지 않는다고 판단되는 유저 )의 비중을 30~40%을 잡아야된다고 판단했습니다.
- 이유는 결국 이 유저들을 나누는 이유가 나누어진 그룹을 통해 목표를 설정하고 유지 관리가 필요한데 휴먼 유저의 비중을 너무 높게 잡는다면 많은 이탈자에 초점이 맞춰질거같고 그렇다고 사용 유저의 비중을 너무 높이기엔 휴먼 유저에 대한 대처(?)가 안될거라고 판단했습니다
- 설명을 잘 못하겠네요…
휴먼 유저 비중이 30~40가 되는 날짜가 “45일” 가장 좋다고 판단해서 45일을 기준으로 User Group을 나누었습니다

User Group	이름	정의
New_User	신규 유저	현재 날짜 기준 45일 이내 최초 접속자
Current_User	사용 유저	이전 접속일자 와 현재 날짜 기준 모두 45일 이내 접속자
Resurrected_User	복귀 유저	이전 접속일자 45일 초과 현재 날짜 기준 45일 이내 접속자
Dormant_User	휴먼 유저	현재날짜 기준 45일 초과 접속자

WITH base AS(
  SELECT
    DISTINCT
      user_pseudo_id,
      DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
  FROM advanced.app_logs
), previous_date AS(
  SELECT
    *,
    LAG(event_date) OVER(PARTITION BY user_pseudo_id ORDER BY event_date) AS previous_event_date
  FROM base
), previous_date_diff AS(
  SELECT
    *,
    DATE_DIFF(event_date, previous_event_date, DAY) AS days_since_last_date,
  FROM previous_date
), last_date_diff AS(
  SELECT
    DISTINCT
      user_pseudo_id,
      LAST_VALUE(days_since_last_date) OVER(PARTITION BY user_pseudo_id ORDER BY event_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_days_since_last_date,
      LAST_VALUE(event_date) OVER(PARTITION BY user_pseudo_id ORDER BY event_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_event_date
  FROM previous_date_diff
), final_diff AS (
  SELECT
    user_pseudo_id,
    last_days_since_last_date,
    DATE_DIFF(DATE '2023-01-20', last_event_date, DAY) AS final_diff_day
  FROM last_date_diff
), user_group AS (
  SELECT
    user_pseudo_id,
    last_days_since_last_date,
    final_diff_day,
    CASE WHEN last_days_since_last_date IS NULL AND final_diff_day <= 45 THEN "New_User"
         WHEN last_days_since_last_date <= 45 AND final_diff_day <= 45 THEN "Current_User"
         WHEN last_days_since_last_date > 45 AND final_diff_day <= 45 THEN "Resurrected_User"
         WHEN final_diff_day > 45 THEN "Dormant_User"
    END AS user_group_name
    FROM final_diff
)

SELECT
  user_group_name,
  COUNT(*) AS group_cnt
FROM user_group
GROUP BY user_group_name;

Q3. 주어진 데이터에서 어떤 사람들이 리텐션이 그나마 높을까요? 찾아보세요.

해보고 싶은 분석이 있는데 쿼리문 작성의 시간이 좀 오래걸릴거 같아서 추후 작성해보도록하겠습니다…
간단하게 세션을 앞선 강의를 참고해서 screen_view-welcome → screen_view-home → screen_view-food_category → screen_view-restaurant → screen_view-cart → click_payment-cart 를 하나의 주문이라 생각하고
주문을 한번이라도 끝까지 해본 그룹과 그렇지 않은 그룹을 나눠서 리텐션을 구해보고 분석해보고 싶습니다!

Q4. Core Event를 “click_payment”라고 설정하고 Weekly Retention을 구해주세요.

WITH base AS(
  SELECT
    DISTINCT
      user_id,
      event_name,
      DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,
      DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
      user_pseudo_id
  FROM advanced.app_logs
  WHERE
    event_name = "click_payment"
), first_week_and_diff AS(
  SELECT
    *,
    DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week
  FROM (
    SELECT
      DISTINCT
        user_pseudo_id,
        DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week,
        DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week
    FROM base
  )
), user_counts AS(
  SELECT
    diff_of_week,
    COUNT(DISTINCT user_pseudo_id) AS user_cnt
  FROM first_week_and_diff
  GROUP BY diff_of_week
)

SELECT
  *,
  ROUND(SAFE_DIVIDE(user_cnt, first_week_user_cnt), 3) AS retention_ratio
FROM (
  SELECT
    diff_of_week,
    user_cnt,
    FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week ASC) AS first_week_user_cnt
  FROM user_counts
)