✅ 연습문제

(1) weekly retention

--1) weekly retention
WITH base AS (
  -- 날짜 데이터 전처리 
  -- DATE도 실제 날짜와 일치하지 않을 수 있으니, 다시 확인하기 
  -- DATETIME(TIMESTAMP_MICROS(timestamp데이터),'대륙/국가') 기억하고 사용하기 

  SELECT 
    DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul') AS event_datetime, 
    DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul')) AS event_date,
    event_name, 
    user_pseudo_id
  FROM `bigquery-432401.avdanced.app_logs_temp` 
)
-- 일자 간 차이 구하기 
, cal_week AS (
  SELECT
    DISTINCT 
      user_pseudo_id,
      -- 최초일(week 단위로) 구하기
      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
), cal_diff AS (
  -- 주차데이터간 차이 구하기
  SELECT
    *,
    DATE_DIFF(event_week, first_week,WEEK) AS diff_week
  FROM cal_week
), user_count AS (
  -- 주차별 user 수 구하기 
  SELECT
    diff_week,
    COUNT(distinct user_pseudo_id) as user_cnt
  FROM cal_diff
  GROUP BY diff_week
)
-- 출력
SELECT
*, 
  ROUND(SAFE_DIVIDE(user_cnt, first_week_user_cnt),2) AS retention_rate
FROM (
  SELECT
    diff_week,
    user_cnt,
    FIRST_VALUE(user_cnt) OVER(ORDER BY diff_week) AS first_week_user_cnt
  FROM user_count
)

• 아직 익숙하지 않아서 중간중간 다시 코드 보고 작성했다. 점점 더 익숙해지길!

   

(2) Retain User

WITH base AS (
  -- 날짜 데이터 전처리 
  -- DATE도 실제 날짜와 일치하지 않을 수 있으니, 다시 확인하기 
  -- DATETIME(TIMESTAMP_MICROS(timestamp데이터),'대륙/국가') 기억하고 사용하기 

  SELECT 
    DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul') AS event_datetime, 
    DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul')) AS event_date,
    event_name, 
    user_pseudo_id
  FROM `bigquery-432401.avdanced.app_logs_temp` 
  WHERE
    event_date BETWEEN "2022-08-01" AND "2022-11-03"
)
, cal_diff AS (
  -- 날짜데이터 간 차이 구하기
  SELECT
    *,
    DATE_DIFF("2022-11-03", event_date,DAY) AS diff_date
  FROM base
), user_type AS (

  -- user type 구하기
  -- 0. 해당없음
  -- 1. new : 첫번째 접속
  --2. current : 최근 30일내에 접속
  -- 3. resurrected : 30일 이상 활동이 없고, 최근 7일 이내 다시 접속한 사용자
  -- 4. dormant : 최근 60일 간 접속기록 없음  
  SELECT
    *,
    CASE
      -- 첫 번째 접속 => 'new'
      WHEN diff_date = FIRST_VALUE(diff_date) OVER(PARTITION BY user_pseudo_id ORDER BY event_date) THEN 'new' 
      -- 최근 30일 내에 접속한 사용자 => 'current'
      WHEN diff_date <= 30 THEN 'current'
      -- 30일 이상 활동이 없고, 최근 7일 이내 다시 접속한 사용자 => 'resurrected'
      WHEN diff_date > 30 AND diff_date <= 37 THEN 'resurrected'
      -- 60일 이상 접속하지 않은 사용자 => 'dormant'
      WHEN diff_date > 60 THEN 'dormant'
      ELSE 'none'
    END AS type
  FROM cal_diff
  GROUP BY all
  ORDER BY user_pseudo_id,diff_date
)
SELECT 
  *
FROM user_type

• 나름 user를 나눈다고 나눴는데, 잘 못 나눠진 느낌이 든다! 현업에는 어떻게 나눌지 궁금하다

(3) 어떤 user가 많은가?

WITH base AS (
  -- 날짜 데이터 전처리
  SELECT 
    DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul') AS event_datetime, 
    DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul')) AS event_date,
    event_name, 
    user_pseudo_id
  FROM `bigquery-432401.avdanced.app_logs_temp` 
  WHERE
    event_date BETWEEN "2022-08-01" AND "2022-11-03"
),
cal_diff AS (
  -- 날짜데이터 간 차이 구하기
  SELECT
    *,
    DATE_DIFF("2022-11-03", event_date, DAY) AS diff_date
  FROM base
),
user_type AS (
  -- user type 구하기
  SELECT
    distinct user_pseudo_id,
    CASE
      WHEN diff_date = FIRST_VALUE(diff_date) OVER(PARTITION BY user_pseudo_id ORDER BY event_date) THEN 'new'
      WHEN diff_date <= 30 THEN 'current'
      WHEN diff_date > 30 AND diff_date <= 37 THEN 'resurrected'
      WHEN diff_date > 60 THEN 'dormant'
      ELSE 'none'
    END AS type
  FROM cal_diff
), cal_cnt AS (
  -- 각 type별 사용자 수 계산
  SELECT 
    type, 
    COUNT(user_pseudo_id) AS user_cnt
  FROM user_type
  GROUP BY type
)
SELECT
  type,
  user_cnt,
  ROUND(user_cnt * 100.0 / (SELECT COUNT(*) FROM user_type), 2) AS ratio
FROM cal_cnt
ORDER BY ratio DESC

• new인 사용자가 69.87 %로 전체의 대다수를 차지한다. 그다음이 최근에 7일 내에 접속한 유저가 21.83%, resurrected : 30일 이상 활동이 없고, 최근 7일 이내 다시 접속한 사용자가 2.59%, 최근 60일 간 접속기록 없는 사용자가 1.25%를 차지하였다.

• 2022-11-03일 기준 "2022-08-01" 에서 "2022-11-03" 기간 동안 서비스를 이용하는 대다수의 사람들은 신규 가입자들이 많다는 것을 알 수 있다.

• 또한, current User가 많은 것으로 보아, 최근 7일이내에 서비스를 사용한 사람들의 비율이 두번째로 높다.

• 현재 신규이용자의 수가 대다수를 차지함으로 이 신규 사용자를 current user로 전환하기 위한 전략을 고안해야 할 것으로 보인다.

• none이 resurrected, dormant보다 많은데, type을 나누는 기준을 수정하면 더 효율적이게 나눌 수 있지 않을까 생각된다.

(4) core event를 click_payment로 놓았을 때 Weekly retention

WITH base AS (
  SELECT 
    event_name,
    user_pseudo_id,
    DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date, 
    DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_time
  FROM
  `bigquery-432401.avdanced.app_logs_temp`
  WHERE event_name = "click_payment"
), week_diff AS (
    SELECT
        *,
        DATE_DIFF(event_week, first_week, WEEK) AS diff
    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
    )
), user_counts AS (
    SELECT 
        diff,
        COUNT(DISTINCT user_pseudo_id) AS user_cnt
    FROM week_diff
    GROUP BY diff
)


SELECT
  *,
  ROUND(SAFE_DIVIDE(user_cnt, first_week_user_cnt), 2) AS retention_rate
FROM (
  SELECT
    diff,
    user_cnt,
    FIRST_VALUE(user_cnt) OVER(ORDER BY diff ASC) AS first_week_user_cnt
  FROM user_counts
)

• 1주차 이후로 사용자의 수가 급감. 2주차 부터는 계속 0.01을 유지. 15주차부터 24주차까지는 0 retention rate를 유지.

• 사용자가 1주차 이후로 대거 이탈함을 볼 수 있음. 15주차부터 0.01에서 0.0으로 감소.

   

• 1주차에 이벤트 혹은 서비스 런칭으로 사용자수가 급격히 증가하였으나, 서비스 경험이 사용자에게 매력적으로 다가오지 못하여 바로 그 다음주부터 급격하게 감소하는 것을 볼 수 있음.

• 따라서, core_event에 대한 근본적인 개선이 필요할 것으로 보임.

   

✅ 배운점 및 느낀점

• 리텐션 분석에 대한 전반적인 지식을 얻을 수 있었다.

• 아직, 데이터에서 리텐션 분석을 하는 것이 익숙치 않아 학습을 더 해야 할 것 같다.

• user type을 나누는 부분에서 더 짜임새 있는? 기준이 뭔지, 현업에서는 어떤 기준으로 사용자를 나누는지 궁금하다!

• 고객을 분석하기 위해서는 어떤 부분을 더 봐야하는 지 아직 크게 감이 없는데, 다른 레퍼런스를 보는 것이 필요할 것 같다는 생각이 들었다