使用SQL+Excel对南美洲Olist电商的商业分析案例

数据源：

Kaggle上Ecommerce_orders的Olist 2016-2018年订单数据。

数据字段很丰富，但是我用到的仅有如下几个

1、seller_id：商家ID
2、order_id：订单ID
3、order_status：订单状态
4、order_purchase_timestamp：下单时间
5、customer_unique_id：用户ID
6、customer_zip_code_prefix：客户邮政编码前5位
7、customer_city：客户所在城市
8、customer_state：客户所在的州
9、payment_type：付款方式
10、payment_value：交易金额
11、freight_value：运费，物品运费价值物品（如果订单包含多个物品，则运费价值将在物品之间
12、product_category_name：类别名称

按照工作中的商业分析方法论对电商平台的用户、收入进行分布分析。流程如下：

一、平台整体发展趋势介绍

二、用户维度分析

        日活、月活分析

        3日、7日留存率分析

        用户消费偏好分析(包括时间、支付方式、地区分布)

        RFM用户价值分析

        总金额前十用户分析

三、商户维度分析

        商户交易额分层

四、产品维度分析

        平台各SKU销量分布

        各年度销量前十产品分析

五、GMV同比、环比和ARPU值

       GMV同比和环比变化趋势

        ARPU值变动趋势

仅个人练习用途，如果分析有误，欢迎指正！

一、整体发展趋势

select year(order_purchase_timestamp) year,
round(sum(payment_value-freight_value),3) as GMV,
count(distinct customer_unique_id) as 用户总数,
count(distinct order_id) as 订单总数,
round(sum(payment_value-freight_value)/count(distinct customer_unique_id),3) as 平均客单价,
count(distinct seller_id) as 商家总数
from Brasil_Eshop_Orders
group by year

年度	GMV (美元）	下单用户数	订单数	商家数
2016	40,757.30	264	267	128
2017	5,951,391.96	42,136	43,428	1,683
2018	7,234,826.70	51,612	52,783	2,317

从2016年8月以来，平台GMV、用户数、订单数和入驻商家数稳步增长，其中2018年仅1-8月三个季度，GMV和用户数量已超越2017年全年。平台整体发展趋势较稳定。

二、用户维度分析

2.1日活、月活分析

日活

select DATE_FORMAT(order_purchase_timestamp,'%Y-%m-%d') day, 
COUNT(distinct customer_unique_id) DAU, 
round(sum(payment_value-freight_value),3) GMV_daily 
from Brasil_Eshop_Orders
group by day

2016-2018年内，平台每日活跃用户数整体波动上涨，日均活跃用户156人次，2017年11月24日达到最高值，单日活跃1,132人次。

月活

select DATE_FORMAT(order_purchase_timestamp,'%Y-%m') month, 
COUNT(distinct customer_unique_id) MAU, 
round(sum(payment_value-freight_value),3) GMV_monthly 
from Brasil_Eshop_Orders
group by month

2016年9月-2018年1月之间，平台各月活跃用户数上涨明显，2018年1月-7月平台月均活跃用户数围绕6,500人次左右波动。2018年1月达到最高，为6,974人次。

对时间创建视图方便后续调用

create view order_time as 
select order_id,
customer_unique_id,
year(order_purchase_timestamp) as y,
month(order_purchase_timestamp) as m,
date(order_purchase_timestamp) as d,
hour(order_purchase_timestamp) as h
from Brasil_Eshop_Orders
order by y,m,d,h asc

2.2留存率

留存率应计算自用户注册后，第n日的登录活跃情况。

（在此数据集中，由于未提供用户注册表，因此将用户第一次消费时间作为注册时间，按第n日消费情况统计留存率）

先将用户第一次消费记录做视图临时表

create view register_time as
select distinct customer_unique_id, 
FIRST_VALUE(order_purchase_timestamp)over(partition by customer_unique_id order by order_purchase_timestamp asc) as '注册时间'
from Brasil_Eshop_Orders
order by '注册时间'

统计一下用户平均下单情况（这么做的原因是，我用了好几个小时排查自己为啥复购率怎么算都是0，最后发现平台用户平均消费频率低得惊人，复购率计算没有意义）

select year(order_purchase_timestamp), 
count(order_id)/count(distinct customer_unique_id) as '平均下单数'
from Brasil_Eshop_Orders
group by 1
order by 2 desc

年度	用户平均订单数
2016	1.01
2017	1.03
2018	1.02

平台各年用户平均订单数波动很小，每年人均1.02单，因此不进行留存率或复购率分析。

7日留存率

select date(order_purchase_timestamp) as '日期',
count(distinct c.customer_unique_id) as '新增用户数',
#如果是3日，30日留存改数字即可
count(distinct case when t.time_diff=7 then t.user_id else null end)/count(distinct c.customer_unique_id) as '三日留存'
from (
select a.order_purchase_timestamp,
a.customer_unique_id as user_id, 
TIMESTAMPDIFF(day,b.注册时间,a.order_purchase_timestamp) as time_diff
from Brasil_Eshop_Orders a left join register_time b
on a.customer_unique_id=b.customer_unique_id
) as t left join register_time c
on t.user_id=c.customer_unique_id
group by 1

2.3 用户消费偏好

消费24小时分布

select hour(order_purchase_timestamp) hour, 
COUNT(distinct customer_unique_id) as 下单用户, 
round(sum(payment_value-freight_value),3) GMV
from Brasil_Eshop_Orders
group by hour

 用户每日下单时间从5点-8点呈现较快上升，从8点-22点波动较小，从22点-次日5点下单用户数逐渐减少，趋势较符合日常作息规律。

用户地区分布

select customer_state,
customer_city,
count(distinct customer_unique_id)
from Brasil_Eshop_Orders
group by 1,2

 从用户在各州的分布看，41.92%的用户来自SP州，约3.92万人，除去人口较密集的SP、RJ、MG地区，其他地区用户占比约33.54%，用户的各地区知名度较高。

支付渠道分析

select 
payment_type,
count(order_id),
count(order_id)/(select count(*) from Brasil_Eshop_Orders) as '占比'
from Brasil_Eshop_Orders
group by 1

从支付渠道看，平台用户主要通过信用卡、boleto进行支付，占所有支付渠道比例约75.80%和19.89%。

分期付款比例

select payment_installments,
count(*),
count(*)/(select count(*) from Brasil_Eshop_Orders)
from Brasil_Eshop_Orders
group by 1

 进一步从信用卡消费用户中查看分期支付的订单数量，发现不分期的订单数为4.72万个，约占信用卡支付订单数的48.92%，分4期及以内的订单数为7.60万个，占比约78.77%。发现平台用户存在分期习惯，但是大部分订单分期数不高。

2.4构建RFM用户价值模型

构建RFM模型可以分为四步，第一步统计每个用户的recency、frequency、monetary数值，第二步按一定打分规则 (例如百分位) 从1-5打分，第三步对打分结果进行0,1标记，合并后生成最终RFM_score，完成建模。

score和用户分层对应表

图片来自知乎：基于RFM模型的用户价值分析——Python&Tableau - 知乎

#第一步构建R,F,M views
create view RFM as
select customer_unique_id,
timestampdiff(day,max(order_purchase_timestamp),(select max(order_purchase_timestamp) as closest_day
from Brasil_Eshop_Orders)) as r,
count(distinct order_id) as f,
sum(payment_value-freight_value) as m
from Brasil_Eshop_Orders
group by 1

#第二步：使用ntile函数直接按几个percentile进行分层，达到打分目的
#由于f只有个别用户超过3次，ntile无法分出5层，所以手工判定
create view RFM_rnk as
select *,
NTILE(5)over(order by r desc) as 'r_score',
case 
when f>=15 then '5'
when f>=8 then '4'
when f>=5 then '3'
when f>=2 then '2'
when f>=1 then '1'
end as 'f_score',
NTILE(5)over(order by m asc) as 'm_score'
from RFM

#第三步：根据打分为用户做0,1标记
create view RFM_rnk_tagged as
select *,
if (r_score>(select avg(r_score) from RFM_rnk),'1','0') as 'r_tag',
if (f_score>(select avg(f_score) from RFM_rnk),'1','0') as 'f_tag',
if (m_score>(select avg(m_score) from RFM_rnk),'1','0') as 'm_tag'
from RFM_rnk
group by customer_unique_id

#第四步：把tag合并为用户分层
select *,
case score
when '111' then '重要价值客户'
when '110' then '一般价值客户'
when '101' then '重要发展客户'
when '100' then '一般发展客户'
when '011' then '重要保持客户'
when '010' then '一般保持客户'
when '001' then '重要挽留客户'
when '000' then '流失客户'
end as '用户价值分层'
from(
select *,
concat(r_tag,f_tag,m_tag) as 'score'
from RFM_rnk_tagged
) a

 统计各层用户数量和贡献的消费金额

select 
用户价值分层,
count(distinct customer_unique_id) as '用户数',
sum(m) as '消费金额'
from RFM_rnk_tagged_cat
group by 1

用户价值分层	用户数	用户占比	消费金额（美元）	金额占比	人均消费
重要发展客户	32,463	34.77%	6,611,876.74	49.99%	203.67
重要挽留客户	21,050	22.55%	4,475,864.18	33.84%	212.63
一般发展客户	21,745	23.29%	816,645.60	6.17%	37.56
流失客户	15,299	16.39%	586,403.11	4.43%	38.33
重要价值客户	1,634	1.75%	479,121.01	3.62%	293.22
重要保持客户	867	0.93%	243,696.35	1.84%	281.08
一般价值客户	172	0.18%	7,954.83	0.06%	46.25
一般保持客户	128	0.14%	5,414.14	0.04%	42.30

通过用户价值分层，发现平台中近期有消费、消费总金额较高但频率较低的重要发展客户约占总用户数34.77%，贡献了价值分层中占比最高的消费金额，约占平台总消费金额的49.99%。近期未消费、消费总金额较高但频率较低的重要挽留客户贡献了第二高消费金额，约占平台总消费金额的33.84%，该部分用户数约占总用户数22.55%。

因此，重要发展客户和重要挽留客户共为平台贡献83.83%的营业收入。营销策略应对其做出相应倾斜，通过向频次深耕客户优先不定期发放优惠券、活动预告和节日提醒，以获取更高此类用户点击量，提高消费频率。向重要挽留客户和重要保持客户发放有奖问卷调查，并向答复群体及时提供反馈和跟进整改结果，提高该类用户对平台满意度。同时提供更多平台活动介绍和产品推荐，以达到挽留目的。

平台中用户占比约23.29%的近期有消费、频率和金额都较低的一般发展客户共贡献6.17%的消费金额，说明新用户人均消费较低，应对其推荐更丰富的优质品类，提高其客单价。

平台流失客户占用户总数约16.39%，数量较大，需要客服人员触达了解流失原因，并总结优化平台的用户体验。

平台近期有消费、频率和金额都很高的重要价值客户占比较小，约占用户总数1.75%，并贡献了3.62%的消费金额，应通过节日问候、提供更多推荐奖励、专属客服及时处理问题等方式进行维护，促进其帮助拉新。

对于一般价值客户和一般保持客户，可提供更多新产品推荐，刺激消费动力。

三、商户维度分析

3.1 商户交易额分层

select
case 
when seller_sale>=150000 then '[150000,∞)'
when seller_sale>=50000 then '[50000,150000)'
when seller_sale>=10000 then '[10000,50000)'
when seller_sale>=5000 then '[5000,10000)'
when seller_sale>=0 then '[0,5000)'
end as '商户金额分层',
sum(a.seller_sale),
count(distinct a.seller_id) as '商户数'
from(
select
seller_id,
sum(payment_value-freight_value) as seller_sale,
#使用sum()over进行累计求和再确定分层区间，方便确认区间边界
sum(sum(payment_value-freight_value)/(select sum(payment_value-freight_value) from Brasil_Eshop_Orders))over(order by sum(payment_value-freight_value) desc) as '金额占比'
from Brasil_Eshop_Orders
group by 1
order by seller_sale desc
) a
group by 1
order by 1

金额分层	总金额（美元）	金额占比	商户数	商户占比
[0,5000)	2,512,382.48	18.99%	2,391	80.94%
[5000,10000)	1,990,035.48	15.05%	279	9.44%
[10000,50000)	4,931,679.42	37.28%	247	8.36%
[50000,100000)	1,247,344.49	9.43%	20	0.68%
[10000,∞)	2,545,585.64	19.25%	17	0.58%

平台共计商户2,959家，其中5家商户为测试商户，金额为负数，未计入表中。其中约19.06%的商户，销售了占消费总金额约81.01%的商品。其余80.94%的商户的销售额约占总金额的18.99%。平台中，小微商户占比较高。

四、产品分析

4.1 产品整体分析

select 
product_category_name,
sum(payment_value-freight_value) as '总金额',
round(sum((payment_value-freight_value)/price),2) as '消费数量',
count(distinct customer_unique_id) as '消费人数'
from Brasil_Eshop_Orders
GROUP BY 1

 4.2 产品集中度

select 
year(order_purchase_timestamp) as year, 
product_category_name,
sum(payment_value-freight_value) as '总金额',
round(sum((payment_value-freight_value)/price),2) as '消费数量',
count(distinct customer_unique_id) as '消费人数'
from Brasil_Eshop_Orders
GROUP BY 1,2
order by 1 asc,3 desc

2016-2018年之间，平台共计销售74个产品类目，其中销售金额前十名产品如下：

商品品类	消费金额	销售总数量	消费人数
beleza_saude	1,228,722.81	9,690.74	8,465
relogios_presentes	1,148,443.02	5,885.57	5,401
cama_mesa_banho	1,032,543.71	11,423.85	8,919
esporte_lazer	956,481.55	8,748.56	7,307
informatica_acessorios	898,125.53	7,846.72	6,383
moveis_decoracao	736,574.18	8,995.44	6,084
utilidades_domesticas	620,647.16	7,384.49	5,628
cool_stuff	600,194.19	3,704.91	3,501
automotivo	574,100.13	4,230.23	3,749
ferramentas_jardim	479,506.91	4,553.21	3,387

销售数量前十名产品如下：

商品品类	消费金额	销售总数量	消费人数
cama_mesa_banho	1,032,543.71	11,423.85	8,919
beleza_saude	1,228,722.81	9,690.74	8,465
moveis_decoracao	736,574.18	8,995.44	6,084
esporte_lazer	956,481.55	8,748.56	7,307
informatica_acessorios	898,125.53	7,846.72	6,383
utilidades_domesticas	620,647.16	7,384.49	5,628
relogios_presentes	1,148,443.02	5,885.57	5,401
ferramentas_jardim	479,506.91	4,553.21	3,387
telefonia	308,453.13	4,541.77	4,033
automotivo	574,100.13	4,230.23	3,749

销售金额前十名产品为平台贡献了62.56%的金额，销售数量前十名产品为平台贡献了93.96%的数量。

通过对比发现，beleza_saude, cama_mesa_banho, relogios_presentes, exporte_lazer, informatica_acessorios, moveis_decoracao, utilidades_dometicas, automotivo, ferramentas_jardim这9类商品种类销售金额和销售数量均处于平台前十名。为热销产品类目。cool_stuff和telefonia，未同时出现在前十金额和数量排名中，但是销售金额和数量均排名前15。用户在平台消费上述11类产品的需求较高，可以增加推广同类型商品的高端产品，提高整体销售金额。

4.3 产品单价分析

select 
case 
when price>=500 then '[500,∞)' 
when price>=200 then '[200,500)'
when price>=100 then '[100,200)'
when price>=50 then '[50,100)'
when price>=0 then '[0,50)'
end as '单价分层',
product_category_name,
sum(payment_value-freight_value) as '总金额',
round(sum((payment_value-freight_value)/price),2) as '消费数量',
count(distinct customer_unique_id) as '消费人数'
from Brasil_Eshop_Orders
GROUP BY 1,2

单价分层	销售金额（美元）	金额占比	销售数量	数量占比	消费人数
[0,50)	1,290,690.53	9.76%	42,290.35	36.90%	31,559
[50,100)	2,468,985.99	18.67%	33,076.69	28.86%	28,091
[100,200)	3,783,631.30	28.61%	26,456.00	23.09%	23,672
[200,500)	2,882,324.38	21.79%	9,740.85	8.50%	9,225
[500,∞)	2,801,343.76	21.18%	3,034.14	2.65%	2,974

通过对单价进行分层分析，发现已销售商品中88.85%的商品单价低于200美元，仅11.15%的商品高于200美元，共计贡献42.97%的平台销售金额。平台高单价商品购买用户较集中，约占总用户的3.11%，可以通过推广热销产品类目的高端产品，以提高整体销售金额。

五、GMV增长率和ARPU值

5.1 GMV增长率

同比、环比增长率

select
time,
monthly_gmv as '本期gmv',
lag(monthly_gmv,12,0)over(order by time) as '去年同期gmv',
(monthly_gmv-lag(monthly_gmv,12,0)over(order by time))/lag(monthly_gmv,12,0)over(order by time)'同比',
lag(monthly_gmv,1,0)over(order by time)as '上月gmv',
(monthly_gmv-lag(monthly_gmv,1,0)over(order by time))/lag(monthly_gmv,1,0)over(order by time)'环比'
from(
select 
date_format(order_purchase_timestamp,'%Y-%m') as 'time',
sum(payment_value-freight_value) as 'monthly_gmv'
from Brasil_Eshop_Orders
group by 1
) a

  平台2018年GMV在1月、3月、4月、5月相对较高，其他月份较低，各月较2017年同比增长幅度较大，但呈现增速下降趋势。说明平台业务增长点减少，发展遇到瓶颈期，可能存在业务缩减的风险。

 2018年各月中同比出现负增长，1月、3月、4月、5月、7月同比增长为正，其余为负增长。说明2018年平台业务变动较大，影响用户对平台的满意度。需要找到新的业务增长点，促进用户在平台的消费行为。

5.2 ARPU值

select 
date_format(order_purchase_timestamp,'%Y-%m') as 'time',
sum(payment_value-freight_value)/count(distinct customer_unique_id) as 'arpu'
from Brasil_Eshop_Orders
group by 1

 平台单个用户平均消费金额波动较小，在140美元上限徘徊。结合RFM用户价值模型和产品单价分析，由于人均消费较高的重要价值客户与重要保持客户合计占比约2.68%，同时平台热销产品平均单价约121美元，因此平台ARPU值也相对较低。应通过精准营销手段，提高平台重要价值客户与重要保持客户数量，从而带动人均消费金额的升高。