#Weekly Retain_User 확인
WITH base AS (
  SELECT DISTINCT
  DATE_TRUNC(event_date,WEEK(MONDAY)) AS event_week,
  event_name,
  params,
  user_pseudo_id,
  platform,
  FROM advanced.app_logs AS a
  CROSS JOIN UNNEST(event_params) AS params
  WHERE event_date BETWEEN "2022-08-01" AND "2022-11-30"
      -- AND user_pseudo_id = '8453651862.1501092804'
), retain_user AS (
  SELECT 
  *,
  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), WEEK) <= 2 THEN "current"
      WHEN DATE_DIFF(event_week, LAG(event_week) OVER (PARTITION BY user_pseudo_id ORDER BY event_week), WEEK) > 2 THEN "resurrected"
      ELSE NULL END AS user_type
  FROM ( 
  SELECT
  *,
  MIN(event_week) OVER (PARTITION BY user_pseudo_id) AS first_week,
  LEAD(event_week) OVER (PARTITION BY user_pseudo_id ORDER BY event_week) AS next_event_week
  FROM base
  )
), dormant_rows AS (
 SELECT
  DATE_ADD(event_week, INTERVAL 2 week) AS event_week,
  user_pseudo_id,
  first_week,
  platform,
  event_name,
  params,
  'dormant' AS user_type
 FROM retain_user
 WHERE 
  CASE
      WHEN next_event_week IS NOT NULL
      THEN DATE_DIFF(next_event_week, event_week, WEEK) > 2
      ELSE DATE_ADD(event_week, INTERVAL 2 WEEK) <= DATE '2022-11-30'
      END
), all_rows AS (
  SELECT
   event_week,
   user_pseudo_id,
   first_week,
   user_type,
   platform,
   event_name,
   params
  FROM retain_user
  UNION ALL
  SELECT
   event_week,
   user_pseudo_id,
   first_week,
   user_type,
   platform,
   event_name,
   params
  FROM dormant_rows
# Dormant 유저 Last Value(event_mame)
), dor_last_action AS
(
SELECT
 event_name,
 params,
 LAST_VALUE(params) OVER (PARTITION BY user_pseudo_id ORDER BY event_week ASC) AS dor_last_act
FROM all_rows
WHERE user_type = "dormant"
      AND event_name = "screen_view"
)
SELECT
 event_name,
 params,
 COUNT(dor_last_act) AS cnt
FROM dor_last_action
GROUP BY params, event_name
  ORDER BY cnt DESC
#각 유저별 카운트
SELECT
 event_week,
 COUNTIF(user_type= "new") AS new_cnt,
 COUNTIF(user_type= "current") AS current_cnt,
 COUNTIF(user_type= "resurrected") AS resurrected_cnt,
 COUNTIF(user_type= "dormant") AS dormant_cnt,   
 platform
FROM all_rows
GROUP BY event_week, platform
ORDER BY event_week, platform ASC
#current 유저의 누적 유니크값
SELECT
  COUNT(DISTINCT user_pseudo_id) AS current_users
FROM all_rows
WHERE user_type = 'current'
#Weekly click_payment 매출 확인, retain 유저별 구분(Dormant 유저는 제외 - 이탈되는 시점을 체크한 것이기에 Click_payment 할 수 없음)
WITH base AS (
  SELECT
  DATE_TRUNC(event_date,WEEK(MONDAY)) AS event_week,
  event_name,
  user_pseudo_id,
  platform
  FROM advanced.app_logs
  WHERE event_date BETWEEN "2022-08-01" AND "2022-08-10"
      -- AND user_pseudo_id = '8453651862.1501092804'
), cp_count AS (
SELECT
 event_week,
--  event_name,
 COUNT(event_name) AS cp_cnt,
 platform 
FROM base
WHERE event_name = "click_payment"
GROUP BY event_week, platform --, event_name
), retain_user AS (
 SELECT 
 *,
  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), WEEK) <= 2 THEN "current"
      WHEN DATE_DIFF(event_week, LAG(event_week) OVER (PARTITION BY user_pseudo_id ORDER BY event_week), WEEK) > 2 THEN "resurrected"
      ELSE NULL END AS user_type
  FROM ( 
  SELECT
  *,
  MIN(event_week) OVER (PARTITION BY user_pseudo_id) AS first_week,
  LEAD(event_week) OVER (PARTITION BY user_pseudo_id ORDER BY event_week) AS next_event_week
  FROM base
  )
), current_sec_action AS
SELECT
 event_week,
 COUNTIF(user_type= "new") AS new_cnt,
 COUNTIF(user_type= "current") AS current_cnt,
 COUNTIF(user_type= "resurrected") AS resurrected_cnt,
 platform
FROM retain_user
WHERE event_name = "click_payment"
GROUP BY event_week, platform
ORDER BY event_week, platform
#Current//New 중에 click_payment를 누른 사람은 몇 명일까?
SELECT
 COUNT(DISTINCT user_pseudo_id) AS current_cp_cnt
FROM retain_user
WHERE event_name = "click_payment"
      AND user_type = "current"--"new" 
#Current 유저의 특징(2nd action 이 무엇인지)
(
SELECT
 event_name,
 LEAD(event_name) OVER (PARTITION BY user_pseudo_id ORDER BY event_week ASC) AS sec_action
FROM retain_user
WHERE user_type = "current" 
)
SELECT
 sec_action,
 COUNT(sec_action) AS sec_action_cnt
FROM current_sec_action
GROUP BY sec_action
ORDER BY sec_action_cnt DESC

#user_pseudo_id 검증
SELECT 
event_name,
DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS KST,
user_pseudo_id
FROM advanced.app_logs
WHERE user_pseudo_id = "1398707938.1043724142"
      AND event_date BETWEEN "2022-08-01" AND "2022-08-10"
ORDER BY KST ASC
#event_params UNNEST
SELECT
event_name,
params
FROM advanced.app_logs as a
CROSS JOIN UNNEST(event_params) AS params
WHERE event_date = "2022-08-01"

Foodie Express 현황

Traffic 관점 2022년 8월 1일 ~ 2022년 11월 30일 기간동안 44,141명의 신규 유입. Current 유저는 9,330명으로 약 21%로 낮은 상황. 또한 Dormant 유저는 56,504명(*복귀했다가 이탈한 유저 포함)으로 이탈율 87.3%로 굉장히 높음 유입 유저의 Android : IOS 비율은 8:2 Revenue 관점 동일기간 매출은 약 1억2천5백5십만원 기록. 매출액에서 신규가 차지하는 비중은 약 60%, Current 유저가 약 40%. 하지만, 유저별 PUR(Pay User Rate)를 보면 Current 유저가 구매를 더 많이 함

제품을 개선하기 위한 전략 Dormant 이탈율을 개선하여 Current 유저를 증가시키는 방안으로 Dormant 유저의 마지막 이벤트가 무엇인지를 분석 —> 해당 UI & UX 를 개선

Screen_view welcome, home 화면과 로그인 화면을 우선 유저들이 지속할 수 있도록 매력적인 UI 개편하여 Dormant 유저를 줄이는 전략 Dormant 유저 이탈 시점을 확인하면 약 60%가 Screenview와 click_login 이후 이탈. Screen_view 상세 시점을 확인하면, Screenview welcome 화면에서 %35, home 화면에서 %23% 로 약 60%가 이탈