[STARTERS 4기 TIL] #52일차(23.04.19)

---

[SQL 미니 프로젝트]

1일차 : 데이터 파악 및 분석 상황 설정

💡 예시
· 고객 관리 담당자 : 효율적인 고객 관리 위한 마케팅 전략!
· 유통/배송 담당자 : 배송 관련해서 뭐 보겠다
· 제품 담당자 : 고객 리뷰 좋은 제품, 많이 팔리는 지역

 

1. 참고 자료

1. ERD/테이블-컬럼 정의서

olist 데이터셋

2. 분석 프로젝트

[DATATHON] Olist에서 SELLER로 살아남기 👊

 

2. 데이터 파악 (특이사항)

테이블명 테이블 설명 데이터 갯수 특이사항

테이블명	테이블 설명	데이터 갯수	특이사항
customers	고객의 지역 정보	99,441	orders 테이블과 1:1 매칭
geolocation	지리적 위치	1,000,163
order_items	주문 아이템	112,650	동일 제품을 다른 판매자가 판매할 수 있음. (판매자 ID가 ORDER_DETAILS? 테이블과 연관됨)
order_payments	주문 결제 정보	103,886
orders	주문 정보	99,441	customers 테이블과 1:1 매칭주문상태를 관리(주문->승인->배송시작->배송완료)
product_category_name_translation	카테고리 명	71	PK 없음. 중복데이터 없음.
products	제품 정보	32,951	제품명과 제품설명은 익명처리되어 알 수 없음
reviews	리뷰	99,224
sellers	판매자	3,095

 

2-1. 특이사항

1. orders ←→ reviews : 리뷰 없는 order_id 768개

select o.order_id, count(r.review_score) 
from orders o
	 left join reviews r on o.order_id = r.order_id
group by 1
having count(r.review_score) < 1

2. order_item_id

select *
from orders o
	 inner join customers c on o.customer_id = c.customer_id
	 inner join order_items oi on o.order_id = oi.order_id
	 left join products p on oi.product_id = p.product_id
	 left join product_category_name_translation pc on p.product_category_name = pc.product_category_name
	 left join reviews r on o.order_id = r.order_id
where o.order_id = 'ff0286485ce9c3f1d6ed22a4c7402060'
order by o.order_id desc

모든 컬럼 일치하는데 order_item_id만 1-2-3-4로 나뉨!!

예시 order_id : ff0286485ce9c3f1d6ed22a4c7402060

→ product_id가 같은 경우, 다른 경우 존재! 주문 수량

 

3. 분석 상황 설정

1. 우리는?

: Olist 입점 셀러

 

2. 우리의 목적

: 매출 확보 판매 전략을 세우기 위한 데이터 분석

ex) 지역 별 선호 카테고리, 고객 그룹(RFM 등) 별 선호 카테고리

 

3. 분석 과정

   1) 어떤 고객을 대상으로 해야할까?

       · RFM 기준

   2) 어떤 품목을 판매해야 할까?

       · 매출 TOP 10 카테고리

       · 꾸준히 판매되고 있는 카테고리 = 카테고리별 재구매율

   3) 어떤 지역에서 판매해야 할까?

       · 주문자와 판매자가 거리, 배송기간, 주문건수 상관관계

       · 2번 품목이 주로 판매되는 지역 TOP 3