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

WITH base AS (
SELECT
  user_pseudo_id,
  user_id,
  platform,
  datetime(timestamp_micros(event_timestamp), 'Asia/Seoul') as event_datetime,
  date_trunc(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul'), day) as event_date,
  date_trunc(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul'), week(monday)) as event_week,  
  date_trunc(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul'), month) as event_month,
  event_name,
  event_params
FROM workspace.app_logs
),
user_visit_base AS (
SELECT DISTINCT
  user_pseudo_id,
  min(event_week) over(partition by user_pseudo_id) as first_visit_week,
  event_week as visit_week
FROM base
),
user_visit_weekdiff AS (
SELECT
  first_visit_week, 
  date_diff(visit_week, first_visit_week, week) as week_diff,
  count(distinct user_pseudo_id) as visit_users
FROM user_visit_base
GROUP BY ALL
)
SELECT
  first_visit_week, 
  week_diff,
  visit_users,
  first_value(visit_users) over(partition by first_visit_week order by week_diff asc rows between unbounded preceding and unbounded following) as cohort_size,
  safe_divide(visit_users, first_value(visit_users) over(partition by first_visit_week order by week_diff asc rows between unbounded preceding and unbounded following)) as retention_rate
FROM user_visit_weekdiff
;

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

1. 제품을 먼저 이해한다.
   1. 이 제품은 음식을 배달 주문하는 앱이다.
   2. 사람이 매일같이 수행하는 의식주에 해당되는 제품이다.
   3. 따라서, 항공권이나 전/월세 부동산 계약 등 제품보다는 사용 빈도가 더 빈번할 것이라고 예상된다.
2. 앱 사용자들의 주기를 구한다.
   1. 마지막으로 앱을 사용한 지 얼마나 지나야 휴면 유저라고 볼 수 있을까? 또는 마지막으로 앱을 사용한 지 얼마가 지나지 않았으면 계속 이용 중인 유저라고 볼 수 있을까?
   2. 위 질문에 답하기 위해, 일단 현재의 데이터를 기반으로 유저들의 앱 사용 주기를 파악해 보았다.
   3. 앱 사용 주기를 구하는 쿼리는 아래와 같다.
      • Query
      1. 앱 사용 주기라는 것은 최소 3개의 서로 다른 날짜에 앱을 실행했을 때부터 구할 수 있다고 판단하여, 현재까지 최소 3일 이상은 실행해 본 유저에 대해서만 사용 주기를 구함
      2. 또한, 비교적 최근에 신규로 유입된 유저는 아직 주기랄 게 없을 수 있어서 최근 1개월 이내 신규 유저는 제외 (현재의 시점은 2023-01-20을 기준으로 하였음)
   4. 앱 사용 주기가 60일까지는 전체 유저의 95%가 해당되므로, 이 앱의 사용 주기를 60일(2개월)로 잡으면 적절한 주기일 것이라 판단
      • 데이터
3. 앱 사용주기에 따라 각 유저를 구분하는 로직을 세운다.

   1. 현재의 시점이 2023-01-20이고, 앱 사용 주기를 60일(2개월)로 정의하였으므로 유저별 최초 접속일과 기간별 접속일수를 활용하여 아래와 같은 로직으로 유저를 구분해 본다.

      유저 그룹	용어적 정의	사업적 정의	쿼리적 정의
      New	신규 유저	최근 60일 이내 새로 방문한 유저	유저의 최초 접속일 ≥ 지금으로부터 60일 이전
      Current	지속 유저	최근 60일 이내 재방문했고, 그 이전 60일 동안에도 방문한 유저	지금으로부터 60일 이내에 유저의 접속일수 1일 이상
      AND 지금으로부터 120일 ~ 60일 이전 기간에 접속일수 1일 이상
      Resurrected	부활 유저	최근 60일 이내 재방문했고, 그 이전 60일 동안에는 방문하지 않은 유저	지금으로부터 60일 이내에 유저의 접속일수 1일 이상
      AND 지금으로부터 120일 ~ 60일 이전 기간에 접속일수 0일
      Dormant	휴면 유저	최근 60일 이내 재방문하지 않은 유저	지금으로부터 60일 이내에 유저의 접속일수 0일

4. 최근 60일 내 접속한 유저를 각 그룹별로 분류하는 쿼리를 작성한다.
   • Query

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

• 검색 기능을 이용했는지 여부에 따라 Weekly 리텐션(앱 방문 기준)이 차이가 나는 것을 확인하였다.
• 검색 기능을 이용해 본 유저는 그렇지 않은 유저 대비 리텐션 플래토가 약 2 ~ 3%p 정도 높다.
• 추정가능한 원인으로는,
  • 우리 제품의 검색 경험(검색의 정확도, 속도, 검색 결과를 보여주는 방식 등)이 고객 경험 측면에서 긍정적으로 받아들여진다.
  • 검색이라는 액션을 통해 음식 및 식당을 탐색하고 주문까지 하는 여정이 더 수월해지고 이는 곧 재방문, 재구매로 이어진다.
• 실행가능한 액션으로는,
  • 검색 기능을 더 접근성 있게 높인다. (찾으시는 식당이 없나요? 검색해보세요!)
  • 검색 기능을 활용하는 범위를 넓힌다. (뭘 먹을지 고민인가요? 아무거나 검색해보세요!)

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

WITH base AS (
SELECT
  user_pseudo_id,
  user_id,
  platform,
  first_value(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul')) over(partition by user_pseudo_id order by event_timestamp asc rows between unbounded preceding and unbounded following) as first_visit_datetime,
  last_value(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul')) over(partition by user_pseudo_id order by event_timestamp asc rows between unbounded preceding and unbounded following) as last_visit_datetime,
  datetime(timestamp_micros(event_timestamp), 'Asia/Seoul') as event_datetime,
  date_trunc(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul'), day) as event_date,
  date_trunc(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul'), week(monday)) as event_week,  
  date_trunc(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul'), month) as event_month,
  event_name
FROM workspace.app_logs
),
user_click_payment_base AS (
SELECT DISTINCT
  user_pseudo_id,
  min(event_week) over(partition by user_pseudo_id) as first_click_payment_week,
  event_week as click_payment_week
FROM base
WHERE event_name = 'click_payment'
),
user_payment_weekdiff AS (
SELECT
  first_click_payment_week, 
  date_diff(click_payment_week, first_click_payment_week, week) as week_diff,
  count(distinct user_pseudo_id) as click_payment_users
FROM user_click_payment_base
GROUP BY ALL
)
SELECT
  first_click_payment_week, 
  week_diff,
  click_payment_users,
  first_value(click_payment_users) over(partition by first_click_payment_week order by week_diff asc rows between unbounded preceding and unbounded following) as cohort_size,
  safe_divide(click_payment_users, first_value(click_payment_users) over(partition by first_click_payment_week order by week_diff asc rows between unbounded preceding and unbounded following)) as retention_rate
FROM user_payment_weekdiff
;