신규 고객 4,334명이 이전 기록을 합치자 1,613명이 됐습니다: 윈도우 함수와 리텐션의 조용한 오류

온라인몰 거래 데이터로 월별 신규 고객의 다음 달 재구매율을 SQL로 계산했더니, 에러 없이 나온 숫자 곳곳이 틀려 있었습니다. PARTITION BY 하나를 빠뜨리자 고객 4,334명의 첫 구매 월이 모두 같아졌고, 데이터가 시작하는 첫 달의 ‘신규’ 884명 중 808명은 사실 기존 고객이었습니다. 관측할 수 있는 이전 기록까지 합쳐 다시 세면 같은 기간의 신규 고객은 1,613명이고, 첫 달 코호트의 재구매율은 36.5%가 아니라 9.2%입니다. 복잡한 쿼리일수록 에러 없이 틀립니다.

이전 글에서 만든 SQLite 데이터베이스(retail.db)를 그대로 씁니다. SQLite는 3.25 버전부터 윈도우 함수를 지원하고, 최근 Python에 포함된 버전이면 충분합니다.

윈도우 함수는 행을 남긴 채로 묶어서 계산합니다

GROUP BY는 여러 행을 묶어 한 행으로 줄입니다. 고객별 합계를 구하면 고객 수만큼의 행만 남습니다. 반면 윈도우 함수는 행을 그대로 두고, 각 행 옆에 묶음 단위의 계산 결과를 붙입니다.

-- GROUP BY: 고객마다 한 행
SELECT customer_id, MIN(ym) FROM p GROUP BY customer_id;

-- 윈도우 함수: 원래 행은 그대로, 각 행에 '그 고객의 첫 구매 월'이 붙음
SELECT customer_id, ym, MIN(ym) OVER (PARTITION BY customer_id) AS first_ym FROM p;

OVER (...) 안의 PARTITION BY customer_id가 “고객별로 따로 계산하라”는 뜻입니다. 순번을 매기는 ROW_NUMBER(), 누적 합계를 구하는 SUM() OVER (ORDER BY ...)도 같은 원리입니다. 이전 글에서 상품마다 가장 자주 쓰인 설명 하나를 고를 때 쓴 것도 ROW_NUMBER() OVER (PARTITION BY stock_code ...)였습니다.

리텐션은 분모와 분자를 먼저 적습니다

이 글에서 계산할 지표를 먼저 정의합니다.

• 코호트(cohort): 첫 구매 월이 같은 고객 묶음. 2011년 1월에 처음 산 고객은 ‘2011-01 코호트’입니다.
• 다음 달 재구매율: 분모는 그 코호트의 고객 수, 분자는 그중 바로 다음 달에 한 번 이상 산 고객 수입니다.
• 판매로 볼 줄: 고객 번호가 있고, 취소(C)·조정(A) 송장이 아니며, 수량과 단가가 양수인 상품 줄입니다.

이 세 줄이 없으면 같은 데이터로도 전혀 다른 숫자가 나옵니다. 아래에서 실제로 보여드립니다.

함정 1: PARTITION BY를 빠뜨리면 모두가 같은 코호트가 됩니다

고객-월 단위로 줄인 표(p, 한 행 = 한 고객이 그 달에 한 번 이상 샀다)에서 첫 구매 월을 구했습니다.

              query first_ym  customers
0   PARTITION BY 있음  2010-12        884
1   PARTITION BY 있음  2011-01        416
2   PARTITION BY 있음  2011-02        380
3   PARTITION BY 있음  2011-03        452
4   PARTITION BY 있음  2011-04        300
5   PARTITION BY 있음  2011-05        284
6   PARTITION BY 있음  2011-06        242
7   PARTITION BY 있음  2011-07        187
8   PARTITION BY 있음  2011-08        169
9   PARTITION BY 있음  2011-09        299
10  PARTITION BY 있음  2011-10        357
11  PARTITION BY 있음  2011-11        323
12  PARTITION BY 있음  2011-12         41
13  PARTITION BY 없음  2010-12       4334 

PARTITION BY customer_id를 지우고 MIN(ym) OVER ()로 쓰면, 표 전체를 하나의 묶음으로 보고 전체의 최솟값 2010-12를 모든 행에 붙입니다. 4,334명 전원이 2010년 12월 코호트가 됩니다. 쿼리는 멀쩡히 실행되고 결과 형태도 그럴듯해서, 코호트 크기를 한 번 세어 보지 않으면 알아채기 어렵습니다.

함정 2: 마지막 달은 아직 관측이 끝나지 않았습니다

PARTITION BY를 제대로 넣고 코호트별 다음 달 재구매율을 계산했습니다.

WITH p AS (SELECT DISTINCT customer_id, substr(invoice_date, 1, 7) AS ym
           FROM order_lines WHERE /* 판매로 볼 줄 */ ...),
first AS (SELECT customer_id, MIN(ym) AS cohort FROM p GROUP BY customer_id)
SELECT f.cohort,
       COUNT(DISTINCT f.customer_id) AS new_customers,
       COUNT(DISTINCT p.customer_id) AS bought_next_month,
       ROUND(100.0 * COUNT(DISTINCT p.customer_id) / COUNT(DISTINCT f.customer_id), 1) AS pct
FROM first f
LEFT JOIN p ON p.customer_id = f.customer_id
           AND p.ym = strftime('%Y-%m', f.cohort || '-01', '+1 month')
GROUP BY f.cohort ORDER BY f.cohort;

위 쿼리는 구조를 보여주려고 판매 조건을 줄여 쓴 것입니다. 실제 조건까지 넣은 실행 코드는 글 끝의 전체 코드에 있습니다.

     cohort  new_customers  bought_next_month   pct
0   2010-12            884                323  36.5
1   2011-01            416                 91  21.9
2   2011-02            380                 71  18.7
3   2011-03            452                 67  14.8
4   2011-04            300                 63  21.0
5   2011-05            284                 54  19.0
6   2011-06            242                 42  17.4
7   2011-07            187                 33  17.6
8   2011-08            169                 34  20.1
9   2011-09            299                 70  23.4
10  2011-10            357                 84  23.5
11  2011-11            323                 36  11.1
12  2011-12             41                  0   0.0 

표 아래쪽을 보면 2011-11 코호트는 11.1%, 2011-12 코호트는 0.0%입니다. 이 두 값은 실제 재구매율이 아닙니다. 고객들이 갑자기 떠난 걸까요? 데이터 기간을 확인하면 답이 나옵니다.

             first_row             last_row
0  2010-12-01 08:26:00  2011-12-09 12:50:00

데이터는 2011년 12월 9일 낮에 끝납니다. 11월 코호트의 ‘다음 달’은 9일 치만 관측됐고, 12월 코호트의 다음 달인 2012년 1월은 데이터에 아예 없습니다. 그런데 LEFT JOIN 뒤 COUNT는 짝이 없으면 0을 돌려주기 때문에, “관측할 수 없음”이 “재구매 0%”로 둔갑합니다. 이 표를 그래프로 그리면 마지막 두 달에 고객 이탈이 급증한 것처럼 보입니다.

다음 달이 온전히 관측되지 않은 코호트는 표에서 빼거나, 값 대신 ‘관측 중’으로 표시해야 합니다.

함정 3: 첫 달의 ‘신규’는 신규가 아닙니다

표 맨 위의 2010-12 코호트는 884명, 재구매율 36.5%로 가장 높습니다. 2011년 코호트 중 최고치인 23.5%보다 13.0%p 높습니다. 이 데이터가 2010년 12월 1일부터 시작하기 때문에, 그 달에 산 모든 고객이 ‘첫 구매’로 잡힌 것입니다. 같은 파일의 이전 시트(2009~2010년)와 대조했습니다.

   dec_2010_new  bought_before
0           884            808 

884명 중 808명(91%)은 2010년 12월 이전에 이미 산 적이 있는 고객입니다. 오래 거래해 온 단골이 신규로 섞였으니 재구매율이 높게 나온 겁니다. 이전 시트까지 합쳐, 관측할 수 있는 기록 안에서의 첫 구매 월로 다시 계산했습니다. 두 시트는 2010년 12월 1~9일이 겹치므로, 이전 시트는 12월 1일 전까지만 씁니다.

     cohort  new_customers  bought_next_month   pct
0   2010-12             76                  7   9.2
1   2011-01             72                 12  16.7
2   2011-02            125                 20  16.0
3   2011-03            179                 32  17.9
4   2011-04            106                 26  24.5
5   2011-05            111                 26  23.4
6   2011-06            108                 25  23.1
7   2011-07            101                 21  20.8
8   2011-08            107                 28  26.2
9   2011-09            188                 51  27.1
10  2011-10            221                 70  31.7
11  2011-11            191                 27  14.1
12  2011-12             28                  0   0.0 

차이가 큽니다.

코호트	한 시트만 사용	이전 기록까지 합침
2010-12	884명, 36.5%	76명, 9.2%
2011-01	416명, 21.9%	72명, 16.7%
2011-10	357명, 23.5%	221명, 31.7%
13개월 신규 고객 합계	4,334명	1,613명

(2011-11, 2011-12 코호트는 다음 달이 온전히 관측되지 않아 비교에서 뺐습니다.)

한 시트만 쓰면 기간 전체에서 신규 고객이 4,334명이지만, 2009년 12월부터의 기록으로 보면 이 기간에 처음 산 고객은 1,613명입니다. 2011년 1월 ‘신규’ 416명 중 344명도 2010년 12월에만 사지 않았을 뿐 그 전에 거래한 기존 고객이었습니다. 코호트 간 비교도 달라집니다. 한 시트만 보면 첫 달 코호트의 재구매율이 가장 높지만, 이전 기록까지 합치면 다음 달이 온전히 관측되는 2010-12~2011-10 코호트 중 첫 달이 9.2%로 가장 낮고 2011년 10월이 31.7%로 가장 높습니다.

다만 이것으로 경계 문제가 사라진 건 아닙니다. 합친 데이터도 2009년 12월에 시작하므로, 그 전에 산 고객은 여전히 알 수 없습니다. 1,613명도 ‘관측 가능한 기록 기준 신규’이지 절대적인 신규 고객 수는 아닙니다. 경계는 없앨 수 없고, 분석 기간보다 앞선 기록을 충분히 확보해 경계를 분석 기간 밖으로 밀어내는 것이 최선입니다.

함정 4: 분모를 바꾸면 다른 지표가 됩니다

“다음 달 재구매율”을 그 달에 산 고객 전체를 분모로 계산하면 이렇게 나옵니다.

        ym  active  bought_next_month   pct
0  2011-01     739                260  35.2
1  2011-06     990                365  36.9 

같은 2011년 1월인데 신규 고객 기준(한 시트) 21.9%, 전체 구매 고객 기준 35.2%입니다. 6월은 17.4%와 36.9%로 두 배 넘게 차이 납니다. 둘 다 맞는 계산이지만 서로 다른 지표입니다. 전자는 “새로 온 고객이 얼마나 남는가”, 후자는 “이번 달 고객 중 다음 달에도 오는 비율”입니다. 보고서에 ‘재구매율’이라고만 쓰면 읽는 사람은 어느 쪽인지 알 수 없습니다.

검증: 한 코호트를 손으로 세어 봅니다

복잡한 쿼리는 전체를 한 번에 믿지 말고 한 조각을 떼어 다른 방법으로 세어 봅니다. 2011-01 코호트를 pandas로 직접 계산해 SQL 결과와 대조했습니다.

pandas: 416 명 중 91 명 (21.9%) | SQL: [[416.0, 91.0, 21.9]]

416명 중 91명으로 정확히 같습니다. 다만 이 검증이 확인해 주는 건 “쿼리가 정의대로 계산했다”는 것까지입니다. pandas 쪽도 같은 시트만 봤기 때문에, 첫 달 경계 문제는 이 방법으로는 잡히지 않습니다. 그래서 **계산 검증(손으로 세기)과 정의 검증(경계 월, 기간 밖 기록 확인)**을 따로 해야 합니다.

리텐션 쿼리 점검표

점검	방법
코호트가 제대로 나뉘었나	코호트별 고객 수를 세어 한 달에 몰리지 않았는지 확인 (PARTITION BY 누락)
분모·분자가 무엇인가	쿼리 옆에 한 줄로 적기. 신규 고객 기준인지, 전체 구매 고객 기준인지
마지막 달	데이터 마지막 날짜를 확인하고, 다음 달이 온전히 관측되지 않은 코호트는 빼거나 표시
첫 달	데이터 시작 전 기록이 있는지 확인. 없으면 첫 달 코호트는 ‘신규’로 해석하지 않기
계산	한 코호트를 다른 도구로 직접 세어 대조

AI에게 리텐션 쿼리를 맡길 때

월별 신규 고객의 다음 달 재구매율을 구하는 SQL을 짜 줘. 코호트와 비교 월을 어떻게 정했는지, 분모와 분자가 각각 무엇인지 적어 주고, 데이터가 시작하는 달과 끝나는 달에서 값이 어떻게 왜곡될 수 있는지도 설명해 줘. 코호트별 고객 수를 확인하는 점검 쿼리도 같이 줘.

면접에서 나올 수 있는 질문

윈도우 함수와 GROUP BY의 차이는요? GROUP BY는 행을 묶어 줄이고, 윈도우 함수는 행을 그대로 둔 채 묶음 단위 계산 결과를 각 행에 붙입니다. 순위, 누적 합계, 고객별 첫 구매일처럼 행마다 맥락이 필요한 계산에 씁니다.

리텐션 그래프 마지막 달이 급락했습니다. 무엇부터 확인하겠습니까? 데이터 마지막 날짜입니다. 다음 달이 온전히 관측되지 않았으면 급락은 이탈이 아니라 관측 부족입니다. 이 데이터에서는 11월 코호트 11.1%, 12월 코호트 0.0%가 그 예입니다.

데이터 첫 달의 신규 고객 수가 유난히 많다면요? 데이터 시작 전에 이미 거래한 고객이 신규로 잡혔을 가능성이 큽니다. 이전 기록과 대조하거나, 첫 달 코호트는 분석에서 빼고 해석합니다. 이 데이터에서는 첫 달 ‘신규’ 884명 중 808명이 기존 고객이었습니다.

전체 코드

이전 글의 build_retail_db.py로 만든 retail.db가 있는 폴더에서 실행합니다.

import sqlite3
import pandas as pd

pd.set_option("display.width", 200)
con = sqlite3.connect("retail.db")
q = lambda sql: pd.read_sql(sql, con)

# 판매로 볼 줄: 고객 번호 있음, 취소(C)·조정(A) 아님, 수량·단가 양수, 상품 코드
SALE = """customer_id IS NOT NULL AND invoice NOT LIKE 'C%' AND invoice NOT LIKE 'A%'
  AND quantity > 0 AND price > 0 AND stock_code GLOB '[0-9][0-9][0-9][0-9][0-9]*'"""
# 고객-월 단위로 줄인 표 (한 행 = 한 고객이 그 달에 한 번 이상 샀다)
P = f"SELECT DISTINCT customer_id, substr(invoice_date, 1, 7) AS ym FROM order_lines WHERE {SALE}"

print("[1] 첫 구매 월: PARTITION BY 있음 vs 없음")
print(q(f"""
WITH p AS ({P})
SELECT 'PARTITION BY 있음' AS query, first_ym, COUNT(DISTINCT customer_id) AS customers
FROM (SELECT customer_id, MIN(ym) OVER (PARTITION BY customer_id) AS first_ym FROM p)
GROUP BY first_ym
UNION ALL
SELECT 'PARTITION BY 없음', first_ym, COUNT(DISTINCT customer_id)
FROM (SELECT customer_id, MIN(ym) OVER () AS first_ym FROM p)
GROUP BY first_ym"""), "\n")

COHORT = f"""
WITH p AS ({P}),
first AS (SELECT customer_id, MIN(ym) AS cohort FROM p GROUP BY customer_id)
SELECT f.cohort,
       COUNT(DISTINCT f.customer_id) AS new_customers,
       COUNT(DISTINCT p.customer_id) AS bought_next_month,
       ROUND(100.0 * COUNT(DISTINCT p.customer_id) / COUNT(DISTINCT f.customer_id), 1) AS pct
FROM first f
LEFT JOIN p ON p.customer_id = f.customer_id
           AND p.ym = strftime('%Y-%m', f.cohort || '-01', '+1 month')
GROUP BY f.cohort ORDER BY f.cohort"""
print("[2] 신규 고객의 다음 달 재구매율 (2010-2011 시트만 사용)")
cohort = q(COHORT)
print(cohort, "\n")

print("[3] 데이터 기간과 경계")
print(q("SELECT MIN(invoice_date) AS first_row, MAX(invoice_date) AS last_row FROM order_lines"))
print(q(f"""
SELECT COUNT(*) AS dec_2010_new, SUM(customer_id IN (
         SELECT customer_id FROM order_lines_prev
         WHERE {SALE} AND invoice_date < '2010-12-01')) AS bought_before
FROM (SELECT DISTINCT customer_id FROM order_lines
      WHERE {SALE} AND substr(invoice_date, 1, 7) = '2010-12')"""), "\n")

print("[4] 이전 시트까지 합쳐 관측 가능한 기록 안의 첫 구매 월로 다시 계산 (겹치는 12월 1~9일은 한 번만)")
print(q(f"""
WITH p AS (
  {P}
  UNION
  SELECT DISTINCT customer_id, substr(invoice_date, 1, 7) FROM order_lines_prev
  WHERE {SALE} AND invoice_date < '2010-12-01'
),
first AS (SELECT customer_id, MIN(ym) AS cohort FROM p GROUP BY customer_id)
SELECT f.cohort,
       COUNT(DISTINCT f.customer_id) AS new_customers,
       COUNT(DISTINCT p.customer_id) AS bought_next_month,
       ROUND(100.0 * COUNT(DISTINCT p.customer_id) / COUNT(DISTINCT f.customer_id), 1) AS pct
FROM first f
LEFT JOIN p ON p.customer_id = f.customer_id
           AND p.ym = strftime('%Y-%m', f.cohort || '-01', '+1 month')
WHERE f.cohort BETWEEN '2010-12' AND '2011-12'
GROUP BY f.cohort ORDER BY f.cohort"""), "\n")

print("[5] 분모를 바꾸면: 그 달에 산 고객 전체 중 다음 달에도 산 비율")
print(q(f"""
WITH p AS ({P})
SELECT a.ym,
       COUNT(DISTINCT a.customer_id) AS active,
       COUNT(DISTINCT b.customer_id) AS bought_next_month,
       ROUND(100.0 * COUNT(DISTINCT b.customer_id) / COUNT(DISTINCT a.customer_id), 1) AS pct
FROM p a LEFT JOIN p b
  ON b.customer_id = a.customer_id AND b.ym = strftime('%Y-%m', a.ym || '-01', '+1 month')
WHERE a.ym IN ('2011-01', '2011-06')
GROUP BY a.ym"""), "\n")

print("[6] 검증: 2011-01 코호트를 pandas로 직접 세기")
lines = q(f"SELECT customer_id, substr(invoice_date, 1, 7) AS ym FROM order_lines WHERE {SALE}")
first = lines.groupby("customer_id")["ym"].min()
jan = set(first[first == "2011-01"].index)
feb = set(lines.loc[lines["ym"] == "2011-02", "customer_id"])
print("pandas:", len(jan), "명 중", len(jan & feb), "명", f"({len(jan & feb) / len(jan):.1%})",
      "| SQL:", cohort.loc[cohort["cohort"] == "2011-01", ["new_customers", "bought_next_month", "pct"]].values.tolist())

참고 자료

• UCI Online Retail II: Chen, D. (2012). UCI Machine Learning Repository. https://doi.org/10.24432/C5CG6D (CC BY 4.0)
• SQLite: Window Functions: 윈도우 함수 공식 문서

실행 환경은 Python 3.9.6, pandas 2.3.3, SQLite 3.51.0입니다.