合并两个数据集创建客户生命周期时间线相关变量求助

核心需求 #

需要基于客户生命周期数据构建包含以下字段的时间线：
first_date - first_lifetime_length（天/月） - defection_date - defection_length（天/月） - reactivation_date - second_lifetime_length（天/月） - last_date

数据分散在两个以ID关联的数据集：customer_data（客户基础信息）和purchase_history（购买记录）。

数据集结构 #

customer_data（相关字段） #

ID first_date
1  2014-01-13
2  2016-03-01
3  2016-06-13
4  2013-12-02
5  2017-03-27
6  2016-04-29
7  2017-11-01
8  2016-04-07
9  2016-02-29
10 2014-12-15

purchase_history（相关字段） #

ID date_of_purchase 
1  2014-09-12
1  2014-11-12
1  2015-02-13
1  2017-02-14
1  2018-12-13
1  2019-04-15
2  2016-03-01
3  2016-06-13
3  2016-09-20
3  2016-10-20

现有代码 #

# Timeline variables needed for further calculation ----
## First purchase date
customer_data$first_date

## End of First-LT, begin defection period
# A customer is considered defected after not purchasing for 365 days
purchase_history <- purchase_history %>%
  group_by(ID) %>%
  mutate(defected = if_else(date_of_purchase - lag(date_of_purchase) > 365, 1, 0))

## Defection date
purchase_history$defection_date <- if_else(purchase_history$defected==1, purchase_history$date_of_purchase, NA)

后续请求 #

1. 将两个数据集按ID合并，保留所有purchase_history中的数据
2. 协助创建时间线所需的全部变量：first_lifetime_length、defection_length、reactivation_date、second_lifetime_length、last_date

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