# Query Connect payments data

Inspect individual Connect charges and analyze 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](https://docs.stripe.com/connect/charges.md). You can use it to investigate individual charges and analyze payment volume, revenue, itemized 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](https://docs.stripe.com/data/connect-analytical-data.md).

## 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](https://docs.stripe.com/data/connect-analytical-data/financial-summary.md) or to inspect revenue and margin for individual charges.

```sql
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](https://docs.stripe.com/data/connect-analytical-data/merchants.md) and [financial summary](https://docs.stripe.com/data/connect-analytical-data/financial-summary.md) 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 | Serialized 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 | Serialized 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 | Serialized 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 | Serialized 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 itemize 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 itemize 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-authorization fee subtotal. |
| `stripe_fees__per_auth_fee_subtotal_currency` | string | Currency of the Stripe per-authorization fee subtotal. |
| `stripe_fees__per_auth_fee_subtotal_amount_usd` | decimal | Stripe per-authorization fee subtotal converted to USD. |
| `stripe_fees__per_auth_fee_tax_amount` | decimal | Tax charged on the Stripe per-authorization fee. |
| `stripe_fees__per_auth_fee_tax_currency` | string | Currency of the Stripe per-authorization fee tax. |
| `stripe_fees__per_auth_fee_tax_amount_usd` | decimal | Stripe per-authorization 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

### Analyze margin by payment method

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

```sql
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](https://docs.stripe.com/data/connect-analytical-data/financial-summary.md). 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.

```sql
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
```