Olist电商数据分析实战：从数据清洗到商业洞察全流程解析

1. 项目概述：从零开始理解Olist电商数据分析平台

如果你在电商行业待过，或者对数据驱动业务增长感兴趣，那你大概率听说过或者接触过“Olist”这个名字。它不是一个直接面向消费者的购物网站，而是一个在数据科学和商业分析领域声名鹊起的“宝藏”数据集和商业案例。简单来说，Olist提供了一个真实、完整、脱敏的巴西电商交易数据集，涵盖了从2016年到2018年超过10万笔订单的详细信息。这个项目之所以重要，是因为它把一个复杂的、多模块的电商后台系统，抽象成了一个可供所有人研究、学习和实践的数据金矿。

我第一次接触Olist数据集，是在为一个中小型电商客户搭建数据分析看板的时候。客户抱怨说，市面上很多教程的数据都太“干净”、太“理想”了，跟实际业务中混乱、缺失、充满异常的数据根本不是一回事。直到我找到了Olist，它完美地模拟了一个真实电商平台的数据生态：有订单、有客户、有商品、有卖家、有支付记录、有评论，甚至还有物流追踪和地理位置信息。这些数据分散在多个互相关联的表中，你需要像真正的数据工程师或分析师一样，进行数据清洗、关联、整合，才能挖掘出商业价值。

所以，这个“Olist项目”的核心，就是利用这份公开数据集，完整地走一遍电商数据分析的全流程。它适合谁呢？如果你是数据分析的初学者，它可以作为你的第一个“真实世界”项目；如果你是业务人员，可以通过它理解数据如何驱动运营决策；如果你是数据工程师，可以把它当作构建ETL管道和数据仓库的练手素材。接下来，我会把自己多次使用Olist数据集进行教学和商业分析的经验，拆解成可复现的步骤和深度思考，带你从数据导入一直走到可视化洞察。

2. 数据全景与核心业务逻辑拆解

在动手写任何代码之前，我们必须像架构师一样，先理解Olist数据集的整体结构和其背后模拟的业务逻辑。这是避免后续分析成为“无源之水”的关键。

2.1 数据集模块化解读

Olist数据集不是一个巨大的CSV文件，而是由9个核心CSV文件组成的星型 schema（星座模式）。理解它们之间的关系，是后续所有分析的基础。

1. 核心事实表 -olist_orders_dataset：这是整个数据宇宙的中心。每一行代表一个唯一的订单。包含订单创建时间、批准时间、发货时间、送达时间等关键生命周期时间戳，以及顾客ID、订单状态等。所有其他数据都直接或间接地与这张表关联。
2. 维度表群：
   • olist_customers_dataset：顾客维度表。通过customer_id与订单表关联。包含顾客所在的城市和州，这是进行地域分析的基础。
   • olist_products_dataset：商品维度表。通过product_id与订单商品表关联。包含商品分类（如“家具装饰”、“健康美容”）、尺寸、重量等。这里有一个关键点：商品名称是葡萄牙语，且已被脱敏处理，分类信息是后续商品分析的核心。
   • olist_sellers_dataset：卖家维度表。通过seller_id与订单商品表关联。包含卖家所在的城市和州，用于分析卖家分布和绩效。
   • olist_geolocation_dataset：地理位置维度表。它比较特殊，包含了巴西所有邮编对应的经纬度和城市/州信息。顾客和卖家的地理位置信息需要与此表关联，才能进行更深层次的地理空间分析（如计算配送距离）。
3. 业务过程事实表：
   • olist_order_items_dataset：订单商品事实表。这是订单和商品之间的“桥梁”。一个订单可能包含多个商品，这张表记录了每个商品在订单中的详细信息，包括价格、运费、卖家等。它是计算订单总金额、商品销售排行、卖家收入的直接数据源。
   • olist_order_payments_dataset：订单支付事实表。记录订单的支付信息，如支付类型（信用卡、银行卡、优惠券等）、分期次数、支付金额。一个订单也可能有多次支付（例如部分用优惠券，部分用信用卡）。
   • olist_order_reviews_dataset：订单评价事实表。记录顾客留下的评分（1-5分）和评论文本（葡萄牙语）。这是进行客户满意度分析和文本情感分析的关键。

注意：很多初学者会直接开始分析订单表，却忽略了order_items表。实际上，真正的销售额、爆款商品、卖家佣金等核心业务指标，都必须通过关联order_items表才能准确计算。把orders和order_items的关系理解为一对多，是正确分析的第一步。

2.2 关键业务指标定义

基于上述数据结构，我们可以定义出电商业务中最核心的指标（KPI）。这些指标将是后续SQL查询和可视化图表的目标。

• 营收相关：
  • 总销售额 (GMV)：SUM(order_items.price + order_items.freight_value)。注意，这里要用order_items表中的单价和运费，而不是orders表。
  • 订单总数：COUNT(DISTINCT orders.order_id)。
  • 平均订单价值 (AOV)：总销售额 / 订单总数。
  • 客单价：总销售额 / 购买顾客数。注意与AOV的区别，一个顾客可能有多个订单。
• 流量与转化相关（由于是交易数据，部分指标需推断）：
  • 热门商品类别：按products.product_category_name分组统计销售额和销量。
  • 复购率：购买次数大于1的顾客数 / 总购买顾客数。这需要基于顾客ID在订单表中的出现频次计算。
• 用户体验相关：
  • 订单履行时长：从order_purchase_timestamp到order_delivered_customer_date的时间差。可以分析平均配送时长、不同州的配送效率。
  • 客户满意度评分：order_reviews.review_score的平均分、分布情况。可以关联配送时长，分析配送是否影响评分。
• 卖家与供应链相关：
  • 卖家集中度：销售额最高的前10%卖家贡献的销售额占比。分析平台对头部卖家的依赖程度。
  • 支付方式分布：分析order_payments.payment_type，了解顾客偏好。

理解这些指标和数据结构后，我们的分析蓝图就清晰了：通过数据清洗和关联，将分散的9张表整合成一个可用于分析的数据模型，然后针对上述业务指标进行查询和可视化，最终回答具体的商业问题。

3. 数据工程：从原始CSV到分析就绪的数据仓库

有了蓝图，接下来就是“盖房子”——搭建我们的数据分析环境。我将使用最通用的技术栈：Python (Pandas) + SQLite + 可选(可视化工具)。你也可以用MySQL、PostgreSQL或直接上云数据仓库，但本地SQLite是最轻量、可复现的选择。

3.1 环境准备与数据加载

首先，确保你的Python环境安装了必要的库：pandas,sqlalchemy,numpy。使用Jupyter Notebook或任何你喜欢的IDE。

import pandas as pd import sqlite3 from sqlalchemy import create_engine # 创建SQLite内存数据库连接（也可持久化到文件） engine = create_engine('sqlite:///olist_analysis.db')

接下来，下载Olist数据集（可以从Kaggle获取），并加载所有CSV文件。这里有一个关键操作：在加载时指定正确的数据类型，尤其是日期时间列，可以避免后续大量转换操作。

# 定义文件路径和对应的表名 file_table_map = { 'olist_orders_dataset.csv': 'orders', 'olist_customers_dataset.csv': 'customers', 'olist_order_items_dataset.csv': 'order_items', # ... 其他文件依此类推 } # 循环读取并写入数据库 for file_name, table_name in file_table_map.items(): df = pd.read_csv(f'./data/{file_name}') # 特别处理orders表的日期列 if table_name == 'orders': date_columns = [col for col in df.columns if 'date' in col or 'timestamp' in col] for col in date_columns: df[col] = pd.to_datetime(df[col], format='%Y-%m-%d %H:%M:%S', errors='coerce') df.to_sql(table_name, engine, if_exists='replace', index=False) print(f"Loaded {table_name} with shape {df.shape}")

实操心得：使用errors='coerce'参数将无法解析的日期转换为NaT（Not a Time），这比让程序直接报错要好，因为真实数据中经常存在格式错误的脏数据。我们可以在后续的数据质量检查中统一处理这些缺失值。

3.2 数据清洗与质量检查

数据加载后，绝不能直接开始分析。必须进行系统的数据质量评估。我通常会创建一个数据质量报告，包含以下检查项：

1. 缺失值检查：统计每张表、每一列的缺失值比例。Olist数据集中，order_reviews表的review_comment_title和review_comment_message（评论标题和内容）缺失率可能非常高，这是正常的，因为很多顾客只打分不评论。但像orders表中的关键时间戳（如order_delivered_customer_date）如果大量缺失，则说明很多订单未完成，需要单独分析。
2. 异常值检测：特别是数值列。例如，order_items表中的price（商品价格）和freight_value（运费）。检查是否有价格为0或负数的记录（可能是测试订单、退款或数据错误），以及是否有运费高得离谱的订单（可能是指向偏远地区或数据异常）。

   -- 在SQL中快速检查价格异常 SELECT MIN(price), MAX(price), AVG(price) FROM order_items; SELECT * FROM order_items WHERE price <= 0 OR price > 10000; -- 设定一个合理的上限

3. 一致性检查：检查业务逻辑的一致性。例如，order_delivered_customer_date（客户收货日期）是否晚于order_approved_at（订单批准日期）？order_estimated_delivery_date（预计送达日期）是否合理？可以通过计算时间差来发现异常订单。
4. 重复值检查：检查主键是否唯一。例如，orders.order_id、customers.customer_id应该都是唯一的。

清洗策略：对于缺失和异常，通常有几种处理方式：删除、填充、保留并标记。对于价格异常，如果数量极少且明显错误，可以直接删除。对于时间戳缺失，如果对应的订单状态是“已送达”，但送达日期缺失，我们可以尝试用预计送达日期或订单批准日期加上平均配送时长进行合理估算，但如果无法估算，则应标记为“数据不完整”，在分析时注意其影响。

3.3 构建分析宽表

为了便于后续的SQL分析和可视化，我们最好提前构建一张或多张“宽表”（Denormalized Table），将关键信息集中在一起。这是提升分析效率的关键步骤。

我们可以通过SQL的JOIN操作，创建一张核心分析宽表：

CREATE TABLE analysis_wide_table AS SELECT o.order_id, o.order_status, o.order_purchase_timestamp, o.order_approved_at, o.order_delivered_carrier_date, o.order_delivered_customer_date, o.order_estimated_delivery_date, -- 顾客信息 c.customer_id, c.customer_city, c.customer_state, -- 商品和订单项信息 oi.order_item_id, oi.product_id, oi.seller_id, oi.price, oi.freight_value, -- 商品信息 p.product_category_name, p.product_weight_g, -- 卖家信息 s.seller_city, s.seller_state, -- 支付信息（这里假设取主要支付方式，实际可能需聚合） op.payment_type, op.payment_installments, op.payment_value, -- 评价信息 r.review_score FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id LEFT JOIN order_items oi ON o.order_id = oi.order_id LEFT JOIN products p ON oi.product_id = p.product_id LEFT JOIN sellers s ON oi.seller_id = s.seller_id LEFT JOIN ( SELECT order_id, payment_type, payment_installments, payment_value, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY payment_sequential) as rn FROM order_payments ) op ON o.order_id = op.order_id AND op.rn = 1 -- 取每个订单的第一条支付记录 LEFT JOIN order_reviews r ON o.order_id = r.order_id;

注意事项：这个宽表创建语句使用了LEFT JOIN，意味着即使某些信息缺失（如没有评价），订单记录也会被保留。同时，对于支付信息，一个订单可能有多种支付方式，这里用窗口函数ROW_NUMBER()取了第一条作为代表。在更精细的分析中，你可能需要单独分析支付表，或者将支付金额聚合后关联。创建宽表会显著增加数据体积，但换来了查询的极大便利。对于超大数据集，你可能需要依赖视图（View）或更现代化的数据湖查询引擎。

4. 核心业务分析SQL实战

现在，我们有了干净、整合的数据。是时候提出业务问题并用SQL寻找答案了。我将展示几个典型场景的SQL查询。

4.1 场景一：宏观业务健康度诊断

问题：平台整体销售额趋势如何？平均订单价值是多少？哪些月份是销售旺季？

-- 按月统计销售额、订单数、平均订单价值 SELECT STRFTIME('%Y-%m', order_purchase_timestamp) as year_month, COUNT(DISTINCT order_id) as order_count, SUM(price + freight_value) as total_sales, ROUND(SUM(price + freight_value) * 1.0 / COUNT(DISTINCT order_id), 2) as aov FROM analysis_wide_table WHERE order_status = 'delivered' -- 只统计已完成的订单 AND order_purchase_timestamp IS NOT NULL GROUP BY STRFTIME('%Y-%m', order_purchase_timestamp) ORDER BY year_month;

分析思路：通过这个查询，我们可以画出销售额的时间序列曲线。通常会发现明显的季节性波动（例如年末假日季销售额飙升）。AOV的波动也能反映促销策略的效果（如果AOV下降但订单数大幅上升，可能是低价促销拉动了销量但拉低了客单价）。

4.2 场景二：商品类目深度分析

问题：哪些商品类目贡献了主要销售额？它们的销售趋势有何不同？

-- 按商品类目统计销售额、销量及占比 WITH category_sales AS ( SELECT product_category_name, COUNT(DISTINCT order_id) as order_count, SUM(price) as category_sales, SUM(price + freight_value) as category_gmv FROM analysis_wide_table WHERE product_category_name IS NOT NULL GROUP BY product_category_name ) SELECT product_category_name, order_count, category_sales, category_gmv, ROUND(category_sales * 100.0 / SUM(category_sales) OVER (), 2) as sales_pct FROM category_sales ORDER BY category_sales DESC LIMIT 15;

分析思路：这里使用了通用表表达式（CTE）让查询更清晰。结果很可能显示“床品浴室”、“健康美容”、“运动休闲”等类目排名靠前。进一步，我们可以将排名前5的类目单独提取出来，进行月度销售趋势对比，观察哪些类目增长快，哪些进入平台期。

4.3 场景三：客户行为与价值分析

问题：我们的客户构成是怎样的？复购率如何？不同地区客户的消费能力有何差异？

-- 1. 客户地域分布与消费能力 SELECT customer_state, COUNT(DISTINCT customer_id) as customer_count, COUNT(DISTINCT order_id) as order_count, SUM(price + freight_value) as total_sales, ROUND(SUM(price + freight_value) * 1.0 / COUNT(DISTINCT customer_id), 2) as sales_per_customer FROM analysis_wide_table WHERE customer_state IS NOT NULL GROUP BY customer_state ORDER BY total_sales DESC; -- 2. 客户复购分析 WITH customer_orders AS ( SELECT customer_id, COUNT(DISTINCT order_id) as purchase_count FROM analysis_wide_table GROUP BY customer_id ) SELECT purchase_count, COUNT(customer_id) as number_of_customers, ROUND(COUNT(customer_id) * 100.0 / SUM(COUNT(customer_id)) OVER (), 2) as pct FROM customer_orders GROUP BY purchase_count ORDER BY purchase_count;

分析思路：第一个查询能帮你画出销售热力图，圣保罗（SP）、里约热内卢（RJ）等经济发达地区通常是消费主力。sales_per_customer指标能反映不同地区客户的“含金量”。第二个查询结果会显示，大部分客户可能只购买了一次（这是电商常态），但那些购买2次、3次以上的客户群体，虽然人数少，却贡献了可观的价值，他们是需要重点维护的“忠诚客户”。

4.4 场景四：物流与用户体验关联分析

问题：配送时长是否影响客户评分？不同州的配送效率如何？

-- 计算实际配送时长，并关联评分 SELECT CASE WHEN julianday(order_delivered_customer_date) - julianday(order_purchase_timestamp) <= 7 THEN '<=7天' WHEN julianday(order_delivered_customer_date) - julianday(order_purchase_timestamp) <= 15 THEN '8-15天' WHEN julianday(order_delivered_customer_date) - julianday(order_purchase_timestamp) <= 30 THEN '16-30天' ELSE '>30天' END as delivery_time_bucket, AVG(review_score) as avg_review_score, COUNT(*) as order_count FROM analysis_wide_table WHERE order_status = 'delivered' AND order_delivered_customer_date IS NOT NULL AND order_purchase_timestamp IS NOT NULL AND review_score IS NOT NULL GROUP BY delivery_time_bucket ORDER BY delivery_time_bucket;

分析思路：这个查询结果通常能清晰地展示一个负相关趋势：配送时间越长，平均评分越低。你可以将此分析细化到州级别，找出配送效率低下、急需物流优化的区域。例如，可能发现北部某些州的平均配送时长远超全国平均水平，且评分显著偏低。

5. 从数据到洞察：可视化与故事构建

SQL给出了数字答案，但如何让业务方一眼看懂？这就需要数据可视化。你可以使用Tableau、Power BI，或者Python的Matplotlib、Seaborn、Plotly库。

可视化建议：

1. 销售仪表盘：

   • 时间趋势：使用折线图展示月度GMV和订单数。用双Y轴或组合图，可以清晰看到趋势是否同步。
   • 类目构成：使用树状图或堆叠柱状图展示销售额前10的类目及其占比，直观显示核心业务。
   • 地域分布：使用巴西地图填充图，用颜色深浅表示各州销售额，一目了然看清市场重心。

2. 客户分析看板：

   • 新老客户占比：用环形图展示首次购买客户与复购客户的销售额贡献比例。
   • RFM客户分群：虽然Olist数据时间跨度有限，但可以尝试计算近度（Recency）、频度（Frequency）、消费金额（Monetary）三个维度，用散点图矩阵将客户分成“重要价值客户”、“重要发展客户”等群体。

3. 物流体验报告：

   • 配送时长分布直方图：展示大部分订单在多少天内送达。
   • 评分与时长关系散点图（或按分箱的柱状图）：直观验证“配送越快，评分越高”的假设。
   • 各州平均配送时长与平均评分对比气泡图：气泡大小代表订单量，既能看效率又能看规模。

构建数据故事：不要只是罗列图表。像讲故事一样组织你的分析报告。例如：“我们的业务在2017年第四季度迎来爆发式增长，主要由‘健康美容’和‘运动休闲’类目驱动。然而，数据发现，尽管销量增长，但平均订单价值下降了15%，这可能与当时的折扣策略有关。同时，北部地区的客户虽然订单量在增长，但配送时长是全国平均的1.8倍，导致该地区客户评分低于平均水平3.2分。建议下一步：1. 优化北部地区物流合作伙伴；2. 设计提升客单价的捆绑销售策略。”

6. 项目延伸与常见问题排查

6.1 项目延伸方向

Olist项目就像一个乐高底座，你可以在此基础上添加更多模块：

• 机器学习预测：利用历史数据，构建模型预测未来销售额、客户流失风险（基于复购间隔）、或商品需求（时间序列预测）。
• 评论文本情感分析：虽然评论是葡萄牙语，但可以使用翻译API或预训练的多语言模型（如mBERT）进行情感分析，将非结构化的文本转化为“满意度”分数，与配送时长、商品类目等进行交叉分析。
• 供应链优化模拟：结合卖家地理位置和顾客地理位置，计算理论上的最优仓储布局，以降低平均配送距离和成本。
• 构建完整的数据管道：使用Apache Airflow或Prefect等工具，将数据下载、清洗、转换、加载（ETL）到数据库、以及生成报告的过程自动化，形成一个可调度的数据流水线。

6.2 常见问题与解决方案实录

在实际操作中，你肯定会遇到各种问题。以下是我和学员们踩过的坑：

1. 问题：关联geolocation表时，一个邮编对应多个经纬度记录，导致JOIN后数据爆炸式增长。解决方案：地理位置表的设计如此。通常的作法不是直接JOIN，而是先对geolocation表进行聚合，例如取每个邮编的经纬度平均值或中心点，生成一个唯一的邮编-位置映射表，再用这个映射表去关联顾客和卖家表。

2. 问题：计算复购率时，如何准确定义“复购客户”？是以自然年计算，还是滚动时间窗口？解决方案：这取决于业务定义。在Olist这个静态数据集中，一个简单实用的定义是：在整个数据集时间范围内，下单次数大于1的客户即为复购客户。更精细的做法是定义时间窗口，例如“在首次购买后的180天内再次购买”，但这需要更复杂的SQL窗口函数逻辑。

3. 问题：商品分类名是葡萄牙语，看不懂，影响分析。解决方案：Olist通常提供一个名为product_category_name_translation.csv的翻译文件。务必加载这个文件，将商品主表与翻译表通过product_category_name关联，从而在分析中使用英语分类名。

4. 问题：order_items表中同一order_id下出现多个相同的product_id，这合理吗？解决方案：合理。这代表顾客在同一个订单中购买了多件相同的商品。在计算商品销量时，需要对order_item_id或数量进行求和，而不是简单的COUNT(DISTINCT product_id)。

5. 问题：使用LEFT JOIN创建宽表后，某些聚合指标（如总销售额）与直接从order_items表计算的结果有微小差异。解决方案：这是由JOIN引起的重复行导致的。例如，如果一个订单有多个支付记录，LEFT JOIN支付表会使该订单重复，导致销售额被重复计算。在聚合计算时，尤其是金额类计算，最安全的做法是回到最细粒度的事实表（如order_items）进行，或者确保你的JOIN逻辑不会产生重复。在宽表中进行聚合时，可以使用COUNT(DISTINCT order_id)来避免重复计数，但对于金额求和，如果重复了则很难后处理，因此设计宽表时的JOIN逻辑至关重要。

这个Olist项目之所以经典，正是因为它不完美，它真实。处理它的过程，就是处理真实商业数据的一次完整预演。当你能够清晰地回答从宏观趋势到微观体验的各种业务问题，并将这些洞察用可视化的方式呈现出来时，你就已经掌握了数据驱动决策的核心技能。