Query Billing data
Billing is made up of different components that work together to provide one-off invoices and periodic billing, with different aspects of billing data available across a number of tables. All billing-specific tables are in the Billing section of the schema, with the primary tables being subscriptions
and invoices
.
To explore billing data further, you can use the additional tables that represent the components of subscriptions and invoices, such as prices
, products
, or coupons
. In addition, the customers
table is a fundamental part of billing, and contains data you might need to report on.
Subscriptions
Each row within the subscriptions
table represents data about an individual Subscription object—the same information that the API retrieves or is available in the Stripe Dashboard. You can report on every subscription that you create on your account.
This table is our recommended starting point for creating reports about your current subscribers. You can join this to other related tables, allowing you to explore your data in more detail.
The following example retrieves a list of subscriptions that have been marked as unpaid, along with any available contact information for the customer.
select subscriptions.id, subscriptions.customer_id, customers.email from subscriptions inner join customers on customers.id = subscriptions.customer_id where subscriptions.status = 'unpaid' limit 5
id | customer_id | |
---|---|---|
sub_xQ5RZh32VOzHhjC | cus_xiBypHKkP0gyPXl | jenny.rosen@example.com |
sub_udnh64wQZVZykuQ | cus_oFA7T1JZLNJ0iSj | noah.wilson@example.com |
sub_vGa4xkqrNn1xlrx | cus_zjsn8h544zhLJ9p | joshua.miller@example.com |
sub_gEkA3UdN1inFbd6 | cus_izxNUGF3xVIkD6e | madison.jackson@example.com |
sub_4qZPMp9p16tOB2s | cus_lVNrHR7wDHHklJa | elijah.smith@example.com |
Customers
Data about Customer objects are contained in the customers
table (this isn’t part of the Billing Tables group). It’s commonly used as part of billing-based reports and can be joined to a number of tables. It’s also useful if you’re creating charges with saved payment information.
The following example retrieves a list of customers with subscriptions that are currently in a trial period. It retrieves both the ID and email address for each customer.
select customers.id, customers.email, subscriptions.price_id from subscriptions inner join customers on customers.id = subscriptions.customer_id where subscriptions.status = 'trialing' limit 5
id | price_id | |
---|---|---|
cus_MAie3YQvxVKmC3f | jenny.rosen@example.com | ruby-pro-522 |
cus_rydoC2NQdgJnNW3 | noah.wilson@example.com | ruby-pro-522 |
cus_dTxcyKcSqtqw7q4 | richard.jones@example.com | gold-basic-221 |
cus_TkuNKnxngkl5IBR | madison.jackson@example.com | gold-basic-221 |
cus_v3wQS0VpRlw3yn2 | elijah.smith@example.com | silver-pro-498 |
Products and prices
Products describe items that your customers can purchase with a subscription. Prices are tied to products and set out the cost, billing interval, and currency. When you view data from the subscriptions
table, you can join it with subscription_items
. Additionally, you can join it to products.id
by using the price_product_id
from the item.
The following example returns a list of active subscriptions along with the product name and its statement descriptor:
with active_subscriptions as ( select s.id as subscription_id, p.name as product_name, p.statement_descriptor from subscriptions s join subscription_items si on s.id = si.subscription_id join products p on si.price_product_id = p.id where s.status = 'active' ) select subscription_id, subscription_item_id, price_id, product_name, statement_descriptor from active_subscriptions order by 1,2
id | name | statement_descriptor |
---|---|---|
sub_FjOSfyGHUVJRmdD | ruby-pro-522 | Ruby Pro |
sub_e9WzuhxPxuWReIU | gold-basic-221 | Gold Basic |
sub_ezoZHvpHm5Wifiw | silver-pro-498 | Silver Pro |
sub_oSoKnrhIdhVgya4 | diamond-mid-244 | Diamond Mid |
sub_rPuqILlgluXhyxo | ruby-standard-196 | Ruby Standard |
Price tiers
While using prices with tiers in your subscriptions, the price_tiers
table can provide specific data about each tier. For instance, if you want to understand the initial tier of your subscriptions, including the maximum quantity for the first tier and the used unit amount, refer to the following query:
with subscription_item_prices as ( select si.subscription_id, si.price_id, p.currency from subscription_items si join prices p on si.price_id = p.id ), price_tier_details as ( select sp.subscription_id, pt.price_id, pt.upto, stringify_amount(sp.currency, pt.amount, '.') as tier_price, sp.currency from subscription_item_prices sp join price_tiers pt on sp.price_id = pt.price_id ) select ptd.subscription_id, ptd.price_id, ptd.upto, ptd.tier_price, ptd.currency from price_tier_details ptd order by ptd.subscription_id, ptd.price_id, ptd.upto asc
subscription_id | price_id | upto | tier_price | currency |
---|---|---|---|---|
sub_iK5IVpCgF2NJwCn | price_vbTF5qk6PQLkt34 | 30 | 2.00 | usd |
sub_DwxsjweUNGuplKs | price_KCnD7CP75xMuM1Q | 60 | 1.00 | usd |
sub_xhRB3z7XGoojH8w | price_SRlEek2SrN3VnBg | 90 | 0.50 | usd |
Invoices
The invoices
table contains data about individual Invoice objects. Each subscription generates an invoice on a recurring basis that represents the amount the customer owes. This automatically includes the amount required for the subscription, and any additional invoice items that might have been created (listed as line items).
Invoices are comprised of individual (invoice) line items. These line items represent any subscriptions that the customer is billed for, and invoice items that have been created and applied to the invoice. To break down an invoice and analyze each of its line items, use the invoice_line_items
table.
The source_id
column of this table contains the ID of either the subscription (for example, sub_fWKEHm6Mtg4ThV5
) or invoice item (for example, ii_KbkeMhxuxBCbYxr
) that the line item corresponds to. The source_type
column reflects whether the line items represent a subscription or an invoice item.
Unlike other foreign keys, the subscription
column of the invoice_line_items
table isn’t always populated. If the corresponding invoice item is a subscription, this column is blank—its ID already appears in the source_id
column.
Invoice items
Data about Invoice items is provided in the invoice_items
table. Invoice items are commonly used to specify an additional amount (or deduct an amount) that’s applied on the next invoice at the beginning of the next billing cycle. For example, you would create an invoice item if you need to bill your customer for exceeding their monthly allowance, or if you need to provide a credit on the next invoice for unused service.
The following example retrieves all the invoices and associated charge IDs for a particular subscription.
select id, charge_id, amount_due from invoices where subscription_id = 'sub_ALJXL9gBYtv6GJ'
id | name | |
---|---|---|
in_RlC6gtdlFQZlnnA | ch_KTZJwNeG3h8Ft3S | 1999 |
in_urwdvdkRVouIoBa | ch_UOTf7a420nnoPuK | 1999 |
in_ccKwXc6CAWA5fjC | 1999 | ch_zAb67Ih7Ur3X1iI |
in_aemWbhsWLERMfFd | 1999 | ch_E0VOsdSz7UaOXik |
in_6Sukp4J1i7AH1wG | 1999 | ch_YHFbhcTMbBn1for |
Invoice totals and discounts
The invoice subtotal represents the amount of all subscriptions, invoice items, and prorations on the invoice before any discount is applied. The invoice total is the amount after discounts and tax have been applied:
invoice.total
= invoice.subtotal
- discount
+ invoice.tax
There is no column to represent the discount amount on an invoice. Instead, you can calculate this by aggregating the line items’ discount amounts. The following query returns a list of invoices, their period start and end, the total discounted amount for the invoice.
with invoices_with_discounts as ( select invoice_id, sum(amount) as total_discount_amount from invoice_line_item_discount_amounts group by invoice_id ) select i.id as invoice_id, i.period_start, i.period_end, stringify_amount(i.currency, ilda.total_discount_amount, '.') as total_discount_amount i.currency from invoices i join invoices_with_discounts ilda on i.id = ilda.invoice_id order by i.id
invoice_id | period_start | period_end | total_discount_amount | currency |
---|---|---|---|---|
in_x2UApyrBz3tme9T | 2024-05-01 | 2024-06-01 | 24.66 | usd |
in_7pgUm4rNPDlM6ow | 2024-06-01 | 2024-07-01 | 24.34 | usd |
in_PtfgdUiGW4F9IaR | 2024-04-01 | 2024-05-01 | 45.96 | usd |
Working with invoice dates and periods
Subscription invoices are pre-billed, meaning the customer makes the payment at the beginning of a billing cycle. This is represented in a line item’s period
value. For example, a customer with a monthly subscription is billed at the start of each month. If they choose to cancel_at_period_end, their subscription stays active until the month’s end, after which the subscription ends.
The period_start
and period_end
values of an invoice represents when invoice items might have been created–it’s not always definitive of the period of service that the customer is being billed for. For example, if a customer is billed on the 1st of each month and exceeds their monthly allowance on the 15th, you might create an invoice item for any additional costs that the customer is charged for. This invoice item is then included in the next invoice, which is created on the 1st of the next month. When the next invoice is generated, the period_start
date would be the 15th of the previous month—the date the additional line item is first created.
Coupons
A Coupon object represents an amount or percentage-off discount that you can apply to subscriptions or customers.
select coupons.id, coupons.amount_off, coupons.percent_off from coupons where valid = false limit 5
id | amount_off | percent_off |
---|---|---|
10FF | 10 | |
SUMMER25 | 25 | |
10FREE | 10 | |
15OFF | 15 | |
FALL30 | 30 |
Discounts
A discount is the application of a coupon, represented by a Discount object. The following query returns a list of subscriptions and their associated discounts and coupons:
select subscriptions.id as subscription_id, t.discount_id, coupons.id as coupon_id from subscriptions cross join unnest(split(subscriptions.discounts, ',')) as t(discount_id) join discounts on discounts.id = t.discount_id join coupons on coupons.id = discounts.coupon_id limit 3
subscription_id | discount_id | coupon_id |
---|---|---|
sub_uKPnBKb2kf5iU8d | di_JC313WgbgfRJgG9 | 10OFF |
sub_Ipz2nQ8bnFFHU2a | di_1tuVc1LOq83rTw8 | 25OFF |
sub_HWdW4hrhTcYvhOd | di_xsVdAhxEgi2txDx | 10FREE |
Promotion codes
A promotion code represents a customer-redeemable code for a coupon. The following query provides a list of promotion codes pertaining to a specific coupon and displays the number of times each code has been redeemed:
select promotion_codes.id as promotion_code_id, promotion_codes.code as promotion_code, promotion_codes.times_redeemed from promotion_codes limit 3
promotion_code_id | code | times_redeemed |
---|---|---|
promo_8eLfuzn1kXRe0qx | 10OFF | 1 |
promo_jJE22xAEeBPMsYj | 25OFF | 2 |
promo_uz9COTPjhpuAiI3 | 10FREE | 3 |
Subscription Item Change Events
The subscription_item_change_events
table tracks changes to subscription items that affect Monthly Recurring Revenue (MRR). Use this table to calculate MRR for individual customers, products, plans, and to create custom metric definitions for your business models.
Caution
This table provides more up-to-date data than the source driving the MRR metrics on the Billing overview in the Stripe Dashboard. This means the data for the last and current day’s MRR here could be more accurate and could differ from what you see in the Dashboard.
local_event_timestamp and event_timestamp
This table includes two timestamp columns:
event_timestamp
: This is the UTC timestamp.local_event_timestamp
: This timestamp is in your local timezone, typically the timezone of the person who created your Stripe account.
currency
Here, you’ll find the subscription item’s settlement currency as a three-letter ISO currency code in lowercase. The currency must be one that Stripe supports.
mrr_change
The mrr_change
column shows the positive or negative impact of an event on your MRR in the subscription item’s settlement currency’s minor unit (such as cents for USD).
event_type
Event type | Definition |
---|---|
ACTIVE_START | The subscription item started contributing to MRR. |
ACTIVE_UPGRADE | The MRR contribution of the subscription item increased (for example, the quantity increased). |
ACTIVE_END | The subscription item stopped contributing to MRR. |
ACTIVE_DOWNGRADE | The MRR contribution of the subscription item decreased (for example, the quantity decreased). |
Note
Some user actions can create multiple events, so you could see an event with an event_type
of ACTIVE_END
on one item and then immediately an event with an event_type
of ACTIVE_START
on another item for the same subscription_id
.
Other columns
Other columns (product_id
, price_id
, customer_id
, subscription_id
, and subscription_item_id
) hold IDs related to the subscription item change event.
Example query
Querying this table to calculate MRR involves window functions and, for those with customers in different currencies, foreign currency exchange calculations. Here’s an example for calculating daily MRR for the past two months in US dollar cents:
-- the following six CTEs load the foreign currency exchange rates into a more efficient datastructure to use later. with date_ranges as ( SELECT date_col FROM UNNEST( sequence(date '2010-01-01', current_date, INTERVAL '1' DAY) ) t (date_col) ), currencies AS ( SELECT DISTINCT t.currency FROM exchange_rates_from_usd CROSS JOIN UNNEST ( CAST(json_parse(buy_currency_exchange_rates) AS MAP(VARCHAR, DOUBLE)) ) AS t (currency, rate) ), dates as ( select date_col as date, date_col + INTERVAL '1' DAY as fx_date from date_ranges ), dates_currencies as ( select date, fx_date, currency from dates cross join currencies ), fx_rates_json as ( select dates.date, last_value( exchange_rates_from_usd.buy_currency_exchange_rates ) ignore nulls over ( order by exchange_rates_from_usd.date asc ) as buy_currency_exchange_rates from dates full outer join exchange_rates_from_usd on dates.fx_date = exchange_rates_from_usd.date ), fx_rates as ( select transform_values( multimap_agg( date_format(date, '%Y-%m-%d'), row(currency, rate) ), ((k, v) -> map_from_entries(v)) ) as rates from fx_rates_json cross join unnest( map_entries( cast( json_parse(buy_currency_exchange_rates) as map(varchar, double) ) ) ) t(currency, rate) where date in ( select date from dates ) ), -- this sums up the per-currency MRR changes for each day per_day_sums as ( select date(local_event_timestamp) as event_date, currency, sum(mrr_change) as mrr_change from subscription_item_change_events t group by 1, 2 ), per_currency_sums as ( select date_format(c.date, '%Y-%m-%d') as date, c.currency, sum(mrr_change) over ( partition by c.currency order by c.date asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) as mrr from per_day_sums right join dates_currencies c on c.currency = per_day_sums.currency and c.date = per_day_sums.event_date ) -- this does the foreign exchange calculation and filters to the time range you want, 2 months. select date, cast( sum( round( -- replace 'usd' below with the currency you want your MRR represented in mrr / fx_rates.rates[date][currency] * fx_rates.rates[date]['usd'] ) ) as bigint ) as mrr from per_currency_sums cross join fx_rates where -- here's where you filter to the range you want, in this case 2 months date >= date_format(current_date - interval '2' month, '%Y-%m-%d') group by 1 order by 1 desc
event_date | mrr |
---|---|
2024-04-17 | 14402570 |
2024-04-16 | 14410900 |
2024-04-15 | 14403403 |
2024-04-14 | 14372582 |
2024-04-13 | 14365918 |
2024-04-12 | 14327600 |
2024-04-11 | 14346759 |
2024-04-10 | 14363419 |
2024-04-09 | 14351757 |
2024-04-08 | 14333431 |
2024-04-07 | 14319270 |
2024-04-06 | 14316771 |
2024-04-05 | 14240135 |
2024-04-04 | 14250131 |
2024-04-03 | 14253463 |
2024-04-02 | 14247632 |
2024-04-01 | 14247632 |
2024-03-31 | 14214312 |
2024-03-30 | 14187656 |
2024-03-29 | 14146839 |
2024-03-28 | 14152670 |
2024-03-27 | 14152670 |
2024-03-26 | 14131012 |
2024-03-25 | 14124348 |
2024-03-24 | 14116851 |
2024-03-23 | 14115185 |
2024-03-22 | 14097692 |
2024-03-21 | 14104356 |
2024-03-20 | 14113519 |
2024-03-19 | 14097692 |
2024-03-18 | 14088529 |