Calculating Simple Monthly Recurring Revenue (MRR)

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

Monthly Recurring Revenue, or MRR, is an important metric for any subscription-based business. It is used to predict future income streams and trajectory, to help measure the health of your business.

We can measure simple MRR within Looker, creating a SQL based derived table comprised of window functions.

There are different methodologies behind calculating MRR, but in its simplest form MRR consists of:

  • Activation (new customers)
  • Recurring payments (when existing customer payments remain the same month-to-month)
  • Expansion (when existing customer payment is greater than prior month)
  • Contraction (when existing customer payment is lower than prior month)
  • Churn (when payments stop)

The following example assumes you have a transaction table that tracks monthly payments by users. The SQL for our derived table would look something like this:

SELECT user_id
,date_month
,prior_amt
,current_amt
,post_amt
,case when prior_amt is null then 'activation'
when date_month <> current_month
and date_month <> prior_month
and post_amt is null then 'churn'
when prior_amt = current_amt then 'recurring'
when prior_amt is not null and prior_amt > current_amt then 'contraction'
when prior_amt is not null and prior_amt < current_amt then 'expansion'
else null end as status
,case when prior_amt is null then current_amt --activation
when date_month <> current_month
and date_month <> prior_month
and post_amt is null then current_amt --churn
when prior_amt = current_amt then current_amt --recurring
when prior_amt is not null and prior_amt > current_amt then prior_amt - current_amt --contraction
when prior_amt is not null and prior_amt < current_amt then current_amt - prior_amt --expansion
else null end as net_mrr
FROM (SELECT user_id
,date_month
,lag(transaction_amt) over (partition by user_id order by date_month asc) as prior_amt
,trans_amt as current_amt
,lead(transaction_amt) over (partition by user_id order by date_month asc) as post_amt
FROM (SELECT user_id
,date_month
,transaction_amt
FROM my_transaction_table
) as raw_txns
) as txns_with_prior_and_post_month

The innermost query simply extracts each payment for each user by month.

The next outer-level level query uses a window lag function to get the prior month's payment, and a lead function to get the following month's payment.

The outermost query calculates the five components of MRR outlined above, for each month's payment:

  • Activation:
    when prior_amt is null then current_amt
  • Recurring:
    when prior_amt = current_amt then current_amt
  • Expansion:
    when prior_amt is not null and prior_amt < current_amt then current_amt
  • Contraction:
    when prior_amt is not null and prior_amt > current_amt then prior_amt - current_amt
  • Churn:
    when date_month <> current_month
    and date_month <> prior_month
    and post_amt is null then current_amt --churn

There are a few caveats to note with this example, some of which could be modeled into the logic if needed:

  1. This assumes payments are made monthly and users do not pay ahead or lag payments
  2. This assumes no partial payments are made
  3. For churn, we go back two months, so we don't count customers that made payment at the very end of the prior month
  4. Not all SQL dialects support window functions (such as MySQL)

We can then plot our MRR over time with a column chart:

6b2169cb-5d3b-40a4-8015-8b2d14e9c436.png


We can also see how our user base is composed using the stacked percentage visualization plot option:

a5758dc7-d1ac-4b65-9d5b-ba1ad7abf326.png


For more information, check out this Looker blog post about window functions.

Version history
Last update:
‎06-22-2022 12:01 PM
Updated by: