Stripe Sigma for Connect Platforms

    Use Stripe Sigma to report on transactional data about your Connect platform.

    Stripe Connect platforms can report on their connected Express and Custom accounts using Stripe Sigma. You can write queries that run across your entire platform in much the same way as your own Stripe account.

    Additional groups of Connect-specific tables within Stripe Sigma’s schema are located in the Connect sections of the schema. If you do not operate a Connect platform, these tables are not displayed.

    Connected account information

    The connected_accounts table provides a list of Account objects with information about connected Express and Custom Stripe accounts. This table is used for account-level information across all accounts on your platform, such as business name, country, or the user’s email address.

    The following example uses the connected_accounts table to retrieve a list of five Custom accounts for individuals located in the U.S. that have payouts disabled because Stripe does not have the required verification information to verify their account.

    select
      id,
      email,
      legal_entity_address_city as city,
      legal_entity_address_line1 as line1,
      legal_entity_address_postal_code as zip,
      legal_entity_address_state as state,
      legal_entity_dob_day as dob_day,
      legal_entity_dob_month as dob_month,
      legal_entity_dob_year as dob_year,
    See all 23 lines legal_entity_first_name as first_name, legal_entity_last_name as last_name, legal_entity_ssn_last_4_provided as ssn_provided, tos_acceptance_date as tos_date, tos_acceptance_ip as tos_ip, legal_entity_personal_id_number_provided as id_provided, legal_entity_verification_document_id as document_id from connected_accounts where type = 'custom' and legal_entity_type = 'individual' and payouts_enabled = false and country = 'US' limit 5

    All of the required fields for individual Custom accounts in the U.S. are retrieved as columns. This allows you to see what information has been provided, and what is needed, for each account. This can be seen in the example report below (some columns have been omitted for brevity).

    id email city ... id_provided document_id
    acct_oG4KqqK... jenny.rosen@example.com San Francisco ... true file_MabXqc4...
    acct_Rdt9B5H... sophia.garcia@example.com ... false file_fGuD7WK...
    acct_xRDlJpt... natalie.davis@example.com Seattle ... true file_vvz9sWc...
    acct_0HI18yu... ella.thompson@example.com Austin ... false file_aLC419z...
    acct_A7nhx9L... james.smith@example.com ... false file_sHFQPaV...

    Transactional data for connected accounts

    Transactional and subscription data for Express and Custom accounts is contained within the connected_account_ tables. The available data for connected accounts is organized and structured in the same way as data for your own account.

    For instance, the balance_transactions table, located in the Payments section, contains balance transaction data for your Stripe account. The connected_account_balance_transactions table, located in the Connect - Payments section, contains balance transaction data for your connected accounts. Each Connect-specific table has an additional account column containing the identifier of a connected account. This can be used when joining tables to build advanced queries.

    The following example is based upon the default query that’s loaded into the editor. Instead of retrieving the ten most recent balance transactions on your account, it does so across all of your platform’s connected accounts.

    select
      date_format(created, '%Y-%m-%d') as day,
      account, -- Added to include corresponding account identifier
      id,
      amount,
      currency,
      source_id,
      type
    from connected_account_balance_transactions -- Changed to use Connect-specific table
    order by day desc
    limit 5
    day account id amount currency source_id type
    2018-09-22 acct_z3S3BHR... txn_0TMt09W... -1,000 usd re_2RE6QSR... refund
    2018-09-22 acct_Yq2UdUz... txn_dIelQBC... 1,000 usd ch_Eh7pJt7... charge
    2018-09-22 acct_0Jo30pd... txn_lB2hNUL... 1,000 usd ch_VrbdgJG... charge
    2018-09-22 acct_ebU6q1G... txn_VNkVRR6... 1,000 eur ch_RNzPBoF... charge
    2018-09-22 acct_fK6s0Mr... txn_aBwKSjv... -1,000 usd re_NltnwRl... refund

    Refer to our Stripe Sigma transactions and subscriptions documentation to learn more about querying transactional and subscription data. You can then supplement or adapt your queries with Connect-specific information to report on connected accounts.

    Querying charges on connected accounts

    You can use Stripe Sigma to report on the flow of funds to your connected accounts. How you do this depends on your platform’s approach to creating charges.

    Direct charges

    If your platform creates direct charges on a connected account, they appear on the connected account, not on your platform. This is analogous to a connected account making a charge request itself. Platforms with Express and Custom accounts can use the Connect-specific tables (e.g., connected_account_charges or connected_account_balance_transactions) to report on direct charges.

    If your platform creates direct charges to Standard accounts, you cannot directly access transactional data for those accounts. However, if you assess an application fee on direct charges, detailed information is available within the application_fees and application_fee_refunds tables. These tables can be used to report on any revenue from application fees without access to the charge data itself.

    The direct charges query template retrieves itemized information about application fees earned through direct charges, and reports on the connected account, transfer, and payment that is created.

    Destination charges

    If your platform creates destination charges on behalf of connected accounts, charge information is available within your own account’s data. A separate transfer of the funds to the connected account is automatically created, which creates a payment on that account. The direct charges query template reports on transfers related to destination charges made by your platform.

    One way to analyze the flow of funds from a destination charge to a connected account is by joining the transfer_id column of the charges table to the id column of the transfers table. This example includes the original charge identifier and amount, the amount transferred to the connected account, and the connected account’s identifiers and resulting payment.

    select
      date_format(date_trunc('day', charges.created), '%y-%m-%d') as day,
      charges.id,
      charges.amount as charge_amount,
      transfers.amount as transferred_amount,
      transfers.destination_id
    from charges
    inner join transfers
      on transfers.id=charges.transfer_id
    order by day desc
    limit 5
    
    day id charge_amount transferred_amount destination_id
    2018-09-22 ch_acct_EV9RdPS... 1,000 1,000 acct_cB46374...
    2018-09-22 ch_acct_wdCc1XQ... 800 800 acct_vkPFovc...
    2018-09-22 ch_acct_tuKpeEb... 1,000 800 acct_Rz1T53z...
    2018-09-22 ch_acct_bgyEzER... 1,100 950 acct_7Me2gFA...
    2018-09-22 ch_acct_nQgnxE6... 1,100 1,100 acct_0cxViNu...

    Payment and transfer information for Connected accounts is also available within Connect-specific tables (e.g., connected_account_charges).

    Separate charges and transfers

    You can report on separate charges and transfers using a similar approach to destination charges. All charges are created on your platform’s account, with funds separately transferred to connected accounts using transfer groups. A payment is created on the connected account that references the transfer and transfer group.

    Both the charges and transfers table include a transfer_group column. Payment, transfer, and transfer group information is available within the Connect-specific connected_account_charges table.

    Questions?

    We're always happy to help with code or other questions you might have! Search our documentation, contact support, or connect with our sales team. You can also chat live with other developers in #stripe on freenode.

    Was this page helpful? Yes No

    Send

    Thank you for helping improve Stripe's documentation. If you need help or have any questions, please consider contacting support.