결과 예상해보기

diff_of_week | user_cnt | first_week_user_cnt | retention_rate

쿼리

WITH base AS (
  SELECT
    DISTINCT
      user_id,
      user_pseudo_id,
      DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_date,
      DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,
      event_name
  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), 2) AS retention_rate
FROM (
  SELECT
    *,
    FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week) AS first_week_user_cnt
  FROM user_counts
)

쿼리 결과

시각화

해석

• 가입 주차(0주차) 이후 1주차 retention rate는 0.08로, 초기 이탈율이 92%로 매우 높은 수준이다.
  • 초기 사용자의 유지율이 낮아 첫 방문 이후 재방문으로 이어지지 않고 있다. 이 부분에 대한 원인 분석이 필요하며, 액션 아이템이 필요하다.
• 1주차부터 10주차까지 retention rate가 0.08~0.09 수준으로 유지됨.
  • 소수의 충성 사용자가 꾸준히 재방문하고 있음을 의미한다.
  • 해당 충성 사용자 그룹의 페르소나를 파악하면 리텐션 개선 전략에 활용할 수 있다.
• 11주차부터 점진적으로 감소, 23주차 부터는 0 수준으로 재방문이 거의 없다고 볼 수 있다.
  • 위 결과는 전체 기간 기준 리텐션이므로, 코호트별 신규 유저 유입 및 유지 상태를 파악하기 위해 코호트 리텐션 분석이 필요하다.