Querying Transactions

    Use Sigma to create custom reports for charges, refunds, disputes, and more. 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.

    There are a two groups of tables within Stripe Sigma’s schema that contain suitable data for you to report on your account’s balance activity. The Payment Tables section contains tables that represent funds that flow between your customers and Stripe account, such as charges or refunds. Information about transfers of your Stripe account balance to your bank account (payouts) are listed within the Transfer Tables section.

    The balance_transactions table is our recommended starting point for accounting purposes. Its biggest advantage over using separate tables (such as charges or refunds) is that it provides a ledger-style record of every type of transaction that comes into, or flows out of, your Stripe account’s balance. Balance transactions are used to generate the most frequently used reports and greatly simplify how you report on financial activity. Some common types of balance transactions include:

    • charges
    • refunds
    • transfers
    • payouts
    • adjustments
    • application_fees

    Each balance transaction row represents an individual Balance_transaction object that, once created, does not change. For instance, when a charge is created, a corresponding balance transaction of type charge is also created. If this charge is refunded at any time, a separate balance transaction of type refund is also created––the original balance transaction is not modified. Similarly, when a payout is sent to your bank account (represented as a transfer), a balance transaction for it is also created.

    The following example query uses this table to retrieve some information about the five most recent balance transactions.

    select
      date_format(created, '%Y-%m-%d') as day,
      id,
      amount,
      fee,
      currency,
      source_id,
      type
    from balance_transactions
    order by day desc
    limit 5
    day id amount fee currency source_id type
    2017-06-27 txn_W3ykqWnBVFJL5Q6 -1,000 -59 usd re_XGAgz3nqVaE8dPr refund
    2017-06-27 txn_ZMBnb1qg9zF0suP 1,000 59 usd ch_vZbG8goongralUv charge
    2017-06-27 txn_Ahw2YKgCP9xy3uf 1,000 59 usd ch_8LL1w38wfuFQ71Y charge
    2017-06-27 txn_eaSwgQIlOGvqE3r 1,000 59 eur ch_JKEySwjZZ186WKA charge
    2017-06-27 txn_wNpbxBKuxqRwtLx -1,000 -59 usd re_K1Urf9O6wtRQAAm refund

    The most common financial summaries can be calculated by joining the balance_transactions table with other tables containing the appropriate information. Some of our query templates (such as Daily report, and Monthly summary and balance) work by joining this table to others.

    Balance transaction fee details

    Fee information about each individual balance transaction is provided in the balance_transaction_fee_details table. Joining this table to balance_transactions in the manner below allows you to return fee information for each balance transaction.

    The following query joins the balance_transactions and balance_transaction_fee_details tables together. Each balance transaction item returned includes the amount, fee, type of fee applied, and a description of the fee.

    select
      date_format(date_trunc('day', balance_transactions.created), '%Y-%m-%d') as day,
      balance_transactions.id,
      balance_transactions.amount,
      balance_transactions.fee,
      balance_transaction_fee_details.type
    from balance_transactions
    inner join balance_transaction_fee_details
      on balance_transaction_fee_details.balance_transaction_id=balance_transactions.id
    order by day desc
    limit 5
    day id amount fee type
    2017-06-27 txn_tHf2SvxOv45C9T9 1,000 59 stripe_fee
    2017-06-27 txn_WGEJQN9o0Bdpg8c 1,000 59 stripe_fee
    2017-06-27 txn_kqWbASKGzzQpf2y 1,000 59 stripe_fee
    2017-06-27 txn_6dV5C4zt7tU3uil 1,000 59 stripe_fee
    2017-06-27 txn_axLBQoU8x9oZRpw 1,000 59 stripe_fee

    Charges

    The charges table contains data about Charge objects. This table is best suited for queries that focus on charge-specific information rather than for accounting or reconciliatory purposes. It also supplements accounting reports with additional customer data. For example, The Payment card mix template query uses the charges table to report on the different types of cards your customers have used.

    You can join the charges table to a number of others to retrieve more information with your queries.

    The following example uses the charges table to report on failed charges, returning the card brand and type, as well as the failure code and message.

    select
      date_format(date_trunc('day', created), '%Y-%m-%d') as day,
      id,
      card_brand,
      failure_code,
      failure_message
    from charges
      where status = 'failed'
    order by day desc
    limit 5
    day id card_brand failure_code failure_message
    2017-06-27 ch_OYrOnLYsjR7MUfo Visa card_declined Your card was declined.
    2017-06-27 ch_s30UUQMyi09AZix MasterCard card_declined Your card does not support this type of purchase.
    2017-06-27 ch_ylXPs5GmUPYx623 Visa card_declined Your card has insufficient funds.
    2017-06-27 ch_DACBd3PeJy1fB0W Visa card_declined Your card was declined.
    2017-06-27 ch_Y63xiS86XZnLn2C MasterCard card_declined Your card was declined.

    Customers

    Data about Customer objects is contained in the customers table (this is not part of the Payment Tables group). This can be used if you’re creating charges using customers (e.g., with saved payment information). It is also useful if you’re making use of subscriptions.

    The following example retrieves a list of failed charges. The ID and email address for each customer is retrieved.

    select
      date_format(date_trunc('day', charges.created), '%Y-%m-%d') as day,
      customers.id,
      customers.email,
      charges.id
    from charges
    inner join customers
    on customers.id=charges.customer_id
    where charges.status = 'failed'
    order by day desc
    limit 5

    Refunds

    Charges and refunds are separate objects within the API. When a charge is refunded, a Refund object is created. This data is available within the refunds table and provides in-depth information about refunds that have been performed. Similar to reporting on charges, a best practice is to start with information about balance transactions. If necessary, you can then gather additional details using the refunds table.

    The refunds table can be joined to the balance_transactions and charges tables to further explore refund data.

    The following example joins the balance_transactions and refunds tables together using the refunds.balance_transaction_id and balance_transactions.id columns. Each balance transaction item returned is a refund, displaying the associated charge ID, the gross and net amounts refunded, and the Stripe fee returned. Only balance transactions created after a certain date are returned (and limited).

    select
      date_format(date_trunc('day', balance_transactions.created), '%Y-%m-%d') as day,
      balance_transactions.source_id,
      refunds.charge_id,
      balance_transactions.amount,
      balance_transactions.net,
      balance_transactions.fee
    from balance_transactions
    inner join refunds
    on refunds.balance_transaction_id=balance_transactions.id
      where balance_transactions.type = 'refund'
    order by day desc
    limit 5
    day source_id charge_id amount net fee
    2017-06-27 re_7fwkhoz22uHwltO ch_mlwWqDcmpxZ5Kmo -1,000 -941 -59
    2017-06-27 re_QKmOkKFHKuanzLE ch_CHcF78YNft7XMao -1,000 -941 -59
    2017-06-27 re_WNTwpxpxUC2g1w9 ch_iNNoTdEUG4Mv0C5 -1,000 -941 -59
    2017-06-27 re_VKfOxsPVWyMegsl ch_0F5RLN2DriZOJFm -1,000 -941 -59
    2017-06-27 re_kCeTGk4Wl6kqy8y ch_nhqWqtJBCQgn4fi -1,000 -941 -59

    Partial capture refunds

    If you’re using auth and capture and capture only some of the authorized amount, these are represented by both charges and refunds. When a charge is authorized, a Charge object for the full amount is created. After a partial capture has been completed, any un-captured amount is released and represented by a separate Refund object. For instance, if you authorize a $10 charge but only capture $7, a charge is created for $10 while the remaining $3 is represented as a refund.

    This should be taken into account if your business is performing auth and capture charges and you’re creating reports to review customer refund rates. Without consideration, auth and capture can misrepresent the number of refunds on your account. Partial capture refunds have a reason of partial_capture, so filtering these from your refund reports ensures they more accurately reflect the refunds you’re performing.

    select
      id,
      amount
    from refunds
      where reason != 'partial_capture'
    limit 5

    Disputes

    The disputes table contains data about all disputes on your account. Each row represents a Dispute object, created when a charge is disputed. Each dispute also includes any available data about any dispute evidence that may have been submitted.

    The following example provides some preliminary information about the five most recent disputes that were lost. It joins the disputes and charges tables together using the disputes.charge_id and charges.id columns. Along with a dispute ID, each row contains an associated charge ID, the amount, and what the outcome of the ZIP and CVC checks were.

    select
      date_format(date_trunc('day', disputes.created), '%Y-%m-%d') as day,
      disputes.id,
      disputes.charge_id,
      disputes.amount,
      charges.card_address_zip_check as zip,
      charges.card_cvc_check as cvc
    from disputes
    inner join charges
    on charges.id=disputes.charge_id
    where disputes.status = 'lost'
    and disputes.reason = 'fraudulent'
    order by day desc
    limit 5
    day id charge_id amount zip cvc
    2017-06-27 dp_cML6ahbt2hGL2TM ch_VnWdBrxjv9VT4TG 1,000 pass
    2017-06-27 dp_LMThvJ4a1m61IFO ch_XuzsdQ0p36h5lRQ 1,000 pass fail
    2017-06-27 dp_g0bS8k193GgFYIy ch_vbyXRhy5p9VSqCZ 1,000 fail fail
    2017-06-27 dp_bT4ZRtTRCrSBLJa ch_pGHlvTkPGfk4WwN 1,000 pass
    2017-06-27 dp_6Uk22arX0HVUMNm ch_4qDirzUrnzE7bj4 1,000 pass

    Using Sigma to create reports about your disputes can help you identify the types of payments that can otherwise be prevented using Radar.

    Transfers and payouts

    The transfers table contains data about payouts made from your Stripe balance to your bank account. You can use this table to reconcile each payout with the specific charges, refunds, and adjustments that it’s made up of, as long as you’re using automatic payouts.

    For Connect platforms, this table also includes data about transfers of funds to connected Stripe accounts.

    If you are performing payouts manually, the amount in each payout to your bank account is arbitrary. As such, it cannot be reconciled to specific balance transactions and only reflects the amount you requested to pay out to your bank account.

    The following example joins the balance_transactions and transfers tables together. This returns a list of charges and refunds, the payout they relate to, and the date that the payout was scheduled to arrive into your bank account.

    select
      date_format(date_trunc('day', balance_transactions.created), '%Y-%m-%d') as bt_created,
      balance_transactions.source_id,
      balance_transactions.type,
      balance_transactions.net as net_amount,
      balance_transactions.automatic_transfer_id as transfer_id,
      date_format(date_trunc('day', transfers.date), '%Y-%m-%d') as transfer_date
    from balance_transactions
    inner join transfers
    on balance_transactions.automatic_transfer_id=transfers.id
    where balance_transactions.type = 'charge'
    and balance_transactions.type != 'refund'
    order by bt_created desc
    limit 5
    day source_id type net_amount transfer_id transfer_date
    2017-05-22 ch_YIbRaDnaY1iGr26 charge 941 tr_msyze8crAHEaGxW 2017-05-24
    2017-05-22 ch_G9Chvk2UoXGQHmY charge 941 tr_aMkFciFpodYZKMO 2017-05-24
    2017-05-21 ch_G6WcBBPkZdITNQI charge 941 tr_j9E1wQIi0gDSOPD 2017-05-23
    2017-05-21 ch_KVEmCXkDVtwLS63 charge 941 tr_DIFaCw7v9ZXqKi5 2017-05-23
    2017-05-21 ch_2QjX7OVzMrQCTVX charge 941 tr_pvhiRt4f7XTddjv 2017-05-23

    Transfer reversals

    A payout (or transfer to a connected Stripe account) that has been created manually can be reversed if it has not yet been paid out, with any funds returned to your account’s available balance. These are represented as Transfer_reversal objects and reside in the transfer_reversals table.

    Transfer reversals only apply to payouts and transfers that have been created manually––automatic payouts cannot be reversed.