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

작성한 질문수

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

리텐션 공부하다가 궁금한게 생겨 질문드립니다

해결된 질문

작성

-- Weekly 리텐션
-- 핵심 : event_date => event_week으로 변경하면 됨
-- 2024-06-30(일) => 2024년 26주차. 2024-06-24(월)도 같은 26주차
-- 데이터를 전처리(가공)하기 위해서 WEEK 함수를 사용할 수도 있고, DATE_TRUNC(일자, 자를 기준)
  -- DATE_TRUNC : 2024-06-30 => 2024-06-24
  -- WEEK : 26. 2024년 26주차! 주차 별 date를 직관적으로 알기 어렵다고 생각하는 편(개인 생각)
-- (깨알 지식) WEEK vs ISO_WEEK
  -- 주 번호를 계산할 때 사용할 수 있는 함수
  -- WEEK : 일요일이 주의 첫 날로 간주. 1월 1일이 속한 주가 1주차
  -- ISO_WEEK : 월요일이 주의 첫 날로 간주. ISO 8601 국제 표준에 따라 정의. 목요일이 속한 주를 기준으로 주 번호를 지정
    -- 목요일이 속하면 그 주의 4일 이상이 포함되기 때문
    -- 연도의 첫 목요일이 있는 주부터 1주차
  -- 2022년 1월 1일(토요일) WEEK : 2022년 1주차. ISO_WEEK 2021년 52주차. 
  -- 첫 목요일은 2022-01-06. 2022-01-03~2022-01-09가 2022년 1주차

WITH base AS (
  SELECT
    DISTINCT
      user_id,
      user_pseudo_id,
      event_name,
      DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
      DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime
  FROM advanced.app_logs
  WHERE
    event_date BETWEEN "2022-08-01" AND "2022-11-03"
), first_week_and_diff AS (
  SELECT
    *,
    -- DATE_DIFF(event_date, first_date, DAY) AS diff_of_day
    DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week
  FROM (
    SELECT
      # 일자별로 중복 제거
      DISTINCT 
        user_pseudo_id,
        -- DATE_TRUNC
        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_of_week,
  COUNT(DISTINCT user_pseudo_id) AS user_cnt
  FROM first_week_and_diff
  GROUP BY diff_of_week
)


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

-- SELECT
--   diff_of_week,
--   COUNT(DISTINCT user_pseudo_id) AS user_cnt
-- FROM first_week_and_diff
-- GROUP BY diff_of_week
-- ORDER BY diff_of_week

# Monthly 리텐션 쿼리 작성해보기!

지금 코드가 각 diff_week마다 남은 유저들을 전체 유저로 나눠서 아 몇주차쯤 되면 얼마나 남아있는지를 계산한 거 같은데 생각해보니까 예를들어 지금 기간이 11월 3일까진데 한 10월 중순에 들어온 유저 같은 경우엔 diff_week가 한 3 이상인 부분부턴 아예 데이터가 없는거 아닌가요? 데이터가 11월 3일까지 밖에 없으니까요
분모로 사용하는 diff_week=0인 지점에서의 값은 그런 경우들까지 전부 포함된거고 그걸 그대로 모든 retention week마다 분모로 사용하면 위 예시 같은 경우 10월 중순 user들은 한 3주차쯤엔 전부 이탈하는 것 처럼 집계되는거 아닌가요? 그 경우 diff_week가 커질수록 실제 리텐션 보다 과소평가 되는게 아닌가 하는 의문점이 들어 문의 남깁니다

sql Google-Analytics firebase google-sheets bigquery

답변 2

카일스쿨

지식공유자

안녕하세요!

어제 질문의 답변이랑 결이 유사해서 이어서 설명을 해볼게요.

https://inf.run/CcfjL

어제 답변에

강의에서 일정 부분만 잡아서 보여드린 것은 계산하는 과정을 보여드릴 때 모든 데이터를 쓰지 않아도 괜찮아서 쿼리 탐색 비용을 줄이려고 기간을 설정했다고 생각해주세요!

이렇게 말씀드렸는데, 회사 데이터를 다룰 때 처음부터 전체 데이터를 한방에 쿼리하는 것보단, 작은 데이터를 범위로 지정해서 쿼리 작성 후, 로직이 정말 잘 맞았다고 확신할 때 데이터 기간을 넓히는 것이 비용적으로도 좋고, 시간적으로도 좋습니다.

지금 강의에서

    event_date BETWEEN "2022-08-01" AND "2022-11-03"

이렇게 기간을 줄인 것은 여러분들의 BigQuery 비용을 줄이기 위함도 있지만, 제가 회사에서 일하면서 너무 자연스럽게 체득된 습관이라고 보시면 됩니다.

그리고 말씀해주신 부분은 gab1004님이 생각하신 것이 맞아요! 제가 조금 더 정리해서 말씀드릴게요

지금 코드가 각 diff_week마다 남은 유저들을 전체 유저로 나눠서 아 몇주차쯤 되면 얼마나 남아있는지를 계산한 거 같은데 생각해보니까 예를들어 지금 기간이 11월 3일까진데 한 10월 중순에 들어온 유저 같은 경우엔 diff_week가 한 3 이상인 부분부턴 아예 데이터가 없는거 아닌가요? 데이터가 11월 3일까지 밖에 없으니까요
분모로 사용하는 diff_week=0인 지점에서의 값은 그런 경우들까지 전부 포함된거고 그걸 그대로 모든 retention week마다 분모로 사용하면 위 예시 같은 경우 10월 중순 user들은 한 3주차쯤엔 전부 이탈하는 것 처럼 집계되는거 아닌가요? 그 경우 diff_week가 커질수록 실제 리텐션 보다 과소평가 되는게 아닌가 하는 의문점이 들어 문의 남깁니다

라고 해주셨는데, 코호트 리텐션 그래프가 삼각형으로 나오는 이유가 이런 상황 때문에 발생합니다. 그래서 어제 말씀드린 것처럼 데이터의 기간이 중요하고, 실제로 뽑을 때는 더 넓은 범위로 뽑습니다.

해당 쿼리는 비용을 줄이고 로직을 확인하는 과정에 있어서 기간을 줄인거라고 생각하시면 됩니다.

그리고 " 위 예시 같은 경우 10월 중순 user들은 한 3주차쯤엔 전부 이탈하는 것 처럼 집계되는거 아닌가요? "라고 해주셨는데, 우리가 집계로 구하는 것은 이탈이 아니라 "잔존"입니다. 이탈은 명시적인 이벤트가 없어서 이탈 수를 COUNT하는 것이 아니라 잔존을 COUNT하고 잔존이 아니면 이탈했다라고 가정한 것입니다. 그래서 과소평가가 되는 것이 아니라 데이터가 없어서 아예 계산이 되지 않습니다. 값이 없어요.

위 그래프에서 37일차가 지금 없지요?

이럴 때 이탈했다라고 생각하는 것이 아니라, "아직 그 기간이 도래하지 않아서 데이터가 없구나", "또는 데이터 기간을 WHERE 조건에 설정해서 없나?" 라고 생각합니다. 데이터가 없는 것이지 이탈이라고 볼 수는 없습니다(잔존이 있어야 이탈을 산출할 수 있겠지요)

즉, 데이터가 없다 = 이탈했다는 아닙니다. 데이터가 없으니 확인을 해보면 됩니다(기간이 도래하지 않았거나 WHERE 조건의 이슈가 있는지 확인하기)

이해 안되시거나 또 궁금하면 언제든 남겨주셔요!! 감사합니다!

인프런 AI 인턴

안녕하세요, 인프런 AI 인턴입니다.

gab1004님, 리텐션 분석에서 diff_week가 큰 경우 데이터 누락 문제를 지적해주셨군요. 리텐션 분석 시, 나중에 가입한 사용자는 분석 기간의 제한으로 인해 충분히 긴 관찰 주기를 채우지 못할 수 있습니다. 따라서, 실제 리텐션보다 낮게 집계되는 “과소평가”의 가능성이 존재합니다.

핵심 포인트:

기간 제한 문제:
- 분석 기준 기간이 11월 3일까지로 설정되어 있어서, 예를 들어 10월 중순에 가입한 사용자의 경우, 이후 주차 데이터가 부족할 수 있습니다.
- 이로 인해 해당 사용자는 관찰이 제한되며 분모 대비 분자의 비율이 작아져 리텐션 값이 낮아질 수 있습니다.
데이터 편향 해결 방법:
- ‘온보딩 날짜’에서의 최소 주차 기간을 기준으로 분석 기간의 각 사용자 데이터를 설정해야 합니다.
- Cohort Analysis 를 통해 각 주로 시작한 사용자 그룹을 추적하여 각 그룹의 리텐션을 별도로 계산할 수 있습니다. 이렇게 함으로써 여러 사용자 간의 리텐션 데이터 오류를 줄일 수 있습니다.
- 초기 가입 주나 달에 맞춘 리텐션 계산을 수행하여 분석 범위를 조정할 수 있습니다.

유사 질문 및 솔루션:

아래 링크들은 리텐션 분석에서 발생할 수 있는 문제점과 그 해결책에 대한 깊이 있는 설명을 제공합니다:

3-7 리텐션 SQL 쿼리 작성하기 연습 (Weekly, Monthly)
- 리텐션 쿼리의 정확성을 높이는 방법과 시간대 처리 주의사항을 제공합니다.
리텐션 연습 문제
- 다양한 환경에서 리텐션 분석을 올바르게 수행하기 위한 추가적인 팁을 제공합니다.

gab1004님의 문제 인식은 리텐션 측정의 정확성을 높이는 데 중요한 단계입니다. 이 점을 계속 염두에 두고 다양한 코호트 별로 데이터를 분석하면 보다 유의미한 결과를 얻을 수 있습니다.

저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다.
현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.

gab1004

작성한 질문수

전체 Q&A

질문하기