Querying Subscriptions

    Use Sigma to retrieve information about subscriptions.If you need help after reading this, search our documentation or check out answers to common questions. You can even chat live with other developers in #stripe on freenode.

    Subscriptions are made up of different components that work together to provide periodic billing, with different aspects of subscription data available across a number of tables. All subscription-specific tables are found in the Subscription section of the schema, with the primary table being subscriptions.

    To explore subscription data further, you can make use of the additional tables that represent the components of subscriptions, such as plans, invoices, or coupons. In addition, the customers table is a fundamental part of subscriptions, 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 can be retrieved through the API or available in the Dashboard. Every subscription that has been created on your account can be reported on.

    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 email
    sub_TLpkEFBFEi47Zb0 cus_C6kfAiLk2CkQ1k8 jenny.rosen@example.com
    sub_n2F5fGf2de2HG2U cus_GGOKSjhQigNKs1Q noah.wilson@example.com
    sub_JtYGqXSzm8QbBGt cus_fHZPqrhNK2W07kt joshua.miller@example.com
    sub_cpBrebPvpUxT1xw cus_qhrG814UduBgQ1G madison.jackson@example.com
    sub_C2peo4DgD0U7qX6 cus_IaYTLQrYyE94qia elijah.smith@example.com

    Customers

    Data about Customer objects is contained in the customers table (this is not part of the Subscription Tables group). It is commonly used as part of subscription-based reports and can be joined to a number of subscriptions 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.plan_id
    from subscriptions
    inner join customers
    on customers.id=subscriptions.customer_id
    where subscriptions.status = 'trialing'
    limit 5
    id email plan_id
    cus_b09olDawmhpK7iR jenny.rosen@example.com ruby-pro-522
    cus_liD0hWLtzDYoBRq noah.wilson@example.com ruby-pro-522
    cus_62oA24T4zC3cV6k richard.jones@example.com gold-basic-221
    cus_COoRgtyHlF1JwYR madison.jackson@example.com gold-basic-221
    cus_7xE4J9n2QS2a3C1 elijah.smith@example.com silver-pro-498

    Plans

    Plans are used as the basis of subscriptions, setting out the base cost, billing interval, and currency. When viewing data from the subscriptions table, subscriptions.plan_id can be joined to plans.id . The following example returns a list of active subscriptions along with the plan name and its statement descriptor.

    select
      subscriptions.id,
      plans.name
    from subscriptions
    inner join plans
      on subscriptions.plan_id=plans.id
    where
      subscriptions.status = 'active'
    limit 10
    id name
    sub_ZwHyWRxG6LST5VI ruby-pro-522
    sub_pxtpOcGszA3wxWa gold-basic-221
    sub_z3nPvyO7EKNbXtz silver-pro-498
    sub_SkorQIo2s7lFrML diamond-mid-244
    sub_0By9p5b4o1YrxL0 ruby-standard-196

    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, as well as 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, as well as 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 (e.g., sub_6ytD1IgXy3JECLS) or invoice item (e.g., ii_N3wQZEEfMaNuKdP) that the line item corresponds to. The source_type column reflects whether the line items represents 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 should be 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 of 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_gEehvfWixWPU2YS ch_ENdORXuKJMAQqY1 1999
    in_zyESLu2kMNiVJRP ch_FjDAYoknNAd2QYm 1999
    in_0VLBTg1tYgi92Js 1999 ch_xOAPLiDW4vsNQHA
    in_5eqIVn3dMFynvyl 1999 ch_QxBgk8bzO4uMteB
    in_GdkYOxjrKvtiVah 1999 ch_n8Uto71F4WCazrO

    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 should be 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 was 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
    id amount_off percent_off
    10FF 10
    SUMMER25 25
    10FREE 10
    15OFF 15
    FALL30 30