Data Wrangling EP 2: Calculating Monthly Recurring Revenue
I discovered this fantastic post written by Claire Carroll and I was instantly inspired. I really enjoyed studying her process and figured that it’d be a fun exercise to implement this data transformation in R. I’m not going to convert the SQL code from Claire’s post to R code verbatim. Instead, I’ll implement my own process for the data transformation. Overall, the input and output data will be the same as in Claire’s post.
Transformations will be executed on this data structure
to return this output
The data from Claire’s post is located at the dbt-labs/mrr_playbook github repository.
Load Data
library(tidyverse)
library(lubridate)
library(testthat)
library(kableExtra)
subscription_periods <- read_csv("https://raw.githubusercontent.com/dbt-labs/mrr-playbook/master/data/subscription_periods.csv")
kable(subscription_periods) %>%
kable_styling(full_width = FALSE) %>%
scroll_box(height = '600px')| subscription_id | customer_id | start_date | end_date | monthly_amount |
|---|---|---|---|---|
| 1 | 1 | 2018-11-01 | 2019-02-01 | 50 |
| 2 | 1 | 2019-04-01 | 2019-06-01 | 50 |
| 3 | 1 | 2019-06-01 | 2019-08-01 | 75 |
| 4 | 2 | 2017-09-01 | 2017-11-01 | 25 |
| 5 | 3 | 2017-09-01 | 2017-10-01 | 50 |
| 6 | 4 | 2017-10-01 | 2017-11-01 | 25 |
| 7 | 5 | 2018-11-01 | 2018-12-01 | 50 |
| 8 | 5 | 2018-12-01 | 2019-03-01 | 25 |
| 9 | 5 | 2019-07-01 | 2019-08-01 | 50 |
| 10 | 5 | 2019-08-01 | 2019-09-01 | 25 |
| 11 | 5 | 2019-09-01 | 2019-10-01 | 50 |
| 12 | 5 | 2019-10-01 | 2019-12-01 | 25 |
| 13 | 5 | 2019-12-01 | 2020-01-01 | 40 |
| 14 | 6 | 2018-05-01 | 2018-06-01 | 35 |
| 15 | 6 | 2018-06-01 | 2020-01-01 | 65 |
| 16 | 7 | 2018-01-01 | 2018-02-01 | 55 |
| 17 | 7 | 2018-02-01 | 2019-12-01 | 70 |
| 18 | 8 | 2019-04-01 | 2019-05-01 | 65 |
| 19 | 8 | 2019-05-01 | 2019-12-01 | 65 |
| 20 | 9 | 2018-05-01 | 2018-06-01 | 85 |
| 21 | 9 | 2018-06-01 | 2020-01-01 | 75 |
| 22 | 10 | 2018-04-01 | 2018-05-01 | 80 |
| 23 | 10 | 2018-09-01 | 2018-10-01 | 50 |
| 24 | 10 | 2018-10-01 | 2019-04-01 | 25 |
| 25 | 10 | 2019-04-01 | 2019-05-01 | 50 |
| 26 | 10 | 2019-05-01 | 2019-06-01 | 25 |
| 27 | 10 | 2019-06-01 | 2019-11-01 | 50 |
| 28 | 10 | 2019-11-01 | 2019-12-01 | 25 |
| 29 | 10 | 2019-12-01 | 2020-01-01 | 35 |
| 30 | 11 | 2018-09-01 | 2018-10-01 | 30 |
| 31 | 11 | 2018-10-01 | 2020-01-01 | 50 |
| 32 | 12 | 2018-06-01 | 2018-07-01 | 25 |
| 33 | 12 | 2018-07-01 | 2020-01-01 | 50 |
| 34 | 13 | 2019-08-01 | 2019-09-01 | 25 |
| 35 | 13 | 2019-09-01 | 2020-01-01 | 50 |
| 36 | 14 | 2019-03-01 | 2019-04-01 | 25 |
| 37 | 14 | 2019-04-01 | 2019-05-01 | 50 |
| 38 | 14 | 2019-05-01 | 2019-06-01 | 25 |
| 39 | 14 | 2019-06-01 | 2019-07-01 | 50 |
| 40 | 14 | 2019-07-01 | 2019-08-01 | 25 |
| 41 | 14 | 2019-08-01 | 2019-09-01 | 25 |
| 42 | 14 | 2019-09-01 | 2019-10-01 | 50 |
| 43 | 14 | 2019-10-01 | 2020-01-01 | 25 |
| 44 | 15 | 2019-02-01 | 2019-03-01 | 30 |
| 45 | 15 | 2019-03-01 | 2020-01-01 | 30 |
| 46 | 16 | 2019-07-01 | 2019-08-01 | 25 |
| 47 | 16 | 2019-08-01 | 2020-01-01 | 25 |
| 48 | 17 | 2018-11-01 | 2018-12-01 | 25 |
| 49 | 17 | 2018-12-01 | 2019-05-01 | 50 |
| 50 | 17 | 2019-05-01 | 2019-06-01 | 25 |
| 51 | 17 | 2019-06-01 | 2019-09-01 | 40 |
| 52 | 17 | 2019-09-01 | 2019-10-01 | 35 |
| 53 | 17 | 2019-10-01 | 2019-11-01 | 65 |
| 54 | 17 | 2019-11-01 | 2019-12-01 | 100 |
| 55 | 17 | 2019-12-01 | 2020-01-01 | 95 |
| 56 | 18 | 2018-11-01 | 2018-12-01 | 90 |
| 57 | 18 | 2018-12-01 | 2019-12-01 | 50 |
| 58 | 19 | 2019-05-01 | 2019-06-01 | 25 |
| 59 | 19 | 2019-06-01 | 2020-01-01 | 50 |
| 60 | 20 | 2019-01-01 | 2019-02-01 | 25 |
| 61 | 20 | 2019-02-01 | 2019-12-01 | 50 |
| 62 | 21 | 2018-11-01 | 2018-12-01 | 25 |
| 63 | 21 | 2018-12-01 | 2019-12-01 | 50 |
| 64 | 22 | 2018-12-01 | 2019-01-01 | 25 |
| 65 | 22 | 2019-01-01 | 2019-08-01 | 35 |
| 66 | 23 | 2019-03-01 | 2019-04-01 | 35 |
| 67 | 23 | 2019-04-01 | 2020-01-01 | 50 |
| 68 | 24 | 2019-05-01 | 2019-06-01 | 25 |
| 69 | 24 | 2019-06-01 | 2020-01-01 | 60 |
| 70 | 25 | 2019-08-01 | 2019-09-01 | 30 |
| 71 | 25 | 2019-09-01 | 2019-10-01 | 35 |
| 72 | 25 | 2019-10-01 | 2020-01-01 | 45 |
| 73 | 26 | 2019-05-01 | 2019-06-01 | 55 |
| 74 | 26 | 2019-06-01 | 2019-08-01 | 50 |
| 75 | 27 | 2019-07-01 | 2019-08-01 | 130 |
| 76 | 27 | 2019-08-01 | 2020-01-01 | 125 |
| 77 | 28 | 2019-07-01 | 2019-08-01 | 50 |
| 78 | 28 | 2019-08-01 | 2019-12-01 | 25 |
| 79 | 29 | 2019-06-01 | 2019-07-01 | 50 |
| 80 | 29 | 2019-07-01 | 2019-12-01 | 35 |
| 81 | 30 | 2019-04-01 | 2019-05-01 | 55 |
| 82 | 30 | 2019-05-01 | 2019-12-01 | 45 |
| 83 | 31 | 2019-11-01 | 2019-12-01 | 50 |
| 84 | 31 | 2019-12-01 | 2020-01-01 | 25 |
| 85 | 32 | 2019-05-01 | 2019-06-01 | 50 |
| 86 | 32 | 2019-06-01 | 2020-01-01 | 25 |
| 87 | 33 | 2019-10-01 | 2019-11-01 | 50 |
| 88 | 33 | 2019-11-01 | 2019-12-01 | 25 |
| 89 | 34 | 2019-08-01 | 2019-09-01 | 50 |
| 90 | 34 | 2019-09-01 | 2020-01-01 | 25 |
| 91 | 35 | 2019-10-01 | 2019-11-01 | 50 |
| 92 | 35 | 2019-11-01 | 2019-12-01 | 25 |
| 93 | 36 | 2019-09-01 | 2019-10-01 | 50 |
| 94 | 36 | 2019-10-01 | 2020-01-01 | 25 |
| 95 | 37 | 2019-09-01 | 2019-10-01 | 25 |
| 96 | 37 | 2019-10-01 | 2020-01-01 | 25 |
| 97 | 38 | 2019-09-01 | 2019-10-01 | 30 |
| 98 | 38 | 2019-10-01 | 2019-12-01 | 30 |
| 99 | 39 | 2019-11-01 | 2019-12-01 | 35 |
| 100 | 40 | 2019-09-01 | 2019-10-01 | 35 |
| 101 | 40 | 2019-10-01 | 2020-01-01 | 50 |
| 102 | 41 | 2019-11-01 | 2019-12-01 | 25 |
| 103 | 41 | 2019-12-01 | 2020-01-01 | 50 |
| 104 | 42 | 2019-09-01 | 2019-10-01 | 25 |
| 105 | 42 | 2019-10-01 | 2019-12-01 | 50 |
| 106 | 43 | 2019-11-01 | 2019-12-01 | 25 |
| 107 | 44 | 2019-10-01 | 2019-11-01 | 50 |
| 108 | 44 | 2019-11-01 | 2020-01-01 | 25 |
| 109 | 45 | 2019-10-01 | 2019-11-01 | 45 |
| 110 | 45 | 2019-11-01 | 2020-01-01 | 35 |
| 111 | 46 | 2019-12-01 | 2020-01-01 | 50 |
| 112 | 47 | 2019-10-01 | 2019-11-01 | 25 |
| 113 | 47 | 2019-11-01 | 2019-12-01 | 50 |
| 114 | 48 | 2019-11-01 | 2019-12-01 | 25 |
| 115 | 49 | 2019-11-01 | 2019-12-01 | 50 |
| 116 | 50 | 2019-12-01 | 2020-01-01 | 25 |
| 117 | 51 | 2020-01-01 | 2020-02-01 | 50 |
| 118 | 52 | 2019-12-01 | 2020-01-01 | 25 |
| 119 | 53 | 2020-01-01 | 2020-02-01 | 50 |
| 120 | 54 | 2020-01-01 | 2020-02-01 | 25 |
| 121 | 55 | 2020-01-01 | 2020-02-01 | 50 |
Data Transformation
Rename Variables
- Rename
start_date,end_dateandmonthly_amounttosubscription_start_date,subscription_end_dateandcurrent_monthly_amountto explicitly state what these fields represent.
Calculate Change Category
The change categories, as defined in Claire’s post, are:
new: the customer is a new customer that has not had a previous subscription
churn: last month the customer paid for a subscription but this month did not. A customer can churn many times
upgrade: the customer has increased their usage and is now paying more money per month
downgrade: the customer has decreased their usage and is now paying less money per month
reactivation: a previously churned customer has started subscribing again
We can translate these business rules into code by executing the following steps:
Create
current_subscription_dateby expandingsubscription_start_dateandsubscription_end_dateranges to contain one row per month for eachsubscription_id. This is the R equivalent of date spining in SQL.Create
previous_monthly_amountvialag(current_monthly_amount) to determine how much the customer previously paid for their subscription.Create
previous_subscription_datevialag(current_subscription_date). Whenprevious_subscription_dateis NA then it’s a new customer.Create
next_subscription_datevialead(current_subscription_date). If the number of months between thecurrent_subscription_dateand thenext_subscription_dateis greater than one month ornext_subscription_dateis NA then the customer churned.If
current_subscription_date == subscription_start_dateandcurrent_monthly_amount > previous_monthly_amountthen the customer upgraded.If
current_subscription_date == subscription_start_dateand thecurrent_monthly_amount < previous_monthly_amountthen the customer downgraded.If the number of months between the
previous_subscription_dateand thecurrent_subscription_dateis greater than one month then the customer reactivated.
transformed_subscription_periods <- subscription_periods %>%
rename(
subscription_start_date = start_date,
subscription_end_date = end_date,
current_monthly_amount = monthly_amount
) %>%
group_by(subscription_id) %>%
expand(
subscription_id,
customer_id,
subscription_start_date,
subscription_end_date,
current_monthly_amount,
current_subscription_date = seq(
subscription_start_date,
subscription_end_date,
by = "month"
)
) %>%
group_by(customer_id) %>%
mutate(
previous_monthly_amount = lag(current_monthly_amount),
next_subscription_date = lead(current_subscription_date),
previous_subscription_date = lag(current_subscription_date),
change_category = case_when(
is.na(previous_subscription_date) ~ "new",
interval(current_subscription_date, next_subscription_date) %/% months(1) > 1 |
is.na(next_subscription_date) ~ "churn",
interval(previous_subscription_date, current_subscription_date) %/% months(1) > 1 ~ "reactivation",
current_subscription_date == subscription_start_date &
current_monthly_amount < previous_monthly_amount ~ "downgrade",
current_subscription_date == subscription_start_date &
current_monthly_amount > previous_monthly_amount ~ "upgrade"
),
current_monthly_amount = if_else(change_category == 'churn', 0, current_monthly_amount)
) %>%
# when there are duplicated dates one of the change categories must be NA.
# arranging allows us to drop the NA change category
arrange(customer_id, current_subscription_date, change_category) %>%
distinct(current_subscription_date, .keep_all = TRUE) %>%
ungroup() %>%
select(
subscription_id,
customer_id,
previous_subscription_date,
current_subscription_date,
next_subscription_date,
previous_monthly_amount,
current_monthly_amount,
change_category
)
kable(transformed_subscription_periods) %>%
kable_styling(full_width = FALSE) %>%
scroll_box(height = '600px')| subscription_id | customer_id | previous_subscription_date | current_subscription_date | next_subscription_date | previous_monthly_amount | current_monthly_amount | change_category |
|---|---|---|---|---|---|---|---|
| 1 | 1 | NA | 2018-11-01 | 2018-12-01 | NA | 50 | new |
| 1 | 1 | 2018-11-01 | 2018-12-01 | 2019-01-01 | 50 | NA | NA |
| 1 | 1 | 2018-12-01 | 2019-01-01 | 2019-02-01 | 50 | NA | NA |
| 1 | 1 | 2019-01-01 | 2019-02-01 | 2019-04-01 | 50 | 0 | churn |
| 2 | 1 | 2019-02-01 | 2019-04-01 | 2019-05-01 | 50 | 50 | reactivation |
| 2 | 1 | 2019-04-01 | 2019-05-01 | 2019-06-01 | 50 | NA | NA |
| 3 | 1 | 2019-06-01 | 2019-06-01 | 2019-07-01 | 50 | 75 | upgrade |
| 3 | 1 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 75 | NA | NA |
| 3 | 1 | 2019-07-01 | 2019-08-01 | NA | 75 | 0 | churn |
| 4 | 2 | NA | 2017-09-01 | 2017-10-01 | NA | 25 | new |
| 4 | 2 | 2017-09-01 | 2017-10-01 | 2017-11-01 | 25 | NA | NA |
| 4 | 2 | 2017-10-01 | 2017-11-01 | NA | 25 | 0 | churn |
| 5 | 3 | NA | 2017-09-01 | 2017-10-01 | NA | 50 | new |
| 5 | 3 | 2017-09-01 | 2017-10-01 | NA | 50 | 0 | churn |
| 6 | 4 | NA | 2017-10-01 | 2017-11-01 | NA | 25 | new |
| 6 | 4 | 2017-10-01 | 2017-11-01 | NA | 25 | 0 | churn |
| 7 | 5 | NA | 2018-11-01 | 2018-12-01 | NA | 50 | new |
| 8 | 5 | 2018-12-01 | 2018-12-01 | 2019-01-01 | 50 | 25 | downgrade |
| 8 | 5 | 2018-12-01 | 2019-01-01 | 2019-02-01 | 25 | NA | NA |
| 8 | 5 | 2019-01-01 | 2019-02-01 | 2019-03-01 | 25 | NA | NA |
| 8 | 5 | 2019-02-01 | 2019-03-01 | 2019-07-01 | 25 | 0 | churn |
| 9 | 5 | 2019-03-01 | 2019-07-01 | 2019-08-01 | 25 | 50 | reactivation |
| 10 | 5 | 2019-08-01 | 2019-08-01 | 2019-09-01 | 50 | 25 | downgrade |
| 11 | 5 | 2019-09-01 | 2019-09-01 | 2019-10-01 | 25 | 50 | upgrade |
| 12 | 5 | 2019-10-01 | 2019-10-01 | 2019-11-01 | 50 | 25 | downgrade |
| 12 | 5 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 25 | NA | NA |
| 13 | 5 | 2019-12-01 | 2019-12-01 | 2020-01-01 | 25 | 40 | upgrade |
| 13 | 5 | 2019-12-01 | 2020-01-01 | NA | 40 | 0 | churn |
| 14 | 6 | NA | 2018-05-01 | 2018-06-01 | NA | 35 | new |
| 15 | 6 | 2018-06-01 | 2018-06-01 | 2018-07-01 | 35 | 65 | upgrade |
| 15 | 6 | 2018-06-01 | 2018-07-01 | 2018-08-01 | 65 | NA | NA |
| 15 | 6 | 2018-07-01 | 2018-08-01 | 2018-09-01 | 65 | NA | NA |
| 15 | 6 | 2018-08-01 | 2018-09-01 | 2018-10-01 | 65 | NA | NA |
| 15 | 6 | 2018-09-01 | 2018-10-01 | 2018-11-01 | 65 | NA | NA |
| 15 | 6 | 2018-10-01 | 2018-11-01 | 2018-12-01 | 65 | NA | NA |
| 15 | 6 | 2018-11-01 | 2018-12-01 | 2019-01-01 | 65 | NA | NA |
| 15 | 6 | 2018-12-01 | 2019-01-01 | 2019-02-01 | 65 | NA | NA |
| 15 | 6 | 2019-01-01 | 2019-02-01 | 2019-03-01 | 65 | NA | NA |
| 15 | 6 | 2019-02-01 | 2019-03-01 | 2019-04-01 | 65 | NA | NA |
| 15 | 6 | 2019-03-01 | 2019-04-01 | 2019-05-01 | 65 | NA | NA |
| 15 | 6 | 2019-04-01 | 2019-05-01 | 2019-06-01 | 65 | NA | NA |
| 15 | 6 | 2019-05-01 | 2019-06-01 | 2019-07-01 | 65 | NA | NA |
| 15 | 6 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 65 | NA | NA |
| 15 | 6 | 2019-07-01 | 2019-08-01 | 2019-09-01 | 65 | NA | NA |
| 15 | 6 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 65 | NA | NA |
| 15 | 6 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 65 | NA | NA |
| 15 | 6 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 65 | NA | NA |
| 15 | 6 | 2019-11-01 | 2019-12-01 | 2020-01-01 | 65 | NA | NA |
| 15 | 6 | 2019-12-01 | 2020-01-01 | NA | 65 | 0 | churn |
| 16 | 7 | NA | 2018-01-01 | 2018-02-01 | NA | 55 | new |
| 17 | 7 | 2018-02-01 | 2018-02-01 | 2018-03-01 | 55 | 70 | upgrade |
| 17 | 7 | 2018-02-01 | 2018-03-01 | 2018-04-01 | 70 | NA | NA |
| 17 | 7 | 2018-03-01 | 2018-04-01 | 2018-05-01 | 70 | NA | NA |
| 17 | 7 | 2018-04-01 | 2018-05-01 | 2018-06-01 | 70 | NA | NA |
| 17 | 7 | 2018-05-01 | 2018-06-01 | 2018-07-01 | 70 | NA | NA |
| 17 | 7 | 2018-06-01 | 2018-07-01 | 2018-08-01 | 70 | NA | NA |
| 17 | 7 | 2018-07-01 | 2018-08-01 | 2018-09-01 | 70 | NA | NA |
| 17 | 7 | 2018-08-01 | 2018-09-01 | 2018-10-01 | 70 | NA | NA |
| 17 | 7 | 2018-09-01 | 2018-10-01 | 2018-11-01 | 70 | NA | NA |
| 17 | 7 | 2018-10-01 | 2018-11-01 | 2018-12-01 | 70 | NA | NA |
| 17 | 7 | 2018-11-01 | 2018-12-01 | 2019-01-01 | 70 | NA | NA |
| 17 | 7 | 2018-12-01 | 2019-01-01 | 2019-02-01 | 70 | NA | NA |
| 17 | 7 | 2019-01-01 | 2019-02-01 | 2019-03-01 | 70 | NA | NA |
| 17 | 7 | 2019-02-01 | 2019-03-01 | 2019-04-01 | 70 | NA | NA |
| 17 | 7 | 2019-03-01 | 2019-04-01 | 2019-05-01 | 70 | NA | NA |
| 17 | 7 | 2019-04-01 | 2019-05-01 | 2019-06-01 | 70 | NA | NA |
| 17 | 7 | 2019-05-01 | 2019-06-01 | 2019-07-01 | 70 | NA | NA |
| 17 | 7 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 70 | NA | NA |
| 17 | 7 | 2019-07-01 | 2019-08-01 | 2019-09-01 | 70 | NA | NA |
| 17 | 7 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 70 | NA | NA |
| 17 | 7 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 70 | NA | NA |
| 17 | 7 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 70 | NA | NA |
| 17 | 7 | 2019-11-01 | 2019-12-01 | NA | 70 | 0 | churn |
| 18 | 8 | NA | 2019-04-01 | 2019-05-01 | NA | 65 | new |
| 18 | 8 | 2019-04-01 | 2019-05-01 | 2019-05-01 | 65 | NA | NA |
| 19 | 8 | 2019-05-01 | 2019-06-01 | 2019-07-01 | 65 | NA | NA |
| 19 | 8 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 65 | NA | NA |
| 19 | 8 | 2019-07-01 | 2019-08-01 | 2019-09-01 | 65 | NA | NA |
| 19 | 8 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 65 | NA | NA |
| 19 | 8 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 65 | NA | NA |
| 19 | 8 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 65 | NA | NA |
| 19 | 8 | 2019-11-01 | 2019-12-01 | NA | 65 | 0 | churn |
| 20 | 9 | NA | 2018-05-01 | 2018-06-01 | NA | 85 | new |
| 21 | 9 | 2018-06-01 | 2018-06-01 | 2018-07-01 | 85 | 75 | downgrade |
| 21 | 9 | 2018-06-01 | 2018-07-01 | 2018-08-01 | 75 | NA | NA |
| 21 | 9 | 2018-07-01 | 2018-08-01 | 2018-09-01 | 75 | NA | NA |
| 21 | 9 | 2018-08-01 | 2018-09-01 | 2018-10-01 | 75 | NA | NA |
| 21 | 9 | 2018-09-01 | 2018-10-01 | 2018-11-01 | 75 | NA | NA |
| 21 | 9 | 2018-10-01 | 2018-11-01 | 2018-12-01 | 75 | NA | NA |
| 21 | 9 | 2018-11-01 | 2018-12-01 | 2019-01-01 | 75 | NA | NA |
| 21 | 9 | 2018-12-01 | 2019-01-01 | 2019-02-01 | 75 | NA | NA |
| 21 | 9 | 2019-01-01 | 2019-02-01 | 2019-03-01 | 75 | NA | NA |
| 21 | 9 | 2019-02-01 | 2019-03-01 | 2019-04-01 | 75 | NA | NA |
| 21 | 9 | 2019-03-01 | 2019-04-01 | 2019-05-01 | 75 | NA | NA |
| 21 | 9 | 2019-04-01 | 2019-05-01 | 2019-06-01 | 75 | NA | NA |
| 21 | 9 | 2019-05-01 | 2019-06-01 | 2019-07-01 | 75 | NA | NA |
| 21 | 9 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 75 | NA | NA |
| 21 | 9 | 2019-07-01 | 2019-08-01 | 2019-09-01 | 75 | NA | NA |
| 21 | 9 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 75 | NA | NA |
| 21 | 9 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 75 | NA | NA |
| 21 | 9 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 75 | NA | NA |
| 21 | 9 | 2019-11-01 | 2019-12-01 | 2020-01-01 | 75 | NA | NA |
| 21 | 9 | 2019-12-01 | 2020-01-01 | NA | 75 | 0 | churn |
| 22 | 10 | NA | 2018-04-01 | 2018-05-01 | NA | 80 | new |
| 22 | 10 | 2018-04-01 | 2018-05-01 | 2018-09-01 | 80 | 0 | churn |
| 23 | 10 | 2018-05-01 | 2018-09-01 | 2018-10-01 | 80 | 50 | reactivation |
| 24 | 10 | 2018-10-01 | 2018-10-01 | 2018-11-01 | 50 | 25 | downgrade |
| 24 | 10 | 2018-10-01 | 2018-11-01 | 2018-12-01 | 25 | NA | NA |
| 24 | 10 | 2018-11-01 | 2018-12-01 | 2019-01-01 | 25 | NA | NA |
| 24 | 10 | 2018-12-01 | 2019-01-01 | 2019-02-01 | 25 | NA | NA |
| 24 | 10 | 2019-01-01 | 2019-02-01 | 2019-03-01 | 25 | NA | NA |
| 24 | 10 | 2019-02-01 | 2019-03-01 | 2019-04-01 | 25 | NA | NA |
| 25 | 10 | 2019-04-01 | 2019-04-01 | 2019-05-01 | 25 | 50 | upgrade |
| 26 | 10 | 2019-05-01 | 2019-05-01 | 2019-06-01 | 50 | 25 | downgrade |
| 27 | 10 | 2019-06-01 | 2019-06-01 | 2019-07-01 | 25 | 50 | upgrade |
| 27 | 10 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 50 | NA | NA |
| 27 | 10 | 2019-07-01 | 2019-08-01 | 2019-09-01 | 50 | NA | NA |
| 27 | 10 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 50 | NA | NA |
| 27 | 10 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 50 | NA | NA |
| 28 | 10 | 2019-11-01 | 2019-11-01 | 2019-12-01 | 50 | 25 | downgrade |
| 29 | 10 | 2019-12-01 | 2019-12-01 | 2020-01-01 | 25 | 35 | upgrade |
| 29 | 10 | 2019-12-01 | 2020-01-01 | NA | 35 | 0 | churn |
| 30 | 11 | NA | 2018-09-01 | 2018-10-01 | NA | 30 | new |
| 31 | 11 | 2018-10-01 | 2018-10-01 | 2018-11-01 | 30 | 50 | upgrade |
| 31 | 11 | 2018-10-01 | 2018-11-01 | 2018-12-01 | 50 | NA | NA |
| 31 | 11 | 2018-11-01 | 2018-12-01 | 2019-01-01 | 50 | NA | NA |
| 31 | 11 | 2018-12-01 | 2019-01-01 | 2019-02-01 | 50 | NA | NA |
| 31 | 11 | 2019-01-01 | 2019-02-01 | 2019-03-01 | 50 | NA | NA |
| 31 | 11 | 2019-02-01 | 2019-03-01 | 2019-04-01 | 50 | NA | NA |
| 31 | 11 | 2019-03-01 | 2019-04-01 | 2019-05-01 | 50 | NA | NA |
| 31 | 11 | 2019-04-01 | 2019-05-01 | 2019-06-01 | 50 | NA | NA |
| 31 | 11 | 2019-05-01 | 2019-06-01 | 2019-07-01 | 50 | NA | NA |
| 31 | 11 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 50 | NA | NA |
| 31 | 11 | 2019-07-01 | 2019-08-01 | 2019-09-01 | 50 | NA | NA |
| 31 | 11 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 50 | NA | NA |
| 31 | 11 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 50 | NA | NA |
| 31 | 11 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 50 | NA | NA |
| 31 | 11 | 2019-11-01 | 2019-12-01 | 2020-01-01 | 50 | NA | NA |
| 31 | 11 | 2019-12-01 | 2020-01-01 | NA | 50 | 0 | churn |
| 32 | 12 | NA | 2018-06-01 | 2018-07-01 | NA | 25 | new |
| 33 | 12 | 2018-07-01 | 2018-07-01 | 2018-08-01 | 25 | 50 | upgrade |
| 33 | 12 | 2018-07-01 | 2018-08-01 | 2018-09-01 | 50 | NA | NA |
| 33 | 12 | 2018-08-01 | 2018-09-01 | 2018-10-01 | 50 | NA | NA |
| 33 | 12 | 2018-09-01 | 2018-10-01 | 2018-11-01 | 50 | NA | NA |
| 33 | 12 | 2018-10-01 | 2018-11-01 | 2018-12-01 | 50 | NA | NA |
| 33 | 12 | 2018-11-01 | 2018-12-01 | 2019-01-01 | 50 | NA | NA |
| 33 | 12 | 2018-12-01 | 2019-01-01 | 2019-02-01 | 50 | NA | NA |
| 33 | 12 | 2019-01-01 | 2019-02-01 | 2019-03-01 | 50 | NA | NA |
| 33 | 12 | 2019-02-01 | 2019-03-01 | 2019-04-01 | 50 | NA | NA |
| 33 | 12 | 2019-03-01 | 2019-04-01 | 2019-05-01 | 50 | NA | NA |
| 33 | 12 | 2019-04-01 | 2019-05-01 | 2019-06-01 | 50 | NA | NA |
| 33 | 12 | 2019-05-01 | 2019-06-01 | 2019-07-01 | 50 | NA | NA |
| 33 | 12 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 50 | NA | NA |
| 33 | 12 | 2019-07-01 | 2019-08-01 | 2019-09-01 | 50 | NA | NA |
| 33 | 12 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 50 | NA | NA |
| 33 | 12 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 50 | NA | NA |
| 33 | 12 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 50 | NA | NA |
| 33 | 12 | 2019-11-01 | 2019-12-01 | 2020-01-01 | 50 | NA | NA |
| 33 | 12 | 2019-12-01 | 2020-01-01 | NA | 50 | 0 | churn |
| 34 | 13 | NA | 2019-08-01 | 2019-09-01 | NA | 25 | new |
| 35 | 13 | 2019-09-01 | 2019-09-01 | 2019-10-01 | 25 | 50 | upgrade |
| 35 | 13 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 50 | NA | NA |
| 35 | 13 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 50 | NA | NA |
| 35 | 13 | 2019-11-01 | 2019-12-01 | 2020-01-01 | 50 | NA | NA |
| 35 | 13 | 2019-12-01 | 2020-01-01 | NA | 50 | 0 | churn |
| 36 | 14 | NA | 2019-03-01 | 2019-04-01 | NA | 25 | new |
| 37 | 14 | 2019-04-01 | 2019-04-01 | 2019-05-01 | 25 | 50 | upgrade |
| 38 | 14 | 2019-05-01 | 2019-05-01 | 2019-06-01 | 50 | 25 | downgrade |
| 39 | 14 | 2019-06-01 | 2019-06-01 | 2019-07-01 | 25 | 50 | upgrade |
| 40 | 14 | 2019-07-01 | 2019-07-01 | 2019-08-01 | 50 | 25 | downgrade |
| 40 | 14 | 2019-07-01 | 2019-08-01 | 2019-08-01 | 25 | NA | NA |
| 42 | 14 | 2019-09-01 | 2019-09-01 | 2019-10-01 | 25 | 50 | upgrade |
| 43 | 14 | 2019-10-01 | 2019-10-01 | 2019-11-01 | 50 | 25 | downgrade |
| 43 | 14 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 25 | NA | NA |
| 43 | 14 | 2019-11-01 | 2019-12-01 | 2020-01-01 | 25 | NA | NA |
| 43 | 14 | 2019-12-01 | 2020-01-01 | NA | 25 | 0 | churn |
| 44 | 15 | NA | 2019-02-01 | 2019-03-01 | NA | 30 | new |
| 44 | 15 | 2019-02-01 | 2019-03-01 | 2019-03-01 | 30 | NA | NA |
| 45 | 15 | 2019-03-01 | 2019-04-01 | 2019-05-01 | 30 | NA | NA |
| 45 | 15 | 2019-04-01 | 2019-05-01 | 2019-06-01 | 30 | NA | NA |
| 45 | 15 | 2019-05-01 | 2019-06-01 | 2019-07-01 | 30 | NA | NA |
| 45 | 15 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 30 | NA | NA |
| 45 | 15 | 2019-07-01 | 2019-08-01 | 2019-09-01 | 30 | NA | NA |
| 45 | 15 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 30 | NA | NA |
| 45 | 15 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 30 | NA | NA |
| 45 | 15 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 30 | NA | NA |
| 45 | 15 | 2019-11-01 | 2019-12-01 | 2020-01-01 | 30 | NA | NA |
| 45 | 15 | 2019-12-01 | 2020-01-01 | NA | 30 | 0 | churn |
| 46 | 16 | NA | 2019-07-01 | 2019-08-01 | NA | 25 | new |
| 46 | 16 | 2019-07-01 | 2019-08-01 | 2019-08-01 | 25 | NA | NA |
| 47 | 16 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 25 | NA | NA |
| 47 | 16 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 25 | NA | NA |
| 47 | 16 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 25 | NA | NA |
| 47 | 16 | 2019-11-01 | 2019-12-01 | 2020-01-01 | 25 | NA | NA |
| 47 | 16 | 2019-12-01 | 2020-01-01 | NA | 25 | 0 | churn |
| 48 | 17 | NA | 2018-11-01 | 2018-12-01 | NA | 25 | new |
| 49 | 17 | 2018-12-01 | 2018-12-01 | 2019-01-01 | 25 | 50 | upgrade |
| 49 | 17 | 2018-12-01 | 2019-01-01 | 2019-02-01 | 50 | NA | NA |
| 49 | 17 | 2019-01-01 | 2019-02-01 | 2019-03-01 | 50 | NA | NA |
| 49 | 17 | 2019-02-01 | 2019-03-01 | 2019-04-01 | 50 | NA | NA |
| 49 | 17 | 2019-03-01 | 2019-04-01 | 2019-05-01 | 50 | NA | NA |
| 50 | 17 | 2019-05-01 | 2019-05-01 | 2019-06-01 | 50 | 25 | downgrade |
| 51 | 17 | 2019-06-01 | 2019-06-01 | 2019-07-01 | 25 | 40 | upgrade |
| 51 | 17 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 40 | NA | NA |
| 51 | 17 | 2019-07-01 | 2019-08-01 | 2019-09-01 | 40 | NA | NA |
| 52 | 17 | 2019-09-01 | 2019-09-01 | 2019-10-01 | 40 | 35 | downgrade |
| 53 | 17 | 2019-10-01 | 2019-10-01 | 2019-11-01 | 35 | 65 | upgrade |
| 54 | 17 | 2019-11-01 | 2019-11-01 | 2019-12-01 | 65 | 100 | upgrade |
| 55 | 17 | 2019-12-01 | 2019-12-01 | 2020-01-01 | 100 | 95 | downgrade |
| 55 | 17 | 2019-12-01 | 2020-01-01 | NA | 95 | 0 | churn |
| 56 | 18 | NA | 2018-11-01 | 2018-12-01 | NA | 90 | new |
| 57 | 18 | 2018-12-01 | 2018-12-01 | 2019-01-01 | 90 | 50 | downgrade |
| 57 | 18 | 2018-12-01 | 2019-01-01 | 2019-02-01 | 50 | NA | NA |
| 57 | 18 | 2019-01-01 | 2019-02-01 | 2019-03-01 | 50 | NA | NA |
| 57 | 18 | 2019-02-01 | 2019-03-01 | 2019-04-01 | 50 | NA | NA |
| 57 | 18 | 2019-03-01 | 2019-04-01 | 2019-05-01 | 50 | NA | NA |
| 57 | 18 | 2019-04-01 | 2019-05-01 | 2019-06-01 | 50 | NA | NA |
| 57 | 18 | 2019-05-01 | 2019-06-01 | 2019-07-01 | 50 | NA | NA |
| 57 | 18 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 50 | NA | NA |
| 57 | 18 | 2019-07-01 | 2019-08-01 | 2019-09-01 | 50 | NA | NA |
| 57 | 18 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 50 | NA | NA |
| 57 | 18 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 50 | NA | NA |
| 57 | 18 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 50 | NA | NA |
| 57 | 18 | 2019-11-01 | 2019-12-01 | NA | 50 | 0 | churn |
| 58 | 19 | NA | 2019-05-01 | 2019-06-01 | NA | 25 | new |
| 59 | 19 | 2019-06-01 | 2019-06-01 | 2019-07-01 | 25 | 50 | upgrade |
| 59 | 19 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 50 | NA | NA |
| 59 | 19 | 2019-07-01 | 2019-08-01 | 2019-09-01 | 50 | NA | NA |
| 59 | 19 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 50 | NA | NA |
| 59 | 19 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 50 | NA | NA |
| 59 | 19 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 50 | NA | NA |
| 59 | 19 | 2019-11-01 | 2019-12-01 | 2020-01-01 | 50 | NA | NA |
| 59 | 19 | 2019-12-01 | 2020-01-01 | NA | 50 | 0 | churn |
| 60 | 20 | NA | 2019-01-01 | 2019-02-01 | NA | 25 | new |
| 61 | 20 | 2019-02-01 | 2019-02-01 | 2019-03-01 | 25 | 50 | upgrade |
| 61 | 20 | 2019-02-01 | 2019-03-01 | 2019-04-01 | 50 | NA | NA |
| 61 | 20 | 2019-03-01 | 2019-04-01 | 2019-05-01 | 50 | NA | NA |
| 61 | 20 | 2019-04-01 | 2019-05-01 | 2019-06-01 | 50 | NA | NA |
| 61 | 20 | 2019-05-01 | 2019-06-01 | 2019-07-01 | 50 | NA | NA |
| 61 | 20 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 50 | NA | NA |
| 61 | 20 | 2019-07-01 | 2019-08-01 | 2019-09-01 | 50 | NA | NA |
| 61 | 20 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 50 | NA | NA |
| 61 | 20 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 50 | NA | NA |
| 61 | 20 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 50 | NA | NA |
| 61 | 20 | 2019-11-01 | 2019-12-01 | NA | 50 | 0 | churn |
| 62 | 21 | NA | 2018-11-01 | 2018-12-01 | NA | 25 | new |
| 63 | 21 | 2018-12-01 | 2018-12-01 | 2019-01-01 | 25 | 50 | upgrade |
| 63 | 21 | 2018-12-01 | 2019-01-01 | 2019-02-01 | 50 | NA | NA |
| 63 | 21 | 2019-01-01 | 2019-02-01 | 2019-03-01 | 50 | NA | NA |
| 63 | 21 | 2019-02-01 | 2019-03-01 | 2019-04-01 | 50 | NA | NA |
| 63 | 21 | 2019-03-01 | 2019-04-01 | 2019-05-01 | 50 | NA | NA |
| 63 | 21 | 2019-04-01 | 2019-05-01 | 2019-06-01 | 50 | NA | NA |
| 63 | 21 | 2019-05-01 | 2019-06-01 | 2019-07-01 | 50 | NA | NA |
| 63 | 21 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 50 | NA | NA |
| 63 | 21 | 2019-07-01 | 2019-08-01 | 2019-09-01 | 50 | NA | NA |
| 63 | 21 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 50 | NA | NA |
| 63 | 21 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 50 | NA | NA |
| 63 | 21 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 50 | NA | NA |
| 63 | 21 | 2019-11-01 | 2019-12-01 | NA | 50 | 0 | churn |
| 64 | 22 | NA | 2018-12-01 | 2019-01-01 | NA | 25 | new |
| 65 | 22 | 2019-01-01 | 2019-01-01 | 2019-02-01 | 25 | 35 | upgrade |
| 65 | 22 | 2019-01-01 | 2019-02-01 | 2019-03-01 | 35 | NA | NA |
| 65 | 22 | 2019-02-01 | 2019-03-01 | 2019-04-01 | 35 | NA | NA |
| 65 | 22 | 2019-03-01 | 2019-04-01 | 2019-05-01 | 35 | NA | NA |
| 65 | 22 | 2019-04-01 | 2019-05-01 | 2019-06-01 | 35 | NA | NA |
| 65 | 22 | 2019-05-01 | 2019-06-01 | 2019-07-01 | 35 | NA | NA |
| 65 | 22 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 35 | NA | NA |
| 65 | 22 | 2019-07-01 | 2019-08-01 | NA | 35 | 0 | churn |
| 66 | 23 | NA | 2019-03-01 | 2019-04-01 | NA | 35 | new |
| 67 | 23 | 2019-04-01 | 2019-04-01 | 2019-05-01 | 35 | 50 | upgrade |
| 67 | 23 | 2019-04-01 | 2019-05-01 | 2019-06-01 | 50 | NA | NA |
| 67 | 23 | 2019-05-01 | 2019-06-01 | 2019-07-01 | 50 | NA | NA |
| 67 | 23 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 50 | NA | NA |
| 67 | 23 | 2019-07-01 | 2019-08-01 | 2019-09-01 | 50 | NA | NA |
| 67 | 23 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 50 | NA | NA |
| 67 | 23 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 50 | NA | NA |
| 67 | 23 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 50 | NA | NA |
| 67 | 23 | 2019-11-01 | 2019-12-01 | 2020-01-01 | 50 | NA | NA |
| 67 | 23 | 2019-12-01 | 2020-01-01 | NA | 50 | 0 | churn |
| 68 | 24 | NA | 2019-05-01 | 2019-06-01 | NA | 25 | new |
| 69 | 24 | 2019-06-01 | 2019-06-01 | 2019-07-01 | 25 | 60 | upgrade |
| 69 | 24 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 60 | NA | NA |
| 69 | 24 | 2019-07-01 | 2019-08-01 | 2019-09-01 | 60 | NA | NA |
| 69 | 24 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 60 | NA | NA |
| 69 | 24 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 60 | NA | NA |
| 69 | 24 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 60 | NA | NA |
| 69 | 24 | 2019-11-01 | 2019-12-01 | 2020-01-01 | 60 | NA | NA |
| 69 | 24 | 2019-12-01 | 2020-01-01 | NA | 60 | 0 | churn |
| 70 | 25 | NA | 2019-08-01 | 2019-09-01 | NA | 30 | new |
| 71 | 25 | 2019-09-01 | 2019-09-01 | 2019-10-01 | 30 | 35 | upgrade |
| 72 | 25 | 2019-10-01 | 2019-10-01 | 2019-11-01 | 35 | 45 | upgrade |
| 72 | 25 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 45 | NA | NA |
| 72 | 25 | 2019-11-01 | 2019-12-01 | 2020-01-01 | 45 | NA | NA |
| 72 | 25 | 2019-12-01 | 2020-01-01 | NA | 45 | 0 | churn |
| 73 | 26 | NA | 2019-05-01 | 2019-06-01 | NA | 55 | new |
| 74 | 26 | 2019-06-01 | 2019-06-01 | 2019-07-01 | 55 | 50 | downgrade |
| 74 | 26 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 50 | NA | NA |
| 74 | 26 | 2019-07-01 | 2019-08-01 | NA | 50 | 0 | churn |
| 75 | 27 | NA | 2019-07-01 | 2019-08-01 | NA | 130 | new |
| 76 | 27 | 2019-08-01 | 2019-08-01 | 2019-09-01 | 130 | 125 | downgrade |
| 76 | 27 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 125 | NA | NA |
| 76 | 27 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 125 | NA | NA |
| 76 | 27 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 125 | NA | NA |
| 76 | 27 | 2019-11-01 | 2019-12-01 | 2020-01-01 | 125 | NA | NA |
| 76 | 27 | 2019-12-01 | 2020-01-01 | NA | 125 | 0 | churn |
| 77 | 28 | NA | 2019-07-01 | 2019-08-01 | NA | 50 | new |
| 78 | 28 | 2019-08-01 | 2019-08-01 | 2019-09-01 | 50 | 25 | downgrade |
| 78 | 28 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 25 | NA | NA |
| 78 | 28 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 25 | NA | NA |
| 78 | 28 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 25 | NA | NA |
| 78 | 28 | 2019-11-01 | 2019-12-01 | NA | 25 | 0 | churn |
| 79 | 29 | NA | 2019-06-01 | 2019-07-01 | NA | 50 | new |
| 80 | 29 | 2019-07-01 | 2019-07-01 | 2019-08-01 | 50 | 35 | downgrade |
| 80 | 29 | 2019-07-01 | 2019-08-01 | 2019-09-01 | 35 | NA | NA |
| 80 | 29 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 35 | NA | NA |
| 80 | 29 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 35 | NA | NA |
| 80 | 29 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 35 | NA | NA |
| 80 | 29 | 2019-11-01 | 2019-12-01 | NA | 35 | 0 | churn |
| 81 | 30 | NA | 2019-04-01 | 2019-05-01 | NA | 55 | new |
| 82 | 30 | 2019-05-01 | 2019-05-01 | 2019-06-01 | 55 | 45 | downgrade |
| 82 | 30 | 2019-05-01 | 2019-06-01 | 2019-07-01 | 45 | NA | NA |
| 82 | 30 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 45 | NA | NA |
| 82 | 30 | 2019-07-01 | 2019-08-01 | 2019-09-01 | 45 | NA | NA |
| 82 | 30 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 45 | NA | NA |
| 82 | 30 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 45 | NA | NA |
| 82 | 30 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 45 | NA | NA |
| 82 | 30 | 2019-11-01 | 2019-12-01 | NA | 45 | 0 | churn |
| 83 | 31 | NA | 2019-11-01 | 2019-12-01 | NA | 50 | new |
| 84 | 31 | 2019-12-01 | 2019-12-01 | 2020-01-01 | 50 | 25 | downgrade |
| 84 | 31 | 2019-12-01 | 2020-01-01 | NA | 25 | 0 | churn |
| 85 | 32 | NA | 2019-05-01 | 2019-06-01 | NA | 50 | new |
| 86 | 32 | 2019-06-01 | 2019-06-01 | 2019-07-01 | 50 | 25 | downgrade |
| 86 | 32 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 25 | NA | NA |
| 86 | 32 | 2019-07-01 | 2019-08-01 | 2019-09-01 | 25 | NA | NA |
| 86 | 32 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 25 | NA | NA |
| 86 | 32 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 25 | NA | NA |
| 86 | 32 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 25 | NA | NA |
| 86 | 32 | 2019-11-01 | 2019-12-01 | 2020-01-01 | 25 | NA | NA |
| 86 | 32 | 2019-12-01 | 2020-01-01 | NA | 25 | 0 | churn |
| 87 | 33 | NA | 2019-10-01 | 2019-11-01 | NA | 50 | new |
| 88 | 33 | 2019-11-01 | 2019-11-01 | 2019-12-01 | 50 | 25 | downgrade |
| 88 | 33 | 2019-11-01 | 2019-12-01 | NA | 25 | 0 | churn |
| 89 | 34 | NA | 2019-08-01 | 2019-09-01 | NA | 50 | new |
| 90 | 34 | 2019-09-01 | 2019-09-01 | 2019-10-01 | 50 | 25 | downgrade |
| 90 | 34 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 25 | NA | NA |
| 90 | 34 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 25 | NA | NA |
| 90 | 34 | 2019-11-01 | 2019-12-01 | 2020-01-01 | 25 | NA | NA |
| 90 | 34 | 2019-12-01 | 2020-01-01 | NA | 25 | 0 | churn |
| 91 | 35 | NA | 2019-10-01 | 2019-11-01 | NA | 50 | new |
| 92 | 35 | 2019-11-01 | 2019-11-01 | 2019-12-01 | 50 | 25 | downgrade |
| 92 | 35 | 2019-11-01 | 2019-12-01 | NA | 25 | 0 | churn |
| 93 | 36 | NA | 2019-09-01 | 2019-10-01 | NA | 50 | new |
| 94 | 36 | 2019-10-01 | 2019-10-01 | 2019-11-01 | 50 | 25 | downgrade |
| 94 | 36 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 25 | NA | NA |
| 94 | 36 | 2019-11-01 | 2019-12-01 | 2020-01-01 | 25 | NA | NA |
| 94 | 36 | 2019-12-01 | 2020-01-01 | NA | 25 | 0 | churn |
| 95 | 37 | NA | 2019-09-01 | 2019-10-01 | NA | 25 | new |
| 95 | 37 | 2019-09-01 | 2019-10-01 | 2019-10-01 | 25 | NA | NA |
| 96 | 37 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 25 | NA | NA |
| 96 | 37 | 2019-11-01 | 2019-12-01 | 2020-01-01 | 25 | NA | NA |
| 96 | 37 | 2019-12-01 | 2020-01-01 | NA | 25 | 0 | churn |
| 97 | 38 | NA | 2019-09-01 | 2019-10-01 | NA | 30 | new |
| 97 | 38 | 2019-09-01 | 2019-10-01 | 2019-10-01 | 30 | NA | NA |
| 98 | 38 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 30 | NA | NA |
| 98 | 38 | 2019-11-01 | 2019-12-01 | NA | 30 | 0 | churn |
| 99 | 39 | NA | 2019-11-01 | 2019-12-01 | NA | 35 | new |
| 99 | 39 | 2019-11-01 | 2019-12-01 | NA | 35 | 0 | churn |
| 100 | 40 | NA | 2019-09-01 | 2019-10-01 | NA | 35 | new |
| 101 | 40 | 2019-10-01 | 2019-10-01 | 2019-11-01 | 35 | 50 | upgrade |
| 101 | 40 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 50 | NA | NA |
| 101 | 40 | 2019-11-01 | 2019-12-01 | 2020-01-01 | 50 | NA | NA |
| 101 | 40 | 2019-12-01 | 2020-01-01 | NA | 50 | 0 | churn |
| 102 | 41 | NA | 2019-11-01 | 2019-12-01 | NA | 25 | new |
| 103 | 41 | 2019-12-01 | 2019-12-01 | 2020-01-01 | 25 | 50 | upgrade |
| 103 | 41 | 2019-12-01 | 2020-01-01 | NA | 50 | 0 | churn |
| 104 | 42 | NA | 2019-09-01 | 2019-10-01 | NA | 25 | new |
| 105 | 42 | 2019-10-01 | 2019-10-01 | 2019-11-01 | 25 | 50 | upgrade |
| 105 | 42 | 2019-10-01 | 2019-11-01 | 2019-12-01 | 50 | NA | NA |
| 105 | 42 | 2019-11-01 | 2019-12-01 | NA | 50 | 0 | churn |
| 106 | 43 | NA | 2019-11-01 | 2019-12-01 | NA | 25 | new |
| 106 | 43 | 2019-11-01 | 2019-12-01 | NA | 25 | 0 | churn |
| 107 | 44 | NA | 2019-10-01 | 2019-11-01 | NA | 50 | new |
| 108 | 44 | 2019-11-01 | 2019-11-01 | 2019-12-01 | 50 | 25 | downgrade |
| 108 | 44 | 2019-11-01 | 2019-12-01 | 2020-01-01 | 25 | NA | NA |
| 108 | 44 | 2019-12-01 | 2020-01-01 | NA | 25 | 0 | churn |
| 109 | 45 | NA | 2019-10-01 | 2019-11-01 | NA | 45 | new |
| 110 | 45 | 2019-11-01 | 2019-11-01 | 2019-12-01 | 45 | 35 | downgrade |
| 110 | 45 | 2019-11-01 | 2019-12-01 | 2020-01-01 | 35 | NA | NA |
| 110 | 45 | 2019-12-01 | 2020-01-01 | NA | 35 | 0 | churn |
| 111 | 46 | NA | 2019-12-01 | 2020-01-01 | NA | 50 | new |
| 111 | 46 | 2019-12-01 | 2020-01-01 | NA | 50 | 0 | churn |
| 112 | 47 | NA | 2019-10-01 | 2019-11-01 | NA | 25 | new |
| 113 | 47 | 2019-11-01 | 2019-11-01 | 2019-12-01 | 25 | 50 | upgrade |
| 113 | 47 | 2019-11-01 | 2019-12-01 | NA | 50 | 0 | churn |
| 114 | 48 | NA | 2019-11-01 | 2019-12-01 | NA | 25 | new |
| 114 | 48 | 2019-11-01 | 2019-12-01 | NA | 25 | 0 | churn |
| 115 | 49 | NA | 2019-11-01 | 2019-12-01 | NA | 50 | new |
| 115 | 49 | 2019-11-01 | 2019-12-01 | NA | 50 | 0 | churn |
| 116 | 50 | NA | 2019-12-01 | 2020-01-01 | NA | 25 | new |
| 116 | 50 | 2019-12-01 | 2020-01-01 | NA | 25 | 0 | churn |
| 117 | 51 | NA | 2020-01-01 | 2020-02-01 | NA | 50 | new |
| 117 | 51 | 2020-01-01 | 2020-02-01 | NA | 50 | 0 | churn |
| 118 | 52 | NA | 2019-12-01 | 2020-01-01 | NA | 25 | new |
| 118 | 52 | 2019-12-01 | 2020-01-01 | NA | 25 | 0 | churn |
| 119 | 53 | NA | 2020-01-01 | 2020-02-01 | NA | 50 | new |
| 119 | 53 | 2020-01-01 | 2020-02-01 | NA | 50 | 0 | churn |
| 120 | 54 | NA | 2020-01-01 | 2020-02-01 | NA | 25 | new |
| 120 | 54 | 2020-01-01 | 2020-02-01 | NA | 25 | 0 | churn |
| 121 | 55 | NA | 2020-01-01 | 2020-02-01 | NA | 50 | new |
| 121 | 55 | 2020-01-01 | 2020-02-01 | NA | 50 | 0 | churn |
Expand Subscription Dates
Create one row per month per customer for an entire customer’s life cycle. The goal is to have dates for all months between a customer’s initial subscription_start_date and final subscription_end_date.
customer_months <- subscription_periods %>%
group_by(customer_id) %>%
expand(
current_subscription_date = seq(
min(start_date),
max(end_date),
by = "month"
)
)
kable(customer_months) %>%
kable_styling(full_width = FALSE) %>%
scroll_box(height = '600px')| customer_id | current_subscription_date |
|---|---|
| 1 | 2018-11-01 |
| 1 | 2018-12-01 |
| 1 | 2019-01-01 |
| 1 | 2019-02-01 |
| 1 | 2019-03-01 |
| 1 | 2019-04-01 |
| 1 | 2019-05-01 |
| 1 | 2019-06-01 |
| 1 | 2019-07-01 |
| 1 | 2019-08-01 |
| 2 | 2017-09-01 |
| 2 | 2017-10-01 |
| 2 | 2017-11-01 |
| 3 | 2017-09-01 |
| 3 | 2017-10-01 |
| 4 | 2017-10-01 |
| 4 | 2017-11-01 |
| 5 | 2018-11-01 |
| 5 | 2018-12-01 |
| 5 | 2019-01-01 |
| 5 | 2019-02-01 |
| 5 | 2019-03-01 |
| 5 | 2019-04-01 |
| 5 | 2019-05-01 |
| 5 | 2019-06-01 |
| 5 | 2019-07-01 |
| 5 | 2019-08-01 |
| 5 | 2019-09-01 |
| 5 | 2019-10-01 |
| 5 | 2019-11-01 |
| 5 | 2019-12-01 |
| 5 | 2020-01-01 |
| 6 | 2018-05-01 |
| 6 | 2018-06-01 |
| 6 | 2018-07-01 |
| 6 | 2018-08-01 |
| 6 | 2018-09-01 |
| 6 | 2018-10-01 |
| 6 | 2018-11-01 |
| 6 | 2018-12-01 |
| 6 | 2019-01-01 |
| 6 | 2019-02-01 |
| 6 | 2019-03-01 |
| 6 | 2019-04-01 |
| 6 | 2019-05-01 |
| 6 | 2019-06-01 |
| 6 | 2019-07-01 |
| 6 | 2019-08-01 |
| 6 | 2019-09-01 |
| 6 | 2019-10-01 |
| 6 | 2019-11-01 |
| 6 | 2019-12-01 |
| 6 | 2020-01-01 |
| 7 | 2018-01-01 |
| 7 | 2018-02-01 |
| 7 | 2018-03-01 |
| 7 | 2018-04-01 |
| 7 | 2018-05-01 |
| 7 | 2018-06-01 |
| 7 | 2018-07-01 |
| 7 | 2018-08-01 |
| 7 | 2018-09-01 |
| 7 | 2018-10-01 |
| 7 | 2018-11-01 |
| 7 | 2018-12-01 |
| 7 | 2019-01-01 |
| 7 | 2019-02-01 |
| 7 | 2019-03-01 |
| 7 | 2019-04-01 |
| 7 | 2019-05-01 |
| 7 | 2019-06-01 |
| 7 | 2019-07-01 |
| 7 | 2019-08-01 |
| 7 | 2019-09-01 |
| 7 | 2019-10-01 |
| 7 | 2019-11-01 |
| 7 | 2019-12-01 |
| 8 | 2019-04-01 |
| 8 | 2019-05-01 |
| 8 | 2019-06-01 |
| 8 | 2019-07-01 |
| 8 | 2019-08-01 |
| 8 | 2019-09-01 |
| 8 | 2019-10-01 |
| 8 | 2019-11-01 |
| 8 | 2019-12-01 |
| 9 | 2018-05-01 |
| 9 | 2018-06-01 |
| 9 | 2018-07-01 |
| 9 | 2018-08-01 |
| 9 | 2018-09-01 |
| 9 | 2018-10-01 |
| 9 | 2018-11-01 |
| 9 | 2018-12-01 |
| 9 | 2019-01-01 |
| 9 | 2019-02-01 |
| 9 | 2019-03-01 |
| 9 | 2019-04-01 |
| 9 | 2019-05-01 |
| 9 | 2019-06-01 |
| 9 | 2019-07-01 |
| 9 | 2019-08-01 |
| 9 | 2019-09-01 |
| 9 | 2019-10-01 |
| 9 | 2019-11-01 |
| 9 | 2019-12-01 |
| 9 | 2020-01-01 |
| 10 | 2018-04-01 |
| 10 | 2018-05-01 |
| 10 | 2018-06-01 |
| 10 | 2018-07-01 |
| 10 | 2018-08-01 |
| 10 | 2018-09-01 |
| 10 | 2018-10-01 |
| 10 | 2018-11-01 |
| 10 | 2018-12-01 |
| 10 | 2019-01-01 |
| 10 | 2019-02-01 |
| 10 | 2019-03-01 |
| 10 | 2019-04-01 |
| 10 | 2019-05-01 |
| 10 | 2019-06-01 |
| 10 | 2019-07-01 |
| 10 | 2019-08-01 |
| 10 | 2019-09-01 |
| 10 | 2019-10-01 |
| 10 | 2019-11-01 |
| 10 | 2019-12-01 |
| 10 | 2020-01-01 |
| 11 | 2018-09-01 |
| 11 | 2018-10-01 |
| 11 | 2018-11-01 |
| 11 | 2018-12-01 |
| 11 | 2019-01-01 |
| 11 | 2019-02-01 |
| 11 | 2019-03-01 |
| 11 | 2019-04-01 |
| 11 | 2019-05-01 |
| 11 | 2019-06-01 |
| 11 | 2019-07-01 |
| 11 | 2019-08-01 |
| 11 | 2019-09-01 |
| 11 | 2019-10-01 |
| 11 | 2019-11-01 |
| 11 | 2019-12-01 |
| 11 | 2020-01-01 |
| 12 | 2018-06-01 |
| 12 | 2018-07-01 |
| 12 | 2018-08-01 |
| 12 | 2018-09-01 |
| 12 | 2018-10-01 |
| 12 | 2018-11-01 |
| 12 | 2018-12-01 |
| 12 | 2019-01-01 |
| 12 | 2019-02-01 |
| 12 | 2019-03-01 |
| 12 | 2019-04-01 |
| 12 | 2019-05-01 |
| 12 | 2019-06-01 |
| 12 | 2019-07-01 |
| 12 | 2019-08-01 |
| 12 | 2019-09-01 |
| 12 | 2019-10-01 |
| 12 | 2019-11-01 |
| 12 | 2019-12-01 |
| 12 | 2020-01-01 |
| 13 | 2019-08-01 |
| 13 | 2019-09-01 |
| 13 | 2019-10-01 |
| 13 | 2019-11-01 |
| 13 | 2019-12-01 |
| 13 | 2020-01-01 |
| 14 | 2019-03-01 |
| 14 | 2019-04-01 |
| 14 | 2019-05-01 |
| 14 | 2019-06-01 |
| 14 | 2019-07-01 |
| 14 | 2019-08-01 |
| 14 | 2019-09-01 |
| 14 | 2019-10-01 |
| 14 | 2019-11-01 |
| 14 | 2019-12-01 |
| 14 | 2020-01-01 |
| 15 | 2019-02-01 |
| 15 | 2019-03-01 |
| 15 | 2019-04-01 |
| 15 | 2019-05-01 |
| 15 | 2019-06-01 |
| 15 | 2019-07-01 |
| 15 | 2019-08-01 |
| 15 | 2019-09-01 |
| 15 | 2019-10-01 |
| 15 | 2019-11-01 |
| 15 | 2019-12-01 |
| 15 | 2020-01-01 |
| 16 | 2019-07-01 |
| 16 | 2019-08-01 |
| 16 | 2019-09-01 |
| 16 | 2019-10-01 |
| 16 | 2019-11-01 |
| 16 | 2019-12-01 |
| 16 | 2020-01-01 |
| 17 | 2018-11-01 |
| 17 | 2018-12-01 |
| 17 | 2019-01-01 |
| 17 | 2019-02-01 |
| 17 | 2019-03-01 |
| 17 | 2019-04-01 |
| 17 | 2019-05-01 |
| 17 | 2019-06-01 |
| 17 | 2019-07-01 |
| 17 | 2019-08-01 |
| 17 | 2019-09-01 |
| 17 | 2019-10-01 |
| 17 | 2019-11-01 |
| 17 | 2019-12-01 |
| 17 | 2020-01-01 |
| 18 | 2018-11-01 |
| 18 | 2018-12-01 |
| 18 | 2019-01-01 |
| 18 | 2019-02-01 |
| 18 | 2019-03-01 |
| 18 | 2019-04-01 |
| 18 | 2019-05-01 |
| 18 | 2019-06-01 |
| 18 | 2019-07-01 |
| 18 | 2019-08-01 |
| 18 | 2019-09-01 |
| 18 | 2019-10-01 |
| 18 | 2019-11-01 |
| 18 | 2019-12-01 |
| 19 | 2019-05-01 |
| 19 | 2019-06-01 |
| 19 | 2019-07-01 |
| 19 | 2019-08-01 |
| 19 | 2019-09-01 |
| 19 | 2019-10-01 |
| 19 | 2019-11-01 |
| 19 | 2019-12-01 |
| 19 | 2020-01-01 |
| 20 | 2019-01-01 |
| 20 | 2019-02-01 |
| 20 | 2019-03-01 |
| 20 | 2019-04-01 |
| 20 | 2019-05-01 |
| 20 | 2019-06-01 |
| 20 | 2019-07-01 |
| 20 | 2019-08-01 |
| 20 | 2019-09-01 |
| 20 | 2019-10-01 |
| 20 | 2019-11-01 |
| 20 | 2019-12-01 |
| 21 | 2018-11-01 |
| 21 | 2018-12-01 |
| 21 | 2019-01-01 |
| 21 | 2019-02-01 |
| 21 | 2019-03-01 |
| 21 | 2019-04-01 |
| 21 | 2019-05-01 |
| 21 | 2019-06-01 |
| 21 | 2019-07-01 |
| 21 | 2019-08-01 |
| 21 | 2019-09-01 |
| 21 | 2019-10-01 |
| 21 | 2019-11-01 |
| 21 | 2019-12-01 |
| 22 | 2018-12-01 |
| 22 | 2019-01-01 |
| 22 | 2019-02-01 |
| 22 | 2019-03-01 |
| 22 | 2019-04-01 |
| 22 | 2019-05-01 |
| 22 | 2019-06-01 |
| 22 | 2019-07-01 |
| 22 | 2019-08-01 |
| 23 | 2019-03-01 |
| 23 | 2019-04-01 |
| 23 | 2019-05-01 |
| 23 | 2019-06-01 |
| 23 | 2019-07-01 |
| 23 | 2019-08-01 |
| 23 | 2019-09-01 |
| 23 | 2019-10-01 |
| 23 | 2019-11-01 |
| 23 | 2019-12-01 |
| 23 | 2020-01-01 |
| 24 | 2019-05-01 |
| 24 | 2019-06-01 |
| 24 | 2019-07-01 |
| 24 | 2019-08-01 |
| 24 | 2019-09-01 |
| 24 | 2019-10-01 |
| 24 | 2019-11-01 |
| 24 | 2019-12-01 |
| 24 | 2020-01-01 |
| 25 | 2019-08-01 |
| 25 | 2019-09-01 |
| 25 | 2019-10-01 |
| 25 | 2019-11-01 |
| 25 | 2019-12-01 |
| 25 | 2020-01-01 |
| 26 | 2019-05-01 |
| 26 | 2019-06-01 |
| 26 | 2019-07-01 |
| 26 | 2019-08-01 |
| 27 | 2019-07-01 |
| 27 | 2019-08-01 |
| 27 | 2019-09-01 |
| 27 | 2019-10-01 |
| 27 | 2019-11-01 |
| 27 | 2019-12-01 |
| 27 | 2020-01-01 |
| 28 | 2019-07-01 |
| 28 | 2019-08-01 |
| 28 | 2019-09-01 |
| 28 | 2019-10-01 |
| 28 | 2019-11-01 |
| 28 | 2019-12-01 |
| 29 | 2019-06-01 |
| 29 | 2019-07-01 |
| 29 | 2019-08-01 |
| 29 | 2019-09-01 |
| 29 | 2019-10-01 |
| 29 | 2019-11-01 |
| 29 | 2019-12-01 |
| 30 | 2019-04-01 |
| 30 | 2019-05-01 |
| 30 | 2019-06-01 |
| 30 | 2019-07-01 |
| 30 | 2019-08-01 |
| 30 | 2019-09-01 |
| 30 | 2019-10-01 |
| 30 | 2019-11-01 |
| 30 | 2019-12-01 |
| 31 | 2019-11-01 |
| 31 | 2019-12-01 |
| 31 | 2020-01-01 |
| 32 | 2019-05-01 |
| 32 | 2019-06-01 |
| 32 | 2019-07-01 |
| 32 | 2019-08-01 |
| 32 | 2019-09-01 |
| 32 | 2019-10-01 |
| 32 | 2019-11-01 |
| 32 | 2019-12-01 |
| 32 | 2020-01-01 |
| 33 | 2019-10-01 |
| 33 | 2019-11-01 |
| 33 | 2019-12-01 |
| 34 | 2019-08-01 |
| 34 | 2019-09-01 |
| 34 | 2019-10-01 |
| 34 | 2019-11-01 |
| 34 | 2019-12-01 |
| 34 | 2020-01-01 |
| 35 | 2019-10-01 |
| 35 | 2019-11-01 |
| 35 | 2019-12-01 |
| 36 | 2019-09-01 |
| 36 | 2019-10-01 |
| 36 | 2019-11-01 |
| 36 | 2019-12-01 |
| 36 | 2020-01-01 |
| 37 | 2019-09-01 |
| 37 | 2019-10-01 |
| 37 | 2019-11-01 |
| 37 | 2019-12-01 |
| 37 | 2020-01-01 |
| 38 | 2019-09-01 |
| 38 | 2019-10-01 |
| 38 | 2019-11-01 |
| 38 | 2019-12-01 |
| 39 | 2019-11-01 |
| 39 | 2019-12-01 |
| 40 | 2019-09-01 |
| 40 | 2019-10-01 |
| 40 | 2019-11-01 |
| 40 | 2019-12-01 |
| 40 | 2020-01-01 |
| 41 | 2019-11-01 |
| 41 | 2019-12-01 |
| 41 | 2020-01-01 |
| 42 | 2019-09-01 |
| 42 | 2019-10-01 |
| 42 | 2019-11-01 |
| 42 | 2019-12-01 |
| 43 | 2019-11-01 |
| 43 | 2019-12-01 |
| 44 | 2019-10-01 |
| 44 | 2019-11-01 |
| 44 | 2019-12-01 |
| 44 | 2020-01-01 |
| 45 | 2019-10-01 |
| 45 | 2019-11-01 |
| 45 | 2019-12-01 |
| 45 | 2020-01-01 |
| 46 | 2019-12-01 |
| 46 | 2020-01-01 |
| 47 | 2019-10-01 |
| 47 | 2019-11-01 |
| 47 | 2019-12-01 |
| 48 | 2019-11-01 |
| 48 | 2019-12-01 |
| 49 | 2019-11-01 |
| 49 | 2019-12-01 |
| 50 | 2019-12-01 |
| 50 | 2020-01-01 |
| 51 | 2020-01-01 |
| 51 | 2020-02-01 |
| 52 | 2019-12-01 |
| 52 | 2020-01-01 |
| 53 | 2020-01-01 |
| 53 | 2020-02-01 |
| 54 | 2020-01-01 |
| 54 | 2020-02-01 |
| 55 | 2020-01-01 |
| 55 | 2020-02-01 |
Notice that for customer_id 1 a current_subscription_date of 2019-03-01 was created. This date was not in subscription_periods data set as the customer did not have an active subscription for this month. Thus, there is now one row for each month within an entire customer’s life cycle.
Create MRR Dataset
Left join transformed_subscription_periods data set to customer_months data set on customer_id and current_subscription_date. We can then fill NAs in current_monthly_amount with the last recorded value of that column. This ensures that there’s always a recorded revenue for a given month. Finally, calculate mrr_change by subtracting the previous monthly amount from the current monthly amount.
monthly_recurring_revenue <- customer_months %>%
left_join(transformed_subscription_periods, by = c("customer_id", "current_subscription_date")) %>%
group_by(customer_id) %>%
fill(current_monthly_amount, .direction = "down") %>%
mutate(mrr_change = coalesce(current_monthly_amount - lag(current_monthly_amount), current_monthly_amount)) %>%
ungroup() %>%
select(date_month = current_subscription_date,
customer_id,
mrr = current_monthly_amount,
mrr_change,
change_category)
kable(monthly_recurring_revenue) %>%
kable_styling(full_width = FALSE) %>%
scroll_box(height = '600px')| date_month | customer_id | mrr | mrr_change | change_category |
|---|---|---|---|---|
| 2018-11-01 | 1 | 50 | 50 | new |
| 2018-12-01 | 1 | 50 | 0 | |
| 2019-01-01 | 1 | 50 | 0 | |
| 2019-02-01 | 1 | 0 | -50 | churn |
| 2019-03-01 | 1 | 0 | 0 | |
| 2019-04-01 | 1 | 50 | 50 | reactivation |
| 2019-05-01 | 1 | 50 | 0 | |
| 2019-06-01 | 1 | 75 | 25 | upgrade |
| 2019-07-01 | 1 | 75 | 0 | |
| 2019-08-01 | 1 | 0 | -75 | churn |
| 2017-09-01 | 2 | 25 | 25 | new |
| 2017-10-01 | 2 | 25 | 0 | |
| 2017-11-01 | 2 | 0 | -25 | churn |
| 2017-09-01 | 3 | 50 | 50 | new |
| 2017-10-01 | 3 | 0 | -50 | churn |
| 2017-10-01 | 4 | 25 | 25 | new |
| 2017-11-01 | 4 | 0 | -25 | churn |
| 2018-11-01 | 5 | 50 | 50 | new |
| 2018-12-01 | 5 | 25 | -25 | downgrade |
| 2019-01-01 | 5 | 25 | 0 | |
| 2019-02-01 | 5 | 25 | 0 | |
| 2019-03-01 | 5 | 0 | -25 | churn |
| 2019-04-01 | 5 | 0 | 0 | |
| 2019-05-01 | 5 | 0 | 0 | |
| 2019-06-01 | 5 | 0 | 0 | |
| 2019-07-01 | 5 | 50 | 50 | reactivation |
| 2019-08-01 | 5 | 25 | -25 | downgrade |
| 2019-09-01 | 5 | 50 | 25 | upgrade |
| 2019-10-01 | 5 | 25 | -25 | downgrade |
| 2019-11-01 | 5 | 25 | 0 | |
| 2019-12-01 | 5 | 40 | 15 | upgrade |
| 2020-01-01 | 5 | 0 | -40 | churn |
| 2018-05-01 | 6 | 35 | 35 | new |
| 2018-06-01 | 6 | 65 | 30 | upgrade |
| 2018-07-01 | 6 | 65 | 0 | |
| 2018-08-01 | 6 | 65 | 0 | |
| 2018-09-01 | 6 | 65 | 0 | |
| 2018-10-01 | 6 | 65 | 0 | |
| 2018-11-01 | 6 | 65 | 0 | |
| 2018-12-01 | 6 | 65 | 0 | |
| 2019-01-01 | 6 | 65 | 0 | |
| 2019-02-01 | 6 | 65 | 0 | |
| 2019-03-01 | 6 | 65 | 0 | |
| 2019-04-01 | 6 | 65 | 0 | |
| 2019-05-01 | 6 | 65 | 0 | |
| 2019-06-01 | 6 | 65 | 0 | |
| 2019-07-01 | 6 | 65 | 0 | |
| 2019-08-01 | 6 | 65 | 0 | |
| 2019-09-01 | 6 | 65 | 0 | |
| 2019-10-01 | 6 | 65 | 0 | |
| 2019-11-01 | 6 | 65 | 0 | |
| 2019-12-01 | 6 | 65 | 0 | |
| 2020-01-01 | 6 | 0 | -65 | churn |
| 2018-01-01 | 7 | 55 | 55 | new |
| 2018-02-01 | 7 | 70 | 15 | upgrade |
| 2018-03-01 | 7 | 70 | 0 | |
| 2018-04-01 | 7 | 70 | 0 | |
| 2018-05-01 | 7 | 70 | 0 | |
| 2018-06-01 | 7 | 70 | 0 | |
| 2018-07-01 | 7 | 70 | 0 | |
| 2018-08-01 | 7 | 70 | 0 | |
| 2018-09-01 | 7 | 70 | 0 | |
| 2018-10-01 | 7 | 70 | 0 | |
| 2018-11-01 | 7 | 70 | 0 | |
| 2018-12-01 | 7 | 70 | 0 | |
| 2019-01-01 | 7 | 70 | 0 | |
| 2019-02-01 | 7 | 70 | 0 | |
| 2019-03-01 | 7 | 70 | 0 | |
| 2019-04-01 | 7 | 70 | 0 | |
| 2019-05-01 | 7 | 70 | 0 | |
| 2019-06-01 | 7 | 70 | 0 | |
| 2019-07-01 | 7 | 70 | 0 | |
| 2019-08-01 | 7 | 70 | 0 | |
| 2019-09-01 | 7 | 70 | 0 | |
| 2019-10-01 | 7 | 70 | 0 | |
| 2019-11-01 | 7 | 70 | 0 | |
| 2019-12-01 | 7 | 0 | -70 | churn |
| 2019-04-01 | 8 | 65 | 65 | new |
| 2019-05-01 | 8 | 65 | 0 | |
| 2019-06-01 | 8 | 65 | 0 | |
| 2019-07-01 | 8 | 65 | 0 | |
| 2019-08-01 | 8 | 65 | 0 | |
| 2019-09-01 | 8 | 65 | 0 | |
| 2019-10-01 | 8 | 65 | 0 | |
| 2019-11-01 | 8 | 65 | 0 | |
| 2019-12-01 | 8 | 0 | -65 | churn |
| 2018-05-01 | 9 | 85 | 85 | new |
| 2018-06-01 | 9 | 75 | -10 | downgrade |
| 2018-07-01 | 9 | 75 | 0 | |
| 2018-08-01 | 9 | 75 | 0 | |
| 2018-09-01 | 9 | 75 | 0 | |
| 2018-10-01 | 9 | 75 | 0 | |
| 2018-11-01 | 9 | 75 | 0 | |
| 2018-12-01 | 9 | 75 | 0 | |
| 2019-01-01 | 9 | 75 | 0 | |
| 2019-02-01 | 9 | 75 | 0 | |
| 2019-03-01 | 9 | 75 | 0 | |
| 2019-04-01 | 9 | 75 | 0 | |
| 2019-05-01 | 9 | 75 | 0 | |
| 2019-06-01 | 9 | 75 | 0 | |
| 2019-07-01 | 9 | 75 | 0 | |
| 2019-08-01 | 9 | 75 | 0 | |
| 2019-09-01 | 9 | 75 | 0 | |
| 2019-10-01 | 9 | 75 | 0 | |
| 2019-11-01 | 9 | 75 | 0 | |
| 2019-12-01 | 9 | 75 | 0 | |
| 2020-01-01 | 9 | 0 | -75 | churn |
| 2018-04-01 | 10 | 80 | 80 | new |
| 2018-05-01 | 10 | 0 | -80 | churn |
| 2018-06-01 | 10 | 0 | 0 | |
| 2018-07-01 | 10 | 0 | 0 | |
| 2018-08-01 | 10 | 0 | 0 | |
| 2018-09-01 | 10 | 50 | 50 | reactivation |
| 2018-10-01 | 10 | 25 | -25 | downgrade |
| 2018-11-01 | 10 | 25 | 0 | |
| 2018-12-01 | 10 | 25 | 0 | |
| 2019-01-01 | 10 | 25 | 0 | |
| 2019-02-01 | 10 | 25 | 0 | |
| 2019-03-01 | 10 | 25 | 0 | |
| 2019-04-01 | 10 | 50 | 25 | upgrade |
| 2019-05-01 | 10 | 25 | -25 | downgrade |
| 2019-06-01 | 10 | 50 | 25 | upgrade |
| 2019-07-01 | 10 | 50 | 0 | |
| 2019-08-01 | 10 | 50 | 0 | |
| 2019-09-01 | 10 | 50 | 0 | |
| 2019-10-01 | 10 | 50 | 0 | |
| 2019-11-01 | 10 | 25 | -25 | downgrade |
| 2019-12-01 | 10 | 35 | 10 | upgrade |
| 2020-01-01 | 10 | 0 | -35 | churn |
| 2018-09-01 | 11 | 30 | 30 | new |
| 2018-10-01 | 11 | 50 | 20 | upgrade |
| 2018-11-01 | 11 | 50 | 0 | |
| 2018-12-01 | 11 | 50 | 0 | |
| 2019-01-01 | 11 | 50 | 0 | |
| 2019-02-01 | 11 | 50 | 0 | |
| 2019-03-01 | 11 | 50 | 0 | |
| 2019-04-01 | 11 | 50 | 0 | |
| 2019-05-01 | 11 | 50 | 0 | |
| 2019-06-01 | 11 | 50 | 0 | |
| 2019-07-01 | 11 | 50 | 0 | |
| 2019-08-01 | 11 | 50 | 0 | |
| 2019-09-01 | 11 | 50 | 0 | |
| 2019-10-01 | 11 | 50 | 0 | |
| 2019-11-01 | 11 | 50 | 0 | |
| 2019-12-01 | 11 | 50 | 0 | |
| 2020-01-01 | 11 | 0 | -50 | churn |
| 2018-06-01 | 12 | 25 | 25 | new |
| 2018-07-01 | 12 | 50 | 25 | upgrade |
| 2018-08-01 | 12 | 50 | 0 | |
| 2018-09-01 | 12 | 50 | 0 | |
| 2018-10-01 | 12 | 50 | 0 | |
| 2018-11-01 | 12 | 50 | 0 | |
| 2018-12-01 | 12 | 50 | 0 | |
| 2019-01-01 | 12 | 50 | 0 | |
| 2019-02-01 | 12 | 50 | 0 | |
| 2019-03-01 | 12 | 50 | 0 | |
| 2019-04-01 | 12 | 50 | 0 | |
| 2019-05-01 | 12 | 50 | 0 | |
| 2019-06-01 | 12 | 50 | 0 | |
| 2019-07-01 | 12 | 50 | 0 | |
| 2019-08-01 | 12 | 50 | 0 | |
| 2019-09-01 | 12 | 50 | 0 | |
| 2019-10-01 | 12 | 50 | 0 | |
| 2019-11-01 | 12 | 50 | 0 | |
| 2019-12-01 | 12 | 50 | 0 | |
| 2020-01-01 | 12 | 0 | -50 | churn |
| 2019-08-01 | 13 | 25 | 25 | new |
| 2019-09-01 | 13 | 50 | 25 | upgrade |
| 2019-10-01 | 13 | 50 | 0 | |
| 2019-11-01 | 13 | 50 | 0 | |
| 2019-12-01 | 13 | 50 | 0 | |
| 2020-01-01 | 13 | 0 | -50 | churn |
| 2019-03-01 | 14 | 25 | 25 | new |
| 2019-04-01 | 14 | 50 | 25 | upgrade |
| 2019-05-01 | 14 | 25 | -25 | downgrade |
| 2019-06-01 | 14 | 50 | 25 | upgrade |
| 2019-07-01 | 14 | 25 | -25 | downgrade |
| 2019-08-01 | 14 | 25 | 0 | |
| 2019-09-01 | 14 | 50 | 25 | upgrade |
| 2019-10-01 | 14 | 25 | -25 | downgrade |
| 2019-11-01 | 14 | 25 | 0 | |
| 2019-12-01 | 14 | 25 | 0 | |
| 2020-01-01 | 14 | 0 | -25 | churn |
| 2019-02-01 | 15 | 30 | 30 | new |
| 2019-03-01 | 15 | 30 | 0 | |
| 2019-04-01 | 15 | 30 | 0 | |
| 2019-05-01 | 15 | 30 | 0 | |
| 2019-06-01 | 15 | 30 | 0 | |
| 2019-07-01 | 15 | 30 | 0 | |
| 2019-08-01 | 15 | 30 | 0 | |
| 2019-09-01 | 15 | 30 | 0 | |
| 2019-10-01 | 15 | 30 | 0 | |
| 2019-11-01 | 15 | 30 | 0 | |
| 2019-12-01 | 15 | 30 | 0 | |
| 2020-01-01 | 15 | 0 | -30 | churn |
| 2019-07-01 | 16 | 25 | 25 | new |
| 2019-08-01 | 16 | 25 | 0 | |
| 2019-09-01 | 16 | 25 | 0 | |
| 2019-10-01 | 16 | 25 | 0 | |
| 2019-11-01 | 16 | 25 | 0 | |
| 2019-12-01 | 16 | 25 | 0 | |
| 2020-01-01 | 16 | 0 | -25 | churn |
| 2018-11-01 | 17 | 25 | 25 | new |
| 2018-12-01 | 17 | 50 | 25 | upgrade |
| 2019-01-01 | 17 | 50 | 0 | |
| 2019-02-01 | 17 | 50 | 0 | |
| 2019-03-01 | 17 | 50 | 0 | |
| 2019-04-01 | 17 | 50 | 0 | |
| 2019-05-01 | 17 | 25 | -25 | downgrade |
| 2019-06-01 | 17 | 40 | 15 | upgrade |
| 2019-07-01 | 17 | 40 | 0 | |
| 2019-08-01 | 17 | 40 | 0 | |
| 2019-09-01 | 17 | 35 | -5 | downgrade |
| 2019-10-01 | 17 | 65 | 30 | upgrade |
| 2019-11-01 | 17 | 100 | 35 | upgrade |
| 2019-12-01 | 17 | 95 | -5 | downgrade |
| 2020-01-01 | 17 | 0 | -95 | churn |
| 2018-11-01 | 18 | 90 | 90 | new |
| 2018-12-01 | 18 | 50 | -40 | downgrade |
| 2019-01-01 | 18 | 50 | 0 | |
| 2019-02-01 | 18 | 50 | 0 | |
| 2019-03-01 | 18 | 50 | 0 | |
| 2019-04-01 | 18 | 50 | 0 | |
| 2019-05-01 | 18 | 50 | 0 | |
| 2019-06-01 | 18 | 50 | 0 | |
| 2019-07-01 | 18 | 50 | 0 | |
| 2019-08-01 | 18 | 50 | 0 | |
| 2019-09-01 | 18 | 50 | 0 | |
| 2019-10-01 | 18 | 50 | 0 | |
| 2019-11-01 | 18 | 50 | 0 | |
| 2019-12-01 | 18 | 0 | -50 | churn |
| 2019-05-01 | 19 | 25 | 25 | new |
| 2019-06-01 | 19 | 50 | 25 | upgrade |
| 2019-07-01 | 19 | 50 | 0 | |
| 2019-08-01 | 19 | 50 | 0 | |
| 2019-09-01 | 19 | 50 | 0 | |
| 2019-10-01 | 19 | 50 | 0 | |
| 2019-11-01 | 19 | 50 | 0 | |
| 2019-12-01 | 19 | 50 | 0 | |
| 2020-01-01 | 19 | 0 | -50 | churn |
| 2019-01-01 | 20 | 25 | 25 | new |
| 2019-02-01 | 20 | 50 | 25 | upgrade |
| 2019-03-01 | 20 | 50 | 0 | |
| 2019-04-01 | 20 | 50 | 0 | |
| 2019-05-01 | 20 | 50 | 0 | |
| 2019-06-01 | 20 | 50 | 0 | |
| 2019-07-01 | 20 | 50 | 0 | |
| 2019-08-01 | 20 | 50 | 0 | |
| 2019-09-01 | 20 | 50 | 0 | |
| 2019-10-01 | 20 | 50 | 0 | |
| 2019-11-01 | 20 | 50 | 0 | |
| 2019-12-01 | 20 | 0 | -50 | churn |
| 2018-11-01 | 21 | 25 | 25 | new |
| 2018-12-01 | 21 | 50 | 25 | upgrade |
| 2019-01-01 | 21 | 50 | 0 | |
| 2019-02-01 | 21 | 50 | 0 | |
| 2019-03-01 | 21 | 50 | 0 | |
| 2019-04-01 | 21 | 50 | 0 | |
| 2019-05-01 | 21 | 50 | 0 | |
| 2019-06-01 | 21 | 50 | 0 | |
| 2019-07-01 | 21 | 50 | 0 | |
| 2019-08-01 | 21 | 50 | 0 | |
| 2019-09-01 | 21 | 50 | 0 | |
| 2019-10-01 | 21 | 50 | 0 | |
| 2019-11-01 | 21 | 50 | 0 | |
| 2019-12-01 | 21 | 0 | -50 | churn |
| 2018-12-01 | 22 | 25 | 25 | new |
| 2019-01-01 | 22 | 35 | 10 | upgrade |
| 2019-02-01 | 22 | 35 | 0 | |
| 2019-03-01 | 22 | 35 | 0 | |
| 2019-04-01 | 22 | 35 | 0 | |
| 2019-05-01 | 22 | 35 | 0 | |
| 2019-06-01 | 22 | 35 | 0 | |
| 2019-07-01 | 22 | 35 | 0 | |
| 2019-08-01 | 22 | 0 | -35 | churn |
| 2019-03-01 | 23 | 35 | 35 | new |
| 2019-04-01 | 23 | 50 | 15 | upgrade |
| 2019-05-01 | 23 | 50 | 0 | |
| 2019-06-01 | 23 | 50 | 0 | |
| 2019-07-01 | 23 | 50 | 0 | |
| 2019-08-01 | 23 | 50 | 0 | |
| 2019-09-01 | 23 | 50 | 0 | |
| 2019-10-01 | 23 | 50 | 0 | |
| 2019-11-01 | 23 | 50 | 0 | |
| 2019-12-01 | 23 | 50 | 0 | |
| 2020-01-01 | 23 | 0 | -50 | churn |
| 2019-05-01 | 24 | 25 | 25 | new |
| 2019-06-01 | 24 | 60 | 35 | upgrade |
| 2019-07-01 | 24 | 60 | 0 | |
| 2019-08-01 | 24 | 60 | 0 | |
| 2019-09-01 | 24 | 60 | 0 | |
| 2019-10-01 | 24 | 60 | 0 | |
| 2019-11-01 | 24 | 60 | 0 | |
| 2019-12-01 | 24 | 60 | 0 | |
| 2020-01-01 | 24 | 0 | -60 | churn |
| 2019-08-01 | 25 | 30 | 30 | new |
| 2019-09-01 | 25 | 35 | 5 | upgrade |
| 2019-10-01 | 25 | 45 | 10 | upgrade |
| 2019-11-01 | 25 | 45 | 0 | |
| 2019-12-01 | 25 | 45 | 0 | |
| 2020-01-01 | 25 | 0 | -45 | churn |
| 2019-05-01 | 26 | 55 | 55 | new |
| 2019-06-01 | 26 | 50 | -5 | downgrade |
| 2019-07-01 | 26 | 50 | 0 | |
| 2019-08-01 | 26 | 0 | -50 | churn |
| 2019-07-01 | 27 | 130 | 130 | new |
| 2019-08-01 | 27 | 125 | -5 | downgrade |
| 2019-09-01 | 27 | 125 | 0 | |
| 2019-10-01 | 27 | 125 | 0 | |
| 2019-11-01 | 27 | 125 | 0 | |
| 2019-12-01 | 27 | 125 | 0 | |
| 2020-01-01 | 27 | 0 | -125 | churn |
| 2019-07-01 | 28 | 50 | 50 | new |
| 2019-08-01 | 28 | 25 | -25 | downgrade |
| 2019-09-01 | 28 | 25 | 0 | |
| 2019-10-01 | 28 | 25 | 0 | |
| 2019-11-01 | 28 | 25 | 0 | |
| 2019-12-01 | 28 | 0 | -25 | churn |
| 2019-06-01 | 29 | 50 | 50 | new |
| 2019-07-01 | 29 | 35 | -15 | downgrade |
| 2019-08-01 | 29 | 35 | 0 | |
| 2019-09-01 | 29 | 35 | 0 | |
| 2019-10-01 | 29 | 35 | 0 | |
| 2019-11-01 | 29 | 35 | 0 | |
| 2019-12-01 | 29 | 0 | -35 | churn |
| 2019-04-01 | 30 | 55 | 55 | new |
| 2019-05-01 | 30 | 45 | -10 | downgrade |
| 2019-06-01 | 30 | 45 | 0 | |
| 2019-07-01 | 30 | 45 | 0 | |
| 2019-08-01 | 30 | 45 | 0 | |
| 2019-09-01 | 30 | 45 | 0 | |
| 2019-10-01 | 30 | 45 | 0 | |
| 2019-11-01 | 30 | 45 | 0 | |
| 2019-12-01 | 30 | 0 | -45 | churn |
| 2019-11-01 | 31 | 50 | 50 | new |
| 2019-12-01 | 31 | 25 | -25 | downgrade |
| 2020-01-01 | 31 | 0 | -25 | churn |
| 2019-05-01 | 32 | 50 | 50 | new |
| 2019-06-01 | 32 | 25 | -25 | downgrade |
| 2019-07-01 | 32 | 25 | 0 | |
| 2019-08-01 | 32 | 25 | 0 | |
| 2019-09-01 | 32 | 25 | 0 | |
| 2019-10-01 | 32 | 25 | 0 | |
| 2019-11-01 | 32 | 25 | 0 | |
| 2019-12-01 | 32 | 25 | 0 | |
| 2020-01-01 | 32 | 0 | -25 | churn |
| 2019-10-01 | 33 | 50 | 50 | new |
| 2019-11-01 | 33 | 25 | -25 | downgrade |
| 2019-12-01 | 33 | 0 | -25 | churn |
| 2019-08-01 | 34 | 50 | 50 | new |
| 2019-09-01 | 34 | 25 | -25 | downgrade |
| 2019-10-01 | 34 | 25 | 0 | |
| 2019-11-01 | 34 | 25 | 0 | |
| 2019-12-01 | 34 | 25 | 0 | |
| 2020-01-01 | 34 | 0 | -25 | churn |
| 2019-10-01 | 35 | 50 | 50 | new |
| 2019-11-01 | 35 | 25 | -25 | downgrade |
| 2019-12-01 | 35 | 0 | -25 | churn |
| 2019-09-01 | 36 | 50 | 50 | new |
| 2019-10-01 | 36 | 25 | -25 | downgrade |
| 2019-11-01 | 36 | 25 | 0 | |
| 2019-12-01 | 36 | 25 | 0 | |
| 2020-01-01 | 36 | 0 | -25 | churn |
| 2019-09-01 | 37 | 25 | 25 | new |
| 2019-10-01 | 37 | 25 | 0 | |
| 2019-11-01 | 37 | 25 | 0 | |
| 2019-12-01 | 37 | 25 | 0 | |
| 2020-01-01 | 37 | 0 | -25 | churn |
| 2019-09-01 | 38 | 30 | 30 | new |
| 2019-10-01 | 38 | 30 | 0 | |
| 2019-11-01 | 38 | 30 | 0 | |
| 2019-12-01 | 38 | 0 | -30 | churn |
| 2019-11-01 | 39 | 35 | 35 | new |
| 2019-12-01 | 39 | 0 | -35 | churn |
| 2019-09-01 | 40 | 35 | 35 | new |
| 2019-10-01 | 40 | 50 | 15 | upgrade |
| 2019-11-01 | 40 | 50 | 0 | |
| 2019-12-01 | 40 | 50 | 0 | |
| 2020-01-01 | 40 | 0 | -50 | churn |
| 2019-11-01 | 41 | 25 | 25 | new |
| 2019-12-01 | 41 | 50 | 25 | upgrade |
| 2020-01-01 | 41 | 0 | -50 | churn |
| 2019-09-01 | 42 | 25 | 25 | new |
| 2019-10-01 | 42 | 50 | 25 | upgrade |
| 2019-11-01 | 42 | 50 | 0 | |
| 2019-12-01 | 42 | 0 | -50 | churn |
| 2019-11-01 | 43 | 25 | 25 | new |
| 2019-12-01 | 43 | 0 | -25 | churn |
| 2019-10-01 | 44 | 50 | 50 | new |
| 2019-11-01 | 44 | 25 | -25 | downgrade |
| 2019-12-01 | 44 | 25 | 0 | |
| 2020-01-01 | 44 | 0 | -25 | churn |
| 2019-10-01 | 45 | 45 | 45 | new |
| 2019-11-01 | 45 | 35 | -10 | downgrade |
| 2019-12-01 | 45 | 35 | 0 | |
| 2020-01-01 | 45 | 0 | -35 | churn |
| 2019-12-01 | 46 | 50 | 50 | new |
| 2020-01-01 | 46 | 0 | -50 | churn |
| 2019-10-01 | 47 | 25 | 25 | new |
| 2019-11-01 | 47 | 50 | 25 | upgrade |
| 2019-12-01 | 47 | 0 | -50 | churn |
| 2019-11-01 | 48 | 25 | 25 | new |
| 2019-12-01 | 48 | 0 | -25 | churn |
| 2019-11-01 | 49 | 50 | 50 | new |
| 2019-12-01 | 49 | 0 | -50 | churn |
| 2019-12-01 | 50 | 25 | 25 | new |
| 2020-01-01 | 50 | 0 | -25 | churn |
| 2020-01-01 | 51 | 50 | 50 | new |
| 2020-02-01 | 51 | 0 | -50 | churn |
| 2019-12-01 | 52 | 25 | 25 | new |
| 2020-01-01 | 52 | 0 | -25 | churn |
| 2020-01-01 | 53 | 50 | 50 | new |
| 2020-02-01 | 53 | 0 | -50 | churn |
| 2020-01-01 | 54 | 25 | 25 | new |
| 2020-02-01 | 54 | 0 | -25 | churn |
| 2020-01-01 | 55 | 50 | 50 | new |
| 2020-02-01 | 55 | 0 | -50 | churn |
Data Tests
Let’s run three tests on the monthly_recurring_revenue data set to ensure that there are no issues.
- Test that every
customer_idinsubscription_periodsdata set is inmonthly_recurring_revenuedata set
test_that('all customers are accounted for', {
subscription_periods_customer_id <- subscription_periods %>%
distinct(customer_id)
mrr_customer_id <- monthly_recurring_revenue %>%
distinct(customer_id)
expect_identical(subscription_periods_customer_id, mrr_customer_id)
})## Test passed 🥳- Test that the date range for a customer in
subscription_periodsdata set is replicated inmonthly_recurring_revenuedata set
test_that('all customer subscription dates are accounted for', {
subscription_periods_date_range <- subscription_periods %>%
group_by(customer_id) %>%
summarise(start_date = min(start_date),
end_date = max(end_date))
mrr_date_range <- monthly_recurring_revenue %>%
group_by(customer_id) %>%
summarise(start_date = min(date_month),
end_date = max(date_month))
expect_identical(subscription_periods_date_range, mrr_date_range)
})## Test passed 😸- Test that the combination of
date_monthandcustomer_idis never duplicated
test_that('a customer only has one active subscription', {
number_unique_rows <- monthly_recurring_revenue %>%
distinct(date_month, customer_id) %>%
nrow()
total_number_rows <- nrow(monthly_recurring_revenue)
expect_equal(number_unique_rows, total_number_rows)
})## Test passed 🎉