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

2023-10-13 22:20
文章标签 mysql 订单 数据分析 电商 database 巴西 tableau olist

本文主要是介绍巴西电商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)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/206225。

相关文章

MySQL更新某个字段拼接固定字符串的实现

MySQL更新某个字段拼接固定字符串的实现

《MySQL更新某个字段拼接固定字符串的实现》在MySQL中,我们经常需要对数据库中的某个字段进行更新操作,本文就来介绍一下MySQL更新某个字段拼接固定字符串的实现,感兴趣的可以了解一下... 目录1. 查看字段当前值2. 更新字段拼接固定字符串3. 验证更新结果mysql更新某个字段拼接固定字符串 -
阅读更多...
python连接本地SQL server详细图文教程

python连接本地SQL server详细图文教程

《python连接本地SQLserver详细图文教程》在数据分析领域,经常需要从数据库中获取数据进行分析和处理,下面：本文主要介绍python连接本地SQLserver的相关资料,文中通过代码... 目录一.设置本地账号1.新建用户2.开启双重验证3,开启TCP/IP本地服务二js.python连接实例1.
阅读更多...
Spring Boot项目中结合MyBatis实现MySQL的自动主从切换功能

Spring Boot项目中结合MyBatis实现MySQL的自动主从切换功能

《SpringBoot项目中结合MyBatis实现MySQL的自动主从切换功能》：本文主要介绍SpringBoot项目中结合MyBatis实现MySQL的自动主从切换功能,本文分步骤给大家介绍的... 目录原理解析1. mysql主从复制（Master-Slave Replication）2. 读写分离3.
阅读更多...
Ubuntu中远程连接Mysql数据库的详细图文教程

Ubuntu中远程连接Mysql数据库的详细图文教程

《Ubuntu中远程连接Mysql数据库的详细图文教程》Ubuntu是一个以桌面应用为主的Linux发行版操作系统,这篇文章主要为大家详细介绍了Ubuntu中远程连接Mysql数据库的详细图文教程,有... 目录1、版本2、检查有没有mysql2.1 查询是否安装了Mysql包2.2 查看Mysql版本2.
阅读更多...
基于SpringBoot+Mybatis实现Mysql分表

基于SpringBoot+Mybatis实现Mysql分表

《基于SpringBoot+Mybatis实现Mysql分表》这篇文章主要为大家详细介绍了基于SpringBoot+Mybatis实现Mysql分表的相关知识,文中的示例代码讲解详细,感兴趣的小伙伴可... 目录基本思路定义注解创建ThreadLocal创建拦截器业务处理基本思路1.根据创建时间字段按年进
阅读更多...
Python3.6连接MySQL的详细步骤

Python3.6连接MySQL的详细步骤

《Python3.6连接MySQL的详细步骤》在现代Web开发和数据处理中,Python与数据库的交互是必不可少的一部分,MySQL作为最流行的开源关系型数据库管理系统之一,与Python的结合可以实... 目录环境准备安装python 3.6安装mysql安装pymysql库连接到MySQL建立连接执行S
阅读更多...
MySQL双主搭建+keepalived高可用的实现

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制，确认复制是否成功4.同
阅读更多...
MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL？1.2 动态SQL的优点1.3 动态S
阅读更多...
Mysql表的简单操作(基本技能)

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例：修改表在数据库中，表的操作主要
阅读更多...
mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言：第一步：第二步：第三步：总结：前言：当你想通过命令窗口想打开mysql时候发现提http://www.cpp
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2