N day 리텐션 쿼리 관련 질문

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

3-7. 리텐션 SQL 쿼리 작성하기 : Weekly Retention

해결된 질문

작성

카일스쿨님 매번 친절한 답변 감사드립니다 정말 많이 배웁니다!!🙇
N day 리텐션 쿼리에 관한 질문이 3가지 있습니다.

1. 맨 처음에 base 테이블 만들 때 DISTINCT를 왜 써야하는 건지 궁금합니다.

(B방법 기준) first_date_and_diff 임시테이블을 만들 때 DISTINCT를 쓰는 것은, '동일 유저가 하루에 여러번 접속한 것을 중복 제거하기 위해' 사용하는 것으로 이해했습니다.
그런데 맨 처음에 base 테이블은 timestamp 컬럼이 있는데 어떻게 중복이 있을 수 있는 건지 헷갈립니다. 완전히 동일한 마이크로초에 여러번의 행동 로그가 기록될 수 있는 건지, 아니면 전산오류로 로그가 중복 기록이 될 수 있다는 건지,, 이해가 잘 안 됩니다😢

2. 강의에서 보여주신 결과 테이블(diff_of_day | user_cnt)은 교안 330p와 다른 것이라고 이해했는데, 제가 이해한 것이 맞는지 확인 부탁드립니다!

a) 강의 결과 테이블: 유저마다 시작일은 다를 수 있지만, 어쨌든 궁금한 건 각 유저들이 첫 접속 이후에 계속 쓰는지가 궁금한 것 → 가입일 코호트별로 그룹화하지 않아도 그자체로 의미가 있음
b) 교안 330p 테이블: 강의 결과에서 한 단계 더 나아가서, 가입일에 따라 리텐션 추이에 차이가 있는지 더 쪼개보는 것
c) 만약 교안 330p 테이블처럼 결과를 출력하고 싶으면, COUNT 집계할 때 GROUP BY와 SELECT에 first_date만 추가로 넣어주면 된다. (아래 쿼리 및 사진 참고)

-- a) 강의 결과
SELECT
  diff_of_day,
  COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM first_date_and_diff
GROUP BY diff_of_day
ORDER BY diff_of_day

-- c) 교안처럼 출력하고 싶을 경우
SELECT
  first_date,
  diff_of_day,
  COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM first_date_and_diff
GROUP BY first_date, diff_of_day
ORDER BY first_date, diff_of_day

3. Weekly 리텐션 파트에서 설명해주신 WEEK 함수는 어떻게 쓰는 걸까요?

아래 쿼리처럼 썼더니 WEEK 함수가 없다고 오류가 나던데, EXTRACT를 말씀하시려던 걸까요? EXTRACT는 제대로 나오는 거 같긴 합니다..!

SELECT
  user_pseudo_id, event_name, event_date,
  DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week,
  EXTRACT(WEEK FROM event_date) AS event_week2,
  WEEK(event_date) AS event_week3
FROM(
  SELECT
    DISTINCT -- event_timestamp 기반으로 중복 제거
      user_id,
      event_name,
      -- event_date, -- Firebase의 형태(ex. '20220813')와 다르니까, 아래처럼 직접 추출
      DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
      user_pseudo_id
  FROM advanced.app_logs
  WHERE
    event_date BETWEEN "2022-08-01" AND "2022-11-03")

항상 감사드립니다!! 완강까지 달려보겠습니다!

sql Google-Analytics firebase google-sheets bigquery 카카오공채-서비스

답변 2

카일스쿨

지식공유자

안녕하세요

1번. DISTINCT를 사용한 이유

(시작하기 전에) 제가 강의에서 사용한 쿼리가 많아서 듣다가 궁금한 내용에 대해서 쿼리를 붙여주셔도 좋을 것 같아요! 또는 몇 초에 나오는 쿼리라고 말씀해주시면 제가 확인하기 수월합니다(저도 모든 쿼리를 다 기억하는 것이 아니라서 확인하고 이 부분인가? 저 부분인가? 생각하게 되더라구요)
말씀하신 것처럼 timestamp 컬럼이 있어서 중복이 있을 확률은 적습니다. 그러나 클라이언트 개발에 따라 다른데 screen_view나 page_view 시리즈와 click이 동시에 발생할 수도 있습니다(약간의 딜레이는 있을 수 있지만 구현에 따라 다름)
- 그리고 저번 질문과 유사한데 지금 단일 테이블에선 중복이 없을 확률이 매우 높지만, 다른 테이블과 JOIN을 할 때 데이터가 많이 늘어날 수 있습니다. 그렇기 때문에 저는 DISTINCT를 항상 사용하면서 중복을 제거하려는 습관이 있다고 생각하시면 됩니다
- 또한 Google Analytics 4, Firebase는 저희가 로깅하지 않아도 발생하는 로그들이 존재합니다. 예를 들어 user_engagement 같은 이벤트가 있습니다. 이런 이벤트를 WHERE 조건에서 제외하는 것도 방법이고, 중복이 있더라도 제거하는 방법도 가능합니다
- 기본적으로 제가 DISTINCT를 쓰는 것은 더 장기적 관점의 습관이라고 보시면 됩니다. DISTINCT를 안써서 JOIN할 때 이슈가 생기는 경험이 많아 중복을 제거하는 것
- 그리고 회사에서 로깅의 이슈로 중복으로 로그가 생기는 경우도 있습니다(개발쪽의 이슈) 그럴 때 개발쪽에서 로그를 수정해달라고 할 수는 있지만, 이미 생긴 로그에 대해서는 삭제가 어려울 수 있습니다. 그런 경우에 데이터쪽에서 쿼리로 중복을 제거하곤 합니다
- 실무에서 JOIN 이슈 + 잘못된 데이터가 있을 경우 => 이를 대비하기 위해 DISTINCT를 쓴다라고 생각해주셔요

2번

강의에서 보여드린 diff_of_day, user_cnt는 리텐션 커브를 위한 집계 결과입니다. 330쪽의 쿼리는 "코호트 리텐션"의 형태입니다. 코호트 리텐션은 3-10 강의와 연결됩니다. 그러나 강의에서 보여드린 쿼리에서 first_date만 추가하면 됩니다. 리텐션 커브 -> 코호트 리텐션으로 데이터를 다른 관점으로 보고 있다고 생각하시면 될 것 같아요. 다른 것이 아니라 리텐션 커브를 뽑는 결과 데이터에서 조금만 바꾸면 코호트 리텐션까지 뽑을 수 있습니다(공유해주신 것처럼 first_date 추가)

3번

WEEK은 WEEK 단독으로 쓰이진 않고, EXTRACT나 DATE_TRUNC에서 사용할 수 있습니다. 강의에선 EXTRACT와 같이 쓰는 WEEK 의미로 말씀드린거라고 생각해주시면 됩니다! 엄밀히 말하면 강의에서 WEEK 함수라고 했는데 EXTRACT와 함께 쓰이는 WEEK이라고 말씀을 드려야 더 정확했을 것 같네요. 단독 WEEK이 아닌!

DATE_TRUNC(date, WEEK(MONDAY))
EXTRACT(WEEK FROM DATE '2025-10-09')

인프런 AI 인턴

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

쿠쿸키님, BigQuery에서의 N Day 리텐션 쿼리에 대한 질문 감사드립니다. 각 질문에 대해 이해를 돕고자 간략히 설명드리겠습니다.

1. DISTINCT의 필요성

DISTINCT 사용 이유: base 테이블에서 DISTINCT를 사용하는 이유는, 로깅 시스템에서 여러 이유로 인해 동일한 이벤트가 중복 기록될 수 있기 때문입니다. 특히, 동일한 user_id에 대해 event_timestamp까지 모두 동일한 경우가 발생할 수 있습니다. 이는 네트워크 지연이나 로깅 메커니즘의 특성 때문에 같은 이벤트가 여러 번 기록될 수 있기 때문입니다. 따라서 쿼리의 중복성을 제거하여 네트워크 리소스의 낭비를 줄이고 정확한 결과를 얻기 위해 DISTINCT를 사용합니다.

2. 교안과 강의 결과 테이블의 차이 이해

a) 강의 결과: 가입 후 몇 일 동안 서비스에 다시 돌아오는지를 보려고 할 때는 단순히 diff_of_day와 user_cnt로만 그룹화하는 것이 효과적입니다.
b) 교안 330p 표: 가입일을 기준으로 추가적으로 나누어 리텐션의 세부적인 차이를 분석할 때 사용됩니다. 특정 날짜의 가입 유저들이 이후 어떻게 행동하는지를 추적할 수 있습니다.
c) 교안처럼 출력하기: first_date로 그룹화하여 가입일별로 리텐션을 분석할 수 있습니다. 이를 통해 가입일에 따른 리텐션의 차이를 보다 쉽게 비교할 수 있습니다.

3. WEEK 함수 관련 문제

WEEK 함수 오류: BigQuery에서는 WEEK 함수가 제공되지 않기 때문에, 주차를 계산할 때는 주로 EXTRACT와 같은 함수를 사용하여 주차를 추출해야 합니다.
- 예를 들어, EXTRACT(WEEK FROM event_date)처럼 사용하여 원하는 결과를 얻을 수 있습니다. 또한, 주차를 명확히 하기 위해 DATE_TRUNC(event_date, WEEK(MONDAY))을 사용하여 주간의 시작을 명시적으로 정할 수 있습니다.

쿠쿸키님의 이해를 돕기 위해 관련된 몇 가지 게시물을 추천드립니다:

리텐션 SQL 작성하기:
- 이 게시물에서는 DISTINCT 사용의 중요성과 중복 제거의 필요성을 자세히 설명합니다.
3-7. Weekly 리텐션 쿼리 작성 및 질문:
- 리텐션 쿼리 구성에서의 함수 사용에 대한 유용한 정보를 제공합니다.

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

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