Stripe
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.
1select
2 s.id
3 , s.customer_id
4 , s.start_date
5 , s.canceled_at
6 , s.id as subscription_id
7 , s.status
8 , d.coupon_id
9 , c.amount_off / 100 as amount_off
10 , si.price_id
11 , si.quantity
12 , p.amount / 100 as amount
13 , p.interval_count
14 , p.interval as interval_period
15 , p.currency
16 , case
17 when date_trunc(start_date, month) <> date_trunc(canceled_at, month) then 0
18 when canceled_at is null then 0
19 else 1
20 end as deleted
21from
22 {{stripe_demo.stripe_subscriptions}} s
23 left join {{raw.stripe.subscription_item}} si on s.id = si.subscription_id
24 left join {{raw.stripe.plan}} p on si.price_id = p.id
25 left join {{raw.stripe.subscription_discount}} d on s.id = d.subscription_id
26 left join {{raw.stripe.coupon}} c on d.coupon_id = c.idExample of output from model:
+-----+------------+---------------------+---------------------+----------------+---------+---------+------------+---------+---------+--------+---------------+----------------+---------+--------+
| id | customer_id| start_date | canceled_at | subscription_id| status |coupon_id| amount_off |price_id|quantity| amount |interval_count|interval_period| currency|deleted|
+-----+------------+---------------------+---------------------+----------------+---------+---------+------------+---------+---------+--------+---------------+----------------+---------+--------+
| sub1| cus_001 | 2023-01-01 00:00:00 | | sub1 | active | coup1 | 5 | price1 | 1 | 15.0 | 1 | month | USD | 0 |
| sub2| cus_002 | 2023-01-10 00:00:00 | | sub2 | active | coup2 | 10 | price2 | 2 | 20.0 | 1 | month | USD | 0 |
| sub3| cus_003 | 2023-01-15 00:00:00 | | sub3 | active | coup3 | 15 | price3 | 1 | 25.0 | 1 | month | USD | 0 |
| sub4| cus_004 | 2023-02-05 00:00:00 | | sub4 | active | coup4 | 10 | price4 | 1 | 30.0 | 1 | month | USD | 0 |
| sub5| cus_005 | 2023-02-12 00:00:00 | | sub5 | active | coup5 | 5 | price1 | 3 | 15.0 | 1 | month | USD | 0 |
| sub6| cus_006 | 2023-03-05 00:00:00 | | sub6 | active | coup6 | 7 | price3 | 2 | 25.0 | 1 | month | USD | 0 |
| sub7| cus_007 | 2023-03-10 00:00:00 | | sub7 | active | coup7 | 20 | price2 | 4 | 20.0 | 1 | month | USD | 0 |
| sub8| cus_008 | 2023-03-15 00:00:00 | | sub8 | active | coup8 | 5 | price4 | 2 | 30.0 | 1 | month | USD | 0 |
+-----+------------+---------------------+---------------------+----------------+---------+---------+------------+---------+---------+--------+---------------+----------------+---------+--------+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.
