如何通过客户购买频率透视表分析电商复购率？

本文共计1319个文字，预计阅读时间需要6分钟。

如果您在电商运营中需要量化用户忠诚度，但复购率的计算逻辑和数据透视实现方式不够清晰，可能是因为复购定义不明确、时间颗粒度或客户重购规则不统一所致。以下是基于标准电商场景的多种计算路径与透视构建方法：

一、按自然月统计复购客户占比（基础法）

该方法以“客户在单个自然月内完成≥2笔有效订单”为复购判定标准，分母为当月所有发生过至少1笔订单的独立客户数，结果反映月度客户行为粘性。

1、清洗原始订单数据：筛选出交易状态为“已支付”或“已完成”的记录，剔除退款、关闭、测试订单等无效行。

2、新增“月份”字段：使用Excel公式 =TEXT([订单日期],"yyyy-mm") 或 =YEAR([订单日期])*100+MONTH([订单日期]) 统一归并时间粒度。

3、创建客户级汇总表：插入数据透视表，将“顾客编号”拖入【行】区域，“订单ID”拖入【值】区域并设置为“计数”，再将“月份”拖入【列】区域，生成每位客户每月下单次数矩阵。

4、标记复购客户：在透视表外新增辅助列，对每位客户每月数值应用公式 =IF(该单元格>1,1,0)，仅当次数≥2时标记为1。

5、计算月复购率：对每列辅助值求和（复购客户数），再除以该列非零行数（当月有消费客户数），结果格式化为百分比。

二、基于首次购买锚点的复购率（新客留存视角）

该方法聚焦新客生命周期，将某月首次下单的客户定义为“当月新增用户”，后续月份中再次下单即视为复购，用于评估拉新质量与初期转化效率。

1、识别每位客户的首次购买月份：使用Excel数组公式 =MIN(IF(顾客编号=当前编号,月份))，或借助Power Query按顾客编号分组取最小月份。

2、为每笔订单标注“是否首购”：新增列，若订单月份等于该客户首次购买月份则填“是”，否则填“否”。

3、筛选出全部首购订单，提取其中的顾客编号并去重，形成当月新增客户清单。

4、在全量订单中筛选出这些新增客户的后续订单（月份 > 首购月份），按月份统计其复购客户数。

5、复购率=当月复购客户数 / 当月新增客户总数，例如7月新增1000人，8月其中180人再次下单，则7月新增客的次月复购率为18%。

三、嵌套透视法实现多维复购频率分析

该方法通过双重透视结构，同时呈现客户分层（如高价值/低频）、时间维度（月/季度）与复购频次分布，支持交叉洞察不同群体的复购强度差异。

1、第一层透视：以“顾客编号”为行、“月份”为列、“订单ID”为值（计数），生成客户-月份频次表。

2、第二层透视：将第一层结果作为新数据源，以“月份”为行、“复购频次区间”为列（如1次、2次、3-5次、6次以上），对“顾客编号”计数。

3、设置频次分组：在第二层透视中右键点击“复购频次”字段→“组合”，设定起始值1、终止值20、步长1，再手动合并区间。

4、添加条件格式：选中频次计数区域，设置色阶规则——数值越高颜色越深，直观识别高频复购集中月份。

5、插入切片器：为“客户等级”或“地域”等维度添加切片器，动态联动观察不同客户群的复购频次结构变化。

四、使用SUMPRODUCT函数免透视快速计算（轻量场景）

适用于数据量较小（

1、确保订单表含“顾客编号”“订单日期”两列，且已按日期升序排列。

2、在空白列输入公式：=SUMPRODUCT((顾客编号列=当前行顾客编号)*(MONTH(订单日期列)=MONTH(当前行订单日期))*(YEAR(订单日期列)=YEAR(当前行订单日期)))，回车后得到该客户当月订单总数。

3、新增“是否复购”列：=IF(上列结果>1,1,0)。

4、计算当月复购率：=SUMIF(月份列,"2026-04",是否复购列)/COUNTIFS(月份列,"2026-04",顾客编号列,"")，其中"2026-04"需替换为目标月份。

5、关键校验点：分母必须使用COUNTIFS去重统计客户数，而非订单总数，否则将导致比率失真。