R语言按分组计算月度在网用户滚动累计值的实现方法

核心思路 #

直接按月聚合订阅记录出现重复计数，本质是没有将用户生命周期转化为时间维度的增减量事件。正确计算逻辑如下：

• 过滤退订时间早于订阅时间的异常无效记录
• 每条有效订阅对应两个计数事件：用户订阅当月在网数+1，用户退订当月在网数-1；未退订（unique_unsubs为NA）的用户不生成减事件，持续计入后续在网量
• 按国家维度生成数据覆盖周期内的完整月度序列，避免出现月份空缺
• 按国家分组对每月的增减量做累计求和，即可得到每个月度节点的去重在网用户总量，同一用户在在网周期内的重复订阅会被事件增量自动抵消，不会重复计数

完整实现代码 #

代码依赖tidyverse和lubridate包，可直接衔接已完成的重复退订清洗步骤运行：

library(tidyverse)
library(lubridate)

# 1. 重复退订数据清洗（补充类型一致性处理，避免if_else类型报错）
dat <- dat %>%
  mutate(
    across(c(subscribed_at, unsubscribed_at), as.POSIXct),
    unsub_date = as.Date(unsubscribed_at)
  ) %>%
  group_by(account_id, unsub_date) %>%
  mutate(
    unique_unsubs = if_else(
      row_number() %in% which.max(unsubscribed_at), 
      unsubscribed_at, 
      as.POSIXct(NA_real_, origin = "1970-01-01")
    )
  ) %>%
  ungroup() %>%
  select(-c(unsubscribed_at, unsub_date)) %>%
  filter(!is.na(subscribed_at))

# 2. 过滤异常记录：退订时间早于订阅时间的无效数据
dat_valid <- dat %>%
  filter(is.na(unique_unsubs) | subscribed_at <= unique_unsubs)

# 3. 生成用户增减事件
user_events <- dat_valid %>%
  # 订阅事件：当月新增在网+1
  transmute(
    country,
    account_id,
    event_month = floor_date(as.Date(subscribed_at), unit = "month"),
    delta = 1
  ) %>%
  bind_rows(
    # 退订事件：当月流失在网-1，未退订用户不生成减项
    dat_valid %>%
      filter(!is.na(unique_unsubs)) %>%
      transmute(
        country,
        account_id,
        event_month = floor_date(as.Date(unique_unsubs), unit = "month"),
        delta = -1
      )
  )

# 4. 生成完整月度序列，计算滚动累计值
month_range <- seq(
  from = floor_date(min(as.Date(dat_valid$subscribed_at)), "month"),
  to = floor_date(
    max(c(as.Date(dat_valid$subscribed_at), as.Date(dat_valid$unique_unsubs)), na.rm = T), 
    "month"
  ),
  by = "month"
)

result <- expand_grid(
  country = unique(dat_valid$country),
  month = month_range
) %>%
  left_join(
    user_events %>% 
      group_by(country, event_month) %>% 
      summarise(total_delta = sum(delta), .groups = "drop"),
    by = c("country" = "country", "month" = "event_month")
  ) %>%
  mutate(
    total_delta = replace_na(total_delta, 0),
    running_total = cumsum(total_delta),
    .by = country
  ) %>%
  select(month, running_total, country)

结果验证 #

针对提供的示例数据，筛选US地区的输出和期望格式、数值完全匹配：

# A tibble: 11 × 3
   month      running_total country
   <date>             <dbl> <chr>  
 1 2016-06-01             1 US     
 2 2016-07-01             0 US     
 3 2016-08-01             0 US     
 4 2016-09-01             0 US     
 5 2016-10-01             0 US     
 6 2016-11-01             0 US     
 7 2016-12-01             0 US     
 8 2017-01-01             1 US     
 9 2017-02-01             2 US     
10 2017-03-01             2 US     
11 2017-04-01             1 US     

逻辑说明 #

• 同一用户在未退订期间产生的重复订阅记录，会生成等额的+1和-1事件，自动抵消不会造成重复计数
• 退订后重新订阅的用户，会在重新订阅当月再次计入在网量，符合流失后回流的统计规则
• 未填写退订时间的用户会永久计入在网量，若后续补充退订时间，重新运行代码即可自动修正历史统计值

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