Olist Store电商数据分析

一、项目背景

        Olist是巴西市场上最大的百货公司，来自巴西各地的小型企业可以通过Olist商店销售他们的产品，并使用Olist物流合作伙伴将其直接运送给客户。本文将从用户、商家、商品、销售、交付五个维度对Olist商店2016年9月-2018年8月的订单数据进行分析，目的是发现平台存在的问题，分析原因，并给出建议。

        数据来源：Brazilian E-Commerce Public Dataset by Olist | Kaggle

        数据集介绍：该数据集由Olist 提供，包含2016年9月-2018年8月在巴西多个市场进行的10万个订单的信息。其功能允许从多个维度查看订单：从订单状态、价格、付款和运费表现到客户位置、产品属性以及最终由客户撰写的评论以及地理定位数据集，它将巴西邮政编码与纬度/经度坐标联系起来。 该数据集包含9个子集，每个数据集之间的联系如下图：

        该数据集包含9个文件：

        <1> 文件olist_customers_dataset.csv包含5个字段，99400行，此数据集包含有关客户及其位置的信息，使用它来识别订单数据集中的唯一客户并查找订单交货地点
        customer_id：订单对应的客户ID。订单数据集的键，每个订单都有一个唯一的customer_id
        customer_unique_id：客户唯一ID
        customer_zip_code_prefix：客户邮政编码的前五位数字
        customer_city：客户所在城市
        customer_state：客户所在的州

        <2>文件olist_geolocation_dataset.csv包含5个字段，1000000行，此数据集包含巴西邮政编码及其纬度/经度坐标信息。 用它来绘制地图并找出卖家和顾客之间的距离
        geolocation_zip_code_prefix：邮政编码的前5位数字
        geolocation_lat：纬度
        geolocation_lng：经度
        geolocation_city：城市名
        geolocation_state：州

        <3>文件olist_order_items_dataset.csv包含7个字段，113000行，此数据集包括有关每个订单中购买的商品的数据
        order_id：订单ID
        order_item_id：序号，用于标识同一订单中包含的商品数量
        product_id：商品ID
        seller_id：卖家ID
        shipping_limit_date：将订单处理到物流合作伙伴的卖家发货限制日期
        price：商品价格
        freight_value：商品运费价值（如果订单含有多个商品，则运费价值在商品之间拆分）

        <4>文件olist_order_payments_dataset.csv包含5个字段，104000行，此数据集包含有关订单付款选项的数据
        order_id：订单ID
        payment_sequential：付款顺序（客户可以使用多种付款方式支付订单，如果他这样做，将创建一个序列以适应所有付款）
        payment_type：客户选择的付款方式
        payment_installments：客户选择的分期付款数量
        payment_value：交易金额

        <5>文件olist_order_reviews_dataset.csv包含7个字段，100000行该数据集包括有关客户所做评论的数据
        review_id：唯一评价ID
        order_id：订单ID
        review_score：评分
        review_comment_title：来自客户留下的评论的评论标题
        review_comment_message：来自客户留下的评论的评论信息
        review_creation_date：显示向客户发送满意度调查的日期
        review_answer_timestamp：显示满意度调查答案时间戳

        <6>文件olist_orders_dataset.csv包含8个字段，99400行，这是核心数据集，可以从每个订单中找到所有其他信息
        order_id：订单ID
        customer_id：客户ID
        order_status：订单状态
        order_purchase_timestamp：购买时间
        order_approved_at：付款审批时间
        order_delivered_carrier_date：订单交付承运人时间
        order_delivered_customer_date：客户实际订单交货日期
        order_estimated_delivery_date：购买时通知客户的预计交货日期

        <7>文件olist_products_dataset.csv包含9个字段，33000行，该数据集包括有关Olist销售的商品的数据
        product_id：唯一商品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：商品宽度以厘米为单位

        <8>文件olist_sellers_dataset.csv包含4个字段，3095行，该数据集包括有关在Olist完成订单的卖家的数据。 使用它来查找卖家位置并确定哪个卖家完成了每个商品的出售
        seller_id：卖家ID
        seller_zip_code_prefix：卖家邮政编码的前5位数字
        seller_city：卖家所在城市
        seller_state：卖家所在州

        <9>文件product_category_name_translation.csv包含2个字段，71行，将商品名从葡萄牙语翻译为英语
        product_category_name：葡萄牙语的类别名称
        product_category_name_english：英语的类别名称

二、分析框架

         分析工具：MySQL、Power BI、Excel

三、数据清洗

3.1 数据导入

        在Navicat中使用MySQL新建一个数据库，导入数据集

3.2 数据类型转换

        数据集中原始数据类型均为varchar，数字、货币、时间类型的数据需要进行数据类型转换

-- 数据类型转换
ALTER TABLE olist_geolocation_dataset
	MODIFY geolocation_lat DECIMAL(10,7),
	MODIFY geolocation_lng DECIMAL(10,7);

ALTER TABLE olist_order_items_dataset
	MODIFY order_item_id INT,
	MODIFY shipping_limit_date datetime,
	MODIFY price DECIMAL(10,2),
	MODIFY freight_value DECIMAL(10,2);

ALTER TABLE olist_order_payments_dataset
	MODIFY payment_sequential INT,
	MODIFY payment_installments INT,
	MODIFY payment_value DECIMAL(10,2);

ALTER TABLE olist_order_reviews_dataset
	MODIFY review_score INT,
	MODIFY review_creation_date datetime,
	MODIFY review_answer_timestamp datetime;

ALTER TABLE olist_orders_dataset
	MODIFY order_purchase_timestamp datetime,
	MODIFY order_approved_at datetime,
	MODIFY order_delivered_carrier_date datetime,
	MODIFY order_delivered_customer_date datetime,
	MODIFY order_estimated_delivery_date datetime;

ALTER TABLE olist_products_dataset
	MODIFY product_name_length INT,
	MODIFY product_description_length INT,
	MODIFY product_photos_qty INT,
	MODIFY product_weight_g INT,
	MODIFY product_length_cm INT,
	MODIFY product_height_cm INT,
	MODIFY product_width_cm INT;

        也可以在设计表中直接修改字段的数据类型

3.3 删除重复行

-- 查看是否有重复行
SELECT customer_id
FROM olist_customers_dataset
GROUP BY customer_id
HAVING count(*) > 1;

SELECT geolocation_zip_code_prefix
FROM olist_geolocation_dataset
GROUP BY geolocation_zip_code_prefix
HAVING count(*) > 1;

 

        可以看到，一个邮编对应多个经纬度，可能是平台准确记录了每个客户下单时的定位，但是不利于后续表连接分析，所以取均值。

SELECT order_id
FROM olist_order_items_dataset
GROUP BY order_id, order_item_id
HAVING count(*) > 1;

SELECT order_id
FROM olist_order_payments_dataset
GROUP BY order_id, payment_sequential, payment_type
HAVING count(*) > 1;

SELECT review_id
FROM olist_order_reviews_dataset
GROUP BY review_id, order_id
HAVING count(*) > 1;

 

SELECT order_id
FROM olist_orders_dataset
GROUP BY order_id
HAVING count(*) > 1;

SELECT product_id
FROM olist_products_dataset
GROUP BY product_id
HAVING count(*) > 1;

SELECT seller_id
FROM olist_sellers_dataset
GROUP BY seller_id
HAVING count(*) > 1;

 

SELECT product_category_name
FROM product_category_name_translation
GROUP BY product_category_name
HAVING count(*) > 1;

-- 删除重复值行
CREATE TABLE temp
	SELECT geolocation_zip_code_prefix
		,ROUND(AVG(geolocation_lat), 7) as geolocation_lat
		,ROUND(AVG(geolocation_lng), 7) as geolocation_lng
		,MAX(geolocation_city) as geolocation_city
		,MAX(geolocation_state) as geolocation_state
	FROM olist_geolocation_dataset
	GROUP BY geolocation_zip_code_prefix;
DROP TABLE olist_geolocation_dataset;
ALTER TABLE temp RENAME TO olist_geolocation_dataset;

3.4 缺失值处理

-- 查看缺失值
SELECT count(customer_id)
	,count(customer_id)
	,count(customer_state)
	,count(customer_unique_id)
	,count(customer_zip_code_prefix)
FROM olist_customers_dataset;

SELECT count(geolocation_lat)
	,count(geolocation_lng)
	,count(geolocation_city)
	,count(geolocation_state)
	,count(geolocation_zip_code_prefix)
FROM olist_geolocation_dataset;

SELECT count(order_id)
	,count(order_item_id)
	,count(product_id)
	,count(seller_id)
	,count(shipping_limit_date)
	,count(price)
	,count(freight_value)
FROM olist_order_items_dataset;

SELECT count(order_id)
	,count(payment_type)
	,count(payment_value)
	,count(payment_sequential)
	,count(payment_installments)
FROM olist_order_payments_dataset;

        olist_order_reviews_dataset表可以明显看出存在缺失值，但是对本次分析没有影响，无需处理。

SELECT count(customer_id)
	,count(order_id)
	,count(order_status)
	,count(order_purchase_timestamp)
	,count(order_approved_at)
	,count(order_delivered_carrier_date)
	,count(order_delivered_customer_date)
	,count(order_estimated_delivery_date)
FROM olist_orders_dataset; 

        根据查询结果，order_delivered_customer_date、order_approved_at、order_delivered_carrier_date缺失数据与整体数据体量相比较少，所以选择直接删除。 

SELECT count(product_id)
	,count(product_category_name)
	,count(product_photos_qty)
	,count(product_name_length)
	,count(product_description_length)
	,count(product_weight_g)
	,count(product_width_cm)
	,count(product_height_cm)
	,count(product_length_cm)
FROM olist_products_dataset;