Public preview

Query Connect analytical data Public preview

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.

Learn more about the features for Connect analytical data.

Before you begin

Review the table details before you get started:

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.

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 the related setting the related setting 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.

Metric identifier columns

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

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).

Categories and metrics reference

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

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.

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.

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