[Mini Team Project] 상품 판매 데이터 분석에 따른 판매 상품 추천

Project

[Mini Team Project] 상품 판매 데이터 분석에 따른 판매 상품 추천

여연찌 2024. 4. 19. 16:35

프로젝트명: 상품 판매 데이터 분석에 따른 판매 상품 추천
기간: 2024-04-15~19
활용 데이터: https://www.kaggle.com/datasets/ammaraahmad/us-ecommerce-record-2020

United States E-Commerce records 2020

15+ columns on Order history

www.kaggle.com

주제를 정하기 전 해당 데이터가 가지고 있는 정보들을 조회해보고
어떤 값을 빼내올 수 있는지를 분석해보았다.
기본적으로 월별,주(state)별, 분기별, 카테고리별 등으로 구분하여

매출액, 수익 등을 분석해보았는데
그 중 가장 데이터를 많이 확보할 수 있는 'Technology' 카테고리를 선정하여 조금더 깊은 분석을 해보고자 하였다

1. 개요

배경: 전자 제품 및 기타 부속제품을 판매하는 유통업체가 아마존에 입점하려는 상황
목적: A 사에서 판매되는 제품들을 분석하여 처음 입점하여 판매할 세부 분야 및 제품 선정
필요성: 입점 후 발생할 수 있는 리스크를 최소화하고 안정적인 이익 확보를 위한 사전 조사 필요
프로젝트 진행과정: 자료 수집 및 데이터 전처리 > 데이터 분석 > 결과 분석 및 결론 도출

# A사로 입점하려고 하는 가상의 TECH 판매업자의 요청사항을 들어주기 위한 분석팀이라고 가정하고, 입점 후 리스크를 최소화하고 안정적인 이익 확보를 위한 사전 조사를 진행하였다.
# 클라이언트의 요청사항: A사에 입점 시 판매하면 좋을 상품 추천

2. 수집 및 전처리

데이터 수집: kaggle
데이터 형식: 미국 사무용품을 판매하는 A사의 2020년 데이터 19개 컬럼과 3,282 개의 데이터로 구성
데이터전처리:

① kaggle 사이트 내 기초 분석 내용을 통해 결측치가 없는 것을 확인했다.

# 위 이미지와 같이 'Missing' 값이 0으로 되어 있으면 결측치가 없는 것
# 모든 칼럼을 확인해본 결과 전부 0으로 표시되어 있었다.

② 팀원들과의 원활한 데이터 분석을 위해 활용데이터의 테이블명과 컬럼명, 데이터타입을 통일

us2020

Order_Date - timestamp
Row_ID - varchar(50)
Order_ID - varchar(50)
Ship_Mode - varchar(50)
Customer_ID - varchar(50)
Segment - varchar(50)
Country - varchar(50)
City - varchar(50)
State - varchar(50)
Postal_Code - varchar(50)
Region - varchar(50)
Product_ID - varchar(50)
Category - varchar(50)
SubCategory - varchar(50)
Product_Name - text
Sales - double
Quantity - int
Discount - double
Profit - double

③ 이상치 발견

# Sales 컬럼에 숫자가 아닌 Blue 등의 데이터 발견
# Product Name 컬럼에 분할되지 않은 데이터 발견

SELECT *
FROM US2020
WHERE LENGTH(Product_Name) > 100;

LENGTH 함수를 사용하여 길이가 긴 행을 확인 후 엑셀에서 분할
> 전처리 결과 데이터 갯수는 3,282개 에서 총 3,311개로 추가됨

3. 데이터분석

Technology 카테고리내에서 Sub-Category 선정

① 총 매출액, 총 판매량, 총 수익금, 수익률, 월별 수익률에 대한 분산

#technology 내의 sub-category들의 총 매출, 총 판매량, 총 수익, 총 수익률
SELECT Category, SubCategory,
	   TRUNCATE(SUM(Sales),0) tot_sales,
	   SUM(Quantity) tot_quantity,
	   TRUNCATE(SUM(Profit),0) tot_profit,
	   ROUND((SUM(Profit) / SUM(Sales) * 100),2) Profit_ratio
FROM US2020
where Category = 'Technology'
GROUP BY 2
ORDER BY 3 DESC, 4 DESC;


#technology 내의 sub-category들의 월별 수익률의 분산
SELECT subcategory,round(VAR_samp(profit_rate)) AS profit_rate_variance
FROM
  (SELECT CONCAT(CAST(MONTH(Order_Date) AS char), "월") month,
          SubCategory,
          SUM(sales) AS sales,
          SUM(quantity) AS quantity,
          SUM(profit) AS profit,
          SUM(profit)/SUM(sales)*100 AS profit_rate
   FROM us2020 u
   WHERE category="Technology"
   GROUP BY 1,2) A
GROUP BY subcategory;

② 최고점 100으로 환산한 상대수치의 평균

Sub-Category구분	총 매출액	총 판매량	총 수익금	수익률	월별 수익률 분산	평균
Phones	105,340	1,090	12,849	12.2	656.1	71.4
Copiers	62,899	73	25,031	39.8	25.3	73.3
Accessories	59,946	1,079	15,672	26.1	29.2	76.8
Machines	43,544	121	-2,869	-6.6	35.5	24.8

③ 'Copiers' 도 고려대상 중 하나였으나 'Accessories' 를 선정한 사유

#고려대상 중 하나였던 Copiers 서브카테고리의 월별 매출, 판매량, 수익, 수익률
select CONCAT(CAST(MONTH(Order_Date) AS char), "월") month ,
       round(sum(Sales),2) Total_Sales,
       sum(Quantity) Total_Quantity,
       round(sum(Profit),2) Total_Profit,
       round(sum(Profit)/sum(Sales)*100,2) Profit_rate,
       SubCategory 
from US2020
where SubCategory = 'Copiers'
group by 1,6

# 'Copiers' 의 경우 판매량이 없었던 달도 존재하는 것으로 보아 수요가 불규칙할 것으로 예상
# 'Accessories' 에 비하여 약 15배 가량 떨어지는 총 판매량

> 안정적인 이윤 추구와 데이터분석을 위한 충분한 양의 데이터 확보를 위해 'Accessories' 선정

판매 제품 선정

① Product Name 컬럼에서 주요하게 중복되는 제품군 그룹화

	WHERE SubCategory = 'Accessories'
),
categorised AS (
	SELECT 
	(case 
		WHEN Product_Name LIKE "%key%" or 
        	 Product_Name LIKE "%trackball%" or 
        	 Product_Name LIKE "%mouse%" or 
         	 Product_Name LIKE "%gameboard%" THEN "키보드&마우스"
		WHEN product_name LIKE "%drive%" or 
        	 Product_Name LIKE "%card%" or 
             Product_Name LIKE "%Data Traveler%" THEN "스토리지"
		WHEN product_name LIKE "%dvd%" or 
        	 Product_Name LIKE "%CD%" or 
             Product_Name LIKE "%disc%" THEN "DVD"
		WHEN product_name LIKE "%head%" or 
        	 Product_Name LIKE "%speak%" THEN "음향기기"
		WHEN product_name LIKE "%pad%" THEN "단말기"
	ELSE "etc."  end) acs_type,

# 키보드&마우스, 스토리지, 음향기기, 단말기, etc
# 'etc' 제품 의 경우 클라이언트가 원하는 제품군을 명확히 제시하지 못하여 이후 분석 제외

② 그룹화한 판매 제품의 총 매출액, 총 판매량, 총 수익금, 수익률, 월별 수익률에 대한 분산

③ 최고점 100으로 환산한 상대수치의 평균

acs_type구분	총 매출액	총 판매량	총 수익금	수익률	월별 수익률 분산	평균
키보드&마우스	18,659	379	4,270	22.9	63.0	83.2
스토리지	12,247	329	2,741	22.4	148.0	69.5
DVD	1,495	108	420	28.1	144.0	44.2
음향기기	21,991	162	6,545	29.8	122.0	85.5
단말기	938	14	89	9.5	435.0	26.2

④ acs_type 의 월별 매출액, 수익금 누적 추이 파악

# (line chart 제작용) acs_type별 월별 매출액 및 월별 수익금 추이 파악 
WITH price AS (
	SELECT  Order_Date, SubCategory, Product_ID, Product_Name, Sales, Quantity, Discount,
		(Sales / Quantity) sales_price, # 할인 먹인 개당 가격 
		(Sales / Quantity / (1 - Discount)) org_price, # 정가
		(Profit / Quantity) per1_profit # 개당 수익 
	FROM US2020
	WHERE SubCategory = 'Accessories'
),
categorised AS (
	SELECT 
	(case 
		WHEN Product_Name LIKE "%key%" or Product_Name LIKE "%trackball%" or Product_Name LIKE "%mouse%" or Product_Name LIKE "%gameboard%" THEN "키보드&마우스"
		WHEN product_name LIKE "%drive%" or Product_Name LIKE "%card%" or Product_Name LIKE "%Data Traveler%" THEN "스토리지"
		WHEN product_name LIKE "%dvd%" or Product_Name LIKE "%CD%" or Product_Name LIKE "%disc%" THEN "DVD"
		WHEN product_name LIKE "%head%" or Product_Name LIKE "%speak%" THEN "음향기기"
		WHEN product_name LIKE "%pad%" THEN "단말기"
	ELSE "etc."  end) acs_type, 
	Product_ID,
	Product_Name,
	Order_Date,
	org_price, 
	sales_price,
	per1_profit,
	Quantity
	FROM price
)
SELECT 
	MONTH(Order_Date) MONTH,
	acs_type,
	SUM(sales_price * Quantity) monthly_sales,
	SUM(per1_profit * Quantity) monthly_profit
FROM categorised
GROUP BY 1, 2
ORDER BY 1 ,4 DESC;

# 고려대상 1~2순위인 음향기기와 키보드&마우스 의 누적 그래프로 확인

⑤ acs_type 의 월별 수익률의 분산

#Accessories 내의 제품 종류들에 대한 월별 수익률의 분산
select acs_type,round(VAR_samp(profit_rate)) AS profit_rate_variance
from
(select case when Product_Name like "%key%" or Product_Name like "%trackball%" or Product_Name like "%mouse%" or Product_Name like "%gameboard%" then "키보드마우스"
when product_name like "%drive%" or Product_Name like "%card%" or Product_Name like "%Data Traveler%" then "스토리지"
when product_name like "%dvd%" or Product_Name like "%CD%" or Product_Name like "%disc%" then "DVD"
when product_name like "%head%" or Product_Name like "%speak%" then "음향기기"
when product_name like "%pad%" then "단말기"
else "etc." end acs_type
,CASE
              WHEN MONTH(Order_Date) = 1 THEN "1월"
              WHEN MONTH(Order_Date) = 2 THEN "2월"
              WHEN MONTH(Order_Date) = 3 THEN "3월"
              WHEN MONTH(Order_Date) = 4 THEN "4월"
              WHEN MONTH(Order_Date) = 5 THEN "5월"
              WHEN MONTH(Order_Date) = 6 THEN "6월"
              WHEN MONTH(Order_Date) = 7 THEN "7월"
              WHEN MONTH(Order_Date) = 8 THEN "8월"
              WHEN MONTH(Order_Date) = 9 THEN "9월"
              WHEN MONTH(Order_Date) = 10 THEN "10월"
              WHEN MONTH(Order_Date) = 11 THEN "11월"
              WHEN MONTH(Order_Date) = 12 THEN "12월"
          END AS month
, round(sum(sales)) sales, round(sum(quantity)) quantity, round(sum(profit)) profit, sum(profit)/sum(sales)*100 profit_rate
FROM us2020
where SubCategory ="Accessories"
group by 1,2
) A
group by 1

> 월별 매출액과 수익금의 누적그래프를 추가 분석한 결과 음향기기가 누적합이 더 컸으며,
월별 수익률 추이의 경우 4월을 제외한다면 키보드&마우스보다 높거나 비슷한 수익률을 유지하는
음향기기 판매를 추천할 수 있겠다는 결론을 도출

음향기기제품을 브랜드별로 분석

① 브랜드별 구분

SELECT substring_index(REPLACE(REPLACE(Product_Name,'�',' '),'?',' '),' ', 1) Product_Name 
FROM us2020 u 
WHERE SubCategory = 'Accessories'

# Product_Name 안에 올바른 데이터가 아닌, 특수 문자 등으로 구분이 되어있는 데이터가 몇개 존재함을 확인 후 데이터의 문자열을 변경 후 브랜드 명 추출

AS-IS

TO-BE

② 각 브랜드의 제품 1개당 평균 가격, 평균 수익, 평균 수익률, 총 판매량

SELECT 
	SUBSTRING_INDEX(Product_Name, ' ', 1) '브랜드명',
	AVG(sales_price) '개당 평균 가',
	AVG(per1profit) '개당 평균 수익',
	AVG(profit_ratio) '개당 평균 수익률',
	SUM(Quantity)
FROM (
	SELECT 
		(case 
			WHEN Product_Name LIKE "%key%" or Product_Name LIKE "%trackball%" or Product_Name LIKE "%mouse%" or Product_Name LIKE "%gameboard%" THEN "키보드&마우스"
			WHEN product_name LIKE "%drive%" or Product_Name LIKE "%card%" or Product_Name LIKE "%Data Traveler%" THEN "스토리지"
			WHEN product_name LIKE "%dvd%" or Product_Name LIKE "%CD%" or Product_Name LIKE "%disc%" THEN "DVD"
			WHEN product_name LIKE "%head%" or Product_Name LIKE "%speak%" THEN "음향기기"
			WHEN product_name LIKE "%pad%" THEN "단말기"
		ELSE "etc." end) acs_type,
		Product_ID, 
		REPLACE(REPLACE(Product_Name, '?', ' '), '�', ' ') Product_Name,
		(Sales / Quantity) sales_price,
		(Profit / Quantity) per1profit,
		(Profit / Sales) profit_ratio,
		Quantity
	FROM e_commerce.US2020
	WHERE SubCategory = 'Accessories'
)tbl
WHERE acs_type = '음향기기'
GROUP BY 1
ORDER BY 5 DESC;

> 클라이언트가 판매하고자 하는 물품들과 A사에서 판매중인 데이터를 비교할 수 있도록

3가지 브랜드의 데이터 제공

4. 한계점 및 아쉬운 점

2020년 1개년의 수치

# 데이터 양이 부족하여 월별, 연별 산출값에 대한 일관성 파악이 어려움

판매처 자료의 부재

# 판매처에 대한 데이터가 없어 타 경쟁사의 정보, 예상매출, 수익 등에 대한 분석이 불가

고객 데이터의 한계

# Segment 가 기업, 홈 오피스, 개인 3개로만 구분되어 있으며 고객의 성별, 나이, 선호도 등 구체적 정보가 부족하여 고객 분류에 대한 접근이 어려움

칼럼 정보 설명 부족

# 컬럼에 대한 정보가 없어 해당 컬럼의 데이터가 어떤 값을 나타내는지 정확하지 않아, 추가적인 확인이 필요 했음
# 'Sales' 컬럼의 데이터 값이 제품 1개당의 금액인지 실제 판매금액인지 확인

# 물건 1개당 가격 구하기 
SELECT Order_Date, Sales, Quantity, Discount,
	(Sales / Quantity) per1Sales, # 할인 먹인 개당 가격 
	(Sales / Quantity / (1 - Discount)) org_price # 정가 
FROM US2020
WHERE Product_ID = 'FUR-FU-10003878'
ORDER BY 1;

> 'Sales' 컬럼의 값은 할인율이 적용된 총 판매 매출인 것으로 확인되었다.

KPT 회고

https://lyj-01.tistory.com/158

KPT 회고 - 상품 판매 데이터 분석에 따른 판매 상품 추천

Keep 팀원분들의 많은 도움들로 무사히 잘 마무리 되었던 팀프로젝트였다.나를 포함한 모든 팀원분들이 EDA 및 프로젝트 결론까지 달려가기 위해 팀프로젝트에 열심히 참여하여

lyj-01.tistory.com

저작자표시 비영리 변경금지

'Project' 카테고리의 다른 글

[Team Project] 성장기 도약의 KEY가 되는 신규 가입자 유치 전략 (3)	2024.08.27
[Team Project] Amazon Fresh 의 미래, 세그먼트별 수익성 개선 전략 수립 (0)	2024.07.12
[Team Project] 구매 성향에 따른 분류와 그에 따른 마케팅 방안 제시 (0)	2024.06.25
[Team Project] 은행 고객데이터를 이용한 서비스 분석 (0)	2024.05.23

현재글[Mini Team Project] 상품 판매 데이터 분석에 따른 판매 상품 추천

데벨챌, 리트코드, JD분석, 팀프로젝트, 데이터넥스트레벨챌린지, 프로젝트, sql, 프로그래머스, 파이썬, 데이터리안, 데이터리안 #데이터넥스트레벨챌린지 #데벨챌, TIL, 스타터노트, 코드카타, ADsP, KPT회고, pandas, WIL, 개인연습, 개인공부,

여연찌

여연찌