Stay up to date to the latest news
on Data Activation
By Brian Laleye · June 7, 2022 · 11 min read
Net monthly recurring revenue is the amount of revenue you would receive if you were to stop all sales activities.
In other words, if you were to shut down your business, this is how much money you would still be making on an ongoing basis.
It’s important because it allows businesses to better understand the potential earnings of their company and how quickly it can grow.
It also provides a benchmark for comparing different companies within similar industries.
It’s calculated by taking your total monthly recurring revenue and subtracting any refunds, chargebacks, or cancellations.
For example, let’s say your company has a $10 subscription plan that generates $1 in revenue per user each month. You have 100 users, so your total monthly recurring revenue is $100. But if five of those users cancel their subscriptions or get refunds during the month, then your net MRR is $95 ($100 – $5).
Net MRR and LTV
The other important metric for SaaS companies is lifetime value (LTV), which measures the total amount of revenue a customer will generate over their lifetime with your product. For example, if one customer pays you $10 per month for two years, then their LTV would be $240 ($120 × 2). The longer you retain customers on average, the higher their LTV will be and therefore the more valuable they are to you as an acquisition channel.
This is why we’re going today to calculate your Net MRR per user overtime of subscription when you don’t have an adequate solution to do this. You’ll learn how to compute it from your database and automate it to a Google spreadsheet.
Let’s take a look at the billing table within a MongoDB for this SaaS business:
As we can see, we have a sample of 3 clients, with the following criteria:
Let’s connect to the source to get billing data and start our analysis:
Click on Step #1 – Configure connectors
Then, click on your data source (Input), we’ll select the MongoDB connector:
Fill in the requested information to connect and check the connection by clicking on the Test button:
Congrats! You’re well connected to your MongoDB source and you’ve also the possibility to set this connector as Input (Use this data connector as a Source to get data) and/or Output (Use this data connector as a Destination to send data):
Once we’re all set, let’s go create our pipeline to calculate once and for all our Net MRR per paying user.
Create your pipeline by giving a name:
Once the pipeline is created, in the list on the left, drag and drop the Input operation to retrieve your billing data from MongoDB:
You can also click on the Play button in the top center of the platform to preview your data:
So now, we have the data, let’s use the operations to get our Net MRR per paying user!
To get a clearer view of our data, let’s sort our ClientName column by ascending (and click Play to preview the results 🙂 ):
Since in computer science, everything starts from 0 and not 1, so you do need to start the period of subscription accordingly, let’s use the Minus function:
As you can see, we’ve created a new column “PeriodArray” to store the results.
This function helps to create an array from a specific field, in our case, it’ll be used to breakdown the subscription per month for each user (we took the PeriodArray column):
Now, we want to attribute each month of subscription to the user, for instance, we want to have 6 rows for Alan for his first 6-month subscription.
Let’s use the Explode function:
In this step, we achieved the distribution of monthly subscription per user but we want to aggregate the months per user, so let’s use the window ranking function.
The partition by parameter helps to choose on which column we’ll be grouping by the results (let’s take an unique identifier: email). The appropriate function to use here within the Window ranking operation is the row_number that will simply count the number of occurrences for our field (mail). Finally, we store the results in the GeneralSequence column:
Here are the results:
We’ve now a 18-month subscription for Alan that is the aggregation of his 6 + 12 months of subscriptions.
Now we know the exact number of months of subscription per user, we do need to start from 0 to make some aggregations.
We want to know the exact month of the first subscription and go from there to build month over month revenue (upsell, downsell, cancellation) per user but as you can see, the StartDate column is in String (text) format and not in DATE format, so we need to convert it:
Let’s use the Cast to Date operation
Tips: Use the parameter “Initial Date type” to choose the input format and transform it in Date
With the GeneralSequence and the StartDate columns, we’re able to know the month to month subscription:
The results are in the “Period per sub” column and we can observe the evolution of subscription per user over months.
Results in the last column “Period per Sub”:
From now on, you notice the clear overview for each paying user of the following info:
Wouldn’t that be nice to have those labels within our dataset?
To do this, we’ll use the window analytical function.
The “partition by” parameter helps to choose on which column we’ll be grouping by the results (let’s take an unique identifier: email).
The appropriate function to use here within the Window ranking operation is the “lag” function that will return the value of the umpteenth row before the current row. Since this is a month by month analysis, we put 1 as the number in the “N” parameter to get it done.
We observe that both Alan and Bob have started their subscription in January 2022.
Let’s add the period of subscription to each user.
In the previous step, we didn’t show the evolution of revenue per user but just the aggregate amount of revenue per month.
In the column “Delta”, we can observe precisely the evolution of the amount of revenue earned, lost and not earned (0$) per user per month.
As an example, the client Alan went from an overall subscription from 780$ for 6 months to 1200$ for 12 months, consequently the revenue from 12-month subscription is less than the first one:
I promised you that we’d label the upgrades and downsells, let’s do it now since we’ve the evolution of revenue per user in the column “Delta”.
With just one “If” like the Excel function, you can decide the fate of each row: either Upgraded or Downgraded or 0 evolution.
Finally, let’s clean our ready-to-use revenue analysis dataset before automating it to GoogleSheets.
Final dataset to export automatically to GoogleSheets:
It is possible to choose an existing table or spreadsheet or you can create one directly from this interface! Quite magic right!
You can choose the syncing mode: Add data, Add and Update data or Erase and Replace data.
In this article, I introduced you to revenue analysis from MongoDB to GoogleSheets with a step-by-step guide. As any template, you can be inspired by this one and above all play around with it!
Subscribe to our newsletter
Stay up to date to the latest news
on Data Activation
Transform your data with our No Code SQL editor
Automate your operational analytics with your business apps
Discover how Data Transformation really means
Find out the new data architecture concept of Data Mesh
Learn how Operational Analytics actives your data
Learn how to deliver great customer experience with real-time data
Empower your teammates
Generate more revenue
Run better campaigns
Drive your metrics
Stay always up to date on data activation
Find out how we keep your data safe
Discover our always evolving and regularly updated documentation