[유데미 스타터스 취업 부트캠프 4기] 11주차 학습 일지(데이터분석/시각화(태블로))

[출처 : Udemy - STARTERS 블로그 학습일지 안내]

 

안녕하세요! 오늘은 유데미 스타터스 부트캠프 11주차 학습일지입니다! 🐸

 

이번주는 SQL 오프라인 강의를 마무리하며 SQL 미니 프로젝트를 진행하였습니다. 

 

그럼 11주차에는 어떤 내용을 학습했는지, 스타터스 부트캠프 11주차 학습일지 시작해보겠습니다 !💨

---

🌟 LEAREND

두 가지 분석 기법

1. Decil 분석

데이터를 10단계로 분할하여 중요도를 파악하는 방법

📌 decil 분석 📌

-- customers : coustomer_id
-- order_details : amount

with cte_customers as (
select c.customer_id
	 , od.unit_price * od.quantity * (1-od.discount) as amount
from customers c , orders o , order_details od 
where c.customer_id = o.customer_id 
  and o.order_id = od.order_id 
 )
 ✅ 매출액 기준으로 상위부터 10%씩 나누어 10개의 그룹 할당
 , cte_customer_info as (
 select customer_id
 	  , sum(amount) as 매출액
 	  , ntile(10) over(order by sum(amount) desc) as decil
 from cte_customers
 group by customer_id
 )
 ✅ decil 별 매출합계
 , cte_decil_group as (
 select decil, sum(매출액) as decil_sum_amount
 from cte_customer_info
 group by decil
 )
  ✅ decil 별 구성비
 , cte_decil_ratio 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
 )
  ✅ decil 별 구성비 누계
, cte_decil_agg as (
select *
	 , sum(decil_sum_amount_rate) over (order by decil) as cumsum
from cte_decil_ratio
order by decil
)
select *
from cte_decil_agg ;

 

Decil 분석의 단점

✔️ 한 번의 구매로 비싼 물건을 구매한 사용자와 정기적으로 저렴한 물건을 여러번 구매한 사용자가 같은 그룹으로 판정되는 문제

✔️ 검색기간이 너무 장기간이면 과거에는 우수고객이었어도 현재는 다른 서비스를 사용하는 휴먼고객이 포함될 수 있음

✔️ 검색기간이 너무 단기간이면 정기적으로 구매하는 안정고객보다 해당 기간 동안 일시적으로 많이 구매한 사용자가 우수고객으로 포함될 수 있음

 

 

RFM 분석

✔️ Recency : 얼마나 최근에 구매했는가?

✔️ Frequency : 얼마나 빈번하게 구매했는가?

✔️ Monetary : 얼마나 많은 금액을 지불했는가?

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

· 마케팅에서 사용자 티켓팅을 위한 방법

 ➡️ Decil 분석의 단점 보완

📌 1️⃣ RFM 집계 📌

with cte_customers as (
select c.customer_id , c.company_name , c.contact_title , c.country , c.city 
	 , o.order_id , o.order_date 
	 , to_char(o.order_date, 'YYYY') as year
	 , to_char(o.order_date, 'mm') as month
	 , to_char(o.order_date, 'dd') as day
	 , to_char(o.order_date, 'q') as quarter
	 , od.product_id , od.unit_price , od.quantity , od.discount 
	 , od.unit_price * od.quantity * (1-od.discount) as amount
	 , p.product_name 
	 , c2.category_id , c2.category_name 
from customers c , orders o , order_details od , products p , categories c2 
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 = c2.category_id 
 ) 
-- 1. 필요한 데이터
, cte_customer_maxo as (
select *, max(order_date) over() as maxo
from cte_customers
)
-- 2. 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 customer_id 
order by 2 , 3 desc, 4 desc
)
select *
from cte_rfm;

 

📌 2️⃣ 단계 정의 📌

, cte_ntile 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
order by r desc, f desc, m desc
)

 

---

 

⚡️ SQL Project

 

1. 분석 상황 설정

1. 우리는?

: Olist 입점 셀러

 

2. 우리의 목적

: 매출 확보 판매 전략을 세우기 위한 데이터 분석

ex) 지역 별 선호 카테고리, 고객 그룹(RFM 등) 별 선호 카테고리

 

3. 분석 과정

   1) 어떤 고객을 대상으로 해야할까?

       · RFM 기준

   2) 어떤 품목을 판매해야 할까?

       · 매출 TOP 10 카테고리

       · 꾸준히 판매되고 있는 카테고리 = 카테고리별 재구매율

   3) 어떤 지역에서 판매해야 할까?

       · 주문자와 판매자가 거리, 배송기간, 주문건수 상관관계

       · 2번 품목이 주로 판매되는 지역 TOP 3

 

2.  분석 분야

1. 카테고리별 매출 상위 top3

  · 매출 top10 카테고리

  · 1에 속한 카테고리의 재구매율

2. 재구매율

  · 월별 재구매율

  · 카테고리별 연도별 재주문율

3. 예상 배송기간 - 주문건수 간 상관관계

4. TOP 10 카테고리가 잘 팔리는 도시 TOP 3

 

3.  발표 자료

 

 

발표 피드백

· 셀러 입장에서의 고객 분석

→ RFM 적절한가?

→ RFM 중 R의 의미가 없다면 F와 M은 의미가 있는가?

· 목적이 명확하지 않음. 발표 초기에 분석 목적을 확실히 명시하고 가야 함

---

그럼 이상으로 11주차 학습일지를 마치겠습니다 !

12주차도 열심히 달려보겠습니다 !!!💨

 

 

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

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

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