Logo RestApp White
How to calculate MRR
learning

[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:

 

Billing Table

As we can see, we have a sample of 3 clients, with the following criteria:

  • Mail
  • 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)

 

Step 1: Input

Let’s connect to the source to get billing data and start our analysis:

Click on Step #1 – Configure connectors

Configure Connectors

Then, click on your data source (Input), we’ll select the MongoDB connector:

Select the MongoDB connector
 

Fill in the requested information to connect and check the connection by clicking on the Test button: 

Connect and check

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):

MongoDB connection

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: 

Pipeline creation

Once the pipeline is created, in the list on the left, drag and drop the Input operation to retrieve your billing data from MongoDB:

Drag-and-drop the input operation

You can also click on the Play button in the top center of the platform to preview your data:

Preview of the 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 🙂 ):

Sort Columns

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:

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):

Sequence array

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:

Explode function

The results: 

Explode function 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:

Window ranking function

Here are the results:

Window ranking function 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.

Step 8: Minus

The results:

Step Minus function 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

Cast to date

Results: 

Cast to date results

Step 10: Add date

With the GeneralSequence and the StartDate columns, we’re able to know the month to month subscription: 

Add date

The results are in the “Period per sub” column and we can observe the evolution of subscription per user over months.

Add date results

Step 11: Divide

Divide function

Results in the last column “Period per Sub”:

Divide function results

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.

Window analytical

Results:

Window analytical 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.

Step 13 Add date

Results: 

Step 13 Add date 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. 

Step 14 Minus

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: 

Step 14 Minus results

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.

Sep 15 If

Results: 

Step 15 If results

Step 16: Drop and Sort Columns

Finally, let’s clean our ready-to-use revenue analysis dataset before automating it to GoogleSheets.

Step 16 - Drop & Sort Columns

Final dataset to export automatically to GoogleSheets: 

Step 16 Drop & Sort Columns results

Bonus - Syncing and automation to GoogleSheets

Step 1 - Just drag and drop the Output operation and link it to the Sort columns’ operation

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 2 - Click on “Select data”

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.

Step 3 - Click on “Select 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 directly.

Share
Share on linkedin
Share on twitter
Share on facebook

Subscribe to our newsletter

Brian Laleye
Brian Laleye
Brian is the co-founder of RestApp. He’s a technology evangelist and passionate about innovation. He has an extensive experience focusing on modern data stack.
Share this article
Share on linkedin
Share on twitter
Share on facebook
Subscribe to our newsletter
Ready to experience operational analytics
without code?

Stay up to date to the latest news
on Data Activation

Product

Activate and combine any data sources without code

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

Solutions

Empower your teammates

Prevent churn

Generate more revenue

Run better campaigns

Drive your metrics

Resources

Stay always up to date on data activation

Find out how we keep your data safe

Discover our always evolving and regularly updated documentation