Stripe Sigma Overview

    Use SQL queries to create customized reports using your Stripe data.

    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 and can be downloaded in CSV format for use in your own reporting tools or spreadsheet applications. You can also automate your reporting with scheduled queries that repeat on a regular basis, sending you results via email or webhook events.

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

    day id amount currency source_id
    2017-05-20 txn_WM0Gm7Smd1X2bjK -1,000 usd re_1WHQpYfeMGZMKBX
    2017-05-20 txn_J6yU0bDihDgjuzb -1,000 usd re_e2xTX6YnK8g2jWx
    2017-05-20 txn_xdoW5W25tHhuxu9 -1,000 usd re_loxCM7xVM4TI91Z
    2017-05-20 txn_sx3VyKTPeoslUGr -1,000 eur re_3Uv5MO3F3kRzmu8
    2017-10-23 txn_tVt7mWv7ODTYdYV -1,000 usd re_JVm9ZENsR4e4HB6

    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

    Stripe Sigma does not immediately reflect your account’s most recent transactions as additional processing time—up to three days—is required to make this data available to query. The interface in the Dashboard displays the date and time that data was last updated.

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

    You can use data_load_time as a value in your queries to represent when data was most recently processed on your account. For example, if the data on your account was last processed on 2017-10-22, data_load_time is interpreted as (timestamp '2017-10-22 00:00:00 +0000').

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

    day amount currency source_id charge.id
    2017-05-20 -1,000 usd re_XvGBCXcEauTxdoP ch_NnKGi0BrfooE3HX
    2017-05-20 -1,000 usd re_F3LZ4Bs0SFvGhT9 ch_RETT5Qht94Z2Dtr
    2017-05-20 -1,000 usd re_zk35DFbdT2Kp91O ch_5KJ9Yf8PgAgDvNU
    2017-05-20 -1,000 eur re_DRoNNmNSvY5g59g ch_SSpUXKZdCHljiwP
    2017-10-23 -1,000 usd re_cQvurULBTO3pPjN ch_Dijibn3VUYyjjjc

    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: