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

# Weekly Retention Query 작성

# 1. 기본 데이터셋 구성
WITH base AS 
(
  SELECT 
    DISTINCT
    * EXCEPT(event_params, event_date),
    DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date
  FROM advanced.app_logs
  CROSS JOIN UNNEST(event_params) AS event_param
)

# 2. 유저 별 week_date, first_week, date_diff 구하기
, user_week_diff AS 
(
SELECT
  *,
  DATE_DIFF(week_date, first_week, WEEK) AS week_diff
FROM (
SELECT 
  DISTINCT
  user_pseudo_id,
  DATE_TRUNC(event_date, WEEK) AS week_date,# week_date
  DATE_TRUNC(MIN(event_date) OVER (PARTITION BY user_pseudo_id), WEEK) AS first_week # first_week 
FROM base
))
, user_count AS
(
# 3. 첫 주차, week_diff 별로 유저 수 집계
SELECT 
  first_week,
  week_diff,
  COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM user_week_diff
GROUP BY ALL
)

# 4. 전체 유저 수, 비율 계산
SELECT
  *,
  ROUND(SAFE_DIVIDE(user_cnt, first_week_user_cnt), 2) AS retention_rate
FROM (
  SELECT
    first_week,
    week_diff,
    user_cnt,
    FIRST_VALUE(user_cnt) OVER(PARTITION BY first_week ORDER BY week_diff ASC) AS first_week_user_cnt
  FROM user_count
)
ORDER BY 1, 2

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

• 유저 앱 사용 주기 확인해보기

# 유저들의 이용 주기 확인해보기
WITH base AS 
(
  SELECT 
    user_pseudo_id,
    event_name,
    DATETIME(TIMESTAMP_MICROS(event_timestamp)) AS event_datetime,
  FROM advanced.app_logs
)

# 이전 사용 날짜와의 차이 계산
, diff_days AS 
(
SELECT 
  *,
  DATE_DIFF(event_datetime, before_datetime, DAY) AS diff_day
FROM 
(
SELECT 
  *,
  LAG(event_datetime) OVER (PARTITION BY user_pseudo_id ORDER BY event_datetime) AS before_datetime
FROM base
)
)

# 주기 별 유저 수 집계
SELECT 
  diff_day,
  COUNT(DISTINCT user_pseudo_id) AS cnt
FROM diff_days
GROUP BY ALL
ORDER BY 1

→ 유저들의 앱 사용 주기를 확인해보았을 때 눈에 띌만한 부분은 확인하지 못해서, 전체 앱 사용 주기의 평균을 구해보았다. 평균 사용 주기는 약 20.76일로 나와 Current user 정의하는데 필요한 기준을 21일로 설정하였다.

<aside> ❓ 위에서 평균 사용 주기를 할 때 유저별로 구한 것이 아니고, 모든 행들의 diff_day를 구한 후, 평균을 구했습니다. 이렇게 구한 방식이 문제가 되지는 않는지 궁금합니다. 개인적인 생각으로는 “개인별 평균 앱 이용 주기를 구하고, 이를 통해서 모든 유저들의 평균을 구해야 하지 않나?” 라는 생각이 들어서요..

</aside>

• 각 유저 분류 정의하기

  • New user : 앱 사용 날짜가 첫 사용 날짜 이후 일주일 이내

    → 앱 사용 날짜 - 첫 사용 날짜 ≤ 7

  • Current user : 앱 사용 날짜가 이전 사용 날짜 이후 21일 이내

    → 앱 사용 날짜 - 이전 앱 사용 날짜 ≤ 21

  • Resurrected user: 앱 사용 날짜가 이전 사용 날짜 22일 이후

    → 앱 사용 날짜 - 이전 앱 사용 날짜 > 21

  • Dormant user: ??

<aside> ❓ 해당 데이터에서 Dormant user를 어떻게 정의할 수 있을지 모르겠습니다.. ㅠ 데이터에서는 앱 사용 기록이 남는 것이기 때문에 유저별로 마지막 데이터는 모두 복귀유저로 봐야하는 것 아닌가요? (이전 기록과의 주기가 21일보다 클 경우)

</aside>

# 유저 세그먼트
# New user: 앱 사용 날짜 - 첫 사용 날짜 ≤ 7
# Current user: 앱 사용 날짜 - 이전 앱 사용 날짜 ≤ 21
# Resurrected user: 앱 사용 날짜 - 이전 앱 사용 날짜 > 21
# Dormant user: ??

# base dataset 생성
WITH base AS 
(
  SELECT
    * EXCEPT(event_params, event_date),
    DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date
  FROM advanced.app_logs
  CROSS JOIN UNNEST(event_params) AS event_param
)

# user 별 first_date 구하고, lag 이용하여 이전 사용 날짜열 생성 후, 
# 이전 날짜와 차이 계산
, date_diff_table AS
  (
  SELECT
    *,
    DATE_DIFF(event_date, first_date, DAY) AS first_date_diff,
    DATE_DIFF(event_date, before_date, DAY) AS before_date_diff
  FROM 
  (
    SELECT
      DISTINCT
      user_pseudo_id,
      event_name,
      MIN(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date) AS first_date,
      event_date,
      LAG(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date) AS before_date
    FROM base
  )
  )

# 조건에 맞게 유저 분류
SELECT
  DISTINCT
  user_pseudo_id,
  first_date,
  event_date,
  before_date,
  CASE WHEN first_date_diff <= 7 THEN "New"
       WHEN before_date_diff <= 21 THEN "Current"
       WHEN before_date_diff > 21 THEN "Resurrected"
       ELSE "Dormant" END AS user_segment
FROM date_diff_table
WHERE before_date IS NULL OR event_date != before_date # 유저별로 첫번째 사용기록 가져오고, 같은 날 사용한 기록은 지우기 위해 조건 부여
                                                       # (Resurrected user가 같은 날 추가 행동을 하면 Current user가 될 수도 있으므로..) 
                                                       # 이는 일별 리텐션을 보기 위해 적용한 조건
ORDER BY user_pseudo_id, event_date

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

<aside> ❓ 위에서 작성한대로 유저를 나눠서 리텐션을 계산해보려고 했는데 어떻게 구해야할지 막막해서 질문을 남깁니다. 유저를 날짜를 토대로 New, Current, Resurrected user로 구분을 했는데 구분한 상태에서 리텐션을 어떻게 구해야할지 모르겠습니다. 예를 들어 유저별로 New user인 경우는 첫 행만 존재하고, 그 후부터는 Current or Resurrected user가 되는데 New user의 리텐션을 구할 수 있을까요..? 제가 처음부터 유저 분류를 잘못한 것일까요…? ㅠ

</aside>

먼저 여쭤보고 피드백에 따라 다시 작성할 수 있도록 하겠습니다.

4. Core event를 ‘click_payment’ 라고 설정하고 Weekly Retention을 구해주세요.

# Event_name = "click_payment"인 경우의 Weekly Retention Query 작성
# 코호트별 리텐션을 구해보았는데 뚜렷한 특징없이 대부분 동일하게 1~4%를 보여 코호트 구분 없이 주차별 리텐션을 구하기로 했습니다.
WITH base AS 
(
  SELECT
    DISTINCT
    * EXCEPT(event_params, event_date),
    DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date
  FROM advanced.app_logs
  CROSS JOIN UNNEST(event_params) AS event_param
  WHERE event_name = "click_payment"
)
, user_week_diff AS
(
  SELECT 
  *,
  DATE_DIFF(week_date, first_week, WEEK) AS week_diff
  FROM
  (
    SELECT
      DISTINCT
      user_pseudo_id,
      DATE_TRUNC(event_date, WEEK) AS week_date,# week_date
      DATE_TRUNC(MIN(event_date) OVER (PARTITION BY user_pseudo_id), WEEK) AS first_week # first_week 
    FROM base
  )
)
, week_diff_cnt AS 
(
  SELECT
    week_diff,
    COUNT(user_pseudo_id) AS user_cnt
  FROM user_week_diff
  GROUP BY 1
)

SELECT
  *,
  ROUND((SAFE_DIVIDE(user_cnt, first_week_cnt) * 100), 2) AS pct
FROM
(
  SELECT
    *,
    FIRST_VALUE(user_cnt) OVER(ORDER BY week_diff) AS first_week_cnt
  FROM week_diff_cnt
)

결제 유저들에 대한 weekly retention을 구해본 결과 첫 주차부터 크게 떨어진 것을 확인할 수 있었다. 이에 대한 원인이 유저들이 신규 가입 쿠폰만 한 번 사용하고 다른 앱을 사용한다던지, 어떤 payment type으로 결제한 그룹이 더 잔존율이 높을지, 그리고 퍼널의 어느 부분에서 이탈이 이루어졌는지 등 세분화하여 분석하고, 그에 맞는 해결 방안을 고민해봐야한다.