0
$\begingroup$

I have a question related to the standard metric used to evaluate subscription model businesses, aka user retention.

We define user retention as the percentage of customers that keep their subscription active over time; retention can be associated with a specific cohort - aka a sub-group of users that share a characteristic - in a business scenario, all the users acquired over the course of a given month.

Let's suppose there is a company that ships fresh flower bouquets every month to your door.

Users can purchase two kinds of subscriptions, with different contract billing cycles:

  • Monthly Plans: the user gets billed each 30 days.

  • 3-Months Prepaid Plans: the user pays in advance every 3 months, with a 30% discount.

The userbase is then composed of two groups - monthly and prepaid users; the proportion of the two groups varies over time, but it usually stays stable around 80% monthly users vs. 20% prepaid users.

monthly_prop = 0.8
prepaid_prop = 1 - monthly_prop

I have enough historical data to observe the previous 36 months retention rates, collected in this google docs.

The document contains 2 main tables:

Monthly Plans Retention Rate, It is expressed as a percentage. At the first period (ie, the acquisition period) the percentage of retained customers is of course 100%, then it goes to 73% at the second period, 45% at the third and so on until the 36th period - when only the 5% of the initially acquired customers are still active.

library(tidyverse)
library(googlesheets4)

ret_monthly_df <- read_sheet('1dRsr1ju0KBSFl7wSgiWYcBMgqxPpD3cRGpfyAD1WpgQ',
                  sheet = 'Retentions', 
                  range = 'B5:C41')

> head(ret_monthly_df)
# A tibble: 6 x 2
  periods_after_acquisition retention
                      <dbl>     <dbl>
1                         1       100
2                         2        73
3                         3        45
4                         4        29
5                         5        20
6                         6        17

Prepaid Plans Retention Rate. It is similarly expressed as a percentage, but each period accounts for 90 days (3 months). At the first period (ie, the acquisition period - aka the months 1,2 and 3) the percentage of retained customers is 100%; at the second period (ie. months 4,5 and 6), 55% of customers are still active and so on until the 12th period (ie. months 34,35 and 36), when only the 5% of the initially acquired customers are still active.

    ret_prepaid_df <- read_sheet('1dRsr1ju0KBSFl7wSgiWYcBMgqxPpD3cRGpfyAD1WpgQ',
                      sheet = 'Retentions', 
                      range = 'E5:G17')

> head(ret_prepaid_df)
# A tibble: 6 x 3
  periods_after_acquisition periods_after_acquisition_ordinal retention
                      <dbl>                             <dbl>     <dbl>
1                         3                                 1       100
2                         6                                 2        55
3                         9                                 3        25
4                        12                                 4        15
5                        15                                 5        10
6                        18                                 6         7

My question is: given the two group's historical retention rates in the previous 36 months, how can I calculate the "blended retention rate"?

For the "blended retention rate", I mean the composed "sum" of the retention rates of monthly and prepaid orders.

I start from the assumption that the prepaid users' retention rate can be intended as a monthly users retention rate that stays the same every 3 months; it might sound like a stupid question, but I would like to understand how to "normalize" the two rates and unify them in a single KPI.

For this purpose, I thought of expanding the dataframe ret_prepaid_df in a way its length matches the ret_monthly_df's one.

ret_prepaid_exp_df <- read_sheet('1dRsr1ju0KBSFl7wSgiWYcBMgqxPpD3cRGpfyAD1WpgQ',sheet = 'Retentions', range = 'N5:O41')

> head(ret_prepaid_exp_df)
# A tibble: 6 x 2
  periods_after_acquisition retention_prepaid_expanded
                      <dbl>                      <dbl>
1                         1                        100
2                         2                        100
3                         3                        100
4                         4                         55
5                         5                         55
6                         6                         55

Any help, advise or comment is extremely appreciated!

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.