1. Weekly Retention 쿼리 작성하기

앱 접속을 기준으로 리텐션 쿼리를 작성하였습니다.

# 중간 예상: user_pseudo_id | event_week | first_event_week | diff_of_week
# 최종 예상: diff_of_week | user_cnt | first_user_cnt | retention_rate
WITH base AS (
  SELECT 
    DISTINCT
      user_pseudo_id,
      DATE_TRUNC(DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')), WEEK(MONDAY)) AS event_week
  FROM advanced.app_logs
  -- WHERE event_date BETWEEN '2022-08-01' AND '2022-11-01'
), base_first_event_week AS (
  SELECT 
    user_pseudo_id,
    event_week,
    FIRST_VALUE(event_week) OVER(PARTITION BY user_pseudo_id ORDER BY event_week) AS first_event_week
  FROM base
), base_diff_of_week AS (
  SELECT
    user_pseudo_id,
    DATE_DIFF(event_week, first_event_week, WEEK) AS diff_of_week
  FROM base_first_event_week
), retain_base AS (
  SELECT
    diff_of_week,
    COUNT(DISTINCT user_pseudo_id) AS user_cnt
  FROM base_diff_of_week
  GROUP BY diff_of_week
)

SELECT 
  rb.diff_of_week
  , rb.user_cnt
  , tmp.user_cnt AS first_user_cnt
  , ROUND(SAFE_DIVIDE(rb.user_cnt, tmp.user_cnt), 3) AS retention_rate 
FROM retain_base rb
CROSS JOIN (
  SELECT 
    diff_of_week,
    user_cnt
  FROM retain_base
  WHERE diff_of_week=0
) tmp
ORDER BY rb.diff_of_week

2. Retain User를 New + Current + Resurrected + Dormant User로 나누는 쿼리 작성하기

2-1) 유저 정의

주 별로 리텐션을 본다고 가정하면, 가장 간단하게 아래와 같이 정의할 수 있을 것 같습니다.

• New User
  • 앱을 이번 주에 처음 사용하는 사람
• Current User
  • 앱을 이번 주에 사용하는 사람
• Resurrected User
  • 앱을 이번 주에 사용하는 사람
  • 앱을 지난주에는 사용하지 않았던 사람
  • 앱을 과거에 사용한 적이 있는 사람
• Dormant User
  • 앱을 이번주에 사용하지 않는 사람
  • 앱을 과거에 사용한 적이 있는 사람

2-2) 쿼리 작성

위 정의로 작성한 전체 쿼리입니다.

접속 이후 주차 별 사용자 상태 값을 구하기 위해 아래 세 컬럼을 추가로 사용하였습니다.

• total_event_week: 모든 이벤트 주.
• next_event_week: 사용자의 다음 이벤트 주. 조인 시 다음 이벤트 주 이전 이전의 값 까지만 조인하기 위해 사용.
• last_diff_of_week: 직전 이벤트 사이 간격. diff_of_week 값과 비교해 사용자 상태를 구하기 위해 사용.