코호트 분석 SQL: 리텐션 표를 직접 뽑는 쿼리 패턴

코호트 분석 SQL이란?
코호트 분석 SQL은 사용자를 첫 행동 시점(가입월, 첫 구매일 등)으로 묶은 뒤, 그 집단이 이후 N번째 기간에 얼마나 다시 활동했는지를 쿼리 한 벌로 계산해 리텐션 표(코호트 × 기간 행렬)로 출력하는 분석 기법이다.

대시보드의 리텐션 숫자를 못 믿게 되는 순간

리텐션 30%. 이 숫자 하나만 보고 회의에 들어갔다가 "어떤 기준이죠?"라는 질문에 막힌 경험은 데이터 담당자라면 대부분 있다. 30일 안에 한 번이라도 들어온 비율인지, 정확히 30일째 되는 날 들어온 비율인지, 분모가 가입자인지 활성화 완료 사용자인지에 따라 같은 데이터에서 15%도 나오고 45%도 나온다.

코호트 표를 SQL로 직접 뽑는 이유가 여기 있다. 정의를 내 손으로 고정하기 위해서다. 쿼리에 적힌 WHERE event_name IN (...) 한 줄이 곧 "우리 팀이 말하는 리텐션"의 정의가 된다. 이 글은 개념 설명보다 실행 순서에 무게를 둔다. 6단계 쿼리 패턴과 각 단계에서 반드시 확인해야 할 체크 항목을 따라가면, 어떤 데이터베이스든 첫 리텐션 표가 나온다.

표를 만들기 전에 고정해야 할 3가지

코호트 표는 결국 이 세 가지 결정에 달려 있다. 이 셋을 문서로 못 박지 않고 쿼리부터 쓰면, 두 주 뒤에 다른 사람이 뽑은 표와 숫자가 안 맞는다.

1) 코호트 키: 무엇으로 묶을 것인가

• 획득 기반: 가입월, 첫 설치일, 유입 채널(paid/organic)
• 행동 기반: 첫 결제일, 첫 프로젝트 생성일, 온보딩 완료일
• 시간 기반: 주 단위, 월 단위, 특정 캠페인 기간

초기 팀이라면 가입월로 시작한다. 다만 제품 가치를 처음 경험한 시점(첫 결제, 첫 콘텐츠 생성)을 기준으로 잡으면 표의 신호가 훨씬 선명해진다. 가입만 하고 사라진 사용자가 분모를 부풀리지 않기 때문이다.

2) 기준 시점: period_index를 어떻게 셀 것인가

기준일에서 며칠, 몇 주, 몇 달이 지났는지를 정수로 환산한 값이 표의 열이 된다. 일 단위는 온보딩 진단에, 주 단위는 사용 습관 형성 진단에, 월 단위는 구독 비즈니스 진단에 맞는다.

3) 리텐션 정의: 셋 중 하나를 고른다

아래 쿼리는 구간 방식을 기준으로 쓴다. 실무에서 가장 널리 쓰이고, 사용 주기가 불규칙한 제품에서도 표가 안정적으로 나오기 때문이다.

6단계 쿼리 패턴

표준 SQL 기준으로 쓴다. BigQuery 함수 표기를 따랐고, PostgreSQL에서는 DATE_TRUNC(MONTH, x) 대신 DATE_TRUNC('month', x), DATE_DIFF 대신 AGE 또는 월 차 계산식으로 바꾼다.

1단계. 이벤트 테이블을 한 줄 = 한 행동으로 정규화

출발점은 user_id, event_name, event_time 세 컬럼이다. 로그가 여러 테이블로 흩어져 있다면 UNION ALL로 먼저 합친다.

• ☐ user_id가 로그인 전후로 바뀌지 않는지 확인(익명 ID와 로그인 ID 병합 로직 점검)
• ☐ event_time이 UTC인지 KST인지 확정하고 하나로 통일
• ☐ 테스트 계정, 내부 직원, 봇 트래픽 제외 조건 준비

2단계. 사용자마다 코호트 부여

WITH first_touch AS (
  SELECT
    user_id,
    DATE_TRUNC(MIN(DATE(event_time)), MONTH) AS cohort_month
  FROM events
  WHERE event_name = 'sign_up'
    AND user_id IS NOT NULL
  GROUP BY user_id
)

사용자 한 명당 반드시 한 행만 나와야 한다. 여기서 중복이 생기면 이후 모든 비율이 틀어진다.

• ☐ SELECT COUNT(*), COUNT(DISTINCT user_id) FROM first_touch 결과가 같은지 확인
• ☐ 재가입 사용자를 어떻게 처리할지 결정(첫 가입 유지 vs 최신 가입으로 갱신)

3단계. 활동 로그에 기간 인덱스 붙이기

activity AS (
  SELECT
    f.cohort_month,
    e.user_id,
    DATE_DIFF(
      DATE_TRUNC(DATE(e.event_time), MONTH),
      f.cohort_month,
      MONTH
    ) AS period_index
  FROM events e
  JOIN first_touch f USING (user_id)
  WHERE e.event_name IN ('app_open', 'order_completed')
)

WHERE 절의 이벤트 목록이 이 표의 리텐션 정의다. 앱 실행까지 활동으로 볼지, 실제 가치 행동만 볼지는 제품 성격에 달렸다. 커머스라면 order_completed 단독으로 두는 편이 표가 훨씬 정직해진다.

• ☐ 활동 이벤트 목록을 팀 위키에 기록했는가
• ☐ period_index가 음수인 행이 있는지 확인(있다면 타임존 또는 ID 병합 문제)

4단계. 분모를 별도 CTE로 고정

cohort_size AS (
  SELECT cohort_month, COUNT(DISTINCT user_id) AS base_users
  FROM first_touch
  GROUP BY cohort_month
)

분모를 활동 테이블에서 파생시키면 안 된다. 0개월 차에 활동하지 않은 사용자가 분모에서 조용히 빠지면서 리텐션이 실제보다 높게 나온다. 이 실수가 초보 코호트 쿼리에서 가장 자주 나온다.

5단계. 조인해서 비율 계산

SELECT
  a.cohort_month,
  a.period_index,
  c.base_users,
  COUNT(DISTINCT a.user_id) AS active_users,
  ROUND(COUNT(DISTINCT a.user_id) / c.base_users * 100, 1) AS retention_pct
FROM activity a
JOIN cohort_size c USING (cohort_month)
WHERE a.period_index >= 0
  AND DATE_ADD(a.cohort_month, INTERVAL a.period_index MONTH)
      < DATE_TRUNC(CURRENT_DATE(), MONTH)
GROUP BY a.cohort_month, a.period_index, c.base_users
ORDER BY a.cohort_month, a.period_index;

마지막 AND 조건이 미완성 기간을 잘라낸다. 이번 달이 아직 열흘밖에 안 지났는데 그 칸을 표에 넣으면, 최근 코호트가 갑자기 무너진 것처럼 보인다. 표를 보는 사람은 이런 조건이 걸려 있는지 모르니 쿼리에서 반드시 막아야 한다.

6단계. 사람이 읽을 수 있게 피벗

SELECT
  cohort_month,
  MAX(base_users) AS base_users,
  MAX(IF(period_index = 0, retention_pct, NULL)) AS m0,
  MAX(IF(period_index = 1, retention_pct, NULL)) AS m1,
  MAX(IF(period_index = 2, retention_pct, NULL)) AS m2,
  MAX(IF(period_index = 3, retention_pct, NULL)) AS m3,
  MAX(IF(period_index = 6, retention_pct, NULL)) AS m6
FROM cohort_retention
GROUP BY cohort_month
ORDER BY cohort_month;

일 단위 표가 필요하면 MONTH를 DAY로 바꾸고 period_index를 0, 1, 3, 7, 14, 30으로 뽑는다. 구조는 동일하다.

표를 잘못 읽게 만드는 함정 5가지

쿼리가 돌아간다고 표가 맞는 건 아니다. 아래 다섯 개는 발행 전에 매번 확인한다.

1. 미완성 코호트: 이번 달 가입자는 아직 1개월 차를 채우지 않았다. 5단계 조건으로 잘라내거나, 표에서 회색 처리한다.
2. 분모 오염: 프로모션으로 유입된 대량 가입이 특정 월 분모를 두 배로 키우면 그 행만 리텐션이 뚝 떨어진다. 채널을 쪼개 다시 본다.
3. 가로줄과 세로줄 혼동: 가로(한 코호트의 시간 변화)는 제품 경험의 문제, 세로(같은 기간 인덱스의 코호트 간 비교)는 개선 효과의 문제다. 개선을 판단하려면 세로로 읽는다.
4. 중복 카운트: COUNT(DISTINCT user_id)를 COUNT(*)로 쓰면 하루에 세 번 들어온 사용자가 세 명이 된다. 리텐션이 100%를 넘으면 이 문제다.
5. 표본이 너무 작은 행: 분모가 30명 미만인 코호트는 비율 변동이 커서 해석 가치가 낮다. base_users 컬럼을 항상 표에 붙여 두면 이 판단이 쉬워진다.

케이스스터디: 국내 B2B SaaS 팀의 3주 (가상 시나리오)

아래는 특정 기업의 실제 데이터가 아니라, 위 쿼리 패턴을 적용할 때 흔히 나타나는 흐름을 시나리오로 재구성한 것이다. 수치도 예시값이다.

월간 신규 가입이 꾸준히 늘던 팀이 있다. 전체 활성 사용자 수는 매달 우상향이었다. 코호트 표를 뽑자 그림이 달라졌다. 가입월 기준 3개월 차 잔존율이 1분기 코호트 22%, 2분기 코호트 18%, 3분기 코호트 14%로 계단처럼 내려가고 있었다. 총량이 늘어난 이유는 리텐션이 좋아져서가 아니라 신규 유입이 이탈을 겨우 메우고 있었기 때문이다.

이 팀은 코호트 키를 가입일에서 "첫 워크스페이스 초대 완료일"로 바꿔 표를 한 번 더 뽑았다. 그러자 초대를 완료한 사용자 집단의 3개월 차 잔존율은 41%로, 전체 평균의 두 배 이상이었다. 문제는 제품이 아니라 초대 단계까지 도달하는 비율에 있었다. 이후 이 팀은 온보딩 리소스를 그 한 단계에 몰아넣기로 했다.

여기서 중요한 건 결과 수치가 아니라 순서다. 총량 지표로는 보이지 않던 문제가 코호트 키를 행동 기준으로 바꾸는 순간 드러났다. 분석의 질은 코호트 정의를 여러 번 바꿔 볼 수 있느냐에서 갈린다.

SQL로 직접 뽑을 때와 도구로 볼 때의 손익분기

SQL 코호트 쿼리의 강점은 명확하다. 정의를 완전히 통제하고, 로데이터에 직접 닿고, 웨어하우스 안의 다른 테이블(결제, CS 티켓, 계약 정보)과 자유롭게 조인한다. 재현 가능한 정의 문서 역할도 한다.

비용도 명확하다. 코호트 키를 한 번 바꿀 때마다 쿼리를 수정하고, 검증하고, 다시 시각화해야 한다. 2026년 8월 기준으로도 이 반복 비용은 줄지 않았다. PM이 "채널별로 쪼개서 보고 싶은데요"라고 말하는 순간 작업 대기열이 하나 늘어난다.

판단 기준은 도구의 기능 목록이 아니라 질문의 빈도다. 코호트 정의가 분기에 한 번 바뀌는 조직이라면 SQL 쿼리를 잘 관리하는 편이 낫다. 반대로 매주 다른 세그먼트로 코호트를 다시 쪼개야 하는 조직이라면, 조직 전체의 의사결정 속도가 분석가 한 명의 대기열에 묶인다.

참고로 GA4도 코호트 탐색 보고서를 제공하지만, 행동 기반 코호트나 이벤트 세그먼트 조합은 구조상 다루기 어렵고, Exploration 리포트는 일정 규모를 넘으면 샘플링이 걸린다. BigQuery를 연동하지 않으면 로데이터에 닿지 못해 결국 위와 같은 쿼리를 쓸 수도 없다. 이 지점이 프로덕트 애널리틱스 도구가 답하는 영역이다. 드래그앤드롭으로 코호트 키와 활동 이벤트를 바꿔 가며 표를 다시 그리는 환경에서는, 마케터나 기획자가 직접 세 번째 가설까지 검증하고 온다.

도구 선택 기준을 더 정리하고 싶다면 프로덕트 애널리틱스란? 개념부터 도입 기준까지를 먼저 읽고, 실제 코호트 화면이 어떻게 동작하는지는 믹스패널 완벽 입문 가이드에서 확인할 수 있다. 도구별 차이를 나란히 놓고 비교하려면 사용자 분석 툴 비교가 참고가 된다.

전체 체크리스트 요약

코호트 분석 SQL 실행 체크리스트

• ☐ 코호트 키 확정 (가입일 / 첫 결제일 / 채널)
• ☐ 기간 단위 확정 (일 / 주 / 월)
• ☐ 리텐션 유형 확정 (N-day / unbounded / 구간)
• ☐ 활동 이벤트 목록 확정 후 문서화
• ☐ 타임존 통일, 테스트 계정 제외
• ☐ first_touch 결과가 사용자당 1행인지 검증
• ☐ 분모를 활동 테이블이 아닌 코호트 테이블에서 산출
• ☐ COUNT(DISTINCT user_id) 사용 확인
• ☐ period_index 음수 행 0건 확인
• ☐ 미완성 기간 필터링 적용
• ☐ 표에 base_users(분모) 컬럼 노출
• ☐ 분모 30명 미만 행은 해석에서 제외 표시
• ☐ 세로 읽기(코호트 간 비교)로 개선 효과 판단
• ☐ 쿼리와 정의를 팀 위키에 버전 관리

더 읽어보기

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

자주 묻는 질문

Q1. 리텐션이 100%를 넘게 나옵니다. 어디가 잘못된 건가요?

대부분 두 가지 중 하나다. 활동 사용자 집계에 DISTINCT가 빠져 한 사람의 여러 이벤트가 중복 카운트되는 경우, 또는 분모를 코호트 테이블이 아니라 활동 테이블에서 뽑아 일부 사용자가 분모에서 누락된 경우다. 4단계처럼 분모를 독립 CTE로 분리하면 두 문제 모두 막힌다.

Q2. 주 단위 코호트와 월 단위 코호트 중 무엇으로 시작해야 하나요?

제품의 자연스러운 사용 주기를 따른다. 사용자가 주 1회 이상 쓸 법한 제품은 주 단위로, 월 구독 형태이거나 사용 간격이 긴 제품은 월 단위로 잡는다. 판단이 어렵다면 주 단위와 월 단위를 한 번씩 뽑아 이탈이 가장 선명하게 꺾이는 구간이 보이는 쪽을 고른다.

Q3. GA4로도 코호트 분석이 되는데 굳이 SQL을 써야 하나요?

GA4의 코호트 탐색은 가입 시점 같은 표준 기준에는 답하지만, "첫 결제를 한 사용자만" 또는 "특정 기능을 3회 이상 쓴 사용자만" 같은 행동 기반 코호트로 넘어가면 구조상 어려워진다. 리포트 반영에도 시간이 걸리고, BigQuery 연동 없이는 로데이터에 접근할 수 없다. 정의를 직접 통제해야 하는 질문이라면 SQL이 답하고, 그 질문을 매주 다시 던져야 하는 상황이라면 프로덕트 애널리틱스 도구가 답한다.

마무리

코호트 표를 처음 뽑는 데 필요한 건 화려한 쿼리가 아니다. 코호트 키, 기간 단위, 활동 이벤트 세 개를 팀이 합의해서 문서로 남기는 일이 절반이고, 나머지 절반은 분모를 지키는 일이다. 위 6단계를 그대로 따라 첫 표를 만든 다음, 코호트 키를 행동 기준으로 한 번 바꿔 보길 권한다. 대부분의 팀은 그 두 번째 표에서 진짜 문제를 발견한다.

그다음 질문은 자연스럽게 이어진다. 이 표를 누가, 얼마나 자주 다시 뽑을 수 있는가. 분석가에게 요청하고 며칠을 기다리는 구조인지, PM이 직접 조건을 바꿔 보는 구조인지에 따라 3개월 뒤 조직의 학습 속도가 아예 달라진다.

리텐션과 코호트를 더 깊게 파고들고 싶다면 Nitrox 블로그에서 관련 가이드 더 보기.