library(DomoR)
library(tidyverse)
library(lubridate)
load('domoCustomer')
load('domoAccessToken')
init(domoCustomer, domoAccessToken)
customerDeathsByMonth <-
fetch('a7bfbd66-af35-4300-9c06-d556cb14a008') %>%
mutate(is_dead = customers_death_date < Sys.Date()) %>%
filter(is_dead == TRUE) %>%
group_by(customers_death_year, customers_death_month) %>%
summarise(monthly_customer_deaths = n()) %>%
ungroup() %>%
rename(year = customers_death_year,
month = customers_death_month)
customers <-
fetch('a7bfbd66-af35-4300-9c06-d556cb14a008')
monthsToCalcFor <- customerDeathsByMonth %>%
select(year, month) %>%
mutate(monthYear = mdy(paste0(month, "/01/", year))) %>%
group_by(year, month, monthYear) %>%
summarise(active_cust_count = 0) %>%
ungroup()
active_cust_count <- customers %>%
select(customers_id, customers_birth_date, customers_death_date) %>%
group_by(customers_id) %>%
summarise(customers_birth_date = as_date(min(customers_birth_date)),
customers_death_date = as_date(max(customers_death_date))) %>%
mutate(active = "")
for(i in seq(1, length(monthsToCalcFor$monthYear))){
active_cust_count_i <- active_cust_count %>%
mutate(active = ifelse(customers_birth_date < monthsToCalcFor$monthYear[i] &
customers_death_date > monthsToCalcFor$monthYear[i],
1, 0)) %>%
filter(!is.na(customers_birth_date))
monthsToCalcFor$active_cust_count[i] <- sum(active_cust_count_i$active, na.rm = T)
}
commercial_churn <- merge(monthsToCalcFor, customerDeathsByMonth, by = c('year', 'month'), all.x = T) %>%
mutate(churn = monthly_customer_deaths/active_cust_count)
qplot(monthYear, churn, data = commercial_churn) + geom_smooth()
