Weld logo
stripe logo
Stripe

Identify active subscriptions and sum revenue

This SQL model fetches the monthly revenue per subscription and applies discounts to provide a net monthly amount. It then identifies if a subscription is active or canceled based on the status.
1with
2    revenue_per_subscription as (
3        select
4            customer_id
5          , cast(date_trunc(start_date, month) as date) as start_date_month
6          , cast(date_trunc(canceled_at, month) as date) as canceled_at
7          , max(amount_off) as amount_off
8          , status
9          , sum(monthly_amount_eur) as monthly_amount_eur
10          , sum(monthly_amount_eur) - ifnull(max(amount_off), 0) as monthly_amount_eur_after_discount
11        from
12            {{stripe_demo.stripe_mrr_normalization}}
13        group by
14            customer_id
15          , start_date_month
16          , canceled_at
17          , status
18    )
19  , active_subscriptions as (
20        select
21            customer_id
22          , max(status) as status
23        from
24            revenue_per_subscription
25        where
26            status <> 'canceled'
27        group by
28            customer_id
29    )
30  , cancelled_subscriptions as (
31        select
32            customer_id
33          , max(canceled_at) as canceled_at
34          , max(status) as status
35        from
36            revenue_per_subscription
37        where
38            status = 'canceled'
39        group by
40            customer_id
41    )
42select
43    r.customer_id
44  , coalesce(s.status, c.status) as status
45  , start_date_month
46  , case
47        when coalesce(s.status, c.status) = 'canceled' then c.canceled_at
48        else null
49    end as canceled_at
50  , cast(
51        round(sum(monthly_amount_eur_after_discount), 0) as int
52    ) as monthly_amount_eur_after_discount
53from
54    revenue_per_subscription r
55    left join active_subscriptions s on r.customer_id = s.customer_id
56    left join cancelled_subscriptions c on r.customer_id = c.customer_id
57group by
58    customer_id
59  , status
60  , r.status
61  , start_date_month
62  , canceled_at
63order by
64    customer_id
Example of output from model:
+------------+----------+-------------------+-------------------------+
| customer_id|  status  |  start_date_month |monthly_amount_eur_after_discount|
+------------+----------+-------------------+-------------------------+
|   cus_001  |  active  |     2023-01-01    |            5000            |
|   cus_001  |  active  |     2023-02-01    |            5500            |
|   cus_001  |  active  |     2023-03-01    |            5200            |
|   cus_001  |  active  |     2023-04-01    |            5100            |
|   cus_001  |  active  |     2023-05-01    |            5400            |
|   cus_001  |  active  |     2023-06-01    |            5600            |
+------------+----------+-------------------+-------------------------+

This model provides insights into the net revenue streams from each subscription after considering discounts. By segregating active and cancelled subscriptions, businesses can assess their revenue health more effectively and make informed financial decisions.

Ready to start modeling your own stripe data?

Get started building your data warehouse with stripe and connect all your apps and databases.

stripe logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.