1. Weekly Retention을 구하는 쿼리 스스로 작성해보기

retain 기준 : 사용자가 최초로 ‘참여’한 Week 0 이후, 각 Week N 별로 한번 더 ‘참여’ 했는지 ?

-- weekly retention 구하기

WITH base AS( -- 1. event_date 구하기
  SELECT
    DISTINCT -- event_date 기준 중복 제거
    user_pseudo_id,
    DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul")) AS event_date
  FROM `advanced.app_logs`
), first_week_and_diff AS ( -- 2. diff_of_week 구하기
  SELECT 
    *,
    DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week
  FROM(
    SELECT
      DISTINCT -- event_week 기준 중복 제거
      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 ( -- 3. diff 별 user의 수 구하기
  SELECT
    diff_of_week,
    COUNT(DISTINCT user_pseudo_id) AS user_cnt
  FROM first_week_and_diff
  GROUP BY ALL
)
-- 4. weekly retention rate 구하기
SELECT
  diff_of_week,
  user_cnt,
  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
)

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

• 데이터 : 2022-08-01 ~ 2023-01-20 (약 6개월)

2-1. 고객들은 서비스를 한 달에 몇 번 이용할까?

• 2022년 8월 한 달간 사용 빈도 확인
• 쿼리

• 한 달에 3번 사용하는 유저가 20%, 7번 & 1번 사용하는 유저가 각각 15%

WITH base AS ( -- 202208 데이터만 추출
  SELECT
    user_pseudo_id,
    DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp),"Asia/Seoul")) AS event_date
  FROM advanced.app_logs
  WHERE event_date BETWEEN '2022-08-01' AND '2022-08-31'
), bin_data AS( -- 사용빈도 별 유저수 count
  SELECT
    user_cnt,
    COUNT(user_pseudo_id) AS bin_cnt
  FROM(
    SELECT
      user_pseudo_id,
      COUNT(*) AS user_cnt
    FROM base
    GROUP BY 1
  )
  GROUP BY 1
)
**-- 결론 : 한달에 평균 6.22번 사용**
SELECT
  ROUND(SUM(user_cnt*bin_cnt)/(SELECT COUNT(DISTINCT user_pseudo_id) FROM base),2) AS avg_using_cnt -- 한달 간 인당 평균 사용 횟수
FROM bin_data

• 인당 한 달에 평균 6.22번 사용 → 30일에 6번 → 평균 5일에 1번 사용
• 일주일에 최소 1번은 사용해야 한다고 가정하고 기준 설정 !

Active User 기준

<aside> <img src="/icons/bookmark_gray.svg" alt="/icons/bookmark_gray.svg" width="40px" />

1. 첫 사용이 활동 주차인 경우 → New
2. 1주 전에도 사용한 경우 → Current
3. 2주 이상 활동이 없고, 최근 7일 이내 활동한 경우 → Resurrected
4. 2주 간 활동 기록 없음→ Dormant </aside>

2-2. DAU, WAU 비교

• 쿼리

<aside> <img src="/icons/thought-alert_gray.svg" alt="/icons/thought-alert_gray.svg" width="40px" />

7일간 DAU의 합과 WAU가 거의 차이가 없는 것으로 보아 기존 유저들이 계속 재방문한다기보다 매일 새로운 유저들이 계속 유입되는 현황으로 보임

</aside>

2-3. 유저 구별하기