Stripe Sigma Analytics: SQL Querying for Churn and LTV (2026)
Last Updated: July 2026Quick Answer: Stripe Sigma Analytics
- What it is: Stripe Sigma is an interactive SQL analytics environment built directly into the Stripe dashboard, running on a Presto/Trino query engine, that provides programmatic access to every charge, subscription, refund, dispute, and customer record as queryable database tables.
- Churn and LTV signals: SQL allows you to identify specific cohorts — customers who purchased Product A but cancelled within 30 days, or subscribers whose cumulative transaction value exceeds $1,000 — with precision that native charts cannot match.
- The algorithmic feedback loop: Export high-LTV cohort data, route it through Make.com, and push it back to Meta and Google as offline conversions to train the ad algorithm to target statistically similar high-value buyers.
Building the feedback loop? The Sigma export needs somewhere to route to — set up Make.com before you get to Section 5.
Stripe Sigma analytics transforms your payment processor from a revenue recording tool into a live intelligence environment.
By writing direct SQL queries against your transaction database, operators can uncover churn signals invisible to native dashboards, calculate true cohort LTV, and route that intelligence back into advertising algorithms to systematically improve the quality of future customer acquisition.
Table of Contents
In the previous guide, we built a real-time Stripe dashboard via Google Sheets for standard cohort analysis. That architecture handles most operational intelligence requirements for businesses processing under a few thousand transactions per month.
When transaction volume scales into the tens of thousands and your subscription matrix includes multiple products with different pricing tiers and cancellation patterns, spreadsheet-based analysis becomes structurally insufficient. You need direct programmatic access to the payment database. This is where Stripe Sigma analytics becomes the correct tool.
1. Stripe Sigma Analytics: What It Is and When to Use It
Stripe Sigma is an interactive SQL environment accessible directly from the Stripe dashboard under the Reports section. It runs on a Presto/Trino query engine against a real-time replica of your Stripe account’s transaction database, covering every object in your account — charges, subscriptions, invoices, customers, refunds, disputes, and balance transactions.
Query results return within seconds for most analytical queries, and results can be exported to CSV for further processing or routed through API calls for automated pipelines.
Presto/Trino, not generic ANSI SQL: Sigma’s engine has its own function names for some operations. Date-difference calculations use date_diff('day', start, end), not the DATEDIFF() syntax found in Redshift, Snowflake, or SQL Server.
Percentile calculations typically use approx_percentile() rather than the standard-SQL PERCENTILE_CONT() WITHIN GROUP construct. The queries in this guide use the correct Presto/Trino syntax throughout.
The distinction between Stripe Sigma and Stripe’s native charts is the difference between reading a summary and interrogating the source data. Native Stripe reports provide pre-built aggregations with fixed dimensions and limited filter options.
Sigma allows you to write arbitrary SQL with custom GROUP BY clauses, HAVING filters, window functions, date range calculations, and multi-table joins.
If you want to know the average revenue per user in their first 90 days for customers who signed up in Q1 2025 on your annual plan from a specific country, native reports cannot answer that question. A Sigma query can.
Sigma is available on Stripe’s Scale plan and above. For businesses whose analytical requirements have outgrown spreadsheet-based reporting and who want to avoid the cost and complexity of exporting Stripe data to an external data warehouse, Sigma provides a significant portion of enterprise BI capability within the Stripe environment itself.
When to use Sigma vs Google Sheets: Use the Google Sheets webhook pipeline for real-time transaction dashboards, blended CAC calculations, and standard cohort tables. Use Sigma when you need complex multi-table joins, window functions for rolling metrics, or queries that span your full historical transaction volume beyond what a spreadsheet can handle performantly.
2. The Key Database Tables
Stripe Sigma exposes your account data as a set of database tables. Understanding which tables to query for which analytical questions is the foundation of effective Sigma use.
| Table | Primary Use | Key Fields |
|---|---|---|
charges | All payment events — one-time and subscription | id, customer_id, amount, fee, status, disputed, created |
customers | Customer records and metadata | id, email, created, metadata |
subscriptions | Recurring billing status and lifecycle | id, customer_id, status, plan_id, created, canceled_at, current_period_end |
invoices | Subscription invoice history | id, customer_id, subscription_id, amount_paid, status, created |
balance_transactions | Net revenue after fee deduction | id, source, amount, fee, net, type, created |
refunds | Refund events and amounts | id, charge_id, amount, status, created |
disputes | Chargeback and dispute tracking | id, charge_id, amount, status, created |
Critical: All Monetary Amounts Are in Cents
Every monetary field in Stripe Sigma — amount, fee, net — is stored in the smallest currency unit. For USD, this means cents. A $997.00 charge appears as 99700 in the amount column.
All SELECT statements that return monetary values must divide by 100 to produce human-readable dollar amounts: SUM(amount) / 100.0 AS total_revenue. Using integer division without the decimal point produces truncated results for amounts that are not exact multiples of 100 cents.
3. SQL Queries for LTV and High-Value Cohorts
The most operationally valuable use of Stripe Sigma for most businesses is identifying high-LTV customer cohorts — the subset of customers whose cumulative transaction value represents disproportionate revenue contribution.
These cohorts are the signal that, when fed back into advertising algorithms as offline conversion events, causes the algorithm to find more customers who match those characteristics.
Here is the foundational LTV cohort query that identifies customers with more than five successful undisputed charges, ranked by total lifetime value:
This query produces a ranked list of your top customers by net lifetime value. The HAVING COUNT(ch.id) > 5 filter ensures you are only looking at customers with sustained payment history — not one-time high-value purchases that may not represent a repeatable customer profile.
Adjust the threshold based on your typical purchase frequency. Note the date_diff('day', ...) function — this is Presto/Trino’s syntax, not the DATEDIFF() form used in some other SQL dialects.
To identify your top 10% LTV cohort specifically — a cleaner signal for algorithmic training — use Presto/Trino’s approx_percentile() function to compute the 90th percentile threshold directly, without a separate subquery:
The export from this query — customer emails ranked by net LTV — is the audience payload you route back to Meta and Google to train the algorithm on the characteristics of your highest-value buyers.
4. Identifying Churn Signals with SQL
Churn analysis in Stripe Sigma goes beyond knowing your overall cancellation rate. The operationally valuable question is which specific products, pricing tiers, and customer cohort characteristics predict early cancellation.
Those signals allow you to intervene before churn occurs and to avoid acquiring new customers who match the high-churn profile.
This query identifies early churn by finding subscribers who cancelled within 30 days of their first charge, grouped by subscription plan to identify which products have the highest early cancellation rates:
If one pricing tier shows significantly higher 30-day churn than others, the product or onboarding experience for that tier is the intervention point.
If churn is concentrated in customers acquired in a specific month, investigate what changed in the acquisition channel or offer during that period. If churn correlates with specific geographic markets, the product may have a fit or expectation mismatch in those regions.
Four Churn Signal Categories to Query
- Early cancellation by plan: Which subscription tiers have the shortest average subscriber lifetimes? High churn on a specific plan is a product signal, not an acquisition signal.
- Refund rate by product: Query
refundsjoined tochargesgrouped by metadata product field. Refund concentration on specific offers indicates misalignment between marketing claims and product experience. - Dispute rate by acquisition cohort: Query
disputesjoined tochargesgrouped by charge creation month. High dispute rates in specific months may indicate low-quality traffic from a particular campaign or partner. - Payment failure by plan age: Query
invoiceswherestatus = 'uncollectible'orpayment_failedgrouped by months since subscription creation. Involuntary churn — where customers meant to stay but failed payment — often peaks at 12 months when stored cards expire.
5. The High-LTV Algorithmic Feedback Loop
Identifying high-LTV customers via Sigma is intelligence. Routing that intelligence back into advertising algorithms is execution.
The two steps together create a self-reinforcing acquisition system where your highest-value customers train the algorithm to find more customers who match their characteristics.
Meta’s Value Optimisation and Google’s Target ROAS bidding both function by building statistical models of the customers most likely to convert at high transaction values. These models improve with more signal data.
When you upload your top-10% LTV customers as offline conversion events with their actual lifetime transaction values attached, you are providing the algorithm with a higher-quality training dataset than the pixel alone can produce — particularly because the pixel likely missed a meaningful share of those customers’ conversion events due to tracking restrictions.
The practical implementation routes the Sigma query export through Make.com where each customer email is normalised to lowercase, stripped of whitespace, and SHA-256 hashed.
The hashed email is included in the user_data object of a CAPI offline conversion event, with the customer’s net LTV value passed in custom_data.value.
This tells Meta not just that a conversion occurred, but what the customer was worth — enabling Value Optimisation to bid higher for similar high-value prospects and lower for characteristics associated with low-LTV customers.
For the complete CAPI payload structure and SHA-256 normalisation sequence required for this upload, see our Facebook CAPI match quality guide. For the Google Enhanced Conversions equivalent, see our Google Ads Enhanced Conversions guide.
6. Infrastructure Stack
Make.com
Routes Sigma export data through SHA-256 hashing and CAPI payload formatting to Meta and Google offline conversion endpoints. Partner affiliate link.
Deploy Make.com →Stripe Sigma
The SQL analytics environment. Available on Stripe Scale plan and above. Full documentation in Stripe’s official developer reference.
Read Sigma Docs →Related Revenue Infrastructure Guides
Solidify Your Tracking Baseline First
Before implementing algorithmic feedback loops, ensure your fundamental server-side attribution is capturing all conversion events accurately. Review the complete ad tracking architecture guide.
Review the Ad Tracking Architecture Guide →7. Frequently Asked Questions
Stripe Sigma is an interactive SQL analytics environment built directly into the Stripe dashboard that provides programmatic access to every charge, subscription, customer, refund, dispute, and balance transaction as queryable database tables. It runs on a Presto/Trino query engine and allows operators to write SQL queries against their live transaction data to calculate custom metrics — LTV, cohort churn rates, plan-level revenue, and any other metric not available in Stripe’s pre-built native reports.
Not necessarily for basic reporting. Stripe provides pre-written query templates and an AI-assisted query builder for common financial calculations including MRR, churn rate, and basic LTV. For custom analysis beyond the templates — multi-table joins, window functions, HAVING filters, percentile calculations, or rolling metrics — SQL proficiency is required, specifically in Presto/Trino syntax rather than generic ANSI SQL, since some function names (like date differences and percentiles) differ from other SQL dialects. The queries on this page use the correct Presto/Trino syntax.
Stripe’s native reports provide pre-built charts for revenue, MRR, churn, and dispute rates with fixed dimensions and limited filter options — you can adjust date ranges and basic filters, but cannot change what the charts measure. Stripe Sigma gives you direct SQL access to the underlying database tables, allowing arbitrary query construction including custom joins between tables, GROUP BY aggregations on any dimension, HAVING filters, and window functions. The difference is between reading a pre-written summary and querying the raw data directly.
All monetary amounts in Stripe Sigma are stored in the smallest currency unit — cents for USD. A $100.00 charge appears as 10000 in the amount column. All SQL queries must divide by 100 in the SELECT statement to convert to standard dollar amounts: SUM(amount) / 100.0 AS revenue. Use 100.0 rather than 100 to avoid integer truncation for amounts that are not exact multiples of 100 cents.
Export customer emails from your Stripe Sigma LTV cohort query. In Make.com, normalise each email to lowercase and strip whitespace before applying SHA-256 hashing. Include the hashed email in the user_data.em field of a Meta CAPI offline Purchase event, and include the customer’s net LTV value in custom_data.value. Submit via the Meta Graph API conversions endpoint. This enables Value Optimisation bidding where Meta adjusts audience targeting based on the expected transaction value of different user characteristics.
The most useful tables are charges for all payment events and amounts, subscriptions for recurring billing status and cancellation dates, customers for customer metadata and creation timestamps, invoices for subscription invoice history, and balance_transactions for net revenue after fee deduction. Joining charges to customers on customer_id enables LTV analysis. Joining subscriptions to customers on customer_id enables churn cohort analysis by plan and acquisition month.
Query the subscriptions table joining to charges on customer_id, filter for subscriptions where canceled_at IS NOT NULL, and compute the number of days between each customer’s first charge date and their cancellation date using Presto’s date_diff('day', start, end) function. Group by plan_id to identify which subscription products have the highest 30-day and 90-day churn rates. Products with significantly higher early churn than others indicate a product fit or onboarding failure, not an acquisition quality problem.
Stripe Sigma is available on Stripe’s Scale plan and above. It is not included in standard Pay-as-you-go or Starter plans. Sigma queries run against a real-time replica of your transaction data with no additional per-query cost beyond the plan subscription. For businesses that need Sigma’s analytical capability but are not yet on a Scale plan, the Google Sheets webhook pipeline documented in our real-time Stripe dashboard guide provides comparable cohort analysis functionality at any Stripe plan level.