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:
- This assumes payments are made monthly and users do not pay ahead or lag payments
- This assumes no partial payments are made
- For churn, we go back two months, so we don't count customers that made payment at the very end of the prior month
- Not all SQL dialects support window functions (such as MySQL)
We can then plot our MRR over time with a column chart:
We can also see how our user base is composed using the stacked percentage visualization plot option:
For more information, check out this Looker blog post about window functions.