SeSAC 전z전능 분석가 성동2기 데이터 분석가 | SQL 프로젝트(1)_olist 데이터 파악 및 전처리

SQL 실무 프로젝트

사용 데이터

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

 

 

SQL 프로젝트

 

우리가 사용할 데이터는 브라질 이커머스인 olist의 자료이다

제품, 카테고리, 주문 내역 등을 알 수 없는데 개인 정보의 경우 보호처리되어 인구통계학적 정보는 얻을 수 없다

‣

• olist_customers_dataset.csv:고객과 위치 정보가 포함

• olist_geolocation_dataset.csv : 브라질 우편번호와 그에 해당하는 위도/경도 좌표 정보가 포함

• olist_order_items_dataset.csv: 각 주문 내에서 구매한 상품에 대한 정보가 포함

• olist_order_payments_dataset.csv: 주문 결제 옵션에 대한 정보가 포함

• olist_order_reviews_dataset.csv: 고객 리뷰에 대한 정보가 포함

• olist_orders_dataset.csv: 핵심 데이터으로 각 주문에서 다른 모든 정보를 찾을 수 있음

• olist_products_dataset.csv: Olist에서 판매된 제품에 대한 정보가 포함

• olist_sellers_dataset.csv : Olist에서 주문을 처리한 판매자에 대한 정보가 포함

• product_category_name_translation.csv: 상품 카테고리명 영어로 번역

 

데이터 컬럼 설명

 

테이블: orders

컬럼 이름	설명
order_id	주문의 고유 식별자
customer_id	주문을 한 고객의 고유 식별자
order_status	주문의 현재 상태 (예: delivered, shipped 등)
order_purchase_timestamp	주문이 접수된 시각
order_approved_at	결제가 승인된 시각
order_delivered_carrier_date	주문이 배송사에게 전달된 시각
order_delivered_customer_date	주문이 고객에게 배송된 시각
order_estimated_delivery_date	구매 시 제공된 예상 배송 날짜

테이블: order_items

컬럼 이름	설명
order_id	주문의 고유 식별자
order_item_id	주문 항목의 식별자
product_id	제품의 고유 식별자
seller_id	판매자의 고유 식별자
shipping_limit_date	판매자가 주문을 발송해야 하는 기한
price	제품의 가격
freight_value	배송비

테이블: customers

컬럼 이름	설명
customer_id	고객의 고유 식별자
customer_unique_id	고객의 고유 식별자(중복 없음)
customer_zip_code_prefix	고객의 우편번호(앞부분)
customer_city	고객의 도시
customer_state	고객의 주

테이블: sellers

컬럼 이름	설명
seller_id	판매자의 고유 식별자
seller_zip_code_prefix	판매자의 우편번호(앞부분)
seller_city	판매자의 도시
seller_state	판매자의 주

테이블: products

컬럼 이름	설명
product_id	제품의 고유 식별자
product_category_name	제품 카테고리 이름
product_name_length	제품 이름의 길이
product_description_length	제품 설명의 길이
product_photos_qty	제품 사진의 개수
product_weight_g	제품의 무게 (그램)
product_length_cm	제품의 길이 (센티미터)
product_height_cm	제품의 높이 (센티미터)
product_width_cm	제품의 너비 (센티미터)

테이블: order_payments

컬럼 이름	설명
order_id	주문의 고유 식별자
payment_sequential	결제 순서 번호
payment_type	결제 수단 (신용카드, 직불카드, 등)
payment_installments	할부 개월 수
payment_value	결제 금액

테이블: order_reviews

컬럼 이름	설명
review_id	리뷰의 고유 식별자
order_id	주문의 고유 식별자
review_score	리뷰 점수 (1~5)
review_comment_title	리뷰 제목
review_comment_message	리뷰 내용
review_creation_date	리뷰가 생성된 날짜
review_answer_timestamp	리뷰에 대한 답변이 작성된 날짜

데이터 전전처리

1) 데이터 utf-8로 바꾸기 (불러올 때 인코딩 utf-8로 수정해서 깨진 부분 처리)

2)데이터명 중복된 부분 지우고 통일하기

 

ex. olist_products_datebase → products로 수정

 

3)products 테이블 카테고리명 innerjoin을 통해 영어로 바꿔주기

 

select 
p.product_id, cn.product_category_name_english,
p. product_name_lenght, p.product_description_lenght,
p. product_photos_qty, p. product_weight_g, p. product_length_cm,
p. product_height_cm, p. product_width_cm
from category_name cn inner join products p
on cn. product_category_name = p.product_category_name ;

*엑셀로 복붙 후 다시 설치하는 과정을 거쳤으나, 이후 멘토님께 sql내에서 저장하는 법 여쭤볼 것

 

엑셀 복사 전 결과 값이 나왔을 때 200개 미리보기만 나오는 버전을 전체보기로 수정해줘야 함

우측 하단에 갈매기 모양 클릭

엑셀에서 csv로 저장할 때 혹시 몰라서 쿼리형태로 만들어줬음 (데이터 머리글 추가 목적)

 

 

 

질문 모음

Olist의 총 매출액은 얼마이며, 시간이 지남에 따라 어떻게 변했나요?

Olist에서 얼마나 많은 주문이 이루어졌으며, 이는 월별 또는 계절별로 어떻게 다른가요?

Olist에서 가장 인기 있는 제품 카테고리는 무엇이며, 각 카테고리의 판매량은 어떻게 비교되나요?

Olist의 평균 주문 금액(AOV)은 얼마이며, 이는 제품 카테고리나 결제 방법에 따라 어떻게 다른가요?

Olist에서 활동 중인 판매자는 몇 명이며, 시간이 지남에 따라 이 숫자는 어떻게 변하나요?

Olist의 판매자 평점 분포는 어떻게 되며, 이는 판매 성과에 어떤 영향을 미치나요?

Olist에서 반복 구매를 한 고객은 몇 명이며, 이들이 총 매출에서 차지하는 비율은 얼마인가요?

Olist에서 판매된 제품에 대한 평균 고객 평점은 얼마이며, 이는 판매 성과에 어떤 영향을 미치나요?

Olist의 평균 주문 취소율은 얼마이며, 이는 판매자 성과에 어떤 영향을 미치나요?

Olist에서 가장 많이 팔린 제품은 무엇이며, 이들의 판매 트렌드는 시간이 지남에 따라 어떻게 변했나요?

Olist 고객들이 가장 많이 사용하는 결제 방법은 무엇이며, 이는 제품 카테고리나 지리적 지역에 따라 어떻게 다른가요?

고객 리뷰와 평점은 Olist에서의 판매와 제품 성과에 어떤 영향을 미치나요?

Olist에서 가장 높은 이익률을 가진 제품 카테고리는 무엇이며, 회사는 다양한 카테고리에서 수익성을 어떻게 증가시킬 수 있나요?

고객 밀집도가 높은 지역을 기준으로 고객 유지율을 계산하세요.

 

챗지피티를 통해 얻은 주제 추천

 

 

현재 카테고리 위주로 분석을 해보고 싶어서 셀러 별 카테고리와 국적 상관관계

카테고리 별 취소율 등을 고민하고 있다 다만, 셀러 정보가 적고 구만개의 상품 중 취소 수는 100개 미만이라 유의미한 분석이 될지 모르겠다

 

[셀러 별 카테고리 수]

perfumery	6
art	2
sports_leisure	22
baby	9
housewares	12
musical_instruments	3
cool_stuff	8
furniture_decor	18
home_appliances	2
toys	9
bed_bath_table	16
construction_tools_safety	1
computers_accessories	7
health_beauty	11
luggage_accessories	5
garden_tools	6
office_furniture	2
auto	9
electronics	2
fashion_shoes	2
telephony	7
stationery	7
fashion_bags_accessories	5
computers	1
home_construction	3
watches_gifts	8
construction_tools_construction	2
pet_shop	2
small_appliances	2
agro_industry_and_commerce	1
furniture_living_room	2
signaling_and_security	1
air_conditioning	1
consoles_games	3
books_general_interest	1
costruction_tools_tools	1
fashion_underwear_beach	1