数据分析实战——EXCEL实现复购率计算

简介: 复购率指消费者对该品牌产品或者服务的重复购买次数,重复购买率越多,则反应出消费者对品牌的忠诚度就越高,反之则越低。

这是数据分析实战的第三篇(复购率计算篇),本文提供真实数据(脱敏),并梳理复购率计算思路和技巧,通过15分钟的阅读和实践,读者即可根据自己的订单数据灵活计算业务涉及到的复购率了。(实战数据在文末)


相关核心技巧:辅助列,数据透视表


ONE:什么是复购率?


对于销售尤其是快消品销售来说,不论线上还是线下,复购率这个指标都是重头戏,你说你的产品粘性很强,他说他的会员营销做的OJBK,我说我的内容营销对客户维系作用非常棒。


谁说了算?


复购率说了算!


我们先明确一下复购率的定义,官方说法(百科介绍):指消费者对该品牌产品或者服务的重复购买次数,重复购买率越多,则反应出消费者对品牌的忠诚度就越高,反之则越低。


粗读概念总是模糊的,提炼一下,复购率就是一段时间内,购买2次及以上客户人数占总人数的比重。这一步提炼揭示了我们计算复购率需要确定的3个点——一是一段时间(这里就是一个月),二是统计购买2次和2次以上对客户数,三是复购人数占总客户数(去重)比重。


TWO:怎么计算复购率?


第一步:数据概览


做数据分析,拿到数据后先撸(粗略看一下)一遍数据是一个极好的习惯,

我们从三个角度(订单数量、是否存在脏数据、数据逻辑)来看,不难发现:


1、实战数据一共有61072笔订单,时间涵盖了2018年7月1日-2018年7月31日,为期一月。


2、复购率计算是针对交易成功的客户,筛选交易状态,发现存在6088笔订单是用户退款,交易关闭了的,因此需要删除。


3、每一行数据只记录一个产品的交易信息,如单个客户一次性购买3个产品,系统会生成3笔(3行数据)订单信息。


第二步:数据清洗


我们先对刚才的脏数据(退款订单)进行清洗,这一列主要是和“交易状态”列相关的,选中该列,点击“排序和筛选”,


40.png


然后勾选中“付款以后用户退款成功,交易自动关闭的订单”:


51.png


样筛选出了所有脏数据,选中他们,


52.jpg


按一下“DELETE”键,和脏数据说拜拜,但是,刚才脏数据占据了很多行,现在这些行空了出来,将数据分的七零八落,为了规整数据,我们必须删除所有空行。


思路:通过定位某一列所有空单元格,再删除空单元格所在行即可:


选中A列,按住"CTRL+G",点击“定位条件”,选中“空值”:


53.jpg


这时我们已经选中了A列所有单元格,把鼠标移动到其中选中的一个,右键删除,然后选择“整行”,


54.png


至此,简单的数据清洗已经完成。(部分同学反映这次数据有点给力,有些操作可能会耗些时间,大家耐心等待)。


第三步:计算复购率


再次重申一下,从系统导出的订单,绝大多数情况,同一个客户购买了多个产品,会生成多笔订单(下单、付款时间几乎一致),更进一步,一个客户在一天内购买多次,比如上午9:00下了一单,下午15:00又下了一单,时间存在不一致,但从消费行为上看,算复购并不合理,我们在此定义,单个客户1天内下的多笔订单,不能算复购。


因此,要计算复购,需要对同一天的多笔订单进行去重,思考30秒。


有思路了吗?


辅助列是个好东西,重要的话说三遍:辅助列好,辅助列秒,辅助列它棒的呱呱叫。

一个用户在同一天下单时间一般是这样的“2018/6/1 00:12:12”,年月日时分秒的格式,如果我们用年月日来进行识别,是不是同一天内下单对多笔订单就能够去除呢?

Let's do it,先插入一列:输入YEAR()&MONTH()&DAY(),


55.png


公式的意思就是获取年份,月份,一个月第几天并合并成一个数据),接着就是去重,我们为了让“同一天内多次下单的客户算作一次购买”,把“买家会员名”和辅助列(标志用户哪一天购买)两个字段作为判断标准,进行去重。


操作:所有列,找到”数据“模块下的”删除重复项“选项卡,


56.jpg


记住,买家ID和辅助时间列两列都勾选,就是买家ID和时间辅助列都一样的情况下才会删除


57.jpg


58.png


至此,我们已经把同一天内重复购买的客户订单完全剔除掉了。


(注:这里单纯计算复购率,因此采用了比较粗暴的删除方式,以后将会介绍更好的方法。)


下面一步的关键是要统计所有用户的购买次数,数据透视表蠢蠢欲动了。


选中所有数据,插入数据透视表,我们想要得到每个客户的复购次数,所以把“买家ID”放在透视表的”行“里面,再把”买家ID“放在值里面,用计数的格式显示,就得到了每个客户的复购次数。


59.png


数据透视表显示区域是这样的:


60.png


后,我们只要统计出购买次数大于等于2次的人数,再用他们除以总人数,复购率就出来了。


用COUNTIF(区域,条件),选中数据透视表“买家ID”这一列,统计条件是“>=2",


61.jpg


结果是4628,再用COUNT统计总人数


62.jpg


可以发现,这段时间(7月份)的复购率就是16.93%(4628/27343),抛开产品和用户谈评价复购率高低就是耍流氓,这里主要讲述一种基于订单数据的通用计算法则, 暂不对指标本身做评判和讨论,后续会更新关于具体分析思维的案例。


喏,我们知道了这个月的复购率是16.93%,也就是说,在这个月,100个客户中会有近17个客户重复购买。


等等,让我们再重新念一遍:“在这个月,100个客户中会有17个重复购买”,基于这个假设,也就是说1000个客户中平均会有170个客户,10000个里面平均有1700个会重复购买。那他们重复购买的行为是怎么分布呢?(购买2次的有多少,购买3次、4次等等各有多少呢?)


SO EASY~


刚才的数据透视表“计数项:买家会员名”这一列已经统计出客户在本月的购买次数,我们可以用COUNTIF公式稍加汇总即可:


63.png


看来,复购用户占比会随着复购次数的增加而减少,有复购行为的客户中,大部分(67.39%)是购买了2次的,购买3次的用户占比16.81%,4、5、6次如上表。


敲黑板,思路和逻辑比会使用工具本身更加重要。


我们拿到订单(最重要的是买家昵称,付款时间两个字段)数据,先明确分析的目的(复购率),然后进行清洗(去掉脏数据),再为了得到结果创造条件(为了避免一天内同一客户多次下单的干扰,我们插入辅助列并去重),最终利用数据透视表进行数据汇总,基于汇总的结果,轻而易举(借助COUNT和COUNTIF)算出了复购率和复购分布。


文末附上实战数据:


链接:pan.baidu.com/s/1mtYxKt 密码:ifyn




相关文章
|
5月前
|
SQL 分布式计算 数据挖掘
从Excel到高级工具:数据分析进阶指南
从Excel到高级工具:数据分析进阶指南
210 54
|
3月前
|
数据采集 数据可视化 搜索推荐
Python数据分析全流程指南:从数据采集到可视化呈现的实战解析
在数字化转型中,数据分析成为企业决策核心,而Python凭借其强大生态和简洁语法成为首选工具。本文通过实战案例详解数据分析全流程,涵盖数据采集、清洗、探索、建模、可视化及自动化部署,帮助读者掌握从数据到业务价值的完整技能链。
388 0
|
5月前
|
机器学习/深度学习 数据采集 数据可视化
Python数据分析,别再死磕Excel了!
Python数据分析,别再死磕Excel了!
194 2
|
11月前
|
消息中间件 数据挖掘 Kafka
Apache Kafka流处理实战:构建实时数据分析应用
【10月更文挑战第24天】在当今这个数据爆炸的时代,能够快速准确地处理实时数据变得尤为重要。无论是金融交易监控、网络行为分析还是物联网设备的数据收集,实时数据处理技术都是不可或缺的一部分。Apache Kafka作为一款高性能的消息队列系统,不仅支持传统的消息传递模式,还提供了强大的流处理能力,能够帮助开发者构建高效、可扩展的实时数据分析应用。
518 5
|
8月前
|
SQL JSON 数据可视化
基于 DIFY 的自动化数据分析实战
本文介绍如何使用DIFY搭建数据分析自动化流程,实现从输入需求到查询数据库、LLM分析再到可视化输出的全流程。基于经典的employees数据集和DIFY云端环境,通过LLM-SQL解析、SQL执行、LLM数据分析及ECharts可视化等模块,高效完成数据分析任务。此方案适用于人力资源分析、薪酬管理等数据密集型业务,显著提升效率并降低成本。
12314 16
|
12月前
|
数据采集 数据可视化 数据挖掘
基于Python的数据分析与可视化实战
本文将引导读者通过Python进行数据分析和可视化,从基础的数据操作到高级的数据可视化技巧。我们将使用Pandas库处理数据,并利用Matplotlib和Seaborn库创建直观的图表。文章不仅提供代码示例,还将解释每个步骤的重要性和目的,帮助读者理解背后的逻辑。无论你是初学者还是有一定基础的开发者,这篇文章都将为你提供有价值的见解和技能。
511 0
|
7月前
|
人工智能 数据可视化 前端开发
Probly:开源 AI Excel表格工具,交互式生成数据分析结果与可视化图表
Probly 是一款结合电子表格功能与 Python 数据分析能力的 AI 工具,支持在浏览器中运行 Python 代码,提供交互式电子表格、数据可视化和智能分析建议,适合需要强大数据分析功能又希望操作简便的用户。
830 2
|
8月前
|
存储 分布式计算 大数据
基于阿里云大数据平台的实时数据湖构建与数据分析实战
在大数据时代,数据湖作为集中存储和处理海量数据的架构,成为企业数据管理的核心。阿里云提供包括MaxCompute、DataWorks、E-MapReduce等在内的完整大数据平台,支持从数据采集、存储、处理到分析的全流程。本文通过电商平台案例,展示如何基于阿里云构建实时数据湖,实现数据价值挖掘。平台优势包括全托管服务、高扩展性、丰富的生态集成和强大的数据分析工具。
|
7月前
|
SQL 数据挖掘 大数据
Excel 后,我们需要怎样的数据分析软件
在现代商业中,数据分析至关重要,但传统BI工具和编程语言如Python、SQL等各有局限。Excel虽交互性强,但面对复杂计算和大数据时力不从心。esProc Desktop作为后Excel时代的数据分析神器,采用SPL语言,具备强大的表格计算能力和天然的大数据支持,可显著降低复杂计算难度。其强交互性、简短代码和内嵌Excel插件功能,让业务人员轻松完成多步骤交互式计算,是理想的数据分析工具。现提供免费使用及丰富学习资源。
|
8月前
|
机器学习/深度学习 存储 数据可视化
这份Excel+Python飞速搞定数据分析手册,简直可以让Excel飞起来
本书介绍了如何将Python与Excel结合使用,以提升数据分析和处理效率。内容涵盖Python入门、pandas库的使用、通过Python包操作Excel文件以及使用xlwings对Excel进行编程。书中详细讲解了Anaconda、Visual Studio Code和Jupyter笔记本等开发工具,并探讨了NumPy、DataFrame和Series等数据结构的应用。此外,还介绍了多个Python包(如OpenPyXL、XlsxWriter等)用于在无需安装Excel的情况下读写Excel文件,帮助用户实现自动化任务和数据处理。

热门文章

最新文章