[빠짝스터디 3주차 과제]리텐션 연습문제

리텐션 연습문제1. weekly retention을 구하는 쿼리를 바닥부터 스스로 작성해보기

WITH event_log_base AS (
	SELECT
		DISTINCT
			DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,
			DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) 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,
			user_id,
			user_pseudo_id,
			event_name,
			platform,
			event_params
	FROM advanced.app_logs
),

-- 사용자별 첫 방문 주차 계산
user_first_visit_base AS (
  SELECT
    user_pseudo_id,
    MIN(event_week) OVER (PARTITION BY user_pseudo_id) AS first_visit_week,
    event_week AS current_week
  FROM event_log_base
),

-- 사용자별 첫 방문 주차와 현재 주차 차이 계산
user_weekly_diff AS (
  SELECT
    first_visit_week, 
    DATE_DIFF(current_week, first_visit_week, WEEK) AS week_diff,
    COUNT(DISTINCT user_pseudo_id) AS weekly_active_users
  FROM user_first_visit_base
  GROUP BY first_visit_week, week_diff
)

-- 유지율 계산
SELECT
  first_visit_week, 
  week_diff,
  weekly_active_users,
  SAFE_DIVIDE(weekly_active_users, FIRST_VALUE(weekly_active_users) OVER (PARTITION BY first_visit_week ORDER BY week_diff ASC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS retention_rate
FROM user_weekly_diff;

리텐션 연습문제2. Retain user를 New + Current + Resurrected + Dormant user로 나누는 쿼리 작성

군집별 분류기준
1. 신규 사용자 (New User)는 시스템에 처음으로 등록되거나 서비스를 처음으로 사용한 이후 30일 이내에 활동한 사용자. 첫 방문 날짜(first_visit_date)와 마지막 방문 날짜(last_visit_date) 간의 차이가 30일 이하일 경우 ‘신규 사용자’로 분류

2. 현재 사용자 (Current User)는 서비스에 처음 등록한 후 30일이 지난 사용자 중, 최근 30일 이내에도 서비스에 접속한 기록이 있는 사용자. 첫 방문 날짜와 마지막 방문 날짜의 차이가 30일을 초과하고, 가장 최근 이벤트 날짜(latest_event_date)로부터 30일 이내에 활동한 경우 ‘현재 사용자’로 분류

3. 휴면 사용자 (Dormant User)는 한동안 서비스를 이용하지 않아서, 마지막 방문 이후 현재까지 30일 이상 비활동 상태에 있는 사용자. 사용자의 마지막 방문 날짜(last_visit_date)가 가장 최근 이벤트 날짜로부터 30일 이상 경과했을 경우 ‘휴면 사용자’로 분류

4. 복귀 사용자 (Resurrected User)는 일정 기간(30일 이상) 동안 비활동 상태였으나, 다시 서비스를 사용하기 시작한 사용자. 사용자 활동 기록에서 비활성화 기간이 30일 이상 지속되었다가 다시 활동이 발생한 경우(이전 방문 이후 30일 동안 활동이 없다가 다시 방문 시), 해당 사용자를 ‘복귀 사용자’로 분류

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
),

-- 앱 로그의 마지막 이벤트 날짜 계산
current_event_date AS (
    SELECT 
        MAX(event_date) AS latest_event_date
    FROM event_data
),

-- 사용자의 이벤트 활동을 기준으로 휴면 여부 계산
user_activity_log 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 inactive_period
    FROM event_data
),

-- 사용자 유형 분류: 신규, 현재, 휴면, 복귀 사용자
user_category AS (
    SELECT
        u.user_pseudo_id,
        u.first_visit_date,
        u.last_visit_date,
        ced.latest_event_date,
        MAX(ual.inactive_period) AS inactive_period,
        CASE 
            WHEN DATE_DIFF(ced.latest_event_date, u.last_visit_date, DAY) > 30 THEN 'dormant_user'
            WHEN DATE_DIFF(u.last_visit_date, u.first_visit_date, DAY) <= 30 THEN 'new_user'
            WHEN MAX(ual.inactive_period) = 1 THEN 'resurrected_user'
            ELSE 'current_user'
        END AS user_type
    FROM user_first_last_visit AS u
    CROSS JOIN current_event_date AS ced
    LEFT JOIN user_activity_log AS ual ON u.user_pseudo_id = ual.user_pseudo_id
    GROUP BY u.user_pseudo_id, u.first_visit_date, u.last_visit_date, ced.latest_event_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. 주어진 데이터에서 어떤 사람들이 리텐션이 그나마 높은지 찾아보기

-- 1) 특정 월에 방문한 사용자들의 월별 리텐션 비교
-- 첫 접속일을 기준으로 리텐션이 높은 코호트를 분석하여, 리텐션 높은 사용자들이 특정 시기와 관련이 있는지 확인

WITH monthly_event_base AS (
  SELECT 
    user_pseudo_id,
    DATE_TRUNC(MIN(event_date) OVER (PARTITION BY user_pseudo_id), MONTH) AS first_event_month,   
    DATE_TRUNC(event_date, MONTH) AS current_month, 
    DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime
  FROM advanced.app_logs
),

-- 첫 방문 월 대비 차이 계산
monthly_diff_data AS (
  SELECT 
    user_pseudo_id, 
    first_event_month, 
    current_month,
    DATE_DIFF(current_month, first_event_month, MONTH) AS month_diff
  FROM monthly_event_base 
  ORDER BY month_diff
),

-- 월별 사용자 수 계산
monthly_user_count AS (
  SELECT 
    first_event_month, 
    month_diff, 
    COUNT(DISTINCT user_pseudo_id) AS user_count
  FROM monthly_diff_data
  GROUP BY first_event_month, month_diff
  ORDER BY month_diff
)

-- 리텐션 비율 계산
SELECT *, 
  ROUND(SAFE_DIVIDE(user_count, initial_user_count), 2) AS retention_rate
FROM (
  SELECT *, 
    FIRST_VALUE(user_count) OVER (PARTITION BY first_event_month ORDER BY month_diff) AS initial_user_count
  FROM monthly_user_count 
) 
ORDER BY first_event_month, month_diff;

-- 2) 특정 이벤트가 높은 리텐션과 관련이 있는가
-- 사용자별 주요 이벤트의 단계와 횟수를 파악하여 리텐션 높은 사용자들이 특정 행동 패턴을 보이는지 확인

WITH event_base AS (
  SELECT 
    user_pseudo_id,
    DATE_TRUNC(MIN(event_date) OVER (PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week,   
    DATE_TRUNC(MIN(event_date) OVER (PARTITION BY user_pseudo_id), MONTH) AS first_month,   
    event_name, 
    param
  FROM advanced.app_logs 
  CROSS JOIN UNNEST(event_params) AS param
),

-- 이벤트별 단계 및 세션 정보 추출
event_stage AS (
  SELECT 
    user_pseudo_id, 
    first_week, 
    first_month,
    event_name, 
    MAX(IF(param.key = "firebase_screen", param.value.string_value, NULL)) AS firebase_screen, 
    MAX(IF(param.key = "food_id", param.value.string_value, NULL)) AS food_id,
    MAX(IF(param.key = "session_id", param.value.string_value, NULL)) AS session_id
  FROM event_base
  GROUP BY user_pseudo_id, first_week, first_month, event_name
  ORDER BY user_pseudo_id 
),

-- 퍼널 단계별 사용자 수 계산
funnel_stage_count AS (
  SELECT 
    first_month, 
    CASE WHEN event_name = "screen_view" THEN 1
         WHEN event_name = "click_login" THEN 2
         WHEN event_name = "click_search" THEN 3
         WHEN event_name = "request_search" THEN 4
         WHEN event_name = "click_restaurant" THEN 5
         WHEN event_name = "click_food_category" THEN 6
         WHEN event_name = "view_recommend_extra_food" THEN 7
         WHEN event_name = "click_cart" THEN 8
         WHEN event_name = "click_payment" THEN 9
         WHEN event_name = "click_banner" THEN 10
    END AS step_number, 
    event_name,
    COUNT(DISTINCT user_pseudo_id) AS count
  FROM event_stage
  GROUP BY first_month, event_name
  HAVING step_number IS NOT NULL
  ORDER BY first_month, step_number 
)

-- 단계별 퍼널 비율 계산
SELECT *, 
  ROUND(SAFE_DIVIDE(count, previous_step_count), 2) AS funnel_ratio
FROM (
  SELECT *, 
    LAG(count) OVER (PARTITION BY first_month ORDER BY step_number) AS previous_step_count 
  FROM funnel_stage_count  
) 
ORDER BY first_month, step_number;

첫째, 10월과 11월에 첫 방문한 사용자가 다른 월에 비해 리텐션이 높게 나타났으며, click_food_category 이벤트를 거친 사용자의 리텐션이 높았다.

리텐션 연습문제4. core event를 "click_payment"라고 설정하고 weekly retention 구하기

 WITH user_payment_data AS (
  SELECT 
    user_pseudo_id,
    event_date,
    DATE_TRUNC(MIN(event_date) OVER (PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_event_week,   
    DATE_TRUNC(event_date, WEEK(MONDAY)) AS current_event_week, 
    DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime
  FROM advanced.app_logs
  WHERE event_name = 'click_payment'  
),

-- 첫 이벤트 주차 대비 현재 주차 차이 계산
weekly_difference_data AS (
  SELECT 
    user_pseudo_id, 
    first_event_week, 
    current_event_week,
    DATE_DIFF(current_event_week, first_event_week, WEEK) AS weeks_since_first_event
  FROM user_payment_data 
  ORDER BY weeks_since_first_event
), 

-- 주차별 사용자 수 집계
weekly_user_counts AS (
  SELECT 
    first_event_week, 
    weeks_since_first_event, 
    COUNT(DISTINCT user_pseudo_id) AS user_count
  FROM weekly_difference_data
  GROUP BY first_event_week, weeks_since_first_event
  ORDER BY weeks_since_first_event
) 

-- 주차별 리텐션 비율 계산
SELECT 
  *, 
  ROUND(SAFE_DIVIDE(user_count, initial_user_count), 2) AS retention_rate
FROM (
  SELECT 
    *, 
    FIRST_VALUE(user_count) OVER (PARTITION BY first_event_week ORDER BY weeks_since_first_event) AS initial_user_count
  FROM weekly_user_counts 
) 
ORDER BY first_event_week, weeks_since_first_event;

인프런 커뮤니티 질문&답변

리텐션 연습문제1. weekly retention을 구하는 쿼리를 바닥부터 스스로 작성해보기

리텐션 연습문제2. Retain user를 New + Current + Resurrected + Dormant user로 나누는 쿼리 작성

리텐션 연습문제3. 주어진 데이터에서 어떤 사람들이 리텐션이 그나마 높은지 찾아보기

리텐션 연습문제4. core event를 "click_payment"라고 설정하고 weekly retention 구하기