Querying transactions
Use Stripe Sigma to create custom reports for charges, refunds, disputes, and more.
There are two groups of tables within Stripe Sigma’s schema that contain suitable data for reporting on your account’s balance activity. The Payment Tables section contains tables that represent funds that flow between your customers and your 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. Use balance transactions 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 is 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, currency, source_id, type from balance_transactions order by day desc limit 5
day | id | amount | currency | source_id | type |
---|---|---|---|---|---|
2020-04-16 | txn_o46V6I32dEGHWQ9 | -1,000 | usd | re_kU4mKp4r81Djsqj | refund |
2020-04-16 | txn_3HA0A9Mhj5j8Qiz | 1,000 | usd | ch_cJkVpWp5KUX2H8q | charge |
2020-04-16 | txn_kyxk5VOz482k3MX | 1,000 | usd | ch_3rYXJNlKXDY8kd1 | charge |
2020-04-16 | txn_JQEPpO0ZMALCFIh | 1,000 | eur | ch_QeOuSojMyhFqApI | charge |
2020-04-16 | txn_PBZk0CWNWb55ggv | -1,000 | usd | re_6L5EZA8KHP5GYh1 | 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 balance 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 |
---|---|---|---|---|
2020-04-16 | txn_E5D7XxMl64sYojP | 1,000 | 59 | stripe_fee |
2020-04-16 | txn_hbtgQszrSfrR7Ug | 1,000 | 59 | stripe_fee |
2020-04-16 | txn_yk7vSFYhybKPx97 | 1,000 | 59 | stripe_fee |
2020-04-16 | txn_7zTO0MZNZeosVAd | 1,000 | 59 | stripe_fee |
2020-04-16 | txn_h0sPjaLx8idYeZR | 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 as well as 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 |
---|---|---|---|---|
2020-04-16 | ch_2qyQ9tnGxl212OG | Visa | card_declined | Your card was declined. |
2020-04-16 | ch_DayxhNqt6vwIbw2 | MasterCard | card_declined | Your card does not support this type of purchase. |
2020-04-16 | ch_11xwvYU43WhUvJP | Visa | card_declined | Your card has insufficient funds. |
2020-04-16 | ch_NvQvPyZGKrYlJ6K | Visa | card_declined | Your card was declined. |
2020-04-16 | ch_43yrDChIo8t0MVr | 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). Use this table if you’re creating charges using customers (e.g., with saved payment information). It’s also useful if you’re making use of subscriptions.
The following example retrieves a list of failed charges, with the ID and email address for each customer.
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 and amount. Only balance transactions created after a certain date are returned.
select date_format(date_trunc('day', balance_transactions.created), '%Y-%m-%d') as day, balance_transactions.source_id, refunds.charge_id, balance_transactions.amount, 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 |
---|---|---|---|
2020-04-16 | re_Ue4UwZESkYeugxF | ch_83U7Yi86rB167Wr | -1,000 |
2020-04-16 | re_Z7NQ9dvcogowSly | ch_uZUB2L5oXbhR9eE | -1,000 |
2020-04-16 | re_DrHG6EeJpXtEmip | ch_lQZqXHRoj9pQiZg | -1,000 |
2020-04-16 | re_aaBAWAVR9iH71xu | ch_X5jX2dfbtKBhHNx | -1,000 |
2020-04-16 | re_hEWz53P84FcUGwW | ch_wHesP5E6fxGYkvm | -1,000 |
Partial capture refunds
If you’re using auth and capture and capturing only some of the authorized amount, this is represented by both charges and refunds. When a charge is authorized, a charge
and associated balance transaction for the full amount are created. After a partial capture has been completed, the un-captured amount is released and represented by a refund
with a reason
field of partial_capture
and an associated balance transaction.
For instance, if you authorize a $10 charge but only capture $7, a charge
is first created for $10. A refund
with the reason partial_capture
is then created for the remaining $3.
Take this 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. Use the refund’s reason
field to filter out partial capture refunds when retrieving payment information.
select balance_transactions.id, balance_transactions.amount from balance_transactions inner join refunds on refunds.id=balance_transactions.source_id 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 the outcome of the ZIP and CVC checks.
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 |
---|---|---|---|---|---|
2020-04-16 | dp_gtBQ4mSAkIPfK18 | ch_VUwJKqf1gfGtgR5 | 1,000 | pass | |
2020-04-16 | dp_Tir32LJi1SCJHCC | ch_SLPBFxWdxOftNin | 1,000 | pass | fail |
2020-04-16 | dp_9Kw7hrbHyshWP62 | ch_Rslo8KaVZ21mR2b | 1,000 | fail | fail |
2020-04-16 | dp_4vQmBaVIhSGFjph | ch_2V3KNQ543l291Zg | 1,000 | pass | |
2020-04-16 | dp_Vc5hRPuG27lqV2Q | ch_DXsf3Ewl2RwT1lj | 1,000 | pass |
Using Stripe Sigma to create reports about your disputes can help you identify fraudulent payments, which can be prevented using Stripe 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. It 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_zZNYBShfkq4BPto | charge | 941 | po_5GmnMsYYpFzoXmQ | 2017-05-24 |
2017-05-22 | ch_fmMgSOhCno4f64y | charge | 941 | po_fiNKQ0Fs2IEVDQH | 2017-05-24 |
2017-05-21 | ch_RUBPkXUcbtT2mg7 | charge | 941 | po_mVcxO9TMhVGIGC6 | 2017-05-23 |
2017-05-21 | ch_njdivfI8lPjpUfI | charge | 941 | po_Ilescw5yA9kQzO9 | 2017-05-23 |
2017-05-21 | ch_REcPCThI3LM2nkB | charge | 941 | po_cUFNFgvD8vap2HA | 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.