[👩‍💻TIL 51일차 ] 유데미 스타터스 취업 부트캠프 4기

 

📍 RFM분석

step2. 단계 정의

• R: 작을수록 좋다.
• F, M: 클수록 좋다.

고려사항

• 몇단계로 나눌 것인가?
• 단계별 기준을 어떻게 잡을 것인가?

 

-- 5단계로 나누어 등급 부여
, 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_three
)
-- 점수부여
, cte_rfe_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_three
)
-- 고객 가치 산정
select customer_id , r, f, m, r+f+m total_score
from cte_rfe_score
order by 5 desc;

 

➡️ 점수가 가장 높은 고객은 특별 관리 대상으로 삼을 수 있음

➡️ 15점인 고객의 특성을 파악해서 이 점수대의 고객을 늘리는 전략을 세울 수 있음 

 

-- 점수별 고객 수 확인
select total_score, count(*) cnt
from cte_customer_value
group by 1

 ➡️ 월별 점수 분포를 확인할 수 있음

 

-- 과거 우수고객 (재방문 유도가 필요한 고객)
, cte_f5m5 as(
select *
from cte_customer_value
where r in (1,2,3,4) and f=5 and m=5
)

➡️ 재방문 전략: 쿠폰, 추천 제품(과거 구매이력, 비슷한 속성을 지닌 고객이 구매한 제품 확인)

 

-- 고객 분류
, cte_score_chart as (
select r
	, count(case when f=5 then 1 end) f5
	, count(case when f=4 then 1 end) f4
	, count(case when f=3 then 1 end) f3
	, count(case when f=2 then 1 end) f2
	, count(case when f=1 then 1 end) f1
from cte_customer_value
group by 1
order by 1 desc
)

 

-- 핵심고객 구매이력 추출
select *
from cte_customer_value v, cte_customers c
where v.customer_id = c.customer_id 
and total_score = 15;

➡️ 매출 기여도, 많이 사는 제품, 지역 등을 더 볼 수 있다.

---

📍 재구매율

/**************************************
 * 연도별 재구매율
 **************************************/
-- 1.고객, 구매년도를 중복되지 않게 불러온다.
, cte_select as(
	select distinct customer_id, year
	from cte_customers
	order by 1
)

-- 2. 다음 연도와 매칭되도록 self join 한다.
, cte_next_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::int+1 = c2.year::int
)

-- 3. 연도별 구매자 수 집계하여 재구매율 계산
, cte_reorder_ratio as(
	select year
	     , count(year) as 당해구매자수
	     , count(next_year) as 다음해재구매자수
	     , round(count(next_year)/count(year)::numeric *100,2)||'%' as 재구매율
	from cte_next_join
	group by 1
)

 

✔️ 날짜 연산 (timestamp 타입)

• interval: 날짜 차이 (1days, 1mon, 100 years)
• timestamp + 'n days/mon/years/hours/minutes/seconds'

 

✔️ 반환 형태

• date_part: int
• date_trunc: timestamp
• to_char: character

---

/**************************************
 * 월별 재구매율
 **************************************/
-- 1.고객, 구매월을 중복되지 않게 불러온다.
, cte_select as(
	select distinct customer_id
	     , date_trunc('month', order_date) as 구매월
	from cte_customers
	order by 1
)

 

-- 2. 다음 월과 매칭되도록 self join 한다.
, cte_next_join as(
	select c1.customer_id, c1.구매월, c2.구매월 as 다음월
	from cte_select c1 left join cte_select c2
		on c1.customer_id = c2.customer_id 
		and c1.구매월+'1 month' = c2.구매월
)

 

-- 3. 월별 구매자 수 집계하여 재구매율 계산
, cte_reorder_ratio as(
	select to_char(구매월,'YYYY-MM') as 월
	     , count(구매월) as 당월구매자수
	     , count(다음월) as 다음월재구매자수
	     , round(count(다음월)/count(구매월)::numeric*100,2)::varchar(10)||'%' as 재구매율
	from cte_next_join
	group by 구매월
	order by 1
)

 

---

• 비활동 고객 전환 비율: 마지막 구매일 이후 90일 이상 경과한 고객의 비율
  기준일: order_date의 max값
• 이탈고객 특성 분석

 

/**************************************
 * 이탈고객 분석
 **************************************/
-- 고객 최종 구매일
, cte_customer_last_order_date as(
	select customer_id 
		, max(order_date) as 최종구매일
	from cte_customers
	group by 1
	order by 2 desc
)

 

-- 경과일
, cte_diff as(
	select * 
	     , max(최종구매일) over()- 최종구매일 as 경과일
	from cte_customer_last_order_date
)

 

 

이탈률

1. 고객별 최종 구매일 추출

2. 경과일 계산

3. 이탈고객 여부 (90일 이상 경과했으면 이탈고객)

4. 이탈률 계산

 

 

-- 5. 이탈고객 특성 분석
, cte_이탈고객특성 as(
select *
from cte_이탈여부 a left join cte_customers b
on a.customer_id = b.customer_id
where 이탈여부 is not null
)

---

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

percentile_cont(n) within group (order by column)

n: 0~1 ➡️ 중앙값을 구하려면 0.5

 

---

📍 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으로 정렬한 테이블의 중앙값을 구해라

 

/************
 * 최빈값 구하기
 */
with
numbers(n) as(
values (1), (1), (1), (3), (4), (5)
)
select mode() within group (order by n)
from numbers;

 

-- product 테이블에서 가격의 사분위수 구하기
select percentile_cont(0.25) within group (order by unit_price) q1
	,percentile_cont(0.5) within group (order by unit_price) q2
	,percentile_cont(0.75) within group (order by unit_price) q3
from products p ;

---

📍 과제

 

-- 1단계: 제품명, 고객, 구매연도를 중복되지 않게 불러온다.
, cte_select as(
	select distinct product_name, customer_id, year
	from cte_customers 
	order by 1
)
-- 2단계: 다음연도와 매칭되도록 셀프조인
, cte_join as(
	select a.product_name
		, a.customer_id
		, a.year as 연도 
		, b.year as 다음연도
	from cte_select a left join cte_select b
	on a.product_name = b.product_name
	and a.customer_id = b.customer_id
	and a.year::int + 1 = b.year::int
	order by 1,2,3,4
)
-- 3단계: 연도별 구매자 수 집계하여 재구매율 계산
select product_name, 연도
	, count(연도) 구매자수
	, count(다음연도) 재구매자수
	, round(count(다음연도)/count(연도)::numeric*100,2)||'%' 재구매율
from cte_join
where 연도 is not null
group by 1,2
order by 1,2;

 

셀프조인에 대해 잘 몰라서 구글링을 통해 보충공부를 했다🥲

 

sql 셀프조인(self join) 쿼리 쓰는 이유와 간단한 예제

 

 

💡 마무리하며

 

들을 땐 이해한 것 같다가도 스스로 쿼리를 짜보려거나 조금이라도 뭔가가 바뀌면 생각처럼 잘되지않는 SQL 🥲 시험전까지 배운 내용은 모두 습득할 수 있도록 노력하자! 

---

* 유데미 큐레이션 바로가기 : https://bit.ly/3HRWeVL

* STARTERS 취업 부트캠프 공식 블로그 : https://blog.naver.com/udemy-wjtb

📌 본 후기는 유데미-웅진씽크빅 취업 부트캠프 4기 데이터분석/시각화 학습 일지 리뷰로 작성되었습니다.