Month-over-month Customer Retention Rate in R

Question

I am attempting to come up with a way of calculating month-over-month customer retention rate with a large data set of 390k rows. Basically, I want to know the percentage of customers present in a month that were also present in the previous month.

So if last month, customers a, b, and c purchased a product. And this month, customers b, c, and d made a purchase. Two of the three customers from last month made a purchase this month. Notice that d did not purchase last month so it is excluded from consideration this month, but next month it will be considered.

I have a simple but representative data frame below.

year_mon = c("2018 Nov", "2018 Nov", "2018 Nov", "2018 Nov", "2018 Nov", "2018 Dec", "2018 Dec", "2018 Dec", "2019 Jan", "2019 Jan", "2019 Feb", "2019 Feb", "2019 Feb")

customer_id = c(1, 2, 3, 4, 5, 2, 3, 4, 3, 4, 1, 2, 3)

data.frame(customer_id, year_mon)

How could I calculate CRR no matter how many months I would have? That is to say, I don't want this hard coded. If I have 30 consecutive months of data or 3 months of consecutive data, I would like a solution that calculates CRR.

From https://www.bitrix24.com/glossary/what-is-customer-retention-rate-definition.php:

Customer Retention Rate = ((EC-NC)/SC)*100, where:

EC - number of customers at the end of a period
NC - number of new customers during that period
SC - number of customers at the start of that period

Let's say you released a mobile game. On September 1st you had 1000 players. You got 500 new players by September 30, however 200 players stopped playing the game. So, at the end of a period (in our case one month) you had 1300 playing customers. Let's calculate the retention rate:

((1300-500)/1000)*100=80

So, you manage to retain 80% of your customers. Each industry has their own "good" and "bad" retention rates. Needless to say, every company tries to retain maximum percentage of customers.

EDIT @r2evans here the solution you offered seems to have "reset" for January of both years oddly enough. I verified that there are customers present in December also in January, so the CRR should not have been zero. I'm wondering if there is any explanation that can account for this.

user_3358272 · Answer

Here's one way to look at it:

dat2 <- dat %>%
  mutate(
    Date = as.Date(paste0(dat$year_mon, "01"), format = "%Y %b %d")
  ) %>%
  select(-year_mon) %>%
  arrange(Date) %>%
  group_by(customer_id) %>%
  mutate(
    stayed = c(FALSE, diff(as.POSIXlt(Date)$mon) %in% c(1L, -11L)),
    notnew = c(FALSE, rep(TRUE, n() - 1))
  ) %>%
  ungroup()
dat2
# # A tibble: 13 x 4
#    customer_id Date       stayed notnew
#                   
#  1           1 2018-11-01 FALSE  FALSE 
#  2           2 2018-11-01 FALSE  FALSE 
#  3           3 2018-11-01 FALSE  FALSE 
#  4           4 2018-11-01 FALSE  FALSE 
#  5           5 2018-11-01 FALSE  FALSE 
#  6           2 2018-12-01 TRUE   TRUE  
#  7           3 2018-12-01 TRUE   TRUE  
#  8           4 2018-12-01 TRUE   TRUE  
#  9           3 2019-01-01 TRUE   TRUE  
# 10           4 2019-01-01 TRUE   TRUE  
# 11           1 2019-02-01 FALSE  TRUE  
# 12           2 2019-02-01 FALSE  TRUE  
# 13           3 2019-02-01 TRUE   TRUE

This tells us: if a customer has had continued service from last month into this month, and if not, if they are not new (i.e., gapped 1 or more months).

For instance ...

dat2 %>%
  group_by(Date) %>%
  summarize(
    n_customers = n(),
    n_stayed = sum(stayed),
    n_new = sum(!notnew),
    n_returned = sum(notnew)
  ) %>%
  mutate(
    prev_n_customer = lag(n_customers),
    CRR = 100 * n_stayed / prev_n_customer
  )
# # A tibble: 4 x 7
#   Date       n_customers n_stayed n_new n_returned prev_n_customer   CRR
#                                     
# 1 2018-11-01           5        0     5          0              NA  NA  
# 2 2018-12-01           3        3     0          3               5  60  
# 3 2019-01-01           2        2     0          2               3  66.7
# 4 2019-02-01           3        1     0          3               2  50

Given the data that you provided, I suspect the CRR is correct. (This flow assumes that the data you have is month-continuous with no gaps in data. If this dat2 could have a gapped month that is not in the data at all, then we need to modify the lag(...) logic into a self-join. Perhaps minor, I thought it unlikely if you have "solid" data.)

Updated to fix Dec-to-Jan transitions (that were previously resetting).

Data:

year_mon = c("2018 Nov", "2018 Nov", "2018 Nov", "2018 Nov", "2018 Nov", "2018 Dec", "2018 Dec", "2018 Dec", "2019 Jan", "2019 Jan", "2019 Feb", "2019 Feb", "2019 Feb")
customer_id = c(1, 2, 3, 4, 5, 2, 3, 4, 3, 4, 1, 2, 3)
dat <- data.frame(customer_id, year_mon)

Month-over-month Customer Retention Rate in R

1 Answers