Query Connect payments data

Inspect individual Connect charges and analyse transaction-level revenue, fees, costs and margin.

The connect_payments table provides transaction-level payment data across connected account types, fee-payer configurations and Connect charge flows. You can use it to investigate individual charges and analyse payment volume, revenue, itemised fees, network costs and margin. Each row shows the current cumulative state of a charge, with refunds and disputes represented as signed amounts on the charge row. Learn more about the features for Connect analytical data.

Before you begin

Review the table details before you get started:

Detail	Value
Table name	`connect_payments`
Schema structure	Wide (one row per charge in settlement currency)
Grain	One row per `charge_id` and `currency`
Date coverage	January 2024 to present
Cadence	Daily
Freshness	Approximately T-3 days

Get started

The following query retrieves the charges for a connected account in a given month. Use it to investigate account-level changes in the financial summary or to inspect revenue and margin for individual charges.

SELECT
 charge_id,
 primary_charge_created_date,
 payment_method_type,
 card_brand,
 card_network,
 fees_payer,
 currency,
 amount,
 refunded_amount,
 disputed_amount,
 revenue__application_fee_amount,
 revenue__revshare_amount,
 margin_amount,
 margin_amount_usd,
 ROUND(
 CAST(1.0 AS double) * margin_amount_usd
 / NULLIF(amount_usd, 0) * 10000,
 1
 ) AS margin_bps
FROM connect_payments
WHERE connected_account_merchant_id = 'acct_XXXX'
 AND DATE_TRUNC('month', primary_charge_created_date) = DATE '2024-07-01'
ORDER BY primary_charge_created_date DESC

Schema overview

The connect_payments table is a wide, current-cumulative-state fact table. The schema shows different types of data in the following ways:

Multicapture charges are collapsed into one row that reflects the amounts of all captures.
When separate charges and transfers involve multiple connected accounts, connected _ account _ merchant _ id identifies the first connected account.
Standalone charges made by connected accounts appear as a single row.
All monetary values use the charge’s settlement currency and are expressed in major units. For example, 23. 97 represents 23.97 USD when currency is usd .
Refund, dispute, reversal and fee fields are negative.
For cross-currency charges, settlement amounts can differ from amounts in the charge’s original currency.

Identity and transaction columns

These columns identify the payment, its Connect relationship, payment method details, timing and capture state. Use connected_account_merchant_id to join with the connected accounts and financial summary datasets where applicable.

Column	Type	Description

`charge_id`	string	Stripe charge or unified payment identifier associated with the payment.
`currency`	string	ISO 4217 currency code in which the payment settled.
`customer_id`	string	Stripe `Customer` identifier associated with the payment, when available.
`charge_flow`	string	Connect charge flow used for the payment.
`connected_account_merchant_id`	string	Stripe account identifier of the connected account attributed to the payment.
`connected_account_merchant_country`	string	ISO 3166-1 alpha-2 country code of the attributed connected account.
`destination_merchant_id`	string	Stripe merchant identifier receiving the destination transfer, when applicable.
`on_behalf_of_merchant_id`	string	Stripe merchant identifier represented by the payment, when applicable.
`card_brand`	string	Card brand for card payments, when available.
`card_funding`	string	Card funding type, such as credit, debit or prepaid.
`card_country`	string	Issuing country of the card, when available.
`card_network`	string	Card network used to process the payment, when available.
`payment_method_type`	string	User-facing payment method name, such as `card` or `sepa_debit`.
`fees_payer`	string	Party billed for the applicable payment fees.
`primary_charge_created_date`	timestamp	Timestamp when the primary charge was created.
`captured_at`	timestamp	Timestamp when the payment was captured, when applicable.
`amount`	decimal	Payment amount in the settlement currency.
`amount_usd`	decimal	Payment amount converted to USD.
`is_payment_successful`	boolean	Whether the payment completed successfully.
`number_multi_captures`	integer	Number of captures associated with the payment.
`is_multi_capture`	boolean	Whether the payment was settled through multiple captures.

Transfer, refund, dispute and reversal columns

These columns describe the cumulative transfer, refund, dispute and reversal activity associated with the payment.

Column	Type	Description

`transfer_count`	integer	Number of transfers associated with the payment.
`transfer_ids`	string	Serialised list of transfer identifiers associated with the payment.
`revenue__transfer_amount`	decimal	Total amount transferred to the connected account.
`revenue__transfer_currency`	string	Currency of the transfer amount.
`revenue__transfer_amount_usd`	decimal	Total transfer amount converted to USD.
`first_transfer_created_at`	timestamp	Timestamp when the first associated transfer was created.
`refund_ids`	string	Serialised list of refund identifiers associated with the payment.
`refunded_amount`	decimal	Total amount refunded for the payment.
`refunded_currency`	string	Currency of the refunded amount.
`refunded_amount_usd`	decimal	Total refunded amount converted to USD.
`refunds_count`	integer	Number of refunds associated with the payment.
`refunded_at`	timestamp	Timestamp when the payment was refunded, when applicable.
`dispute_ids`	string	Serialised list of dispute identifiers associated with the payment.
`disputed_amount`	decimal	Total amount disputed for the payment.
`disputed_currency`	string	Currency of the disputed amount.
`disputed_amount_usd`	decimal	Total disputed amount converted to USD.
`dispute_count`	integer	Number of disputes associated with the payment.
`disputed_at`	timestamp	Timestamp when the payment was disputed, when applicable.
`reversal_ids`	string	Serialised list of transfer-reversal identifiers associated with the payment.
`revenue__transfer_reversal_amount`	decimal	Total transfer reversal amount associated with the payment.
`revenue__transfer_reversal_currency`	string	Currency of the transfer reversal amount.
`revenue__transfer_reversal_amount_usd`	decimal	Total transfer reversal amount converted to USD.
`reversal_count`	integer	Number of transfer reversals associated with the payment.
`first_transfer_reversal_created_at`	timestamp	Timestamp when the first associated transfer reversal was created.

Revenue columns

Revenue fields contain the platform’s earnings and transfer amounts associated with the payment.

Column	Type	Description

`number_of_app_fees`	integer	Number of application fees associated with the payment.
`number_of_app_fee_refunds`	integer	Number of application fee refunds associated with the payment.
`revenue__application_fee_amount`	decimal	Application fee collected on the payment.
`revenue__application_fee_currency`	string	Currency of the application fee.
`revenue__application_fee_amount_usd`	decimal	Application fee amount converted to USD.
`revenue__application_fee_refunded_amount`	decimal	Application fee refund amount associated with the payment.
`revenue__application_fee_refunded_currency`	string	Currency of the application fee refund amount.
`revenue__application_fee_refunded_amount_usd`	decimal	Application fee refund amount converted to USD.
`revenue__implicit_application_fee_amount`	decimal	Implicit application fee amount collected on the payment.
`revenue__implicit_application_fee_currency`	string	Currency of the implicit application fee.
`revenue__implicit_application_fee_amount_usd`	decimal	Implicit application fee amount converted to USD.
`revenue__revshare_amount`	decimal	Revenue share amount earned by the platform.
`revenue__revshare_currency`	string	Currency of the revenue share amount.
`revenue__revshare_amount_usd`	decimal	Revenue share amount converted to USD.
`revenue__application_fee_instant_payouts_amount`	decimal	Application fee amount associated with instant payouts.
`revenue__application_fee_instant_payouts_currency`	string	Currency of the application fee instant payout amount.
`revenue__application_fee_instant_payouts_amount_usd`	decimal	Application fee instant payout amount converted to USD.

Network cost columns

These columns itemise card network costs and their taxes.

Column	Type	Description

`network_costs__subtotal_amount`	decimal	Card network cost subtotal for the payment.
`network_costs__subtotal_currency`	string	Currency of the card network cost subtotal.
`network_costs__subtotal_amount_usd`	decimal	Card network cost subtotal converted to USD.
`network_costs__tax_amount`	decimal	Tax charged on card network costs.
`network_costs__tax_currency`	string	Currency of the card network cost tax.
`network_costs__tax_amount_usd`	decimal	Card network cost tax converted to USD.
`network_costs__other_subtotal_amount`	decimal	Other card network cost subtotal.
`network_costs__other_subtotal_currency`	string	Currency of other card network costs.
`network_costs__other_subtotal_amount_usd`	decimal	Other card network cost subtotal converted to USD.

Stripe fee columns

These columns itemise Stripe processing, product, and Connect fees. Fee categories include amount, currency and USD-converted variants where available.

Column	Type	Description

`stripe_fees__per_auth_fee_subtotal_amount`	decimal	Stripe per-authorisation fee subtotal.
`stripe_fees__per_auth_fee_subtotal_currency`	string	Currency of the Stripe per-authorisation fee subtotal.
`stripe_fees__per_auth_fee_subtotal_amount_usd`	decimal	Stripe per-authorisation fee subtotal converted to USD.
`stripe_fees__per_auth_fee_tax_amount`	decimal	Tax charged on the Stripe per-authorisation fee.
`stripe_fees__per_auth_fee_tax_currency`	string	Currency of the Stripe per-authorisation fee tax.
`stripe_fees__per_auth_fee_tax_amount_usd`	decimal	Stripe per-authorisation fee tax converted to USD.
`stripe_fees__volume_fee_subtotal_amount`	decimal	Stripe volume-based fee subtotal.
`stripe_fees__volume_fee_subtotal_currency`	string	Currency of the Stripe volume-based fee subtotal.
`stripe_fees__volume_fee_subtotal_amount_usd`	decimal	Stripe volume-based fee subtotal converted to USD.
`stripe_fees__volume_fee_tax_amount`	decimal	Tax charged on the Stripe volume-based fee.
`stripe_fees__volume_fee_tax_currency`	string	Currency of the Stripe volume-based fee tax.
`stripe_fees__volume_fee_tax_amount_usd`	decimal	Stripe volume-based fee tax converted to USD.
`stripe_fees__other_card_payments_fees_subtotal_amount`	decimal	Other Stripe card payment fee subtotal.
`stripe_fees__other_card_payments_fees_subtotal_currency`	string	Currency of other Stripe card payment fees.
`stripe_fees__other_card_payments_fees_subtotal_amount_usd`	decimal	Other Stripe card payment fees converted to USD.
`stripe_fees__other_card_payments_fees_tax_amount`	decimal	Tax charged on other Stripe card payment fees.
`stripe_fees__other_card_payments_fees_tax_currency`	string	Currency of tax on other Stripe card payment fees.
`stripe_fees__other_card_payments_fees_tax_amount_usd`	decimal	Tax on other Stripe card payment fees converted to USD.
`stripe_fees__processing_fees_subtotal_amount`	decimal	Stripe processing fee subtotal.
`stripe_fees__processing_fees_subtotal_currency`	string	Currency of the Stripe processing fee subtotal.
`stripe_fees__processing_fees_subtotal_amount_usd`	decimal	Stripe processing fee subtotal converted to USD.
`stripe_fees__processing_fees_tax_amount`	decimal	Tax charged on Stripe processing fees.
`stripe_fees__processing_fees_tax_currency`	string	Currency of Stripe processing fee tax.
`stripe_fees__processing_fees_tax_amount_usd`	decimal	Stripe processing fee tax converted to USD.
`stripe_fees__dispute_fees_subtotal_amount`	decimal	Stripe dispute processing fee subtotal.
`stripe_fees__dispute_fees_subtotal_currency`	string	Currency of the Stripe dispute processing fee subtotal.
`stripe_fees__dispute_fees_subtotal_amount_usd`	decimal	Stripe dispute processing fee subtotal converted to USD.
`stripe_fees__dispute_fees_tax_amount`	decimal	Tax charged on Stripe dispute processing fees.
`stripe_fees__dispute_fees_tax_currency`	string	Currency of Stripe dispute processing fee tax.
`stripe_fees__dispute_fees_tax_amount_usd`	decimal	Stripe dispute processing fee tax converted to USD.
`stripe_fees__refund_fees_subtotal_amount`	decimal	Stripe refund processing fee subtotal.
`stripe_fees__refund_fees_subtotal_currency`	string	Currency of the Stripe refund processing fee subtotal.
`stripe_fees__refund_fees_subtotal_amount_usd`	decimal	Stripe refund processing fee subtotal converted to USD.
`stripe_fees__refund_fees_tax_amount`	decimal	Tax charged on Stripe refund processing fees.
`stripe_fees__refund_fees_tax_currency`	string	Currency of Stripe refund processing fee tax.
`stripe_fees__refund_fees_tax_amount_usd`	decimal	Stripe refund processing fee tax converted to USD.
`stripe_fees__radar_subtotal_amount`	decimal	Stripe Radar fee subtotal.
`stripe_fees__radar_subtotal_currency`	string	Currency of the Stripe Radar fee subtotal.
`stripe_fees__radar_subtotal_amount_usd`	decimal	Stripe Radar fee subtotal converted to USD.
`stripe_fees__radar_tax_amount`	decimal	Tax charged on Stripe Radar fees.
`stripe_fees__radar_tax_currency`	string	Currency of Stripe Radar fee tax.
`stripe_fees__radar_tax_amount_usd`	decimal	Stripe Radar fee tax converted to USD.
`stripe_fees__adaptive_acceptance_fee_subtotal_amount`	decimal	Stripe Adaptive Acceptance fee subtotal.
`stripe_fees__adaptive_acceptance_fee_subtotal_currency`	string	Currency of the Adaptive Acceptance fee subtotal.
`stripe_fees__adaptive_acceptance_fee_subtotal_amount_usd`	decimal	Adaptive Acceptance fee subtotal converted to USD.
`stripe_fees__adaptive_acceptance_fee_tax_amount`	decimal	Tax charged on Adaptive Acceptance fees.
`stripe_fees__adaptive_acceptance_fee_tax_currency`	string	Currency of Adaptive Acceptance fee tax.
`stripe_fees__adaptive_acceptance_fee_tax_amount_usd`	decimal	Adaptive Acceptance fee tax converted to USD.
`stripe_fees__connect_loss_liability_subtotal_amount`	decimal	Connect loss liability fee subtotal.
`stripe_fees__connect_loss_liability_subtotal_currency`	string	Currency of the Connect loss liability fee subtotal.
`stripe_fees__connect_loss_liability_subtotal_amount_usd`	decimal	Connect loss liability fee subtotal converted to USD.
`stripe_fees__connect_loss_liability_tax_amount`	decimal	Tax charged on the Connect loss liability fee.
`stripe_fees__connect_loss_liability_tax_currency`	string	Currency of Connect loss liability fee tax.
`stripe_fees__connect_loss_liability_tax_amount_usd`	decimal	Connect loss liability fee tax converted to USD.
`stripe_fees__connect_account_initiation_billing_subtotal_amount`	decimal	Connect account initiation billing fee subtotal.
`stripe_fees__connect_account_initiation_billing_subtotal_currency`	string	Currency of the Connect account initiation billing fee subtotal.
`stripe_fees__connect_account_initiation_billing_subtotal_amount_usd`	decimal	Connect account initiation billing fee converted to USD.
`stripe_fees__connect_crossborder_transfer_subtotal_amount`	decimal	Connect cross-border transfer fee subtotal.
`stripe_fees__connect_crossborder_transfer_subtotal_currency`	string	Currency of the Connect cross-border transfer fee subtotal.
`stripe_fees__connect_crossborder_transfer_subtotal_amount_usd`	decimal	Connect cross-border transfer fee converted to USD.
`stripe_fees__connect_instant_payout_subtotal_amount`	decimal	Connect instant payout fee subtotal.
`stripe_fees__connect_instant_payout_subtotal_currency`	string	Currency of the Connect instant payout fee subtotal.
`stripe_fees__connect_instant_payout_subtotal_amount_usd`	decimal	Connect instant payout fee converted to USD.
`stripe_fees__connect_monthly_active_account_subtotal_amount`	decimal	Connect monthly active account fee subtotal.
`stripe_fees__connect_monthly_active_account_subtotal_currency`	string	Currency of the Connect monthly active account fee subtotal.
`stripe_fees__connect_monthly_active_account_subtotal_amount_usd`	decimal	Connect monthly active account fee converted to USD.
`stripe_fees__connect_payout_fixed_subtotal_amount`	decimal	Connect fixed payout fee subtotal.
`stripe_fees__connect_payout_fixed_subtotal_currency`	string	Currency of the Connect fixed payout fee subtotal.
`stripe_fees__connect_payout_fixed_subtotal_amount_usd`	decimal	Connect fixed payout fee converted to USD.
`stripe_fees__connect_payout_volume_subtotal_amount`	decimal	Connect volume-based payout fee subtotal.
`stripe_fees__connect_payout_volume_subtotal_currency`	string	Currency of the Connect volume-based payout fee subtotal.
`stripe_fees__connect_payout_volume_subtotal_amount_usd`	decimal	Connect volume-based payout fee converted to USD.
`stripe_fees__terminal_subtotal_amount`	decimal	Terminal fee subtotal for the payment.
`stripe_fees__terminal_subtotal_currency`	string	Currency of the Terminal fee subtotal.
`stripe_fees__terminal_subtotal_amount_usd`	decimal	Terminal fee subtotal converted to USD.
`stripe_fees__connect_account_volume_billing_subtotal_amount`	decimal	Connect account volume billing fee subtotal.
`stripe_fees__connect_account_volume_billing_subtotal_currency`	string	Currency of the Connect account volume billing fee subtotal.
`stripe_fees__connect_account_volume_billing_subtotal_amount_usd`	decimal	Connect account volume billing fee subtotal converted to USD.
`stripe_fees__connect_account_volume_billing_tax_amount`	decimal	Tax charged on the Connect account volume billing fee.
`stripe_fees__connect_account_volume_billing_tax_currency`	string	Currency of Connect account volume billing fee tax.
`stripe_fees__connect_account_volume_billing_tax_amount_usd`	decimal	Connect account volume billing fee tax converted to USD.
`stripe_fees__other_subtotal_amount`	decimal	Other Stripe fee subtotal.
`stripe_fees__other_subtotal_currency`	string	Currency of other Stripe fees.
`stripe_fees__other_subtotal_amount_usd`	decimal	Other Stripe fee subtotal converted to USD.
`stripe_fees__other_tax_amount`	decimal	Tax charged on other Stripe fees.
`stripe_fees__other_tax_currency`	string	Currency of tax on other Stripe fees.
`stripe_fees__other_tax_amount_usd`	decimal	Tax on other Stripe fees converted to USD.

Proportional non-transaction fee columns

Some platform-level fees aren’t attributable to a specific transaction. These columns allocate them proportionally by country, and payment method scope.

Column	Type	Description

`proportional_nontransaction_fees__global_amount`	decimal	Global proportional non-transactional fees allocated to the payment.
`proportional_nontransaction_fees__global_currency`	string	Currency of global proportional non-transactional fees.
`proportional_nontransaction_fees__global_amount_usd`	decimal	Global proportional non-transactional fees converted to USD.
`proportional_nontransaction_fees__country_amount`	decimal	Country-level proportional non-transactional fees allocated to the payment.
`proportional_nontransaction_fees__country_currency`	string	Currency of country-level proportional non-transactional fees.
`proportional_nontransaction_fees__country_amount_usd`	decimal	Country-level proportional non-transactional fees converted to USD.
`proportional_nontransaction_fees__payment_method_amount`	decimal	Payment method-level proportional non-transactional fees allocated to the payment.
`proportional_nontransaction_fees__payment_method_currency`	string	Currency of payment method-level proportional non-transactional fees.
`proportional_nontransaction_fees__payment_method_amount_usd`	decimal	Payment method-level proportional non-transactional fees converted to USD.
`proportional_nontransaction_fees_amount`	decimal	Total proportional non-transactional fees allocated to the payment.
`proportional_nontransaction_fees_currency`	string	Currency of total proportional non-transactional fees.
`proportional_nontransaction_fees_amount_usd`	decimal	Total proportional non-transactional fees converted to USD.

Margin and additional payment columns

These columns provide transaction margin and additional payment classification details.

Column	Type	Description

`margin_amount`	decimal	Transaction margin amount in the margin currency.
`margin_currency`	string	Currency of the transaction margin.
`margin_amount_usd`	decimal	Transaction margin converted to USD.
`card_category`	string	Card category for the payment, when available.
`end_payer_country`	string	Country of the end payer, when available.
`is_mail_order_or_telephone_order`	boolean	Whether the payment was initiated as a mail order or telephone order transaction.

Additional example queries

Analyse margin by payment method

Segment margin by payment method, card details and fee payer to identify the most and least profitable payment segments.

SELECT
 payment_method_type,
 card_brand,
 card_funding,
 fees_payer,
 COUNT(*) AS charge_count,
 SUM(amount) AS gross_volume,
 SUM(margin_amount) AS total_margin,
 ROUND(
 CAST(1.0 AS double) * SUM(margin_amount)
 / NULLIF(SUM(amount), 0) * 10000,
 1
 ) AS average_margin_bps
FROM connect_payments
WHERE DATE_TRUNC('month', primary_charge_created_date) = DATE '2024-07-01'
GROUP BY 1, 2, 3, 4
ORDER BY total_margin ASC

Reconcile monthly connected account activity

Aggregate payments to the connected account and month grain for comparison with the financial summary. Results can differ by platform-level activity that isn’t attributable to a payment. The latest schema doesn’t expose platform_merchant_id, so join within the current platform’s data using connected_account_merchant_id, activity month, and currency.

SELECT
 connected_account_merchant_id,
 connected_account_merchant_country,
 DATE_TRUNC('month', primary_charge_created_date) AS activity_month,
 currency,
 COUNT(*) AS charge_count,
 SUM(amount) AS gross_volume,
 SUM(refunded_amount) AS total_refunded,
 SUM(disputed_amount) AS total_disputed,
 SUM(revenue__application_fee_amount) AS total_application_fees,
 SUM(revenue__revshare_amount) AS total_revshare,
 SUM(margin_amount) AS total_margin
FROM connect_payments
GROUP BY 1, 2, 3, 4
ORDER BY activity_month DESC, gross_volume DESC