인프런 커뮤니티 질문&답변

4. Core Event를 "click_payment"라고 설정하고 Weekly Retention을 구해주세요.

WITH base AS(
  SELECT
    event_date,
    event_timestamp,
    user_pseudo_id,
    event_name,
    MAX(IF(ep.key = 'firebase_screen', ep.value.string_value, NULL)) AS firebase_screen,
    MAX(IF(ep.key = 'food_id', ep.value.int_value, NULL)) AS food_id,
    MAX(IF(ep.key = 'session_od', ep.value.string_value, NULL)) AS session_od
  FROM advanced.app_logs
  CROSS JOIN UNNEST(event_params) AS ep
  GROUP BY ALL
), payment AS(
  SELECT 
    user_pseudo_id,
    event_timestamp,
    event_date,
    event_name,
    --firebase_screen,
  FROM base 
  WHERE event_name = 'click_payment'
  ORDER BY event_date
), payment_first_week AS(
  SELECT
    *,
    DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week,
    DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week
  FROM(
    SELECT 
      * EXCEPT(event_timestamp, event_date),
      DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date
    FROM payment
    )
), payment_user_cnt AS(
  SELECT
    diff_of_week,
    COUNT(DISTINCT user_pseudo_id) AS user_cnt 
  FROM(
    SELECT 
      *,
      DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week
    FROM payment_first_week
  )
  GROUP BY diff_of_week
  ORDER BY diff_of_week
)

############################### 리텐션 비율 구하기 ###############################

SELECT 
*,
SAFE_DIVIDE(user_cnt, first_week_user_cnt) AS retention_rate
FROM(
 SELECT 
   *,
   FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week) AS first_week_user_cnt
 FROM payment_user_cnt
)

여기까지는 payment 테이블 WHERE절에 event_name으로 "click_payment" 조건을 걸어준 것 빼곤 강의의 코드를 거의 그대로 사용했습니다.



결과는 이렇게 나왔고, 저는 diff_of_week가 4일 때 갑자기 user_cnt가 상승했다가 5일 때 다시 이전 수치로 돌아간 것에 주목했습니다(약 18% 상승).

SELECT
event_week,
COUNT(DISTINCT user_pseudo_id) AS user_cnt 
FROM(
SELECT 
  *,
  DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week
FROM payment_first_week
)
WHERE diff_of_week = 4
GROUP BY event_week
ORDER BY user_cnt DESC

그래서 diff_of_week가 4일 때의 "click_payment" 이벤트를 발생시킨 유저의 수를 추출해보았습니다.

결과를 보았을 때, 2022-10-31 ~ 2023-01-09의 유저 수가 많음을 확인하였고 이는, 해당 기간이 첫 주문 후 4주가 지난 사람들의 재주문 건수가 많았을 것이라는 생각이 들었습니다.

##################### 해당 날짜, 이전, 이후의 데이터 추출 ####################

# target_date 테이블: 해당 날짜를 만족하는 행만 추출
, target_date AS(
  SELECT 
    *
  FROM base
  WHERE user_pseudo_id IN(
    SELECT user_pseudo_id
    FROM payment_first_week
    WHERE first_week BETWEEN '2022-10-03' AND '2022-12-12'
    ) 
  ORDER BY user_pseudo_id, event_timestamp 

# before_target_date 테이블: 해당 날짜 이전
), before_target_date AS(
  SELECT 
    *
  FROM base
  WHERE user_pseudo_id IN(
    SELECT user_pseudo_id
    FROM payment_first_week
    WHERE first_week BETWEEN '2022-08-01' AND '2022-10-02'
    ) 
  ORDER BY user_pseudo_id, event_timestamp 

# after_target_date 테이블: 해당 날짜 이후
), after_target_date AS(
  SELECT 
    *
  FROM base
  WHERE user_pseudo_id IN(
    SELECT user_pseudo_id
    FROM payment_first_week
    WHERE first_week BETWEEN '2022-12-13' AND '2023-12-31'
    ) 
  ORDER BY user_pseudo_id, event_timestamp 
)

################ click_payment를 발생시킨 유저와 그 때의 날짜 추출 ################

# payment_users 테이블: 해당 날짜에서 event_name칼럼에 "click_payment"를 가지고 있는 user_pseudo_id와 그 때의 event_date를 추출
, payment_users_target_date AS(
  SELECT 
    event_date,
    user_pseudo_id
  FROM(
  SELECT 
    *,
    CASE WHEN event_name = 'click_payment' THEN 1 
    ELSE 0 END AS payment_user
  FROM target_date
  )
  WHERE payment_user = 1
)

# payment_users_before 테이블: 해당 날짜 이전의 payment user의 정보
,payment_users_before AS(
  SELECT 
    event_date,
    user_pseudo_id
  FROM(
  SELECT 
    *,
    CASE WHEN event_name = 'click_payment' THEN 1 
    ELSE 0 END AS payment_user
  FROM before_target_date
  )
  WHERE payment_user = 1
)

# payment_users_after 테이블: 해당 날짜 이후의 payment user의 정보
,payment_users_after AS(
  SELECT 
    event_date,
    user_pseudo_id
  FROM(
  SELECT 
    *,
    CASE WHEN event_name = 'click_payment' THEN 1 
    ELSE 0 END AS payment_user
  FROM after_target_date
  )
  WHERE payment_user = 1
)

그래서 전체 날짜를 위의 해당 날짜와 그 전, 후 3개로 나누어 해당 기간의 정보만을 담는 테이블을 생성하고, 각 기간에 "click_payment" 이벤트를 가지고 있는 유저의 user_pseudo_id와 그 때의 event_date를 추출하였습니다.

################ 3개의 기간동안 사람들이 주문한 음식의 food_id 추출 ################


# 해당 기간동안 사람들이 주문한 food_id
, food_cnt_target_date AS(
  SELECT 
    food_id,
    COUNT(*) AS food_cnt
  FROM target_date td
  JOIN payment_users_target_date pu
  ON td.user_pseudo_id = pu.user_pseudo_id 
  AND td.event_date = pu.event_date
  WHERE event_name = "click_cart"
  GROUP BY food_id
  ORDER BY food_cnt DESC
  --ORDER BY td.user_pseudo_id, event_timestamp

), # 해당 기간 이전 사람들이 주문한 food_id
  food_cnt_before_target AS(
  SELECT 
    food_id,
    COUNT(*) AS food_cnt
  FROM before_target_date td
  JOIN payment_users_before pu
  ON td.user_pseudo_id = pu.user_pseudo_id 
  AND td.event_date = pu.event_date
  WHERE event_name = "click_cart"
  GROUP BY food_id
  ORDER BY food_cnt DESC

), # 해당 기간 이후 사람들이 주문한 food_id 
  food_cnt_after_target AS(
  SELECT 
    food_id,
    COUNT(*) AS food_cnt
  FROM after_target_date td
  JOIN payment_users_after pu
  ON td.user_pseudo_id = pu.user_pseudo_id 
  AND td.event_date = pu.event_date
  WHERE event_name = "click_cart"
  GROUP BY food_id
  ORDER BY food_cnt DESC
)

위에서 구한 id와 date를 이용해 세 기간동안 사람들이 주문한 음식과 음식 별 건수를 추출하였습니다. 전체 데이터를 살펴본 결과, "click_payment"는 항상 주문 마지막에 발생하는 이벤트이며, 이 row에는 food_id는 없고, "click_cart" 이벤트를 발생시켰을 때 해당 주문건에 대한 food_id가 row에 포함되어 있어 event_name을 "click_cart"로 지정하였습니다.

################ 각 food_id 별 해당 기간, 이전, 이후의 주문 건수 카운트 ################

, total_cnt AS(
  SELECT 
    td.food_id,
    btd.food_cnt AS food_cnt_before_target_date,
    td.food_cnt AS food_cnt_target_date,
    atd.food_cnt AS food_cnt_after_target_date
  FROM food_cnt_target_date td
  JOIN food_cnt_before_target btd
  ON td.food_id = btd.food_id
  JOIN food_cnt_after_target atd
  ON td.food_id = atd.food_id
)

################ 각 food_id 별 해당 기간, 이전, 이후의 주문 건수 비율 ################

SELECT 
 food_id,
 food_cnt_before_target_date,
 ROUND(SAFE_DIVIDE(food_cnt_before_target_date, SUM(food_cnt_before_target_date) OVER()), 4) AS rate_before,

 food_cnt_target_date,
 ROUND(SAFE_DIVIDE(food_cnt_target_date, SUM(food_cnt_target_date) OVER()), 4) AS rate_target,

 food_cnt_after_target_date,
 ROUND(SAFE_DIVIDE(food_cnt_after_target_date, SUM(food_cnt_after_target_date) OVER()), 4) AS rate_after
FROM total_cnt
ORDER BY rate_target DESC

세 기간동안 사람들이 주문한 food_id와 그 횟수, 비율을 구해보았습니다. 각 음식의 비율은 매우 적지만 target_date를 기준으로 정렬해 보았을 때, 확실히 해당 기간동안 주문량이 높고 나머지 기간에는 주문량이 낮은 음식들이 존재했습니다(ex. 1438, 1516 등).

하지만, 이것이 4주전 주문자들의 실제 재주문 결과인지, 단지 해당기간 동안 신규 유저가 늘었기 때문인지 확실하지 않다고 생각문자와 그 외 기간동안의 신규 주문자를 비교해 보았습니다.

SELECT
  new_user_target_date,
  COUNT(*) AS new_user_cnt
FROM(
SELECT
 CASE WHEN first_week BETWEEN '2022-10-31' AND '2023-01-09' THEN 1 
 ELSE 0 END AS new_user_target_date
FROM payment_first_week
)
GROUP BY new_user_target_date

해당 기간의 신규 유저수는(new_user_target_date = 1) 6261명, 그 외 기간의 신규 유저수는 6898명으로, 전체 기간 대비 해당 기간의 비율을 고려해보았을 때, 신규 유저가 크게 늘었다고 보기 어려웠습니다.

따라서, 2022~10-03 ~ 2022-12-12일이 포함된 주차에 첫 주문을 한 고객들의 만족도가 높았거나, 그 당시에 시행했던 첫 주문 관련 마케팅, 광고 효과가 좋았을 것이라고 판단할 수 있었습니다.

** 앱 로그 데이터를 처음 접했기 때문에 코드 오류나 논리적인 비약이 있을 것이라고 생각하지만, 단지 Weekly Retention에 WHERE 조건을 추가하는 것에 그치기 아쉬워서(?) 의식의 흐름대로 분석해보았습니다.