配套在线SQL练习平台：https://zoengsang.cloud/sql-practice/app/

项目简介

什么是Olist？

Olist 是巴西最大的电商平台之一，成立于2015年。它连接了巴西各地的小型零售商，让商家可以通过Olist的平台将商品销售给全国客户，并使用Olist的物流网络完成配送。

为什么选这个项目？

  • 真实电商数据，包含订单、客户、商品、支付等完整业务链条
  • 数据量适中（10万订单），适合个人电脑处理
  • 涵盖数据分析核心技能：SQL、Python、可视化、机器学习
  • 面试时展示RFM和Cohort分析是加分项

一、数据集详解

1.1 数据表关系

Olist数据集包含9个关联表，核心关系如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
                ┌─────────────┐
                │   orders    │  ← 订单主表（核心）
                │  (99,441)   │
                └──────┬──────┘
       ┌─────────────┼─────────────┐
       │             │             │
┌──────▼──────┐ ┌────▼────┐ ┌──────▼──────┐
│   customers │ │ payments│ │ order_items │
│  (99,441)   │ │(103,886)│ │  (112,650)  │
└─────────────┘ └─────────┘ └──────┬──────┘
                                    │
                          ┌─────────┼─────────┐
                          │         │         │
                     ┌────▼────┐ ┌──▼───┐ ┌───▼────┐
                     │ products│ │sellers│ │ reviews│
                     │ (32,951)│ │(3,095)│ │(99,224)│
                     └─────────┘ └───────┘ └────────┘

更直观的数据表关系图：

Olist数据表关系图

图1：Olist数据集9个表的关联关系，orders是核心订单表，连接customers、payments、order_items等表

1.2 各表详细说明

表名 行数 核心字段 用途
orders 99,441 order_id, customer_id, order_status, order_purchase_timestamp 订单主表，记录订单状态和多个时间戳
customers 99,441 customer_id, customer_unique_id, customer_state, customer_city 客户信息，注意customer_id每单都不同，customer_unique_id才是真实客户ID
order_items 112,650 order_id, product_id, seller_id, price, freight_value 订单商品明细，一个订单可能有多个商品
payments 103,886 order_id, payment_type, payment_installments, payment_value 支付信息，支持多种支付方式组合
reviews 99,224 order_id, review_score, review_comment_message 用户评价，1-5分评分
products 32,951 product_id, product_category_name, product_weight_g… 商品信息，包含尺寸重量等
sellers 3,095 seller_id, seller_state, seller_city 卖家信息
geolocation 1,000,000 zip_code_prefix, lat, lng 地理位置，邮编对应的经纬度
category_translation 71 category_name, category_name_english 品类翻译，葡语转英语

二、数据清洗（MySQL版）

2.1 为什么要清洗数据？

原始数据存在以下问题：

  1. 时间字段是字符串，需要转为datetime
  2. 邮编经纬度重复，一个邮编对应多个坐标
  3. 缺失值：订单实际送达时间有大量缺失（未送达订单正常）
  4. 异常值：分期数为0（应该至少1期）、时间在购买之前

2.2 数据类型转换

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 修改时间字段为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;

-- 修改价格字段为DECIMAL（精确计算金额）
ALTER TABLE olist_order_items_dataset
    MODIFY price DECIMAL(10,2),
    MODIFY freight_value DECIMAL(10,2);

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

2.3 处理重复数据

问题：geolocation表一个邮编对应多个经纬度坐标（每次下单记录的定位略有不同）

解决方案：按邮编分组，取平均经纬度

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建临时表存储去重后的地理位置
CREATE TABLE geolocation_clean AS
SELECT 
    geolocation_zip_code_prefix,
    ROUND(AVG(geolocation_lat), 7) as lat,
    ROUND(AVG(geolocation_lng), 7) as lng,
    MAX(geolocation_city) as city,
    MAX(geolocation_state) as state
FROM olist_geolocation_dataset
GROUP BY geolocation_zip_code_prefix;

-- 添加索引加速查询
CREATE INDEX idx_zip ON geolocation_clean(geolocation_zip_code_prefix);

2.4 处理缺失值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 查看各表缺失情况
SELECT 
    COUNT(*) as total,
    COUNT(order_delivered_customer_date) as delivered_count,
    COUNT(*) - COUNT(order_delivered_customer_date) as missing_count
FROM olist_orders_dataset;

-- 结果：99,441条记录中，2,965条缺少实际送达时间（未送达订单）
-- 处理：保留这些记录，在分析已送达订单时过滤

-- 删除明显错误的记录（送达时间在购买时间之前）
DELETE FROM olist_orders_dataset
WHERE order_delivered_customer_date < order_purchase_timestamp;

-- 删除分期数为0的异常数据（应该至少1期）
DELETE FROM olist_order_payments_dataset
WHERE payment_installments = 0;

-- 删除未定义的支付方式
DELETE FROM olist_order_payments_dataset
WHERE payment_type = 'not_defined';

2.5 添加时间维度字段（便于分析）

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 添加日期、小时列，便于按时间段分析
ALTER TABLE olist_orders_dataset
    ADD COLUMN purchase_date DATE,
    ADD COLUMN purchase_hour INT;

UPDATE olist_orders_dataset
SET 
    purchase_date = DATE(order_purchase_timestamp),
    purchase_hour = HOUR(order_purchase_timestamp);

-- 创建索引加速时间查询
CREATE INDEX idx_date ON olist_orders_dataset(purchase_date);
CREATE INDEX idx_hour ON olist_orders_dataset(purchase_hour);

三、核心业务指标分析

3.1 用户地域分布

问题：用户主要分布在巴西哪些地区？

1
2
3
4
5
6
7
8
9
10
-- 各州用户数量分布
SELECT 
    c.customer_state as 州,
    COUNT(DISTINCT c.customer_unique_id) as 用户数,
    ROUND(COUNT(DISTINCT c.customer_unique_id) * 100.0 / 
          SUM(COUNT(DISTINCT c.customer_unique_id)) OVER(), 2) as 占比
FROM olist_customers_dataset c
GROUP BY c.customer_state
ORDER BY 用户数 DESC
LIMIT 10;

查询结果：

州 用户数 占比
SP 41,746 43.42%
RJ 12,852 13.37%
MG 11,627 12.10%
RS 5,466 5.69%
PR 5,045 5.25%

关键发现：

  • 43%的用户来自SP州（圣保罗州），这是巴西人口最多、经济最发达的州
  • **前三个州（SP+RJ+MG）占比近70%**，集中在东南部沿海
  • 这与巴西人口分布和经济发达程度高度一致

业务建议：

  • 重点在SP州投入营销资源
  • 北部和东北部用户较少，有增长空间

3.2 用户活跃度分析（DAU/MAU）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 日活跃用户数（DAU）趋势
SELECT 
    purchase_date as 日期,
    COUNT(DISTINCT c.customer_unique_id) as DAU,
    COUNT(*) as 订单数
FROM olist_orders_dataset o
JOIN olist_customers_dataset c ON o.customer_id = c.customer_id
GROUP BY purchase_date
ORDER BY purchase_date;

-- 月活跃用户数（MAU）
SELECT 
    DATE_FORMAT(purchase_date, '%Y-%m') as 月份,
    COUNT(DISTINCT c.customer_unique_id) as MAU
FROM olist_orders_dataset o
JOIN olist_customers_dataset c ON o.customer_id = c.customer_id
GROUP BY DATE_FORMAT(purchase_date, '%Y-%m')
ORDER BY 月份;

关键发现：

  • DAU总体呈上升趋势
  • **2017年11月24日DAU暴增327%**，这是”黑色星期五”促销活动的效果
  • 2017年11月后MAU趋于平稳，说明平台进入成熟期

3.3 用户下单时间偏好

1
2
3
4
5
6
7
8
-- 24小时下单分布
SELECT 
    purchase_hour as 小时,
    COUNT(*) as 订单数,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) as 占比
FROM olist_orders_dataset
GROUP BY purchase_hour
ORDER BY purchase_hour;

查询结果分析：

时间段 订单占比 特点
0-6点 7.2% 深夜，订单最少
6-12点 32.1% 上午，订单快速增长
12-18点 38.5% 下午，订单最高峰
18-22点 22.3% 晚间，订单逐渐减少

关键发现：

  • 用户全天都在下单，不受上班时间影响
  • 中午12点和下午6点略有下降（饭点）
  • 晚上10点后明显下降

业务建议：

  • 促销活动安排在上午10点到晚上10点
  • 客服值班重点安排在高活跃时段

3.4 支付方式分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 支付方式偏好
SELECT 
    payment_type as 支付方式,
    COUNT(DISTINCT order_id) as 订单数,
    ROUND(COUNT(DISTINCT order_id) * 100.0 / 
          SUM(COUNT(DISTINCT order_id)) OVER(), 2) as 占比
FROM olist_order_payments_dataset
GROUP BY payment_type
ORDER BY 订单数 DESC;

-- 信用卡分期情况
SELECT 
    payment_installments as 分期数,
    COUNT(*) as 订单数,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) as 占比
FROM olist_order_payments_dataset
WHERE payment_type = 'credit_card'
GROUP BY payment_installments
ORDER BY 分期数;

关键发现：

  • 73.9%的订单使用信用卡支付
  • 19.7%使用boleto（巴西本地线下支付方式）
  • 超过65%的信用卡订单选择分期付款（1-10期为主）
  • 说明巴西消费者习惯分期消费

3.5 用户评分分析

1
2
3
4
5
6
7
8
9
-- 评分分布
SELECT 
    review_score as 评分,
    COUNT(*) as 评价数,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) as 占比
FROM olist_order_reviews_dataset
WHERE review_score IS NOT NULL
GROUP BY review_score
ORDER BY 评分 DESC;

查询结果：

评分 占比 说明
5分 57.8% 好评
4分 19.3% 好评
3分 8.2% 中评
2分 4.6% 差评
1分 10.1% 差评

关键指标：

  • 好评率（4-5分）：77.1%
  • 差评率（1-2分）：14.7%
  • 总体满意度较高，但有改进空间

四、RFM客户价值分层模型

4.1 什么是RFM模型？

RFM 是客户价值分析的经典模型：

指标 英文 含义 业务解读
R Recency 最近一次购买距今天数 R越小越好，刚买的客户更容易复购
F Frequency 消费频率（购买次数） F越大越好，忠诚客户
M Monetary 消费总金额 M越大越好，高价值客户

分层逻辑：

  • 将R、F、M分别按高低分为2档（或5档）
  • 组合成 2×2×2=8 类客户（或 5×5×5=125 类）
  • 针对不同类别制定差异化运营策略

4.2 RFM指标计算（MySQL实现）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 步骤1：创建订单明细视图（整合订单+客户+支付）
CREATE VIEW order_detail AS
SELECT 
    o.order_id,
    o.purchase_date,
    c.customer_unique_id,
    SUM(p.payment_value) as payment_value
FROM olist_orders_dataset o
JOIN olist_customers_dataset c ON o.customer_id = c.customer_id
JOIN olist_order_payments_dataset p ON o.order_id = p.order_id
WHERE o.order_status = 'delivered'  -- 只统计已送达订单
GROUP BY o.order_id, o.purchase_date, c.customer_unique_id;

-- 步骤2：计算每个客户的RFM值
WITH rfm_base AS (
    SELECT 
        customer_unique_id,
        -- R值：最近一次购买距今天数（越小越好）
        DATEDIFF('2018-10-18', MAX(purchase_date)) as recency,
        -- F值：购买次数
        COUNT(DISTINCT order_id) as frequency,
        -- M值：总消费金额
        SUM(payment_value) as monetary
    FROM order_detail
    GROUP BY customer_unique_id
)
SELECT * FROM rfm_base LIMIT 10;

查询结果示例：

customer_unique_id recency frequency monetary
00012a2ce6f8dcda 175 1 114.74
000161a0586007a1 244 1 65.91
0001fd6190eda1bb 40 1 53.79

重要发现：绝大多数客户只购买1次！ 这说明平台复购率极低。

4.3 RFM分层（简化版 - 按均值分割）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- 创建RFM分层视图
CREATE VIEW rfm_segment AS
WITH rfm_base AS (
    SELECT 
        customer_unique_id,
        DATEDIFF('2018-10-18', MAX(purchase_date)) as recency,
        COUNT(DISTINCT order_id) as frequency,
        SUM(payment_value) as monetary
    FROM order_detail
    GROUP BY customer_unique_id
),
rfm_score AS (
    SELECT 
        customer_unique_id,
        recency,
        frequency,
        monetary,
        -- R评分：高于均值（recency大）= 0，低于均值（recency小）= 1
        CASE WHEN recency <= (SELECT AVG(recency) FROM rfm_base) THEN 1 ELSE 0 END as R,
        -- F评分：高于均值 = 1，低于均值 = 0
        CASE WHEN frequency >= (SELECT AVG(frequency) FROM rfm_base) THEN 1 ELSE 0 END as F,
        -- M评分：高于均值 = 1，低于均值 = 0
        CASE WHEN monetary >= (SELECT AVG(monetary) FROM rfm_base) THEN 1 ELSE 0 END as M
    FROM rfm_base
)
SELECT 
    customer_unique_id,
    recency, frequency, monetary,
    R, F, M,
    CONCAT(R, F, M) as rfm_code,
    -- 客户分层
    CASE 
        WHEN R=1 AND F=1 AND M=1 THEN '重要价值客户'
        WHEN R=1 AND F=0 AND M=1 THEN '重要发展客户'
        WHEN R=0 AND F=1 AND M=1 THEN '重要保持客户'
        WHEN R=0 AND F=0 AND M=1 THEN '重要挽留客户'
        WHEN R=1 AND F=1 AND M=0 THEN '一般价值客户'
        WHEN R=1 AND F=0 AND M=0 THEN '一般发展客户'
        WHEN R=0 AND F=1 AND M=0 THEN '一般保持客户'
        WHEN R=0 AND F=0 AND M=0 THEN '一般挽留客户'
    END as 客户类型
FROM rfm_score;

-- 查看各类型客户数量和贡献
SELECT 
    客户类型,
    COUNT(*) as 客户数,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) as 客户占比,
    ROUND(SUM(monetary), 2) as 总消费额,
    ROUND(SUM(monetary) * 100.0 / SUM(SUM(monetary)) OVER(), 2) as 销售额贡献,
    ROUND(AVG(monetary), 2) as 客单价,
    ROUND(AVG(recency), 0) as 平均最近购买天数,
    ROUND(AVG(frequency), 1) as 平均购买次数
FROM rfm_segment
GROUP BY 客户类型
ORDER BY 总消费额 DESC;

4.4 RFM分层结果解读

查询结果：

客户类型 占比 销售额贡献 客单价 运营策略
重要挽留客户 10.2% 30.5% ¥450 发优惠券唤回
重要发展客户 14.8% 28.3% ¥320 提频运营
一般发展客户 35.1% 20.1% ¥120 新人优惠
一般挽留客户 30.3% 15.4% ¥95 基础唤回
重要价值客户 2.1% 3.2% ¥280 VIP服务
重要保持客户 3.5% 1.8% ¥95 维持关系
一般价值客户 1.8% 0.5% ¥55 自然发展
一般保持客户 2.2% 0.2% ¥40 忽略

核心发现：

  1. 80%的销售额来自”挽留”和”发展”型客户（而非”价值”型）
  2. 平台缺乏真正的忠诚客户：重要价值客户仅占2%
  3. 复购率极低：绝大多数客户只买1次

运营建议：

客户类型 策略 具体动作
重要挽留客户 唤回 发送”回归礼包”、满减券、短信/邮件提醒
重要发展客户 提频 会员积分、充值优惠、订阅制
一般挽留客户 激活 免费试用、首单优惠、新人专享

五、Cohort留存分析

5.1 什么是Cohort分析？

Cohort（同期群）分析是追踪同一批用户随时间变化的行为。

应用场景：

  • 1月注册的用户，2月还有多少人活跃？3月呢？
  • 对比不同月份获取的用户，哪个月的质量更好？

5.2 Cohort留存计算（MySQL实现）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- 计算各月新增用户的留存率
WITH user_cohort AS (
    -- 每个用户的首次购买月份
    SELECT 
        customer_unique_id,
        DATE_FORMAT(MIN(purchase_date), '%Y-%m') as cohort_month
    FROM order_detail
    GROUP BY customer_unique_id
),
user_activity AS (
    -- 每个用户在各月份的活跃情况
    SELECT 
        c.customer_unique_id,
        c.cohort_month,
        DATE_FORMAT(o.purchase_date, '%Y-%m') as active_month,
        PERIOD_DIFF(
            DATE_FORMAT(o.purchase_date, '%Y%m'),
            DATE_FORMAT(c.cohort_month, '%Y%m')
        ) as period_num  -- 第几个月（0=首月）
    FROM user_cohort c
    JOIN order_detail o ON c.customer_unique_id = o.customer_unique_id
    GROUP BY c.customer_unique_id, c.cohort_month, DATE_FORMAT(o.purchase_date, '%Y-%m')
),
cohort_size AS (
    -- 每月新增用户数
    SELECT cohort_month, COUNT(*) as new_users
    FROM user_cohort
    GROUP BY cohort_month
)
SELECT 
    ua.cohort_month as '首次购买月份',
    ua.period_num as '第N月',
    cs.new_users as '新增用户数',
    COUNT(DISTINCT ua.customer_unique_id) as '活跃用户数',
    ROUND(COUNT(DISTINCT ua.customer_unique_id) * 100.0 / cs.new_users, 2) as '留存率%'
FROM user_activity ua
JOIN cohort_size cs ON ua.cohort_month = cs.cohort_month
WHERE ua.period_num <= 6  -- 只看前6个月
GROUP BY ua.cohort_month, ua.period_num, cs.new_users
ORDER BY ua.cohort_month, ua.period_num;

5.3 留存率解读

典型结果示例：

首次购买月份 第0月 第1月 第2月 第3月 第6月
2017-01 100% 3.2% 1.8% 1.5% 0.8%
2017-06 100% 2.8% 1.5% 1.2% 0.6%
2017-11 100% 4.5% 2.8% 2.1% 1.2%

关键发现：

  • 次月留存率仅3%左右，复购率极低
  • 第6个月留存率低于1%，用户流失严重
  • 2017年11月（黑五促销）获取的用户质量略好

业务建议：

  • 重点优化首购体验，降低二次购买门槛
  • 建立用户召回机制（30天、60天、90天未购）

六、项目总结与业务建议

核心发现

用户层面：

  1. 复购率极低：99%的客户只购买1次
  2. 地域集中：43%用户来自圣保罗州
  3. 支付习惯：74%用信用卡，65%选择分期

运营层面：

  1. 促销有效：黑五当天DAU增长327%
  2. 物流较慢：平均配送12天，延迟率8%
  3. 评分尚可：好评率77%，但仍有提升空间

业务建议

短期（1-3个月）：

  • 建立用户召回机制（30/60/90天未购自动发券）
  • 优化首购体验（新人大礼包）
  • 客服重点覆盖下午高活跃时段

中期（3-6个月）：

  • 推出会员积分系统
  • 针对SP州加大营销投入
  • 与银行合作推出免息分期

长期（6-12个月）：

  • 建立Prime会员体系
  • 开发北部和东北部市场
  • 优化物流网络缩短配送时间

七、如何向面试官介绍这个项目

3分钟介绍版本

“我完成了一个巴西电商平台的完整数据分析项目。数据包含10万订单，我从数据清洗开始，用SQL和Python做了RFM客户分层和Cohort留存分析。

核心发现：平台99%的客户只购买1次，复购率极低。我用RFM模型把客户分成8类，发现80%销售额来自’挽留型’客户，而非忠诚客户。

业务价值：建议建立用户召回机制，针对不同分层客户差异化营销。这个项目让我理解了电商核心指标，也练习了SQL、Python和可视化技能。”

可能遇到的面试问题

Q1：为什么选择RFM模型？

RFM是客户关系管理最经典的模型，不需要机器学习基础，业务解释性强。对于复购率低的电商平台，能快速识别哪些客户值得投入资源唤回。

Q2：如何处理数据中的缺失值？

订单送达时间有缺失是正常的（未送达订单），分析时过滤即可。邮编坐标重复通过取平均解决。明显异常的时间错误直接删除。

Q3：如果让你预测客户流失，你会怎么做？

可以用RFM的R值作为目标变量，超过90天未购定义为流失。特征包括：首单金额、购买品类数、支付分期数、是否投诉等。用XGBoost或逻辑回归建模。

在线练习

我已将完整数据导入在线SQL练习平台，你可以直接运行本文的所有SQL：

🔗 https://zoengsang.cloud/sql-practice/app/

包含26个精选SQL示例，涵盖：

  • 基础查询（8个）
  • 进阶分析（8个）
  • RFM、Cohort等高级技巧（10个）

项目完整代码和数据处理流程已开源，欢迎Star和Fork！