Sigma Overview

    Use SQL queries to create customized reports using your Stripe data. If you need help after reading this, search our documentation or check out answers to common questions. You can even chat live with other developers in #stripe on freenode.

    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

    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.

    day id amount fee currency source_id
    2017-05-20 txn_nG18iVdEjq1EPB5 -1,000 -59 usd re_1GFCcQK56Z1Sgo3
    2017-05-20 txn_99mgo8d8aqyvFDr -1,000 -59 usd re_23TqKlFRFE2dUTw
    2017-05-20 txn_UaugstGRq1BzeGe -1,000 -59 usd re_STssaXg7JLg9oik
    2017-05-20 txn_ACo82LkuSuZVlJi -1,000 -59 eur re_YonjRfaDn7Nen9L
    2017-06-27 txn_lmGpMZ8fzJnERTi -1,000 -59 usd re_8UDfktkCqrmqdFF

    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

    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.

    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 48 hours–is required to make your account’s transactional data available in Sigma. This means that it does not reflect your account’s most recent data and should be considered a couple days behind. The Sigma interface displays the date and time of the last update to your data.

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

    day amount fee currency source_id charge.id
    2017-05-20 -1,000 -59 usd re_CVE20uqFkfygtez ch_SrJrM66fpEGDfra
    2017-05-20 -1,000 -59 usd re_K01vshtoV1ULhXB ch_f7syoyJ6FUxnhbV
    2017-05-20 -1,000 -59 usd re_CSpNoVuHlSTVAKK ch_Y6ws9qjW2FWN3vd
    2017-05-20 -1,000 -59 eur re_PCmBtdy3iJvcvEM ch_qoiyKI1PM0Ov21Z
    2017-06-27 -1,000 -59 usd re_4hIdpwQHsHaaia8 ch_DboD2NrAP420pCP

    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 Sigma. You may want to learn more about querying payments, subscription data, or using Sigma for Connect: