[스타터스 TIL] 51일차.SQL 실전 트레이닝 (7) - RFM 분석, 재구매율, 이탈고객 분석, 백분위수, 최빈값

---

1. RFM 분석

- 얼마나 최근에 구매를 했는지

- 얼마나 빈번하게 구매를 했는지

- 얼마나 많은 금액을 지불을 했는지를 확인하기 위한 지표

 

1.1 RFM 집계

WITH cte_customers AS (
	SELECT c.customer_id, c.company_name
		 , o.order_id, o.order_date 
		 , od.product_id, od.unit_price, od.quantity, od.discount
		 , od.unit_price * od.quantity * (1-od.discount) AS sales
	FROM customers c, orders o , order_details od
	WHERE c.customer_id = o.customer_id
	  AND o.order_id = od.order_id)
-- 1) 기준일 설정
, cte_max_date AS (
	SELECT *
		 , max(order_date) over() AS 기준일
		 , max(order_date) over() - order_date AS diff
	FROM cte_customers)
-- 2) RFM 계산
SELECT customer_id
	 , min(diff) AS R
	 , count(DISTINCT order_id) AS F
	 , sum(sales) AS M
FROM cte_max_date
GROUP BY 1
ORDER BY 4 desc;

 

1.2 단계 정의

✔ 고려사항

- 몇 단계로 나눌 것인가?

- 단계별 기준을 어떻게 잡을 것인가?

 

-- 3) 단계 설정 및 스코어 지정
, cte_score AS (
	SELECT *
		 , CASE WHEN recency <= 6 THEN 5
		 		WHEN recency <= 15 THEN 4
		 		WHEN recency <= 30 THEN 3
		 		WHEN recency <= 70 THEN 2
		 		ELSE 1 
		 	END AS R
		 , CASE WHEN frequency >= 14 THEN 5
		 		WHEN frequency >= 10 THEN 4
		 		WHEN frequency >= 7 THEN 3
		 		WHEN frequency >= 5 THEN 2
		 		ELSE 1 
		 	END AS F
		 , CASE WHEN monetary >= 22000 THEN 5
		 		WHEN monetary >= 12000 THEN 4
		 		WHEN monetary >= 5500 THEN 3
		 		WHEN monetary >= 3000 THEN 2
		 		ELSE 1 
		 	END AS M		 
	FROM cte_rfm)
SELECT *
FROM cte_score;

 

1.3 총점 계산 및 고객 가치 산정

→ 가장 높은 점수를 받은 고객들을 핵심고객으로 산정하여 그에 따른 특별 관리를 진행

-- 4) 계산한 RFM 점수를 합산한 total_score 계산
, cte_total_score AS (
	SELECT customer_id
		 , R, F, M
		 , R+F+M AS total_score
	FROM cte_score
	ORDER BY total_score DESC)
SELECT *
FROM cte_total_score;

 

1.4 점수 별 고객 수

→ 점수 별 고객의 수를 파악하여 증가, 감소해야하는 점수대를 확인하고 관리 진행

-- 5) 점수 별 고객 수
, cte_score_cnt AS (
	SELECT total_score
		 , count(*) AS cnt
	FROM cte_total_score
	GROUP BY 1
	ORDER BY 1 DESC)
SELECT *
FROM cte_score_cnt;

 

1.5 과거 우수고객이었으나 최근 구매하지 않은 고객

→ F, M은 5점이나 R은 1,2,3,4점인 고객

→ 재방문 유도하기

-- 6) 과거 우수고객
, cte_f5m5 AS (
	SELECT *
	FROM cte_total_score
	WHERE R IN (1, 2, 3, 4) AND F = 5 AND M = 5)
SELECT *
FROM cte_f5m5;

 

1.6 고객 분류

→ R, F 두가지 특성을 사용하여 분류

-- 5) 고객분류 (rf)
, cte_r5f5 AS (
	SELECT 'r'||r AS r
		 , count(CASE WHEN f = 5 THEN 1 END) AS f5
		 , count(CASE WHEN f = 4 THEN 1 END) AS f4
		 , count(CASE WHEN f = 3 THEN 1 END) AS f3
		 , count(CASE WHEN f = 2 THEN 1 END) AS f2
		 , count(CASE WHEN f = 1 THEN 1 END) AS f1
	FROM cte_total_score
	GROUP BY 1
	ORDER BY 1 DESC)
SELECT *
FROM cte_r5f5;

 

1.7 핵심고객 구매이력 추출

total_score가 15점인 핵심고객의 구매이력추출

→ total_score를 구한 테이블 (cte_total_score)와 처음 만든 테이블 (cte_customers)을 조인하여 출력

-- 5) 첫번째 cte 테이블과 조인하여 구매이력 추출
SELECT *
FROM cte_customers c1
	INNER JOIN cte_total_score c2
		ON c1.customer_id = c2.customer_id
WHERE total_score = 15
ORDER BY 1;

---

2. 재구매율

2.1 연도별 재구매율

1) 고객, 구매년도 출력

WITH cte_customers AS (
	SELECT c.customer_id, c.company_name
		 , o.order_id, o.order_date 
		 , to_char(o.order_date, 'YYYY') AS YEAR
		 , to_char(o.order_date, 'MM') AS MONTH
		 , od.product_id, od.unit_price, od.quantity, od.discount
		 , od.unit_price * od.quantity * (1-od.discount) AS sales
	FROM customers c, orders o , order_details od
	WHERE c.customer_id = o.customer_id
	  AND o.order_id = od.order_id)
-- 1) 고객, 구매연도를 중복되지 않도록 불러오기
, cte_select AS (
	SELECT DISTINCT customer_id
		 , year
	FROM cte_customers
	ORDER BY 1)
SELECT *
FROM cte_select;

 

2) 다음연도와 매칭되도록 self join

다음 연도와 매칭되도록 join할 때 → ON 조건절을 활용하여 year+1 = year로 걸어야 함

💡 next_year에 NULL값이 나오려면 LEFT JOIN 해야 함!!!!!!

-- 2) 다음 연도와 매칭되도록 self join
, cte_self_join AS (
	SELECT c1.customer_id
		 , c1.YEAR
		 , c2.YEAR AS next_year
	FROM cte_select c1
		LEFT JOIN cte_select c2
			ON c1.customer_id = c2.customer_id 
			AND c1.YEAR::integer +1= c2.YEAR::integer)
SELECT *
FROM cte_self_join;

 

3) 재구매율 계산

count(컬럼명)은 null 값을 제외한 값을 카운트하기 때문에 count만을 활용하여 당해, 다음해 구매자수를 구할 수 있음

또한 numeric으로 형 변환을 해주지 않은채 재구매율을 구하게 된다면 정수로만 나오기 때문에 실수로 나오도록 하기 위해서는 형 변환을 해주어야 함

-- 3) 재구매율 계산
, cte_re_sales AS (
	SELECT YEAR
		 , count(year) AS 당해구매자수
		 , count(next_year) AS 다음해재구매자수
		 , round(count(next_year)/count(year)::NUMERIC*100, 2)::char(10) ||'%' AS 재구매율
	FROM cte_self_join
	GROUP BY YEAR)
SELECT *
FROM cte_re_sales;

 

2.2 월 별 재구매율

① 고객 구매월을 중복되지 않게 불러온다.

② 다음 월과 매칭되도록 self join

③ 월별 구매자 수 집계하여 재구매율 계산

 

WITH cte_customers AS (
	SELECT c.customer_id, c.company_name
		 , o.order_id, o.order_date 
		 , date_trunc('month', o.order_date) AS MONTH
		 , od.product_id, od.unit_price, od.quantity, od.discount
		 , od.unit_price * od.quantity * (1-od.discount) AS sales
	FROM customers c, orders o , order_details od
	WHERE c.customer_id = o.customer_id
	  AND o.order_id = od.order_id)
-- 1) 고객, 구매월을 중복되지 않도록 불러오기
, cte_select AS (
	SELECT DISTINCT customer_id
		 , month
	FROM cte_customers
	ORDER BY 1)
-- 2) 다음 월과 매칭되도록 self join
, cte_self_join AS (
	SELECT c1.customer_id
		 , c1.MONTH
		 , c2.MONTH AS next_month
	FROM cte_select c1
		LEFT JOIN cte_select c2
			ON c1.customer_id = c2.customer_id
			AND c1.MONTH + '1 mon' = c2.month)
-- 3) 월별 구매자 수 집계하여 재구매율 계산
, cte_month_re_sales AS (
	SELECT to_char(MONTH, 'YYYY-MM') AS month
		 , count(month) AS 당월구매자수
		 , count(NEXT_month) AS 익월구매자수
		 , round(count(NEXT_month) / count(month)::NUMERIC*100, 2)::char(10) || '%' AS 재구매율
	FROM cte_self_join
	GROUP BY 1)
SELECT *
FROM cte_month_re_sales
ORDER BY 1;

---

3. 이탈고객 분석

3.1 비활동 고객 전환 비율 

① 고객 별 최종 구매일 추출

② 경과일 계산

③ 이탈 고객 여부 (마지막 구매일 이후 90일 이상 경과한 고객)

④ 이탈률 계산

 

WITH cte_customers AS (
	SELECT c.customer_id, c.company_name
		 , o.order_id, o.order_date 
		 , od.product_id, od.unit_price, od.quantity, od.discount
		 , od.unit_price * od.quantity * (1-od.discount) AS sales
	FROM customers c, orders o , order_details od
	WHERE c.customer_id = o.customer_id
	  AND o.order_id = od.order_id)

 

① 고객 별 최종 구매일 추출

각 고객 별 order_date의 가장 마지막 날을 max 함수를 사용하여 구하기

→ max 함수를 사용하기 때문에 customer_id로 group by 해주어야 함

-- ① 고객 별 최종 구매일 추출
, cte_cust_max_date AS (
	SELECT customer_id
		 , max(order_date) AS 최종구매일
	FROM cte_customers
	GROUP BY 1)

 

② 경과일 계산

기준일인 전체 데이터에서 가장 마지막 날을 구하려면 max() over() 함수를 사용해야 함

기준일과 위에서 구한 최종구매일을 빼면 경과일이 됨

-- ② 경과일 계산
, cte_diff AS (
	SELECT *
		 , max(최종구매일) over() AS 기준일
		 , max(최종구매일) over() - 최종구매일 AS 경과일
	FROM cte_cust_max_date)

 

③ 이탈 고객 여부

경과일이 90일이 넘을 경우 1, 그렇지 않은 경우 0으로 이탈여부 컬럼을 새롭게 만듦

→ 이탈률을 구할 때 해당 컬럼의 데이터를 합산하기 위함

-- ③ 이탈 고객 여부 (마지막 구매일 이후 90일 이상 경과한 고객)
, cte_cust_90 AS (
	SELECT customer_id
		 , CASE WHEN 경과일 >= 90 THEN 1 ELSE 0 END AS 이탈여부
	FROM cte_diff)

 

④ 이탈률 계산

count(*)은 null 값을 포함한 전체 고객수를 계산함

위에서 만든 이탈여부 컬럼의 값을 다 더한 값이 이탈고객의 수가 됨

이를 활용하여 이탈률을 구할 수 있음

-- ④ 이탈률 계산
SELECT count(*) AS 전체고객수
	 , sum(이탈여부) AS 이탈고객수
	 , sum(이탈여부) / count(*)::NUMERIC * 100 AS 이탈률
FROM cte_cust_90;

---

4. 백분위수, 최빈값

✔ percentile_cont 함수 : 백분위수를 연속값으로 계산
   → 입력받은 수치값이 어느 두 값의 사이일 경우 두 값의 평균을 계산하여 리턴
✔ percentile_disc 함수: 백분위수를 이산값으로 반환
   → 입력받은 수치값이 어느 두 값의 사이일 경우 두 값중 작은 값을 리턴
✔ mode 함수: 최빈값 반환

 

4.1 백분위수 구하기

# 연속값 계산
percentile_cont(n) within group (order by  column)

# 이산값 반환
percentile_disc(n) within group (order by  column)

 

WITH numbers(n) AS (
	VALUES (1), (1), (2), (3), (4), (5))
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY n)
FROM numbers;

 

4.2 최빈값 구하기

mode() within group (order by  column)

 

WITH numbers(n) AS (
	VALUES (1), (1), (2), (3), (4), (5))
SELECT mode() WITHIN GROUP (ORDER BY n)
FROM numbers;

 

4.3 제품 가격 사분위수 구하기

- 제품 가격 : unit_price

- product 테이블

SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY unit_price) AS q1
	 , percentile_cont(0.5) WITHIN GROUP (ORDER BY unit_price) AS q2
	 , percentile_cont(0.75) WITHIN GROUP (ORDER BY unit_price) AS q3
FROM products;

---

5. SQL 함수

단일 행 함수 (스칼라 함수)	✓ 한 개의 행을 인자로 받아들여 한 개의 값으로 결과를 반환하는 함수 ✓ 문자함수, 숫자함수, 날짜함수, 형변환함수, NULL 관련함수
다중 행 함수 (그룹 함수)	✓ 다중 행을 인자로 받아들여 한 개의 값으로 결과를 반환하는 함수 ✓ 집계함수(전체에 레코드에 대한 집계, 소그룹에 대한 집계)

 

5.1 단일 행 함수

1) 문자 함수

 

2) 숫자 함수

 

5.2 다중 행 함수 (집계함수)

---

6. 과제

수업시간에 진행한 연도별 재구매율을 제품을 기준으로 데이터를 추출하는 쿼리를 짜는 것이 과제이다.

동일한 고객이 구매한 후 다음 해에 해당 제품을 재구매하였을 경우를 구해야 한다.

 

6.1 과제 안내 & 결과

 

6.2 쿼리 작성

1) 기본 테이블 생성

기본 테이블을 생성할 때, 필요한 컬럼들만 가져옴

→ 고객, 주문, 주문연도, 제품명

-- 1) 기본 테이블 생성
WITH cte_product AS (
	SELECT c.customer_id, c.company_name
		 , o.order_id, o.order_date
		 , date_trunc('year', o.order_date) AS YEAR
		 , p.product_name 
	FROM orders o
		INNER JOIN order_details od
			ON o.order_id = od.order_id
		INNER JOIN customers c
			ON o.customer_id = c.customer_id 
		INNER JOIN products p 
			ON od.product_id = p.product_id)

 

2) 다음 연도와 매칭되도록 self join

cte_product 테이블을 self join하여 기준 연도 이후 다음 연도에 주문한 데이터를 매칭함

JOIN 시 걸어야 할 조건은 총 3가지임

→ 각 테이블 별 customer_id 동일 / product_name 동일 / 기준연도 +1과 다음연도 동일

→ 제품 기준으로 재구매 계산을 해야하기 떄문에 같은 제품을 구매한 다음 연도를 매칭해주어야 함

-- 2) self join하여 next_year 컬럼 추가
, cte_self_join AS (
	SELECT DISTINCT c1.product_name
		 , c1.customer_id
		 , c1.YEAR
		 , c2.YEAR AS next_year
	FROM cte_product c1
		LEFT JOIN cte_product c2
			ON c1.customer_id = c2.customer_id
			AND c1.product_name = c2.product_name
			AND c1.YEAR + '1 year' = c2.YEAR
	ORDER BY 1, 2)

 

3) 제품의 연도 별 재구매율

product_name과 year를 group by 해주어 이에 맞는 구매자수, 재구매자수, 재구매율을 구함

→ 구매자수 : 기준 연도의 구매자수

→ 재구매자수 : 다음 연도의 구매자수

-- 3) 재구매율 계산
SELECT product_name
	 , to_char(YEAR, 'YYYY') AS year
	 , count(year) AS 구매자수
	 , count(next_year) AS 재구매자수
	 , round(count(next_year) / count(year)::NUMERIC * 100, 2)::varchar(10) ||'%' AS 재구매율
FROM cte_self_join
GROUP BY 1, 2
ORDER BY 1, 2;

 

✅ 데이터 출력

---

💡 회고

마지막 SQL 이론 강의 시간이었다.

내일부터는 미니 프로젝트가 진행이 된다. 오늘까지 배웠던 SQL 함수를 활용하여 내일부터는 다양한 관점으로 데이터를 추출하고 분석하면 된다.

아직까지는 다양한 관점에서 데이터를 바라보는 것이 조금은 어렵다ㅠㅠ

그래도 꾸준히 쿼리짜고 복습하다보면 다양한 관점에서 데이터를 추출할 수 있지 않을까 싶다.

 

우선은 수업시간에 배운 쿼리를 보지 않고 쿼리 짜기를 진행하고, 이후에는 시중에 있는 SQL 쿼리 테스트를 풀면서 다양한 함수에 익숙해지자!

그리고는 시중 빅데이터를 활용하여 요것조것 분석해보쟈 :)💪💪💪