[코멘토] SQL 입문부터 활용까지 - 데이터 분석 보고서 작성과 대시보드 개발 3주차 과제

해당 포스팅은 [코멘토] SQL 입문부터 활용까지 - 데이터 분석 보고서 작성과 대시보드 개발을 수강하면서 했던 과제를 업로드한 것입니다. (본 코드는 MySQL 5.7 환경에서 작성되었습니다.)

---

 

 

 

데이터 소개

 

• 가상의 Northwind 식품회사의 데이터
• DB는 직원, 고객, 상품, 주문, 발주 등 총 20개의 테이블로 구성됨
• 데이터의 경우 2006년 1월부터 6월까지의 주문에 관한 데이터로 구성

 

 

테이블 소개

 

• products : 식품회사에서 취급하는 상품 정보를 담은 테이블.
  • 45개의 레코드가 존재.
  • 제품 코드, 제품명, 가격, 카테고리 등을 보여줌.
• orders : 고객이 주문한 정보를 담은 테이블.
  • 2006년 1월부터 6월까지의 주문에 관한 데이터로 총 48개의 레코드가 존재.
  • 고객이 어떤 직원에게 주문을 했으며 주문한 날짜, 배송된 날짜, 배송원, 수령인, 배송 주소, 배송 도시, 배송상태 등을 보여줌. 
• order_details : orders 테이블의 세부 사항을 나타내는 테이블.
  • 58개의 레코드 존재.
  • 고객이 어떤 제품을 구매했는지, 구매 수량, 가격 등을 보여줌.

 

 

1. 분석 주제 설정

 

3주차 과제는 데이터에 대한 가설을 3개 정하고, 그 가설에 대한 핵심 지표 및 보조 지표를 설정한 뒤, 그 지표를 분석해 가설에 대한 분석 보고서를 작성하는 것이었습니다.

또한 각 가설은 서로 연결되는 하나의 흐름으로 연결되어야 한다는 전제도 주어졌습니다. 

 

사실 강의를 신청하기 전부터 3주차 과제 설명을 보고 '아 이거 어렵겠는데...'라는 생각을 가지고 있었던 터라

3주차가 되자마자 드디어 왔다...! 싶었네요 

 

해당 과제를 수행할 때 가장 어려웠던 부분은 역시 분석 주제 설정이었습니다. 처음부터 막힘 현상 발생...

그래서 한 2,3일은 데이터를 가지고 그냥 이것저것 출력해보기만 했습니다

 

일주일에 절반쯤 남았을 때 이제 정말 분석을 진행해야 한다라는 압박이 왔을 때 그제야 식품회사의 특징에 초점을 맞춰보자는 아이디어가 떠올랐습니다.  

 

저는 각 식품회사 브랜드를 생각하면 주로 생각나는 제품이 존재합니다.

케찹이나 카레하면 오뚜기, 간장하면 샘표, 밀가루하면 곰표(대한제분) 등등 

그래서 그런지 케찹을 구매할 때면 당연히 오뚜기, 간장을 구매할 때면 샘표 제품을 구매했습니다.

아마 이 글을 보시는 분들도 각자마다 그런 제품이 있을 거라 생각합니다.

저는 이러한 행동을 식품 회사가 가지는 특징이라 판단하였습니다. 

 

즉, 식품회사는 여러 종류의 제품을 취급하지만 그중에서도 해당 브랜드를 대표하는 주력 상품이 존재합니다.

Northwind사 또한 회사를 대표하는 제품군이 있을 것이며, 주 제품군이 존재한다면 어떤 것에 해당하는지 알아보기 위한

데이터 분석을 진행하였습니다.  

 

이름하여 [Northwind 식품회사 대표 제품에 관한 분석 보고서] 두둥...!

 

 

 

 

2. 첫 번째 가설

첫 번째로 설정한 가설은 '카테고리에 해당하는 제품수가 많을수록 주 제품군에 해당한다'입니다.

 

해당 가설을 설정한 이유는 식품 회사는 여러 종류의 상품을 취급하고 있지만 주 제품군일수록 더 많은 제품을 취급함과 동시에

많은 판매건수를 발생시킬 것이라 판단하였습니다.

 

이 가설이 맞는지 확인하기 위해 각 카테고리에 해당하는 제품 수와 판매건수와 매출액, 판매건수 대비 매출액을 구해보았습니다.

 

 

- 카테고리 별 제품 수

-- 카테고리에 해당하는 제품 건수
select category, count(id) as count
from products
group by category
order by count desc;

 

 

Northwind사는 총 16개의 카테고리에 해당하는 제품을 취급하고 있습니다. 

가장 많은 제품을 포함하는 카테고리는 8개로 Canned Fruit & Vegetables이며

그다음은 5개로 Dried Fruit & Nuts와 Beverages입니다. 

설정한 가설이 맞다면 Canned Fruit & Vegetables 카테고리에 속하는 제품이 가장 많은 판매건수를 기록해야 합니다.

 

이제 가설이 맞는지 확인해보겠습니다.

 

 

- 카테고리별 판매건수와 매출액

--  카테고리별 판매건수와 판매액
select  p.category,
       count(od.product_id) as cnt,
       sum(od.quantity * unit_price) as total_sales
from orders o 
inner join order_details od on o.id = od.order_id
inner join products p on p.id = od.product_id
group by p.category
order by cnt desc;

 

 

조회된 결과를 보면 Beverages가 14건으로 가장 높고 Canned Fruit & Vegetables의 판매건수는 1건으로 가장 낮습니다.

 

 

추가로 카테고리에 포함된 제품의 평균 가격대가 다르고, 판매건수가 많다고 해서 매출액이 높은 것이 아님으로 판매건수 대비 매출액을 구해보았습니다.

 

 

- 카테고리별 제품의 평균 가격대

-- 카테고리별 평균 가격대
select category, avg(list_price) as avg
from products
group by category
order by avg desc;

 

 

- 판매건수 대배 매출액

--  판매건수대비 매출액
select  p.category,
       sum(od.quantity * unit_price) as total_sales,
       count(od.product_id) as cnt,
       sum(od.quantity * unit_price) / count(od.product_id) as "total_sales/cnt"
from orders o 
inner join order_details od on o.id = od.order_id
inner join products p on p.id = od.product_id
group by p.category
order by sum(od.quantity * unit_price) / count(od.product_id) desc;

 

 

카테고리별 평균 가격대

 

판매건수 대비 매출액

 

첫 번째 분석 결과

가장 많은 제품을 포함하고 있는 카테고리는 Canned Fruit & Vegetables입니다.

그러나 6개월 동안 판매건수가 1건에 그친 걸 확인할 수 있습니다.

 

판매건수 대비 매출액이 가장 높은 카테고리는 Beverages, Jams, Preserves, Dairy products 순입니다.

Beverages 카테고리가 제품 수도 많고, 많은 판매건수와 높은 판매 대비 매출액을 보였으나,

다른 카테고리는 그렇지 않아 첫 번째 가설은 받아들이기 어렵다고 판단하였습니다. 

 

 

 

 

3. 두 번째 가설

 

두 번째로 설정한 가설은 '고객이 주로 구매하는 카테고리 제품이 주 제품군에 해당한다'입니다.

 

해당 가설을 설정한 이유는 식품 회사에서 취급하는 주력 제품군 A가 존재한다면 A 제품군을 구매할 때 상대적으로 소비자는 Nortwind사의 제품을 구매할 확률이 높을 것이라 생각했습니다.

 

해당 가설이 맞는지 확인하기 위해 고객이 주로 구매한 카테고리와 그에 해당하는 구매건수를 구해보았습니다.

여기서 6개월 동안 특정 카테고리의 제품을 한 번 구매한 경우

주로 구매한 제품이라 보기 어렵다 판단하여 구매건수가 2건 이상인 카테고리를 포함하는 경우를 기준으로 하였습니다.  

 

 

- 고객 별 주로 구매한 카테고리

-- 고객별 구매한 제품 카테고리 (6개월)
select  a.customer_id as customer_id, 
        c.category, 
        count(a.id) as count_order
from orders a inner join order_details b
on a.id = b.order_id
left join products c
on c.id = b.product_id
group by a.customer_id, c.category
having count(a.id) >= 2
order by c.category, count_order desc;

 

 

 

식품 회사는 총 29명의 고객을 보유하고 있습니다. 

그중 6개월 동안 한 번 이상의 구매가 이뤄진 경우는 15번이며,

특정 카테고리의 제품을 두 번 이상 주문한 고객의 수는 7명입니다.

 

여기서 주의해야 할 부분이 있습니다!

고객 별 주로 구매한 카테고리를 조회 쿼리의 결과를 보면 9개의 레코드가 존재합니다.

customer_id를 보시면 8번과 6번 고객은 각각 두 개의 카테고리에서 2회 이상 구매한 것을 확인할 수 있습니다. 

9개의 행이 조회됐다고 해서 특정 카테고리의 제품을 두 번 이상 주문한 고객이 9명이라고 생각하시면 안 됩니다.

 

테이블 살펴보면

Beverages와 Dried Fruit & Nuts 카테고리가 각각 3명의 고객이 주로 구매한 카테고리로 조회된 것을 확인할 수 있습니다.

 

 

 

 

위의 결과를 파이차트로 표현하면 다음과 같습니다.

Beverages와 Dried Fruit & Nuts가 35%, Candy가 20%, Baked Goods & Mixes가 10%입니다.

 

Beverages, Dried Fruit & Nuts 두 카테고리가 동일한 점유율을 보이고 있어 하나의 주 제품군으로 단정하기 어려워 보입니다. 그러므로 고객이 주로 구매하는 카테고리 제품이 주 제품군에 해당한다는 가설을 받아들이기 어려워 보입니다. 

 

 

 

4. 분석 결과 및 결론

 

분석 결과 첫 번째 가설인 '카테고리에 해당하는 제품수가 많을수록 주 제품군에 해당한다'와  두 번째 가설 '고객이 주로 구매하는 제품이 주 제품군에 해당한다' 모두 받아들이기 어려워 보입니다,

 

다만, 처음과 마지막 분석 결과를 종합해 본 결과 Baverages 카테고리의 경우 해당하는 제품의 수도 많고, 많은 판매 건수와 높은 판매 대비 매출액을 보여주고 있습니다.

또한 7명의 고객 중 3명이 해당 카테고리의 제품을 주로 구매한 것으로 보아 Northwind 식품 회사에 주 제품이 존재한다면 Baverages 일 확률이 높아 보입니다.

 

 

 또한, 식품회사가 보유한 전체 고객 29명 중 약 절반의 고객만이 6개월 동안 한 건 이상의 주문을 한 것을 보고

식품회사에서 취급하는 물품이 제대로 관리되고 있는 것인지에 대한 의구심과 고객을 확보하기 위해 어떤 액션을 취해야 하는지 고민해봤습니다. 

 

 

- 카테고리 별 주문건수 및 고객수

select A.category, A.cnt_item, B.cnt_order, B.cnt_order_customer
from (select category, count(id) as cnt_item
      from products
      group by category) A
left join (select p.category, count(o.id) as cnt_order,
                  count(distinct o.customer_id) as cnt_order_customer
           from orders o
           inner join order_details od on o.id = od.order_id
           inner join products p on p.id = od.product_id
           group by p.category) B
on A.category = B.category
order by cnt_item desc;

 

카테고리별 속하는 제품수와 주문건수 및 주문한 고객의 수를 조회한 결과입니다.

여기서 Cereal과 Chips, Snacks 카테고리의 경우 6개월 동안 발생한 주문건수가 0건입니다. 

 

데이터가 수집된 기간이 짧아 지금 당장 어떤 액션을 취해야 한다고 보긴 어려우나,

판매 부진 제품이 존재한다는 걸 인지할 필요는 있어 보입니다.

계속해서 낮은 판매량을 보인다면 식품회사의 효율적인 제품 관리를 위해 두 카테고리에 해당하는 제품 판매를 중지하는 것을 고려해 볼 수 있다고 생각합니다.

 

 

마지막으로 주력 상품이 아직 없는 경우,

특정 제품하면 Northwind 사가 떠오르는 이미지 구축을 위해 주력 상품을 설정할 필요가 있어 보입니다.

주력 상품을 설정한 후 매출액 증대를 위해 6개월 동안 한 건 이상의 구매를 한 고객을 대상으로 제품 홍보 마케팅을 진행하면 더 많은 매출을 발생시킬 수 있을 것이라 생각합니다. 

 

 

 

 

 

이렇게 해서 저의 Northwind 식품회사 대표 제품에 관한 분석 보고서가 끝났습니다!

저의 한계로 가설을 두 개밖에 설정하지 못했습니다...ㅠ

계속해서 어떤 가설을 설정할 수 있는지 고민하고 있습니다. 

만일 가설이 추가된다면 분석 내용을 추가 포스팅하겠습니다 ㅎㅎ

 

 

긴 글을 읽어 주셔서 감사드립니다 :)