Cộng đồng Hỏi & Đáp của Inflearn
câu hỏi đã được viết
안녕하세요 강사님 코호트 쿼리 공부하다가 의문점이 생겨서 문의드립니다
Đã giải quyết
Viết
·
31
0
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로 집계되는거지 않나요? 그럼 본래 보려던 거랑 결이 달라지는게 아닌가 싶어서요
Câu trả lời 2
1
gab1004 님 안녕하세요!
오 네 맞습니다. 말씀하신 것처럼 데이터 기간이 중요합니다.
그래서 지금 같은 형태의 데이터에선 전체 데이터를 스캔해서 봐야 합니다. 데이터의 시작일 이후부터 다 봐야 정확한 데이터가 나오고, 그러지 않으면 말씀하신 것처럼 이슈가 생깁니다.
이런 문제를 해결하기 위해 현업에서는 여러 방법을 활용해요. 대표적인 세가지는 다음과 같아요.
유저의 가입일 데이터를 DB에 넣고, 그 DB 데이터를 JOIN해서 가입일로 설정
유저 로그 데이터에서 first_date를 확인해서 별도의 테이블에 저장(1번과 동일한데, 1번에서 가입일자랑 사용자가 첫 로그를 발생시킨 일자랑 다를 수도 있어서 이 방법을 쓰기도 합니다. 가입만 하고 추후에 쓰는 경우도 있어요)
모든 데이터를 활용해서 리텐션을 계산함 : 강의에서 진행한 방법이라고 보시면 됩니다. 저희 데이터는 8월 1일부터 있어요. 8월 1일 이후라고 걸어주면 됩니다. 강의에서는 일부만 보여드린 것이라고 생각해주시면 되어요
강의에서 일정 부분만 잡아서 보여드린 것은 계산하는 과정을 보여드릴 때 모든 데이터를 쓰지 않아도 괜찮아서 쿼리 탐색 비용을 줄이려고 기간을 설정했다고 생각해주세요!
디테일하게 잘 생각해주셨네요 👍👍 그런 생각하는 과정이 데이터를 이렇게 보는게 맞나? 고민하게 되고 더 일을 잘하게 되더라구요. 계속 그런 생각을 해보셔요.
0
안녕하세요, 인프런 AI 인턴입니다.
gab1004님, 코호트 분석에서 발생할 수 있는 날짜 필터링과 관련된 중요한 질문을 해주셨네요. 코호트 분석은 특정 시점을 기준으로 사용자의 행동 패턴이나 유지율을 추적하여 분석하는 것이 핵심입니다.
질문 요약 및 이해
코드에서 사용된 필터링 조건 event_date BETWEEN "2022-08-01" AND "2023-08-03"이 문제가 될 수 있다는 점에 대해 질문하셨습니다. 말씀하신 대로, 이 필터링 조건은 특정 기간 동안의 데이터만 포함하기 때문에 해당 기간 이전에 가입한 사용자가 기간 내에 다시 돌아왔을 경우, 이 사용자는 최초 가입자가 아닌 것으로 잘못 집계될 우려가 있습니다.
코호트 분석과 날짜 필터링 문제
필터링 문제점:
- 현재 필터링된 데이터는 지정된 기간 내에 발생한 이벤트만 포함합니다. 따라서 ‘실제’ 첫 가입일이 필터링된 기간 이전일 수 있는 사용자는 그들이 처음 가입한 시점이 아닌 나중의 날짜로 분석되어 버립니다.
- 현재 필터링된 데이터는 지정된 기간 내에 발생한 이벤트만 포함합니다. 따라서 ‘실제’ 첫 가입일이 필터링된 기간 이전일 수 있는 사용자는 그들이 처음 가입한 시점이 아닌 나중의 날짜로 분석되어 버립니다.
해결 방법:
- 진정한 ‘첫’ 가입일을 분석하기 위해서는 데이터 필터링 시
event_date를 가입 날짜가 아닌 실제 ‘첫 이벤트 발생일’로 설정하는 것이 중요합니다. 모든 사용자의 전체 이벤트 로그를 참조하여 가입일을 계산하는 전략을 추천합니다.
- 진정한 ‘첫’ 가입일을 분석하기 위해서는 데이터 필터링 시
대안 방법:
- 전체 로그에 대한 첫 이벤트 날짜를 저장해두고, 해당 날짜를 기준으로 이벤트를 추적할 수 있습니다. 가령 ‘first_event_date’를 기준으로 분석하여 더욱 정확한 결과를 얻을 수 있습니다.
- 전체 로그에 대한 첫 이벤트 날짜를 저장해두고, 해당 날짜를 기준으로 이벤트를 추적할 수 있습니다. 가령 ‘first_event_date’를 기준으로 분석하여 더욱 정확한 결과를 얻을 수 있습니다.
관련 질문 및 해결책 설정한 날짜 조건이 잘못된 결괏값을 초래할 수 있다는 경우에 대한 해결책은 다음 링크에서 더 자세하게 논의됩니다:
-
- 리텐션 분석에서 발생할 수 있는 문제점과 해결책이 논의됩니다.
- 리텐션 분석에서 발생할 수 있는 문제점과 해결책이 논의됩니다.
3-7 리텐션 SQL 쿼리 작성하기 연습 (Weekly, Monthly)
- 리텐션 분석 시 발생할 수 있는 왜곡을 방지하는 방법들이 설명되어 있습니다.
- 리텐션 분석 시 발생할 수 있는 왜곡을 방지하는 방법들이 설명되어 있습니다.
저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.