Calculating Monthly Recurring Revenue (MRR) in Python

What is Monthly Recurring Revenue?

Monthly Recurring Revenue – regular monthly income. This metric is used primarily in subscription models. In this case, the income itself must be reduced to months.

Why is it valuable?

If we have a subscription service, we have regular or periodic payments, then we can understand how much money we will earn and how effective our business is. Further, we can increase MRR by switching customers to a more expensive tariff or try to reduce customer churn.

The problem

For this task, use the new dataset: https://alimbekov.com/wp-content/uploads/2021/03/mrr.csv

Structure:

• customer_id – already familiar customer ID
• first_order – Subscription start date
• EndDate – Subscription end date
• rate – subscription plan (monthly, semi-annual, annual)
• Amount – amount paid
• commission – payment system commission

We will use the following formula to calculate MRR: MRR = new + old + expansion + reactivation – churn – contraction

1. new MRR – the first payment of a new client
2. old MRR – recurring customer payment
3. expansion MRR – increase in MRR due to the new tariff
4. contraction MRR – decrease in MRR due to the new tariff
5. churn MRR — MRR outflow due to termination of payment
6. reactivation MRR – return of a client who had an outflow of MRR

Implementing Monthly Recurring Revenue (MRR) Calculation in Python

import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings("ignore")
import seaborn as sns
import matplotlib
import matplotlib.pyplot as plt
import matplotlib.ticker
import matplotlib as mpl
pd.set_option('max_columns', 50)
mpl.rcParams['lines.linewidth'] = 2
%matplotlib inline
sns.set_context(
     "notebook", 
     font_scale = 1.5,       
     rc = { 
         "figure.figsize" : (30, 30), 
         "axes.titlesize" : 18 
     }
 )
df_mrr = pd.read_csv('mrr.csv')

We import all the necessary libraries and read the file.

Let’s now do some necessary calculations for further work. Let’s bring all the dates to the required format, calculate the subscription duration, extract the month and year from the subscription date, calculate the amount minus the commission (the commission is 5% everywhere) and find out the amount we receive per month.

df_mrr['first_order'] = pd.to_datetime(df_mrr['first_order'])
df_mrr['EndDate'] = pd.to_datetime(df_mrr['EndDate'])
df_mrr['Period'] = (df_mrr.EndDate.dt.to_period('M').astype(int) - df_mrr.first_order.dt.to_period('M').astype(int))
df_mrr['mmr_period'] = pd.to_datetime(df_mrr['first_order']).dt.to_period('M')
df_mrr['price_after_commission'] = df_mrr['Amount']*0.95
df_mrr['Amount_per_month'] = df_mrr['price_after_commission']/df_mrr['Period']
print(df_mrr.head(2))

The next step is to create a list of all unique periods.

periods = df_mrr.mmr_period.sort_values(ascending=True).unique().astype(str).tolist()

We are now ready to calculate MMR. The main idea is to calculate the amount taking into account the end and start dates of the subscription. In this way, we will spread the entire payment over the subscription period.

mrr=[]
for mdate in periods:
         my_df = df_mrr[(df_mrr['EndDate'].dt.to_period('M')>mdate ) 
                      & (df_mrr['first_order'].dt.to_period('M')<= mdate)]
         mmrr = my_df['Amount_per_month'].sum()
         mrr.append({'date':mdate,'mmrr':mmrr})

Let’s visualize what we’ve got.

dates = [x['date'] for x in mrr]
y = [x['mmrr'] for x in mrr]
sns.set()
plt.figure(figsize=(40,10))
plt.title('MRR by month')
plt.ylabel('total')
plt.bar(dates, y)
plt.rc('font',**{'family' : 'normal',
         'weight' : 'bold',
         'size'   : 60})
plt.show()

We see that we have a fairly good product. MMR grew steadily until April, then the growth rate slowed down slightly, and in September we see a fall. Subscriptions began to end, and there were no more new ones.

Calculation check

Let’s do a little visual check of the MMR calculation. Take one customer customer_id = 084a0e74918bd829d509441c2504135e

test_df = df_mrr[df_mrr.customer_id=='084a0e74918bd829d509441c2504135e']
mrr = []
 for mdate in periods:
          my_df = test_df[(test_df['EndDate'].dt.to_period('M')>mdate ) 
                       & (test_df['first_order'].dt.to_period('M')<= mdate)]
          mmrr = my_df['Amount_per_month'].sum()
          mrr.append({'date':mdate,'mmrr':mmrr})
dates = [x['date'] for x in mrr]
y = [x['mmrr'] for x in mrr]
print(mrr)
sns.set()
plt.figure(figsize=(40,10))
plt.title('MRR by month')
plt.ylabel('total')
plt.bar(dates, y)
plt.rc('font',**{'family' : 'normal',
         'weight' : 'bold',
         'size'   : 60})
plt.show()

As we can see, all income from the client is evenly spread over the months.

Conclusion

We dismantled the calculation of MMR. Of course, this example is not the most combative. So in it, for example, there are no examples of changing the tariff for the client.