基于Created_Date计算客户首年营收的R语言技术问询

Hey there! Let's solve this problem of calculating each customer's revenue within their first year of account creation. Your original code was limited to calendar years, but we need to use a relative 12-month window starting from each customer's Created_Date instead.

Step 1: Prepare the Dataset #

First, let's get your raw data into a properly formatted R dataframe (converting date strings to actual Date types):

df <- data.frame(
  ID = c(1,1,2,2,3,4,5,6,6),
  Status = c("Confirmed","Confirmed","Confirmed","Confirmed","Cancelled","Confirmed","Confirmed","Confirmed","Confirmed"),
  Created_Date = as.Date(c("2013-03-01","2013-03-01","2013-04-11","2013-04-11","2013-02-21","2013-08-30","2014-01-01","2015-02-22","2015-07-13")),
  Booking_Date = as.Date(c("2013-08-21","2013-10-01","2013-10-01","2013-10-01","2014-04-01","2013-10-01","2014-12-01","2015-11-18","2017-04-09")),
  Price_Booking = c(400,350,299,178,99,525,439,200,100)
)

Step 2: Calculate First-Year Revenue #

We'll use dplyr for clean data grouping/summarizing and lubridate to handle date arithmetic correctly (it takes care of leap years and date rollovers automatically):

library(dplyr)
library(lubridate)

first_year_revenue <- df %>%
  # Keep only confirmed bookings (cancelled ones don't count)
  filter(Status == "Confirmed") %>%
  # Flag bookings that fall within the first year of account creation
  mutate(
    first_year_end = Created_Date + years(1),
    is_first_year = Booking_Date >= Created_Date & Booking_Date < first_year_end
  ) %>%
  # Filter to retain only first-year bookings
  filter(is_first_year) %>%
  # Group by customer ID and sum their qualifying revenue
  group_by(ID) %>%
  summarise(Sum_Price_Booking = sum(Price_Booking)) %>%
  # Ensure all original customer IDs are included (even those with no revenue)
  right_join(df %>% distinct(ID), by = "ID") %>%
  # Replace missing sums (no valid bookings) with NA as requested
  mutate(Sum_Price_Booking = ifelse(is.na(Sum_Price_Booking), NA, Sum_Price_Booking)) %>%
  # Sort by ID to match your expected output
  arrange(ID)

Step 3: Verify the Output #

Running this code produces exactly the result you're looking for:

# A tibble: 6 × 2
     ID Sum_Price_Booking
  <dbl>             <dbl>
1     1               750
2     2               477
3     3                NA
4     4               525
5     5               439
6     6               200

Key Notes: #

• Relative Date Window: The first_year_end calculation uses lubridate::years(1) to add exactly 12 months to the creation date, which handles edge cases like leap years (e.g., a Feb 29 creation date will roll to Feb 28 in non-leap years).
• Including All Customers: The right_join ensures we don't drop customers who have no confirmed bookings in their first year (like ID 3, which gets NA instead of being omitted).
• Cancelled Bookings: We explicitly filter out cancelled bookings since they shouldn't contribute to revenue.

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