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

    day id amount fee currency source_id
    2017-05-20 txn_z9NmQW9jASMNtuA -1,000 -59 usd re_ah1rZdgO2afboDV
    2017-05-20 txn_KarLUNGrmbgSt2O -1,000 -59 usd re_w8nAaBROVZ6qMR0
    2017-05-20 txn_1OKj8RhWtfR0rE5 -1,000 -59 usd re_SXsnG5RAFQxrwtf
    2017-05-20 txn_SNjL4TO7MSCtFhm -1,000 -59 eur re_b07mbbmPSIs0CAH
    2017-08-22 txn_upC5WkG0VNtD9vF -1,000 -59 usd re_sXKlLfORqUeGQQI

    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.

    day amount fee currency source_id charge.id
    2017-05-20 -1,000 -59 usd re_L662ArH9elcWT0i ch_WHgyJfSDKjwiMZ5
    2017-05-20 -1,000 -59 usd re_VrT4gz4EUIgfivX ch_hNQtmsDicDc8aMn
    2017-05-20 -1,000 -59 usd re_QKFxx5ue9WhgaAd ch_YOeOt9OX25T7ZpD
    2017-05-20 -1,000 -59 eur re_qRJmnUIFxB57aOM ch_rdu6I6ZW2mNltlC
    2017-08-22 -1,000 -59 usd re_F5Ja73ftc8zeai4 ch_0bmPw03Ciy66krX

    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: