[SQL-Excel] SQL & Excel 활용해서 회원프로필, RFM 만들기.

 

 

[SQL-Excel] SQL & Excel 활용해서 회원프로필, RFM 만들기.

---

 

RFM 분석이란?

RFM은 Recency, Frequency, Monetary를 의미합니다.

 

Recency : 얼마나 최근에 구매했는가
Frequency : 얼마나 자주 구매했는가
Monetary : 얼마나 많은 금액을 지출했는가

 

즉, 사용자별로 얼마나 최근에, 얼마나 자주, 얼마나 많은 금액을 지출했는지에 따라 사용자들의 분포를 확인하거나 사용자 그룹(또는 등급)을 나누어 분류하는 분석 기법입니다.

아래와 같은 사실을 보여줍니다.

• 최근 구매가 많을수록 프로모션에 대한 고객의 반응이 높다.
• 구매 빈도가 높은 고객일수록 참여도와 만족도가 높다.
• 고액 구매자와 저액 구매자를 차별화한다.

 

---

#활용한 csv파일,

 

Customer.csv

0.11MB

Product.csv

0.00MB

Sales.csv

0.09MB

 

∇ 회원 프로필 추출 쿼리문.

USE ana_pratice;

/****************************************************************************/
/*******************************회원 프로파일 분석********************************/
/****************************************************************************/

/***************회원 프로파일 분석용 데이터 마트***************/

CREATE TABLE CUSTOMER_PROFILE AS
SELECT  A.*
		,DATE_FORMAT(JOIN_DATE, '%Y-%m') AS 가입년월

        ,2021 - YEAR(BIRTHDAY) + 1 AS 나이
        ,CASE WHEN 2021 - YEAR(BIRTHDAY) + 1 < 20 THEN '10대 이하'
              WHEN 2021 - YEAR(BIRTHDAY) + 1 < 30 THEN '20대'
              WHEN 2021 - YEAR(BIRTHDAY) + 1 < 40 THEN '30대'
              WHEN 2021 - YEAR(BIRTHDAY) + 1 < 50 THEN '40대'
              ELSE '50대 이상' END AS 연령대

		,CASE WHEN B.MEM_NO IS NOT NULL THEN '구매'
			  ELSE '미구매' END AS 구매여부
  FROM  CUSTOMER AS A
  LEFT
  JOIN  (
		SELECT  DISTINCT MEM_NO
          FROM  SALES
		)AS B
    ON  A.MEM_NO = B.MEM_NO;



SELECT * 
FROM CUSTOMER_PROFILE;


/* 1. 가입년월별 회원수 */
SELECT  가입년월
		,COUNT(MEM_NO) AS 회원수
  FROM  CUSTOMER_PROFILE
 GROUP
    BY  가입년월;

/* 2. 성별 평균 연령 / 성별 및 연령대별 회원수 */
SELECT  GENDER AS 성별
		,AVG(나이) AS 평균나이
  FROM  CUSTOMER_PROFILE
 GROUP
    BY  GENDER;

SELECT  GENDER AS 성별
		,연령대
		,COUNT(MEM_NO) AS 회원수
  FROM  CUSTOMER_PROFILE
 GROUP
    BY  GENDER
		,연령대
 ORDER
    BY  GENDER
		,연령대;   

/* 3. 성별 및 연령대별 회원수(+구매여부) */
SELECT  GENDER AS 성별
		,연령대
		,구매여부
        ,COUNT(MEM_NO) AS 회원수
  FROM  CUSTOMER_PROFILE
 GROUP
    BY  GENDER
		,연령대
        ,구매여부
 ORDER
    BY  구매여부
		,GENDER
		,연령대;

 

 

 

∇ RFM 쿼리문.

 

use ana_pratice;

select * 
from product;

/****************************************************************************/
/*********************************RFM 분석************************************/
/****************************************************************************/

/***************RFM 분석용 데이터 마트***************/
CREATE TABLE RFM AS
SELECT  A.*
		,B.구매금액
        ,B.구매횟수
  FROM  CUSTOMER AS A
  LEFT
  JOIN  (
		SELECT  A.MEM_NO
				,SUM(A.SALES_QTY * B.PRICE) AS 구매금액 /* Monetary: 구매 금액 */
				,COUNT(A.ORDER_NO) AS 구매횟수 /* Frequency: 구매 빈도 */
		  FROM  SALES AS A
		  LEFT
		  JOIN  PRODUCT AS B
			ON  A.PRODUCT_CODE = B.PRODUCT_CODE
		 WHERE  YEAR(A.ORDER_DATE) = '2020' /* Recency: 최근성 */
		 GROUP
			BY  A.MEM_NO
		)AS B
    ON  A.MEM_NO = B.MEM_NO;

/* 확인 */
SELECT  *
  FROM  RFM;


/* 1. RFM 세분화별 회원수 */

SELECT  *
		,CASE WHEN 구매금액 >  5000000 THEN 'VIP'
			  WHEN 구매금액 >  1000000 OR 구매횟수 > 3 THEN '우수회원'
			  WHEN 구매금액 >        0 THEN '일반회원'
			  ELSE '잠재회원' END AS 회원세분화
  FROM  RFM;

SELECT  회원세분화
		,COUNT(MEM_NO) AS 회원수
  FROM  (
		SELECT  *
				,CASE WHEN 구매금액 >  5000000 THEN 'VIP'
					  WHEN 구매금액 >  1000000 OR 구매횟수 > 3 THEN '우수회원'
					  WHEN 구매금액 >        0 THEN '일반회원'
					  ELSE '잠재회원' END AS 회원세분화
		  FROM  RFM
		)AS A
 GROUP
    BY  회원세분화
 ORDER
    BY  회원수 ASC;


/* 2. RFM 세분화별 매출액 */

SELECT  회원세분화
		,SUM(구매금액) AS 구매금액
  FROM  (
		SELECT  *
				,CASE WHEN 구매금액 >  5000000 THEN 'VIP'
					  WHEN 구매금액 >  1000000 OR 구매횟수 > 3 THEN '우수회원'
					  WHEN 구매금액 >        0 THEN '일반회원'
					  ELSE '잠재회원' END AS 회원세분화
		  FROM  RFM
		)AS A
 GROUP
    BY  회원세분화
 ORDER
    BY  구매금액 DESC;


/* 3. RFM 세분화별 인당 구매금액 */

SELECT  회원세분화
		,SUM(구매금액) / COUNT(MEM_NO) AS 인당_구매금액
  FROM  (
		SELECT  *
				,CASE WHEN 구매금액 >  5000000 THEN 'VIP'
					  WHEN 구매금액 >  1000000 OR 구매횟수 > 3 THEN '우수회원'
					  WHEN 구매금액 >        0 THEN '일반회원'
					  ELSE '잠재회원' END AS 회원세분화
		  FROM  RFM
		)AS A
 GROUP
    BY  회원세분화
 ORDER
    BY  구매금액 DESC;