[STARTERS 4기 TIL] SQL 심화 #8, 9 - 미니 프로젝트 1~2일차 (230419~20)

 

📖 오늘 내가 배운 것

 

1. 데이터 임포트

2. 데이터 파악

3. 분석 계획, 목적

4. 지표 설정, 분석

5. 결론

 

---

최종 평가와 해커톤 전 마지막 미니 프로젝트 진행

데이터 셋은 kaggle의 Brazilian E-Commerce Public Dataset by Olist

 

1. 데이터  임포트

1. 데이터 다운 (csv)

2. posgresql에서 olist 스키마 생성

3. posgresql에서 테이블 생성

CREATE TABLE olist.order_items (
	order_id varchar,
	order_item_id varchar (50),
	product_id varchar (50),
	seller_id varchar (50),
	shipping_limit_date timestamp,
	price float,
	freight_value float);
	
CREATE TABLE olist.order_payments (
	order_id varchar(50), 
	payment_sequential int, 
	payment_type varchar (50), 
	payment_installments int, 
	payment_value float);

CREATE TABLE olist.customers (
	customer_id varchar (50),
	customer_unique_id varchar(50),
	customer_zip_code varchar(10),
	customer_city varchar (50),
	customer_state varchar (50)
	);
	
CREATE TABLE olist.geolocation
	(geolocation_zip_code_prefix varchar(10),
	 geolocation_lat float,
	 geolocation_Ing float,
	 geolocation_city varchar (50),
	 geolocation_state varchar (10)
	);

CREATE TABLE olist.reviews
	 (review_id varchar (50),
	 order_id varchar (50), 
	 review_score int, 
	 review_comment_title varchar (50), 
	 review_comment_message varchar (500), 
	 review_creation_date timestamp, 
	 review_answer_timestamp timestamp);

CREATE TABLE olist.orders (
	order_id VARCHAR(100),
	customer_id VARCHAR(100),
	order_status VARCHAR(50),
	order_purchase_timestamp TIMESTAMP,
	order_approved_at TIMESTAMP,
	order_delivered_carrier_date TIMESTAMP,
	order_delivered_customer_date TIMESTAMP,
	order_estimated_delivery_date TIMESTAMP
	) ;

CREATE TABLE olist.products (
	product_id varchar(50),
	product_category_name VARCHAR(70),
	product_name_length int ,
	product_description_length int,
	product_photos_qty int,
	product_weight_g int ,
	product_length_cm int ,
	product_height_cm int ,
	product_width_cm int ) ;

CREATE TABLE olist.sellers
	(seller_id varchar(50),
	seller_zip_code_prefix varchar(10),
	seller_city varchar (100) ,
	seller_state varchar(15)
	);

CREATE TABLE olist.product_category_name_translation (
	product_category_name varchar(50),
	product_category_name_english varchar(70)
	);

 

3. SQL Shell 이용해서 encoding 정의

 

4. SQL Shell 에서 다운로드 받은 csv들 각 table에 import 

\copy olist.order_items  from '~경로~/olist_order_items_dataset.csv'  DELIMITER ',' CSV HEADER;
\copy olist.order_payments   from '~경로~/olist_order_payments_dataset.csv'  DELIMITER ',' CSV HEADER;
\copy olist.customers   from '~경로~/olist_customers_dataset.csv'  DELIMITER ',' CSV HEADER;
\copy olist.geolocation   from '~경로~/olist_geolocation_dataset.csv'  DELIMITER ',' CSV HEADER;
\copy olist.reviews   from '~경로~/olist_order_reviews_dataset.csv'  DELIMITER ',' CSV HEADER;
\copy olist.orders  from '~경로~/olist_orders_dataset.csv' DELIMITER ',' CSV HEADER;
\copy olist.products  from '~경로~/olist_products_dataset.csv' DELIMITER ',' CSV HEADER;
\copy olist.sellers  FROM '~경로~/olist_sellers_dataset.csv' DELIMITER ',' CSV HEADER;
\copy olist.product_category_name_translation  from '~경로~/product_category_name_translation.csv'  DELIMITER ',' CSV HEADER;

* 경로에 한글 포함 x

 

5. postgresql에서 PK 및 FK 생성

LTER TABLE ONLY orders    			ADD CONSTRAINT pk_orders PRIMARY KEY (order_id);
ALTER TABLE ONLY order_payments   	ADD CONSTRAINT pk_order_payments PRIMARY KEY (order_id, payment_sequential);
ALTER TABLE ONLY reviews    		ADD CONSTRAINT pk_reviews PRIMARY KEY (order_id, review_id);
ALTER TABLE ONLY customers    		ADD CONSTRAINT pk_customers PRIMARY KEY (customer_id);
ALTER TABLE ONLY order_items    	ADD CONSTRAINT pk_order_items PRIMARY KEY (order_id, order_item_id);
ALTER TABLE ONLY products    		ADD CONSTRAINT pk_products PRIMARY KEY (product_id);
ALTER TABLE ONLY sellers    		ADD CONSTRAINT pk_sellers PRIMARY KEY (seller_id);
ALTER TABLE ONLY product_category_name_translation    ADD CONSTRAINT pk_product_category_name_translation PRIMARY KEY (product_category_name);

ALTER TABLE ONLY order_payments ADD CONSTRAINT fk_order_payments FOREIGN KEY (order_id) REFERENCES orders;
ALTER TABLE ONLY reviews 		ADD CONSTRAINT fk_reviews FOREIGN KEY (order_id) REFERENCES orders;
ALTER TABLE ONLY orders 		ADD CONSTRAINT fk_orders FOREIGN KEY (customer_id) REFERENCES customers;--확인필요
ALTER TABLE ONLY order_items 	ADD CONSTRAINT fk_order_items FOREIGN KEY (order_id) REFERENCES orders;
ALTER TABLE ONLY order_items 	ADD CONSTRAINT fk_order_items_products FOREIGN KEY (product_id) REFERENCES products;
ALTER TABLE ONLY order_items 	ADD CONSTRAINT fk_order_items_seller FOREIGN KEY (seller_id) REFERENCES sellers;

 

2. 데이터 파악

2.1 테이블 정의

테이블명	테이블 설명	데이터 갯수	특이사항
customers	고객의 지역 정보	99,441	orders 테이블과 1:1 매칭
geolocation	지리적 위치	1,000,163
order_items	주문 아이템	112,650	동일 제품을 다른 판매자가 판매할 수 있음. (판매자 ID가 ORDER_DETAILS? 테이블과 연관됨)
order_payments	주문 결제 정보	103,886
orders	주문 정보	99,441	customers 테이블과 1:1 매칭주문상태를 관리(주문->승인->배송시작->배송완료)
product_category_name_translation	카테고리 명	71	PK 없음. 중복데이터 없음.
products	제품 정보	32,951	제품명과 제품설명은 익명처리되어 알 수 없음
reviews	리뷰	99,224
sellers	판매자	3,095

 

2.2 테이블 관계 주의사항

📌 orders - customers

- customer_id 는 고객을 유일하게 식별하는 식별자가 아니다! customer_unique_id가 유일한 식별자

- orders-customers가 1 대 1 관계

- 이에 고객 관련 분석을 위해 customers 테이블 JOIN 후 이용 시 customer_unique_id 사용해야 함

 

 

 

 

 

 

 

 

 

📌 product_category_name_translation

카테고리명 포르투갈어 -> 영어 이용 가능

 

 

 

 

📌 주요 테이블 재정리

orders에는 주문 상태에 대한 내용이 많음!

주문 timestamp, 배송 timestamp, 배송완료 timestamp 등등

 

order_items는 순번 존재

 

customers는 고객 사는 지역

 

 

2.3 특이사항

📌 orders ←→ reviews : 리뷰 없는 order_id 768개

select o.order_id, count(r.review_score) 
from orders o
	 left join reviews r on o.order_id = r.order_id
group by 1
having count(r.review_score) < 1

 

📌 order_item_id

JOIN한 테이블 모든 컬럼 일치 하지만 order_item_id만 다른 경우 존재

 

예시) order_id : ff0286485ce9c3f1d6ed22a4c7402060

select *
from orders o
	 inner join customers c on o.customer_id = c.customer_id
	 inner join order_items oi on o.order_id = oi.order_id
	 left join products p on oi.product_id = p.product_id
	 left join product_category_name_translation pc on p.product_category_name = pc.product_category_name
	 left join reviews r on o.order_id = r.order_id
where o.order_id = 'ff0286485ce9c3f1d6ed22a4c7402060'
order by o.order_id desc

→ product_id가 같은 경우, 다른 경우 둘 다 존재! 주문 수량으로 추정

→ 인지하고 분석 들어가기

 

3. 분석 계획 / 목적

고객 관리 담당자 - 효율적인 고객 관리 위한 마케팅 전략!

유통/배송 담당자 - 배송관련해서 뭐 보겠다~

제품 담당자로써 ~하겠다 - 고객 리뷰 좋은 제품, 많이 팔리는 지역

등등 해볼 수 있는 거 많은데

 

3.1 우리는?

: Olist에 입점하려는 셀러

 

3.2 분석 목적

: Olist 상위 고객들 특성 파악 후 이에 맞춰 성공적인 입점/셀링 전략 수립

 

3.3 분석 흐름

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

• RFM 기준
• 최근 활성화 여부, 구매 횟수, 총 구매액 3가지 지표를 통해 고

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

• 매출 TOP 10 카테고리
• 꾸준히 판매되고 있는 카테고리 = 카테고리별 재구매율

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

• 주문자와 판매자가 거리, 배송기간, 주문건수 상관관계
• 2번 품목이 주로 판매되는 지역 TOP 3

 

4. 지표 설정 및 분석

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

1. RFM 지표 설정

Status : delivered

기준일 : order_purchase_timestamp

 

📌 Recency

max - min order_purchase_timestamp = 773 days!

ntile(5) 이용해서 약 150일 기준 5등분

-- recency 내림차순 후 ntile 사용하여 5등분 후 점수
ntile(5) over (order by recency desc) as r

-- 최근 구매일 빠른 그룹부터 5점~1점
case 
	when r <= 1 then 1
	when r <= 2 then 2
	when r <= 3 then 3
	when r <= 4 then 4
	when r <= 5 then 5
end recency

 

📌 Frequency

2번 이상 구매한 사람 수가 대략 3000명 정도로 매우 부족

재 구매한 고객 표본이 너무 적어 2회 이상 구매 시 전부 포함

 

case 
	when frequency < 2 then 1
	when frequency = 2 then 2
	when frequency <= 4 then 3
else 4

 

 

📌 Monetary

ntile(5) 등분 매출 상위 20%

-- recency와 마찬가지로 ntile 사용하여 계산!
-- 다른 점은 매출이 높다면 ntile에서 높은 score 부여해야 하기 때문에 ntile order by에 asc
ntile(5) over (order by monetary asc) as m

case 
	when m <= 1 then 1
	when m <= 2 then 2
	when m <= 3 then 3
	when m <= 4 then 4
	when m <= 5 then 5
end monetary

 

📌 지표 결론

recency 5등분 후 1~5점 | frequency 1=1, 2=2, 3/4=3, 4이상=4 | monetary 5등분 후 1~5점

 

---

2. RFM Score 별 주요 지표

-- 4조​
/***********************************
 * RFM / 카테고리 / 지역 분석
 ***********************************/
with cte_main as (
select distinct o.order_id
	 , c.customer_unique_id
	 , o.order_status
	 , date_trunc('day', o.order_purchase_timestamp) as order_date
	 , oi.price
from orders o
	 inner join customers c on o.customer_id = c.customer_id
	 inner join order_items oi on o.order_id = oi.order_id
	 left join products p on oi.product_id = p.product_id
	 left join product_category_name_translation pc on p.product_category_name = pc.product_category_name
	 left join reviews r on o.order_id = r.order_id
where order_status = 'delivered'
)
---------- 1. RFM 구하기 -----------
-- 1. r 구하기 위한 max order_date
, cte_customer_maxo as (
select *
	 , MAX(order_date) over() as maxo
from cte_main
)
-- 2. max(order_date) - order_date 테이블
, 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
order by 2 asc, 3 desc, 4 desc
)
-- 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
)
-- 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
)
---------- 2. 고객 등급 별 매출액 및 인당 매출액 구하기 -----------
-- 1. 매출 구하기 위해 필요한 컬럼(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
)
---------------- RFM 테이블 정리 --------------------
-- *cte_customer_status_amount2 : rfm_score 별 총구매액, 고객수, 평균구매액, 평균최근구매일
-- rfm_customer_status : customer 별 r,f,m, rfm_score 수치 확인
-- cte_final2 : rfm_score 별 고객 수
-- cte_final1 : rfm_score 별 r,f,m 수치 별 고객 수
select *
from cte_customer_status_amount2

이 스코어 등급 중 8점부터 14점까지의 Score 그룹을 타겟으로 선정!

이유는?

1. r-f-m 중 한 수치에 값이 극단적으로 몰리는 것을 고려했어야 했고
2. 최근구매일/구매액수의 분포 폭을 고려하여 타겟 그룹은 전체 고객 수에서 차지하는 비중이 컸어야 함과 동시에 총매출액에서 차지하는 비중이 3분에 2정도 되는 상위 그룹 표본을 확보하기 위해!

 

rfm score 8~14 그룹은 고객 수 및 매출액에서 각각 아래와 같은 구성비를 갖는다

 

4.2 카테고리 분석

rfm_score를 통해 분류된 고객 그룹에 대해 잘 팔리는, 우수 카테고리를 도출!

입점 시 해당 카테고리 위주의 판매 전략을 세우고자 함

---------- 3. 카테고리 분석 -----------
-- 1. 매출 구성비를 알아보자
, 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
)
-- 2. 고객 선별 : 총 매출의 약 78% 차지하는 score층
, cte_select_score as (
select *
from cte_ratio_agg
where ratio_agg <= 80
)
-- 3. 매출 top10 카테고리
, cte_rfm_product as (
select pcnt.product_category_name_english
, oi3.price, c3.customer_city 
, z3.*
from cte_select_score z2
, cte_customer_status_amount z3
, customers c3
, orders o3
, order_items oi3
, products p2
, product_category_name_translation pcnt
where z2.rfm_score = z3.rfm_score
  and z3.customer_unique_id = c3.customer_unique_id
  and c3.customer_id = o3.customer_id
  and o3.order_id = oi3.order_id
  and oi3.product_id = p2.product_id
  and p2.product_category_name = pcnt.product_category_name
)
, cte_category_sales as (
select product_category_name_english , sum(price) as sales
from cte_rfm_product
group by product_category_name_english
order by 2 desc
limit 10
)
----------------- 3-1. 선별 카테고리의 재구매율 -----------------------
, cte_top_sale_category as (
select distinct c3.customer_unique_id, a2.product_category_name_english
	, to_char(o2.order_purchase_timestamp, 'YYYY') as year 
from cte_category_sales a2
	, product_category_name_translation pcnt2 
	, products p2 
	, order_items oi2 
	, orders o2 
	, customers c3 
where a2.product_category_name_english = pcnt2.product_category_name_english 
  and pcnt2.product_category_name = p2.product_category_name
  and p2.product_id = oi2.product_id
  and o2.order_id = oi2.order_id  
  and c3.customer_id = o2.customer_id 
)
-- self join
, cte_join as (
select a.product_category_name_english, a.customer_unique_id, a.year, b.year as next_year
from cte_top_sale_category a left join cte_top_sale_category b
on a.product_category_name_english = b.product_category_name_english
and a.customer_unique_id = b.customer_unique_id
and a.year::int + 1 = b.year::int
)
, cte_reorder as (
select product_category_name_english, year
	,count(year) as 구매자수
	,count(next_year) as 재구매자수
	,round(count(next_year)/count(year)::numeric *100,2) as num
	,round(count(next_year)/count(year)::numeric *100,2)::varchar||'%' as 재구매율
from cte_join
group by 1, 2
order by 1, 2
)

 

1. 대상 그룹 매출 TOP 10 카테고리

대상 그룹 내에서는 watches_gifts - health_beauty ... 순으로 높은 매출을 갖는다

 

 

2. 대상 그룹 매출 TOP 10 카테고리 재구매율 관련성

재구매율은 매출에 영향을 주지 않는다....

 

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

대상 그룹 내에서 높은 매출을 갖는 지역을 선정, 해당 지역에 집중하고자 한다

 

-- 4조​
/***********************************
 * RFM / 카테고리 / 지역 분석
 ***********************************/
with cte_main as (
select distinct o.order_id
	 , c.customer_unique_id
	 , o.order_status
	 , date_trunc('day', o.order_purchase_timestamp) as order_date
	 , oi.price
from orders o
	 inner join customers c on o.customer_id = c.customer_id
	 inner join order_items oi on o.order_id = oi.order_id
	 left join products p on oi.product_id = p.product_id
	 left join product_category_name_translation pc on p.product_category_name = pc.product_category_name
	 left join reviews r on o.order_id = r.order_id
where order_status = 'delivered'
)
---------- 1. RFM 구하기 -----------
-- 1. r 구하기 위한 max order_date
, cte_customer_maxo as (
select *
	 , MAX(order_date) over() as maxo
from cte_main
)
-- 2. max(order_date) - order_date 테이블
, 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
order by 2 asc, 3 desc, 4 desc
)
-- 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
)
-- 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
)
---------- 2. 고객 등급 별 매출액 및 인당 매출액 구하기 -----------
-- 1. 매출 구하기 위해 필요한 컬럼(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
)
---------------- RFM 테이블 정리 --------------------
-- *cte_customer_status_amount2 : rfm_score 별 총구매액, 고객수, 평균구매액, 평균최근구매일
-- rfm_customer_status : customer 별 r,f,m, rfm_score 수치 확인
-- cte_final2 : rfm_score 별 고객 수
-- cte_final1 : rfm_score 별 r,f,m 수치 별 고객 수
---------- 3. 카테고리 분석 -----------
-- 1. 매출 구성비를 알아보자
, 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
)
-- 2. 고객 선별 : 총 매출의 약 78% 차지하는 score층
, cte_select_score as (
select *
from cte_ratio_agg
where ratio_agg <= 80
)
-- 3. 매출 top10 카테고리
, cte_rfm_product as (
select pcnt.product_category_name_english
, oi3.price, c3.customer_city 
, z3.*
from cte_select_score z2
, cte_customer_status_amount z3
, customers c3
, orders o3
, order_items oi3
, products p2
, product_category_name_translation pcnt
where z2.rfm_score = z3.rfm_score
  and z3.customer_unique_id = c3.customer_unique_id
  and c3.customer_id = o3.customer_id
  and o3.order_id = oi3.order_id
  and oi3.product_id = p2.product_id
  and p2.product_category_name = pcnt.product_category_name
)
, cte_category_sales as (
select product_category_name_english , sum(price) as sales
from cte_rfm_product
group by product_category_name_english
order by 2 desc
limit 10
)
----------------- 3-1. 선별 카테고리의 재구매율 -----------------------
, cte_top_sale_category as (
select distinct c3.customer_unique_id, a2.product_category_name_english
	, to_char(o2.order_purchase_timestamp, 'YYYY') as year 
from cte_category_sales a2
	, product_category_name_translation pcnt2 
	, products p2 
	, order_items oi2 
	, orders o2 
	, customers c3 
where a2.product_category_name_english = pcnt2.product_category_name_english 
  and pcnt2.product_category_name = p2.product_category_name
  and p2.product_id = oi2.product_id
  and o2.order_id = oi2.order_id  
  and c3.customer_id = o2.customer_id 
)
-- self join
, cte_join as (
select a.product_category_name_english, a.customer_unique_id, a.year, b.year as next_year
from cte_top_sale_category a left join cte_top_sale_category b
on a.product_category_name_english = b.product_category_name_english
and a.customer_unique_id = b.customer_unique_id
and a.year::int + 1 = b.year::int
)
, cte_reorder as (
select product_category_name_english, year
	,count(year) as 구매자수
	,count(next_year) as 재구매자수
	,round(count(next_year)/count(year)::numeric *100,2) as num
	,round(count(next_year)/count(year)::numeric *100,2)::varchar||'%' as 재구매율
from cte_join
group by 1, 2
order by 1, 2
)
---------- 4. 지역 분석 -----------
-- 1. 카테고리 별 도시 별 구매 횟수
, cte_city_category as (
select a.product_category_name_english, b.customer_city, a.sales, count(*) as 구매횟수
from cte_category_sales a, cte_rfm_product b
where a.product_category_name_english = b.product_category_name_english
group by 1,2,3
order by 1, 3 desc
)
-- 2. 순위 구하기
, cte_city_category_rank as (
select *
	, row_number() over(partition by product_category_name_english order by 구매횟수 desc) as rank
from cte_city_category
)
-- 3. TOP3 도시 피벗하기
select product_category_name_english
	, max(case when rank=1 then customer_city||'('||구매횟수||')' end) as "1위"
	, max(case when rank=2 then customer_city||'('||구매횟수||')' end) as "2위"
	, max(case when rank=3 then customer_city||'('||구매횟수||')' end) as "3위"
	, sales
from cte_city_category_rank
group by 1,5
order by 5 desc;

위와 같은 상위 3개 지역에 60% 이상 

 

4.4 적절한 배송 기간은?

매출액을 최대한 끌어올 수 있는 적절한 배송기간은?

적절한 배송 기간을 도출 후 분석해 판매할 카테고리, 지역 등을 구체화하고자 한다.

 

/***********************************
 * 예상배송기간, 주문건수 상관관계
 ***********************************/
with
cte_customers as (
select o.order_purchase_timestamp as 구매일시, o.order_id, o.order_estimated_delivery_date as 예상배송일시
	,op.payment_value
	,c.customer_unique_id
	,p.product_category_name , pcnt.product_category_name_english 
	,oi.price 
from orders o , order_payments op , customers c , products p , order_items oi , product_category_name_translation pcnt 
where o.order_id = op.order_id 
and o.customer_id = c.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_select as (
select to_char(예상배송일시-구매일시, 'dd') as 예상배송기간
	,count(distinct order_id) as 주문건수
from cte_customers
group by 1
order by 1
)
-- 예상배송기간, 주문건수 간 상관관계 도출
, cte_corr as (
select corr(예상배송기간::numeric , 주문건수) as "예상배송기간-주문건수 상관관계"
from cte_select
where 예상배송기간::numeric <= 20
)
select * 
from cte_customers;

 

- 예상배송기간과 주문건수의 상관관계 : 0.96 (20일 이하)
- 예상배송기간이 20일 이상일 경우 주문 건수 하락
→ 예상배송기간이 20일 이내 권장

 

5. 결론

📌 판매해야 할 카테고리
FURNITURE_DECOR, COMPUTERS_ACCESSORIES, WATCHES_GIFTS,
TOYS, SPORTS_LEISURE, COOL_STUFF, HEALTH_BEAUTY,
BED_BATH_TABLE, AUTO, HOUSEWARES

📌 주력해야 할 도시

SAO PAULO, RIO DE JANEIRO, BELO HORIAONTE

 

📌 예상 배송 기간은 20일 이내로 

 

---

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

 

📌 SQL 심화 학습도 거의 막바지에 다다랐다! 이번 미니 프로젝트에서는 SQL 활용한 분석 프로세스를 전반적으로 학습했으며,

그 과정에서 이전에 배웠던 심화 쿼리들을 복습할 수 있었다.

최종 평가 및 해커톤 전에 분석 프로세스, 기술적인 부분 두 가지를 모두 잡고 갈 수 있었던 시간이라 큰 의미가 있었고 SQL 쿼리 짜는 게 점점 익숙해지면서 더더욱 분석에 재미가 붙어서 즐거웠다😋

 

추가로 아직 SQL로 구현해보지 못한 다양한 분석 기법들이 존재하고,

분석 프로세스 중 목적 정의 부분을 창의력 있게 풀어가지 못한다 느껴지기에,

다양한 분야의 데이터에 대해 어울리는 분석 이론을 적용하여 분석 프로세스를 익히는 학습이 필요하다 생각된다!!!💪🏻

 

 

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