---

前言

本次分析的数据，来自巴西最大的电商平台之一Olist在Kaggle上公开的真实数据集。它完整记录了该平台2016年9月至2018年8月期间的订单全链路信息，涵盖约10万笔订单、7.3万个商品、9.6万名客户。
通过这个数据集可以让我们像商业分析师一样，从最底层的交易记录入手，去发现业务中那些正在发生、却未被看见的事情或问题。

---

一、数据集介绍

（一）、数据来源

https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce?select=product_category_name_translation.csv

（二）、表关系清单

原始数据并非一张现成的报表，而是由9张相互关联的CSV表组成。这是一个真实且典型的业务数据环境——信息是分散且需要自主整合的。为了理解它们之间的关系，我做了以下梳理：

表名	内容	核心指标	表关联键
orders	订单主表	订单状态、购买时间、物流时效	order_id
order_items	订单商品明细表	每个订单买了哪些商品、数量、价格	order_id, product_id
products	商品表	商品品类、尺寸、重量等属性	product_id
customers	客户信息表	客户所在城市、州	customer_id
payments	支付明细表	支付方式、分期数、总金额	order_id
reviews	订单评价表	评分、评论文本标题和正文	order_id
sellers	商家信息表	商家所在城市、州	seller_id
geolocation	地理字典	邮政编码对应的经纬度	zip_code_prefix
translation	类别名称信息表	将葡萄牙语品类名译为英文	category_name

二、数据处理及指标搭建

1、项目执行流程分为四个阶段：数据清洗、数据建模、业务分析和报告输出。
2、业务线：一个客户（客户表）在某天下了个订单（订单表），买了几个商品（商品表），怎么付的款（支付表），卖家什么时候发的货、物流耗时多久（订单表），最后他给了什么评价（评论表）。
3、这里我直接在Power Query中合并为“宽表”进行分析，不使用Power Pivot中的“数据建模”。

注意：
Power Query合并 (构建宽表)：适合探索式分析和交付静态报告。输出一个固定的业务和结论，选这个。
Power Pivot建模 (构建关系)：适合交互式探索和动态仪表板。当需要反复切换分析维度，实时计算不同视角下的指标时，选这个

（一）、数据处理

1、导入数据

打开Excel，在数据选项卡，选择导入CSV数据，在选项卡中选择“仅创建连接”和“添加到数据模型”，共计9个表。最终结果如下：

2、简单清洗数据

清洗思路：数据清洗分为两步，第一步先进行简单数据清洗，再合并为宽表，合并之后的宽表再进行完整的数据清洗。

A、删除重复项

这一步得必须得操作，如果不删除唯一键中的重复值，确保用于连接的列（如order_id, product_id），在各自表中是唯一的。
同时，去重是有要求的：products表的product_id 去重、customers表的customer_id 去重、sellers表的seller_id 去重。

如下图：

B、处理会引起数据膨胀的表(支付表和订单商品表)

这步骤的目的是提前将“一对多”的表聚合成“一对一”。
（1）、支付表： 一个订单可能分多期支付，导致一个order_id有多行。合并前，必须用“分组依据”，按order_id对支付金额求和，确保每个订单只有一行支付记录。
（2）、订单商品表： 这是唯一一个需要保留多行的表，后续要分析“订单-商品”级别的粒度。

操作：对支付表进行聚合
第一步：选择olist_order_payments_dataset表，并打开分组依据

第二步：分组依据，是“按什么分类”，类似SQL中的分组，这里我们选择 order_id 列。
如下图：

C、格式统一与缺失值初步处理

日期列： 将所有包含日期时间的列，设置为“日期”或“日期/时间”格式。
关键列的空值： 查看order_id, customer_id等关键列有无空值。有则直接删除整行，因为无法关联。

3、合并宽表

连接思路：将所有信息汇总到订单这条业务线上。需要把订单主表(olist_orders_dataset)作为左表，其他所有表都作为右表，用各自的关联键进行左连接。
连接顺序：
订单主表 合并 订单商品明细表（用 order_id 关联）

上一步表 合并 商品信息表（用 product_id 关联）

上一步表 合并 客户信息表（用 customer_id 关联）

上一步表 合并 支付信息表（用 order_id 关联）

上一步表 合并 订单评论表（用 order_id 关联）

第一步：合并订单商品明细，变“一行一单”为“一行一商品”
将olist_orders_dataset（订单主表）与olist_order_items_dataset（订单商品明细表）进行合并。将第一次合并的表命名为合并1-订单与商品。

第二步：合并商品详情，知道卖的是什么
将之前合并好的 “合并1-订单与商品” 为左表再进行合并操作。右表：olist_products_dataset，选中 product_id 列。将第二次合并表命名为 “合并2-接入商品信息”。

第三步：第3步：接入客户信息，知道是谁买的
继续合并，以 “合并2-接入商品信息” 为左表，再次合并。右表：olist_customers_dataset，选中 customer_id 列。将第三次合并的表命名为 合并3-接入客户信息。

第四步：接入支付信息，知道钱怎么走的
以 “合并3-接入客户信息” 为左表，准备合并支付明细表。这步骤前需要确定一个点，就是一个订单可能有多笔分期付款，支付表里一个order_id会有多行。合并前，需要先对支付表做个分组聚合（前面已做）。将表命名为合并4-接入支付信息。

第五步：接入用户反馈，知道满不满意
以 “合并4-接入支付信息” 为左表，合并评论表。将表命名为 分析宽表_主数据。。

最后将数据上传至连接和模型。到此，PQ的操作就结束了。后续进入PP操作。

（二）、创建业务分析指标

第一层：全局业务健康指标

1、订单总数指标
指标定义：衡量销售业务体量的指标。用DISTINCTCOUNT确保即使宽表里一个订单因多个商品而有多行，也只会被算作一个订单。
指标公式：订单总数 = DISTINCTCOUNT(‘分析宽表_主数据’[order_id])。

打开excel，按照下图操作。

2、平均订单金额
指标定义：每笔订单的平均收入。用之前做好的 total_payment_value 字段。
指标公式：平均订单金额 ==DIVIDE(SUM(‘分析宽表_主数据’[olist_order_payments_dataset.total_payment_value]),[订单总数])。

操作如下图：

第二层：物流体验诊断指标

1、平均配送耗时
这部前提需要在Power Query中自定义添加一列，该新增的列为，实际订单送达日期-购买时间。
具体操作如下图：

上述操作完成之后，在power pivot中刷新即可出现计算好的数据列。

2、按时交付率
在计算这个指标时需要在Power Query中添加一个条件列，计算筛选出配送延迟的数据。
业务点： order_delivered_customer_date是实际送达的时间字段，order_estimated_delivery_date是预计送达的字段，如果order_delivered_customer_date晚于order_estimated_delivery_date的时间，是未超时的，这是针对已送出的订单的时间配置规则。
对于未送达的订单，送达日期可能为空，所以需要再加一条规则，这时的订单状态为未送达。
具体操作如下图：

计算按时交付率指标，具体操作如下图：

3、延迟订单数
公式：延迟订单数 = CALCULATE([订单总数], ‘分析宽表’[is_late] = “Yes”)
具体操作如下：

第三层：用户指标

1、差评率
首先进入Power Query中，添加一列条件列，为总体评价列。这里需要注意，没收到评论（沉默用户）不等于不满意，所以标记为 No。
具体操作如下：

最后进入power pivot中，创建差评率指标。
公式：=DIVIDE(CALCULATE([订单总数], ‘分析宽表_主数据’[is_bad_review] = “Yes”
),CALCULATE([订单总数], NOT(ISBLANK(‘分析宽表_主数据’[olist_order_reviews_dataset.review_score]))))

2、延迟订单的差评率 vs 非延迟订单的差评率
拉取数据透视表，即可对比出来。

可以直接看到延迟订单的差评率可能是非延迟的数倍（重点）。

3、重复购买率（复购率）
这里计算下单超过2单及以上的用户占比。
首先计算复购客户数指标：
=CALCULATE(DISTINCTCOUNT(‘分析宽表_主数据’[customer_id]),FILTER(VALUES(‘分析宽表_主数据’[customer_id]),CALCULATE([订单总数]) > 1))

但是呢，该度量值为空白，所以为了进一步验证复购客户数这个指标，可以计算出最大订单数，最大订单数 = MAXX(VALUES(‘分析宽表_主数据’[customer_id]), [订单总数])
最大订单数为1，说明在这个数据集涵盖的两年窗口内，没有任何一个客户重复购买。
“复购客户数”指标恒为0，继续围绕它建模已经没有意义，放弃复购率，改为分析“一次性生意”的代价（重点）。

三、业务分析

（一）、整体业务情况

核心解读：
1、销售总额 2057万：平台在两年内实现超两千万元的销售额，验证了其市场可行性和稳定的交易规模。

2、订单总数 99,441 & 客户总数 99,441：订单数与客户数完全一致，表明统计周期内每位客户仅完成一次购买，复购率为零。

3、核心问题分析：
获客成本居高不下：依赖单一客户价值，平台需持续投入高额拉新成本以维持销售；
客户价值缺失：复购作为利润核心来源的完全缺失，暴露出商业模式可持续性缺陷；
平台竞争力存疑：零复购现象折射出商品、价格或用户体验等关键环节存在短板。

4、核心矛盾：初步数据扫描揭示了一个核心矛盾：平台拥有近10万独立客户，却无人复购。这是一门“流量驱动”而非“客户驱动”的生意。

下一步分析：找出客户不再回头的根本原因，直接切入离“用户情绪”最近的数据——评价与物流体验，去定位是什么在制造不满、扼杀信任。

（二）、物流延迟与差评分析

A、延迟 vs 非延迟订单的差评率对比：

核心解读：
1、总计差评率22.98%：这意味着，在留下评价的用户中，每4-5个人就有1个给出了差评。说明近四分之一的有评价用户经历了不满意。这为‘为何无人复购’提供了第一条线索。”

2、“对比数据清晰显示：物流延迟是差评率飙升的‘引爆点’。按时交付时差评率为17%，而一旦延迟，差评率骤升至65.47%，两者相差近4倍。物流体验，就是那条把用户推向不满深渊的红线。”

3、“特别值得警惕的是，未交付订单的差评率高达85.68%，这指向了供应链执行层面的硬伤——库存虚标、配送丢失或地址错误等问题，亟需根因排查。”

4、非延迟订单的差评率是17.26%：这个数字虽然也不低，但它反映了商品质量、价格、描述等综合因素。可以视为“基准不满意率”。

业务建议：
1、立即设立物流体验红线和熔断机制；
2、对“未交付”问题零容忍，启动根因倒查；
3、改善非延迟订单的17%差评率。

B、物流耗时与评分散点图：
通过下图进一步深入分析‘配送耗时’与‘用户评分’的关系。通过透视表提取每个配送天数对应的平均评分，我们发现了一条清晰的下滑曲线：配送每慢一天，用户的满意度就流失一点。没有‘容忍临界点’，只有持续累积的负面体验。这条曲线解释了我们之前看到的‘延迟订单差评率高达65%’——这不是一个突然的爆发，而是物流体验长期失血后的总爆发。

（三）、哪个品类的物流拖后腿最严重

A、各品类按时交付率对比

最上面就是表现最差的。

B、各品类的“物流延迟”与“差评率”关系
这里我只筛选订单是大于等于200的数量进行分析

结论一：
Top 10 品类的按时交付率惊人地集中在 89%-91% 之间，数据表明，核心品类的订单中有近10%无法按时送达。结合此前得出的关键结论（延迟订单差评率高达65.47%），这10%的交付延迟正在持续产生大量差评，这一问题已超越单个品类范畴，反映出平台整体履约能力的系统性不足。

结论二：
办公家具品类（moveis_escritorio）表现堪忧：差评率高达37.29%（超出平台均值60%），按时交付率仅为89.47%。家居舒适品类（casa_conforto）同样表现不佳，差评率达32.41%，交付准时率88.41%。音频设备（audio）因物流问题严重，差评率31.41%，按时交付率86.57%。

其中，办公家具品类已成为平台口碑的最大隐患。尽管该品类仅1273单，却创下37%的惊人差评率。值得注意的是，其物流表现并非最差，这表明产品本身质量、包装或描述可能存在严重问题，亟需立即进行问题排查和改进。

结论三：
请注意 alimentos (食品) 和 bebidas (饮料)：它们的按时交付率分别为 88.22% 和 90.24%，低于平台均值，但它们的差评率仅为 16.40% 和 19.59%，远低于平台均值 23%。这说明：用户对某些品类的物流延迟容忍度天生就高。 在最终报告中，你可以这样升华：物流优化需“区别对待”，在资源有限时，应先聚焦于因延迟而差评率飙升的品类。

四、优化建议

基于上述分析，提出3个优先级的改进方向，旨在将差评率从当前的23%拉回健康水平，并从根本上改善客户体验。

1、建立物流体验三级响应机制
① 一级预警（延误1-3天）：自动触发道歉邮件 + 小额补偿券，在用户情绪恶化前主动干预。
② 二级预警（延误4-7天）：客服人工介入，提供实质性补偿（如运费返还），并同步排查延误原因。
③ 三级熔断（延误超7天或未交付）：启动订单升级，专人跟进，考虑先行补发或全额退款。

2、对重灾区品类启动专项治理
① 大件/易损品类：复盘包装破损率、配送商资质、页面描述准确性，必要时对高风险品类设置更保守的预计送达时间。
② 高价值电子品类：专项分析差评评论文本，区分是“物流慢”、“商品质量问题”还是“描述不符”，分类施策。
③ 优选品类（食品饮料）：虽然物流不快但差评率低，可适度放宽交付预期，将优化资源集中在高差评品类上。

3、搭建供应链健康度监控仪表板
① 核心监控指标：按时交付率、延迟订单差评率、各品类平均配送耗时、未交付订单占比。
② 告警规则：任一品类按时交付率低于85%，或延迟差评率超过50%，自动触发根因分析。