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

1. 项目概述从零开始理解Olist电商数据分析平台如果你在电商行业待过或者对数据驱动业务增长感兴趣那你大概率听说过或者接触过“Olist”这个名字。它不是一个直接面向消费者的购物网站而是一个在数据科学和商业分析领域声名鹊起的“宝藏”数据集和商业案例。简单来说Olist提供了一个真实、完整、脱敏的巴西电商交易数据集涵盖了从2016年到2018年超过10万笔订单的详细信息。这个项目之所以重要是因为它把一个复杂的、多模块的电商后台系统抽象成了一个可供所有人研究、学习和实践的数据金矿。我第一次接触Olist数据集是在为一个中小型电商客户搭建数据分析看板的时候。客户抱怨说市面上很多教程的数据都太“干净”、太“理想”了跟实际业务中混乱、缺失、充满异常的数据根本不是一回事。直到我找到了Olist它完美地模拟了一个真实电商平台的数据生态有订单、有客户、有商品、有卖家、有支付记录、有评论甚至还有物流追踪和地理位置信息。这些数据分散在多个互相关联的表中你需要像真正的数据工程师或分析师一样进行数据清洗、关联、整合才能挖掘出商业价值。所以这个“Olist项目”的核心就是利用这份公开数据集完整地走一遍电商数据分析的全流程。它适合谁呢如果你是数据分析的初学者它可以作为你的第一个“真实世界”项目如果你是业务人员可以通过它理解数据如何驱动运营决策如果你是数据工程师可以把它当作构建ETL管道和数据仓库的练手素材。接下来我会把自己多次使用Olist数据集进行教学和商业分析的经验拆解成可复现的步骤和深度思考带你从数据导入一直走到可视化洞察。2. 数据全景与核心业务逻辑拆解在动手写任何代码之前我们必须像架构师一样先理解Olist数据集的整体结构和其背后模拟的业务逻辑。这是避免后续分析成为“无源之水”的关键。2.1 数据集模块化解读Olist数据集不是一个巨大的CSV文件而是由9个核心CSV文件组成的星型 schema星座模式。理解它们之间的关系是后续所有分析的基础。核心事实表 -olist_orders_dataset这是整个数据宇宙的中心。每一行代表一个唯一的订单。包含订单创建时间、批准时间、发货时间、送达时间等关键生命周期时间戳以及顾客ID、订单状态等。所有其他数据都直接或间接地与这张表关联。维度表群olist_customers_dataset顾客维度表。通过customer_id与订单表关联。包含顾客所在的城市和州这是进行地域分析的基础。olist_products_dataset商品维度表。通过product_id与订单商品表关联。包含商品分类如“家具装饰”、“健康美容”、尺寸、重量等。这里有一个关键点商品名称是葡萄牙语且已被脱敏处理分类信息是后续商品分析的核心。olist_sellers_dataset卖家维度表。通过seller_id与订单商品表关联。包含卖家所在的城市和州用于分析卖家分布和绩效。olist_geolocation_dataset地理位置维度表。它比较特殊包含了巴西所有邮编对应的经纬度和城市/州信息。顾客和卖家的地理位置信息需要与此表关联才能进行更深层次的地理空间分析如计算配送距离。业务过程事实表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, errorscoerce) df.to_sql(table_name, engine, if_existsreplace, indexFalse) print(fLoaded {table_name} with shape {df.shape})实操心得使用errorscoerce参数将无法解析的日期转换为NaTNot a Time这比让程序直接报错要好因为真实数据中经常存在格式错误的脏数据。我们可以在后续的数据质量检查中统一处理这些缺失值。3.2 数据清洗与质量检查数据加载后绝不能直接开始分析。必须进行系统的数据质量评估。我通常会创建一个数据质量报告包含以下检查项缺失值检查统计每张表、每一列的缺失值比例。Olist数据集中order_reviews表的review_comment_title和review_comment_message评论标题和内容缺失率可能非常高这是正常的因为很多顾客只打分不评论。但像orders表中的关键时间戳如order_delivered_customer_date如果大量缺失则说明很多订单未完成需要单独分析。异常值检测特别是数值列。例如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; -- 设定一个合理的上限一致性检查检查业务逻辑的一致性。例如order_delivered_customer_date客户收货日期是否晚于order_approved_at订单批准日期order_estimated_delivery_date预计送达日期是否合理可以通过计算时间差来发现异常订单。重复值检查检查主键是否唯一。例如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库。可视化建议销售仪表盘时间趋势使用折线图展示月度GMV和订单数。用双Y轴或组合图可以清晰看到趋势是否同步。类目构成使用树状图或堆叠柱状图展示销售额前10的类目及其占比直观显示核心业务。地域分布使用巴西地图填充图用颜色深浅表示各州销售额一目了然看清市场重心。客户分析看板新老客户占比用环形图展示首次购买客户与复购客户的销售额贡献比例。RFM客户分群虽然Olist数据时间跨度有限但可以尝试计算近度Recency、频度Frequency、消费金额Monetary三个维度用散点图矩阵将客户分成“重要价值客户”、“重要发展客户”等群体。物流体验报告配送时长分布直方图展示大部分订单在多少天内送达。评分与时长关系散点图或按分箱的柱状图直观验证“配送越快评分越高”的假设。各州平均配送时长与平均评分对比气泡图气泡大小代表订单量既能看效率又能看规模。构建数据故事不要只是罗列图表。像讲故事一样组织你的分析报告。例如“我们的业务在2017年第四季度迎来爆发式增长主要由‘健康美容’和‘运动休闲’类目驱动。然而数据发现尽管销量增长但平均订单价值下降了15%这可能与当时的折扣策略有关。同时北部地区的客户虽然订单量在增长但配送时长是全国平均的1.8倍导致该地区客户评分低于平均水平3.2分。建议下一步1. 优化北部地区物流合作伙伴2. 设计提升客单价的捆绑销售策略。”6. 项目延伸与常见问题排查6.1 项目延伸方向Olist项目就像一个乐高底座你可以在此基础上添加更多模块机器学习预测利用历史数据构建模型预测未来销售额、客户流失风险基于复购间隔、或商品需求时间序列预测。评论文本情感分析虽然评论是葡萄牙语但可以使用翻译API或预训练的多语言模型如mBERT进行情感分析将非结构化的文本转化为“满意度”分数与配送时长、商品类目等进行交叉分析。供应链优化模拟结合卖家地理位置和顾客地理位置计算理论上的最优仓储布局以降低平均配送距离和成本。构建完整的数据管道使用Apache Airflow或Prefect等工具将数据下载、清洗、转换、加载ETL到数据库、以及生成报告的过程自动化形成一个可调度的数据流水线。6.2 常见问题与解决方案实录在实际操作中你肯定会遇到各种问题。以下是我和学员们踩过的坑问题关联geolocation表时一个邮编对应多个经纬度记录导致JOIN后数据爆炸式增长。解决方案地理位置表的设计如此。通常的作法不是直接JOIN而是先对geolocation表进行聚合例如取每个邮编的经纬度平均值或中心点生成一个唯一的邮编-位置映射表再用这个映射表去关联顾客和卖家表。问题计算复购率时如何准确定义“复购客户”是以自然年计算还是滚动时间窗口解决方案这取决于业务定义。在Olist这个静态数据集中一个简单实用的定义是在整个数据集时间范围内下单次数大于1的客户即为复购客户。更精细的做法是定义时间窗口例如“在首次购买后的180天内再次购买”但这需要更复杂的SQL窗口函数逻辑。问题商品分类名是葡萄牙语看不懂影响分析。解决方案Olist通常提供一个名为product_category_name_translation.csv的翻译文件。务必加载这个文件将商品主表与翻译表通过product_category_name关联从而在分析中使用英语分类名。问题order_items表中同一order_id下出现多个相同的product_id这合理吗解决方案合理。这代表顾客在同一个订单中购买了多件相同的商品。在计算商品销量时需要对order_item_id或数量进行求和而不是简单的COUNT(DISTINCT product_id)。问题使用LEFT JOIN创建宽表后某些聚合指标如总销售额与直接从order_items表计算的结果有微小差异。解决方案这是由JOIN引起的重复行导致的。例如如果一个订单有多个支付记录LEFT JOIN支付表会使该订单重复导致销售额被重复计算。在聚合计算时尤其是金额类计算最安全的做法是回到最细粒度的事实表如order_items进行或者确保你的JOIN逻辑不会产生重复。在宽表中进行聚合时可以使用COUNT(DISTINCT order_id)来避免重复计数但对于金额求和如果重复了则很难后处理因此设计宽表时的JOIN逻辑至关重要。这个Olist项目之所以经典正是因为它不完美它真实。处理它的过程就是处理真实商业数据的一次完整预演。当你能够清晰地回答从宏观趋势到微观体验的各种业务问题并将这些洞察用可视化的方式呈现出来时你就已经掌握了数据驱动决策的核心技能。