Outbound Call Analysis

tickets=read.csv("Hubspot_V4W_Q2_2026.csv")

tickets <- tickets %>%
  mutate(
    Create.date = ymd_hm(Create.date, tz = "America/New_York"),
    Create.date = with_tz(Create.date, "UTC")
  )


tickets <- tickets %>%
  dplyr::rename(Phone = Phone.Number)

tickets <- tickets %>%
  mutate(
    Phone = as.character(Phone),
    Phone = gsub("[^0-9]", "", Phone),
    Phone = str_sub(str_remove_all(Phone, "\\D"), -10)# remove non-numeric characters
  )
# choose the *nearest case on either side* of Start.Time.
merged_candidates <- calls_with_id %>%
  inner_join(tickets, by = "Phone", relationship = "many-to-many") %>%
  filter(Start.Time. >= Create.date) %>%
  mutate(
    diff_secs = as.numeric(
      difftime(Start.Time., Create.date, units = "secs")
    )
  )

nearest_case_per_call <- merged_candidates %>%
  group_by(call_id) %>%
  slice_min(order_by = diff_secs, n = 1, with_ties = FALSE) %>%
  ungroup()

# Recreate the final merged_df with unique (call_id, Agent) and compute time deltas
merged_df <- nearest_case_per_call %>%
  mutate(
    TimeDifference = Start.Time. - Create.date,
    TimeDifferenceMinutes = as.numeric(TimeDifference, units = "mins"),
    TimeDifferenceHours = TimeDifferenceMinutes / 60
  )

# Identify phones that still appear multiple times (multiple callbacks matched to potentially different cases)
duplicate_phones <- merged_df %>%
  group_by(Phone) %>%
  filter(n() > 1) %>%
  pull(Phone) %>%
  unique()

duplicates_df <- merged_df %>%
  filter(Phone %in% duplicate_phones)



#Remove negative values due to some sort of system errors
filtered_df <- merged_df %>%
  filter(TimeDifference > 0) %>%
  group_by(Phone, Create.date) %>%
  filter(TimeDifference == min(TimeDifference)) %>%
  ungroup()

filtered_df <- subset(filtered_df, Phone != "9802696274")

#these are being removed. They are improperly merged with case creation due to being marked as Duplicates in SF


#Add a new column with the day of the week for each web request and call back
filtered_df$V4W_Day <- weekdays(filtered_df$Start.Time.)
filtered_df$WR_Day <- weekdays(filtered_df$Create.date)


filtered_df$V4W_Hour <- hour(filtered_df$Start.Time.)
filtered_df$WR_Hour <- hour(filtered_df$Create.date)

##################################

fif.min=subset(filtered_df, TimeDifferenceMinutes <= 15)

Summarization of 24 Hour Data

Discrepencies Depending on Day and Time

Summarization of Nighttime Data

Outlier Analysis

Subset data to only include the datapoints above the Q3 timepoint from summary statistics. Those in the 25% longest wait time are those greater than or equal to 4 minutes

All calls greater than 15 minutes

By Peer Statistics

#summary statistics for each person like above

Summarization of Monthly Data

24 Hour Time Period

Overnight Hours

Holiday Biases