-- 기간: 2022-08-01 ~ 2022-08-31
-- New : event_date 가 첫 접속일과 같은 유저
-- Current : 첫 접속일도 있고, 다음 접속일, 그다음 접속일이 14일 초과하지 않는 유저
-- Dormant User : 접속일 이후 그다음 접속일에 관해 14일 동안 접속없다면 15일째 일자에 user_type = "dolmant" 가상행 추가 후 UNION ALL
-- Resurrected : 마지막 event_date 기준으로 그 이전 event_date diff 가 14일 초과하는 유저
-- event_date | event_timestamp | user_pseudo_id
-- 1) event_date | event_timestamp KST 변환
-- 2) user_pseudo_id 기준 첫 접속일, 마지막 접속일, 마지막 접속일, 마지막 직전 접속일 확인
-- 3) CASE WHEN으로 New|Current|Resurrected|Dormant 구분
WITH base AS (
SELECT
 event_date,
 DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS kst,
 user_pseudo_id
FROM advanced.app_logs
WHERE --user_pseudo_id = "2678139396.8763162692"
      --AND
      event_date BETWEEN "2022-08-01" AND "2022-08-31"
), daily_event AS (
 SELECT DISTINCT
  event_date,
  user_pseudo_id
 FROM base

)
,f_l_bl_event AS (
SELECT
  event_date,
  user_pseudo_id,
  MIN(event_date) OVER (PARTITION BY user_pseudo_id) AS first_event,
  MAX(event_date) OVER (PARTITION BY user_pseudo_id) AS last_event,
  LAG(event_date, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_date ASC) AS before_last_event,
  LEAD(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date ASC) AS next_event_dt
FROM daily_event
), retain_user AS (
SELECT 
  event_date,
  user_pseudo_id,
  first_event,
  last_event,
  before_last_event,
  next_event_dt,
  CASE WHEN first_event = event_date THEN "new"
       WHEN DATETIME_DIFF(event_date, LAG(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date ASC), DAY) <= 14 THEN "current"
       WHEN DATETIME_DIFF(event_date, LAG(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date ASC), DAY) > 14 THEN "resurrected"
        ELSE NULL END AS user_type
  FROM f_l_bl_event  
), dormant_rows AS (
 SELECT
   DATE_ADD(event_date, INTERVAL 15 DAY) AS event_date,
   user_pseudo_id,
   first_event,
   last_event,
   event_date AS before_last_event,
  'dormant' AS user_type 
 FROM retain_user
 WHERE 
   CASE
     WHEN next_event_dt IS NOT NULL
      THEN DATE_DIFF(next_event_dt, event_date, DAY) > 14
      ELSE DATE_ADD(event_date, INTERVAL 15 DAY) <= DATE '2022-08-31'
    END
), all_rows AS (
 SELECT
  event_date,
  user_pseudo_id,
  first_event,
  last_event,
  before_last_event,
  user_type
 FROM retain_user
 UNION ALL
 SELECT
  event_date,
  user_pseudo_id,
  first_event,
  last_event,
  before_last_event,
  user_type
 FROM dormant_rows

)
SELECT
--*
 event_date,
-- user_pseudo_id,
 COUNTIF(user_type = "new") AS new_cnt,
 COUNTIF(user_type = "dormant") AS dormant_cnt,
 COUNTIF(user_type = "resurrected") AS resurrected_cnt,
 COUNTIF(user_type = "current") AS current_cnt   
FROM all_rows
-- WHERE event_date = "2022-08-16"
 GROUP BY event_date--, user_pseudo_id
ORDER BY event_date ASC

결과 출력

데이터 해석

8월 전반적으로 신규유저(New)는 매일 들어오고, 8월 하순으로 갈수록 우상향하는 추세 다만, 1일만 접속하고 들어오지 않는 유저가 95% 이상으로 리텐션이 무너져 있는 상황으로 복귀유저(resurrected) 상당히 낮음

• dormant 유저 정의: 접속일 이후 14일 동안 접속하지 않는 유저로 8/1 - 8/16, 8/2-8/17 이런식으로 대조해보면 잔존유저가 거의 없음
• current 유저 정의: 접속일 이후 14일 이내에 다시 접속한 유저로 New 유저 유입대비 너무 극소수
• resurrected 유저 정의: dormant 유저 기반에서 재접속한 유저로 굉장히 소수인 상황

→ 일평균 95%가 넘는 이탈율을 개선하는 작업이 최우선

Weekly 기준 추가

# Weekly로 변환해서 구분
WITH base AS (
 SELECT DISTINCT
  DATE_TRUNC(event_date, WEEK) AS event_week,
  user_pseudo_id
 FROM advanced.app_logs
 WHERE --user_pseudo_id = "2678139396.8763162692"
      --AND
      event_date BETWEEN "2022-08-01" AND "2022-11-03"

),f_l_bl_event AS (
SELECT
  event_week,
  user_pseudo_id,
  MIN(event_week) OVER (PARTITION BY user_pseudo_id) AS first_week,
  MAX(event_week) OVER (PARTITION BY user_pseudo_id) AS last_week,
  LAG(event_week, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_week ASC) AS before_last_week,
  LEAD(event_week) OVER (PARTITION BY user_pseudo_id ORDER BY event_week ASC) AS next_week_dt
FROM base
), retain_user AS (
SELECT 
  event_week,
  user_pseudo_id,
  first_week,
  last_week,
  before_last_week,
  next_week_dt,
  CASE WHEN first_week = event_week THEN "new"
       WHEN DATE_DIFF(event_week, LAG(event_week) OVER (PARTITION BY user_pseudo_id ORDER BY event_week ASC), WEEK) <= 2 THEN "current"
       WHEN DATE_DIFF(event_week, LAG(event_week) OVER (PARTITION BY user_pseudo_id ORDER BY event_week ASC), WEEK) > 2 THEN "resurrected"
        ELSE NULL END AS user_type
  FROM f_l_bl_event  
), dormant_rows AS (
 SELECT
   DATE_ADD(event_week, INTERVAL 3 WEEK) AS event_week,
   user_pseudo_id,
   first_week,
   last_week,
   event_week AS before_last_week,
  'dormant' AS user_type 
 FROM retain_user
 WHERE 
   CASE
     WHEN next_week_dt IS NOT NULL
      THEN DATE_DIFF(next_week_dt, event_week, WEEK) > 2
      ELSE DATE_ADD(event_week, INTERVAL 3 WEEK) <= DATE '2022-11-03'
    END
), all_rows AS (
 SELECT
  event_week,
  user_pseudo_id,
  first_week,
  last_week,
  before_last_week,
  user_type
 FROM retain_user
 UNION ALL
 SELECT
  event_week,
  user_pseudo_id,
  first_week,
  last_week,
  before_last_week,
  user_type
 FROM dormant_rows

)
SELECT
--*
 event_week,
-- user_pseudo_id,
 COUNTIF(user_type = "new") AS new_cnt,
 COUNTIF(user_type = "dormant") AS dormant_cnt,
 COUNTIF(user_type = "resurrected") AS resurrected_cnt,
 COUNTIF(user_type = "current") AS current_cnt   
FROM all_rows
-- WHERE event_date = "2022-08-16"
 GROUP BY event_week--, user_pseudo_id
ORDER BY event_week ASC- 기간: 2022-08-01 ~ 2022-08-31

결과 출력