[ 유데미 스타터스 ] 52일차 TIL 프로젝트 기반 SQL 실전 트레이닝 - 미니 프로젝트 1

1. 개요

1.1 데이터셋 설치

① 스키마 생성

② olist_ec.sql 파일 불러오기

③ 테이블 생성

④ psql 실행

    → client_encoding 변경

    → 데이터 가져오기

⑤ pk 생성, fk 생성

Brazilian E-Commerce Public Dataset by Olist

 

1.2 테이블 구조 파악

- product_id가 같은 제품을 여러 sellers가 판매할 수 있음

- sellers가 product가 아니라 order_items와 연관이 있음

- product_name_length가 비식별화되어 있기 때문에 제품명은 알 수 없고 카테고리 이름만 알 수 있음

- product_category_name_translation으로 카테고리 이름을 영어로 확인 가능함

- orders와 customers의 customer_id는 1:1 관계로 고객을 유일하게 식별하는 식별자는 customer_unique_id임

 

1.3 SQL 데이터 분석

① 데이터 탐색

② 분석 목적 설정

③ 지표 설정 및 분석 계획

④ 데이터 추출/정제/가공/분석

⑤ 리포트 작성 및 발표

 

✔ 분석 목적 설정

- 고객 담당자

EX) 효과적인 고객관리 전략 수립을 위한 데이터 분석

- 유통 담당자

- 제품 담당자

- 배송 담당자

➡️ 비즈니스 가치를 창출하기 위해 어떤 지표를 추출해서 분석하면 좋을지 고민하기

 

---

2. 미니 프로젝트 기획

2.1 분석 상황 설정

1) 우리는?

- Olist 입점을 원하는 seller

 

2) 우리의 목적

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

 

3) 분석 과정

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

- RFM 고객 분석

② 어떤 제품을 판매해야 할까?

- 매출 TOP 10 카테고리

- 꾸준히 판매되고 있는 카테고리(재구매율)

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

- 매출 TOP 10 카테고리가 판매되는 지역

- 배송기간과 주문건수의 상관관계

 

2.2 현황 파악 분석

📌 고객 구매지표 분석

필요한 컬럼

- customers : customer_id, customer_unique_id, customer_city

- orders : order_id, order_status

- order_payments : payment_value

- order_items : order_item_id, product_id, seller_id, price

- products : product_category_name

- product_category_name_translation : product_category_name_english

with cte_customers as (
select c.customer_id, c.customer_unique_id, c.customer_city
	, o.order_id, o.order_status
	, oi.order_item_id, oi.product_id, oi.seller_id, oi.price
	, p.product_category_name
	, pcnt.product_category_name_english 
from customers c, orders o, order_items oi, products p, product_category_name_translation pcnt
where c.customer_id = o.customer_id 
	and o.order_id = oi.order_id 
	and oi.product_id = p.product_id 
	and p.product_category_name = pcnt.product_category_name
)

 

✅ 도시 별 매출액, 주문건수, 고객수 집계 / 구성비

-- 도시별 매출액, 주문건수, 고객수 집계
, cte_city as (
select customer_city
	, count(distinct customer_unique_id) as 고객수
	, sum(price) as 매출액
	, count(distinct order_id) as 주문건수
from cte_customers
group by 1
order by 3 desc
)
-- 구성비
select customer_city
	, 고객수
	, round((고객수/sum(고객수) over())*100,2)::varchar(10)||'%' as 고객수구성비
	, 매출액
	, round((매출액/sum(매출액) over())::numeric*100,2)::varchar(10)||'%' as 매출액구성비
	, 주문건수
	, round((주문건수/sum(주문건수) over())*100,2)::varchar(10)||'%' as 주문건수구성비
from cte_city;

 

✅ 매출액 TOP 3 도시 별 판매된 제품 순위

- 정렬은 매출액을 기준으로, 괄호 안은 판매 수량을 표시

-- 도시별 카테고리 매출액
, cte_city_product_sale as (
select customer_city, product_category_name_english, sum(price) as 매출액, count(product_id) as 판매수량
from cte_customers
group by 1,2
order by 4 desc
)
-- 도시별 카테고리 매출액 순위
, cte_city_product_sale_rank as (
select customer_city, '['||product_category_name_english||'] '||'('||판매수량||')' as product_category_name_english
	, row_number() over(partition by customer_city order by 매출액 desc) as 순위
from cte_city_product_sale
)
-- 도시별 매출액순위 비교
-- 매출액 top3 (sao paulo, rio de janeiro, belo horizonte)
, cte_city_group_product_sale_rank_pivot as (
 select 순위
 	, max(case when customer_city='sao paulo' then product_category_name_english end) as "sao paulo"
 	, max(case when customer_city='rio de janeiro' then product_category_name_english end) as "rio de janeiro"
 	, max(case when customer_city='belo horizonte' then product_category_name_english end) as "belo horizonte"
 from cte_city_product_sale_rank
 group by 순위
 order by 순위
 limit 10
 )
select * from cte_city_group_product_sale_rank_pivot;

 

✅ 고객 별 매출액, 주문건수, 건당 평균 주문액

-- 고객별 매출액, 주문건수, 건당 평균주문
, cte_customer_info as (
select customer_unique_id
	, count(distinct order_id) as 주문건수
	, sum(price) as 매출액
	, sum(price)/count(distinct order_id) as 건당평균주문
from cte_customers
group by 1
order by 3 desc
)
select * from cte_customer_info;

 

---

3. 지표 설정 및 분석

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

✅ RFM 지표 설정

- Order Status : delivered

- 기준일 : order_purchase_timestamp

 

1) RFM 집계

-- 1) max order_date
, cte_customer_maxo as (
select *
	 , MAX(order_date) over() as maxo
from cte_main
)
-- 2) 고객별 RFM
, cte_rfm as (
select customer_unique_id
	 , max(maxo) - max(order_date) as Recency
	 , count(distinct order_id) as Frequency
	 , sum(price) as Monetary
from cte_customer_maxo
group by 1
)

 

2) 단계 정의 및 RFM 점수 부여

- Recency 5등분 후 1~5점

- Frequency 1=1, 2=2, 3/4=3, 4이상=4

- Monetary 5등분 후 1~5점

-- 3) 1차 rfm_score
, cte_rfm_score as (
select customer_unique_id
	 , recency
	 , ntile(10) over (order by recency desc) as r
	 , frequency
	 , case 
	 	when frequency < 2 then 1
	 	when frequency = 2 then 2
	 	when frequency <= 4 then 3
	 	else 4
	 end as f
	 , monetary
	 , ntile(10) over (order by monetary asc) as m
from cte_rfm
order by r desc, f desc, m desc
)
-- 4) 2차 rfm_score
, cte_rfm_final as (
select customer_unique_id
	 , recency as r
	 , case 
		 when r <= 2 then 1
		 when r <= 4 then 2
		 when r <= 6 then 3
		 when r <= 8 then 4
		 when r <= 10 then 5
	 	end recency
	 , frequency as f
	 , f as frequency 
	 , monetary as m
	 , case 
		 when m <= 2 then 1
		 when m <= 4 then 2
		 when m <= 6 then 3
		 when m <= 8 then 4
		 when m <= 10 then 5
	 	end monetary
from cte_rfm_score
)

 

✅ RFM Score 별 주요 지표

 

1) RFM Score 별 고객 수

-- 5) customer 별 r,f,m과 rfm_score
, rfm_customer_status as (
select *, recency + frequency + monetary as rfm_score
from cte_rfm_final
order by rfm_score desc, r desc, f desc, m desc
)
-- 6) rfm_score 별 r, f, m 별 고객 수 
, cte_final1 as (
select rfm_score
	 , recency
	 , frequency
	 , monetary
	 , count(customer_unique_id) as customer_cnt
from rfm_customer_status
group by 1,2,3,4
order by 2 desc, 3 desc,4 desc
)
-- 7) rfm_score 별 고객 수
, cte_final2 as (
select rfm_score
	 , count(customer_unique_id) as customer_cnt
from rfm_customer_status
group by 1
order by 1 desc
)
select * from cte_final2;

 

2) RFM Score 별 주요 지표 집계

-- 매출 구하기 위해 필요한 컬럼(Monetary=매출) 가져와서 고객 등급표 INNER JOIN
, cte_customer_status_amount as (
select rcs.customer_unique_id
	 , cr.Recency
	 , rcs.frequency as f
	 , rcs.r as r
	 , cr.frequency
	 , cr.Monetary
	 , rcs.rfm_score
from rfm_customer_status as rcs
	 inner join cte_rfm as cr on rcs.customer_unique_id = cr.customer_unique_id
order by 5
)
-- rfm_score 별 주요 지표 집계
, cte_customer_status_amount2 as (
select rfm_score
	 , round(sum(Monetary)) as 총구매액
	 , count(customer_unique_id) as 고객수
	 , round(sum(Monetary) / count(customer_unique_id)) as 평균구매액
	 , round(avg(f),1)||'번' as 평균주문수
	 , round(avg(to_char(r,'dd')::int),1)||'일' as 평균최근구매일
from cte_customer_status_amount
group by rfm_score
order by 1 desc
)

 

✅ 타겟 고객 선정

✔ 14점 ~ 8점까지의 Score그룹을 타겟 고객으로 선정

- r-f-m 중 한 수치에 값이 극단적으로 몰리는 것을 고려함

- 최근구매일/구매액의 분포 폭을 고려하여 전체 고객 수에서 차지하는 비중과 총 매출액에서 차지하는 비중이 어느 정도 확보되는 표본을 구성하고자 함

-- 매출 구성비
, cte_rfm_sales as (
select z1.customer_unique_id, avg(rfm_score) as rfm_score, sum(price) as sales
from rfm_customer_status z1, customers c2, orders o2 , order_items oi2
where z1.customer_unique_id = c2.customer_unique_id
  and c2.customer_id = o2.customer_id
  and o2.order_id = oi2.order_id
group by z1.customer_unique_id
order by 2 desc, 1
)
, cte_rfm_total_sales as (
select rfm_score
, sum(sales) as rfm_total_sales
from cte_rfm_sales
group by rfm_score
)
, cte_sales_ratio as (
select *
, sum(rfm_total_sales) over() as total_sales
, rfm_total_sales / sum(rfm_total_sales) over() * 100 as ratio
from cte_rfm_total_sales
)
, cte_ratio_agg as (
select *, sum(ratio) over (order by rfm_score desc) as ratio_agg
from cte_sales_ratio
)
-- 고객 선별
, cte_select_score as (
select *
from cte_ratio_agg
where ratio_agg <= 80
)
select * from cte_select_score;

→ 타겟 그룹은 전체 고객 수 중 40.77%의 고객 수를 차지하고, 전체 매출 중 64.57%를 차지함