물류 창고 재고관리 데이터분석 담당자가 되었다고 가정해보자. 담당자인 나는악성 재고 파악과 관리방안 플랜 수립이라는 프로젝트를 진행한다. 목표는 악성 재고 현황 파악 및 최소화 플랜 수립이다. 다음과 같은 포인트에서 분석을 진행해볼 수 있다.
일반 재고와 악성 재고의 비율은?
악성 재고의 추산 가치는?
해당 악성 재고가 창고에 보관된 기간은?
악성 재고를 모두 팔려면 시간이 얼마나 필요한가?
악성 재고 내에서도 등급을 매겨서 관리해볼 수 있을까?
중요하게 봐야 할 지표는 무엇이 있을까?
입고량, 판매량, 재고(입고량-판매량) 권장 판매가(List price), 실 판매가(Net price), SKU, Unit quantity, DOC(Day of Coverage, 재고량/하루 판매량) 등.
DOC에 대해 좀 더 알아보자. DOC가 낮다-> 현 재고로 커버할 수 있는 일수가 적다 ->재고 전환율이 높다. DOC가 높다-> 현 재고로 커버할 수 있는 일수가 많다 ->재고 전환율이 낮다. =>DOC가 낮은 상품은 높은 상품에 비해 잘 팔리는 상품이다 => DOC가 낮은 상품의 발주 주기를, 높은 상품에 비해 짧게 가져가야 한다 => DOC가 낮은 상품을, 높은 상품에 비해 창고 바깥에 배치하는게 좋다 => DOC가 높은 상품이 낮은 상품에 비해 악성 재고가 될 확률이 높다.
이제 데이터를 SQL을 통해 분석해보도록 하자. DBeaver를 사용한다.
SQL 분석
데이터 살펴보기(재고 파악)
재고 유형과 sold flag 별 재고 수량 파악하기.
active: 활성재고 historical: 비활성재고 soldflag: 6개월 내 판매 이력 있으면 1, 아니면 0 itemcount: 재고 수량 soldcount: 판매 수량
활성재고의 경우는 계속 잘 팔리는 재고이기 때문에 soldflag나 sold_count 데이터는 없고 보유한 재고량만 데이터로 남아있는 것을 볼 수 있다.
활성재고/비활성재고로 나누어 보면 아래와 같고
활성/비활성/악성재고로 나누어 보면 아래와 같다.
각각 어느 정도의 비중을 차지하는지도 표시해보면?
Total 값도 표시해주고 싶다.
6개월 내 판매 이력이 있는 비활성 재고에 대해서 파악하기
6개월 내 판매이력이 있는 비활성재고는 82만개 정도가 쌓여 있으며, 24000개 정도가 팔렸다. 이 추세가 계속된다면 다 파는 데에 201개월 정도가 걸릴 것으로 예상된다.
재고 가치 파악 PriceReg: 물품 가격(원가)
비활성재고와 활성재고의 재고 가치를 파악해봤을 때, 6개월 내 판매이력이 없는 비활성재고의 가치가 활성재고 가치의 절반 이상임을 확인할 수 있다.
악성재고 원인 파악
재고가 쌓이는 원인이 뭘까? 파악해보자.
가격
LowNetPrice: 제품의 실제 판매가격(=권장소비자가격)
아래과 같은 것들을 알 수 있다 1) 비활성재고에 비해 활성재고 원가가 낮다. 2) 판매 이력이 있는 비활성재고의 할인율은 거의 50%이다. 할인율이 높아서 그나마 좀 팔린 것일까? 3) 악성재고(판매이력 없는 비활성재고)의 경우, 할인율을 좀 높여야 판매가 될 것 같다. 4) 활성재고의 할인율이 그리 높지 않은 것은, 이미 잘 팔리고 있는 제품이라서인 듯 하다.
신제품 출시
NewReleaseFlag: 1(신제품 출시 or 업그레이드 후 재출시), 0(이미 최신 제품)
어떤 제품의 신 버전 or 업그레이드 버전이 출시되었다면, 구버전은 비활성재고로 남게 될 가능성이 있다. 확인해보자. 1) new relase flag가 2개인 제품을 추려낸 후
2) 해당 sku 중 하나에 관한 데이터 직접 조회
new release flag가 1인 제품 => 최신 버전이 출시된 제품 => 구버전 제품 new release flag가 0인 제품 => 최신 버전 제품 구버전은 비활성재고, 신버전은 활성재고로 분류되어 있는 것을 볼 수 있다. 즉 같은 제품이라면 구버전은 신버전에 비해 안 팔린다는 말이다. 당연하겠지만...
창고 보관 기간
창고에 오래 있던 제품은 비활성재고일 확률이 높을까? ReleaseYear: 창고 입고년도. 현재(2024년) 기준으로 제품들이 창고에 얼마나 있었는지 확인해보자
입고년도와 보관기간은 활성재고 -> 비활성재고 -> 악성재고 순으로 짧다. 당연한 결과인 듯... 안 팔리니까 창고에 오래 보관되어있겠지.
재고 관리를 위한 SKU Grade 기획
창고 계약 기간이 5년(=60개월) 남은 상황이라고 가정해 보자. 이 상황에서 재고관리 데이터분석 담당자인 나는SKU 유형 별로 세분화해서 각 유형에 맞는 관리방안을 수립하고자 한다. S -> A -> B -> ... -> F 1) 활성재고[S, A, B] => 물량 기준 2) 비활성재고[C, D, E] => 재고 보유량과 재고 전환율 기준 3) 악성재고 : 그냥 F
해당 기준에 맞춰 결과를 출력하고, 출력 결과를 새 table로 만들어보자. 쿼리는 다음과 같다.
CREATE TABLE Inventory.SKU_grade AS
(
WITH sub_table AS (
SELECT sku_number AS SKU
, CASE WHEN file_type = 'Historical' AND SoldFlag=1 THEN '2. Non-Activated'
WHEN file_type = 'Historical' AND SoldFlag=0 THEN '3. BAD'
WHEN file_type = 'Active' THEN '1. Activated'
ELSE '기타'
END AS inventory_type
, itemcount
, soldcount
, round(itemcount/if(soldcount = 0, NULL, soldcount/6), 1) AS MOC
FROM Inventory
ORDER BY 1,2
), pct_rank AS (
SELECT *
, percent_rank() OVER (PARTITION BY inventory_type ORDER BY itemcount desc) AS pct
FROM sub_table
), sku_grade AS (
SELECT *
, CASE WHEN inventory_type = '1. Activated' AND pct < 0.1 THEN 'S'
WHEN inventory_type = '1. Activated' AND pct between 0.1 AND 0.5 THEN 'A'
WHEN inventory_type = '1. Activated' AND pct > 0.5 THEN 'B'
WHEN inventory_type = '2. Non-Activated' AND pct < 0.5 AND MOC < 60 THEN 'C'
WHEN inventory_type = '2. Non-Activated' AND pct > 0.5 AND MOC < 60 THEN 'D'
WHEN inventory_type = '2. Non-Activated' AND MOC >= 60 THEN 'E'
WHEN inventory_type = '3. BAD' THEN 'F'
END AS SKU_grade
FROM pct_rank
ORDER BY inventory_type, pct
)
SELECT SKU
, inventory_type
, SKU_grade
FROM sku_grade
);
