使用Tidyr/Dplyr实现迭代计数：统计接通前的呼叫次数

数据预处理（保留所有记录） #

先加载依赖包，再对原始数据按号码分组、按时间排序，计算每个呼叫的尝试序号，同时标记首次接通的位置：

library(dplyr)

# 处理原始数据，保留所有记录并计算尝试次数
processed_data <- data %>%
  group_by(PhoneNum) %>%
  # 按呼叫日期升序排列，确保时间顺序正确
  arrange(Date, .by_group = TRUE) %>%
  # 给每个号码的呼叫按顺序编号
  mutate(CallOrder = row_number()) %>%
  # 找到当前号码首次接通的位置（无接通记录则返回Inf）
  mutate(FirstAnswerIdx = min(which(Answered == "Member Answered"), na.rm = TRUE)) %>%
  # 标记每个呼叫属于接通前的第几次尝试，或接通后的呼叫
  mutate(CallAttempts = ifelse(CallOrder <= FirstAnswerIdx, as.character(CallOrder), "Post-Answer")) %>%
  ungroup()

处理后的数据会保留所有原始记录，同时新增CallOrder（呼叫顺序）、FirstAnswerIdx（首次接通位置）、CallAttempts（尝试次数标记）三列，示例输出：

PhoneNum     Answered           Date CallOrder FirstAnswerIdx CallAttempts
  <chr>        <chr>              <date>     <int>          <int> <chr>       
1 1112223334 Voice Mail      2024-05-26         1              3 1           
2 1112223334 Answering Machine 2024-05-27         2              3 2           
3 1112223334 Member Answered 2024-05-28         3              3 3           
4 1112223333 Voice Mail      2024-05-29         1              3 1           
5 1112223333 Answering Machine 2024-05-30         2              3 2           
6 1112223333 Member Answered 2024-05-31         3              3 3           

完成三个统计目标 #

1. 总呼叫尝试次数

可根据需求选择两种统计方式：

• 所有呼叫的总次数（含接通后继续呼叫的记录）：

total_all_attempts <- nrow(processed_data)
cat("总呼叫尝试次数（所有呼叫）：", total_all_attempts, "\n")

• 首次接通前的总尝试次数（含接通那次）：

total_pre_answer_attempts <- processed_data %>%
  filter(Answered == "Member Answered" & CallOrder == FirstAnswerIdx) %>%
  summarise(Total = sum(CallOrder)) %>%
  pull(Total)
cat("总呼叫尝试次数（接通前含接通）：", total_pre_answer_attempts, "\n")

若需不含接通那次的次数，将sum(CallOrder)替换为sum(CallOrder - 1)即可。

2. 产生“曝光”的呼叫次数

假设Voice Mail和Answering Machine属于用户知晓的曝光类型，可按需调整：

exposure_types <- c("Voice Mail", "Answering Machine")
exposure_count <- processed_data %>%
  filter(Answered %in% exposure_types) %>%
  nrow()
cat("产生曝光的呼叫次数：", exposure_count, "\n")

若需统计每个用户的曝光次数，新增group_by(PhoneNum)后用summarise(ExposureAttempts = n())即可。

3. 每个用户接通前的平均尝试次数

同样分两种统计逻辑：

• 含接通那次的平均次数：

avg_pre_answer_attempts <- processed_data %>%
  filter(Answered == "Member Answered" & CallOrder == FirstAnswerIdx) %>%
  summarise(Average = mean(CallOrder)) %>%
  pull(Average)
cat("每个用户接通前的平均尝试次数（含接通）：", avg_pre_answer_attempts, "\n")

• 不含接通那次的平均次数：

avg_pre_answer_attempts_excl <- processed_data %>%
  filter(Answered == "Member Answered" & CallOrder == FirstAnswerIdx) %>%
  summarise(Average = mean(CallOrder - 1)) %>%
  pull(Average)
cat("每个用户接通前的平均尝试次数（不含接通）：", avg_pre_answer_attempts_excl, "\n")

可选：处理从未接通的号码 #

若数据存在从未接通的号码，可单独筛选统计：

# 筛选从未接通的号码记录
unanswered_users <- processed_data %>%
  group_by(PhoneNum) %>%
  filter(all(Answered != "Member Answered")) %>%
  ungroup()

# 统计这类号码的总尝试次数
unanswered_attempts <- nrow(unanswered_users)
cat("从未接通的号码总尝试次数：", unanswered_attempts, "\n")

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