11주 2일차 TIL 정리

11주 2일차에는 SQL을 활용한 RFM 분석과 재구매율 분석, 이탈율 분석을 진행했다.

---

※ RFM 분석 ※

---

1. RFM분석

- RFM분석은 11주 1일차에 진행했던 것과 동일한 내용으로, 최근 며칠 안에 재구매를 했으며, 총 몇 번 구매했고, 구매액수는 얼마나 되는지의 3개 지표를 활용하여 분석하는 것이다.

 

R-F, R-M, F-M으로도 분석 가능하며,

R, F, M을 점수화해 세 지표를 모두 활용할 수도 있다.

/* RFM 분석 */
WITH cte_customers as(
SELECT o.order_id , o.order_date , od.unit_price, od.quantity , od.discount
     , od.unit_price * od.quantity * (1-od.discount) AS sales
     , to_char(o.order_date,'YYYY') AS YEAR
     , to_char(o.order_date,'Q') AS quarter
     , to_char(o.order_date,'MM') AS MONTH
     , to_char(o.order_date,'dd') AS DAY
     , date_part('dow',o.order_date) AS dow 
     , c.customer_id, c.company_name , c.city , c.country
     , c.contact_title ,p.product_name , p.product_id
     , ct.category_name, ct.category_id
FROM customers c, orders o , order_details od , products p , categories ct
WHERE c.customer_id = o.customer_id AND o.order_id = od.order_id 
  AND od.product_id = p.product_id AND p.category_id = ct.category_id 
)
-- RFM집계
, cte_rfm AS (
SELECT company_name, customer_id
     , COALESCE (max(max(order_date)) over() - max(order_date), 0) AS recency
     , COALESCE (count(DISTINCT order_id), 0) AS frequency
     , COALESCE (round(sum(sales)), 0) AS monetary
FROM cte_customers
GROUP BY company_name, customer_id 
ORDER BY 1 DESC
)
-- RFM의 구간을 ntile로 나누기
,
cte_ntile as(
SELECT customer_id, recency, ntile(5) OVER (ORDER BY recency) AS R
     , frequency, ntile(5) OVER (ORDER BY frequency) AS F
     , monetary, ntile(5) OVER (ORDER BY monetary) AS M
FROM cte_rfm
)
-- ntile값과 유사값으로 case를 통해 구간 설정(중복값이 걸러지는 것을 막기 위해)
,cte_rfm_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
)
-- r, f, m점수를 기준으로 total score 산정
SELECT customer_id ,r,f,m,r+f+m AS total_score 
FROM cte_rfm_score
ORDER BY total_score DESC

해당 점수를 기반으로 분석을 진행할 수 있다.

 

2. r, f, m이 모두 5점인 고객사의 구매정보

-- rfm 15점인 고객에 대한 구매분석

	/*(생략)*/
    
, cte_rfm_scored AS(

	/*(생략)*/
    
)

SELECT cte_rfm_score.company_name, count(order_id), sum(sales) AS 총구매액
     , sum(quantity) AS 총구매수량, sum(sales)/sum(quantity) AS 개당구매액
FROM cte_customers, cte_rfm_score
WHERE r=5 AND f=5 AND m=5 AND cte_rfm_score.company_name = cte_customers.company_name
GROUP BY 1
ORDER BY sum(sales) DESC

 

 

3. r, f값을 기준으로 고객사의 개수를 카운팅한 이중축 텍스트그래프

-- r, f를 기준으로 이중축 텍스트그래프 카운팅

	/*(생략)*/
    
, cte_rfm_scored AS(

	/*(생략)*/
    
)
SELECT 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_rfm_score
GROUP BY r
ORDER BY r DESC

 

---

※ 재구매율 분석 ※

---

/* 재구매율 분석 */
-- 재구매의 기준을 기준별(여기선 year) 다음 지표가 존재하는 것으로 잡는다. 
-- (2017 -> 2018 then 2017년 재구매 / 2017 -> 2019 then 재구매x)

WITH cte_customers as(
SELECT o.order_id , o.order_date
     , to_char(o.order_date,'YYYY') AS YEAR
     , c.customer_id, c.company_name
FROM customers c, orders o
WHERE c.customer_id = o.customer_id
) 
-- 1. 고객, 구매연도 중복되지 않게 불러옴
, cte_select AS (
SELECT distinct customer_id, year
FROM cte_customers
ORDER BY 1
)
-- 2. self join해서 컬럼 복사 후, 원래의 year값을 year를 더한 값과 연결해 join해준다.
, cte_nextyear_join AS (
SELECT a.customer_id , a.YEAR, b.YEAR AS next_year
FROM cte_select a LEFT JOIN cte_select b		
			-- 뒤의 데이터가 null이 돼도 앞의 데이터는 남아있어야 한다
ON a.customer_id = b.customer_id AND a.YEAR::NUMERIC+1 = b.YEAR::NUMERIC  
			-- 실수가 필요한 경우 numermic, 정수만 사용은 int(정수끼리 나누면 정수만 값으로 나옴)
)
SELECT YEAR, count(year) AS 당해구매고객수, count(next_year) AS 재구매고객수
     , round(count(next_year)::numeric/count(year)::NUMERIC * 100,2)||'%' AS 재구매율
FROM cte_nextyear_join
GROUP BY year

재구매율 분석의 순서는 다음과 같다.

1. 고객, 구매연도에 대해 고객을 distinct값으로 설정하여 불러온다.

 

2. self join을 하여 컬럼을 복사한 후, join 시 원래 데이터의 year+1값이 이후 데이터의 year값이 되도록 한다.

 (뒤에 노출되는 year값이 다음 해의 값이 된다.)

  + 이때, left join으로 묶어 뒤의 값이 null이 되더라도 앞의 값이 null이 아니게 한다.

 

3. 연도, 선택 해에 구매한 고객, 그 중 다음 해에도 다시 구매한 고객(재구매고객), 둘을 나눈 재구매율을 구해 띄운다.

 

---

※ 이탈고객 분석 ※

---

/* 이탈고객 분석 */
WITH cte_customers as(
SELECT o.order_id , o.order_date , od.unit_price, od.quantity , od.discount
	 , od.unit_price * od.quantity * (1-od.discount) AS sales
     , c.customer_id, c.company_name
FROM customers c, orders o , order_details od
WHERE c.customer_id = o.customer_id AND o.order_id = od.order_id 
)
-- 1. 고객별 최종 구매일 추출
, cte_lastorder as(
SELECT customer_id, max(order_date) AS lastOrder, max(max(order_date)) OVER () AS recentDate
FROM cte_customers
GROUP BY customer_id
)
-- 2. 경과일 계산
, cte_datediff as(
SELECT *, date_trunc('day',recentDate) - date_trunc('day',lastOrder) AS dateDiff
FROM cte_lastorder
)
-- 3. 이탈고객 여부
, cte_leave_cust AS(
SELECT *, CASE WHEN dateDiff >= '90 days' THEN 1 ELSE 0 END AS 이탈여부
FROM cte_datediff
)
-- 4. 이탈률 계산
, cte_leave_rate AS (
SELECT round((sum(이탈여부)/count(이탈여부)::NUMERIC)*100,2)||'%' AS 이탈율
FROM cte_leave_cust
)
-- 5. 이탈고객의 구매이력 추출
SELECT l.customer_id, sum(sales), count(DISTINCT order_id), max(order_date), l.dateDiff
FROM cte_leave_cust l, cte_customers c
WHERE l.customer_id = c.customer_id AND 이탈여부 = 1
GROUP BY 1, l.dateDiff

이탈고객 분석의 순서는 다음과 같다. 

 

1. 고객별 (group by customer_id) 최종 구매일을 추출하고, 기준으로 삼을 일자를 추출한다.(예시에선모든 구매이력 중  가장 최근의 구매일)

 

2. 고객별 최종 구매일을 기준으로 기준일자로부터 얼마나 떨어져있는지 경과일을 계산한다.

 

3. 이탈로 판단할 기준 기간을 잡은 후(예시에선 90일), 경과일이 90일 이상인 고객들을 이탈고객으로 분류한다.

  - 이때 편의상 이탈고객의 경우 1, 해당없는 경우 0으로 처리한다.

 

4. 이탈고객과 전체고객을 비율화해 이탈률을 계산한다.

  - 이때 앞에서 1과 0으로 설정했으므로, 이탈고객의 수는 sum(이탈여부)가 되며, 미이탈고객의 수는 count(이탈여부)가 된다.

 

5. 맨 처음 cte테이블과 join해 이탈고객의 구매이력을 추출하며 마무리한다.