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

with base as(
  select
    distinct
      user_id, 
      user_pseudo_id,
      event_name,
      datetime(timestamp_micros(event_timestamp), 'Asia/Seoul') as event_datetime,
      date_trunc(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul'), day) as event_date,
      date_trunc(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul'), week(monday)) as event_week,
      date_trunc(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul'), month) as event_month,
      date_trunc(min(event_date) over(partition by user_pseudo_id), week(monday)) as first_week,    
  from `advanced.app_logs`
), first_week_diff as(
  select
    distinct
      user_pseudo_id,
      DATE_DIFF(event_week, first_week, week) as diff_of_week,
      event_week,
      event_date
  from base
), user_counts as(
  select
    diff_of_week,
    count(distinct user_pseudo_id) as user_count
  from first_week_diff
  group by diff_of_week
)

select
  *,
  round(safe_divide(user_count,first_week_user_count), 2) as retention_rate
from(
  select
    diff_of_week,
    first_value(user_count) over(order by diff_of_week asc) as first_week_user_count,
    user_count
  from user_counts
)

2)RetainUser를New + Current + Resurrected + DormantUser 로 나누는 쿼리를 작성해보세요.

WITH event_data AS (
    SELECT DISTINCT
        DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
        user_pseudo_id
    FROM advanced.app_logs
),

-- 사용자별 첫 방문 및 마지막 방문 날짜 계산
user_first_last_visit AS (
    SELECT
        user_pseudo_id,
        MIN(event_date) AS first_visit_date,
        MAX(event_date) AS last_visit_date
    FROM event_data
    GROUP BY user_pseudo_id
),

-- 전체 이벤트 데이터에서 가장 최근의 이벤트 날짜 계산
latest_event_date AS (
    SELECT MAX(event_date) AS current_date
    FROM event_data
),

-- 비활동 기간 확인 및 휴면 여부 판단
user_activity AS (
    SELECT
        user_pseudo_id,
        event_date,
        LEAD(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date) AS next_event_date,
        CASE
            WHEN DATE_DIFF(LEAD(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date), event_date, DAY) > 30 THEN 1
            ELSE 0
        END AS dormant_history
    FROM event_data
),

-- 사용자 유형 분류: 신규 사용자, 현재 사용자, 복귀 사용자, 휴면 사용자
user_category AS (
    SELECT
        u.user_pseudo_id,
        u.first_visit_date,
        u.last_visit_date,
        led.current_date,
        MAX(ua.dormant_history) AS dormant_history,
        CASE 
            WHEN DATE_DIFF(led.current_date, u.last_visit_date, DAY) > 30 THEN 'Resurrected User'  -- 마지막 방문 이후 30일 이상 비활동
            WHEN DATE_DIFF(u.last_visit_date, u.first_visit_date, DAY) <= 30 THEN 'New User'       -- 첫 방문 후 30일 이내
            WHEN MAX(ua.dormant_history) = 1 THEN 'Dormant User'                                   -- 30일 이상 비활동 이후 다시 활동
            ELSE 'Current User'                                                                   -- 첫 방문 후 30일 이상 지났으며 최근 30일 내 접속
        END AS user_type
    FROM user_first_last_visit AS u
    CROSS JOIN latest_event_date AS led
    LEFT JOIN user_activity AS ua ON u.user_pseudo_id = ua.user_pseudo_id
    GROUP BY u.user_pseudo_id, u.first_visit_date, u.last_visit_date, led.current_date
),

-- 사용자 유형별 첫 방문 주차와 현재 주차 간 차이 계산
user_weekly_retention AS (
    SELECT
        uc.user_type,
        ed.user_pseudo_id,
        ed.event_date,
        DATE_DIFF(DATE_TRUNC(ed.event_date, WEEK(MONDAY)), DATE_TRUNC(uc.first_visit_date, WEEK(MONDAY)), WEEK) AS week_difference
    FROM event_data AS ed
    JOIN user_category AS uc ON ed.user_pseudo_id = uc.user_pseudo_id
),

-- 사용자 유형과 주차별 활성 사용자 수 집계
user_count_by_type_and_week AS (
    SELECT
        user_type,
        week_difference,
        COUNT(DISTINCT user_pseudo_id) AS user_count
    FROM user_weekly_retention
    GROUP BY user_type, week_difference
),

-- 유지율 계산을 위한 초기 사용자 수와 현재 주차별 유지율 계산
retention_calculation AS (
    SELECT
        user_type,
        week_difference,
        user_count,
        FIRST_VALUE(user_count) OVER (PARTITION BY user_type ORDER BY week_difference) AS initial_user_count
    FROM user_count_by_type_and_week
)

SELECT
    user_type,
    week_difference,
    ROUND(SAFE_DIVIDE(user_count, initial_user_count), 2) AS retention_rate
FROM retention_calculation
ORDER BY user_type, week_difference;

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

Current User의 초기 주차(week_difference = 0)에서 리텐션이 1.0(즉, 100%)로 시작한 후, 주차가 지남에 따라 급격히 감소함
첫 주차 이후 리텐션이 19%로 떨어지고, 이후에는 점진적으로 감소하며 10주차에는 9%, 13주차 이후에는 5% 미만으로 떨어짐
초기 2~3주 동안의 리텐션 감소가 매우
- Current User가 새로운 콘텐츠나 유지 요소가 부족해 쉽게 이탈할 수 있을듯

user_type별 week_difference

Dormant User >Resurrected User>Current User>New User
비율은 dormant의 값이 커보이나 리텐션은 current가 길게 유지됨을 보인다.

4)CoreEvent를 “click_payment”라고 설정하고WeeklyRetention을 구해주세요

정답이 없는 부분이라, 생각해보고 게시판에 올려주세요. 의견을 공유드릴게요!

with base as(
  select
    distinct
      user_id, 
      user_pseudo_id,
      event_name,
      datetime(timestamp_micros(event_timestamp), 'Asia/Seoul') as event_datetime,
      date_trunc(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul'), day) as event_date,
      date_trunc(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul'), week(monday)) as event_week,
      date_trunc(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul'), month) as event_month,
      date_trunc(min(event_date) over(partition by user_pseudo_id), week(monday)) as first_week,    
  from `advanced.app_logs`
  where event_name = 'click_payment'
), first_week_diff as(
  select
    distinct
      user_pseudo_id,
      DATE_DIFF(event_week, first_week, week) as diff_of_week,
      event_week,
      event_date
  from base
), user_counts as(
  select
    diff_of_week,
    count(distinct user_pseudo_id) as user_count
  from first_week_diff
  group by diff_of_week
)

select
  *,
  round(safe_divide(user_count,first_week_user_count), 2) as retention_rate
from(
  select
    diff_of_week,
    first_value(user_count) over(order by diff_of_week asc) as first_week_user_count,
    user_count
  from user_counts
  group by all
)