리텐션 분석 과제

BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)

해결된 질문

24.08.18 18:25 작성

리텐션 분석 과제 노션 문서에 정리해서 공유드립니다~

문서 링크

sql Google Analytics firebase google-sheets bigquery

답변 1

카일스쿨

지식공유자

2024. 08. 19. 15:15

붕어빵님 안녕하세요!

리텐션 분석 과제 잘 진행해주셨네요! 처음에 Weekly 리텐션을 보신 후에, Retain User 그룹으로 보신 것 잘하셨어요. Retain User 나누신 것이 제가 그린 그래프와 유사하게 나오네요.

Retain 유저 그룹

Retain 유저 그룹 그래프를 보면 말씀하신 것처럼 New가 갑자기 줄어들고 Resurrected가 늘어나는 추세를 보이지요. 이 부분은 약간 특이한 부분입니다. 부활한 유저가 많은 상황이지요. 신규 유저는 감소하고. 이런 부분을 해석해보시는 것을 추천드려요(1~2줄로 해석할 수도 있는데, 조금 더 생각해보고 가설을 만들어 보는 것이지요)

일단 신규 유저가 줄어들고 있지만, Current와 Resurrected가 늘어나는 추세입니다. 이럴 때 New를 Current로 전환을 시키면 더 많은 사용자들이 서비스를 사용하게 될거에요. Resurrected가 왜 높은가에 대해 생각해볼 수도 있는데, 회사라고 하면 어떤 Action을 해서 이게 올라가고 있을 가능성이 있습니다. 회사 위키나 다른 조직에서 무엇을 했나 확인해보면 왜 이런 결과가 나왔는지 알 수 있을거에요. 혹은 아무것도 안했는데 이런 현상이 발생했다면 이 부분에 대해 추가적으로 데이터를 확인해볼 필요가 있지요.

New => Current로 전환하면 해당 그래프에서 다시 New가 올라가서 사용자가 늘어날 것이다!라는 생각을 하고 어떻게 해야 전환될지를 생각하면서 데이터를 보면, Action Item을 생각하기 수월할거에요(제일 중요한 것은 우리가 뭘 해야할까에 대해 답을 찾는 것)

리텐션이 높은 유저 그룹

리텐션이 높은 유저 그룹에선 그래프의 Cohort가 10월 Current 유저인가요? 이 그래프를 처음 봤을 때 어떻게 해석해야 하지?라는 생각이 들었네요. 10월 활성 유저와 아닌 유저들의 리텐션 차이인지 10월 활성 유저와 다른 달 활성 유저의 차이인지가 헷갈렸습니다. 쿼리를 보면 10월 활성 유저 / 그 외로 처리하신 것 같네요. 비교 하는 집단을 그 외로 하신 이유는 무엇인가요?

코어 이벤트

코어 이벤트를 봐주셨는데, 퍼널도 같이 봐주셔도 좋을 것 같아요. 특정 주차에 갑자기 많이 주문을 했는지 혹은 연휴나 주말에 더 많이 구매했는지 등의 패턴도 존재할 수 있거든요. 이 부분에 대해서도 추가적으로 데이터 파악해보시면 결과 해석에 도움이 될거에요. 다 연결되는 부분이 존재해요(그리고 데이터를 만들 때 이런 부분을 고려해서 만들었어요)

쿼리 피드백

TIMESTAMP_ADD(TIMESTAMP_MICROS(event_timestamp), INTERVAL 540 MINUTE) AS event_timestamp : 이런 표현을 사용해주셨는데, 540을 추가하는 방법을 사용하면 데이터의 타임존이 달라질 때 이슈가 생길 수 있어요. 차라리 DATETIME으로 바꾸면서 Asia/Seoul을 명시해주는 것이 더 좋을 것 같아요(기본편 시간 데이터 다루기에 나오는 내용)
다음과 같이 변경하는 것을 추천드리고 싶어요

DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime

제가 작성했던 쿼리도 공유드려요. 약간의 차이가 존재해요. 데이터를 일요일 기준으로 하는지 월요일 기준으로 하는지 등. WEEK을 사용하셨는데, 저는 ISO_WEEK을 선호하곤 해요. New, Current 등을 구분할 때는 first와 prev activity week을 구해서 처리했어요. 이 기준은 근거에 따라 달라질 수 있어서, 말씀해주신 조건도 괜찮답니다(회사에서는 이 기준에 대해 조금 더 데이터를 보고 결정하면 되겠지요!)

WITH weekly_user_activity AS (
  SELECT
    user_pseudo_id,
    DATE_TRUNC(event_date, WEEK) AS event_week,
    MIN(DATE_TRUNC(event_date, WEEK)) OVER (PARTITION BY user_pseudo_id) AS first_activity_week, 
    LAG(DATE_TRUNC(event_date, WEEK)) OVER (PARTITION BY user_pseudo_id ORDER BY DATE_TRUNC(event_date, WEEK)) AS prev_activity_week
  FROM
    advanced.app_logs
  GROUP BY
    user_pseudo_id,
    event_date
), weekly_user_type AS (
  # 첫 사용이 첫 주차일 경우 new
  # 1주 전에도 사용한 경우 current
  # 첫 사용한지 2주가 지났고, 이전 사용과 지금 사용이 2주가 지났으면 resurrected
  # 그 외엔 이탈로 간주해서 dormant
  SELECT
    *,
    CASE
      WHEN event_week = first_activity_week THEN 'new_user'
      WHEN DATE_DIFF(event_week, prev_activity_week, WEEK) = 1 THEN 'current_user'
      WHEN DATE_DIFF(event_week, first_activity_week, WEEK) > 2 AND DATE_DIFF(event_week, prev_activity_week, WEEK) > 2 THEN 'resurrected_user'
      ELSE 'dormant_user'
  END AS user_type
  FROM weekly_user_activity
)

SELECT
  event_week,
  user_type,
  COUNT(DISTINCT user_pseudo_id) AS user_count
FROM
  weekly_user_type
GROUP BY
  event_week,
  user_type
ORDER BY
  event_week

다른 분의 글에 제가 남긴 의견도 보시면 좋을 것 같아 남겨보아요.

https://www.inflearn.com/community/questions/1356126/3-13-4%EB%B2%88-%EB%AC%B8%EC%A0%9C-%ED%92%80%EC%96%B4%EB%B3%B4%EC%95%98%EC%8A%B5%EB%8B%88%EB%8B%A4

제한된 데이터에서 이렇게 데이터를 추가적으로 보고, 가설을 만들고, 근거를 찾는 과정을 하는 것이 제가 문제를 출제한 의도에요. 이런 의도를 잘 충족하시면서 문제를 풀어주셨어요. 너무 고생하셨습니다!

붕어빵

질문자

2024. 08. 23. 21:00

상세하게 답변주셔서 감사합니다! 😃

Resurrected가 늘어나는 부분은 저도 흥미롭긴 했는데.. 추가로 살펴보고 공유드리겠습니다!
특정 코호트인 유저 vs 아닌 유저의 경우에는 쿼리를 여러 코호트랑 한 번에 비교하는 쿼리를 어떻게 작성해야할지 몰라서 약간 코호트 하나하나씩 봤던 것 같아요ㅠ 일단 아래 처럼 작성했었습니다..!

#과제3. 리텐션이 높은 유저 그룹 찾는 쿼리 작성하기

WITH base AS (
SELECT
  TIMESTAMP_ADD(TIMESTAMP_MICROS(event_timestamp), INTERVAL 540 MINUTE) AS event_timestamp, -- 한국 시간 기준으로 변경
  event_name,
  user_id,
  user_pseudo_id,
  platform
FROM `advanced.app_logs`
WHERE
  1=1
  AND event_name = "screen_view" -- screen_view 이벤트가 있으면 활동한 유저 
),

-- 1. 유저 그룹 쿼리
usergroup_base AS (
SELECT
  user_pseudo_id,
  event_week,
  LAG(event_week) OVER(PARTITION BY user_pseudo_id ORDER BY event_week ASC) AS prior_event_week,
  MIN(event_week) OVER(PARTITION BY user_pseudo_id ORDER BY event_week ASC) AS first_event_week
FROM (
  SELECT
    DISTINCT
    user_pseudo_id,
    event_name,
    DATE_TRUNC(PARSE_DATE('%Y-%W', FORMAT_TIMESTAMP('%Y-%W', event_timestamp)), WEEK(MONDAY)) AS event_week,
  FROM base
  )
),

usergroup AS (
SELECT
  user_pseudo_id,
  event_week,
  DATE_DIFF(event_week, prior_event_week, WEEK(MONDAY)) AS diff_of_prior_week, 
  DATE_DIFF(event_week, first_event_week, WEEK(MONDAY)) AS diff_of_first_week,
  CASE
    WHEN prior_event_week IS NULL THEN "New" -- 첫 접속 주차와 동일하 경우 New로 정의
    WHEN DATE_DIFF(event_week, prior_event_week, WEEK(MONDAY)) = 1 THEN "Current" -- 주 평균 1.6회 배달앱 사용하므로 일주일 내로 접속 시 Current 유저로 정의
    WHEN DATE_DIFF(event_week, prior_event_week, WEEK(MONDAY)) > 1 THEN "Resurrected" -- 나머지는 복귀 유저로 정의
    WHEN DATE_DIFF(event_week, prior_event_week, WEEK(MONDAY)) > 5 THEN "Dormant" -- 보통 배달 앱의 경우 한 달 단위로 멤버십 변경되기 때문에 5주(한 달) 초과하여 재접속한 경우 휴먼 유저로 정의
  END AS user_group
FROM usergroup_base
),

cohortusergroup AS (
SELECT
  DISTINCT
  user_pseudo_id
FROM usergroup
WHERE
  1=1
  AND user_group = "Resurrected"
  AND FORMAT_DATE("%Y-%m", event_week) = "2022-10"
),

-- 2. 위클리 리텐션 기본 쿼리
retain_base AS (
SELECT
  user_pseudo_id,
  event_name,
  event_date,
  MIN(event_date) OVER(PARTITION BY user_pseudo_id ORDER BY event_date ASC) AS first_date,
  cohort
FROM (
  SELECT
    DISTINCT
    DATE(b.event_timestamp) AS event_date,
    b.event_name,
    b.user_pseudo_id,
    CASE
      WHEN c.user_pseudo_id IS NULL THEN "Not"
      ELSE "Cohort"
    END AS cohort -- 코호트 그룹과 아닌 그룹과 비교할 값 추가하기
  FROM base AS b
  LEFT JOIN cohortusergroup AS c
    ON b.user_pseudo_id = c.user_pseudo_id
  )
),

weekly_retention AS (
SELECT
  diff_of_week,
  cohort,
  COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM (
SELECT
  user_pseudo_id,
  event_name,
  event_date,
  first_date,
  cohort,
  DATE_DIFF(event_date, first_date, WEEK(MONDAY)) AS diff_of_week
FROM retain_base
  )
GROUP BY ALL
)

SELECT
  diff_of_week,
  cohort,
  user_cnt,
  first_week_user_cnt,
  ROUND(SAFE_DIVIDE(user_cnt, first_week_user_cnt),3) AS retention_rate
FROM (
  SELECT
    diff_of_week,
    cohort,
    user_cnt,
    FIRST_VALUE(user_cnt) OVER(PARTITION BY cohort ORDER BY diff_of_week) AS first_week_user_cnt
  FROM weekly_retention
)
ORDER BY
  1, 2

코어 이벤트 관련해서도 시간날 때 보고 과제 업데이트해보겠습니다~!

인프런 커뮤니티 질문&답변