[Series] Revenue Ops - How to calculate Net MRR from MongoDB to GoogleSheet? A full step-by-step guide with template
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:
- StartDate: Month during which they’ve subscribed to the solution
- Period: Period of subscription either 6 months or 12 months
- TotalSub: Amount paid by the client in total during the period (so Alan is paying in total 780$ for 6-month subscription)
Summary
Step 1: Input
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.
Step 2: Modeling
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!
Step 3: Sort Columns
To get a clearer view of our data, let’s sort our ClientName column by ascending (and click Play to preview the results 🙂 ):
Step 4: Minus
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.
Step 5: Sequence array
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):
Step 6: Explode
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:
The results:
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.
Step 7: Window ranking
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.
Step 8: Minus
Now we know the exact number of months of subscription per user, we do need to start from 0 to make some aggregations.
The results:
Step 9: Cast to date
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
Results:
Step 10: Add 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.
Step 11: Divide
Results in the last column “Period per Sub”:
From now on, you notice the clear overview for each paying user of the following info:
- Month of starting subscription
- Month of upselling
- Month of downselling
Wouldn’t that be nice to have those labels within our dataset?
To do this, we’ll use the window analytical function.
Step 12: Window analytical
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.
Results:
We observe that both Alan and Bob have started their subscription in January 2022.
Step 13: Add date
Let’s add the period of subscription to each user.
Results:
Step 14: Minus
In the previous step, we didn’t show the evolution of revenue per user but just the aggregate amount of revenue per month.
Results:
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”.
Step 15: If
With just one “If” like the Excel function, you can decide the fate of each row: either Upgraded or Downgraded or 0 evolution.
Results:
Step 16: Drop and Sort Columns
Finally, let’s clean our ready-to-use revenue analysis dataset before automating it to GoogleSheets.
Final dataset to export automatically to GoogleSheets:
Bonus - Syncing and automation to GoogleSheets
Step 1 - Just drag and drop the Output operation and link it to the Sort columns’ operation
Step 2 - Click on “Select data” to choose your Output aka the destination
It is possible to choose an existing table or spreadsheet or you can create one directly from this interface! Quite magic right!
Step 3 - Choose how to Automate your Revenue Analysis
You can choose the syncing mode: Add data, Add and Update data or Erase and Replace data.
Et voilà! A complete and automated revenue analysis per user straight in your GoogleSheet!
Conclusion
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!
Start your Data Activation journey!
RestApp is a No Code Data Activation platform that empowers anyone with an all-in-one solution to connect, model, and sync any data with his favorite tools.
If you’re interested in starting with Data Activation, check out the RestApp website or book a demo.
Subscribe to our newsletter
Additional resources:
- How to Automate Lead Scoring from PostgreSQL to Hubspot?
- [Series] Data teams – How to connect MongoDB to PostgreSQL: The Hard Way vs The Easy Way
- [Series] Data teams – How to connect MongoDB to MySQL: The Hard Way vs The Easy Way
- [Series] How to Build Data Pipelines in No-Code? A 6-Step guide with examples
