주류 산업의 데이터 분석 컨설턴트가 된 본인. 데이터 처리부터, 대시보드로 비즈니스 인사이트를 전달하기까지의 과정을 직접 처리해 보자!
지난 시간엔 아래와 같은 내용에 대해 실습해보았다.
주류 산업의 데이터 분석 컨설턴트가 된 본인이 아래와 같은 업무를 맡았다고 가정한다.
해당 지역(여기서는, 데이터가 수집된 미국 아이오와 주를 의미함)의 주류 판매 시장의 동향은 어떤지 알려달라.
어느 지역에 어떤 상품을 주력으로 팔아야 할 지, 관련 인사이트를 제공해 달라.
이번 시간엔, 지난 시간에 PySpark로 전처리 했던 데이터를 가지고 MySQL을 활용해 조금 더 세부적인 분석을 진행하고자 한다.
Metadata
갑자기 뜬금없이 메타데이터가 나오는가 싶겠지만, 알고 넘어가야 할 내용이다.
메타 데이터란 무엇인가?
데이터에 대해 이해하거나 분류하는 데에 도움을 주는 데이터로, 쉽게 말해"데이터의 데이터"라 고 할 수 있다. 예를 들어파일의 저장 날짜, 종류, 태그 등등이 모두 메타 데이터라 할 수 있다.
그래서, 갑자기 메타데이터에 대한 소개가 왜 나왔는가? 메타데이터를 이용하면 대용량의 데이터-예를 들어 주류 산업의 데이터 컨설턴트인 본인이 다룰 2천만 행이 넘는 데이터-를 효율적으로 압축해서 사용할 수 있기 때문이다
본인이 사용할 데이터에서도, 아래와 같이 메타데이터를 담은 데이터프레임을 따로 생성한 후 활용할 것이다.
각 지역과 store의 수익 구조를 분석해보자!
해보자! MySQL을 사용할 것이다.
점포 매출 분석하기
우선지역별 매출 순위를 알아보자.
DES MOINES가 압도적 1위다. 그 뒤로 CORALVILLE, DEWITT... 이 뒤따른다.
지역별, store별 매출 순위는 어떨까?
DES MOINES에 속한 store가 최상위를 차지한다.
지역별, store별 평균 매출 순위는 어떨까?
지역별 매출 순위와 크게 다른 결과를 보인다. DES MOINES가 4등이 되었고, 5등이던 MOUNT VERNON이 1등이 되었다 이는 무엇을 시사하는가? => DES MOINES에는 점포가 다른 지역에 비해 많다는 것을 알 수 있다.
이번엔매출이 잘 나오는 store가 많은 지역을 찾아보자. => 구체적으로는전체 매출 상위 20위권 store는 어느 지역에 많을까?를 알아보자. => 아무 곳에나 store를 새로 낼 수 없다. 비교적 매출이 잘 나오는 지역에 store를 내는 것이 성공 확률이 높을 것이다.
매출 상위 20곳의 가게 중 절반 이상이 DES MOINES에 속한 가게들이다.
새 점포 개점할 곳 선정하기
위에서 Top20곳의 가게 중 절반 이상이 DES MOINES에 속한다는 것을 확인했다. 그럼 바로 DES MOINES에 새 점포를 내면 되는가? 아니다!
추가로 중요하게 고려해야 할 것이 있는데, 다음의 2가지이다. 1. 해당 지역의 가게들 중 Top20위권의 가게들이 얼마나 많은 비중을 차지하는가 2. Top20위권 가게들의 매출 편차가 어느 정도인가
왜 위 사항들을 고려해야 하나요??
1번의 경우 100곳의 가게 중 Top20위권 가게가 10곳 있는 지역과 20곳의 가게 중 Top20위권 가게가 10곳 있는 지역을 비교하자면, 당연히 후자에 새 점포를 내는 것이 좋을 것이다.
2번의 경우 Top 20위권의 가게 중 10~20위 가게가 A 지역에 있고, 그 비중이 다른 지역보다 높다고 해 보자. 하지만 Top 1~3위 가게는 B 지역에 있는데 1~3위 매출의 합이 전체 20위권 가게들 매출 총합의 90%를 넘는다고 한다면? A 지역에 가게를 내는 것이 옳은 판단인가?
그 후 최대/최소 sales, sales의 표준편차, 최대 sales와 최소 sales의 차이를 출력해보자.
편차가 큰 편이다. => 그러므로, 새 점포 입점의 목적에 따라 다른 지역을 선택해야 한다.
이제, 아래와 같은 결론을 내릴 수 있다. 1)점포 수를 확장하기 위해서는 점포 수도 많고 Top20위권 가게도 많은 DES MOINES에 가게를 내는 것이 좋을 것이고 2)안정적으로 높은 매출을 내기 위해서는 평균 매출이 가장 높았던 MOUNT VERNON에 가게를 내는 것이 좋을 것이다.
지역별 아이템 수익성 분석
수익 잘 나는, 좋은 아이템은 뭘까??
위에서 선택한 2개 도시에 한정해서 알아보자.
2개 지역에서 주류 순위는 다음과 같이 나왔다.
그럼, 지역 각각은 주류 매출 순위 경향이 어떨까? 비슷할까 다를까?
1등은 동일하고, 그 외에도 순위는 다를 지언정 10위권에 겹치는 주류가 몇몇 보인다.
하지만 매출을 보자. DES MOINES와 MOUNT VERNON의 1위는 둘 다 동일한 보드카 종류지만, 매출이 열 배 이상 차이난다.
새로운 곳에서 사업을 하려면 당연히 그 지역에 어떤 제품이 잘 팔리는지도 알아야 하지만, 수익이 잘 나는 제품이 전체 제품의 몇 %를 차지하는지, 몇 종류의 제품을 팔고 있는지, 그 지역의 총 매출 규모나 수익 구조 등의 정보도 알아야 한다.
지역별로 몇 종류의 제품을 파는지 알아보면, 파는 제품 수가 3배나 차이난다.
각 아이템들이 총 매출에 기여하는 비중은 어느 정도일까? 이것을 왜 알아보는가? => 적은 수의 아이템이 매출에 차지하는 비중이 클 수록 사업을 하기가 쉬워 질 것이다 => 해당 아이템들에만 집중에서 사업을 하면 되니까. => 이런 측면에서는 MOUNT VERNON이 이러한 경향이 조금 더 클 것으로 예상된다. 직접 알아보자.
with city_sales as (
select city
, round(sum(SaleDollars)) as sum_sales
from city_data
where city = 'DES MOINES'
or city = 'MOUNT VERNON'
group by city
), city_item_ranks as (
select c.city
, i.ItemDescription as item
, round(sum(c.SaleDollars), 0) as item_total_sales
, rank() over(partition by c.city order by sum(c.SaleDollars) desc) as ranks
from city_data c
left join item_meta i
on c.ItemNumber = i.ItemNumber
where c.city = 'DES MOINES'
or c.city = 'MOUNT VERNON'
group by c.city, i.ItemDescription
order by ranks
)
select r.*
, s.sum_sales
, round(100*r.item_total_sales/s.sum_sales) as item_sales_ratio
from city_item_ranks r
left join city_sales s
on r.city = s.city
10위권까지 보자면, DES MOINES에 비해 MOUNT VERNON의 제품들이 좀 더 많은 매출 파이를 차지하고 있는 것을 볼 수 있다. => 위에서 예상했던 것과 같은 결과가 나왔다.
좀 더 직관적으로 볼까?
with city_sales as (
select city
, round(sum(SaleDollars)) as sum_sales
from city_data
where city = 'DES MOINES'
or city = 'MOUNT VERNON'
group by city
), city_item_ranks as (
select c.city
, i.ItemDescription as item
, round(sum(c.SaleDollars), 0) as item_total_sales
, rank() over(partition by c.city order by sum(c.SaleDollars) desc) as ranks
from city_data c
left join item_meta i
on c.ItemNumber = i.ItemNumber
where c.city = 'DES MOINES'
or c.city = 'MOUNT VERNON'
group by c.city, i.ItemDescription
order by ranks
), city_item_sales_ratio as (
select r.*
, s.sum_sales
, 100*r.item_total_sales/s.sum_sales as item_sales_ratio
from city_item_ranks r
left join city_sales s
on r.city = s.city
)
select city
, round(sum(item_sales_ratio), 2) as Top10_item_sales_ratio
from city_item_sales_ratio
where ranks <= 10
group by city;
도시별 10위권 내의 아이템 판매수익은 전체 수익에 비해 위와 같은 파이를 차지한다. MOUNT VERNON에서의 수익이 DES MOINES의 2배가량 된다. => 즉 MOUNT VERNON에서는 좀 더 적은 아이템에 집중하더라도 수익이 많이 날 것을 기대할 수 있다..
