1. Weekly Retention을 구하는 쿼리를 바닥부터 스스로 작성해보기
2. Retain User를 New + Current + Resurrected + Dormant User로 나누는 쿼리를 작성하기
3. 주어진 데이터에서 어떤 사람들이 리텐션이 그나마 높을지 찾아보기
4. Core Event를 “click_payment”라고 설정하고 Weekly Retention을 구하기

→ 정답은 없고, 스스로 처음부터 생각해보기

Weekly Retention 쿼리 작성

WITH base AS(
  SELECT
    DISTINCT
      user_id,
      user_pseudo_id,
      event_name,
      DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
      DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime
  FROM advanced.app_logs
  WHERE
  event_date BETWEEN '2022-08-01' AND '2022-10-31'
), first_date_diff AS (
  SELECT
    *,
    DATE_DIFF(event_week, fisrt_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 fisrt_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_date_diff
  GROUP BY diff_of_week
  ORDER BY diff_of_week
)

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

• Weekly_Retention에 대해 바닥부터 작성
• 데이터 처리 과정에서 필요없는 데이터들을 활용하지 않고 가장 효율적으로 사용한 쿼리가 수업 중 배운 쿼리라고 생각해 해당 쿼리 처음부터 재작성하여 진행

Retain User를 New + Current + Ressurected + Dormat User로 나누는 쿼리 작성

• New, Current, Ressurected, Dormat User에 대한 정의가 먼저
  • New : 우리 서비스에 처음 방문한 고객
  • Currnet : 특정 주차에 활동했고, 직전 3주 동안 1번 이상 활동한 유저를 Current로 정의
  • Ressurcted (복귀 유저) : 첫 이벤트 발생 이후 발생한 이벤트 사이 간격이 5주 이상일 때 처음 다시 돌아온 주를 복귀 주로 설정
  • Dormat User : 특정 주에 활동했으나 +4주 후에도 전혀 활동이 없는 경우 휴면 유저로 정의
    • 시간이 지날수록 감소하는 생존 곡선 형태로 분석 예정

1. NEW : 우리 서비스에 새롭게 들어온 고객

• 쿼리 작성 순서
  • 전 기간 로그에서 같은 유저, 같은 날, 같은 이벤트 중복을 제거하기 위한 기본 base 테이블 생성
  • 유저별 최초 방문일과 그 날짜가 속한 월요일을 전 기간 기준으로 정립
  • 활동을 유저X주차 단위로 중복을 제거하여 1번만 집계
  • 코호트 주X경과주 별로 신규 유저수 집계
  • 최종 SELECT 문에서 리텐션율을 계산하고 정렬

-- 1) 전 기간 기준 같은 유저-같은날-같은 이벤트 중복 제거
WITH base AS (
  SELECT DISTINCT
    user_id,
    user_pseudo_id,
    event_name,
    DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
    DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime
  FROM advanced.app_logs
),

-- 2) 유저별 서비스 최초 방문일과 그 날짜가 속한 주 정립
user_first_all AS (
  SELECT
    user_pseudo_id,
    MIN(event_date) AS first_event_date_all,
    DATE_TRUNC(MIN(event_date), WEEK(MONDAY)) AS first_week
  FROM base
  GROUP BY user_pseudo_id
),

-- 3) 유저X주차 단위 활동을 1번만 남기도록 설계 (주차 중복 제거)
user_week AS (
  SELECT DISTINCT
    b.user_pseudo_id,
    DATE_TRUNC(b.event_date, WEEK(MONDAY)) AS event_week
  FROM base b
),

-- 4) 유저의 각 방문 주에 대해 코호트 주로부터 경과주를 계산
first_week_and_diff AS (
  SELECT
    uw.user_pseudo_id,
    ufa.first_week,
    uw.event_week,
    DATE_DIFF(uw.event_week, ufa.first_week, WEEK) AS diff_of_week
  FROM user_week uw
  JOIN user_first_all ufa USING (user_pseudo_id)
  WHERE DATE_DIFF(uw.event_week, ufa.first_week, WEEK) >= 0  -- 코호트 주 이후만
),

-- 5) 코호트 주 x 경과 주별 신규 유저 수 (해당 diff에 활동한 신규 유저 수)
cohort_counts AS (
  SELECT
    first_week,
    diff_of_week,
    COUNT(DISTINCT user_pseudo_id) AS new_user_cnt
  FROM first_week_and_diff
  GROUP BY first_week, diff_of_week
)

-- 6) 최종: 코호트 크기(= diff_of_week=0) 대비 리텐션
SELECT
  first_week,                 -- 코호트 주(월요일 날짜)
  diff_of_week,               -- 경과 주(0,1,2,…)
  new_user_cnt,               -- 해당 코호트 중 해당 경과 주에 활동한 신규 유저 수
  FIRST_VALUE(new_user_cnt) OVER (
    PARTITION BY first_week
    ORDER BY diff_of_week
  ) AS cohort_user_cnt,       -- 코호트 크기(= diff_of_week=0)
  ROUND(
    SAFE_DIVIDE(
      new_user_cnt,
      FIRST_VALUE(new_user_cnt) OVER (PARTITION BY first_week ORDER BY diff_of_week)
    ),
    3
  ) AS new_user_retention_rate
FROM cohort_counts
ORDER BY first_week, diff_of_week;

1. Currnet : 특정 주차에 활동했고, 직전 3주 동안 1번 이상 활동한 유저

• 쿼리 작성 순서
  • base문 동일
  • 전 기간의 최솟값 산출
  • 최솟값 기준 0부터 시작하는 주차 번호 부여
  • 각 user, week_no에 대해 그 주에 활동했고, 직전 3주 중 최소 1주 이상 활동했으면 Currnet user로 정의
  • 유저가 처음 Current가 된 주를 계산
  • 코호트 주로부터 경과 주까지 계산
  • 코호트 주X경과주 별 Current 유저수 집계
  • 최종적으로 활동 유저에 대한 리텐션율 계산