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

2023-10-28 13:40

本文主要是介绍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 获取月新增用户和月复购用户的人均订单数

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

#月新增用户的人数
selectmin_month,count(user_id)
from(select distinctuser_id,min(sale_month) as min_monthfrom sale_infogroup by user_id
) a
group by min_month

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

#月新增用户的订单数
selectsale_month,count(user_id) as cnt_order
from sale_info a
where (a.user_id,a.sale_month) in(selectuser_id,min(sale_month) as min_monthfrom sale_infogroup 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表中找到每个月新增用户和首次购买月的记录,然后按照销售月分组对销售金额求和,即可获得月新增用户的消费。

#月新增用户的销售额
selectsale_month,round(sum(sale),2) as sum_sale
from sale_info a
where (a.user_id,a.sale_month) in(select distinctuser_id,min(sale_month) as min_monthfrom sale_infogroup 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) as2from(select distinctmain.user_id,attr.sale_month,min_monthfrom(#统计月新增用户的记录selectuser_id,min(sale_month) as min_monthfrom sale_infogroup by user_id) mainleft 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月
110.17340.14490.12350.14960.14250.1211
210.26880.16760.19940.14160.15610
310.23560.19830.17820.146600
410.20920.20630.1433000
510.20680.15570000
610.168800000
71000000
求平均121.04%17.46%16.11%14.59%14.93%12.11%
  • 分析人均订单和人均消费:发现新增用户的人均下单数和消费远低于复购用户,但整体趋势大致一样,在4月不论是新增还是复购用户的订单数都下降,但是销售额反而是最高的。

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

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

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

这篇关于Excel 分析用户复购情况 嵌套数据透视的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/293586

相关文章

Java利用JSONPath操作JSON数据的技术指南

《Java利用JSONPath操作JSON数据的技术指南》JSONPath是一种强大的工具,用于查询和操作JSON数据,类似于SQL的语法,它为处理复杂的JSON数据结构提供了简单且高效... 目录1、简述2、什么是 jsONPath?3、Java 示例3.1 基本查询3.2 过滤查询3.3 递归搜索3.4

java中使用POI生成Excel并导出过程

《java中使用POI生成Excel并导出过程》:本文主要介绍java中使用POI生成Excel并导出过程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录需求说明及实现方式需求完成通用代码版本1版本2结果展示type参数为atype参数为b总结注:本文章中代码均为

Spring事务中@Transactional注解不生效的原因分析与解决

《Spring事务中@Transactional注解不生效的原因分析与解决》在Spring框架中,@Transactional注解是管理数据库事务的核心方式,本文将深入分析事务自调用的底层原理,解释为... 目录1. 引言2. 事务自调用问题重现2.1 示例代码2.2 问题现象3. 为什么事务自调用会失效3

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T

Python Dash框架在数据可视化仪表板中的应用与实践记录

《PythonDash框架在数据可视化仪表板中的应用与实践记录》Python的PlotlyDash库提供了一种简便且强大的方式来构建和展示互动式数据仪表板,本篇文章将深入探讨如何使用Dash设计一... 目录python Dash框架在数据可视化仪表板中的应用与实践1. 什么是Plotly Dash?1.1

找不到Anaconda prompt终端的原因分析及解决方案

《找不到Anacondaprompt终端的原因分析及解决方案》因为anaconda还没有初始化,在安装anaconda的过程中,有一行是否要添加anaconda到菜单目录中,由于没有勾选,导致没有菜... 目录问题原因问http://www.chinasem.cn题解决安装了 Anaconda 却找不到 An

Spring定时任务只执行一次的原因分析与解决方案

《Spring定时任务只执行一次的原因分析与解决方案》在使用Spring的@Scheduled定时任务时,你是否遇到过任务只执行一次,后续不再触发的情况?这种情况可能由多种原因导致,如未启用调度、线程... 目录1. 问题背景2. Spring定时任务的基本用法3. 为什么定时任务只执行一次?3.1 未启用

Redis 中的热点键和数据倾斜示例详解

《Redis中的热点键和数据倾斜示例详解》热点键是指在Redis中被频繁访问的特定键,这些键由于其高访问频率,可能导致Redis服务器的性能问题,尤其是在高并发场景下,本文给大家介绍Redis中的热... 目录Redis 中的热点键和数据倾斜热点键(Hot Key)定义特点应对策略示例数据倾斜(Data S

利用Python开发Markdown表格结构转换为Excel工具

《利用Python开发Markdown表格结构转换为Excel工具》在数据管理和文档编写过程中,我们经常使用Markdown来记录表格数据,但它没有Excel使用方便,所以本文将使用Python编写一... 目录1.完整代码2. 项目概述3. 代码解析3.1 依赖库3.2 GUI 设计3.3 解析 Mark