Weld
Start free
Stripe model

Get subscription plans

Pulls an exhaustive view of Stripe subscriptions, diving into associated items, the specifics of plans, discounts applied through coupons, and additional metrics like the amount discounted and subscription intervals.

See the SQLAll Stripe models
Grain
See the notes
Built from
  1. 5
    Raw tables
  2. 1
    Staging
  3. 1
    Core
  4. 1
    Analytics
Reads from Stripe
couponplansubscriptionsubscription_discountsubscription_item
Ships as
Weld and dbt SQL, open source on GitHub
core.stripe.subscription_item
1-- core.stripe.subscription_item
2-- Get subscription plans. Pulls an exhaustive view of Stripe subscriptions, diving
3-- into associated items, the specifics of plans, discounts applied through coupons,
4-- and additional metrics like the amount discounted and subscription intervals.
5--
6-- From the weld.app template library. Not reconciled against Stripe;
7-- check the output against the source before relying on it.
8
9select
10    s.id,
11    s.customer_id,
12    s.start_date,
13    s.canceled_at,
14    s.id as subscription_id,
15    s.status,
16    d.coupon_id,
17    c.amount_off / 100 as amount_off,
18    si.price_id,
19    si.quantity,
20    p.amount / 100 as amount,
21    p.interval_count,
22    p.interval as interval_period,
23    p.currency,
24    case
25        when date_trunc(start_date, month) <> date_trunc(canceled_at, month) then 0
26        when canceled_at is null then 0
27        else 1
28    end as deleted
29from
30    {{staging.stripe.subscriptions}} s
31    left join {{raw.stripe.subscription_item}} si on s.id = si.subscription_id
32    left join {{raw.stripe.plan}} p on si.price_id = p.id
33    left join {{raw.stripe.subscription_discount}} d on s.id = d.subscription_id
34    left join {{raw.stripe.coupon}} c on d.coupon_id = c.id

This model and the 2 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 SQL model provides businesses with intricate details about their Stripe subscriptions. Beyond just fetching the status and duration of each subscription, this model delves into the associated items and plans, detailing specifics like pricing and intervals. Furthermore, it fetches discount details applied through coupons, a crucial insight for understanding customer incentives. It also provides an additional metric - if the subscription was deleted within the same month of its start date.

Raw tables it reads

  • 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