연습과제

첫 주차에 주문할 때 한 번이라도 음식 추천 시스템(use_recommend_food)을 이용한 사람들이, 그렇지 않은 사람들보다 재주문 리텐션이 높을까?

SQL / 최초 AI 제안본

WITH payment_base AS (
  -- 1. 결제 로그만 추출하며, 스칼라 서브쿼리로 추천시스템 사용 여부 컬럼화 (1 row per event)
  SELECT
    user_pseudo_id,
    DATE_TRUNC(DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), WEEK(MONDAY)) AS event_week,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'use_recommend_food') AS use_recommend_food
  FROM advanced.app_logs
  WHERE event_name = 'click_payment'
),
cohort_base AS (
  -- 2. 유저별 첫 결제 주차(first_week)와, '첫 결제 시점'의 추천시스템 사용 여부 정의
  SELECT
    user_pseudo_id,
    MIN(event_week) AS first_week,
    -- 첫 주차에 추천기능을 썼는지 확인 (해당 주차 내 True가 하나라도 있으면 Yes)
    MAX(IF(use_recommend_food = 'True', 'Yes', 'No')) AS is_recommend_used_first_week
  FROM payment_base
  GROUP BY user_pseudo_id
),
activity_data AS (
  -- 3. 코호트 유저들이 이후 주차에 결제(활동)를 했는지 매핑
  SELECT
    c.is_recommend_used_first_week,
    c.first_week,
    p.event_week,
    DATE_DIFF(p.event_week, c.first_week, WEEK(MONDAY)) AS weeks_after_first_week,
    COUNT(DISTINCT p.user_pseudo_id) AS active_users
  FROM cohort_base AS c
  LEFT JOIN payment_base AS p 
    ON c.user_pseudo_id = p.user_pseudo_id
  GROUP BY ALL
),
cohort_retention AS (
  -- 4. 추천시스템 사용여부(Yes/No) 및 첫 주차(first_week)별 코호트 모수 계산
  SELECT
    *,
    FIRST_VALUE(active_users) OVER(
      PARTITION BY is_recommend_used_first_week, first_week 
      ORDER BY weeks_after_first_week
    ) AS cohort_users
  FROM activity_data
)
-- 5. 최종 리텐션 비율 추출
SELECT
  is_recommend_used_first_week, 
  first_week,                   
  weeks_after_first_week,
  active_users,
  cohort_users,
  ROUND(SAFE_DIVIDE(active_users, cohort_users), 3) AS retention_rate
FROM cohort_retention
ORDER BY 
  is_recommend_used_first_week DESC, 
  first_week, 
  weeks_after_first_week;

https://docs.google.com/spreadsheets/d/12Co5_iXocSfDD7i8HaKFtkp0bowpV1r0ayDSxyhKjLg/edit?usp=sharing

첫 시도 결과: 음식 추천을 이용한 그룹(Yes/녹색)과 이용하지 않은 그룹(No/붉은색)의 시각화 데이터가 극명한 차이를 보여, 음식 추천을 이용한 유저의 재주문 리텐션이 확연히 높을 것으로 가정하고 과제를 시작했음.

SQL / 최종 자가 수정본

WITH base AS(
  # 1. 주차별 결제 유저들의 음식추천 시스템 사용 여부
  SELECT
    user_pseudo_id,
    event_timestamp,
    DATE_TRUNC(DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), WEEK(MONDAY)) AS event_week,
    CASE
      WHEN ep.value.string_value = 'True' THEN 'Yes'
      WHEN ep.value.string_value = 'False' THEN 'No'
      ELSE NULL
    END AS is_recommend_used
  FROM advanced.app_logs
  CROSS JOIN UNNEST(event_params) AS ep
  WHERE
    event_name = 'click_payment'
    AND ep.key = 'use_recommend_food'
)
, week_data AS(
  # 2. 코호트 그룹 설정 및 각 그룹 별 경과 주차
  SELECT
    *,
    MAX(IF(event_week = first_week, is_recommend_used, NULL)) OVER(PARTITION BY user_pseudo_id) AS is_recommend_used_first_week,
    DATE_DIFF(event_week, first_week, WEEK(MONDAY)) AS weeks_after_first_week
  FROM(
    SELECT
      *,
      MIN(event_week) OVER(PARTITION BY user_pseudo_id) AS first_week
    FROM base
  )
), user_cnt_data AS(
  # 3. 코호트 그룹 별 활성 및 전체 유저
  SELECT
    *,
    FIRST_VALUE(active_users) OVER(PARTITION BY is_recommend_used_first_week, first_week ORDER BY weeks_after_first_week) AS cohort_users
  FROM(
    SELECT
      is_recommend_used_first_week,
      first_week,
      weeks_after_first_week,
      COUNT(DISTINCT user_pseudo_id) AS active_users
    FROM week_data
    GROUP BY ALL
  )
)

# 4. 코호트 그룹 별 최종 리텐션 비율 
SELECT
  is_recommend_used_first_week,
  first_week,
  weeks_after_first_week,
  ROUND(SAFE_DIVIDE(active_users, cohort_users), 3) AS retention_rate
FROM user_cnt_data
ORDER BY
  is_recommend_used_first_week DESC,
  first_week,
  weeks_after_first_week

https://docs.google.com/spreadsheets/d/1M83okX3vjDyuQgOVxf9vXiIFufuUnHKloCsZo_lDdkI/edit?usp=sharing

최종 검증 결과: 최초 시도와는 달리, 첫 주차에 한 번이라도 음식 추천을 이용해 결제한 유저와 그렇지 않은 유저 사이에 유의미한 리텐션 차이는 발견되지 않았음.

분석 결과

첫 시도의 결과는 유저가 전체 기간 동안 단 한 번이라도 추천 기능을 썼다면 무조건 'Yes' 그룹으로 몰아넣어진 것임. 따라서 오래 살아남아 결제를 자주 한 유저(당연히 리텐션이 높음)들이 자연스럽게 'Yes' 그룹으로 모두 넘어가 버림. 반면 가입하자마자 이탈한 유저들은 기능을 써 볼 기회조차 없어 전부 'No' 그룹에 남게 됨. 즉, 음식 추천 기능이 좋아서 리텐션이 높은 것이 아니라, 원래부터 리텐션이 높은 생존자들이 'Yes' 그룹을 점령해 버린 **심각한 데이터 오류(생존자 편향)**가 발생하였음.