Sign in
An image of the Stripe logo
Create account
Sign in
Home
Payments
Business operations
Financial services
Developer tools
No-code
All products
Home
Payments
Business operations
Home
Payments
Business operations
Financial services
Developer tools
Overview
Fraud detection
Stripe data
    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
Financial reports
Payment authentication reports
Revenue recognition
Startup incorporation
Climate
Identity
Tax
Financial Connections
Account
Dashboard
HomeBusiness operationsStripe data

Query transactional data

Create custom reports for charges, refunds, disputes, and more.

Use the data in the tables within the schema for reporting on your account’s balance activity. The tables in the Payment Tables sections represent funds that flow between your customers and your Stripe account, such as charges or refunds. The Transfer Tables section has information about transfers of your Stripe account balance to your bank account (payouts).

Use the balance_transactions table as a starting point for accounting purposes. Unlike using separate tables (such as charges or refunds), it provides a ledger-style record of every type of transaction that comes into or flows out of your Stripe account balance. Use balance transactions to generate frequently used reports and to 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 doesn’t change after it’s created. For example, creating a charge also creates a corresponding balance transaction of type charge. Refunding this charge creates a separate balance transaction of type refund—but it doesn’t modify the original balance transaction. Similarly, receiving a payout in your bank account (represented as a transfer) creates a balance transaction.

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

select date_format(created, '%m-%d-%Y') as day, id, amount, currency, source_id, type from balance_transactions order by day desc limit 5
dayidamountcurrencysource_idtype
4/1/2023txn_XJdXuf0RGIseSpF-1,000usdre_GUT8ygfdp7JTBDhrefund
4/1/2023txn_azcRh2opDoiOujV1,000usdch_sLj9k414je2E9Qrcharge
4/1/2023txn_YSND0jbfT1QA65h1,000usdch_sDbLUYcmJghcCO9charge
4/1/2023txn_D5uphxiPwcvhuvC1,000eurch_kOGmt0UQlSyRTB2charge
4/1/2023txn_LamyC4ADCIPXpvc-1,000usdre_KfCE2ydYH7V4jFmrefund

You can calculate the most common financial summaries by joining the balance_transactions table with other tables containing the appropriate information. Some of our query templates (such as the daily balance and monthly summary and balance) work by joining this table to others.

Balance transaction fee details

The balance_transaction_fee_details table provides fee information about each individual balance transaction. 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), '%m-%d-%Y') 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
dayidamountfeetype
4/1/2023txn_lbSf6uHUKTkRBnQ1,00059stripe_fee
4/1/2023txn_YEBokvSWPbA5qp01,00059stripe_fee
4/1/2023txn_sxuWHUWjWe0wAzg1,00059stripe_fee
4/1/2023txn_wxL5wLDN87N4xCt1,00059stripe_fee
4/1/2023txn_z67wgimZXrtOim01,00059stripe_fee

Charges

The charges table contains data about Charge objects. Use this table 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 breakdown 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 a failure code and message.

select date_format(date_trunc('day', created), '%m-%d-%Y') as day, id, card_brand, failure_code, failure_message from charges where status = 'failed' order by day desc limit 5
day idcard_brandfailure_codefailure_message
4/1/2023ch_3KmSs848P3Zsp4BVisacard_declinedYour card was declined.
4/1/2023ch_8P4s4XlbPUSNHueMasterCardcard_declinedYour card doesn’t support this type of purchase.
4/1/2023ch_XrHsY0a6WlrcYZ9Visacard_declinedYour card has insufficient funds.
4/1/2023ch_GbdgyBZXseP6PYwVisacard_declinedYour card was declined.
4/1/2023ch_QU5sj8fQtmLsxPBMasterCardcard_declinedYour card was declined.

Customers

The customers table contains data about Customer objects (this table isn’t part of the Payment Tables group). Use it if you’re creating charges using customers (for example, with saved payment information). It’s also useful if you’re using 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), '%m-%d-%Y') 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. Refunding a charge creates a Refund. This data is available in the refunds table and provides in-depth information about completed refunds. 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.

You can join the refunds table 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 charge ID and amount. Only balance transactions created after a certain date are returned.

select date_format(date_trunc('day', balance_transactions.created), '%m-%d-%Y') 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
daysource_idcharge_idamount
4/1/2023re_4uhufOJbxoQ5P6Ach_ZrxJwwYu6u7MqGp-1,000
4/1/2023re_DlV8ItkwphEGUbRch_5OTSjlFEA0g1Jgr-1,000
4/1/2023re_0tT6bpIEKPEeD6tch_l4hkWFG7NKJHXoh-1,000
4/1/2023re_UtrM85r5taAVxPUch_2J8zGeWyKmXGeZZ-1,000
4/1/2023re_NrKU9VFgdwdkOQBch_TiiQTXrGx5fk6pH-1,000

Partial capture refunds

Using auth and capture and capturing only some of the authorized amount creates both a charge and a refund. An authorized charge creates a charge and an associated balance transaction for the full amount. After a partial capture is complete, the uncaptured amount is released and a refund is created with a reason field of partial_capture along with an associated balance transaction.

For example, authorizing a 10 USD charge but only capturing 7 USD creates a charge for 10 USD. This also creates a refund with the reason partial_capture for the remaining 3 USD.

Take this into account if your business is performing auth and capture charges as 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, which is created when a charge is disputed. Each dispute also includes any available data about dispute evidence that’s been submitted.

The following example provides some preliminary information about the five most recent lost disputes. 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), '%m-%d-%Y') 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
dayidcharge_idamountzipcvc
4/1/2023dp_LvNZPA1BsvixVIKch_hQWER3TWy5aOt4u1,000pass
4/1/2023dp_Wz3iX24817r04CWch_XtM7MJglOFs9Vg91,000passfail
4/1/2023dp_n4PVFkoVzw7xHTqch_FM14FKxKFaUudcM1,000failfail
4/1/2023dp_0YIgMYf5pXgdhklch_M1FJST1P2ppgGfI1,000pass
4/1/2023dp_YRh8rlacHB4DRUCch_111SFLciAvEoQHC1,000pass

Using Sigma or Data Pipeline to create reports about your disputes can help you identify fraudulent payments, which you can prevent by 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 comprise it, 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’re performing payouts manually, the amount in each payout to your bank account is arbitrary. As such, you can’t reconcile it to specific balance transactions and it 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 is scheduled to arrive into your bank account.

select date_format(date_trunc('day', balance_transactions.created), '%m-%d-%Y') 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), '%m-%d-%Y') 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
daysource_idtypenet_amounttransfer_idtransfer_date
05-22-2017ch_rDXq0L2iuAlCD4qcharge941po_I7izZje25TpFTmh05-24-2017
05-22-2017ch_Aw0EZxzpPVu8sSmcharge941po_WSvqRmpGidu8gl705-24-2017
05-21-2017ch_4semGrGs6zUPZvqcharge941po_swdYasZWWXywxWC05-23-2017
05-21-2017ch_kjUGlhq4BEjkHEDcharge941po_tPCWX2GVGCkAZFX05-23-2017
05-21-2017ch_tZNyX4zlwoucFfMcharge941po_wbr92ejRAa9TcR705-23-2017

Payouts before 04-06-2017 have a TRANSFER_ID with a tr_ prefix.

Transfer reversals

You can reverse a manually created payout (or transfer to a connected Stripe account) if it hasn’t been paid out yet by using funds returned to the available balance in your account. 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—you can’t reverse automatic payouts.

Was this page helpful?
Need help? Contact Support.
Watch our developer tutorials.
Check out our product changelog.
Questions? Contact Sales.
Powered by Markdoc
You can unsubscribe at any time. Read our privacy policy.
On this page
Balance transaction fee details
Charges
Customers
Refunds
Partial capture refunds
Disputes
Transfers and payouts
Transfer reversals
Stripe Shell
Test mode
Welcome to the Stripe Shell! Stripe Shell is a browser-based shell with the Stripe CLI pre-installed. Login to your Stripe account and press Control + Backtick on your keyboard to start managing your Stripe resources in test mode. - View supported Stripe 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.
$