[TIL] 53일차 TIL(20230420) - SQL 미니 프로젝트 2

[TIL] 53일차 TIL(20230420) - SQL 미니 프로젝트 2

 

 

📗 SQL 미니 프로젝트

 

✅ 분석 개요

 

✅ 물류창고 지역 선정

전날 추출한 전체 데이터를 바탕으로 먼저 물류창고를 신설할만한 지역을 찾기 위한 분석을 진행했다.

 

먼저 지역별 매출 발생 현황을 살펴보고, 이와 더불어 운임비와 평균 배송 소요기간을 살폈다.

select
	customer_state,
	sum(sum_price) sum_state_sales,
	round(avg(sum_freight_value)::numeric, 2) avg_state_freight_value,
	round(avg(sum_배송소요기간)::numeric, 2) avg_배송소요기간
from cte_state_order_sales
group by 1
order by 2 desc

 

이 지표들을 바탕으로 브라질의 전체 지역을 3개의 그룹으로 나눌 수 있었다.

가장 많은 매출이 발생하며 배송기간이 가장 짧은 편인 A구역,

수도권외 지역이며 배송기간이 다소 긴 편이나, 매출이 어느정도 발생하는 B구역,

배송시간이 상당히 길며 매출은 가장 적게 발생하는 C구역으로 나누었다.

 

 

시각화를 통해 기존에 물류창고를 건설하려고 했던 목적에 B구역이 가장 적합하다는 사실을 확인할 수 있었다.

최종적인 물류창고의 보급 범위는 B구역의 중심에 위치한 BA 주를 기준으로 반경 620km에 위치한 모든 주들로 설정했으며, 해당 주들은 다음과 같다.

 

 

✅ 물류창고 배치 상품 선정

물류창고의 위치와 물류창고의 보급 범위를 설정한 뒤, 해당 물류창고에 놓을 상품들을 선정하는 과정을 거쳤다. 초반에 기획한 것은 '상품'을 기준으로 하는 것이었으나, 상품의 수가 너무 많아 전반적인 구매 횟수가 너무 적었고, 따라서 상품보다 조금 큰 범위인 카테고리를 기준으로 살펴보는 것으로 정했다.

 

구체적으로 배치 카테고리를 선정하기 위해 ABC 분석을 활용했다. 카테고리의 수만 해도 최대 73개가 존재했으나, 모든 카테고리에서 좋은 매출을 올리고 있는 것은 아니었고 상위 카테고리들은 각각 매출의 10%를 담당하는 등 특정 카테고리에 매출이 집중되어있는 경향을 찾을 수 있었다. 따라서 매출의 대부분을 담당하는 소수 카테고리를 찾아내 집중하자는 ABC분석을 활용하기에 적합해보였고 이를 사용했다.

 

cte_b_category_sales as (
select
	카테고리명,
	round(sum(price)::numeric, 2) sum_b_category_sales,
	sum(round(sum(price)::numeric, 2)) over () sum_b_total_sales,
	round((round(sum(price)::numeric, 2) / sum(round(sum(price)::numeric, 2)) over () * 100)::numeric, 2) 구성비
from cte_table
WHERE customer_state in ('AL', 'BA', 'CE', 'DF', 'ES', 'GO', 'MA', 'MG', 'PB', 'PE', 'PI', 'RN', 'SE', 'TO')
GROUP BY 1
order by 3 desc
)
select
	*,
	sum(구성비) over (order by 구성비 desc) as 구성비누계,		
	CASE
	WHEN sum(구성비) over (order by 구성비 desc) <= 60 THEN 'A'
	WHEN sum(구성비) over (order by 구성비 desc) <= 90 THEN 'B'
	ELSE 'C'
	END AS GROUP
from cte_b_category_sales

 

 

 

결론적으로 집중해야 할 A그룹의 카테고리는 다음의 9개로 선정되었다. 해당 9개의 카테고리는 물류창고 담당 지역의 60%의 매출을 차지하는 카테고리이다.

 

 

✅ 결론

• 매출이 일정 이상 수준으로 발생하며, 운임료가 수도권 대비 많이 부과되는 B그룹의 중심인 'BA' 지역에 물류창고를 신설
• 물류창고를 통해 BA기준 반경 620km인 주의 물류를 담당
• 해당 주들을 일괄해 상위 60%의 매출을 형성하는 제품들을 물류창고에 배치

 

 

---