코츠사 DB를 SQL로 분석하기 [코드스테이츠 PMB 7기]

요즘 자주 들어가는 곳이 있다. 바로, 구직 플랫폼 원티드와 로켓펀치. 관심 있는 회사에서 원하는 PM은 어떤 역량을 요하는지, 어떤 사람과 함께 일을 하고 싶어 하는지 개인적으로 조사도 해볼 겸 해서 틈틈이 들어간다.

 

회사에서 정말 다양한 역량을 가진 PM을 뽑고 있다. 회사의 현 상황에 맞춰서 그에 맞는 사람을 뽑고 있나보다. 그로스 PM, 기술 PM, UX PM, 마케팅 PO 등등.

 

이렇게 다양한 PM을 채용하는데, 우대조건에 공통점이 있었다. 바로 SQL(Structured Query Language)로 데이터를 다룰 수 있는 사람을 먼저 찾는다는 것.

 

 

모 스타트업 채용공고, 우대사항

 

 

그들은 왜 데이터를 다룰 수 있는 능력자를 찾는걸까?

 

 

 

데이터 광산에서 캐는 인사이트💎

사실, 데이터는 아주 오래전부터 중요한 도구였다.

기원전 6000년 전부터 작물 수율과 휴경에 대한 데이터를 사용하여 생산량과 수확량을 늘렸고, 15세기에는 천문학 데이터를 활용하여 세상을 탐험하고 해외 무역의 길을 열었다. 1850년대에는 데이터를 사용하여 각종 질병이나 전염병 등의 관계를 밝혀 인간을 생명을 구했다

이처럼 데이터는 인류 발전 및 경제 성장에 주요 원동력이다. 이처럼 데이터는 우리 삶의 '인사이트'를 주는 중요한 도구다.

 

 

 

그럼 데이터는 많으면 많을수록 좋은 것일까? 

오늘날의 데이터 혁신을 주도하는 것은 방대한 양의 데이터가 아니라 우리가 데이터를 수집하고, 저장하고, 분석하고 변환하는 기본적인 기술입니다. 이러한 기술 덕분에 데이터에 포함된 사실로부터 강력한 통찰력을 얻을 수 있으며, 새로운 지식을 발견하고 새로운 관계를 찾으며 새롭게 예측을 할 수 있습니다. 

- BSA | The Software Alliance

-

 

답은 NO! 많은 양의 데이터가 중요한 게 아니다.

 

즉, 우리의 방향성이나 의사결정에 필요한 데이터를 알맞게 수집하고 저장하고, 저장한 내용을 분석해 필요한 유의미한 결과로 인사이트를 발견하는지가 중요하다. 따라서, 이 과정을 효율적으로 잘 처리할 수 있는 SQL(Structured Query Language)의 중요성이 더욱 강조되고 있다.

 

 

 

SQL을 사용해 데이터 추출하기⛏

실습 예제를 통해 SQL을 어떻게 사용할지 알아보자.

과제 1. DB에 저장되어 있는 고객 데이터를 살펴보고, 이를 DB 간 개체, 속성, 관계를 알 수 있도록 ERD(Entity - Relationship Diagram)를 그려보자.

과제 2. DB에서 데이터를 추출해, 원하는 내용을 확인할 수 있는 SQL 문장을 만들어보자(3개 이상)

*DB는  w3schools(SQL)에서 찾을 수 있다.

 

 

과제 1. ERD(Entity - Relationship Diagram) 그려보기

 

ERD는 데이터베이스 구조를 한눈에 알아보기 위해 그려놓는 다이어그램이다. 즉, 개체(Entity)와 관계(Relationship)를 중심으로 테이블과 그 속에 정의된 여러 관계를 보기 쉽게 나타내기 위해서 그리는 다이어그램. DB를 설계하기 전 개발자와 기획자 등 팀 내의 이해관계자 인원이 모여 프로덕트를 잘 이해하고 구성하기 위해 함께 그린다.

 

 

*w3schools(SQL) DB로 그린 ERD

draw.io를 통해 그린 ERD

모든 Table에는 기준이 되는 PK(primary key)를 ID로 갖고 있어서 구분하기 좋았다. 이 PK 속성 값을 기준으로 관계선을 연결했고, 각 Table 간의 관계(Cardinality)에 따라 1:1, 1:N로 표시했다. 그리고 Table간의 관계를 고려해 Mandatory(반드시 있어야 한다 : | )와 Optional(있을 수도 있고, 없을 수도 있다 : O)을 표시했다.

 

 

 

과제 2. DB에서 데이터를 추출해, 원하는 내용을 확인할 수 있는 SQL 문장을 만들어보자(3개 이상)

나는 이번 온라인 커머스 플랫폼 코츠사에 취업한 주니어 PM이다. 나는 코츠사의 미래 방향성을 제안하라는 미션을 받았다.
현재 우리 회사의 DB를 보니, 여러 고객과 국가에서 우리를 통해 다양한 제품을 구매, 판매하고 있다. 많은 구매자와 판매자가 우리 플랫폼에서 거래하는 건 좋지만, 앞으로 더 멀리 가기 위해선 '선택과 집중'이 필요할 것 같다.  그래서 [판매량이 가장 낮은 카테고리]와 [매출이 가장 낮은 국가(BEP기준)]는 과감히 제거하고, 신규 고객을 유치하기 위해 [매출이 가장 높은 제품]에 집중하여 할인 프로모션 진행하고자 한다.  (물론, 가설이기에 확인이 필요한 부분이 많다.)

*BEP : Break Even Point

 

 

 Q-1.  판매량이 가장 낮은 카테고리는 어떤 것인가? 

 

우리 회사는 정말 다양한 카테고리의 제품을 취급하고 있다. 하지만, 이는 좋은 점이자, 나쁜 점이 될 수 있다. 카테고리가 다양하다는 것은 그만큼 다양한 고객의 니즈를 담아내고 있다는 것인데, 이는 마케팅이나 광고 등에서 불필요한 과지출을 유도할 수도 있다. 그리고 카테고리를 줄여야 기존에 인기 있는 카테고리를 더 철저히 관리할 수 있을 것이다.

 

 

 

SELECT DISTINCT Categories.CategoryName,SUM(OrderDetails.Quantity) AS Sales_CNT
FROM Categories,OrderDetails,Products
WHERE Categories.CategoryID=Products.CategoryID and Products.ProductID=OrderDetails.ProductID
GROUP BY Categories.CategoryName
ORDER BY Sales_CNT ASC;

 

 

A -1. [ Categories ]와 [OrderDetails], [ Products ]를 join해서 분석해본 결과 판매량이 가장 낮은 카테고리는 [ Produce ] 다. 

-> 앞서 말한 듯이, [ Produce ] 카테고리는 제거한다.

 

 

 

 Q - 2. 매출이 가장 낮은 국가는 어디인가? (BEP를 넘지 못한 국가) 

우리는 전 세계를 대상으로 판매하고 있다. 다양한 국가들 중 다행히 매출이 제법 나오는 곳도 있지만, 생각보다 미미한 곳도 있다. 매출이 발생하지 않더라도 각 국가에서 발생하는 유지비와, 인건비 등은 비슷하게 나오는 상태다. 따라서, BEP : Break Even Point(Sales_sum: 1000 이상)을 달성하지 못한 국가에서 거래를 그만하려 한다.

 

 

 

SELECT DISTINCT Customers.Country,SUM(OrderDetails.Quantity*Products.Price) AS Sales_sum
FROM Customers,Orders,OrderDetails,Products
WHERE Customers.CustomerID=Orders.CustomerID and Orders.OrderID=OrderDetails.OrderID
and OrderDetails.ProductID=Products.ProductID
GROUP BY Customers.Country
ORDER BY Sales_sum ASC;

 

A - 2. [ Customers ]와 [ Orders ], [OrderDetails], [ Products ]를 join해서 분석해본 결과 BEP를 넘지 못한 국가는 [ Argentina ]와 [ Poland ] 다.

-> BEP는 넘지 못한 [ Argentina ]와 [ Poland ]는 거래를 중단한다.

 

 

 Q - 3. 판매 상품 중 가장 인기가 높은 제품은 무엇인가? 

현재 우리 브랜드에서 가장 인기 높은 제품을 이용해 적극적으로 신규 고객을 유도하고자 한다. 방법은 간단하다. 가장 매출이 높은 제품을 찾고, 그 제품을 할인하는 프로모션을 진행할 예정이다. 

 

 

SELECT productname, quantity, price, sum(quantity), sum(quantity)*price as Sales_sum 

FROM OrderDetails,  products
WHERE orderdetails.productid = products.productid
GROUP BY productname
ORDER BY Sales_sum DESC;

 

A - 3. [ OrderDetails ]와 [ products ]를 join해서 분석해본 결과, 현재 가장 많은 매출을 보이고 있는 제품은 [ Côte de Blaye ]다.

-> 따라서, [ Côte de Blaye ]를 제품 가격의 10% 할인가 = 237.5 (원가: 263.5)로 신규 고객 유입을 위한 할인 프로모션을 진행한다.