Weld logo
stripe
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 asc
Example 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.

Ready to start modeling your own stripe data?

Get started building your data warehouse with stripe and 100+ more apps and databases available.

stripe
Weld logo
Sign up for free
Weld logo

Tired of scattered data? Sync data to your data warehouse with Weld in minutes with our powerful ETL, SQL Transformations, Reverse ETL and AI Assistant, Ed - connected to 100+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
GDPR logoSOC2
© 2024 Weld. All rights reserved.