데이터 요건 및 SQL 마이닝 인프라: 자사몰 구매 테이블에서 유저별 고유 ID 기준 최신 구매일, 누적 구매 횟수, 총 결제 금액 데이터 파싱 쿼리

RFM을 뽑으려고 별도 설문이나 새 트래킹 도구가 필요하지 않습니다. 자사몰의 주문(order) 또는 결제(payment) 테이블에 유저별 고유 ID, 주문 일시, 결제 금액, 결제 상태(완료/취소/환불) 컬럼만 있으면 세 지표를 모두 계산할 수 있습니다. 회원 가입 시 발급되는 유저 고유 ID(user_id)가 기준 키가 되고, 이 ID로 주문 테이블을 GROUP BY 해서 고객 단위로 값을 집계하는 것이 전체 쿼리의 뼈대입니다.

가장 단순한 형태의 쿼리는 다음과 같은 구조를 가집니다.

SELECT
  user_id,
  MAX(order_date) AS last_purchase_date,
  COUNT(order_id) AS purchase_count,
  SUM(payment_amount) AS total_spent
FROM orders
WHERE order_status = 'completed'
GROUP BY user_id;

MAX(order_date)가 Recency 계산의 기초 데이터(최신 구매일)이고, COUNT(order_id)가 Frequency, SUM(payment_amount)가 Monetary의 원시값입니다. 여기서 Recency는 "최신 구매일" 자체가 아니라 "오늘 날짜 - 최신 구매일"로 환산한 경과 일수를 써야 값의 방향(작을수록 좋음)이 F·M과 자연스럽게 맞습니다. 이 환산은 DATEDIFF나 동등한 날짜 함수로 별도 컬럼을 만들어 처리합니다.

주문 테이블에는 결제 완료 건뿐 아니라 취소·환불·미결제 건도 함께 쌓여 있는 경우가 많습니다. WHERE절에서 결제 상태를 필터링하지 않으면 Monetary가 실제보다 부풀려지고, Frequency도 실제로 산 적 없는 주문까지 횟수에 잡힙니다. 특히 환불은 결제 자체는 완료됐다가 나중에 취소되는 경우가 많아서, 주문 시점 상태가 아니라 "현재 시점 최종 상태"를 기준으로 필터링해야 합니다. 부분 환불(금액 일부만 환불)이 있는 쇼핑몰이라면 결제금액에서 환불금액을 차감한 순결제액 컬럼을 별도로 관리하는 것이 정확도를 높입니다.

회원 탈퇴 후 재가입, 비회원 주문 후 회원 전환, SNS 로그인 연동 변경 등으로 같은 사람이 여러 개의 user_id로 흩어지는 경우가 실무에서 자주 생깁니다. 이 경우 RFM 집계가 한 사람을 여러 명으로 쪼개서 계산하게 되어 세그먼트 판정이 왜곡됩니다. 이메일이나 휴대폰 번호처럼 비교적 안정적인 식별자를 기준으로 ID를 병합하는 정책을 먼저 세워야 하며, 병합이 어려운 경우에는 "탈퇴 이력이 있는 계정은 별도 표시"처럼 최소한의 예외 처리 규칙이라도 정해두는 것이 낫습니다. 이 정책을 세우지 않고 바로 스코어링(3강)으로 넘어가면, 실제로는 활성 고객인데 이탈 등급으로 잘못 분류되는 사례가 반복적으로 생깁니다.

Recency 계산의 기준점(오늘 날짜)을 매번 쿼리 실행 시점으로 잡을지, 매일 특정 시각(예: 새벽 배치 시점)으로 고정할지도 미리 정해야 합니다. 기준일이 흔들리면 같은 고객의 R값이 하루 단위로 들쭉날쭉해지고, 등급 경계에 걸친 고객이 매일 다른 세그먼트로 왔다갔다하는 현상(경계 흔들림)이 생깁니다. 대부분의 실무 파이프라인은 자정 직후 배치로 전날까지의 데이터를 기준으로 하루 한 번만 RFM 값을 갱신하는 방식을 씁니다. 이 배치 주기는 8강에서 다루는 자동화 동기화 파이프라인과 그대로 이어집니다.

고객 수가 수만 명대까지는 위 쿼리를 그때그때 돌려도 크게 무리가 없지만, 주문 테이블이 수백만 건을 넘어가면 GROUP BY 집계 자체가 무거워집니다. 이 시점부터는 매번 원본 테이블을 통으로 스캔하지 않고, user_id·order_date에 인덱스를 걸거나 RFM 결과만 담는 별도 요약 테이블(예: user_rfm_daily)을 두고 매일 배치로 갱신하는 구조로 바꾸는 것이 일반적입니다. 요약 테이블을 따로 두면 CRM 툴이나 광고 플랫폼과 연동할 때도 원본 주문 테이블에 매번 무거운 쿼리를 날리지 않고 이 요약 테이블만 읽으면 되므로, 8강에서 다룰 자동화 동기화 파이프라인의 부하도 크게 줄어듭니다. 요약 테이블 설계는 다음 강의부터 이어지는 스코어링·세그먼트 분류 작업 전체의 기반이 되므로, 이 시점에 컬럼 구조를 한 번 더 점검해두는 편이 좋습니다.