如何在R或Power BI中实现4周滚动平均转化率计算

需求说明 #

基于按周统计的线索量、预约量数据集，新增4周窗口滚动转化率列，核心规则：

• 单周转化率LTA = Appts(预约量) / Leads(线索量)
• 滚动转化率从最新周往前回溯取4周窗口，计算窗口内总预约量/总线索量
• 窗口不足4周时，缺失的更早周的预约量、线索量按0取值

样例数据集：

Week    Leads   Appts   LTA
4/17/2022   205  83    40.49%
4/24/2022   126  68    53.97%
5/1/2022    117  40    34.19%
5/8/2022    82   38    46.34%
5/15/2022   60   32    53.33%
5/22/2022   45   19    42.22%
5/29/2022   25   19    76.00%

计算示例：

2022年5月29日滚动转化率：取最近4周(5/8-5/29)数据，(19+19+32+38)/(25+45+60+82) = 50.943%
2022年5月22日滚动转化率：取对应4周窗口数据，不足部分补0，最终结果为29.276%

R语言实现 #

使用dplyr做数据处理，slider包做滑动窗口计算，步骤如下：

• 将Week列转换为日期格式，按周升序排序
• 定义滑动窗口为当前行+往前3行（共4周），分别计算窗口内预约量总和、线索量总和
• 两数相除得到滚动转化率

完整代码：

library(dplyr)
library(slider)
library(lubridate)

# 构造/导入数据集
df <- tibble(
  Week = mdy(c("4/17/2022","4/24/2022","5/1/2022","5/8/2022","5/15/2022","5/22/2022","5/29/2022")),
  Leads = c(205,126,117,82,60,45,25),
  Appts = c(83,68,40,38,32,19,19)
) %>% 
  mutate(LTA = Appts/Leads)

# 计算4周滚动转化率
df <- df %>%
  arrange(Week) %>%
  mutate(
    RollingAppts = slide_dbl(Appts, sum, .before = 3, .after = 0, .complete = FALSE),
    RollingLeads = slide_dbl(Leads, sum, .before = 3, .after = 0, .complete = FALSE),
    RollingAvg = RollingAppts/RollingLeads
  )

参数说明：.before = 3, .after = 0指定窗口包含当前行及之前3行，不足4行时自动对已有数据求和，天然匹配缺失周按0计算的规则，无需额外补值。

Power BI实现 #

通过DAX计算列实现，首先确保数据表中Week列为日期格式，且无重复周数据：

RollingAvg = 
VAR CurrWeek = '数据表'[Week]
VAR WindowStart = CurrWeek - 21 // 往前推3周，加上当前周共4周周期
VAR SumAppts =
    CALCULATE (
        SUM ( '数据表'[Appts] ),
        FILTER (
            ALL ( '数据表' ),
            '数据表'[Week] <= CurrWeek
                && '数据表'[Week] > WindowStart
        )
    )
VAR SumLeads =
    CALCULATE (
        SUM ( '数据表'[Leads] ),
        FILTER (
            ALL ( '数据表' ),
            '数据表'[Week] <= CurrWeek
                && '数据表'[Week] > WindowStart
        )
    )
RETURN
    DIVIDE ( SumAppts, SumLeads, 0 )

注意：如果原始数据存在缺周情况，需要先创建连续的周维度日期表，关联原数据表后将缺周的Leads、Appts填充为0，再进行计算，避免窗口范围计算错误。

内容的提问来源于stack exchange，提问作者Jacob Nordstrom