Stripe
Summarize Monthly Recurring Revenue (MRR)
This SQL model provides a comprehensive view of the Monthly Recurring Revenue (MRR) by categorizing the revenue into new MRR, contraction MRR, churn MRR, and expansion MRR. It also computes the net new MRR and total MRR over time.
1with
2 mrr_calc as (
3 select
4 *
5 , case
6 when mrr_rank_asc = 1 then mrr_change
7 else 0
8 end as new_mrr
9 , case
10 when mrr_change < 0 then mrr_change
11 else 0
12 end as contraction_mrr
13 , case
14 when mrr_change > 0
15 and mrr_rank_asc > 1 then mrr_change
16 else 0
17 end as expansion_mrr
18 , case
19 when mrr_rank_desc = 1
20 and status = 'canceled' then mrr * -1
21 else 0
22 end as churn_mrr
23 from
24 {{stripe_demo.stripe_mrr_time_series}}
25 )
26 , net_mrr_calc as (
27 select
28 month
29 , sum(new_mrr) new_mrr
30 , sum(contraction_mrr) contraction_mrr
31 , sum(churn_mrr) churn_mrr
32 , sum(expansion_mrr) expansion_mrr
33 , sum(
34 new_mrr + contraction_mrr + churn_mrr + expansion_mrr
35 ) as net_new_mrr
36 from
37 mrr_calc
38 group by
39 month
40 )
41select
42 month
43 , new_mrr
44 , contraction_mrr
45 , churn_mrr
46 , expansion_mrr
47 , net_new_mrr
48 , sum(net_new_mrr) over (
49 order by
50 month asc
51 ) as mrr_total
52from
53 net_mrr_calcExample of output from model:
+---------+--------+----------------+----------+--------------+-------------+----------+
| month | new_mrr|contraction_mrr| churn_mrr|expansion_mrr | net_new_mrr | mrr_total|
+---------+--------+----------------+----------+--------------+-------------+----------+
|2023-01-01| 1000 | -50 | -200 | 150 | 900 | 900 |
|2023-02-01| 900 | -60 | -100 | 160 | 900 | 1800 |
|2023-03-01| 850 | -55 | -150 | 155 | 800 | 2600 |
|2023-04-01| 920 | -58 | -110 | 168 | 920 | 3520 |
|2023-05-01| 910 | -65 | -120 | 170 | 895 | 4415 |
|2023-06-01| 930 | -70 | -130 | 175 | 905 | 5320 |By understanding the different components of MRR, businesses can pinpoint areas of growth or potential issues. The breakdown helps in identifying trends in customer acquisition, churn, or expansion and provides a clear picture of the business health over time.
