# Querying and Reporting on Stripe Data

This documentation section provides comprehensive guidance on how to access, query, and report on your Stripe data. It covers various tools and methods, including Stripe Sigma, the Reports API, and direct database access, to help you gain insights into your business performance, financial operations, and customer behavior.

## Key Concepts and Tools

*   **Stripe Sigma:** An interactive SQL environment within the Stripe Dashboard that allows you to query your transactional data using standard SQL. It's ideal for creating custom financial reports, building reports from templates, and tracking key metrics. Sigma provides read-only access to data on payments, subscriptions, customers, payouts, and more.
*   **Reports API:** Enables programmatic access to Stripe's financial reports, allowing you to automate reconciliation workflows. You can retrieve various report types, such as balance, payout reconciliation, fees, tax, and Connect reports, in CSV format.
*   **Balance Transactions:** The recommended starting point for reporting on your account's balance activity. Balance transactions represent every type of transaction that affects your Stripe account's balance and can be queried using the API or Sigma.
*   **Metadata:** Allows you to attach custom key-value pairs to Stripe objects and transactions. This enables you to store and retrieve supplementary information, facilitating targeted reporting and advanced querying.
*   **Data Pipeline:** A service that allows you to sync Stripe data to your data warehouse or cloud storage destinations like Snowflake, Amazon Redshift, Databricks, Amazon S3, Google Cloud Storage, or Azure Blob Storage.
*   **Stripe Database:** A Stripe-hosted, read-only PostgreSQL database that provides direct SQL access to your current Stripe data with sub-second latency. You can connect using any standard PostgreSQL client.

## Querying Your Data

### Using Stripe Sigma

*   **Writing Queries:** Compose custom queries in standard ANSI SQL using the Sigma query editor. You can leverage saved queries, templates, and an LLM-based query assistant to help generate SQL.
*   **Querying Across Organizations:** If you use Stripe Organizations, you can use Sigma to run queries across multiple accounts, providing insights into your customers and payments across your entire business.
*   **Scheduling Queries:** Automate your Sigma queries to run on a recurring basis (daily, weekly, or monthly). Results can be emailed to team members or sent as webhook events.
*   **Migrating Queries:** Adapt your Sigma queries to work with the Trino query engine, which powers Sigma.
*   **Sigma API:** (Private preview) Execute Sigma queries programmatically to retrieve and extract data for use in applications, Jupyter notebooks, or scheduled tasks.

### Using the Reports API

*   **Report Types:** Access various financial reports programmatically, including:
    *   Balance report
    *   Payout reconciliation report
    *   Fees report
    *   Tax report
    *   Connect report
*   **Running Reports:** Reports can be run using specific parameters, such as `interval_start` and `interval_end`, to define the desired time frame.
*   **Data Formats:** CSV reports format monetary amounts in major currency units, while the Stripe API uses minor units.

### Querying Specific Data Categories

*   **Transactional Data:** Query data related to charges, refunds, transfers, and other financial movements using tables like `balance_transactions`.
*   **Fees Data:** Retrieve detailed information about transaction and product fees using the `itemized_fees` table.
*   **Billing Data:** Explore billing information through tables like `subscriptions` and `invoices`.
*   **Disputes and Fraud Data:** Analyze disputes and fraud-related information using the `disputes` table, often joined with the `charges` table.
*   **Connected Account Data:** For Connect platforms, query data related to connected accounts using specific tables like `connected_accounts`.
*   **Card Issuing Data:** Access information about Issuing objects, including Authorizations, Transactions, Cards, and Cardholders, within the Issuing section of the schema.

## Data Schema and Freshness

*   **Stripe Data Schema:** Understand the organizational structure of Stripe data to effectively write queries.
*   **Data Freshness:** Sigma makes most transaction data available within three hours. API activity is also generally available within three hours. `data_load_time` can be used in queries to represent the most recently processed data.

## Advanced Reporting and Reconciliation

*   **Reporting and Reconciliation:** Use Stripe's reporting features to generate financial reports and perform reconciliation. Automatic payouts are recommended for maintaining transaction-to-payout associations.
*   **Fees Report:** View a detailed list of all fees charged by Stripe, networks, and external partners. This report can be accessed at a summary or itemized transaction level.
*   **Balance Report:** Retrieve your complete transaction history to aid in reconciliation.
*   **Payout Reconciliation Report:** Access data related to payouts to match them with the transactions they represent.
*   **Revenue Recognition Reports:** Utilize prebuilt report templates in Sigma for revenue recognition, including period summaries, income statements, and balance sheets.
*   **Custom Columns with Metadata:** Add custom columns to Financial Reports by utilizing metadata associated with Stripe objects.

## Accessing Data

*   **Dashboard:** Many reports and data can be accessed and downloaded directly from the Stripe Dashboard.
*   **API:** Programmatically access reports and data using the Reports API and other relevant Stripe APIs.
*   **Sigma:** Use the interactive SQL environment in the Dashboard or the Sigma API for custom querying.
*   **Data Pipeline:** Sync data to external data warehouses and cloud storage.
*   **Stripe Database:** Connect directly to a Stripe-hosted PostgreSQL database for direct SQL access.