RFM 분석 방법, 엑셀 공식과 등급 6개 메시지
RFM 분석 방법을 엑셀 공식으로 따라 합니다. 최근성 · 빈도 · 금액 점수로 고객을 6개 등급으로 나누고 등급마다 보낼 메시지를 정리했습니다.

RFM 분석 방법은 고객마다 마지막 주문일(R · Recency) · 구매 횟수(F · Frequency) · 누적 결제 금액(M · Monetary)을 구해 각각 1~5점으로 매기고, 점수 조합으로 등급을 나눈 뒤 묶음마다 보낼 메시지를 하나씩 정하는 순서입니다. 주문 내역 파일 하나와 엑셀이면 시작할 수 있습니다. 아래에 엑셀 점수 공식 · 등급 6개의 조건과 메시지 예시 · 결과 기록표를 차례로 정리했습니다.
RFM 분석 방법, 어떤 데이터로 시작하나요?
필요한 열은 세 가지입니다 — 고객 ID · 주문일 · 결제 금액. 쇼핑몰 관리자나 판매 채널에서 주문 내역을 내려받아 이 세 열만 남기고, 취소 · 환불 건은 지웁니다. 한 사람이 여러 채널에서 주문했다면 전화번호처럼 겹치지 않는 값을 기준으로 고객 ID를 하나로 맞춥니다.
분석 기간은 주력 상품의 재주문 주기가 여러 번 들어가는 길이로 잡습니다. 예를 들어 한 달에 한 번 다시 주문하는 소모품이면 최근 12개월이 무난합니다. 분석 기준일은 시트 맨 위 한 곳에 적어 두고 모든 계산이 그 셀을 참조하게 합니다 — 다음 달에는 날짜만 바꾸면 점수가 새로 나옵니다.
그다음 피벗 테이블(여러 줄의 주문을 고객별로 모아 합계 · 개수 · 최댓값을 내는 엑셀 기능)을 만듭니다. 행에는 고객 ID, 값에는 주문일 최댓값 · 건수 · 결제 금액 합계를 넣으면 사람별 R · F · M 원재료가 나옵니다. 피벗 전에 원본 표를 정리하는 순서는 엑셀 표 만들기 글을 참고하세요.
엑셀로 RFM 점수는 어떻게 매기나요?
고객을 값 크기 순으로 줄 세워 다섯 구간으로 자르고 낮은 쪽부터 1~5점을 줍니다. 엑셀의 PERCENTRANK.INC 함수는 범위 안에서 값의 백분율 순위를 0~1 사이로 돌려줍니다. 여기에 5를 곱해 올림하고, 0이 나오는 맨 아래 값은 MAX로 1점에 맞춥니다. R은 경과일이 짧을수록 좋으니 6에서 빼서 뒤집습니다. 아래 공식은 B열에 경과일, C열에 주문 건수, D열에 결제 금액이 있다고 가정했습니다.
| 지표 | 원재료 | 점수 공식(2행 예시) | 높은 점수의 뜻 |
|---|---|---|---|
| R 최근성 | 경과일 = 기준일 − 마지막 주문일 | =6-MAX(1,ROUNDUP(PERCENTRANK.INC($B$2:$B$1000,B2)*5,0)) | 최근에 주문함 |
| F 빈도 | 분석 기간의 주문 건수 | =IFS(C2>=10,5,C2>=5,4,C2>=3,3,C2=2,2,TRUE,1) | 자주 주문함 |
| M 금액 | 분석 기간의 결제 금액 합계(원) | =MAX(1,ROUNDUP(PERCENTRANK.INC($D$2:$D$1000,D2)*5,0)) | 많이 지출함 |
F만 공식이 다릅니다 — 1회 주문자가 절반을 넘으면 백분율로 자를 때 그들이 모두 1점에 몰리고, 2회는 2점을 건너뛰어 3점이 되는 식으로 구간이 빕니다. 그래서 F는 횟수 구간을 직접 정합니다. 표의 1회 · 2회 · 3~4회 · 5~9회 · 10회 이상은 예시로 가정한 경계값입니다 — COUNTIF로 횟수별 인원을 먼저 세어 보고 구간마다 고르게 들어가도록 고치세요.
처음 정한 경계값은 따로 적어 두고 다음 달에도 그대로 씁니다. 경계가 매달 바뀌면 등급 변화의 원인이 고객 행동인지 기준 변경인지 가릴 수 없습니다.
점수 조합은 몇 개 등급으로 묶나요?
세 점수를 그대로 이으면 조합이 125개라 메시지를 정하기 어렵습니다. F와 M은 함께 움직이는 경우가 많습니다. 그래서 등급 6개는 R · F 두 점수로 나누고, M은 같은 등급 안에서 응대 수준을 정하는 보조 표시로 씁니다. 조건이 겹치면 표의 위쪽 등급을 먼저 적용합니다.
| 등급 | 조건 | 메시지 목적 | 메시지 예시 |
|---|---|---|---|
| 핵심 고객 | R 4~5 · F 4~5 | 관계 유지 | "새 색상이 공개되기 하루 전에 먼저 보실 수 있도록 링크를 드립니다" |
| 신규 고객 | R 4~5 · F 1 | 두 번째 주문 | "받으신 제품을 오래 쓰는 방법과 다음 주문용 쿠폰을 함께 보내 드립니다" |
| 성장 고객 | R 4~5 · F 2~3 | 주문 주기 맞추기 | "주문하신 지 4주가 되었습니다 — 같은 제품을 바로 다시 담을 수 있는 링크입니다" |
| 이탈 우려 고객 | R 2~3 · F 3~5 | 다시 찾게 하기 | "자주 찾으시던 제품이 새 구성으로 바뀌었습니다 — 이번 주까지 쓰는 혜택을 드립니다" |
| 관심 필요 고객 | R 2~3 · F 1~2 | 관심 되살리기 | "처음 주문하신 제품과 함께 많이 고르는 구성 3가지를 모았습니다" |
| 휴면 고객 | R 1 | 발송 횟수 줄이기 | 분기 1회 신제품 소식만 보내고, 수신 동의를 먼저 확인합니다 |
R 점수가 H열, F가 I열에 있다면 =IFS(AND(H2>=4,I2>=4),"핵심",AND(H2>=4,I2=1),"신규",H2>=4,"성장",AND(H2>=2,I2>=3),"이탈 우려",H2>=2,"관심 필요",TRUE,"휴면") 공식 하나로 등급이 붙습니다. M 점수가 5점인 고객은 등급과 별도로 표시해 두고, 문구에 지난 주문 제품 이름을 넣어 개별 응대에 가깝게 보냅니다.
등급마다 메시지는 어떤 기준으로 다르게 쓰나요?
- 등급마다 목적 하나, 문구 하나만 둡니다 — 핵심 등급 메시지에 신규용 쿠폰이 섞이면 무엇의 효과를 쟀는지 흐려집니다.
- 할인은 이탈 우려 · 관심 필요 등급에 먼저 씁니다. 핵심 고객은 이미 자주 주문하는 사람이라 먼저 보기 · 재입고 알림처럼 비용이 적은 혜택이 맞습니다.
- 보내는 날은 등급이 붙은 주에 정합니다. 주문 뒤 어느 시점에 어떤 연락이 닿는지 그려 보려면 고객 여정 지도의 구매 이후 단계를 함께 보세요.
- 발송 전에 수신 동의 열로 명단을 거릅니다. 정보통신망법 제50조는 전자적 전송매체로 영리목적의 광고성 정보를 보내려면 수신자의 명시적인 사전 동의를 받도록 정합니다. 수신거부 의사를 밝히거나 동의를 철회한 사람에게는 보낼 수 없습니다.
예외로, 주문 과정에서 연락처를 직접 받은 경우에는 해당 거래가 끝난 날부터 6개월 동안 같은 종류의 재화에 대한 광고성 정보를 사전 동의 없이 보낼 수 있습니다(정보통신망법 제50조제1항제1호 · 시행령 제61조제1항). 휴면 등급은 마지막 주문이 6개월을 넘긴 경우가 많으니 수신 동의가 있는 사람만 남기세요. 자기 상황에 어떻게 적용되는지는 전문가에게 확인하세요.
메시지를 보낸 뒤 무엇을 재고, 언제 다시 나누나요?
등급마다 10%는 발송하지 않는 비교군(조건은 같고 메시지만 받지 않은 집단)으로 남깁니다. 발송 14일 뒤 두 집단의 구매율을 견주면 메시지 덕에 늘어난 몫이 보입니다. 주문 주기가 긴 상품이면 기간을 30일로 늘립니다.
| 기록 항목 | 계산 | 예시(가정한 값) |
|---|---|---|
| 발송군 구매율 | 14일 안에 주문한 인원 ÷ 발송 인원 | 27명 ÷ 450명 = 6% |
| 비교군 구매율 | 14일 안에 주문한 인원 ÷ 비교군 인원 | 2명 ÷ 50명 = 4% |
| 늘어난 구매율 | 발송군 − 비교군 | 2%p |
| 등급 이동 | 지난달 신규 → 이번 달 성장으로 옮긴 인원 | 38명 |
RFM 분석 방법은 매달 되풀이할 때 쓸모가 커집니다. 점수와 등급은 매월 정한 날짜에, 처음 적어 둔 경계값으로 다시 계산합니다. 차이가 없는 등급은 다음 달에 문구 한 가지만 바꿔 다시 보냅니다. RFM은 구매 기록만 보므로 반품이 많은 고객이나 마진이 낮은 제품 비중은 따로 확인합니다.
주문 데이터가 여러 판매 채널에 흩어져 있거나 등급 설계부터 메시지 운영까지 한 번에 정해야 한다면 CRM 컨설팅(고객 관계 관리 설계)으로 외부와 역할을 나누기도 합니다. 이때 견적 범위와 비용은 채널 수 · 데이터 정리 정도 · 매달 재계산 운영 포함 여부로 정해집니다.
자주 묻는 질문
RFM 점수는 5점과 3점 중 무엇으로 나누나요?
인원이 다섯 구간에 고르게 나뉘면 5점을 쓰고, 고객 수가 적어 한 점수에 몇 명만 남으면 3점으로 줄이세요. 3점 척도에서는 공식의 5를 3으로 고치고 R 점수는 4에서 빼면 됩니다. 등급 조건도 3점 기준으로 다시 정하세요.
한 번만 구매한 고객이 대부분이면 RFM 분석을 어떻게 하나요?
F 점수를 백분율 순위 대신 1회 · 2회 · 3회 이상처럼 횟수 구간으로 직접 정하세요. 그래도 F가 거의 1점에 몰리면 R과 M 두 점수로 등급을 나누고, 첫 구매 고객의 재주문을 메시지 목표로 둡니다.
RFM 분석을 외부에 맡기면 비용은 무엇으로 정해지나요?
주문 데이터가 몇 개 판매 채널에 흩어져 있는지, 등급 설계에서 그칠지 문구 작성과 발송 일정까지 포함할지, 매달 다시 계산하는 운영을 넣을지로 범위와 비용이 정해집니다. 견적을 받기 전에 최근 12개월 거래 내역을 고객 ID · 주문일 · 결제 금액 · 환불 표시 · 수신 동의 열로 정리하고, 이름과 전화번호는 뺀 파일로 준비하면 범위를 잡기 쉽습니다.
등급별로 문자나 알림을 보낼 때 수신 동의가 꼭 필요한가요?
정보통신망법 제50조는 영리목적의 광고성 정보를 전자적 전송매체로 보내려면 수신자의 명시적인 사전 동의를 받도록 정합니다. 예외로 주문 과정에서 연락처를 직접 받았다면 해당 거래가 끝난 날부터 6개월 동안 같은 종류의 재화 광고는 동의 없이 보낼 수 있다고 시행령 제61조가 정합니다. 수신거부나 동의 철회를 한 사람은 명단에서 빼고, 자기 상황의 적용은 전문가에게 확인하세요.
오늘 할 일
쇼핑몰 관리자나 판매 채널에서 최근 12개월 주문 내역을 내려받아 고객 ID · 주문일 · 결제 금액 세 열만 남긴 시트를 만드세요. 취소 · 환불 건은 이 시트를 만들 때 함께 지워 두세요.



