[빠짝스터디 3주차 과제] 리텐션 연습문제

1) Weekly Retention을 구하는 쿼리를 바닥부터 스스로 작성해보세요

WITH base AS (
  SELECT
    DISTINCT
      user_id,
      user_pseudo_id,
      event_name,
      DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul") AS event_datetime,
      DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul")) AS event_date
  FROM `advanced.app_logs`
), first_week_and_diff AS (
  SELECT
    *,
    DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week
  FROM (
    SELECT
      DISTINCT
        user_pseudo_id,
        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 base
  )
)

SELECT
  diff_of_week,
  COUNT(DISTINCT user_pseudo_id) AS user_cnt,
  ROUND(SAFE_DIVIDE((COUNT(DISTINCT user_pseudo_id)), (FIRST_VALUE(COUNT(DISTINCT user_pseudo_id)) OVER(ORDER BY diff_of_week))), 3) AS retention
FROM first_week_and_diff
GROUP BY diff_of_week
ORDER BY diff_of_week

2) Retain User를 New+Current+Resurrected+DormantUser로 나누는 쿼리를 작성해보세요.

• 유저 구분 기준은 제품이나 서비스에 따라 다르겠지만 applogs 데이터는 주단위로 했을때 총 24주치 데이터가 있었고, 유저별로 평균 구매 주기와 방문 횟수를 구해보았을 때 6주를 기준으로 유저를 구분하면 될 것이라 생각함.

  • New : 첫 방문한 신규 유저

     

  • Current : 방문 횟수 2회 이상이면서 6주 이내 재 방문 이력 있는 유저

  • Resurrected : 방문 횟수 2회 이상 & 직전 방문 경과 7주 이상이면서 6주 이내 재방문 이력 있는 유저

  • Dormant : 직전 방문 경과 7주 이상이면서 6주 이내 재방문 없는 유저

    

    (사실 이렇게 나누는게 맞는지 모르겠지만, 나름의 논리적인 기준을 찾아보려고 노력하였음. 😫 과제 기한 연장에도 개인적인 사정으로 시간이 부족하여 깊이 탐구하지 못했고, 쿼리 작성을 완성하지 못했습니다 😩 추후에 완성하면 보완하도록 하겠습니다. )

3) 주어진 데이터에서 어떤 사람들이 리텐션이 그나마 높을까요? 찾아보세요

• 미완성으로 추후에 완성하면 보완하겠습니다 !

4) Core Event를 “click_payment” 라고 설정하고 Weekly Retention을 구해주세요

WITH base AS (
  SELECT
    DISTINCT
      user_id,
      user_pseudo_id,
      event_name,
      DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul") AS event_datetime,
      DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul")) AS event_date
  FROM `advanced.app_logs`
), first_week_and_diff AS (
  SELECT
    *,
    DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week
  FROM (
    SELECT
      DISTINCT
        user_pseudo_id,
        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 base
  )
)

SELECT
  diff_of_week,
  COUNT(DISTINCT user_pseudo_id) AS user_cnt,
  ROUND(SAFE_DIVIDE((COUNT(DISTINCT user_pseudo_id)), (FIRST_VALUE(COUNT(DISTINCT user_pseudo_id)) OVER(ORDER BY diff_of_week))), 3) AS retention
FROM first_week_and_diff
GROUP BY diff_of_week
ORDER BY diff_of_week