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.currencyExample 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.
