1. Weekly retention을 구하는 쿼리를 구하기.

	# Weekly Retenttion 쿼리 구하기.
	
	WITH base AS ( 
	    SELECT
	    DISTINCT
	      user_id,
	      user_pseudo_id,
	      event_name,
	      DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul') AS event_datetime,
	      DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul')) AS event_date,
	    FROM advanced.app_logs)
	    
	# 리텐션을 구하기 위한 유저의 event_week, first_week, diff_week 구하기.
	, first_week_and_diff AS (
	      SELECT
	      *,
	      DATE_DIFF(event_week,first_week,week) AS diff_week
	      FROM
	          ( SELECT 
	              DISTINCT
	              user_pseudo_id,
	              DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week,
	              DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id),WEEK(MONDAY)) AS first_week 
	            FROM base ) )
	# 유저별 event_week, first_week, diff_week 수.
	, user_counts AS ( 
	    SELECT
	      diff_week,
	      COUNT(DISTINCT user_pseudo_id) AS user_cnt
	    FROM first_week_and_diff
	    GROUP BY ALL ) 
	
	# Weekly retention의 수와 비율.
	SELECT
	  diff_week,
	  user_cnt,
	  ROUND(SAFE_DIVIDE(user_cnt,first_week_user_cnt), 3) AS retention_rate
	FROM (
	    SELECT
	    diff_week,
	    user_cnt,
	    FIRST_VALUE(user_cnt) OVER(ORDER BY diff_week ASC) AS first_week_user_cnt
	    FROM user_counts)
	ORDER BY 1 

2. Retain User를 User + Current + Resurrected + Document User로 나누기.

1) 유저 그룹 분류

• NEW : 제품 사용의 첫 번째 주기에 속한 유저
• Current : 직전 주기 및 현재 주기 모두 제품을 사용하는 유저 → 배달 서비스 앱의 특성상 넓게 봤을때 2주안에 사용하는 유저를 현재 유저라고 설정.
• 참고 : https://www.localtoday.co.kr/news/articleView.html?idxno=229048 → 1주일에 배달 앱 서비스 이용자는 약 1.1~1.2회로 나타남. → 한달 간 2~3회의 비중도 높았음. 한달로 치면 2주에 1.5번 정도 시킨다는 것. → 두 가지를 조합해서, 2주안으로 다시 안으로 나타나는 유저를 Current 유저로 설정.
• Resuurected : 이전 주기에 휴면 유저였으나 현재 주기에는 다시 제품을 사용하는 유저 → Amplitude 연구 기준 60~90일 이내로 설정. 여기서는 60일로 설정(= 8주). → 첫 사용 후 2주 초과 ~ 8주 안으로 복귀하는 유저로 설정.
• Document : 현재 주기에 비활성화된 상태인, 휴면 유저. → 활동 주기가 8주를 초과한 유저로 설정.
  • 참고 : https://blog.ab180.co/posts/retention-series-3-2, https://blog.ab180.co/posts/retention-series-2

# 2. Retain User를 User + Current + Resurrected + Document User로 나누기.

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

, weekly_user_active AS (
    SELECT 
      user_pseudo_id,
      DATE_TRUNC(event_date, WEEK) AS event_week,
      MIN(DATE_TRUNC(event_date, WEEK)) OVER (PARTITION BY user_pseudo_id) AS first_active_week,
      LAG(DATE_TRUNC(event_date, WEEK)) OVER (PARTITION BY user_pseudo_id ORDER BY DATE_TRUNC(event_date, WEEK)) AS pre_active_week
      FROM base
)

, user_group AS (
  SELECT
    user_pseudo_id,
    event_week,
    DATE_DIFF(event_week, pre_active_week, WEEK(MONDAY)) AS diff_prior_week, 
    DATE_DIFF(event_week, first_active_week, WEEK(MONDAY)) AS diff_first_week,
    CASE 
      WHEN event_week = first_active_week THEN 'NEW'
      WHEN DATE_DIFF(event_week, pre_active_week, WEEK) = 2 THEN 'Current'
      WHEN DATE_DIFF(event_week, pre_active_week, WEEK) > 2 THEN 'Resurrected'
      ELSE 'Document '
    END AS user_seg
    FROM weekly_user_active
)

SELECT  
  event_week,
  user_seg,
  COUNT(DISTINCT user_pseudo_id) AS user_cnt,
FROM user_group
GROUP BY ALL
ORDER BY 1 ; 

3. 주어진 데이터에서 어떤 사람들이 리텐션이 높은지.

1. New : 2022.10.09일 이후로 지속 리텐션이 낮아지고 있음.
2. Current : 지속적으로 상승하다가, 22.10.23 이후로 400~600명 대로 현상 유지 하고 있음.