LogoLogo
HomeDocumentation
  • 🐳Using Whaly Guides
  • Core concepts
    • 📚Getting started
      • Data stack architecture
      • Consumers vs Builders
      • Data layers in Whaly
      • License Mapping
    • 🪄Data modeling
      • Understanding data models
      • Designing data models
      • Common modeling patterns
        • Event schema
      • Maintaining data models
      • Data models best practices
    • 🖌️Explorations
      • Understanding Explorations
      • Designing Explorations
      • Maintaining Explorations
      • Mistakes to avoid
  • Training
    • 👁️For viewers
    • 👩‍💻For editors
    • 🧙For builders
      • Setting up the training material
      • Creating a chart
      • Using and editing explorations
      • Filtering a dashboard
      • Creating explorations and models
  • Inspiration
    • 🗒️Use cases
      • Billing / Invoicing
      • Customer success
      • Fundraising
      • Marketing
      • Partnerships
      • Product
      • Sales
      • Strategy
    • 💬Communication
    • 💡Tips
  • Recipes
    • 🤝Customer care
      • How to build a 360° customer dashboard
    • 🏦Finance
      • Modeling your recurring revenue
        • SQL for simplified MRR calculation
        • SQL for advanced MRR calculation
    • 📣Marketing
      • Track your entire Marketing Funnel
      • Calculate your Customer Acquisition Cost
      • Create a partner dashboard
    • 💼Sales
      • Analyze the impact of your Sales velocity on your closing rate
      • Create a sales performance dashboard
      • Build a target oriented sales dashboard
  • Misc
    • 🧐SQL Fanout
    • 📦Backup your data using BigQuery
    • ☁️Embedding reports in Salesforce
    • 👨‍💻Useful SQL operations
      • Flattening categories
Powered by GitBook
On this page
  • High level plan
  • Gathering our required data sources
  • Importing our data and creating a SQL Model
  • Writing down the SQL Query (BigQuery)
  1. Recipes
  2. Finance

Modeling your recurring revenue

PreviousFinanceNextSQL for simplified MRR calculation

Last updated 1 year ago

Objectives:

Calculating your recurring revenue (MRR or ARR) is vital for every SaaS and subscription based businesses. It helps understanding your past and current revenue as well as projecting your upcoming revenue. When talking with investors, you will also be asked to share this information, the more detailed the better. Keeping track of your recurring revenue will allow you to monitor your company growth.

If you follow this article, you will be able to build the following dashboard:

High level plan

  • Gathering our required data sources

  • Importing our data and creating a SQL Model

  • Writing down the SQL Query (BigQuery)

    • SQL for simplified MRR calculation

    • SQL for advanced MRR calculation

Gathering our required data sources

Every business is unique, and the way your business handles subscriptions may be slightly different than what this article assumes. Here, we will work with a really simple data source :

As you can see our subscription dataset is very clean, as we have:

  • One line per subscription, with the monthly amount, the subscription start date and the subscription end date.

  • Our subscriptions begin the first day of the month and ends the last day of the month.

  • A customer can only have one active subscription during a given month.

  • At the end of his subscription, a customer can cancel (churn), downgrade, upgrade or renew his subscription.

The main problem with the current form of this dataset is that it will not play well with visualisation software, i.e. you will not be able to build this kind on charts :

At the end of this article, we will have modeled our date into an analytics-ready dataset, meaning that we will have one line per subscription per active month. We will also take things one step further in order to identify churn, new revenue, renewals, upgrades and downgrades, as well as calculate MRR changes.

Importing our data and creating a SQL Model

Importing our data into Whaly

Let's import our data into Whaly, you can use the source of your choice in order to do so. If you are just playing around and experimenting, we recommend you use either our Google Sheet connector:

Creating a SQL Model

In order to model our current subscriptions data, we will need to create a SQL Model. This can easily done in Whaly.

You can create a new source, select the option From warehouse and name it SQL. Inside this source create a SQL Model named Simplified SQL.

Writing down the SQL Query (BigQuery)

We will see two methods in order to model our MRR:

  • A simplified method for SQL intermediates users.

  • An advanced method for SQL advanced to expert users.

During these steps by steps we will rely on some specific BigQuery commands in order to manipulate dates. These steps should be adapted if used with an other warehouse.

If you do not have suitable data you can download our example dataset by .

If you have not created any models until now, it is easily done by following our .

🏦
clicking here
documentation on SQL Models
Google SheetsDocumentation
Logo