Stripe Sigma Overview

Stripe Sigma makes all of your transactional data available as an interactive SQL environment in the Dashboard. You can write queries that leverage its extensive schema, allowing you to create fully customized reports using information about payments, subscriptions, customers, payouts, and more. You can also browse our collection of example queries to answer common questions and serve as a starting point for your own explorations.

Query results are displayed directly in the browser. They’re also available for download in CSV format for use in your own reporting tools or spreadsheet applications.

Writing queries

Stripe Sigma uses standard ANSI SQL and a broad range of reporting functions. Any team members on your account with permission to view reports can write queries.

To execute your query and see the results, click Run. Depending on its complexity, the query can take a few moments to complete before the results are displayed. If you attempt to run an invalid query, an error message is displayed that contains the line number and position of where the error has occurred.

For example, the following query uses the balance_transactions table to get information about the five most recent balance transactions related to refunds.

select
  date_format(created, '%Y-%m-%d') as day,
  id,
  amount,
  fee,
  currency,
  source_id
from balance_transactions
where type = 'refund'
order by day desc
limit 5

Each row corresponds to a particular balance transaction item, along with the requested information about them.

Viewing and downloading query results

Results for queries are displayed below the editor. A maximum of 1,000 results are returned. You can optionally sort the results by clicking on the header of the column you wish to sort by. You can also resize each column to make it easier to read the results, or adjust the height of the results output.

Amounts are expressed in the lowest available currency unit, such as cents in USD or yen in JPY. For instance, an amount of 1,000 with a currency of usd would equate to $10.00.

You can download your query results in CSV format for use in spreadsheet applications or other reporting tools that you may be using. There is no limit when downloading a CSV, so you can work with more than 1,000 results if necessary.

Query history

You can view all previously performed queries within the History tab. Selecting a previously-run query loads it into the query editor, ready to be run again.

Templates

Stripe Sigma includes a selection of example queries for you to use, available in the Templates tab. These are based on commonly-used metrics and reports, and serve as useful starting points for you to create your own reports. Selecting a template loads it into the editor, ready to be run.

Templates themselves cannot be modified. Click Duplicate to create a copy that you can then edit and save.

The schema

All of the the data available for use in your queries is displayed in the Schema tab, organized by category. Each category contains a set of tables representing the available data for use in queries.

You can select a table to expand it and reveal its available columns, along with a description of the type of data it contains (e.g., Boolean , Varchar, Foreign key, etc.). Hover the cursor over any column to reveal a description of it. As the schema is extensive, you can also use the search field at the top of the schema to find specific tables and columns.

Stripe Sigma’s schema closely follows our API conventions as much as possible. Many of the tables correspond to specific API objects, with each column representing a specific attribute that can be reported on. For instance, the charges table represents information about Charge objects, which are displayed in the Payments section of the Dashboard.

When writing queries, our API reference can provide additional context and possible values.

Data freshness

Additional processing time—up to two days—is required to make your account’s transactional data available to query. This means that Stripe Sigma does not reflect your account’s most recent transactions and should be considered a few days behind. The Sigma interface displays the date and time that it last updated.

For example, the data for the end of Monday should be available by Wednesday afternoon. Similarly, data for the last day of the month would be available two days into the following month. Keep this in mind when writing monthly report queries.

Joining tables

Columns of type Primary key or Foreign key can be joined to similar columns in other tables:

• Primary key represents the unique identifier (ID) for each record in a table
• Foreign key represents data that refers to the primary key of another table

For instance, the charge_id column of the disputes table (a foreign key) can be joined to the id column of the charges table (a primary key).

There are many opportunities to join multiple tables together, depending on the data you’re wanting to report on. The rest of our Stripe Sigma documentation illustrates commonly used joins between tables, though the flexibility of our schema allows for many different combinations. For example, the balance transaction query from earlier can be modified to join with the refunds table to provide further information.

select
  date_format(date_trunc('day', balance_transactions.created), '%Y-%m-%d') as day,
  balance_transactions.amount,
  balance_transactions.fee,
  balance_transactions.currency,
  balance_transactions.source_id,
  refunds.charge_id
from
  balance_transactions
inner join refunds -- Joining these tables to retrieve additional information
on balance_transactions.source_id=refunds.id
where balance_transactions.type = 'refund'
order by day desc
limit 5

This extended query now returns the original charge ID that the refund relates to.

Saving queries

To save a query, enter a name by clicking on the query’s title (labeled as Untitled if it is a new query) and then click Save.

Saved queries are found within the Saved tab. Queries that you have saved are listed in the You section. The All section includes saved queries by all team members. Selecting a saved query fills in the query editor automatically and not executed until you click Run.

Sharing queries

All of the queries you save are also added to the All section and made available to every team member on your account. Each saved query is also given a unique URL which you can share by clicking Copy link. You can use this link as a shortcut to a particular report you regularly use, or share it directly to other team members on the Stripe account.

Queries can only be shared with team members who have permission to view reports. Shared queries are also read-only, so other team members cannot modify the queries you create. If a team member wants to make changes to the query, they can save a copy of it and edit accordingly.

Next steps

Congrats! You can now begin writing queries to deep-dive into your account's transactional data using Stripe Sigma. You may want to learn more about querying payments, subscription data, or using Sigma for Connect:

• Querying Transactions
• Querying Subscriptions
• Sigma for Connect Platforms