Excel 分析用户复购情况 嵌套数据透视

一、业务需求

某APP产品为了分析新用户的复购情况，提供某电商平台2022年1-7月销售数据。现希望根据7个月的用户购买数据，分析本次活动的用户复购情况。
分析业务需求：

• 方法：数据透视嵌套数据透视；
• 指标：新用户的复购率；
• 目标：观察新用户的复购率得出复购情况。
• 实现过程：
  分析新用户的复购情况，可以从新用户的购买行为入手：了解新用户的首次购买行为，例如他们购买的产品或服务、购买的时间、购买数量、购买金额等。这有助于企业了解新用户的需求和购买偏好，并制定相应的营销策略。
  ​ 1、分析月新增用户和月复购用户的人均订单数。
  ​ 2、分析月新增用户和月复购用户的人均消费（ARPU即Average Revenue Per User）；
  ​ 3、分析月新增用户的复购率。

定义月新增用户：若用户首次购买行为发生在某月，在这个月该用户无论发生几次购买行为均被定义为新增用户；
定义月复购用户：若用户在首次购买月之后再发生购买行为则被定义为月复购用户。

介绍ARPU：

ARPU是一种衡量公司或运营商在特定时间内每个用户平均收入的指标，通常用于评估移动运营商、互联网服务提供商和游戏开发商等行业。ARPU（平均每用户收入）的计算公式为：

计算公式：ARPU = 总收入 ÷ 用户数量

例如，如果一个公司在某个季度内获得了10,000的收入，并且该公司有1,000个用户，则其ARPU为10。

ARPU可以帮助公司了解他们每个用户带来的价值，并可用于比较不同时间段、地区或产品之间的表现。高ARPU通常意味着该公司的用户具有更高的付费意愿或使用频率，或者该公司的产品和服务具有更高的附加值。然而，ARPU并不能完全反映公司的盈利能力，因为它未考虑到公司的成本，例如营销费用和服务成本。

二、制定操作步骤

---

MySQL：

1、处理数据：对列名进行重命名，修改为英文，将重命名的数据导入到数据库中，接下来的操作都在数据库中进行。
2、统计计算：获取月新增用户和月复购用户的人均订单数、ARPU以及月新增用户的复购率。

---

Excel:

1、收集数据：对原始数据进行处理。
2、数据透视：获取月新增用户和月复购用户的人均订单数、ARPU以及月新增用户的复购率。

---

三、根据操作步骤计算用户留存率

------------------------------------------------Excel--------------------------------------------

1、处理数据：

不论是月新增用户还是月复购用户，都是围绕月进行的，所以首先获取“销售月”，使用如下公式：

= month(D2)

根据月新增用户的定义，首先要知道每个用户的首次购买月份。

这里注意，注册月并不一定是首次购买月份，存在用户注册后其他月再购买的情况。

所以要得到用户的“首次购买月”，需要对用户分组求销售月的最小值，这样就得到每个用户的首次购买月。我们知道数据透视具有分组聚合的功能，所以采取数据透视的方法获取“首次购买月”。

利用“vlookup”公式，在《首次购买月》中按照会员编号查找最小值，并放在《原始数据》中。具体公式如下：

=VLOOKUP(A2,首次购买月!A:B,2,FALSE)

如何判断记录是新增还是复购？使用“if”函数，具体公式如下：

=IF(F2=G2,"新增","复购")

2、数据透视：

2.1 月新增用户和月复购用户的人均订单数

计算月新增用户和复购的人数：复制会员编号、销售月、新增&复购这三列到《人均订单—excel》表中，对数据去重后插入数据透视表，拖动“销售月”为行，“新增&复购”为列，对会员编号计数。

计算月新增用户和复购的订单数：复制订单编号、销售月、新增&复购这三列到《人均订单—excel》表中，插入数据透视表，拖动“销售月”为行，“新增&复购”为列，对订单编号计数。

将数据透视表中的数分别放在月新增用户和月复购用户的人均订单表中，并求平均值：

最后画出来的月新增用户和月复购用户的人均订单图如下：

2、月新增用户和月复购用户的人均消费

2.1 月新增用户和月复购用户的人均消费

计算月新增用户和复购的消费额：复制销售金额、销售月、新增&复购这三列到《人均消费—excel》表中，插入数据透视表，拖动“销售月”为行，“新增&复购”为列，对销售金额求和。

将数据透视表中的数分别放在月新增用户和月复购用户的人均消费表中，并求平均值，最后画出来的月新增用户和月复购用户的人均订单图如下：

3、月新增用户的用户留存（复购）率

首先计算第几月：销售月 - 首次购买月。

• 对会员编号、首次购买月、第几月同时去重之后。使用去重后的数据插入数据透视表，拖动“首次购买月”为行，“第几月”为列，“会员编号”为值。
• 右键选择值显示方式—百分比；基本字段：第几月，基本项：0。即可得到第N月的新用户留存率。
  

最后复购率的计算结果和复购率图表如下：

------------------------------------------------MySQL--------------------------------------------

1、处理数据：

对列名进行重命名，修改为英文，并导入数据

创建表sale_info(会员编号user_id；订单编号order_id；注册日期login_date;销售日期sale_date;销售金额sale;销售月sale_month)

2、统计计算

获取月新增用户和月复购用户的人均订单数、ARPU以及月新增用户的复购率。

2.1 获取月新增用户和月复购用户的人均订单数

计算月新增用户数：首先对用户分组求每个用户的首次购买月份；再把结果按首次购买月份分组后对用户编号计数获得用户数。

#月新增用户的人数
select
	min_month,
	count(user_id)
from(
	select distinct
		user_id,
		min(sale_month) as min_month
	from sale_info
	group by user_id
) a
group by min_month

计算月新增用户的订单数：从sale_info表中找到每个月新增用户和首次购买月的记录，然后按照销售月分组计数，即可获得月新增用户的ing单数。

#月新增用户的订单数
select
	sale_month,
  count(user_id) as cnt_order
from sale_info a
where (a.user_id,a.sale_month) in(
	select
		user_id,
		min(sale_month) as min_month
	from sale_info
	group by user_id
	)
group by sale_month
order by sale_month

月新增用户的人均订单数：不想让sql代码看起来太复杂，所以将sql的计算结果复制到excel中做求月新增用户的人均订单数处理。

计算月复购用户数：月复购用户数 = 月购买用户数 - 月新增用户数，下面计算月购买用户数：

#月购买用户的人数
select 
	sale_month,
	count(distinct user_id)
from sale_info
group by sale_month
order by sale_month

计算月复购用户的订单数：同理，月复购订单数 = 月购买订单数 - 月新增订单数，下面计算月购买订单数：

#月购买用户的订单数
select 
	sale_month,
	count(user_id) as cnt_order
from sale_info
group by sale_month
order by sale_month

月复购用户的人均订单数：将sql的计算结果复制到excel中做求月复购用户的人均订单数处理。

2.2 获取月新增用户和月复购用户的人均消费

月新增用户的销售额：计算月新增用户的消费金额：从sale_info表中找到每个月新增用户和首次购买月的记录，然后按照销售月分组对销售金额求和，即可获得月新增用户的消费。

#月新增用户的销售额
select
	sale_month,
	round(sum(sale),2) as sum_sale
from sale_info a
where (a.user_id,a.sale_month) in(
	select distinct
		user_id,
		min(sale_month) as min_month
	from sale_info
	group by user_id
)
group by sale_month
order by sale_month

月新增用户的人均消费：将sql的计算结果复制到excel中做求月新增用户的人均消费处理。

计算月复购用户的消费金额：同理，月复购消费金额 = 月购买消费金额 - 月新增消费金额，下面计算月购买消费金额：

#月购买用户的销售额
select 
	sale_month,
	round(sum(sale),2) as sum_sale
from sale_info
group by sale_month
order by sale_month

月复购用户的人均消费：将sql的计算结果复制到excel中做求月复购用户的人均订单数处理。

2.3 月新增用户的复购率

按照首次购买月分组求留存率。下面是求第2月的留存率：

#3、统计每个月的新增用户的第N月留存率
select 
		min_month,
    count(sale_month)/count(min_month) as 第2月
from(
		select distinct
				main.user_id,
				attr.sale_month,
				min_month
		from(
				#统计月新增用户的记录
				select
						user_id,
						min(sale_month) as min_month
				from sale_info
				group by user_id
		) main
		left join sale_info attr on main.user_id = attr.user_id and sale_month-min_month = 1
) temp
group by min_month
order by min_month

运行temp的结果如下：

user_id		sale_month		min_month
3108072				5				4
3109779				5				4
3128885				6				5
13387207			3				2
14051088			5				4
13311641			2				1
13395869			3				2
14128438			5				4
14024326			4				3
13196539			2				1
13982546			4				3
14028152			5				4
。。。。。。。。。。。。。。

最终运行结果：

月   第2月
1		0.1734
2		0.2688
3		0.2356
4		0.2092
5		0.2068
6		0.1688
7		0

依次修改sale_month-min_month = N-1 的数值就可以得到第N月的复购率，分别复制到excel中，如下：

四、得出结论

1、结论：

根据提供的数据，可以得出新用户在不同月份内的复购率情况。通过计算平均值可以了解到整体的趋势：

• 每个月之后的复购率逐渐下降，表明新用户的忠诚度和回购率有所降低。
• 新用户在第2-3个月之间的复购率相对较高，但在第4个月之后开始急剧下降，说明企业应该特别注重第4个月以后的用户维护和营销策略。

新增用户月份	第1月	第2月	第3月	第4月	第5月	第6月	第7月
1	1	0.1734	0.1449	0.1235	0.1496	0.1425	0.1211
2	1	0.2688	0.1676	0.1994	0.1416	0.1561	0
3	1	0.2356	0.1983	0.1782	0.1466	0	0
4	1	0.2092	0.2063	0.1433	0	0	0
5	1	0.2068	0.1557	0	0	0	0
6	1	0.1688	0	0	0	0	0
7	1	0	0	0	0	0	0
求平均	1	21.04%	17.46%	16.11%	14.59%	14.93%	12.11%

• 分析人均订单和人均消费：发现新增用户的人均下单数和消费远低于复购用户，但整体趋势大致一样，在4月不论是新增还是复购用户的订单数都下降，但是销售额反而是最高的。

  • 新用户和复购用户的人均订单数随着时间的推移而增加，这表明企业的市场营销策略和客户服务正在发挥作用。
  • 大多数月份内，复购用户的人均订单数高于新用户的人均订单数，这表明复购用户更加忠诚并且更有可能在未来继续购买。
  • 在第五个月之后，新增用户和复购用户的人均订单数都出现了较大幅度的增长，这可能是由于该时期涉及到某些促销活动或者新产品的推出，从而吸引了更多的订单。

2、建议：

通过分析这些数据，企业可以针对性地制定改进措施，以提高新用户的忠诚度和复购率。例如，可以尝试以下做法：

• 针对第4个月以后的新用户实施更具吸引力的优惠和促销活动，以鼓励他们再次购买。
• 针对新增用户：了解新用户的需求和使用场景，适时推出新产品或服务，以满足他们的需求和期望。
• 针对复购用户：企业可以开展更多的回馈活动，例如赠送礼品、折扣等，激励他们继续购买。
• 加强客户服务和售后支持，提高用户体验和满意度，从而增强用户忠诚度。