[STARTERS 4기 TIL] SQL 심화 #7 - RFM, 재구매율, 이탈고객, 함수, 과제 (230418)

 

📖 오늘 내가 배운 것

 

1. RFM

2. 재구매율 분석

3. 이탈고객 분석

4. 함수

5. 과제

 

 

---

1. RFM 분석

고객 세분화 방법에는 DECIL, RFM 등등이 있다! 그 중 RFM 분석~

 

1.1 개요

📌 RFM 분석?

Decil 분석의 단점을 보완한 분석 기법이 RFM 분석!

1. 구매 가능성이 높은 고객을 식별하기 위한 데이터 분석 방법

2. 마케팅에서 사용자 타겟팅을 위한 방법

 

📌 지표 및 우선순위

1. Recency : 얼마나 최근에 구매?

2. Frequency : 얼마나 빈번하게 구매?

3. Monetary : 얼마나 많은 금액을 지불?

 

📌 구하는 순서

기준일 : orders 테이블의 order_date 최대값으로 하기

R : 기준일 대비 며칠이 지났는가 (MAX(order_date) - order_date)

F : 총구매횟수

M : 총구매액

 

1.2 직접 구해보기

1. RFM에 쓰일 지표 추출

with cte_customers as (
select 	 -- customers
	  c.customer_id
	 , c.company_name
	 , c.contact_title
	 , c.country
	 , c.city
	 -- order
	 , o.order_id
	 , o.order_date
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'MM') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , date_part('dow' ,order_date) as dow
	 -- order_details
	 , od.product_id 
	 , od.unit_price 
 	 , od.quantity 
 	 , od.discount 
	 , round( od.unit_price * od.quantity * (1-od.discount) ) as amount
	 -- categories
	 , c2.category_id
	 , c2.category_name
	 --, p.product_id as 제품ID order_details와 중복!
 	 , p.product_name
from customers c
	 INNER join orders o ON o.customer_id = c.customer_id
	 INNER join order_details od ON od.order_id = o.order_id
	 inner join products p on p.product_id = od.product_id
	 INNER join categories c2 on p.category_id = c2.category_id
)
, cte_country_customercnt_amount_ordercnt as (
-- 국가별 고객수, 매출액, 주문건수
select country as 국가
	 , count(distinct customer_id) as 고객수
	 , sum(amount) as 매출액
	 , count(distinct order_id) as 주문건수 
from cte_customers
group by country
order by 3 desc
)
-- 상관계수 뽑기
, cte_country_customercnt_amount_ordercnt_corr as (
select corr(고객수, 매출액) as "고객수_매출액"
	 , corr(매출액, 주문건수) as "매출액_주문건수"
	 , corr(고객수, 주문건수) as "고객수_주문건수"
from cte_country_customercnt_amount_ordercnt
)
-- 지역 추가하기
, cte_country_group as (
select *
	 , case
	 	when lower(국가) in ('usa','canada','mexico') then 'NorthAmerica'
	 	when lower(국가) in ('brazil','venezuela','argentina') then 'SouthAmerica'
	 	else 'Europe'
	 end as 지역
from cte_country_customercnt_amount_ordercnt
)
-- 지역별 고객수, 매출액, 주문건수 집계
, cte_country_group_customercnt_amount_ocnt as (
select 지역
	 , sum(고객수) as 고객수
	 , sum(매출액) as 매출액
	 , sum(주문건수) as 주문건수 
from cte_country_group
group by 1
order by 3 desc
)
-- 지역별 고객수, 매출액, 주문건수 구성비
, cte_country_group_customercnt_amount_ocnt_per as (
select 지역
	 , 고객수
	 , 고객수 / sum(고객수) over() * 100 as 고객수구성비
	 , 매출액
	 , 매출액 / sum(매출액) over() * 100 as 매출액구성비
	 , 주문건수
	 , 주문건수 / sum(주문건수) over() * 100 as 주문건수구성비
from cte_country_group_customercnt_amount_ocnt
)
-- 지역별 매출 비교
-- 순위-지역([카테고리명]제품명(제품번호))-지역-지역
, cte_country_category_quantity as (
select 지역
	 , category_name
	 , product_name
	 , product_id
	 , sum(quantity) as total_quantity
from cte_customers cc
	 left join cte_country_group ccg on cc.country = ccg.국가
group by 1,2,3,4
)
-- 지역-카테고리 quantitiy에 따른 RANK
, cte_country_category_quantity_rank AS (
select *
	 , row_number () over(partition by 지역 order by total_quantity desc) as rank
from cte_country_category_quantity
)
-- 지역별 판매된 제품 순위
, cte_country_category_quantity_rank_dsec as (
select rank
	 , MAX(case when 지역 = 'NorthAmerica' then '['||category_name||']'||' '||product_name||' '||'('||product_id||')' end) as "NorthAmerica"
	 , MAX(case when 지역 = 'SouthAmerica' then '['||category_name||']'||' '||product_name||' '||'('||product_id||')' end) as "SouthAmerica"
	 , MAX(case when 지역 = 'Europe' then '['||category_name||']'||' '||product_name||' '||'('||product_id||')' end) as "Europe"
from cte_country_category_quantity_rank
group by 1
order by 1
-- 강사님은 CASE WHEN으로 먼저 묶고 가져오심!!!!
-- 추후에 따라해보기
)
--고객별 매출액, 주문건수, 건당평균주문액
, cte_customer_quantity_amount as (
select customer_id
	 , count(distinct order_id) as order_cnt
	 , sum(amount) as amount
from cte_customers
group by 1
order by 3 desc
)
-- 고객별 매출액, 주문건수, 건당평균주문액 순위
, cte_customer_ocnt_amount_amountpero_rank as (
select customer_id
	 , order_cnt as 주문건수
	 , row_number() over(order by order_cnt desc) as 주문건수순위
	 , amount as 매출액
	 , row_number() over(order by amount desc) as 매출액순위
	 , round(amount / order_cnt) as 건단평균주문액
	 , row_number() over(order by amount / order_cnt desc) as 건당평균주문액순위
from cte_customer_quantity_amount
order by 5 asc
)
-- 고객당 매출액 및 ntile-등급화
, cte_amount_decil as (
select customer_id, amount
	 , ntile(10) over (order by amount desc) as decil
from cte_customer_quantity_amount
)
-- decil 별 매출액
, cte_decil_group_amount as (
select decil
	 , sum(amount) as decil_sum_amount
from cte_amount_decil
group by 1
order by 2 desc
)
-- decil별 매출액 토탈 비율
, cte_decil_amount_per as (
select *
	 , sum(decil_sum_amount) over() as total
	 , decil_sum_amount / sum(decil_sum_amount) over() * 100 as decil_sum_amount_rate
from cte_decil_group_amount
)
-- decil별 매출액 토탈 비율 누적비
, cte_decil_amount_per_cumsum as (
select *
	 , sum(decil_sum_amount_rate) over (order by decil) as cumsum
from cte_decil_amount_per
)
---------- RFM -----------
-- recency 구하기 위한 max(order_date) - order_date 테이블
, cte_customer_maxo as (
select *
	 , MAX(order_date) over() as maxo
from cte_customers
)
-- rfm
, cte_rfm as (
select customer_id
	 , max(maxo) - max(order_date) as Recency
	 , count(distinct order_id) as Frequency
	 , sum(amount) as Monetary
from cte_customer_maxo
group by 1
order by 2 asc, 3 desc, 4 desc
)
select *
from cte_rfm

 

✅ 요기서 RFM에 대한 기준을 정하게 된다.

➡️ 몇 단계로 나눌?

➡️ 단계별 기준은 어떻게?

 

 

 

 

 

 

ntile 사용한 연습

---------- RFM -----------
-- recency 구하기 위한 max(order_date) - order_date 테이블
, cte_customer_maxo as (
select *
	 , MAX(order_date) over() as maxo
from cte_customers
)
-- rfm
, cte_rfm as (
select customer_id
	 , max(maxo) - max(order_date) as Recency
	 , count(distinct order_id) as Frequency
	 , sum(amount) as Monetary
from cte_customer_maxo
group by 1
order by 2 asc, 3 desc, 4 desc
)
, cte_rfm2 as (
select customer_id
	 , recency
	 , ntile(5) over (order by recency desc) as r
	 , frequency
	 , ntile(5) over (order by frequency )as f
	 , monetary
	 , ntile(5) over (order by monetary ) as m
from cte_rfm
)
select *
from cte_rfm2

 

2. rfm_score

---------- RFM -----------
-- 1. r 구하기 위한 max order_date
, cte_customer_maxo as (
select *
	 , MAX(order_date) over() as maxo
from cte_customers
)
-- 2. max(order_date) - order_date 테이블
, cte_rfm as (
select customer_id
	 , max(maxo) - max(order_date) as Recency
	 , count(distinct order_id) as Frequency
	 , sum(amount) as Monetary
from cte_customer_maxo
group by 1
order by 2 asc, 3 desc, 4 desc
)
-- rfm 스코어 부여 연습 (ntile로)
, cte_rfm2 as (
select customer_id
	 , recency
	 , ntile(5) over (order by recency desc) as r
	 , frequency
	 , ntile(5) over (order by frequency )as f
	 , monetary
	 , ntile(5) over (order by monetary ) as m
from cte_rfm
)
-- 3. rfm 스코어 부여
, 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
)
select customer_id
	 , r
	 , f
	 , m
	 , r+f+m as total_score
from cte_rfm_score
order by 5 desc

핵심고객 특별 관리, 점수 별 고객 분포 파악, 과거에는 우수고객이었는데 r 떨어져-> 메일 보낼 마케팅 대상

 

 

3. 점수 별 고객 분포

select total_score
	 , count(*) 
from cte_rfm_score2
group by total_score
order by 1 desc

 

 

 

4. 이전에 우수 고객이었는데 최근에 구매하지 않은 사람들

, cte_f5m5 as (
select *
from cte_rfm_score2
where r in (1,2,3,4) and f=5 and m=5
)
select *
from cte_f5m5

✅ 이 고객들 한테 실시할 수 있는 전략?

1. 쿠폰 발행

2. 추천제품 (어떤 제품? 과거 구매이력, 해당 고객과 속성이 유사한 고객 그룹 정보)

 

 

5. R과 F를 통한 신규/단골/이탈 분석

-- r이 5점 f가 5,4,3,2,1인 경우의 고객 수
, cte_r5f5 as (
select 'R'||r as " "
	 , sum(case when f = 5 then 1 else 0 end) as "F5"
	 , sum(case when f = 4 then 1 else 0 end) as "F4"
	 , sum(case when f = 3 then 1 else 0 end) as "F3"
	 , sum(case when f = 2 then 1 else 0 end) as "F2"
	 , sum(case when f = 1 then 1 else 0 end) as "F1"
from cte_rfm_total_score
group by r
order by r desc
)
select *
from cte_r5f5

 

6. 핵심고객 구매이력 도출

-- 핵심고객 구매이력 도출
, cte_high_rank_customers_order as (
select *
from cte_customers
where customer_id in (select customer_id
from cte_rfm_total_score
where total_score = 15
)
)
-- 핵심고객 구매이력 도출 2번째 방법
, cte_high_rank_customers_order2 as (
select *
from cte_rfm_total_score a
	 inner join cte_customers b on a.customer_id = b.customer_id
	 and a.total_score=15
)
select *
from cte_high_rank_customers_order2

✅ 이렇게 구해놓고 고객 속성에 따른 분석 가능하다~

구매한 카테고리 - 제품

어떤 지역-국가

구매 패턴 (주기)

매출에서 차지하는 비중 등등

 

2. 재구매율 분석

2.1 연도별 재구매율

1996년에 구매를 했는데 1997년에는 구매를 했고, 1998년에 구매를 했다!

이건 재구매를 하지 않은 거!

연속으로 그 다음 년도에 구매해야 구매로 친다~

 

Step 1. 핵심고객 구매이력 도출

---------------------- 재구매율 분석 ------------------------
-- 1. 핵심고객 구매이력 도출
, cte_high_rank_customers_order as (
select *
from cte_customers
where customer_id in (select customer_id
from cte_rfm_total_score
where total_score = 15
)
)
-- 핵심고객 구매이력 도출 2번째 방법
, cte_high_rank_customers_order2 as (
select *
from cte_rfm_total_score a
	 inner join cte_customers b on a.customer_id = b.customer_id
	 and a.total_score=15
)

 

 

Step 2. 구매년도를 중복되지 않게 불러온다

---------------------- 재구매율 분석 ------------------------
-- 1. 핵심고객 구매이력 도출
, cte_high_rank_customers_order as (
select *
from cte_customers
where customer_id in (select customer_id
from cte_rfm_total_score
where total_score = 15
)
)
-- 핵심고객 구매이력 도출 2번째 방법
, cte_high_rank_customers_order2 as (
select *
from cte_rfm_total_score a
	 inner join cte_customers b on a.customer_id = b.customer_id
	 and a.total_score=15
)
-- 2. 고객, 구매년도를 중복되지 않게 불러온다.
, cte_select as (
select distinct customer_id
	 , year
from cte_customers
order by 1
)

 

Step 3. self join을 통해 고객 별로 구매 연도 우측에 int+1 조건으로 붙여준다

---------------------- 재구매율 분석 ------------------------
-- 1. 핵심고객 구매이력 도출
, cte_high_rank_customers_order as (
select *
from cte_customers
where customer_id in (select customer_id
from cte_rfm_total_score
where total_score = 15
)
)
-- 핵심고객 구매이력 도출 2번째 방법
, cte_high_rank_customers_order2 as (
select *
from cte_rfm_total_score a
	 inner join cte_customers b on a.customer_id = b.customer_id
	 and a.total_score=15
)
-- 2. 고객, 구매년도를 중복되지 않게 불러온다.
, cte_select as (
select distinct customer_id
	 , year
from cte_customers
order by 1
)
-- 3. left self join을 통해 고객 별로 구매 연도 우측에 int+1로 붙여준다.
, cte_self_join as (
select a.customer_id
	 , a.year
	 , b.year as next_year
from cte_select a 
	 left join cte_select b on a.customer_id = b.customer_id
							and a.year::int+1 = b.year::int
							-- 다음 년도랑 join을 했을 경우 값이 같지 않다면? left join이므로 null이 들어가게 된다.
)
select *
from cte_self_join

해당 연도가 없을 경우, left join이라 전부 null로 들어간다

 

Step 4. 당해구매자수, 다음해재구매자수

, cte_reorders as (
select year
	 , count(year) as 당해구매자수
	 , sum(case when next_year is null then 0 else 1 end) as 다음해재구매자수
	 -- case when 안 써도 되고 그냥 count 해도 null 없이 나온다!
	 , round(count(next_year) / count(year)::numeric*100, 2)::varchar(10)||'%' as 재구매율
from cte_self_join
group by 1

# 형변환 시 실수 numeric, 정수만 필요하다 int

# 요기선 0으로 연산해야 하기 때문에 아무 컬럼이나 numeric 붙여줘야 함!

 

 

2.2 월별 재구매율

들어가기 전 날짜 연산 (timestamp 타입)

----------------------- 날짜 시간 데이터 다루기 ----------------------------------
-- 날짜 + 숫자 = 날짜 + days/months/year 등등
select now()
	 , now() + '1days' "+1"
	 , now() + '1month'
	 , now() + '1year'
	 , now() + '1day'
	 , now() + '1hour'
	 , now() + '1minutes'
	 
-- date_trunc('part', 날짜/시간)
-- 이 함수의 특징은 특정 부분 이외의 default setting이 출력
select date_trunc('year', now())

 

 

Step 1. 고객 구매월 중복되지 않게 불러오기

Step 2. 다음 월과 매칭되도록 self join

Step 3. 월별 구매자 수 집계하여 재구매율 계산

-- 월별 재구매율
-- step 1. 고객 구매월 중복되지 않게 불러온다
-- step 2. 다음 얼과 매칭되도록 self join
-- step 3. 월별 구매자 수 집계하여 재구매율 계산
-- step 1
, cte_select2 as ( 
select distinct customer_id
	 , date_trunc('month', order_date) as month
from cte_customers
order by 1
)
-- self_join
, cte_self_join2 as (
select a.customer_id
	 , a.month as month
	 , b.month as next_month
from cte_select2 a
	 left join cte_select2 b on a.customer_id = b.customer_id
	 						and a.month + '1 month' = b.month
)
select to_char(month, 'YYYY-MM') as month
	 , count(month) as 이번달
	 , count(next_month) as 다음달
	 , (count(next_month)* 100 / count(month))||'%' as 재구매율 
from cte_self_join2
group by 1
order by 1

 

++++ 연도 별로 전환하려면?

-- 연도별
-- step 1. 고객 구매연도 중복되지 않게 불러온다
-- step 2. 다음 얼과 매칭되도록 self join
-- step 3. 연도별 구매자 수 집계하여 재구매율 계산
-- step 1
, cte_select23 as ( 
select distinct customer_id
	 , date_trunc('year', order_date) as year
from cte_customers
order by 1
)
-- self_join
, cte_self_join23 as (
select a.customer_id
	 , a.year as year
	 , b.year as next_year
from cte_select23 a
	 left join cte_select23 b on a.customer_id = b.customer_id
	 						and a.year + '1 year' = b.year
)
select to_char(year, 'YYYY') as year
	 , count(year) as 이번연도
	 , count(next_year) as 다음연도
	 , (count(next_year)* 100 / count(year))||'%' as 재구매율 
from cte_self_join23
group by 1
order by 1

 

3. 이탈고객 분석

✅ 비활동 고객 전환 비율!

(마지막 구매일 이후 90일 이상 경과한 고객의 비율)

(기준일 : order_date의 max값)

 

신규 고객을 유입시키는 비용보다 기존 고객을 유지시키는 비용이 저렴하다

 

3.1 구하는 순서! 따라 구해보기

Step 1 : 고객별 order_date의 max값, 전체 order_date의 max값

Step 2 : 경과일 계산

Step 3 : 이탈고객 여부 (이탈일 90일 이상)

Step 4 : 이탈률 계산

 

------------------------------ 이탈고객 분석 ---------------------------
-- Step 1 : 고객별 마지막 구매일 구하기
, cte_customer_max_orderdate as (
select customer_id
	 , max(order_date) as maxo
from cte_customers
group by 1
)
-- Step 2 : 경과일 및 이탈고객 여부 계산
, cte_customer_bye as (
select *
	 , max(maxo) over() as t_maxo
	 , max(maxo) over() - maxo as 이탈일
	 , case when max(maxo) over() - maxo >= 90 then 1 else 0 end as 이탈고객여부
from cte_customer_max_orderdate
)
-- Step 3 : 이탈률 계산
select count(customer_id) as 전체고객
	 , sum(이탈고객여부) as 이탈고객
	 , (sum(이탈고객여부) / count(customer_id)::numeric * 100)::varchar(10)||'%' as 이탈률
from cte_customer_bye

 

3.2 이탈 고객 구매 이력

-- Step + : 이탈 고객 구매 이력
select *
from cte_customers a
	 inner join (select customer_id
				 from cte_customer_bye
				 where 이탈고객여부 = 1) b on a.customer_id = b.customer_id

 

4. 함수

4.1 단일 행 함수 : 한 개의 행을 인자로 받아들임

📌 문자열

LOWER, UPPER : 알파벳 모두 대/소문자로

SUBSTRING : 문자열 원하는 부분 잘라서 가져오기

LENGTH : 문자열 길이 = 문자 갯수

CONCAT : 문자열 합치기

LTRIM/RTRIM/TRIM : 문자열 자르기

 

📌 숫자

ROUND 정도

 

📌 날짜

오늘 배운 것들!

 

4.2 다중 행 함수 : 주로 집계함수

COUNT, SUM, AVG 등등

 

4.3 백분위수, 최빈값

📌 percentile_cont 함수 : 백분위수 연속값으로 계산

 

사용법?

함수(0~1) within group (order by column)

최소=0/중앙=0.5/최대=1 등

------------------------ 없는 테이블 생성하기 with 이용해서
with
numbers(n) as (
	values
	(1)
	,(1)
	,(1)
	,(3)
	,(4)
	,(5)
)
select percentile_cont(0.5) within group (order by n)
from numbers;

: n 컬럼 정렬한 상태에서 중앙값 가져와라~

 

📌 percentile_disc 함수 : 백분위수를 이산값으로 반환

: 두 값 중 작은 값을 반환

 

📌 mode 함수 : 최빈값 리턴

 

예제 : products 테이블 제품 가격 4분위 수 구하기!

select percentile_cont(0.25) within group (order by unit_price)

, percentile_cont(0.5) within group (order by unit_price)

, percentile_cont(0.75) within group (order by unit_price)

from products

 

5. 과제

-- 20230418 신사율 과제
-- 제품의 연도별 재구매율 (동일한 고객이 재구매)

with cte_customers as (
select 	 -- customers
	  c.customer_id
	 , c.company_name
	 , c.contact_title
	 , c.country
	 , c.city
	 -- order
	 , o.order_id
	 , o.order_date
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'MM') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , date_part('dow' ,order_date) as dow
	 -- order_details
	 , od.product_id 
	 , od.unit_price 
 	 , od.quantity 
 	 , od.discount 
	 , round( od.unit_price * od.quantity * (1-od.discount) ) as amount
	 -- categories
	 , c2.category_id
	 , c2.category_name
	 --, p.product_id as 제품ID order_details와 중복!
 	 , p.product_name
from customers c
	 INNER join orders o ON o.customer_id = c.customer_id
	 INNER join order_details od ON od.order_id = o.order_id
	 inner join products p on p.product_id = od.product_id
	 INNER join categories c2 on p.category_id = c2.category_id
)
---------------------- 재구매율 분석 ------------------------
-- 1. 고객별 연도별 제품 구매 기록을 중복없이 불러온다!
, cte_customer_year_product_order as (
select distinct customer_id
	 , product_name
	 , year
from cte_customers
order by 1, 2
)
-- 2. 연도+1과 고객, 제품이 같은 경우 조건으로 주어 self join
, cte_self_join as (
select a.customer_id
	 , a.product_name
	 , a.year as year
	 , b.year as next_year
from cte_customer_year_product_order a
	 left join cte_customer_year_product_order b on a.customer_id = b.customer_id
	 					and a.product_name = b.product_name
	 					and a.year::int+1 = b.year::int
)
-- 3. product_name 별 연도 별 구매자,재구매자,재구매율 구하기
, cte_final as (
select product_name
	 , year as 연도
	 , count(year) as 구매자수
	 , count(next_year) as 재구매자수
	 , round(count(next_year)* 100 / count(year)::numeric,2)||'%' as 재구매율
from cte_self_join
group by product_name, 현재년
order by 1,2
)
select *
from cte_final

 

---

💪🏻 좋았던 점, 앞으로 개선해야 할 점 (추가로 배워야 할 점)

 

📌 오늘은 활용도가 높은 여러 분석 기법들에 대해 학습했다!!

학습하는 와중에 특히 시계열 관련 기법을 이용하는 빈도가 잦았는데,

이러한 부분에서 내 역량은 아직 부족하다 느껴졌기에 더 열심히 연습해야겠다!!🫠

 

#유데미, #유데미코리아, #유데미부트캠프, #취업부트캠프, #부트캠프후기, #스타터스부트캠프, #데이터시각화 #데이터분석 #태블로