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:
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:
when prior_amt is null then current_amt
when prior_amt = current_amt then current_amt
when prior_amt is not null and prior_amt < current_amt then current_amt
when prior_amt is not null and prior_amt > current_amt then prior_amt - current_amt
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:
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.