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 output
+ ---------+--------+----------------+----------+--------------+-------------+----------+
| 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.