Stripe
Create time series for each customer
This SQL model captures the monthly recurring revenue (MRR) trend for each customer, including the change in MRR, and provides a rank based on the MRR. It also considers the customer's subscription status and cancellation date.
1with
2 time_series as (
3 select
4 month
5 from
6 unnest (
7 generate_date_array(
8 date_trunc('2021-01-01', month)
9 , current_date
10 , interval 1 month
11 )
12 ) as month
13 )
14 , time_series_customer as (
15 select
16 *
17 from
18 (
19 select distinct
20 (customer_id) customer_id
21 , month
22 from
23 {{stripe_demo.stripe_mrr_active_subscriptions}}
24 cross join time_series
25 order by
26 customer_id
27 , month
28 )
29 )
30 , time_series_revenue as (
31 select
32 c.customer_id
33 , month
34 , start_date_month
35 , first_value(canceled_at ignore nulls) over (
36 partition by
37 c.customer_id
38 order by
39 month asc
40 ) canceled_at
41 , monthly_amount_eur_after_discount
42 , first_value(status ignore nulls) over (
43 partition by
44 c.customer_id
45 order by
46 month asc
47 ) status
48 , last_value(monthly_amount_eur_after_discount ignore nulls) over (
49 partition by
50 c.customer_id
51 order by
52 month asc
53 ) mrr
54 from
55 time_series_customer c
56 left join {{stripe_demo.stripe_mrr_active_subscriptions}} r on c.customer_id = r.customer_id
57 and c.month = r.start_date_month
58 order by
59 customer_id
60 , month asc
61 )
62select
63 *
64 , case
65 when mrr - lag(mrr) over (
66 partition by
67 customer_id
68 order by
69 month asc
70 ) is null then mrr
71 else mrr - lag(mrr) over (
72 partition by
73 customer_id
74 order by
75 month asc
76 )
77 end mrr_change
78 , row_number() over (
79 partition by
80 customer_id
81 order by
82 month asc
83 ) mrr_rank_asc
84 , row_number() over (
85 partition by
86 customer_id
87 order by
88 month desc
89 ) mrr_rank_desc
90from
91 time_series_revenue
92where
93 mrr is not null
94 and (
95 canceled_at is null
96 or month <= canceled_at
97 )
98order by
99 customer_id
100 , month ascExample of output from model:
+------------+------------+----------------+----------------+------------+------------+-------------+
| customer_id| month | monthly_amount | mrr_change |mrr_rank_asc|mrr_rank_desc| canceled_at|
+------------+------------+----------------+----------------+------------+------------+-------------+
| cus_001 |2023-01-01 | 1000 | 50 | 1 | 6 | |
| cus_001 |2023-02-01 | 990 | -10 | 2 | 5 | |
| cus_001 |2023-03-01 | 1010 | 20 | 3 | 4 | |
| cus_001 |2023-04-01 | 1020 | 10 | 4 | 3 | |
| cus_001 |2023-05-01 | 1030 | 10 | 5 | 2 | |
| cus_001 |2023-06-01 | 1045 | 15 | 6 | 1 | |
+------------+------------+----------------+----------------+------------+------------+-------------+By using this model, businesses can gain insights into their customer's MRR progression, helping in understanding the customer's lifetime value, churn patterns, and overall business health. The time series analysis also assists in predicting future revenue patterns and making informed business decisions.
