Query data across accounts belonging to an organisation

If you use Organisations, you can use Sigma to run queries across multiple accounts. These queries can provide insight into your customers and payments across your entire business.

Get started

Before you begin, complete the following steps:

1. Create an organisation, adding all the accounts you want to include in your queries.
2. Enable Sigma in each account you want to include in your queries.

To run and execute Sigma queries, you must be assigned an organisation-level role with permissions to view reports, such as Analyst.

Run Sigma queries across multiple accounts

To run Sigma queries that span multiple accounts in your organisation, follow these steps:

1. From the Dashboard, use the account picker to select your organisation. Expand Reporting, then click Sigma.

2. Write a new query, choose from saved queries, or select from the list of templates.

3. To specify the accounts you want to run your query on, click Run on accounts, then Select accounts.

4. Run the query across the accounts you selected.

Return results for each account

By default, queries return results that include data across all selected accounts. To return results for each account, you can group results by merchant_id. For example, the following query returns the sum of Payment Intents in the last 14 days for each account and currency:

select
  merchant_id,
  currency,
  sum(amount) as total_payment_volume_last_14d
from payment_intents
where created >= date_add('day', -14, current_date)
group by 1, 2

Filter accounts within a query

To use the merchant_id field to filter results to specific accounts directly in your query, add a WHERE merchant_id = 'acct_id' clause. For example, the following query returns the total volume of payments in a specific account:

select
  currency,
  sum(amount) as total_payment_volume_last_14d
from payment_intents
where created >= date_add('day', -14, current_date)
AND merchant_id in (
  'acct_d8upQPPBbFtSIe1',
  'acct_1fCSEPk3waoDZox'
)
group by 1