Weld
Start free
Stripe model

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.

See the SQLAll Stripe models
Grain
See the notes
Built from
  1. 6
    Raw tables
  2. 1
    Staging
  3. 2
    Core
  4. 1
    Analytics
Reads from Stripe
incrementalcouponplansubscriptionsubscription_discountsubscription_item
Ships as
Weld and dbt SQL, open source on GitHub
core.stripe.mrr_normalization
1-- core.stripe.mrr_normalization
2-- Normalize to monthly revenue. This SQL model fetches the monthly subscription
3-- amounts, converts them to EUR using average monthly forex rates, and provides
4-- insights into revenue streams in a standardized currency.
5--
6-- From the weld.app template library. Not reconciled against Stripe;
7-- check the output against the source before relying on it.
8
9with
10subscription_item as (
11    select
12        customer_id,
13        subscription_id,
14        amount_off,
15        start_date,
16        canceled_at,
17        quantity,
18        amount,
19        currency,
20        status,
21        case
22            when interval_period = 'year' then ((amount / interval_count) / 12) * quantity
23            else (amount / interval_count) * quantity
24        end as monthly_amount
25    from
26        {{core.stripe.subscription_item}}
27    where
28        deleted = 0
29),
30forex as (
31    select
32        avg(rate) as rate,
33        currency,
34        date_trunc(timestamp, month) as month
35    from
36        {{raw.forex_eur.incremental}}
37    group by
38        month, currency
39    order by
40        month

This model and the 3 it depends on. Deploy them together; refs like {{staging.…}} resolve to the matching folders in Weld, and the dbt view uses source() and ref().

Notes

How it counts. And where it stops.

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.

Raw tables it reads

  • raw.forex_eur.incremental
  • raw.stripe.coupon
  • raw.stripe.plan
  • raw.stripe.subscription
  • raw.stripe.subscription_discount
  • raw.stripe.subscription_item

More Stripe models

Put Stripe in front
of your agents.

Connect Stripe, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.

Start for freeTalk to us