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_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,
      date_trunc(min(event_date) over(partition by user_pseudo_id), week(monday)) as first_week,    
  from `advanced.app_logs`
), first_week_diff as(
  select
    distinct
      user_pseudo_id,
      DATE_DIFF(event_week, first_week, week) as diff_of_week,
      event_week,
      event_date
  from base
), user_counts as(
  select
    diff_of_week,
    count(distinct user_pseudo_id) as user_count
  from first_week_diff
  group by diff_of_week
)

select
  *,
  round(safe_divide(user_count,first_week_user_count), 2) as retention_rate
from(
  select
    diff_of_week,
    first_value(user_count) over(order by diff_of_week asc) as first_week_user_count,
    user_count
  from user_counts
)

개인적인 해석: retention의 지표가 완만해지기는 하지만 전체적으로 우하향 적인 성격을 띄고 있기 때문에 이 app은 뭔가 조치를 취해 줘야할거 같다.

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

Retain User를 나누려면 우선 이 제품에 대해 알고 있어야 하며 사회적인 통념에 대해서도 이해를 하고 있어야 한다고 생각한다.

그리하여서 다음과 같은 생각을 가지고 기준 및 가설을 가지고 을 나눠 보았다

1. 이 제품은 배달 주문 앱으로서 사람들은 매일 음식을 먹는다.
2. 음식은 매일 먹는 만큼 사람들이 자주 앱을 사용한다고 생각을 하였다.
3. 그리하여서 일로 구해볼까 하다가 일단 주별로 구해보고 그 결과를 본 뒤에 조정을 해보자 라는게 나의 생각이었다.
   1. NEW : 첫 활동 주와 마지막 활동 주가 동일한 사용자.
   2. Current : 총 활동 주간의 수가 4주 이상인 사용자
   3. Resurrected : 활동 주간의 수가 4주 미만이지만 첫 활동 주와 마지막 활동 주 사이의 기간이 4주 이상인자
   4. Dormant: 마지막 활동 주 이후로 현재 날짜까지 4주 이상 비활성 상태인 사용자

관련 QUERY

• QUERY

그룹화 하여 비율로 볼때 복귀자와 신규 그룹은 많아 보이지만 그만큼 이 앱을 한번 해보고 꾸준히 사용하는 비율은 적다는 뜻이며 꾸준히 사용하는 비율은 전체 사용자의 15% 정도로 집계됨을 알수 있다.

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

• user_group별 retention_rate비율
• query