Weld logo
stripe logo
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.id
Example 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.

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.