一、分析背景

巴西Olist是当地电商平台，本数据集包含2016年9月-2018年8月的数据，本文将通过对平台的整体情况、用户、商家、产品、销售五个方面分析平台的经营状况，目的是为了发现平台可能存在的问题，分析可能的原因并给出建议。

数据链接：https://www.kaggle.com/jainaashish/orders-merged

数据字段说明：

1、product_id：商品ID
2、seller_id：商家ID
3、order_id：订单ID
4、customer_id：订单对应的用户ID。订单数据集的键，每个订单都有一个唯一的customer_id。
5、order_status：订单状态
6、order_purchase_timestamp：下单时间
7、order_approved_at：付款审批时间
8、order_delivered_carrier_date：订单过账日期
9、order_delivered_customer_date：客户实际订单交货日期
10、order_estimated_delivery_date：订单预计交货日期
11、customer_unique_id：用户ID
12、customer_zip_code_prefix：客户邮政编码前5位
13、customer_city：客户所在城市
14、customer_state：客户所在的州
15、review_id：评论ID
16、review_score：评价得分，客户在满意度调查中给出的注释范围为1到5。
17、review_comment_title：评论标题（葡萄牙语）
18、review_comment_message：评论内容（葡萄牙语）
19、review_creation_date：发出满意度调查日期
20、review_answer_timestamp：客户满意度回复日期
21、payment_sequential：付款顺序，客户可以使用多种付款方式付款。
22、payment_type：付款方式
23、payment_installments：客户选择的分期付款数量
24、payment_value：交易金额
25、order_item_id：序号，用于标识同一订单中包含的商品数量。
26、price：商品价格
27、freight_value：运费，物品运费价值物品（如果订单包含多个物品，则运费价值将在物品之间分配）
28、seller_zip_code_prefix：卖家邮政编码前5位
29、seller_city：卖家所在城市
30、seller_state：卖家所在州
31、product_category_name：类别名称
32、product_name_lenght：产品名称长度
33、product_description_lenght：产品说明长度
34、product_photos_qty：产品照片数量
35、product_weight_g：产品重量单位g
36、product_length_cm：产品长度单位cm
37、product_height_cm：产品高度单位cm
38、product_width_cm：产品宽度单位cm

二、分析框架

通过整体情况、用户、商家、产品、销售五个方面分析，使用工具为SQL和可视化工具FineBI。

三、分析及可视化

1、整体情况

1、总交易金额
select sum(payment_value) as 总交易金额 from orders_merged

2、总订单量
select count(order_id) as 总订单量 from orders_merged

3、用户数
select count(distinct customer_unique_id) as 用户数 from orders_merged

4、客单价
select sum(payment_value)/count(distinct customer_unique_id) as 客单价 from orders_merged

5、商家数
select count(distinct seller_id) as 商家数 from orders_merged

6、SpU
select count(distinct product_category_name) as SPU from orders_merged

7、SKU
select count(distinct product_id) as SPU from orders_merged

运行代码分别得到结果为总交易金额15,170,437.55元，用户数93,358位，客单价162.50元，总订单数96,478笔,，商家数2,959位，SPU74个，SKU31111个。

2、用户分析

CREATE view  order_time AS
SELECT 
 order_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_merged

为了方便后续分析，先转化日期格式，建立视图，方便后续分析。

2.1、用户流量分析

（1）日活跃用户数

select 
 d 日期,
 count(DISTINCT customer_unique_id) DAU 
from order_time
group by d
order by d

从图中可以看出，DAU的趋势是逐渐递增的，并且在2017年11月24日当天DAU增长至1132，较前一日增长327%，经查询可知，11月24日为“黑色星期五”，该日欧美国家会进行打折促销活动，可以看出活动效果很好。

（2）月活跃用户数

select 
 y 年份,
 m 月份,
 count(DISTINCT customer_unique_id,) MAU 
from order_time
group by y,m
order by y,m

从图中可以看出，2017年11月前，MAU呈现整体快速增长的趋势，2017年11月后MAU逐渐趋向平稳，也说明2017年11月24日的活动效果显著。

（3）各时段活跃用户数

select 
 h 时,
 count(DISTINCT customer_unique_id) 时段活跃用户数 
from order_time
group by h
order by h

从图中可以看出，晚上10点至上午5点，活跃用户数逐渐降低，因为该时间段大多数人都在休息。从上午5点至上午10点，活跃用户数快速上升。从上午10点至晚上10点，活跃用户数基本平稳。运营人员可以根据活跃用户的不同时间段，采取相应的促销措施。

2.2、用户RFM分析

（1）构造RFM用户分类

根据RFM分类模型定义，将客户分成8大类别

具体代码如下：

CREATE VIEW Recency AS
SELECT 
 customer_unique_id,
 (CASE WHEN DATEDIFF(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, 
 count(1)>(SELECT avg(cons) from (SELECT customer_unique_id, count(*) as cons from order_detail
GROUP BY  customer_unique_id) a) as F
FROM order_detail
GROUP BY  customer_unique_id

# 构造M值
CREATE VIEW Monetary AS
SELECT 
 customer_unique_id,
 payment_value,payment_value>(SELECT AVG(payment_value) FROM order_detail) as M
FROM order_detail
GROUP BY  customer_unique_id

# 构造RFM分层
CREATE VIEW RFM AS
SELECT 
Recency.customer_unique_id, 
Monetary.payment_value,
(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 '一般挽留用户'
ELSE '其他' END) AS 用户类型
FROM Recency 
INNER JOIN Frequency
ON Recency.customer_unique_id = Frequency.customer_unique_id
INNER JOIN Monetary 
ON Recency.customer_unique_id = Monetary.customer_unique_id

（2）查看各类型用户数量

SELECT 
用户类型,
count(*) 数量,
count(*)/(select count(*) from rfm) 占比
from rfm
GROUP BY 用户类型
ORDER BY 用户类型

从图中可以看出，一般发展用户、一般挽留用户占全体用户68%，说明用户中大多数还是新用户为主，但流失用户比重较大。重要发展用户、重要挽留用户占全体用户29%。其余四类合计占比不到3%，平台应加强客户价值管理，特别是重要价值客户占比应努力提高。

（3）查看各类型用户销售金额及占比

SELECT 
 用户类型,
 sum(payment_value) as 销售额,
 sum(payment_value)/(SELECT sum(payment_value) from rfm) as 占比
from rfm
GROUP BY 用户类型

从图中可以看出，重要发展客户和重要挽留客户的消费金额占全体用户的63%，一般发展客户和一般挽留客户的消费金额占全体用户的34%。结合RFM客户分类图，两者之间的比例正好相反，这也符合电商的二八定律，因此重要型客户仍是运营维护的重要。

（4）查看各类型用户品类偏好

a.重要发展用户

SELECT 
 用户类型,
 product_category_name 商品品类,
 sum(a.payment_value) 消费金额,
 rank() over(order by sum(a.payment_value) desc ) 排名
FROM order_detail a 
LEFT JOIN orders_merged b ON a.order_id=b.order_id
left join rfm c on a.customer_unique_id = c.customer_unique_id
where 用户类型='重要发展用户'
GROUP BY product_category_name

b.重要挽留用户

SELECT 
 用户类型,
 product_category_name 商品品类,
 sum(a.payment_value) 消费金额,
 rank() over(order by sum(a.payment_value) desc ) 排名
FROM order_detail a 
LEFT JOIN orders_merged b ON a.order_id=b.order_id
left join rfm c on a.customer_unique_id = c.customer_unique_id
where 用户类型='重要挽留用户'
GROUP BY product_category_name

c.一般发展用户

SELECT 
 用户类型,
 product_category_name 商品品类,
 sum(a.payment_value) 消费金额,
 rank() over(order by sum(a.payment_value) desc ) 排名
FROM order_detail a 
LEFT JOIN orders_merged b ON a.order_id=b.order_id
left join rfm c on a.customer_unique_id = c.customer_unique_id
where 用户类型='一般发展用户'
GROUP BY product_category_name

d.一般挽留用户

SELECT 
 用户类型,
 product_category_name 商品品类,
 sum(a.payment_value) 消费金额,
 rank() over(order by sum(a.payment_value) desc ) 排名
FROM order_detail a 
LEFT JOIN orders_merged b ON a.order_id=b.order_id
left join rfm c on a.customer_unique_id = c.customer_unique_id
where 用户类型='一般挽留用户'
GROUP BY product_category_name

其余四类以此类推，由于金额占比较小不做呈现，从上面4张图可以看出，每种类别的客户都有相应自己的偏好，平台在做客户维护时，可以根据对应客户类别进项推送商品类别信息，从而有的放矢。另外可以看出，moveis_decoracao品类商品收到4大客户类别同时喜爱，商家可以对该类商品进行促销推广。

2.3、用户地区分布

select 
 customer_state
 ,count(distinct customer_unique_id) as cons
from orders_merged
group by customer_state

从图中可以看出，有近42%的用户来自SP州，可能是该地区经济发达，人口密度大，平台在后续运营过程中在该地区的宣传和维护投入的同时，也应该根据不同地区特点，开发不同地区。

2.4、用户支付分析

a、支付方式占比

select
 payment_type
 ,count(1) as cons
 ,count(1)/(select count(1) from orders_merged) as  占比
 from orders_merged
group by payment_type

从图中可以看出，75.8%的用户选择用信用卡的方式来支付，19.89%的用户选择boleto的方式，说明这两种方式在该平台是主流的支付方式。

b、分期付款期数占比

select
 payment_installments
 ,count(1) as cons
 ,count(1)/(select count(1) from orders_merged) as 占比
from orders_merged
group by payment_installments
ORDER BY payment_installments

从图中可以看出近半数的用户不选择分期，而选择分期付款的用户大部分选择分2、3期的方式来进行。另外分期数从2期到10期的合计占比也将近总人数的一半，说明平台也推荐分期付款，且大多数人选择10期以内。

3、商户分析

3.1、商户地区分布

select 
 seller_state
 ,count(distinct seller_id) as cons
from orders_merged
GROUP BY seller_state

从图中可以看出，有近60%的商户来自SP州，说明该地区商业发达，这与用户地区分布的分析结果文和。

3.2、商户交易额

select 
 价格区间
 ,count(1) as cons
 ,count(1)/(select count(distinct seller_id)from orders_merged) as 占比
from (select 
 seller_id
 ,sum(payment_value) as sum_p
 ,case when sum(payment_value)<=1000 then '(0,1000]'
       when sum(payment_value)>1000 and sum(payment_value)<=5000 then '(1000,5000]'
	   when sum(payment_value)>5000 and sum(payment_value)<=10000 then '(5000,10000]'
	   else '(10000,∞]' end as '价格区间'
from orders_merged
GROUP BY seller_id
order by sum_p) a
group by 价格区间

从图中可以看出，近半数的商户的交易额在1000以内，1000至5000以内的商户占29%，5000至1万和1万以上的商户分别各占10%左右，说明平台的商户仍是以小商户为主。

3.3、商户平均分

select 
 评分区间
 ,count(1) as cons
 ,count(1)/(select count(distinct seller_id)from orders_merged) as 占比
from (select 
		seller_id
		,avg(review_score) as avg_r
		,case when avg(review_score)>=1 and avg(review_score)<2 then '[1,2)'
              when avg(review_score)>=2 and avg(review_score)<3 then '[2,3)'
			  when avg(review_score)>=3 and avg(review_score)<4 then '[3,4)'
			  else '[4,5]' end as '评分区间'
	  from orders_merged
	  GROUP BY seller_id
	  order by avg_r) a
group by 评分区间

从图中可以看出，超过75%的商户平均评分为4分以上，平均分3分以下的商户仅占5%左右，说明平台的商户服务较好，商品品质也不错。

4、产品分析

4.1、交易额、交易量、单价

select
 case when product_category_name !='' then product_category_name
 else 'lost_name' end 商品品类
 ,sum(payment_value) 交易额
 ,sum(order_item_id)  交易量
 ,sum(payment_value)/sum(order_item_id)  单价
from orders_merged
GROUP BY product_category_name



select
 单价区间
 ,count(1) as cons
 ,count(1)/(select count(DISTINCT product_category_name) from orders_merged)
from (select
product_category_name 
,sum(payment_value)/sum(order_item_id)  单价
,case when sum(payment_value)/sum(order_item_id)<=100 then '(0,100]'
      when sum(payment_value)/sum(order_item_id)>100 and sum(payment_value)/sum(order_item_id)<=200 then '(100,200]'
			when sum(payment_value)/sum(order_item_id)>200 and sum(payment_value)/sum(order_item_id)<=500 then '(100,500]'
			when sum(payment_value)/sum(order_item_id)>500 then '(500,∞]'
			else '(10000,∞]' end as '单价区间'
from orders_merged
GROUP BY product_category_name) a
group by 单价区间

从上面两图可以看出，平台各个品类商品的交易额和交易量的趋势基本相同，说明平台的商品价格相对比较接近，且交易额和交易量排在前列的基本都是价格较低的产品，只有少数用户购买了高价商品，说明平台还是以平价策略为主。平台的商品品类58%在100至200之间，属于平价商品，可以看出此平台的商品价格定位属于中等偏下。所以，吸引商品价格较低的商户如文具类、幼儿玩具类、日用品类等商户入驻，是平台拉新重点。在资源充足的情况下，在考虑培养高价格商品商户。

4.2、帕累托分析

select
 case when product_category_name !='' then product_category_name
 else 'lost_name' end 商品品类
 ,sum(payment_value) as sum_p
 ,sum(sum(payment_value)) over(order by sum(payment_value) desc)
from orders_merged
GROUP BY product_category_name

从上图可以看出，销售额前20%的商品占据了总销售额的76.56%，二八定律依然有效。因此维护好该类商品的商户依然是平台的重点，但同时根据长尾定律，增加足够多商品品类同样能带来不俗收益，因此平台在后期也可以该类增加平台商品品类。

五、销售分析

5.1、GMV（季度、月度）

季度GMV
select 
 y 年份,
 q 季度,
 sum(payment_value) 季GMV 
from order_detail
group by y,q
order by y,q

月GMV
select 
 y 年份,
 m 月份,
 sum(payment_value) 月GMV 
from order_detail
group by y,m
order by y,m

源数据中，2016年第四季度可能因为数据不完整，导致GMV异常，但从整体的GMV上可以看出，该平台的交易额是增长的，其中2017年第4季度是一个节点，2017年第四季度之前，平台GMV增长迅速，节点之后虽然仍然是增长趋势，但速率已经明显放缓，2018年第三季度由于缺少2018年9月数据，所以不具有比较意义。

将数据继续按时间向下挖掘可以看出，结论基本与前图相同，但是节点之后的2018年6、7、8月GMV已经出现下降趋势，平台应该分析原因引起重视。

5.2、ARPU（季度、月度、日）

季度ARPU
select 
 y 年份,
 q 季度, 
 round((sum(payment_value)/count(DISTINCT customer_unique_id)),2) 季度ARPU 
from order_detail
group by y,q
order by y,q

月ARPU
select 
 y 年份,
 m 月份,
 round((sum(payment_value)/count(DISTINCT customer_unique_id)),2) 月ARPU 
from order_detail
group by y,m
order by y,m


日ARPU
select 
 d 日期, 
 round((sum(payment_value)/count(DISTINCT customer_unique_id)),2) ARPU 
from order_detail
group by d
order by d

从上面三图可以看出，平台的ARPU在相对较长的一段时间里比较平稳，但平台也需要重视ARPU长期没有有效突破，且2018年开始出现下降趋势。

六、分析总结

1.平台运营建议：平台前期属于快速成长期，但目前发展已经遇到瓶颈，个别指标甚至出现回落。

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

2.用户、商家和产品运营建议：结合用户共同特点和商品喜好采取运营措施。

平台用户的消费次数都较少，有较大的提升空间。对于已经有消费行为的老用户进行针对性消息推送并提供消费返还抵用券等优惠，降低二次消费门槛，提升复购率。同时可以推出vip会员服务，为用户提供消费折扣，引导用户长期消费，增强粘性。
在挑选用户均喜爱的产品的同时，也可以根据不同客户类型挑选出各类型用户偏好的产品，分别推送，有的放矢。
平台的用户和商家地域性明显，主要集中在SP地区，建议平台可以选择扩大运营的规模，根据不同地区的消费特点，增加SP以为的其他地区的业务投入，加大宣传折扣力度，引入更多的商家和用户。
商品以价格偏低为主，种类相对比较单一，可以实适当的引入价格中等或偏高的商品，丰富商品的种类促进购买。

原文链接：https://blog.csdn.net/canwish/article/details/120158751