巴西电商Olist订单数据分析(MySQL+Tableau)

本文主要是介绍巴西电商Olist订单数据分析(MySQL+Tableau)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

一、项目背景

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

二、数据信息

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

数据说明：数据集为巴西电商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用户价值分层：各层次用户的热销品类

四、结论先行

结论总结：

一、平台运营建议：综合订单数、MAU和GMV指标来看，平台遇到发展瓶颈，典型表现为用户平均收入水平维持，指标增速放缓，甚至出现回落的趋势。

针对用户规模，需评估国内市场流量是否见顶进入存量竞争阶段。如果是，可以考虑发展海外市场，同时做好用户运营，减少用户流失；如果否，则可能与平台自身运营不佳有关或竞争对手抢占市场导致，一方面需要对内调整运营策略，优化用户体验，减少流失，对外及时跟进竞争对手动态，持续拓展站外流量。
针对成交额，在保证用户规模健康增长的同时，一方面有效利用10-22点的用户活跃时段进行运营，提高各环节的转化率，另一方面做好用户运营，培养优质用户，提升用户复购率及活跃度，提高用户平均收入。
继续利用“黑色星期五”的节日优势，创新销售策略和活动玩法。

二、用户运营建议：结合用户特点、商品喜好、巴西节假日采取精细化运营措施。

用户普遍消费频率低且最近消费时间较远，需要对于RFM模型中不同类别用户进行差异化精准营销：对于重要发展用户和重要挽留用户，应主动联系，给予复购优惠，降低二次消费门槛，提高复购率，同时采取会员积分制度或会员卡充值制度，引导用户长期消费，增强粘性；对于一般发展用户和一般挽留用户，应增加免费试用提升用户兴趣，提高新用户留存率，同时开展用户满意度调查，分析用户流失原因，有针对性地唤回；对于重要价值用户和重要保持用户，应提供个性化服务，根据用户的需求和兴趣给予个性化的产品推荐和优惠方案；对于一般价值用户和一般保持用户，应优化关联销售，提高客单价。
健康美容、家居用品、运动休闲是大众热门品类，适合结合特定节假日对各类用户做推广和促销，吸引用户关注，增加曝光量；手表、家具、电脑配件则属于小众热门品类，向特定的用户群体进行推荐效果更好。

数据可视化：

一、流量指标：活跃用户数（DAU、MAU、时段）、订单数（天、月、时段）

（因几乎每位用户对应1个订单，所以这里订单数放在流量指标处一起分析）

发现：

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点后恢复到活跃水平。运营人员可以根据活跃用户的时间段，采取促销措施。

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

发现：

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月圣诞节等，节日期间或前夕销量会上升，由于消费者选择集中在节日消费，故与节日相关的前一个月与后一个月的销售额均有下降。

发现：

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

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

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

发现：

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

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

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

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

五、数据分析具体过程（代码部分）

/*
分析框架：
1.流量指标：活跃用户数（DAU、MAU、时段）
2.运营指标：GMV（季度、月）、ARPU（季度、月）、订单数（天、月、时段）
3.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;#将空值替换为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;
#结果显示该5份数据表均没有重复值#提取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-%';
#2016年9、12月，2018年9、10月数据量过少，需要过滤掉这些数据量异常的月份#计算每笔订单的金额
CREATE TABLE total_order_value AS 
SELECT order_id,product_id,seller_id,price,freight_value,(price * count(*)+ freight_value * count(*)) 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;#数据分析
#一、流量指标：活跃用户数（DAU、MAU、时段）及订单数分析
#日活跃用户数及订单数
CREATE TABLE day_user_order AS 
SELECT a.日期,DAU,订单数 
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.y '年',a.m '月',MAU,订单数 
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.y = b.y AND a.m = b.m 
ORDER BY 年,月) c;#时活跃用户数及订单数
CREATE TABLE hour_user_order AS 
SELECT a.h, 各时段活跃用户数, 订单数 
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 h;#二、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;#三、ARPU分析
#(每用户平均收入,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.月)'月份',b.月度ARPU,concat(round((b.月度arpu/b.lag_arpu-1),2)*100,'%') AS '涨幅' 
FROM (SELECT a.*,lag(月度ARPU, 1 ) over (ORDER BY 年,月) 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;#四、RFM模型用户分层
#构造R值
create view recency as 
select customer_unique_id, case 
when datediff(max(d), (select max(d) from order_detail))> (select avg(datediff(d,(select max(d) from order_detail))) 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(label) 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 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;
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;

（PS：如有不足，请指正，感谢）

这篇关于巴西电商Olist订单数据分析(MySQL+Tableau)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！