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

1. Weekly Retention

WITH 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_visit_base AS (
  SELECT
    user_pseudo_id,
    MIN(event_week) OVER (PARTITION BY user_pseudo_id) AS first_visit_week,
    event_week AS visit_week
  FROM base
), user_visit_weekdiff AS (
  SELECT
    first_visit_week, 
    DATE_DIFF(visit_week, first_visit_week, week) AS week_diff,
    COUNT(DISTINCT user_pseudo_id) AS visit_users
  FROM user_visit_base
  GROUP BY ALL
)


SELECT
  first_visit_week, 
  week_diff,
  visit_users,
  SAFE_DIVIDE(visit_users, FIRST_VALUE(visit_users) OVER (PARTITION BY first_visit_week ORDER BY week_diff ASC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS retention
FROM user_visit_weekdiff

2. Retain User → New User, Current User, Resurrected User, Dormant User

-- 한 달(30일)을 기준으로 구분하였다.
-- 신규 유저: 최근 한 달 이내 새로 방문
-- 기존 유저: 최근 한 달 이내 재방문, 그 이전 한달에도 방문
-- 복귀 유저: 최근 한 달 이내 재방문, 그 이전 한달에는 방문 X
-- 휴먼 유저: 최근 한 달 이내 재방문 X

WITH base AS (
    SELECT
        DISTINCT
            DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
            user_pseudo_id
    FROM advanced.app_logs
), user_first_last AS (
    SELECT
        user_pseudo_id,
        MIN(event_date) AS first_date,
        MAX(event_date) AS last_date
    FROM base
    GROUP BY user_pseudo_id
), last_event_date AS (
    SELECT 
        MAX(event_date) AS current_date
    FROM base
), 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 base
), user_types AS (
    SELECT
        u.user_pseudo_id,
        u.first_date,
        u.last_date,
        led.current_date,
        MAX(us.dormant_history) AS dormant_history,
        CASE 
            WHEN DATE_DIFF(led.current_date, u.last_date, DAY) > 30 THEN 'dormant_user'
            WHEN DATE_DIFF(u.last_date, u.first_date, DAY) <= 30 THEN 'new_user'
            WHEN MAX(us.dormant_history) = 1 THEN 'resurrected_user'
            ELSE 'current_user'
        END AS user_type
    FROM user_first_last AS u
    CROSS JOIN last_event_date AS led
    LEFT JOIN user_activity AS us ON u.user_pseudo_id = us.user_pseudo_id
    GROUP BY u.user_pseudo_id, u.first_date, u.last_date, led.current_date
), first_week_and_diff AS (
    SELECT
        ut.user_type,
        fw.user_pseudo_id,
        fw.event_date,
        DATE_DIFF(DATE_TRUNC(fw.event_date, WEEK(MONDAY)), DATE_TRUNC(ut.first_date, WEEK(MONDAY)), WEEK) AS diff_of_week
    FROM base AS fw
    JOIN user_types AS ut ON fw.user_pseudo_id = ut.user_pseudo_id
), user_cnt_by_type_and_week AS (
    SELECT
        user_type,
        diff_of_week,
        COUNT(DISTINCT user_pseudo_id) AS user_cnt
    FROM first_week_and_diff
    GROUP BY user_type, diff_of_week
), retention_base AS (
    SELECT
        user_type,
        diff_of_week,
        user_cnt,
        FIRST_VALUE(user_cnt) OVER (PARTITION BY user_type ORDER BY diff_of_week) AS first_user_cnt
    FROM user_cnt_by_type_and_week
)

SELECT
    user_type,
    diff_of_week,
    ROUND(SAFE_DIVIDE(user_cnt, first_user_cnt), 2) AS retention_rate
FROM retention_base
ORDER BY user_type, diff_of_week

3. retention이 높은 그룹?

• current user: 최고 0.41 (4~5주차)

• new user: 최고 0.11 (1,3주차)

• resurrected user: 최고 0.14 (9~12주차)

• dormant user: 최고 0.09 (3주차)

• 리텐션이 그나마 높은 그룹: current user

• current user, resurrected user 리텐션 분포의 경우 상승세 → 하강세 경향성을 보여 피크를 찍을 때 즈음 어떤 이벤트가 있었다고도 추측할 수 있다.

4. click_payment 이벤트를 중점으로 본 Weekly Retention?

WITH base AS (
	SELECT
		DISTINCT
			DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
			DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,
			event_name,
			user_id,
			user_pseudo_id
	FROM advanced.app_logs
	WHERE 1=1
	AND event_name = "click_payment"
), event_week_and_first_week AS (
	SELECT
		DISTINCT
			DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week, 
			user_pseudo_id,
			DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id ORDER BY event_date), WEEK(MONDAY)) AS first_week
	FROM base
), retention_base AS (
	SELECT
		*, 
		FIRST_VALUE(user_cnt) OVER(ORDER BY day_of_week) AS total_user
	FROM (
		SELECT
			DATE_DIFF(event_week, first_week, WEEK) AS day_of_week,
			COUNT(DISTINCT user_pseudo_id) AS user_cnt
		FROM event_week_and_first_week
		GROUP BY day_of_week
	)
)

SELECT
	day_of_week,
	ROUND(SAFE_DIVIDE(user_cnt, total_user), 2) AS retention
FROM retention_base
ORDER BY day_of_week