# Query Connect analytical data

Access aggregated financial metrics for all your connected accounts, including volume, revenue, fees, chargebacks, and payouts.

The `account_financial_summary_hourly` table provides a complete aggregated view of financial metrics for every connection between your platform and its connected accounts. Unlike the standard Connect tables, Connect analytical data covers all connected accounts and charge flows, regardless of which party is responsible for fees. This includes destination charges, on-behalf-of charges, facilitated direct charges, separate charges and transfers, and so on. For a full description of each charge type, see [Connect charge types](https://docs.stripe.com/connect/charges.md).

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** | `account_financial_summary_hourly` |
| **Schema structure** | Vertical (one financial metric per row) |
| **Grain** | One row per hour, connected account, metric, currency, and dimensional combination |
| **Date coverage** | January 1, 2024–present |
| **Freshness** | Approximately T-1 day |

## Get started

The following query produces a pivoted financial summary for a specific connected account. It combines volume, revenue, Stripe fees, network costs, chargebacks, and payouts into one row per hour and currency. Use it as a starting point for margin calculations.

```sql
SELECT
    date_trunc('month',primary_revenue_date) AS activity_month,
    connected_account_merchant_id,
    currency,

    -- Volume
    SUM(CASE WHEN category='VOLUME' AND metric_type='GROSS_CONNECT_PAY_IN' THEN amount_usd END)
      AS gross_pay_in_usd,
    SUM(CASE WHEN category='VOLUME' AND metric_type='GROSS_CONNECT_TRANSFER' THEN amount_usd END)
      AS gross_transfer_usd,

    -- Revenue
    SUM(CASE WHEN category='REVENUE' AND metric_type='APPLICATION_FEE' THEN amount_usd END)
      AS application_fee_revenue_usd,
    SUM(CASE WHEN category='REVENUE' AND metric_type='IMPLICIT_APP_FEE' THEN amount_usd END)
      AS implicit_app_fee_revenue_usd,
    SUM(CASE WHEN category='REVENUE' AND metric_type='REVSHARE' THEN amount_usd END)
      AS revenue_revshare_usd,

    -- Platform Application Fees (gross fees charged to connected accounts)
    SUM(CASE WHEN category='APPLICATION_FEE' THEN amount_usd END)
      AS gross_application_fee_usd,

    -- Platform Application Fee Refunds
    SUM(CASE WHEN category='APPLICATION_FEE_REFUND' THEN amount_usd END)
      AS application_fee_refund_usd,

    -- Stripe Fees (platform-billed only; connected-account-billed rows excluded at source)
    SUM(CASE WHEN category='STRIPE_FEE' THEN amount_usd END)
      AS stripe_fees_usd,

    -- Network Costs
    SUM(CASE WHEN category='NETWORK_COST' THEN amount_usd END)
      AS network_cost_usd,

    -- Chargebacks
    SUM(CASE WHEN category='CHARGEBACK' AND metric_type='DISPUTE' THEN amount_usd END)
      AS dispute_amount_usd,
    SUM(CASE WHEN category='CHARGEBACK' AND metric_type='PAY_IN_REFUND' THEN amount_usd END)
      AS pay_in_refund_usd,

    -- Payouts
    SUM(CASE WHEN category='PAY_OUT' THEN amount_usd END) AS payout_usd,

    -- RevShare from Stripe (product-level rollup)
    SUM(CASE WHEN category='REVSHARE' THEN amount_usd END) AS revshare_usd

FROM account_financial_summary_hourly
-- NOTE: Add below filter to get data for specific connected account
-- WHERE connected_account_merchant_id='<connected_account_id>'
GROUP BY 1, 2, 3
ORDER BY 1 DESC
```

## Schema overview

The `account_financial_summary_hourly` table uses a vertical (unpivoted) schema—rather than one wide row with a column per metric, each metric is stored by its own row, identified by the combination of `category` and `metric_type`. To produce side-by-side comparisons of multiple metrics, use `CASE WHEN` pivots as shown in the previous example.

### Dimension columns

The following table describes who the activity belongs to, when it occurred, and how the payment was processed.

| Column | Type | Description |
| --- | --- | --- |
| `platform_merchant_id` | string | Stripe account ID of the platform (`acct_…`) |
| `connected_account_merchant_id` | string | Stripe account ID of the connected account (`acct_…`) |
| `connected_account_merchant_country` | string | Two-letter ISO country code of the connected account, such as `US` or `GB` |
| `user_facing_payment_method_name` | string | Payment method name, such as `card`, `ach_debit`, `sepa_debit`, or `affirm` |
| `charge_flow` | string | Connect charge flow. Values: `DESTINATION`, `DESTINATION_OBO`, `FACILITATED_DIRECT`, `FACILITATED_DIRECT_OBO`, `STANDALONE_OBO`, `LINKED`, `SEPARATE_CHARGES_AND_TRANSFERS`, `SEPARATE_CHARGES_AND_TRANSFERS_OBO`. For more details see [how charges work in a Connect](https://docs.stripe.com/connect/charges.md). |
| `currency` | string | Three-letter ISO currency code, such as `usd`, `eur`, or `gbp` |
| `card_funding` | string | Card funding type (`credit`, `debit`, `prepaid`). Populated for card transactions; `null` otherwise. |
| `card_network` | string | Card network (`visa`, `mastercard`, `amex`). Populated for card transactions; `null` otherwise. |
| `fee_payer` | string | Who bears the fee: `PLATFORM` or `CONNECTED_ACCOUNT`. Relevant for `STRIPE_FEE` and `GROSS_PLATFORM_FEE` rows; `null` for other categories. |
| `product_feature_description` | string | Granular product or feature label for fee rows, such as `connect_payout_fixed`. |
| `primary_revenue_date` | timestamp | When the activity was created. |
| `reported_at` | timestamp | When the activity was settled. |

### Metric identifier columns

These two columns together identify which financial metric a row represents.

| Column | Type | Description |
| --- | --- | --- |
| `category` | string | High-level metric category. See the [Categories and metrics reference](https://docs.stripe.com/data/connect-analytical-data/financial-summary.md#categories-and-metrics-reference) below. |
| `metric_type` | string | Metric type within the category. |

### Measure columns

These columns contain the numeric values for each metric row. All `amount` values are in the currency’s major unit (10.50 USD means $10.50).

| Column | Type | Description |
| --- | --- | --- |
| `amount` | decimal(38,18) | Metric amount in the transaction’s original currency. |
| `amount_usd` | decimal(38,18) | Metric amount converted to USD. Use this for cross-currency comparisons. |
| `activity_count` | int | Count of underlying activities (transactions, disputes, payouts, and so on). |
| `tax_amount` | decimal(38,18) | Tax amount in the transaction’s original currency. |
| `tax_amount_usd` | decimal(38,18) | Tax amount converted to USD. |
| `tax_currency` | string | Currency of the tax amount. |

## Categories and metrics reference

The following table lists every valid combination of `category` and `metric_type`.

| Category | Metric Type | Description |
| --- | --- | --- |
| PAYMENT | `PAY_IN` | Pay-in transactions across all charge flows. Use the `charge_flow` column to distinguish between the different types. |
| TRANSFER | `TRANSFER_WITH_SOURCE_TRANSACTION` | Destination transfers |
| TRANSFER | `DESTINATION_OBO` | On-behalf-of destination transfers |
| TRANSFER | `SEPARATE_CHARGES_AND_TRANSFERS` | Linked transfers (to separate charges) |
| TRANSFER | `TRANSFER` | Standalone unlinked transfers (sourced from `UNLINKED_TRANSFER`) |
| CHARGEBACK | `PAY_IN_REFUND` | Pay-in refunds. Use `charge_flow` for per-flow breakdown. |
| CHARGEBACK | `DISPUTE` | Dispute activity. Use `charge_flow` for per-flow breakdown. |
| CHARGEBACK | `TRANSFER_REFUND_WITH_SOURCE_TRANSACTION` | Transfer reversals. Use `charge_flow` for per-flow breakdown. |
| CHARGEBACK | `TRANSFER_REFUND` | Standalone unlinked transfer refunds (sourced from `UNLINKED_TRANSFER_REFUND`) |
| VOLUME | `GROSS_CONNECT_PAY_IN` | Gross platform pay-in volume. |
| VOLUME | `GROSS_CONNECT_TRANSFER` | Gross platform transfer volume. |
| PAY OUT | `STANDARD` | Standard payout volume and count |
| PAY OUT | `INSTANT` | Instant payout volume and count |
| PAY OUT | `PRIORITY` | Priority payout volume and count |
| PAY OUT | `OTHER` | Other payout types |
| REVENUE | `APPLICATION_FEE` | Application fee revenue earned by the platform (pay-ins and instant payouts) |
| REVENUE | `IMPLICIT_APP_FEE` | Implicit app fee revenue (platforms on % pricing) |
| REVENUE | `REVSHARE` | Revenue share earned from Stripe |
| REVENUE | `ACCOUNT_DEBIT` | Account debit revenue |
| REVENUE | `ACCOUNT_DEBIT_REFUND` | Account debit refund revenue |
| APPLICATION FEE | `PAY_IN` | Platform gross earnings on pay-ins (buy rate model) |
| APPLICATION FEE | `CAPTURE` | Platform gross earnings on captures |
| APPLICATION FEE | `CHARGE_BACK` | Platform gross earnings adjustment for chargebacks |
| APPLICATION FEE | `INSTANT_PAYOUT` | Platform gross earnings on instant payouts |
| APPLICATION FEE | `PMP_CAPTURE` | Platform gross earnings on PMP captures |
| APPLICATION FEE | `TRANSFER` | Platform gross earnings on transfers |
| APPLICATION FEE REFUND | `PAY_IN` | Refunds of platform fees on pay-ins |
| APPLICATION FEE REFUND | `CAPTURE` | Refunds of platform fees on captures |
| APPLICATION FEE REFUND | `CHARGE_BACK` | Platform fee adjustments for chargebacks |
| APPLICATION FEE REFUND | `INSTANT_PAYOUT` | Refunds of platform fees on instant payouts |
| APPLICATION FEE REFUND | `PMP_CAPTURE` | Refunds of platform fees on PMP captures |
| APPLICATION FEE REFUND | `TRANSFER` | Refunds of platform fees on transfers |
| IMPLICIT APP FEE | `PAY_IN` | Implicit app fees collected on pay-ins |
| IMPLICIT APP FEE | `TRANSFER_REVERSAL` | Implicit app fees collected on transfer reversals |
| IMPLICIT APP FEE | `PAY_IN_REFUND` | Implicit app fee refunds on pay-in refunds |
| IMPLICIT APP FEE | `DISPUTE` | Implicit app fee refunds on disputes |
| IMPLICIT APP FEE | `TRANSFER` | Implicit app fee refunds on transfers |
| STRIPE FEE | `CONNECT_FEE` | Stripe fees for Connect product features. |
| STRIPE FEE | `CONNECT_INSTANT_PAYOUT_FEE` | Stripe fee for instant payouts to connected accounts. |
| STRIPE FEE | `DISPUTE_FEE` | Stripe fees for dispute handling and representment. |
| STRIPE FEE | `PROCESSING_FEE` | Card and payment processing fees. |
| STRIPE FEE | `STRIPE_COSTPLUS_COST` | Cost-plus pricing markup fees. |
| STRIPE FEE | `TERMINAL_FEE` | Terminal hardware and usage fees. |
| STRIPE FEE | `TAX_FEE` | Stripe Tax calculation and integration fees. |
| STRIPE FEE | `ADAPTIVE_ACCEPTANCE_FEE` | Adaptive acceptance and auth optimization fees. |
| STRIPE FEE | `BILLING_FEE` | Billing and invoicing fees. |
| STRIPE FEE | `CARD_ACCOUNT_UPDATER_FEE` | Card account updater fees. |
| STRIPE FEE | `CONNECTION_VERIFICATION_FEE` | Financial connections verification fees. |
| STRIPE FEE | `RADAR_FEE` | Radar fraud prevention fees. |
| STRIPE FEE | `REFUND_FEE` | Refund-related Stripe fees. |
| STRIPE FEE | `OTHER_FEE` | Per-transaction Stripe fees across all payment methods and products. |
| NETWORK COST | `NETWORK_COST` | Card network interchange and assessment costs (cost+ pricing). |
| NETWORK COST | `OTHER_NETWORK_COST` | Non-transaction network costs. |
| NETWORK COST | `REFUND_FEE` | Network fees on refund transactions. |
| SPREAD FEE | `SPREAD_FEE` | Non transactional fee spread on payment. |
| REVSHARE | `PAYMENTS` | Revenue share from Payments product |
| REVSHARE | `ISSUING` | Revenue share from Issuing |
| REVSHARE | `BILLING` | Revenue share from Billing |
| REVSHARE | `CAPITAL` | Revenue share from Capital |
| REVSHARE | `INSTANT_PAYOUTS` | Revenue share from Instant Payouts |
| REVSHARE | `TAX` | Revenue share from Tax |
| REVSHARE | `TREASURY` | Revenue share from Treasury |

## Additional example queries

Refer to the following use-case example queries.

### Retrieve a breakdown of revenue by connected account

Revenue earned from connected accounts, including application fees, implicit app fees, and revenue share. Use it to track which connected accounts generate the most platform revenue.

```sql
SELECT
    date_trunc('month',primary_revenue_date) AS activity_month,
    platform_merchant_id,
    connected_account_merchant_id,
    connected_account_merchant_country,
    metric_type AS revenue_type,
    currency,
    SUM(amount_usd) AS revenue_usd
FROM account_financial_summary_hourly
WHERE category = 'REVENUE'
GROUP BY 1, 2, 3, 4, 5, 6
ORDER BY 1 DESC, 7 DESC
```

### Retrieve a summary of chargebacks and disputes by connected account

Chargeback and dispute activity for all connected accounts, by type and charge flow. Use it to monitor dispute rates and identify connected accounts with elevated chargeback risk.

```sql
SELECT
    date_trunc('month',primary_revenue_date) AS activity_month,
    connected_account_merchant_id,
    connected_account_merchant_country,
    metric_type AS chargeback_type,
    charge_flow,
    currency,
    SUM(amount_usd) AS chargeback_amount_usd,
    SUM(activity_count) AS event_count
FROM account_financial_summary_hourly
WHERE category = 'CHARGEBACK'
GROUP BY 1, 2, 3, 4, 5, 6
ORDER BY 1 DESC
```