## Stripe Reporting and Data Querying

This documentation section covers how to access and analyze your Stripe data for reporting, reconciliation, and business insights. It focuses on Stripe Sigma, a SQL-based querying tool, and the Reports API, which allows programmatic access to financial reports.

### Core Concepts

*   **Stripe Sigma:** An interactive SQL environment within the Stripe Dashboard that allows you to query your transactional data. You can create custom financial reports, build reports from templates, and track key metrics. Sigma provides read-only access to data about payments, subscriptions, customers, payouts, and more.
*   **Reports API:** Enables programmatic access to Stripe's financial reports, automating reconciliation workflows. These reports are available in CSV format and can be scheduled to run automatically or on demand.
*   **Balance Transactions:** The recommended starting point for reporting on your account's balance activity. These represent every type of transaction that enters or leaves your Stripe account balance.
*   **Metadata:** Allows you to attach custom key-value pairs to Stripe objects and transactions, enabling more targeted reporting and advanced querying.

### Key Features and Functionality

*   **Querying Data:**
    *   **Sigma:** Write custom queries in standard ANSI SQL using the Sigma query editor. Utilize saved queries, templates, and an LLM-based query assistant.
    *   **Reports API:** Access various report types programmatically, including Balance, Payout Reconciliation, Fees, Tax, and Connect reports.
    *   **Stripe Database:** Connect to a Stripe-hosted PostgreSQL database for direct SQL access to your Stripe data with sub-second latency.
*   **Report Types:**
    *   **Balance Report:** Provides your complete transaction history for reconciliation.
    *   **Fees Report:** Lists all fees charged by Stripe, networks, and external partners.
    *   **Payout Reconciliation Report:** Returns data related to payouts to help match them with associated transactions.
    *   **Connect Reports:** Retrieve financial reports for your connected accounts.
    *   **Revenue Recognition Reports:** Pre-built templates in Sigma for analyzing revenue, deferred revenue, and billings.
*   **Data Management:**
    *   **Data Pipeline:** Sync Stripe data to your data warehouse (Snowflake, Amazon Redshift, Databricks) or cloud storage (Amazon S3, Google Cloud Storage, Azure Blob Storage).
    *   **Import External Data:** Use no-code connectors to import data from sources like Amazon S3, Apple App Store, and Google Play Store into Stripe.
*   **Advanced Querying:**
    *   **Querying Authentication Conversion:** Use Stripe Sigma to retrieve information about authentication, conversion, and SCA exemptions.
    *   **Querying Disputes and Fraud Data:** Analyze dispute and fraud information using the `disputes` table.
    *   **Querying Billing Data:** Explore subscription and invoice data using the `subscriptions` and `invoices` tables.
    *   **Querying Connected Account Data:** Report on connected accounts using Sigma or Data Pipeline.
    *   **Querying Card Issuing Data:** Access data on Authorizations, Transactions, Cards, and Cardholders.
*   **Organizations:** Query data across multiple accounts belonging to your organization using Sigma.
*   **Scheduling Queries:** Automate Sigma queries to run on a recurring basis (daily, weekly, monthly) with results sent via email or webhook.
*   **API Access:**
    *   **Sigma API (Private Preview):** Execute Sigma queries programmatically for advanced integration use cases.
    *   **Reports API:** Automate report generation and data extraction.
    *   **Query Run API (v2 Reports API):** Execute ad-hoc SQL queries against your Stripe data programmatically.

### Key Tables and Data Access

*   **`authentication_report_attempts`:** For querying authentication conversion data.
*   **`balance_transactions`:** A ledger-style record of all transactions affecting your Stripe balance.
*   **`disputes`:** Contains data about all disputes on your account.
*   **`itemized_fees`:** Provides a granular breakdown of every fee charged.
*   **`connected_accounts`:** Lists Account objects with information about connected accounts.
*   **`issuing_authorizations`:** Represents Authorization objects for issued card usage.
*   **`subscriptions`:** Contains data about individual Subscription objects.

### Data Freshness

*   Most Stripe transaction data is available in Sigma within three hours.
*   API activity is generally available within three hours.
*   `data_load_time` can be used in queries to represent the timestamp of the most recently processed data.

### Migration and Best Practices

*   **Migrate Queries:** Adapt your Sigma queries from Presto to Trino to ensure compatibility with the upgraded query infrastructure.
*   **API Key Permissions:** Ensure your API key has the necessary `reporting_write` and `sigma_api_write` permissions for creating query runs.
*   **Use `reporting_category`:** For accounting purposes, use the `reporting_category` field instead of the `type` field on balance transactions.
*   **Automatic Payouts:** Recommended for simplifying reporting and reconciliation by maintaining transaction-payout associations.