SQL을 통한 간단한 RFM Segmentation
(데이터 리안 SQL 수업을 참고하여 작성)
데이터 요약 통계 제공 라이브러리가 SQL은 따로 있지않아서
r이랑 python이랑 다르게 직접 입력해줘야한다.
그래서 이제 코드를 통해 고객들을 분류해야하는데
RFM Segmentation 개념을 활용해보자!
R, F, M은 다음과 같다.
- Recency: 얼마나 최근에 구매했는지
- Frequency: 얼마나 자주 구매했는지
- Monetary: 얼마나 많은 금액을 구매했는지
Recency, Frequency, Monetary의 기준이 회사나 사업마다 전부 다르고
서비스 특성에 따라도 없어지거나 크게 달라질 수 있기에 기준을 명확하게 정의해야 한다.
그리고 복잡함을 줄여주고자 R, F, M으로 용어 통일하겠다.
R, F, M 수치로 고객을 분류하자면 다음과 같다.
1) R, F, M이 모두 크다면 : VIP
2) R은 작고 F, M이 모두 크다면 : 떠난 VIP
3) R만 크고, F, M이 모두 작다면 : 자주 보지만 사지는 않는 고객들
4) R, F, M이 모두 작다면 : 떠났지만 타격이 크지 않은 고객들
이제 실제로 SQL을 이용하여 RFM을 구해보자!
CASE문과 IF문을 활용하여야 하는데 CASE문과 IF문을 간단하게 소개하자면 다음과 같다.
|
1
2
3
4
5
|
CASE
WHEN (A조건) THEN (A조건이 참일 때 결과)
WHEN (B조건) THEN (B조건이 참일 때 결과)
THEN (위 조건들이 거짓일 경우)
END
|
cs |
|
1
|
IF(조건, 조건이 참일 때, 조건이 거짓일 때)
|
cs |
Solve SQL의 문제로 예시를 들어보자
고객 정보 테이블에서 R, F, M 을 표기하고자 할 때
최대한 기준을 간단하게 잡아보았다.
마지막 주문 날짜가 한달 이내 인 경우 1 (현재 날짜가 2021.01.01 이라 했을 때)
주문횟수가 3회 이상일 경우 1
지불 금액 합계가 500 이상일 경우 1
위의 기준을 앞서 설명한 CASE, IF를 활용해 SQL문으로 표현한다면 다음과 같다.
|
1
2
3
4
5
6
|
SELECT
고객 ID, 마지막주문일, 주문횟수, 지불금액,
CASE WHEN 마지막주문일 >= 20201201 THEN 1 ELSE 0 end as recency,
IF(주문횟수 >= 3, 1, 0) as frequency,
IF(지불금액 >= 500, 1, 0) as monetary
FROM 고객정보
|
cs |
실행 시킨다면 다음과 같은 결과를 산출 할 수 있을 것이다.
이제 이 결과를 바탕으로 고객들을 분류할 수 있다.
COUNT 함수로 고객 ID 수를 계산하고
GROUP BY를 통해 RFM을 묶어준다면
RFM 수치 별 고객 수를 보여주는 분류표를 만들 수 있다!
분류표는 따로 올리지 않고 마무리 하겠다.
일단 오늘은 이렇게 끝!