[SQL] SQL을 활용한 이커머스 고객 세분화 분석 (RFM 분석)

2025.02.09 - [Python] - [Python] 이커머스 고객 세분화 분석 (RFM, 코호트 분석)

[Python] 이커머스 고객 세분화 분석 (RFM, 코호트 분석)

이전 포스팅에서 파이썬을 이용하여 RFM 분석을 하여 고객 세그맨테이션을 진행했었다.

이번 포스팅은 같은 데이터로 SQL을 이용하여 RFM 분석을 통해 고객 세그맨테이션을 진행해보겠다.

RFM 분석

고객의 구매 행동을 기반으로 충성도 높은 고객을 식별하고 마케팅 전략을 최적화하는 데 사용되는 데이터 분석 기법

 

RFM 분석의 세 가지 요소

• Recency (최신성): 고객이 마지막으로 구매한 날짜 (최근 구매일)
• Frequency (빈도): 고객이 특정 기간 동안 몇 번 구매했는지 (구매 횟수)
• Monetary (금액): 고객이 특정 기간 동안 총 얼마를 소비했는지 (구매 금액)

---

0. 스키마 생성 후 데이터 로드

use rfm;

select * from customer_info;
select * from discount_info;
select * from marketing_info;
select * from onlinesales_info;
select * from tax_info;

customer_info

discount_info

marketing_info

onlinesales_info

tax_info

---

1. 데이터 조인

할인율은 월별로 그룹화되어있지만,
다른 테이블과 조인할 칼럼이 제품 카테고리라서 같은 제품에 할인이 무작위로 들어갈 수 있다.
따라서 할인율은 마지막에 따로 칼럼을 만들 것임.

# discount_info를 제외한 테이블 모두 결합
create table df as (
	select *
    from customer_info as c
    join onlinesales_info as o using (고객ID)
    join marketing_info as m on (o.거래날짜 = m.날짜)
    join tax_info as t using (제품카테고리)
);

# 중복된 날짜 열 제거
alter table df drop column 날짜;

select * from df;

---

2. 할인율 칼럼 생성

update df
set 할인율 = if(쿠폰상태 = 'Used', 
				case
					when date_format(거래날짜, '%c') in (1, 4, 7, 10) then 0.1
					when date_format(거래날짜, '%c') in (2, 5, 8, 11) then 0.2
					else 0.3 end, 0);
select * from df;

---

3. R, F, M 변수 생성

1. Recency: 기준일 (최근 구매일)
2. Frequency: 방문 빈도
3. Monetary: 총 구매 금액

• 최근 거래 기준일 만들기
• 모든 고객의 거래 기록 중 가장 최근 구매일 선택
• 위 과정까지만 하면 R의 최소값이 0이 되는데, 이를 방지하기 위해 최소값을 1로 설정

create table rfm as (
select 
    고객ID,
    datediff((select date_add(max(거래날짜), interval 1 day) from df), max(거래날짜)) as Recency,  -- 기준일 - 최근 구매일
    count(*) as Frequency,  -- 총 구매 횟수
    round(sum(평균금액 * (1 - 할인율) * (1 - GST) * 수량 + 배송료), 5) as Monetary  -- 총 구매 금액
from df
group by 고객ID
order by 고객ID
);

select * from rfm;

---

4. 변수별 분위수 할당

• Recency는 높은 수치일수록 낮은 점수

create table rfm_quantile as (
	select 고객ID,
		   ntile(5) over (order by datediff((select date_add(max(거래날짜), interval 1 day) from df), max(거래날짜)) desc) as R,
           ntile(5) over (order by count(*)) as F,
           ntile(5) over (order by round(sum(평균금액 * (1 - 할인율) * (1 - GST) * 수량 + 배송료), 5)) as M
	from df
    group by 고객ID
    order by 고객ID
);
select * from rfm_quantile;

---

5. 고객 세그맨테이션

create table customer_type as (
	select 고객ID,
		   case
			   when R=5 and F=5 and M=5 then 'champion' -- 최고의 고객
               when R>=3 and F>=3 and M>=3 then 'Potential' -- 잠재 고객
               when R>=3 and F=1 and M<=2 then 'New' -- 신규 고객
               when R<=3 and F>=3 and M>=3 then 'Risk' -- 리스크 고객
               when R<=2 and F>=3 and M>=2 then 'Cant_lose' -- 놓치지 말아야 할 고객
               when R<=2 and F between 2 and 3 and M between 2 and 3 then 'Dormant' -- 휴면 고객
               when R<=2 and F<=2 and M<=2 then 'Churned' -- 이탈 고객
               else 'others' -- 기타
			end as customer_type
	from rfm_quantile
);
select * from customer_type;

• 충성 고객: 가장 최근에 자주 구매하고 지출이 많은 고객
• 잠재 고객: 평균 빈도를 보이고 최근 상당한 금액을 지출한 고객
• 신규 고객: 낮은 구매 빈도, 낮은 구매 금액이지만 최근에 구매한 고객
• 리스크 고객: 충성 고객이었지만 최근에는 방문 빈도가 낮은 고객
• 놓치지 말아야 할 고객: 자주 방문하여 적당히 구매했지만 최근에는 방문 빈도가 낮은 고객
• 휴면 고객: 적은 금액을 소비하고 방문 빈도가 낮으며 오래 전에 방문한 고객
• 이탈 고객: 세 가지 수치 모두 가장 낮은 고객
• 기타: 그 외의 고객
•  

---

6. 태블로 활용 시각화

세그먼트별 고객 수

잠재 고객의 비중이 타 고객 평균 대비 2배 이상 높았고, 위험 고객, 기타, 이탈 고객, 신규 고객, 휴면 고객, 충성 고객, 놓치지 말아야 할 고객 순으로 비중이 높게 나타났다.

---

데이터 제공: https://dacon.io/competitions/official/236222/data