[코드스테이츠 PMB 6기] W5L2 빅쿼리, SQL을 이용한 데이터 분석

1. 데이터 파악하기

https://www.kaggle.com/olistbr/brazilian-ecommerce

Brazilian E-Commerce Public Dataset by Olist

 

kaggle에 있는 Brazilian E-Commerce Public Dataset by Olist로 데이터 분석을 해보려고 한다.

​

이 데이터는 실제 브라질의 멀티 마켓 플레이스 사이트인 Olist의 dataset으로 2016년부터 2018년도까지의 100k 개의 쇼핑 정보를 가지고 있다.

​

DB는 order, order_reviews, order_payment, order_item, order_customer, product, sellers, geolocation와 category_name_translation을 포함하여 총 9개의 테이블을 제공한다.

​

캐글에서 제공된 스키마와 DB를 토대로 테이블, 칼럼을 만들고 스키마를 다시 만들었다.

​

제공된 스키마(왼쪽)/내가 만든 스키마(오른쪽)

 

 

2. 빅쿼리와 SQL을 이용한 데이터 분석

1) 목표

​고객 인사이트와 문제점을 찾고 문제점 개선을 하여 매출 올리기

2) 질문

(1)고객이 사는 주

(2) 고객이 가장 많이 사는 도시 TOP10

(3) 고객들의 결제수단 비율

(4)가장 많이 팔린 제품 TOP10

(5) 가장 적게 팔린 제품 TOP10

​

- 빅쿼리에 데이터세트 만들기

빅쿼리 데이터 테이블

- SQL를 이용해 데이터 추출하고 데이터 시각화

​

(1) 고객이 사는 주

​

SELECT customer_state,count(customer_state) as cnt

FROM `bionic-water-315304.W5L2.bz`

group by customer_state order by cnt desc

​

SQL 실행(왼쪽)/각 주의 고객 수(오른쪽)

 

(2) 고객이 가장 많이 사는 도시 TOP10

​

SELECT customer_city,count(customer_city) as top10

FROM `bionic-water-315304.W5L2.bz`

group by customer_city

order by top10 desc

LIMIT 10

​

SQL 실행(왼쪽)/고객이 가장 많이 사는 도시 TOP10(오른쪽) ​

 

(3) 고객의 결제수단 비율

​

SELECT payment_type,count(payment_type) as type

FROM `bionic-water-315304.W5L2.order_payment`

group by payment_type

order by type desc

​

SQL 실행(왼쪽)/고객의 결제 수단 비율(오른쪽) ​

 

 

 

(4),(5)는 아직 SQL를 잘 못 다루는 관계로 결과를 얻지 못했다. ㅎㅎ

(꼭 SQL을 더 공부하여 과제를 보완하겠다..)

​

3) 결론

- Olist의 고객은 대부분 Sao Paul, rio de janeiro 주와 도시에 있다. 특히 대도시인 Sao Paul에 가장 많은 고객이 있다.

(가정) 물류창고가 다른 주나 도시에 있다면? -> 고객이 가장 많은 Sao Paul에 있다면 배송 시간을 줄일 수 있지 않을까?

​

- Olist의 고객은 카드 결제의 비율이 높다.

(가정) 간편 결제 서비스가 없다면? -> Olist만의 간편 결제 서비스를 제공하면 구매 비율이 더 높아질까?

 

3. 데이터로 또 다른 인사이트 찾기

 1) 2016~2018 연도별 가장 많이 팔린 제품을 찾고 브라질의 트렌드 파악하기

 2) 일주일 중 주문이 가장 많은 요일, 하루 중 주문이 가장 많은 시간대 알아보기

 3) 분기별 총 주문수와 판매 수익으로 Olist는 성장하고 있는지 파악하기