巴西电商Olist订单数据分析(SQL + PowerBI)

原创已于 2025-08-18 03:38:30 修改 · 704 阅读

26 ·

CC 4.0 BY-SA版权

文章标签：

#powerbi #sql

于 2025-08-18 03:35:19 首次发布

一、项目背景

Olist电商是巴西的一个电商平台，巴西各地小型企业可通过Olist商店销售产品，并使用Olist物流合作伙伴将产品直接运送给客户。本文从用户维度对Olist商店2016.9-2018.8的订单数据进行分析，目的是发现平台存在的问题，分析原因，并给出平台运营及用户运营方面的建议。

二、数据信息

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

数据说明：数据集为巴西电商Olist的交易订单数据，涵盖该平台2016年9月-2018年8月近10万条订单交易记录，数据集包含9张数据表。本次分析只用到其中5张表：

olist_orders_dataset：包含订单id，顾客id，订单状态，购买时间等信息

olist_order_items_dataset：包含订单id，物品数量，商品id，商品价格和运费等信息

olist_products_dataset：包含商品id，商品品类等商品信息

olist_customers_dataset：包含顾客id，顾客唯一id，顾客地域等信息

product_category_name_translation：包含商品品类和商品品类的英文翻译

三、分析框架

流量指标：活跃用户数（DAU、MAU、时段）

运营指标：GMV（季度、月）、ARPU（季度、月）、订单数（天、月、时段）

RFM用户价值分层：各层次用户的热销品类

四、数据清洗

# 原数据表名过长，为方便后续处理对表重新命名
# 重命名表
RENAME TABLE olist_customers_dataset TO customers;
RENAME TABLE olist_orders_dataset TO orders;
RENAME TABLE olist_order_items_dataset TO items;
RENAME TABLE olist_products_dataset TO products;
RENAME TABLE product_category_name_translation TO category;

# 空值处理
# 从kaggle数据源提供的各表各列空值情况可知
# 而orders表的空值集中在order_approved_at列、order_delivered_carrier_date列和order_delivered_customer_date列
# 用0替换这几列空值数据
UPDATE orders SET order_approved_at = 0 WHERE order_approved_at IS NULL;
UPDATE orders SET order_delivered_carrier_date = 0 WHERE order_delivered_carrier_date IS NULL;
UPDATE orders SET order_delivered_customer_date = 0 WHERE order_delivered_customer_date IS NULL;

#查看是否有重复值
SELECT order_id FROM orders GROUP BY order_id HAVING count(*)> 1;
SELECT order_id FROM items GROUP BY	order_id,order_item_id HAVING count(*)> 1;
SELECT product_id FROM products GROUP BY product_id HAVING count(*)> 1;
SELECT product_category_name_english FROM category GROUP BY	product_category_name_english HAVING	COUNT(*)> 1;
SELECT customer_id FROM customers GROUP BY	customer_id HAVING COUNT(*)> 1;

#提取orders表里的时间，建立订单时间表，辅助后续的分析
CREATE TABLE order_time AS 
SELECT order_id,a.customer_id,customer_unique_id,
YEAR ( order_purchase_timestamp ) AS y,
QUARTER ( order_purchase_timestamp ) AS q,
MONTH ( order_purchase_timestamp ) AS m,
DATE ( order_purchase_timestamp ) AS d,
HOUR ( order_purchase_timestamp ) AS h 
FROM orders a
LEFT JOIN customers b ON a.customer_id = b.customer_id 
WHERE order_purchase_timestamp NOT LIKE '2016-09-%' 
AND order_purchase_timestamp NOT LIKE '2016-12-%' 
AND order_purchase_timestamp NOT LIKE '2018-09-%' 
AND order_purchase_timestamp NOT LIKE '2018-10-%';

#计算每笔订单的金额
CREATE TABLE total_order_value AS 
SELECT order_id,product_id,seller_id,price,freight_value,ROUND((price * count(*)+ freight_value * count(*)),2) AS order_value 
FROM items 
GROUP BY order_id, product_id,	seller_id, price, freight_value;

#整合每笔订单的时间和金额，便于后续计算GMV等指标
CREATE TABLE order_detail AS 
SELECT a.order_id,product_id,seller_id,customer_id,customer_unique_id,cast(order_value AS DECIMAL (8,2)) AS order_value,y,q,m,d,h 
FROM total_order_value AS a
INNER JOIN order_time AS b ON a.order_id = b.order_id;

五、数据分析及可视化

1.流量指标：活跃用户数（DAU、MAU、时段）及订单数分析

#一、流量指标：活跃用户数（DAU、MAU、时段）及订单数分析
#日活跃用户数及订单数
CREATE TABLE day_user_order AS 
SELECT a.日期,a.DAU,b.订单数 
FROM (SELECT d '日期',count(DISTINCT customer_unique_id)DAU FROM order_detail GROUP BY d ORDER BY d)a
LEFT JOIN (SELECT d '日期',count(DISTINCT order_id)订单数 FROM order_detail GROUP BY d ORDER BY d)b 
ON a.日期 = b.日期
ORDER BY a.日期;

#月活跃用户数及订单数
CREATE TABLE month_user_order AS 
select concat(年,'-',月) as 月份, MAU, 订单数
from (SELECT a.年,a.月,a.MAU,b.订单数 
FROM (SELECT y '年',m '月',count(DISTINCT customer_unique_id)MAU FROM order_detail GROUP BY y,m ORDER BY y,m)a
LEFT JOIN (SELECT y '年',m '月',count(DISTINCT order_id)订单数 FROM order_detail GROUP BY y,m ORDER BY y,m)b 
ON a.年 = b.年 and a.月 = b.月
ORDER BY a.年,a.月)c;
	
#时活跃用户数及订单数
CREATE TABLE hour_user_order AS 
SELECT a.h,a.各时段活跃用户数,b.订单数 
FROM (SELECT h,count(DISTINCT customer_unique_id)各时段活跃用户数 FROM order_detail GROUP BY h ORDER BY h)a
LEFT JOIN (SELECT h,count(DISTINCT order_id)订单数 FROM order_detail GROUP BY h ORDER BY h)b 
ON a.h = b.h
ORDER BY a.h;

发现：

1）DAU整体缓慢增长。 DAU的趋势是逐渐递增的，尤其2017年11月24日当天DAU增长至1166，较前一日增长420%，这是因为2017年11月24日为“黑色星期五”，该日为巴西的购物狂欢节，平台会进行打折促销活动，可以看出活动效果很好。

2）MAU由快速增长后趋于平稳。 2017年11月前，MAU及订单数呈现整体快速增长的趋势，并在17年11月下旬出现明显峰值，由此带动当月MAU和订单数达到两年以来的最高值，也说明2017年11月24日“黑色星期五”的活动效果显著，18年后月活跃用户数增长势头放缓。

3）各时段活跃用户数高峰值集中在10 am-10 pm。 10 am-10 pm的用户数和订单数明显高于平均水平，是用户活跃时段，其中12点和18点推测由于饭点时间导致活跃度略有下滑。22点-次日5点是用户的睡眠时间，活跃用户数处于低点，之后5-10点开始回升，直到10点后恢复到活跃水平。运营人员可以根据活跃用户的时间段，采取促销措施。

2.运营指标：GMV（季度、月）、ARPU（季度、月）

#二、GMV分析
#季度GMV
CREATE TABLE q_gmv AS 
SELECT y 年份,q 季度,round(sum(order_value),0) as 季度GMV 
FROM order_detail 
GROUP BY y,q 
ORDER BY y,q

#月度GMV及环比涨幅
 
CREATE TABLE month_gmv AS
SELECT CONCAT(b.年,'-',b.月)'月份',b.月度GMV,CONCAT(round((b.月度GMV/b.lag_gmv - 1),2)*100,'%')as '环比' 
FROM (SELECT a.*,LAG(月度GMV,1)over(ORDER BY 年,月)as lag_gmv 
FROM (SELECT y 年,m 月,ROUND(sum(order_value),0)as 月度GMV FROM order_detail GROUP BY y,m ORDER BY y,m)a)b;

SELECT concat(b.年,'-',b.月)'月份',b.月度GMV,concat(round((b.月度GMV/b.lag_gmv-1),2)*100,'%') AS '涨幅' 
FROM (SELECT a.*,lag(月度GMV,1) over (ORDER BY 年,月) AS lag_gmv 
FROM (SELECT y 年,m 月,round(sum(order_value),0) AS 月度GMV FROM order_detail GROUP BY y,m ORDER BY y,m) a 
) b;

发现：

1）GMV快速增长后，进入瓶颈期，并有负增长的苗头。 16-17年间，平台GMV快速增长，平均季度增幅近50%，但在进入18年后增速放缓，甚至在18年Q3出现负增长。 18年Q3的GMV出现负增长的原因一方面是缺少18年9月的数据，但同时也可以看到18年7-8月的GMV整体是不及3-5月的水平，一定程度上可以说明出现了负增长的苗头。

2）各月GMV环比增幅呈现节日性波动。 细化到各月情况来看，平台的快速发展期其实是在16年的Q4和17年的Q1，随后增速整体趋缓，仅靠个别月的小峰值维持整体增速。环比涨幅随时间变化易看出月GMV呈节日性波动。2-3月狂欢节、5月母亲节、6月巴西情人节、8月父亲节、11月黑五、12月圣诞节等，节日期间或前夕销量会上升，由于消费者选择集中在节日消费，故与节日相关的前一个月与后一个月的销售额均有下降。

#(每用户平均收入,Average Revenue Per User)
#各季度ARPU值(季度GMV/季度活跃用户数）
CREATE TABLE q_arpu AS
SELECT y 年,q 季度,ROUND(SUM(order_value)/COUNT(DISTINCT customer_unique_id),0)as '季度ARPU' 
FROM order_detail GROUP BY y,q ORDER BY y,q

#各月ARPU值及环比涨幅
CREATE TABLE month_arpu AS
SELECT CONCAT(b.年,'-',b.月)AS '月份',b.月度ARPU,CONCAT(ROUND(b.月度ARPU/lag_arpu - 1,2)*100,'%')as '环比'
FROM(SELECT a.*,LAG(月度ARPU,1)over(ORDER BY a.年,a.月)as lag_arpu 
FROM(SELECT y 年, m 月,ROUND(SUM(order_value)/COUNT(DISTINCT customer_id),0)as'月度ARPU'
FROM order_detail GROUP BY y,m ORDER BY y,m)a)b;

发现：

1）平台ARPU长期未实现突破。 平台的季度ARPU在16年Q4处于最高水平，之后在相对较长的一段时间里比较平稳，但平台需要注意到：ARPU长期没有有效突破，且2018年开始出现下降趋势。

2）细化到各月的情况来看，16年Q4ARPU达到最高值，随后的ARPU均未有突破。18年Q3的各月ARPU下滑原因跟GMV类似，跟数据缺失有一定关系，但ARPU值无法突破的问题值得平台重视。

3.RFM用户价值分层：各层次用户的热销品类

#构造R值
create view recency as 
select customer_unique_id, case 
when datediff((select max(d) from order_detail),max(d))> (select avg(datediff((select max(d) from order_detail),d)) from order_detail) then 1
else 0 
end as r
from order_detail 
group by customer_unique_id;

#构造F值
CREATE VIEW frequency AS
select customer_unique_id, case 
when num> (SELECT avg(num)from (SELECT customer_unique_id,count(DISTINCT order_id)num from order_detail GROUP BY customer_unique_id)a) then 1
else 0 
end as f
from (SELECT customer_unique_id,count(DISTINCT order_id)num from order_detail GROUP BY customer_unique_id)a;

#构造M值
create view monetary as 
select customer_unique_id, case 
when money> (SELECT avg(money)from (SELECT customer_unique_id,sum(order_value)money from order_detail GROUP BY customer_unique_id)a) then 1
else 0 
end as m
from (SELECT customer_unique_id,sum(order_value)money from order_detail GROUP BY customer_unique_id)a;

#构造rfm用户分层
CREATE view rfm AS 
SELECT r.customer_unique_id, CASE
WHEN concat( r, f, m ) = '111' THEN '重要价值客户' 
WHEN concat( r, f, m ) = '011' THEN '重要保持客户' 
WHEN concat( r, f, m ) = '101' THEN '重要发展客户' 
WHEN concat( r, f, m ) = '001' THEN '重要挽留客户' 
WHEN concat( r, f, m ) = '110' THEN '一般价值客户' 
WHEN concat( r, f, m ) = '010' THEN '一般保持客户' 
WHEN concat( r, f, m ) = '100' THEN	'一般发展客户' 
WHEN concat( r, f, m ) = '000' THEN '一般挽留客户' 
END AS label 
from recency r left join frequency f on r.customer_unique_id = f.customer_unique_id 
left join monetary m on r.customer_unique_id = m.customer_unique_id;

#计算RFM各用户分层对应的客户数  
CREATE table rfm_data AS
SELECT label,count(*)as num from rfm GROUP BY label

#各用户分层的热门商品类型及消费金额汇总表
create view hot_sales as
select a.customer_unique_id, b.label, d.product_category_name_english, sum(a.order_value) value_sum
from order_detail a
left join rfm b
using (customer_unique_id) 
left join products c
using (product_id) 
left join category d
using (product_category_name)
group by a.customer_unique_id, b.label, d.product_category_name_english;

#各用户分层消费金额占比情况
create table user_consumption as 
SELECT label,round(sum(value_sum),0) as amount,concat(ROUND((SUM(value_sum)/(SELECT SUM(value_sum) from hot_sales)*100),2),'%')as 消费金额占比
FROM	hot_sales GROUP BY label

#整体用户的热门商品类型排行
create table total_hot_sales as
SELECT product_category_name_english 商品品类,round(sum(value_sum),0) 消费金额,ROW_NUMBER()over(ORDER BY round(sum(value_sum),0) desc)as rk
FROM	hot_sales group by product_category_name_english;

#将商品类型为空的商品用others代替类型名称
UPDATE total_hot_sales SET 商品品类 = 'others' WHERE 商品品类 IS NULL; 
select * from total_hot_sales;

#一般发展客户、一般挽留客户、重要挽留客户、重要发展客户、重要价值客户的热门商品品类
create table 一般发展客户 as
SELECT product_category_name_english 商品品类,round(sum(value_sum),0) 消费金额,ROW_NUMBER()over(ORDER BY ROUND(SUM(value_sum),0)desc)as rk
from hot_sales
where label = '一般发展客户'
GROUP BY product_category_name_english;

create table 一般挽留客户 as
select product_category_name_english 商品品类, round(sum(value_sum),0) 消费金额, 
row_number()over(order by round(sum(value_sum),0) desc) as rk
from hot_sales 
where label = '一般挽留客户'
group by product_category_name_english;

create table 重要挽留客户 as
select product_category_name_english 商品品类, round(sum(value_sum),0) 消费金额, 
row_number()over(order by round(sum(value_sum),0) desc) as rk
from hot_sales 
where label = '重要挽留客户'
group by product_category_name_english;

create table 重要发展客户 as
select product_category_name_english 商品品类, round(sum(value_sum),0) 消费金额, 
row_number()over(order by round(sum(value_sum),0) desc) as rk
from hot_sales 
where label = '重要发展客户'
group by product_category_name_english;

create table 重要价值客户 as
select product_category_name_english 商品品类, round(sum(value_sum),0) 消费金额, 
row_number()over(order by round(sum(value_sum),0) desc) as rk
from hot_sales 
where label = '重要价值客户'
group by product_category_name_english;

发现：

1）平台30%的用户贡献60%的销售额，符合电商行业的二八原则。 从用户数量来看：重要发展用户、重要挽留用户、重要价值用户的比例不到30%，明显低于一般发展用户和一般挽留用户的70%比例；从消费金额来看：重要发展用户和重要挽留用户的消费金额占全体用户的58.85%，一般发展用户和一般挽留用户的消费金额占全体用户的35.45%。这说明：

①重要型用户仍是运营维护的主要关注群体；

②平台用户大多数为新用户，普遍消费频率低且最近消费时间较远，客户流失情况严重。平台应加强客户价值管理，特别是要努力提高重要价值客户占比。对于重要发展用户和重要挽留用户，应主动联系，给予优惠，提高复购率，并分析用户流失原因，通过针对性的运营手段唤回；对于一般发展用户和一般挽留用户，应增加免费试用提升用户兴趣，提高新用户留存率，同时分析用户流失原因，有针对性地唤回。

2）大众热门品类集中在健康美容、家居用品、运动休闲。 主要用户的热门商品品类中，健康美容、家居用品、运动休闲等品类均出现在热门排名前列，说明这类商品是大众热门品类，适合对各类用户做推广和促销；手表、家具、电脑配件则属于小众热门商品，对特定类型用户来说更受欢迎，可选择合适的用户群体进行推荐。

六、项目结论与运营建议

1.平台运营层面

增长瓶颈：从订单数、MAU 和 GMV 等核心指标来看，平台整体增速放缓，用户平均收入水平维持不变甚至出现回落，显示出发展进入瓶颈期。
用户规模：需明确国内市场是否已触顶进入存量竞争阶段。若是，应积极开拓海外市场，同时强化用户运营、降低流失率；若否，则需审视平台内部运营与外部竞争，优化运营策略、提升用户体验，并保持对竞争对手动向的敏锐跟进，扩大站外流量。
交易额提升：
- 抓住用户高活跃时段（10:00–22:00），重点进行运营推广以提升转化率；
- 通过会员运营、差异化服务提升复购率与活跃度，进而提高用户平均收入；
- 继续深耕“黑色星期五”等节日营销，创新活动玩法，放大销售峰值效应。

2.用户运营层面

差异化营销（RFM 模型指导）：
- 重要发展/挽留用户：主动触达、复购优惠、会员积分或充值制度，降低复购门槛、增强消费粘性；
- 一般发展/挽留用户：提供免费试用、提升初次消费兴趣；通过满意度调查定位流失原因并进行针对性唤回；
- 重要价值/保持用户：提供个性化推荐和专属优惠方案，进一步提升长期价值；
- 一般价值/保持用户：通过关联销售提升客单价。
品类运营：
- 大众热门品类（健康美容、家居用品、运动休闲）：结合节假日进行重点推广与促销，提升曝光与转化；
- 小众热门品类（手表、家具、电脑配件）：聚焦精准人群，进行定向推荐以提高转化效率。