Sign in
An image of the Stripe logo
Create account
Sign in
Home
Payments
Business operations
Financial services
Developer tools
Security
All products
Home
Payments
Business operations
Home
Payments
Business operations
Financial services
Developer tools
Support
Overview
Overview
Get started
Access data in your Stripe Dashboard with Sigma
Access data in your data warehouse with Data Pipeline
Data freshness
Query data
Write queries
Query transactional data
Query Billing data
Query Connect data
Query Issuing data
Schedule queries
HomeBusiness operationsStripe data

Query Billing data

Use Sigma or Data Pipeline to retrieve information about Billing.

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 found in the Billing section of the schema, with the primary tables being subscriptions and invoices.

To explore billing data further, you can make use of 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 may 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
idcustomer_idemail
sub_B6Vf7vY9uiU1lTQcus_bWeQIKvwuQxdGnojenny.rosen@example.com
sub_F5SBP05bzCH6SmWcus_AHbZ7e7UqFCwOJdnoah.wilson@example.com
sub_hbbrX6CkLk98qmzcus_6A7726qnwwtdtEujoshua.miller@example.com
sub_C88IWGW8yYRchtJcus_AdmOFMF04cIwHaEmadison.jackson@example.com
sub_FZ386ZUCCAQxHdocus_UHe9KBqBimcAYCBelijah.smith@example.com

Customers

Data about Customer objects are contained in the customers table (this isn’t part of the Billing Tables group). It is 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. Both the ID and email address for each customer is retrieved.

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
idemailprice_id
cus_ZbYB4jQxTGGXAicjenny.rosen@example.comruby-pro-522
cus_bUE6xJuoPF6UfUXnoah.wilson@example.comruby-pro-522
cus_hrQWrJ1FrjDIhUtrichard.jones@example.comgold-basic-221
cus_1r1X267FKGUgblhmadison.jackson@example.comgold-basic-221
cus_MCpkGNUEdR7dQAkelijah.smith@example.comsilver-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 viewing data from the subscriptions table, subscriptions.price_id can be joined to prices.id, and prices.product_id can be joined to products.id. The following example returns a list of active subscriptions along with the product name and its statement descriptor.

select subscriptions.id, products.name, products.statement_descriptor from subscriptions inner join prices on subscriptions.price_id=prices.id inner join products on prices.product_id=products.id where subscriptions.status = 'active' limit 10
idnamestatement_descriptor
sub_ZZOaPAt0TgxEofXruby-pro-522Ruby Pro
sub_FjPDWGl3fh1W95ogold-basic-221Gold Basic
sub_dk3wIj0uS3hY0oVsilver-pro-498Silver Pro
sub_UWzJHXFb4bH3wmndiamond-mid-244Diamond Mid
sub_NzUD5zxCAyuxv48ruby-standard-196Ruby Standard

Invoices

Working with invoices

Refer to our invoices documentation to learn more about invoices, invoice items, and invoice line items.

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 may 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_ZTomOx65BjfMLDp) or invoice item (for example, ii_KGGUO6L73Y0uHIM) 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 is applied on the next invoice at the beginning of the next billing cycle. For instance, 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'
idname
in_twQ5uro6jc521ekch_wD4RLUTfRLmN02V1999
in_reJY5MPR2c6hqtKch_fG2ElpLFeVM1R5G1999
in_AksDy1gZHgIqoww1999ch_chQB3nJjtINXfhL
in_cQC56cfKTlcsHEi1999ch_j3GTjkekNXC4LCB
in_PFhyh9485EcS2VM1999ch_pu2Z3IbeFn2k7Ro

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 using the total, subtotal, and tax amounts:

discount = invoice.total - invoice.tax - invoice.subtotal

Working with invoice dates and periods

As subscription invoices are pre-billed, the customer pays at the start of a billing period. This is reflected in the value for a line item’s period. For instance, a customer on a monthly subscription is charged at the start of each month. If they cancel, their subscription remains active until the end of that month, at which point the subscription ends.

The period_start and period_end values of an invoice represents when invoice items may have been created–it is not always indicative 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 15th of the previous month: the date the additional line item is first created.

Coupons and discounts

A Coupon object represents an amount or percentage-off discount that can be applied to subscriptions or customers. A discount is the application of a coupon, represented by a Discount object.

Discounts are not tabulated separately. Instead, join coupon.id to either customers.discount_coupon_id or subscriptions.discount_coupon_id. This returns the coupon information for the discount that has been applied. For example, the following query returns a list of subscriptions where a coupon was applied to create a discount, along with the coupon’s discount amount or percentage.

select coupons.id, coupons.amount_off, coupons.percent_off from coupons where valid = false limit 5
idamount_offpercent_off
10FF10
SUMMER2525
10FREE10
15OFF15
FALL3030
Was this page helpful?
Questions? Contact us.
View developer tutorials on YouTube.
Check out our product changelog.
Powered by Markdoc
You can unsubscribe at any time. Read our privacy policy.
On this page
Subscriptions
Customers
Products and Prices
Invoices
Coupons and discounts
Stripe Shell
Test mode
▗▄ ▄▟█ █▀▀ ▗▟████▙▖ ██████ ███▗▟█ ███ ███▗▟██▙▖ ▗▟█████▙▖ ███▖ ▀▀ ███ ███▀▀▀ ███ ███▀ ███ ███ ███ ▝▜████▙▖ ███ ███ ███ ███ ███ █████████ ▄▄ ▝███ ███ ▄ ███ ███ ███▄ ███ ███ ▄▄ ▝▜████▛▘ ▝▜███▛ ███ ███ ███▝▜██▛▘ ▝▜█████▛▘ ███ ▀▘
Welcome to the Stripe Shell! Stripe Shell is a browser-based shell with the Stripe CLI pre-installed. Login to Stripe docs and press Control + Backtick on your keyboard to start managing your Stripe resources in test mode. - View supported commands: - Find webhook events: - Listen for webhook events: - Call Stripe APIs: stripe [api resource] [operation] (e.g. )
The Stripe Shell is best experienced on desktop.
$