SQL로 리텐션 분석하기: 쿼리 예시와 PA툴 비교

SQL 리텐션 분석이란?
SQL 리텐션 분석은 이벤트 로그 테이블에서 사용자의 첫 활동 시점으로 코호트를 만들고, 이후 기간별 재방문 사용자 수를 첫 코호트 크기로 나눠 잔존율을 계산하는 쿼리 기반 분석 방법이다.

가입자는 매주 늘어나는데 활성 사용자 그래프가 평평하다. 이럴 때 가장 먼저 봐야 할 건 리텐션 커브다. 문제는 커브를 그리는 방법이 하나가 아니라는 점이다. 같은 로그 테이블에서도 코호트 기준을 어떻게 잡느냐에 따라 4주차 리텐션이 19%로도, 68%로도 나온다.

리텐션 지표 자체의 정의와 커브 해석법은 믹스패널 리텐션 분석 가이드에서 이미 다뤘다. 여기서는 그 위에 얹는다. 실제 이벤트 테이블을 가정하고 SQL로 숫자를 직접 뽑아본 다음, 같은 결과를 프로덕트 애널리틱스(PA)툴에서 만들 때와 무엇이 달라지는지 비용까지 계산한다.

시나리오: 주간 1,000명이 가입하는 B2B SaaS 팀

가상의 국내 B2B SaaS 팀을 세운다. 아래 값은 계산 흐름을 보여주려고 잡은 가정치이며, 독자가 자기 서비스 숫자로 바꿔 넣을 수 있게 변수를 그대로 노출한다.

판정 이벤트를 로그인이 아니라 리포트 생성으로 잡은 이유가 있다. 로그인 기준 리텐션은 대체로 높게 나오고, 대시보드를 예쁘게 만들지만 개선 액션을 주지 못한다.

1단계: 코호트를 만드는 쿼리

사용자별 첫 이벤트 주를 구해 코호트 라벨을 붙인다. 여기서 MIN()을 쓰는 순서가 중요하다. 주 단위로 자른 뒤 최솟값을 구하면 타임존 경계에서 사용자가 두 코호트에 걸친다.

WITH first_seen AS (
  SELECT
    user_id,
    DATE_TRUNC(MIN(DATE(event_time, 'Asia/Seoul')), WEEK(MONDAY)) AS cohort_week
  FROM events
  WHERE event_name = 'signup'
  GROUP BY user_id
),
activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC(DATE(event_time, 'Asia/Seoul'), WEEK(MONDAY)) AS active_week
  FROM events
  WHERE event_name = 'report_created'
)

2단계: 재방문 주차를 붙이고 분모를 고정한다

초보자가 가장 많이 틀리는 지점이 분모다. 코호트 크기는 first_seen에서 한 번만 세서 고정해야 하고, 재방문 집계와는 따로 계산한 뒤 조인한다.

cohort_size AS (
  SELECT cohort_week, COUNT(DISTINCT user_id) AS cohort_users
  FROM first_seen
  GROUP BY cohort_week
),
retention AS (
  SELECT
    f.cohort_week,
    DATE_DIFF(a.active_week, f.cohort_week, WEEK) AS week_number,
    COUNT(DISTINCT a.user_id) AS retained_users
  FROM first_seen f
  JOIN activity a USING (user_id)
  GROUP BY 1, 2
)
SELECT
  r.cohort_week,
  r.week_number,
  c.cohort_users,
  r.retained_users,
  ROUND(r.retained_users * 100.0 / c.cohort_users, 1) AS retention_pct
FROM retention r
JOIN cohort_size c USING (cohort_week)
WHERE r.week_number BETWEEN 0 AND 4
ORDER BY 1, 2;

3단계: 숫자를 대입해본다

1월 첫째 주 코호트 1,000명에 위 쿼리를 돌렸다고 가정하면 이런 표가 나온다.

같은 로그, 같은 사용자다. 그런데 오른쪽 열만 보고 보고하면 "4주차 리텐션 90%"가 된다. 실제 코호트 잔존율은 19%다. W2 계산을 손으로 따라가면 차이가 분명하다. 260 ÷ 1,000 = 26.0%, 260 ÷ 380 = 68.4%. 분모를 무엇으로 두느냐가 전부다.

커브가 평평해지는 구간부터 읽는다

W3에서 W4로 갈 때 감소폭은 2.0%p다. W1에서 W2 구간의 12.0%p와 비교하면 6분의 1 수준이다. 이 팀의 이탈은 가입 후 2주 안에 몰려 있고, 그 구간을 넘긴 사용자는 남는다. 온보딩을 손대야 할 지점이 계산으로 나온다.

N-day 방식으로 바꾸면 숫자가 또 달라진다

캘린더 주 기준은 화요일에 가입한 사용자의 W0을 6일로 계산한다. 금요일 가입자는 3일이다. 관찰 창 길이가 사용자마다 다르니 요일 구성이 바뀌면 커브도 흔들린다. 가입일 기준 상대 일수로 바꾸면 이 왜곡이 사라진다.

DATE_DIFF(a.active_date, f.first_date, DAY) AS day_number
-- day_number = 7  : 7일차 정확히 그날 돌아온 사용자 (N-day)
-- day_number >= 7 : 7일 이후 언젠가 돌아온 사용자 (unbounded)

세 방식 중 무엇이 맞느냐는 질문에는 정답이 없다. 정해야 할 건 팀 전체가 같은 정의를 쓰는 것이고, SQL로 관리하면 그 정의가 쿼리 파일마다 조금씩 갈라진다.

SQL과 PA툴, 무엇이 실제로 다른가

2026년 8월 기준으로 두 접근을 비교하면 기능 유무보다 반복 비용에서 갈린다.

SQL이 약한 도구라는 뜻이 아니다. 사내 매출 테이블과 조인해 리텐션과 LTV를 같이 보는 작업은 SQL 쪽이 자유롭다. 다만 리텐션 분석의 본질은 한 번 그리고 끝나는 게 아니라, 커브가 왜 그렇게 생겼는지 물으며 세그먼트를 스무 번 쪼개보는 반복 작업이다. 그 스무 번을 누가 하느냐가 조직 속도를 결정한다.

케이스 시나리오: 요청 대기가 만드는 비용

아래는 실제 기업 사례가 아니라, 위 가정을 이어붙인 계산 예시다.

이 팀은 분석가 1명이 리텐션 관련 쿼리 요청을 월 12건 처리한다. 건당 작성과 검증에 90분, PM이 결과를 받기까지 리드타임은 평균 2.5일이다. 계산하면 분석가 시간은 월 18시간(12 × 1.5h), 연 216시간이 리텐션 쿼리에만 들어간다. PM 쪽에서 보면 가설 하나를 검증할 때마다 2.5일이 소진된다. 온보딩 개선안 4개를 순차 검증하면 10일이다.

여기서 PM이 직접 브레이크다운을 돌려 리드타임이 2.5일에서 10분으로 줄면, 검증 가능한 가설 수가 같은 기간 안에서 늘어난다. 도구를 바꿨다기보다 데이터를 보는 사람이 늘어난 결과다. 티맵 CDO는 공개 인터뷰에서 이 상태를 "자판기에서 버튼 누르듯 데이터가 나오는 환경"으로 표현했다. 분석가에게 쿼리를 요청하고 며칠 기다리던 구조가 바뀌는 지점이 바로 여기다.

도구를 검토할 때 기능 개수보다 먼저 볼 기준도 여기서 나온다. 도입 3개월 후 실제로 몇 명이 스스로 리텐션 리포트를 열어보고 있는지다. 기능이 많은 도구가 좋은 도구라는 등식은 성립하지 않는다. 핵심 기능을 많은 사람이 꾸준히 쓰는 구조가 더 중요하다.

더 깊게 보려면 프로덕트 애널리틱스란 무엇인가부터 짚고, 도구 선택 단계라면 사용자 분석 툴 비교(믹스패널·앰플리튜드·포스트호그)와 믹스패널 입문 가이드를 함께 보면 기준이 잡힌다.

더 읽어보기

• 프로덕트 애널리틱스란?
• 믹스패널 완벽 입문 가이드
• 사용자 분석 툴 비교 (믹스패널·앰플리튜드·포스트호그)

자주 묻는 질문

SQL로 뽑은 리텐션과 PA툴 숫자가 다르게 나오는 이유는?

대부분 정의 차이다. 판정 이벤트가 다른 경우, 캘린더 주와 N-day가 섞인 경우, 타임존이 UTC와 KST로 갈린 경우가 흔하다. 대조할 때는 코호트 크기(분모) 한 숫자부터 맞춘다. 분모가 같아지면 나머지 차이의 원인 범위가 좁혀진다.

GA4로 리텐션 분석을 하면 되지 않나?

GA4는 트래픽 소스와 광고 연동에 특화된 마케팅 도구다. 코호트 리포트 기능은 있지만, 제품 안에서 어떤 행동을 한 사용자가 남는지 추적하려면 벽에 부딪힌다. Exploration 리포트에서 이벤트 규모가 커지면 샘플링이 발생해 정밀 세그먼트 정확도가 떨어지고, 데이터 처리 지연이 하루 이상 걸리며, 로데이터는 BigQuery 연동 없이는 접근하기 어렵다. GA4로는 실질적으로 답하기 힘든 "왜 이탈했나"를 다루는 게 PA툴의 영역이다.

PA툴을 쓰면 SQL은 안 배워도 되나?

둘의 역할이 다르다. 일상적인 리텐션 모니터링과 세그먼트 탐색은 툴에서 끝내고, 사내 결제 테이블이나 CS 티켓 데이터와 결합하는 심화 분석은 SQL로 간다. 최근 PA툴은 Snowflake, BigQuery, Redshift와 연동되니 툴에서 발견한 코호트를 웨어하우스로 넘겨 파고드는 흐름이 자연스럽다. SQL은 여전히 데이터 직군의 기본기다.

정리하며

리텐션 쿼리 자체는 어렵지 않다. 어려운 건 정의를 팀 전체가 같게 유지하는 일, 그리고 스무 번의 세그먼트 질문을 며칠이 아니라 몇 분 안에 돌리는 일이다. 첫 쿼리는 SQL로 직접 써보길 권한다. 손으로 분모를 고정해보면 툴이 무엇을 대신해주는지 정확히 알게 된다. 그 다음 판단은 각 팀의 상황에 달렸다.

Nitrox 블로그에서 관련 가이드 더 보기