Data Overview

We’ll use payment data from a subscription-based business. The data can be downloaded from this page.

In this dataset, each row represents a customer’s monthly payment history, with columns containing the following information:

• Customer ID
• Payment Date
• Payment Plan
• Payment Amount

Preparation

We’ll create a table like the one below to calculate the MRR retention rate:

To create this table, we’ll start with payment data that has been augmented with payment count and expansion/contraction information in steps 2-4, and then calculate the MRR retention rate.

The method for calculating payment count can be found here, and the method for calculating expansion and contraction can be found here.

Since we want to aggregate MRR, MRR from existing customers, and expansion, select “Aggregate” from the “Payment Date” column header.

In the aggregation dialog, select “Payment Date” for the group and “Month” for rounding.

MRR (Monthly Recurring Revenue) is the monthly total of payments made by each user, so select “Payment Amount” for the value and SUM for the aggregation function.

Then, change the column name to MRR.

Next, we’ll aggregate MRR from existing customers.

MRR from existing customers is the MRR obtained from customers who have made two or more payments, so we’ll aggregate the sum of payment amounts from customers with two or more payments.

Add “Payment Amount” to the value and select “Conditional Sum (SUM_IF)” for the aggregation function.

A dialog to specify conditions will appear. Enter “Payment Count” for the column, “Greater than or equal to” for the operator, and “2” for the value.

Then set the column name to “MRR from Existing Customers” and apply and execute.

We have now aggregated MRR from existing customers.

Finally, select “Expansion” for the value, choose SUM for the aggregation function, and execute.

We’re now ready to calculate the MRR retention rate.

Calculating MRR Retention Rate

From the “MRR from Existing Customers” column header menu, select “Create Calculation” then “Standard”.

In the calculation creation dialog, enter (MRR from Existing Customers - Expansion) / lag(MRR) in the calculation editor.

Note that the lag function retrieves the value from the previous row of the specified column, so lag(MRR) calculates the value from the previous month, i.e., the previous month’s MRR.

Finally, ensure “Create a new column” is checked, set the column name to “MRR Retention Rate”, select “After the last column” for “Create after this column”, and execute.

We have now calculated the “MRR Retention Rate”.

For Payment Data

If you want to calculate the MRR retention rate using a custom R command with this payment data, please refer to the following R code:

# Step 1: Calculate payment count for each customer
mutate_group(
 # Group by Customer ID
 group_cols = c(`customer_id` = "customer_id"),
 group_funs = c("none"),
 # Sort by payment date
 sort_cols = c("payment_date"),
 sort_funs = c("none"), 
 `payment_count` = dplyr::dense_rank(`payment_date`),  # Rank payment dates to calculate payment count
 `payment_amount_difference` = payment_amount - dplyr::lag(payment_amount)  # Calculate difference from previous payment amount
) %>%

# Step 2: Change payment date to monthly units
# When calculating monthly MRR retention rate, truncate the date to "month"
mutate(`payment_date` = floor_date(`payment_date`, unit = "month")) %>%

# Step 3: Aggregate MRR and MRR from existing customers monthly
group_by(`payment_date`) %>%
summarize(
 MRR = sum(`payment_amount`, na.rm = TRUE),  # Calculate total monthly MRR
 `MRR_from_existing_customers` = sum_if(`payment_amount`, `payment_count` >= 2),  # MRR from customers with 2 or more payments
 `expansion` = sum_if(`payment_amount`, `payment_amount_difference` > 0)  # Increase from additional purchases or upsells
) %>%
ungroup() %>%

# Step 4: Calculate MRR Retention Rate
mutate(
 `MRR_retention_rate` = (`MRR_from_existing_customers` - `expansion`) / lag(MRR)
)

# Step 1: Calculate payment count for each customer
mutate_group(
 # Group by Customer ID
 group_cols = c(`customer_id` = "customer_id"),
 group_funs = c("none"),
 # Sort by payment date
 sort_cols = c("payment_date"),
 sort_funs = c("none"), 
 `payment_count` = dplyr::dense_rank(`payment_date`),  # Rank payment dates to calculate payment count
 `payment_amount_difference` = payment_amount - dplyr::lag(payment_amount)  # Calculate difference from previous payment amount
) %>%

# Step 2: Change payment date to monthly units
# When calculating monthly MRR retention rate, truncate the date to "month"
mutate(`payment_date` = floor_date(`payment_date`, unit = "month")) %>%

# Step 3: Aggregate MRR and MRR from existing customers monthly
group_by(`payment_date`, `segment`) %>%
summarize(
 MRR = sum(`payment_amount`, na.rm = TRUE),  # Calculate total monthly MRR
 `MRR_from_existing_customers` = sum_if(`payment_amount`, `payment_count` >= 2),  # MRR from customers with 2 or more payments
 `expansion` = sum_if(`payment_amount`, `payment_amount_difference` > 0)  # Increase from additional purchases or upsells
) %>% ungroup() %>%

# Step 4: Calculate MRR Retention Rate by Segment
group_by(`segment`) %>%
mutate(
 `MRR_retention_rate` = (`MRR_from_existing_customers` - `expansion`) / lag(MRR)
)