-- 기간: 2022-08-01 ~ 2022-08-18
-- event_date | event_timestamp | event_name | user_pseudo_id
-- event_date 가 KST 기준인 것은 확인한 것으로 가정
-- 1) 특정 이벤트를 시도한 유저들의 리텐션이 높을 것이다 => event_name 별로 리텐션 체크 - 여러개를 동시에 하는 유저들도 있을 텐데 이 부분은 무시
-- 2) user_pseudo_id 기준 event_name 별 Day retention 체크
--    이벤트 첫 수행일: event_name, user_pseudo_id, event_date => first_date
--    앱 접속일: user_pseudo_id, event_date => visit_date
--    JOIN user_pseudo_id 연결
--    이벤트 첫 수행 후 접속일 유저수  
WITH base AS (
SELECT
  DISTINCT
    event_date,
    event_name,
    user_pseudo_id
FROM advanced.app_logs
WHERE event_date BETWEEN "2022-08-01" AND "2022-08-18" 
      -- AND event_name = "click_restaurant_nearby"
), fd AS (  -- 유저, 이벤트별 첫 수행일자
SELECT
 event_name, 
 user_pseudo_id,
 MIN(event_date) AS first_date
FROM base
GROUP BY 
 event_name,
 user_pseudo_id
), visit_date AS ( --유저 일접속일자
 SELECT 
  user_pseudo_id,
  event_date
 FROM base
 GROUP BY 1,2
), diffday AS ( --JOIN 유저 이벤트별 수행 + 접속일
SELECT
 fd.event_name,
 fd.user_pseudo_id,
 fd.first_date,
 vd.event_date,
 DATE_DIFF(vd.event_date,fd.first_date,DAY) AS diff_of_day
FROM fd
LEFT JOIN visit_date AS vd
    ON fd.user_pseudo_id = vd.user_pseudo_id
    AND vd.event_date >= fd.first_date
), cnt AS (
SELECT -- 이벤트 첫 수행별 리텐션
  event_name,
  diff_of_day,
  COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM diffday
GROUP BY
  event_name,
  diff_of_day
ORDER BY
  event_name,
  diff_of_day
)
SELECT
 *,
 ROUND(SAFE_DIVIDE(user_cnt, f_cnt),2) AS retention
FROM (
SELECT
 *,
 FIRST_VALUE(user_cnt) OVER (PARTITION BY event_name ORDER BY diff_of_day) AS f_cnt
FROM cnt
)

결과 출력

주어진데이터 높은 리텐션 이벤트 찾기.csv

데이터 해석

가정: 특정 이벤트를 시도한 유저들의 리텐션이 높을 것이다 => event_name 별로 리텐션 체크 - 여러개를 동시에 하는 유저들도 있을 텐데 이 부분은 무시

이벤트 별 클릭 유저 모수는 천차만별이지만 클릭 Day Retention 을 보면 모든 이벤트가 D1 ~ D17 까지 거의 0% 에 수렴

• 그나마 상대적으로 리텐션이 집계되는 것이 click_payment > click_recommend_food > click_restaurant_nearby 순