# Query connected account data

Access structured attributes about your connected accounts, including onboarding milestones, activation dates, account configuration, and connection status.

The `connect_merchants` table includes data for all v1 `Accounts` and all v2 `Accounts` that have the `merchant` *configuration* (Account configurations represent role-based functionality that you can enable for accounts, such as merchant, customer, or recipient).

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_merchants` |
| **Schema structure** | Wide (one row per connected account) |
| **Grain** | One row per `connected_account_merchant_id` |
| **Date coverage** | All active and inactive (disconnected) connections for all platforms |
| **Freshness** | Approximately T-1 day |

> Some onboarding milestones have limited historical coverage. For records before a coverage start, `NULL` means that Stripe didn’t capture the historical event. When comparing multiple milestones, limit the cohort to the latest applicable coverage start.
> 
> | Column                            | Date that data is available from |
| --------------------------------- | -------------------------------- |
| `completed_onboarding_at`         | May 2025                         |
| `capabilities_fully_enabled_at`   | December 2025                    |
| `last_visited_onboarding_step_at` | April 2026                       |

## Get started with the data

The following query lists connected accounts for your platform with their configuration, country, and derived onboarding status. Because the status compares `completed_onboarding_at` and `capabilities_fully_enabled_at`, the query limits the onboarding cohort to December 2025 and later.

```sql
SELECT
    connected_account_merchant_id,
    identity__connected_account_merchant_country,
    identity__legal_entity_type,
    capability_summary_status,
    is_active_connection,
    connected_at,
    started_onboarding_at,
    completed_onboarding_at,
    capabilities_fully_enabled_at,
    CASE
        WHEN capabilities_fully_enabled_at IS NOT NULL THEN 'ENABLED'
        WHEN completed_onboarding_at IS NOT NULL THEN 'COMPLETED_NOT_ENABLED'
        WHEN started_onboarding_at IS NOT NULL THEN 'STARTED_NOT_COMPLETED'
        ELSE 'NOT_STARTED'
    END AS onboarding_status
FROM connect_merchants
-- Use the latest coverage start for the milestones compared above.
WHERE started_onboarding_at >= TIMESTAMP '2025-12-01 00:00:00'
ORDER BY connected_at DESC
```

## Schema overview

The `connect_merchants` table is a current-state account dimension for all platform–connected account relationships. It’s a wide-dimension table, with each row representing one connected account and columns representing an account’s attributes and lifecycle status. This differs from the [financial summary table](https://docs.stripe.com/data/connect-analytical-data/financial-summary.md), which is a vertical metrics table.

### Identity and connection columns

These columns identify the platform–account relationship and its current connection state.

| 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_…`). Consistent join key across all Connect Analytical Data datasets. |
| `connected_at` | timestamp | When the Connect relationship was established. `NULL` if the account started onboarding but has not yet completed the connection. |
| `disconnected_at` | timestamp | When the connected account was disconnected from the platform. `NULL` for active connections. |
| `is_active_connection` | boolean | true if the connection is currently active (not disconnected). |

### Core account attribute columns

| Column | Type | Description |
| --- | --- | --- |
| `identity__connected_account_merchant_country` | string | ISO 3166-1 alpha-2 country code of the connected account’s registered country, such as `US` or `GB`. |
| `identity__legal_entity_type` | string | Legal entity type of the connected account. Values: `individual`, `company`. Visible to controlling platforms only. |
| `capability_summary_status` | string | Capability summary account status as visible to the platform. Values: `ENABLED`, `RESTRICTED`, `RESTRICTED_SOON`. `NULL` if status cannot be determined. |

### Account behavior configuration columns

These columns describe the configured behavior of the connected account, such as who pays fees, who bears responsibility for negative balances, and how requirements are collected. Only controlling platforms can access them. For details about which v1 and v2 `Account` properties correspond to these columns, see the [Account property map](https://docs.stripe.com/connect/accounts-v2/connected-account-configuration.md#property-map).

| Column | Type | Description |
| --- | --- | --- |
| `responsibilities_fee_collector` | string | Indicates whether the platform or connected account is responsible for paying Stripe fees for pricing-control-eligible products.
- `APPLICATION` - The platform is responsible for collecting fees from the account.
- `APPLICATION_CUSTOM` - Direct charge fee behavior is [the same as for Custom accounts](https://docs.stripe.com/connect/direct-charges-fee-payer-behavior.md#application_custom-or-application_express).
- `APPLICATION_EXPRESS` - Direct charge fee behavior is [the same as for Express accounts](https://docs.stripe.com/connect/direct-charges-fee-payer-behavior.md#application_custom-or-application_express). |
| `connect_controller_losses_payments` | string | Indicates the responsibility for losses on the account.
- `APPLICATION` - The platform is responsible for negative balances on the account.
- `STRIPE` - Stripe is responsible for negative balances on the account. |
| `connect_controller_requirement_collection` | string | Indicates responsibility for collecting requirements on the account.
- `APPLICATION` - The platform is responsible for collecting outstanding and updated requirements on the account.
- `STRIPE` - Stripe is responsible for collecting outstanding and updated requirements on the account. |
| `dashboard` | string | Indicates which Stripe dashboard the account can access.
- `EXPRESS` - The account can access the Express Dashboard.
- `FULL` - The account can access the full Stripe Dashboard.
- `NONE` - The account can’t access any Stripe-hosted dashboard. |

### Onboarding milestone columns

These columns capture the timestamp, device type, and access method for each step in the connected account onboarding flow. All values are from the first occurrence of each event.

| Column | Type | Description |
| --- | --- | --- |
| `started_onboarding_at` | timestamp | When the connected account first started the onboarding flow. |
| `started_onboarding_device_type` | string | The device type used by the account when it started onboarding. `NULL` if not captured. |
| `started_onboarding_surface` | string | The onboarding flow type used when the account began onboarding. Values: `api`, `hosted`, `embedded`. |
| `authentication_completed_at` | timestamp | When the account authentication was completed. |
| `authentication_completed_device_type` | string | The device type used by the account when it completed authentication. |
| `authentication_completed_surface` | string | The onboarding flow type used by the connected account when it completed authentication verification. Values: `api`, `hosted`, `embedded`. |
| `personal_and_business_details_completed_at` | timestamp | When personal and business details were completed. |
| `personal_and_business_details_device_type` | string | The device type used by the account when it completed personal and business details. |
| `personal_and_business_details_surface` | string | The onboarding flow type used by the connected account when it completed personal and business details. Values: `api`, `hosted`, `embedded`. |
| `bank_and_financial_details_completed_at` | timestamp | When bank and financial details were completed. |
| `completed_onboarding_at` | timestamp | When the account completed all required steps in the onboarding flow. |
| `completed_onboarding_device_type` | string | The device type used by the account when it completed onboarding. |
| `capabilities_fully_enabled_at` | timestamp | When all payment capabilities became active for the account, making it ready to process payments. |
| `last_visited_onboarding_step_at` | timestamp | Timestamp of the most recent onboarding step event. `NULL` if no step events exist for this connection or if historical data isn’t available. |

### Financial activation columns

These columns capture the first date the connected account processed specific payment activity through the platform.

| Column | Type | Description |
| --- | --- | --- |
| `first_card_pay_in_date` | date | The first date a card payment was processed through the platform. `NULL` if no card payments have been made yet. |
| `first_lpm_pay_in_date` | date | The first date a non-card (local payment method) transaction was processed through the platform. `NULL` if no LPM payments have been made yet. |
| `first_payout_date` | date | The first date the connected account received a payout through the platform. `NULL` if it hasn’t received any payouts yet. |

## Example queries

### Analyze the account onboarding funnel

Measure conversion at each step of the onboarding funnel. Use this query to identify bottlenecks and compare funnel performance across accounts.

```sql
SELECT
    identity__connected_account_merchant_country,
    COUNT(*) AS total_connections,
    COUNT(started_onboarding_at) AS started_onboarding,
    COUNT(authentication_completed_at) AS completed_authentication,
    COUNT(personal_and_business_details_completed_at) AS completed_personal_details,
    COUNT(bank_and_financial_details_completed_at) AS completed_bank_details,
    COUNT(completed_onboarding_at) AS completed_onboarding,
    COUNT(capabilities_fully_enabled_at) AS capabilities_enabled,
    ROUND(100.0 * COUNT(capabilities_fully_enabled_at) / NULLIF(COUNT(started_onboarding_at), 0), 1)
        AS start_to_enabled_pct
FROM connect_merchants
WHERE is_active_connection = TRUE
    -- Use the latest coverage start for the milestones compared above.
    AND started_onboarding_at >= TIMESTAMP '2025-12-01 00:00:00'
GROUP BY 1
ORDER BY total_connections DESC
```

### Track activation milestones across connected accounts

Identify which connected accounts have processed their first payment, LPM transaction, or payout. Use this to track activation cohorts and find accounts that have onboarded but not yet transacted. Because this analysis uses `capabilities_fully_enabled_at`, the query limits the onboarding cohort to December 2025 and later.

```sql
SELECT
    connected_account_merchant_id,
    identity__connected_account_merchant_country,
    capabilities_fully_enabled_at,
    first_card_pay_in_date,
    first_lpm_pay_in_date,
    first_payout_date,
    CASE
        WHEN first_card_pay_in_date IS NOT NULL OR first_lpm_pay_in_date IS NOT NULL
            THEN 'ACTIVATED'
        WHEN capabilities_fully_enabled_at IS NOT NULL
            THEN 'ENABLED_NOT_ACTIVATED'
        ELSE 'NOT_ENABLED'
    END AS activation_status,
    DATE_DIFF('day', CAST(capabilities_fully_enabled_at AS DATE), first_card_pay_in_date)
        AS days_to_first_card_payment
FROM connect_merchants
WHERE is_active_connection = TRUE
    AND started_onboarding_at >= TIMESTAMP '2025-12-01 00:00:00'
ORDER BY capabilities_fully_enabled_at DESC
```

### Segment accounts by configured behavior

Group connected accounts by configured behavior to audit your account portfolio.

```sql
SELECT
    connect_controller_fees_payer,
    connect_controller_losses_payments,
    connect_controller_requirement_collection,
    connect_controller_dashboard_type,
    COUNT(*) AS account_count,
    COUNT(CASE WHEN capability_summary_status = 'ENABLED' THEN 1 END) AS enabled_count,
    COUNT(CASE WHEN capability_summary_status = 'RESTRICTED' THEN 1 END) AS restricted_count,
    COUNT(CASE WHEN capability_summary_status = 'RESTRICTED_SOON' THEN 1 END) AS restricted_soon_count
FROM connect_merchants
WHERE is_active_connection = TRUE
GROUP BY 1, 2, 3, 4
ORDER BY account_count DESC
```

### Join with financial summary for a combined account view

Combine account attributes from `connect_merchants` with financial performance from `account_financial_summary_hourly`. Join on `(platform_merchant_id, connected_account_merchant_id)`, which use `acct_…` identifiers in both tables.

```sql
SELECT
    a.connected_account_merchant_id,
    a.identity__connected_account_merchant_country,
    a.capability_summary_status,
    a.capabilities_fully_enabled_at,
    a.first_card_pay_in_date,
    DATE_TRUNC('month', f.primary_revenue_date) AS activity_month,
    SUM(CASE WHEN f.category = 'VOLUME' AND f.metric_type = 'GROSS_CONNECT_PAY_IN'
        THEN f.amount_usd END) AS gross_pay_in_usd,
    SUM(CASE WHEN f.category = 'REVENUE' AND f.metric_type = 'APPLICATION_FEE'
        THEN f.amount_usd END) AS application_fee_usd
FROM connect_merchants a
LEFT JOIN account_financial_summary_hourly f
    ON a.platform_merchant_id = f.platform_merchant_id
    AND a.connected_account_merchant_id = f.connected_account_merchant_id
WHERE a.is_active_connection = TRUE
GROUP BY 1, 2, 3, 4, 5, 6
ORDER BY 6 DESC, 7 DESC NULLS LAST
```