Olist_Brazil_E-commerce RFM분석을 위한 데이터 전처리 및 EDA

최민혁·2023년 8월 11일

olist_RFM 분석

Olist_Brazil_E-commerce RFM분석

목록 보기

1/2

Dataset

Brazilian E-Commerce Public Dataset by Olist from kaggle

EDA 및 분석 관련 코드: RFM분석 및 EDA

🔎 데이터 탐색

총 9개 테이블로 구성되어 있다.

📊 데이터 전처리

product_category_name_transiation
- 상품 카테고리 번역(포르투갈어 -> 영어)
  
  products table에 product_category_name을 product_category_name_translation table에 product_category_name_english 조인하여 업데이트

Data type 변환 및 PK (Primary key) , FK (Foreign key) 설정

각 테이블 칼럼 타입(text) -> varchar()으로 변경
orders table 5개 date 칼럼 타입(text)을 datetime으로 변경

 -- PK 설정
ALTER TABLE `olist`.`orders` 
CHANGE COLUMN `order_id` `order_id` VARCHAR(40) NOT NULL ,
CHANGE COLUMN `customer_id` `customer_id` VARCHAR(40) NULL DEFAULT NULL ,
CHANGE COLUMN `order_status` `order_status` VARCHAR(20) NULL DEFAULT NULL ,
CHANGE COLUMN `order_purchase_timestamp` `order_purchase_timestamp` DATETIME NULL DEFAULT NULL ,
CHANGE COLUMN `order_approved_at` `order_approved_at` DATETIME NULL DEFAULT NULL ,
CHANGE COLUMN `order_delivered_carrier_date` `order_delivered_carrier_date` DATETIME NULL DEFAULT NULL ,
CHANGE COLUMN `order_delivered_customer_date` `order_delivered_customer_date` DATETIME NULL DEFAULT NULL ,
CHANGE COLUMN `order_estimated_delivery_date` `order_estimated_delivery_date` DATETIME NULL DEFAULT NULL ,
ADD PRIMARY KEY (`order_id`);


ALTER TABLE `olist`.`order_payments` 
CHANGE COLUMN `order_id` `order_id` VARCHAR(40) NOT NULL ,
CHANGE COLUMN `payment_sequential` `payment_sequential` INT NOT NULL ,
CHANGE COLUMN `payment_type` `payment_type` VARCHAR(15) NULL DEFAULT NULL ,
CHANGE COLUMN `payment_installments` `payment_installments` INT NULL DEFAULT NULL ,
CHANGE COLUMN `payment_value` `payment_value` FLOAT NULL DEFAULT NULL ,
ADD PRIMARY KEY (`order_id`, `payment_sequential`);


ALTER TABLE `olist`.`customers` 
CHANGE COLUMN `customer_id` `customer_id` VARCHAR(40) NOT NULL ,
CHANGE COLUMN `customer_unique_id` `customer_unique_id` VARCHAR(40) NULL DEFAULT NULL ,
CHANGE COLUMN `customer_zip_code_prefix` `customer_zip_code_prefix` VARCHAR(10) NULL DEFAULT NULL ,
CHANGE COLUMN `customer_city` `customer_city` VARCHAR(40) NULL DEFAULT NULL ,
CHANGE COLUMN `customer_state` `customer_state` VARCHAR(5) NULL DEFAULT NULL ,
ADD PRIMARY KEY (`customer_id`);


ALTER TABLE `olist`.`order_reviews` 
CHANGE COLUMN `review_id` `review_id` VARCHAR(40) NOT NULL ,
CHANGE COLUMN `order_id` `order_id` VARCHAR(40) NOT NULL ,
CHANGE COLUMN `review_score` `review_score` INT NULL DEFAULT NULL ,
CHANGE COLUMN `review_creation_date` `review_creation_date` DATETIME NULL DEFAULT NULL ,
CHANGE COLUMN `review_answer_timestamp` `review_answer_timestamp` DATETIME NULL DEFAULT NULL ,
ADD PRIMARY KEY (`review_id`, `order_id`);


ALTER TABLE `olist`.`order_items` 
CHANGE COLUMN `order_id` `order_id` VARCHAR(40) NOT NULL ,
CHANGE COLUMN `order_item_id` `order_item_id` INT NOT NULL ,
CHANGE COLUMN `product_id` `product_id` VARCHAR(40) NULL DEFAULT NULL ,
CHANGE COLUMN `seller_id` `seller_id` VARCHAR(40) NULL DEFAULT NULL ,
CHANGE COLUMN `shipping_limit_date` `shipping_limit_date` DATETIME NULL DEFAULT NULL ,
CHANGE COLUMN `price` `price` FLOAT NULL DEFAULT NULL ,
CHANGE COLUMN `freight_value` `freight_value` FLOAT NULL DEFAULT NULL ,
ADD PRIMARY KEY (`order_id`, `order_item_id`);

ALTER TABLE `olist`.`products` 
CHANGE COLUMN `product_id` `product_id` VARCHAR(255) NOT NULL ,
ADD PRIMARY KEY (`product_id`);

 -- FK 설정
 ALTER TABLE olist.order_payments 
ADD FOREIGN KEY (order_id) REFERENCES olist.orders(order_id); 

ALTER TABLE olist.orders 
ADD FOREIGN KEY (customer_id) REFERENCES olist.customers(customer_id);

테이블 컬럼 row 개수

결측값 조회 (null)
- orders - 2980건
  - order_delivered_carrier_date (1783건)
  - order_approved_at (160건)
  - order_delivered_customer_date (2965건)
  - 그 외 컬럼 1건
- order_reviews - 89385건
  - review_comment_title (87656건)
  - review_comment_message (58247건)
- products - 611건
  - product_category_name (610건)
  - product_name_lenght (610건)
  - product_description_lenght (610건)
  - product_photos_qty (610건)
  - 그 외 칼럼 2건

결측값 처리

결측값 비중이 1% 미만으로 작다면 무시하고 분석
상당량의 결측값이 존재할 경우 변수 제거 또는 결측값이 있는 row만 제거
제거가 어려울 경우 평균 대치법을 적용하여 처리

📌 orders
order_delivered_customer_date
- order_status에 따라 'NULL' 값 처리
- order_status가'delivered' 일 때 'NULL' 인 경우 '총 8건'
  -> 물류 처리 시점 부터 예상 배송일 차이로 대체 또는 제거

order_delivered_carrier_date
- order_status가 'shipped', 'delivered' 일 때 'NULL' 인 경우 '총 2건' -> 결측값 제거
- 그 외 order_status 인 경우 'NULL'로 처리
📌 product
product_category_name 전체 32951건 중 610건 -> 결측값 제거
나머지 결측값도 위와 같이 진행

📌 order_reviews
review_comment_title , review_comment_message 제거

ALTER TABLE order_reviews
DROP COLUMN review_comment_title,
DROP COLUMN review_comment_message;

📊TABLE_EDA(탐색적 데이터 분석)

Customers

고객 별 주문횟수,결제횟수,총 결제금액, 평균 결제금액

Orders (고객 주문)

연도별 / 월별 주문 건수 (단. order_status가 'canceled','unavailable' 인 상태는 제외)
2017,2018년 주문량 비교

2017년 1월 부터 2018년 1월까지 주문량이 꾸준히 상승하였으나 , 2018년 2월 부터 점점 주문량이 감소하는 추세이다.

고객 만족

review_score 별 주문 건수 와 비율
2018년 해당하는 주문 건수만 조회
(단. order_status가 'canceled','unavailable' 인 상태는 제외)

2018년 전체 주문 건수는 56105건 이며 각 리뷰점수 별 전체 주문건수 와 비율은
1점 : 6474(11.54%) , 2점 : 1773(3.16%) , 3점 : 4589(8.18%) , 4점 :10739(19.17%) ,
5점 : 32753(58.38%) 으로 주문건수에 따른 리뷰점수를 조사했을떄 고객의 만족도가 전반적으로 만족스럽다는 결과가 보인다.

Products

order_status가 'canceled', 'unavailable'를 제외한 상태에서 카테고리 제품 매출 상위 10개 / 하위 10개

상위 10건에 해당하는 카테고리는 주로 생활/가전 제품이며 , 하위 10건에 해당하는 카테고리는
보안 서비스, 아동/스포츠 패션 , 뮤지컬 cd/dvd , pc게임과 꽃, 미술 공예용품 등이 있다.

카테고리 제품별 성장률
어떤 카테고리 제품이 성장하고 감소하고 있는지 확인
2017년 ~ 2018년 까지 제품 카테고리
전체 주문건수를 기준으로 조회

bed_bath_table 제품이 가장 많이 판매되었지만, 전년대비 판매량이 약 5.96% 상승으로 크게 성장했다고 보기는 어려울 거 같다. 그 다음으로 많은 판매량을 기록한 health_beauty 제품은 약 57.80% 판매량이 상승하였다. 반면 diapers_and_hygiene 제품이 약 2300%로 비율로만 보자면 엄청난 성장률을 기록했다 볼 수 있지만 , 전년도 판매 수량은 1개로 2300%라는 수치는 크게 의미가 없어 보인다.

Order_payments

결제 방식 집계
(단. order_status가 'canceled','unavailable' 인 상태는 제외)

결제 수단으로 가장 많이 사용된것은 credit_card로 74579건 결제에 사용되었고 , 그 다음으로 boleto(브라질 은행 연맹에서 규제하는 결제 방법)로 19191건 결제 되었다.