Stripe Sigma

    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 use it as a starting point for your own explorations.

    You can view query results directly in the browser, download them in CSV format, and use them 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 in email or webhook events.

    Writing queries

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

    To execute your query and see the results, click Run. More complex queries can take a few moments to complete and display results. Attempting to run an invalid query generates an error message that contains the line number and position of the error.

    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_HotvXM1Q03EbPQ8 -1,000 usd re_38tdpTxEP6UzVIW
    2017-05-20 txn_npz6qmD4ElFogGZ -1,000 usd re_Npb0XX2UBOY1qd5
    2017-05-20 txn_l9EcpMfqf2LPbHC -1,000 usd re_uiJFpP1ExVP7sLf
    2017-05-20 txn_C51uI55zG74yxFq -1,000 eur re_lqduNrhOem92byK
    2019-11-12 txn_AHjG6e9JexDWXdX -1,000 usd re_ii7spiPj2fPZpY0

    Viewing and downloading query results

    Query results display below the editor, and you can view a maximum of 1,000 results. You can sort the results by clicking on the column headers. 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 for USD or yen for JPY. For example, an amount of 1,000 with a currency of usd would equate to 10 USD.

    You can download your query results in CSV format and use them in spreadsheet applications or other reporting tools. 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 queries are based on commonly-used metrics and reports, and serve as useful starting points for creating 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

    The Schema tab displays all of the available data that you can use in your queries, organized by category. Each category contains a set of tables that represents the available data.

    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.

    The Stripe Sigma schema follows our API conventions as closely 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 makes your transaction data available to query within one day. All account activity for a given day (from 12:00am to 11:59pm UTC) is available to query by 12:00pm UTC the following day. For example, data for the end of day Sunday UTC time should be available by Monday afternoon. Similarly, data for the last day of the month would be available one day into the following month.

    The interface in the Dashboard displays the date and time that data was last updated. 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 2019-10-31, data_load_time is interpreted as (timestamp '2019-10-31 00:00:00 +0000').

    Please note that Sigma may reflect activity that is more recent than data_load_time. For example, a charge authorized just before midnight, but captured soon after, may show as captured.

    Joining tables

    You can join columns of type Primary key or Foreign key 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, you can join the charge_id​ column of the disputes​ table (a foreign key) to the id​ column of the charges​ table (a primary key).

    Stripe Sigma provides 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_vjTY1HOej9gvapG ch_cgqlF1022YYTt9f
    2017-05-20 -1,000 usd re_uaG0huXreOOcQVO ch_LMTen3mgvopx8AS
    2017-05-20 -1,000 usd re_BnmxbPKMHfn27XI ch_rodvKVkn99L4nQS
    2017-05-20 -1,000 eur re_c14piQtN4ie2aeO ch_pSFIIwFBVot5D0t
    2019-11-12 -1,000 usd re_JCeJ5mQb43bZQYw ch_tlQziWMJzUvY3eE

    Saving queries

    To save a query, enter a name by clicking on the title of the query (labeled as Untitled if it’s 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 queries saved by all team members. Selecting a saved query fills in the query editor automatically; the query does not execute until you click Run.

    Sharing queries

    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.

    You can only share queries with team members. Shared queries are also read-only, so other team members can’t modify the queries you create. If a team member wants to make changes to the query, they can save a copy and edit it accordingly.

    Unsubscribing from Stripe Sigma

    If you currently have an active Stripe Sigma subscription and want to cancel it for any reason, you can unsubscribe in data settings page in the Dashboard—click Cancel Stripe Sigma subscription. You can continue using Sigma until the end of the billing cycle, at which point the subscription ends.

    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 transactional or billing data, or using Sigma for Connect:

    Was this page helpful?

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

    On this page