Weld logo
stripe logo
Stripe

Normalize to monthly revenue

This SQL model fetches the monthly subscription amounts, converts them to EUR using average monthly forex rates, and provides insights into revenue streams in a standardized currency.
1with
2    subscription_item as (
3        select
4            customer_id
5          , subscription_id
6          , amount_off
7          , start_date
8          , canceled_at
9          , quantity
10          , amount
11          , currency
12          , status
13          , case
14                when interval_period = 'year' then ((amount / interval_count) / 12) * quantity
15                else (amount / interval_count) * quantity
16            end as monthly_amount
17        from
18            {{stripe_demo.stripe_subscription_item}}
19        where
20            deleted = 0
21    )
22  , forex as (
23        select
24            avg(rate) as rate
25          , currency
26          , date_trunc(timestamp, month) as month
27        from
28            {{raw.forex_eur.incremental}}
29        group by
30            month
31          , currency
32        order by
33            month
34    )
35select
36    customer_id
37  , subscription_id
38  , start_date
39  , canceled_at
40  , amount_off
41  , status
42  , case
43        when monthly_amount > 0 then monthly_amount / rate
44        else null
45    end as monthly_amount_eur
46from
47    subscription_item
48    left join forex on date_trunc(start_date, month) = forex.month
49    and upper(subscription_item.currency) = forex.currency
Example of output from model:
+------------+-----------------+------------+------------------+
| customer_id| subscription_id | start_date | monthly_amount_eur |
+------------+-----------------+------------+------------------+
|   cus_001  |     sub1        |2023-01-01  |     5000         |
|   cus_002  |     sub2        |2023-02-01  |     5500         |
|   cus_003  |     sub3        |2023-03-01  |     4800         |
|   cus_004  |     sub4        |2023-04-01  |     5100         |
|   cus_005  |     sub5        |2023-05-01  |     5200         |
|   cus_006  |     sub6        |2023-06-01  |     5300         |
+------------+-----------------+------------+------------------+

This model helps in getting a clear view of revenue when dealing with multiple currencies. By converting all subscription amounts to EUR, you can analyze your revenue without the complexity of currency fluctuations. It uses average monthly forex rates for conversions and provides a monthly breakdown of the subscription amounts.

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.