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

작성한 질문수

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

안녕하세요 강사님 코호트 쿼리 공부하다가 의문점이 생겨서 문의드립니다

해결된 질문

작성


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 "2023-08-03"
), first_week_and_diff AS (
  SELECT
    *,
    DATE_DIFF(event_week, first_week, WEEK) AS weeks_after_first_week
  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
    first_week,
    weeks_after_first_week,
    COUNT(DISTINCT user_pseudo_id) AS active_users
  FROM first_week_and_diff
  GROUP BY 
    first_week,
    weeks_after_first_week
)


SELECT
  *,
  ROUND(SAFE_DIVIDE(active_users, cohort_users), 2) AS retention_rate
FROM (
  SELECT
    first_week,
    weeks_after_first_week,
    active_users,
    FIRST_VALUE(active_users) OVER(PARTITION BY first_week ORDER BY weeks_after_first_week ASC) AS cohort_users
  FROM user_counts
)
ORDER BY first_week, weeks_after_first_week

수업때 사용했던 코드인데 제가 처음엔 지금 하고 있는 코호트 분석은 first_week(가입주) 마다 각자 시간이 흐르면서(기준은 week) 리텐션이 어떻게 바뀌는지를 보는 것 이라고 이해했었습니다 그래서 예를들어 첫 달 부터 확 떨어지면 이거 온보딩에 문제가 있는거 아닌가? 라는 문제정의를 하는 식의 생각을 할 수있다... 라고 이해하고 있었는데 다시 보니까
지금처럼 base에 날짜 조건을 필터링 하고 시작하면

min(event_date)를 걸어도 그게 실제 첫 가입일이 아닐 수 있는거 아닌가요? 예를들어 필터링 조건 이전인 2022년 7월에 가입을 한 사람이 2022년 10월에 다시 돌아왔다고 치면 이 경우 2022년 10월 가입 user로 집계되는거지 않나요? 그럼 본래 보려던 거랑 결이 달라지는게 아닌가 싶어서요

sql Google-Analytics firebase google-sheets bigquery

답변 2

카일스쿨

지식공유자

gab1004 님 안녕하세요!

오 네 맞습니다. 말씀하신 것처럼 데이터 기간이 중요합니다.

그래서 지금 같은 형태의 데이터에선 전체 데이터를 스캔해서 봐야 합니다. 데이터의 시작일 이후부터 다 봐야 정확한 데이터가 나오고, 그러지 않으면 말씀하신 것처럼 이슈가 생깁니다.

이런 문제를 해결하기 위해 현업에서는 여러 방법을 활용해요. 대표적인 세가지는 다음과 같아요.

유저의 가입일 데이터를 DB에 넣고, 그 DB 데이터를 JOIN해서 가입일로 설정
유저 로그 데이터에서 first_date를 확인해서 별도의 테이블에 저장(1번과 동일한데, 1번에서 가입일자랑 사용자가 첫 로그를 발생시킨 일자랑 다를 수도 있어서 이 방법을 쓰기도 합니다. 가입만 하고 추후에 쓰는 경우도 있어요)
모든 데이터를 활용해서 리텐션을 계산함 : 강의에서 진행한 방법이라고 보시면 됩니다. 저희 데이터는 8월 1일부터 있어요. 8월 1일 이후라고 걸어주면 됩니다. 강의에서는 일부만 보여드린 것이라고 생각해주시면 되어요

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

디테일하게 잘 생각해주셨네요 👍👍 그런 생각하는 과정이 데이터를 이렇게 보는게 맞나? 고민하게 되고 더 일을 잘하게 되더라구요. 계속 그런 생각을 해보셔요.

인프런 AI 인턴

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

gab1004님, 코호트 분석에서 발생할 수 있는 날짜 필터링과 관련된 중요한 질문을 해주셨네요. 코호트 분석은 특정 시점을 기준으로 사용자의 행동 패턴이나 유지율을 추적하여 분석하는 것이 핵심입니다.

질문 요약 및 이해

코드에서 사용된 필터링 조건 event_date BETWEEN "2022-08-01" AND "2023-08-03"이 문제가 될 수 있다는 점에 대해 질문하셨습니다. 말씀하신 대로, 이 필터링 조건은 특정 기간 동안의 데이터만 포함하기 때문에 해당 기간 이전에 가입한 사용자가 기간 내에 다시 돌아왔을 경우, 이 사용자는 최초 가입자가 아닌 것으로 잘못 집계될 우려가 있습니다.

코호트 분석과 날짜 필터링 문제

필터링 문제점:
- 현재 필터링된 데이터는 지정된 기간 내에 발생한 이벤트만 포함합니다. 따라서 ‘실제’ 첫 가입일이 필터링된 기간 이전일 수 있는 사용자는 그들이 처음 가입한 시점이 아닌 나중의 날짜로 분석되어 버립니다.
해결 방법:
- 진정한 ‘첫’ 가입일을 분석하기 위해서는 데이터 필터링 시 event_date를 가입 날짜가 아닌 실제 ‘첫 이벤트 발생일’로 설정하는 것이 중요합니다. 모든 사용자의 전체 이벤트 로그를 참조하여 가입일을 계산하는 전략을 추천합니다.
대안 방법:
- 전체 로그에 대한 첫 이벤트 날짜를 저장해두고, 해당 날짜를 기준으로 이벤트를 추적할 수 있습니다. 가령 ‘first_event_date’를 기준으로 분석하여 더욱 정확한 결과를 얻을 수 있습니다.

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

안녕하세요 강사님 코호트 쿼리 공부하다가 의문점이 생겨서 문의드립니다

질문 요약 및 이해

코호트 분석과 날짜 필터링 문제

관련 질문 및 해결책 설정한 날짜 조건이 잘못된 결괏값을 초래할 수 있다는 경우에 대한 해결책은 다음 링크에서 더 자세하게 논의됩니다: