How to surface business insights with Stripe

There’s no shortage of data: customer and payment information are captured at every stage of the lifecycle, from engaging with marketing campaigns to making a purchase. Accurate and actionable data insights are key for businesses to understand and report on past performance, make informed decisions in the present day, and identify future opportunities. By slicing data in smart, strategic ways, businesses can improve:

Finance and accounting: Evaluate and forecast financial performance, manage costs, improve efficiency, and close books quickly and accurately.
Customer and product: Get granular insights into customer demographics and behavior, such as churn by specific segments. Additionally, monitor product revenue metrics such as monthly recurring revenue (MRR).
Operations: Data insights can provide revealing information about fraud, payments, and revenue operations.

However, according to IDC research, while most companies have access to data, they aren’t able to derive fundamental business insights from it because of quality issues, operational silos, and a lack of data standards.

Businesses on Stripe have access to a range of data, Dashboard reports, and products that make it easy to access these insights. This guide walks you through how to leverage Stripe data to track financial performance, better understand your customers, refine your product offerings, and run your companies more efficiently. We’ve also included sample SQL queries to make it easier for you to get valuable information out of Stripe’s advanced data analysis tools: Sigma and Data Pipeline.

Data available in Stripe

When Stripe processes payment transactions, we capture data points that provide valuable insights into customer transactions. The Stripe Schema is a blueprint that defines all of the Stripe data that is collected, including how it’s named, defined, and organized. Key data points include:

Transaction details (amount, currency, date, time)
Customer information (name, geography)
Product or service details (items purchased, quantity, price)
Payment method details (credit card type, digital wallet)
Metadata (Stripe allows businesses to attach custom metadata to transactions, which can include data specific to a business’s needs)
Risk and fraud indicators
Refund and dispute details

You can monitor your business performance at a high level by logging in to your Stripe Dashboard. The Dashboard houses several prebuilt reports that give you visibility into your overall sales, payments, disputes, refunds, subscriptions, and financial metrics. Some of the most-used free reports available in the Stripe Dashboard include:

Sales summary: These reports provide an overview of key sales metrics, including total sales volume, revenue, and number of successful transactions. This information can help you track your sales performance and monitor revenue trends.
Financial reporting: Stripe offers financial reports, including revenue breakdowns, fee details, and sales tax reports. These reports help in financial planning, tax reporting, and cost analysis.
Subscription insights: Stripe Billing users can monitor subscriber count, churn rate, and recurring revenue metrics—providing a view of subscription performance and retention.

Stripe also offers two advanced data tools that offer more customization capabilities when it comes to their business metrics and reporting:

Stripe Sigma is an interactive business insights tool within the Dashboard. You can get instant answers by writing custom SQL queries or selecting from templates that contain prewritten queries for common reporting needs. Additionally, Sigma Assistant, our AI-powered chat assistant, can help you get answers by simply typing in questions using natural language. Finally, you can transform query results into dynamic charts with the click of a button to easily visualize your data.
Stripe Data Pipeline sends all your up-to-date Stripe data to your external data storage destination. This connection can be set up with just a few clicks, allowing you to consolidate your Stripe data with other business data from across your systems, such as CRM, ERP, and others. Then, from within your centralized data storage, you can query your Stripe data along with other business data, enabling a range of teams to gather rich insights.

How to track key metrics with Stripe Sigma or Data Pipeline

To help you monitor the business insights that matter most, here is a list of metrics—organized by business area—that users have found useful. We also share some sample SQL queries that can be used in both Stripe Sigma and Data Pipeline, as well as prompts and templates that can be leveraged in Sigma to get answers without needing to write any SQL. We also have an exhaustive list of all the data available, which includes table sets from Core API, Interchange Plus, Connect, and more.

Finance and accounting

Finance and accounting teams require access to their business’s Stripe revenue data to close their books, reconcile accounts, and make forecasts and projections. Many rely on Sigma and Data Pipeline to streamline these processes.

指标	好处	SQL 查询示例	尝试询问 Sigma Assistant
总收入	了解您的企业在特定时间段内产生的收入额，以掌握业务绩效情况。	WITH successful_charges AS ( SELECT currency, SUM(amount - amount_refunded) AS net_amount -- subtract the refunded amount FROM charges WHERE status = ’succeeded’ AND created >= DATE_ADD(’month’, -3, CURRENT_DATE) GROUP BY currency ), paid_out_of_band_invoices AS ( -- Include paid out of band invoices in addition to charges SELECT currency, SUM(total) AS total_amount FROM invoices WHERE paid_out_of_band = true AND status = ’paid’ AND date >= DATE_ADD(’month’, -3, CURRENT_DATE) GROUP BY currency ), combined_revenue AS ( SELECT currency, SUM(net_amount) AS total_revenue FROM successful_charges GROUP BY currency UNION ALL SELECT currency, SUM(total_amount) AS total_revenue FROM paid_out_of_band_invoices GROUP BY currency ) SELECT currency, SUM( decimalize_amount_no_display(currency, total_revenue, 2) ) AS total_revenue_past_3_months FROM combined_revenue GROUP BY currency	我在过去 3 个月里获得了多少收入？
平均交易金额	确定通过 Stripe 处理的每笔交易的平均金额，以分析定价策略和客户支出模式。	WITH customer_transactions AS ( SELECT c.id AS customer_id, c.address_country AS country, SUM(ch.amount) AS total_amount FROM charges ch JOIN customers c ON ch.customer_id = c.id WHERE ch.status = ’succeeded’ GROUP BY c.id, c.address_country ) SELECT country, AVG(decimalize_amount_no_display(’USD’, total_amount, 2)) AS avg_transaction_value FROM customer_transactions GROUP BY country ORDER BY country;	按国家划分的客户平均交易金额（以美元计）是多少？
MRR	评估基于订阅的产品或服务在一个月内产生的可预测和经常性收入。	WITH sparse_mrr_changes AS ( SELECT DATE_TRUNC( ’day’, DATE(local_event_timestamp) ) AS date, currency, SUM(mrr_change) AS mrr_change_on_day FROM subscription_item_change_events GROUP BY 1, 2 ), sparse_mrrs AS ( SELECT date, currency, mrr_change_on_day, SUM(mrr_change_on_day) OVER ( PARTITION BY currency ORDER BY date ASC ) AS mrr FROM sparse_mrr_changes ORDER BY currency, date DESC ), fx AS ( SELECT date - INTERVAL ’1’ DAY AS date, cast( JSON_PARSE(buy_currency_exchange_rates) AS MAP(VARCHAR, DOUBLE) ) AS rate_per_usd FROM exchange_rates_from_usd ), currencies AS ( SELECT DISTINCT(currency) FROM subscription_item_change_events ), date_currency AS ( SELECT date, rate_per_usd, currency FROM fx CROSS JOIN currencies ORDER BY date, currency ), date_currency_mrr AS ( SELECT dpc.date, dpc.currency, dpc.rate_per_usd, mrr_change_on_day, mrr AS _mrr, LAST_VALUE(mrr) IGNORE NULLS OVER ( PARTITION BY dpc.currency ORDER BY dpc.date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS mrr FROM date_currency dpc LEFT JOIN sparse_mrrs sm on dpc.date = sm.date AND dpc.currency = sm.currency ), daily_mrrs_pre_fx AS ( SELECT date, currency, rate_per_usd, SUM(mrr) AS mrr FROM date_currency_mrr GROUP BY 1, 2, 3 ORDER BY date DESC ), daily_mrrs AS ( SELECT date, -- change usd below to the currency you want your report in SUM( ROUND( mrr / rate_per_usd [currency] * rate_per_usd [’usd’] ) ) AS total_mrr_in_usd_minor_units FROM daily_mrrs_pre_fx GROUP BY 1 ), months AS ( SELECT date_col - (INTERVAL ’1’ DAY) AS month_end FROM UNNEST( SEQUENCE( CAST(DATE_FORMAT(CURRENT_DATE, ’%Y-%m-01’) AS date) - INTERVAL ’12’ MONTH, CURRENT_DATE, INTERVAL ’1’ MONTH ) ) t (date_col) ), monthly_mrrs AS ( SELECT month_end, -- change usd below to the currency you want your report in DECIMALIZE_AMOUNT_NO_DISPLAY(’usd’, dm.total_mrr_in_usd_minor_units, 2) AS total_mrr_in_usd FROM months m LEFT JOIN daily_mrrs dm ON m.month_end = dm.date ORDER BY month_end DESC ) SELECT * FROM monthly_mrrs	Sigma 模板：月度经常性收入 (MRR) 总额
报税	通过按客户地点跟踪纳税义务，从而确保履行纳税义务。	WITH tax_amounts as ( select li.amount, li.amount_tax, li.tax_behavior, li.currency, li.determined_destination_address_state, li.determined_destination_address_country from tax_transaction_line_items li union all select sc.amount, sc.amount_tax, sc.tax_behavior, sc.currency, sc.determined_destination_address_state, sc.determined_destination_address_country from tax_transaction_shipping_costs sc ), tax_liability as ( select determined_destination_address_country as customer_location_country, determined_destination_address_state as customer_location_state, currency as presentment_currency, sum( ( case when tax_behavior = ’inclusive’ then amount - amount_tax else amount end ) ) as total_sales_excluding_tax, sum(amount_tax) as total_tax from tax_amounts group by 1, 2, 3 ) select customer_location_country, customer_location_state, -- Learn more about currencies at Stripe: https://docs.stripe.com/currencies presentment_currency, stringify_amount( presentment_currency, total_sales_excluding_tax, ’.’ ) as total_sales_excluding_tax, stringify_amount(presentment_currency, total_tax, ’.’) as total_tax from tax_liability order by 1, 2, 3	Sigma 模板：税务义务（按客户地点）
应收账款账龄/收款/未付账单	分析应收账款账龄，以监控未结账单，并发现客户付款和收款中的潜在问题。	WITH outstanding_invoices AS ( SELECT invoices.customer_id, SUM(invoices.amount_due) AS total_outstanding FROM invoices WHERE invoices.status = ’open’ AND invoices.due_date > CURRENT_DATE GROUP BY invoices.customer_id ), ranked_customers AS ( SELECT customer_id, total_outstanding, ROW_NUMBER() OVER (ORDER BY total_outstanding DESC) AS rank FROM outstanding_invoices ) SELECT rc.customer_id, rc.total_outstanding / 100.0 AS total_outstanding_amount, c.email FROM ranked_customers rc JOIN customers c ON rc.customer_id = c.id WHERE rc.rank <= 10 ORDER BY rc.rank;	确定未结账单金额最高的前 10 名客户。

Customer and product

Product teams can use these metrics to make data-driven product enhancements and identify growth opportunities. Sales and marketing teams can target opportunities more effectively with an understanding of customer profiles.

指标	好处	SQL 查询示例	尝试询问 Sigma Assistant
客户细分	明确细分客户群体（例如，贡献最多收入或购买次数最多的客户群体），以了解忠诚客户的概况。监控客户留存率，以识别忠诚客户并制定对应的留客策略。	WITH customer_purchases AS ( SELECT c.id AS customer_id, COUNT(ch.id) AS purchase_count FROM customers c JOIN charges ch ON c.id = ch.customer_id WHERE ch.status = ’succeeded’ GROUP BY c.id ), ranked_customers AS ( SELECT customer_id, purchase_count, RANK() OVER (ORDER BY purchase_count DESC) AS purchase_rank FROM customer_purchases	显示购买次数最多的前 10 名客户。
MRR 按产品排名	跟踪不同产品的经常性收入增长。	WITH sparse_mrr_changes AS ( SELECT DATE_TRUNC( ’day’, DATE(local_event_timestamp) ) AS date, currency, product_id, SUM(mrr_change) AS mrr_change_on_day FROM subscription_item_change_events GROUP BY 1, 2, 3 ), sparse_mrrs AS ( SELECT date, currency, product_id, mrr_change_on_day, SUM(mrr_change_on_day) OVER ( PARTITION BY currency, product_id ORDER BY date ASC ) AS mrr FROM sparse_mrr_changes ORDER BY product_id, currency, date DESC ), fx AS ( SELECT date - INTERVAL ’1’ DAY AS date, CAST( JSON_PARSE(buy_currency_exchange_rates) as MAP(VARCHAR, DOUBLE) ) AS rate_per_usd FROM exchange_rates_from_usd ), segments AS ( SELECT DISTINCT(product_id) FROM subscription_item_change_events ), currencies AS ( SELECT DISTINCT(currency) FROM subscription_item_change_events ), date_segment_currency AS ( SELECT date, rate_per_usd, product_id, currency FROM fx CROSS JOIN segments CROSS JOIN currencies ORDER BY date, currency, product_id ), date_segment_currency_mrr AS ( SELECT dsc.date, dsc.product_id, dsc.currency, dsc.rate_per_usd, mrr_change_on_day, mrr AS _mrr, LAST_VALUE(mrr) IGNORE NULLS OVER ( PARTITION BY dsc.product_id, dsc.currency ORDER BY dsc.date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS mrr FROM date_segment_currency DSC LEFT JOIN sparse_mrrs sm ON dsc.date = sm.date AND dsc.product_id = sm.product_id AND dsc.currency = sm.currency ), daily_mrrs_pre_fx AS ( SELECT date, product_id, currency, rate_per_usd, SUM(mrr) as mrr FROM date_segment_currency_mrr GROUP BY 1, 2, 3, 4 ), daily_mrrs AS ( SELECT date, product_id, -- change usd below to the currency you want your report in SUM(ROUND(mrr / rate_per_usd [currency] * rate_per_usd [’usd’])) as total_mrr_in_usd_minor_units FROM daily_mrrs_pre_fx GROUP BY 1, 2 ), months AS ( SELECT date_col - (INTERVAL ’1’ DAY) AS month_end FROM UNNEST( SEQUENCE( CAST(DATE_FORMAT(CURRENT_DATE, ’%Y-%m-01’) AS date) - INTERVAL ’12’ MONTH, CURRENT_DATE, INTERVAL ’1’ MONTH ) ) t (date_col) ), monthly_mrrs AS ( SELECT month_end, dm.product_id, -- change usd below to the currency you want your report in DECIMALIZE_AMOUNT_NO_DISPLAY(’usd’, dm.total_mrr_in_usd_minor_units, 2) AS total_mrr_in_usd FROM months m LEFT JOIN daily_mrrs dm ON m.month_end = dm.date ORDER BY 1 DESC, 3 DESC, 2 ) SELECT p.name, * FROM monthly_mrrs mrr JOIN products p ON mrr.product_id = p.id	Sigma 模板：按产品排名的月度经常性收入 (MRR) 总额
流失	衡量客户停止使用您的产品或服务的比率。按细分客户群体或产品类别分析客户流失率，以明确重点关注领域。	WITH subscription_starts AS ( SELECT s.customer_id, p.id AS product_id, COUNT() AS subscription_count FROM subscriptions s JOIN subscription_items si ON s.id = si.subscription_id JOIN prices pr ON si.price_id = pr.id JOIN products p ON pr.product_id = p.id WHERE s.status = ’active’ AND s.created >= DATE_ADD(’year’, -1, CURRENT_DATE) GROUP BY s.customer_id, p.id ), subscription_cancellations AS ( SELECT s.customer_id, p.id AS product_id, COUNT() AS cancellation_count FROM subscriptions s JOIN subscription_items si ON s.id = si.subscription_id JOIN prices pr ON si.price_id = pr.id JOIN products p ON pr.product_id = p.id WHERE s.status IN (’canceled’, ’unpaid’) AND s.canceled_at >= DATE_ADD(’year’, -1, CURRENT_DATE) GROUP BY s.customer_id, p.id ), product_churn AS ( SELECT ss.product_id, COALESCE(sc.cancellation_count, 0) AS cancellations, ss.subscription_count AS starts FROM subscription_starts ss LEFT JOIN subscription_cancellations sc ON ss.customer_id = sc.customer_id AND ss.product_id = sc.product_id ), churn_rate_by_product AS ( SELECT p.name AS product_name, SUM(pc.cancellations) AS total_cancellations, SUM(pc.starts) AS total_starts, ROUND(SUM(pc.cancellations) / SUM(pc.starts), 4) AS churn_rate FROM product_churn pc JOIN products p ON pc.product_id = p.id GROUP BY p.name ) SELECT product_name, total_cancellations, total_starts, churn_rate FROM churn_rate_by_product ORDER BY churn_rate DESC;	Sigma 模板：每日流失收入或订户流失人数
不同产品或订阅业务的受欢迎程度/季节性	根据销量或收入确定最受欢迎的产品或服务。跟踪新产品发布或新功能版本的受欢迎程度	WITH sales_per_product AS ( SELECT YEAR(c.created) AS sales_year, pr.id AS product_id, COUNT(*) AS total_sales FROM charges c JOIN invoice_line_items ili ON c.invoice_id = ili.invoice_id JOIN prices p ON ili.price_id = p.id JOIN products pr ON p.product_id = pr.id WHERE c.status = ’succeeded’ GROUP BY 1, 2 ), ranked_products AS ( SELECT spp.sales_year, spp.product_id, p.name AS product_name, spp.total_sales, RANK() OVER ( PARTITION BY spp.sales_year ORDER BY spp.total_sales DESC ) AS rank FROM sales_per_product spp JOIN products p ON spp.product_id = p.id ) SELECT sales_year, product_id, product_name, total_sales FROM ranked_products WHERE rank = 1 ORDER BY sales_year ASC;	哪些产品每年最受欢迎？
折扣和优惠券的影响	评估特定折扣的有效性，以确定价格弹性，并为定价策略的制定提供依据。	WITH discount_transactions AS ( SELECT c.id AS charge_id, c.amount, c.currency, ’With Discount’ AS discount_status FROM charges c JOIN invoice_line_items ili ON c.invoice_id = ili.invoice_id JOIN invoice_line_item_discount_amounts ilida ON ili.id = ilida.invoice_line_item_id WHERE c.status = ’succeeded’ GROUP BY c.id, c.amount, c.currency ), no_discount_transactions AS ( SELECT c.id AS charge_id, c.amount, c.currency, ’Without Discount’ AS discount_status FROM charges c LEFT JOIN invoice_line_items ili ON c.invoice_id = ili.invoice_id LEFT JOIN invoice_line_item_discount_amounts ilida ON ili.id = ilida.invoice_line_item_id WHERE c.status = ’succeeded’ AND ilida.invoice_line_item_id IS NULL GROUP BY c.id, c.amount, c.currency ), unioned AS ( SELECT * FROM discount_transactions UNION ALL SELECT * FROM no_discount_transactions ), aggregated AS ( SELECT discount_status, currency, AVG(amount) AS avg_order_value FROM unioned GROUP BY discount_status, currency ) SELECT discount_status, currency, decimalize_amount_no_display(currency, avg_order_value, 2) AS avg_order_value FROM aggregated ORDER BY discount_status DESC, currency ASC	使用折扣代码与未使用折扣代码交易的平均订单金额分别是多少？

Operations

Operations teams can find several ways to run their businesses more efficiently, including improving fraud and risk detection.

指标	好处	SQL 查询示例	尝试询问 Sigma Assistant
日常活动（例如，付款总额、退款和争议）	监控日常运营状态，以发现潜在威胁或问题。	SELECT COUNT(id) AS total_disputes_today FROM disputes WHERE DATE(created) = CURRENT_DATE;	我们今天出现了多少起争议？
识别欺诈交易	尽可能减少财务损失，维持客户信任，防止撤单，保障数据安全，确保监管合规，并获得宝贵洞察，以持续改进防欺诈措施。	WITH fraudulent_transactions AS ( SELECT charges.payment_method_type, COUNT(*) AS total_fraudulent_transactions, SUM(charges.amount) AS total_fraudulent_amount FROM charges INNER JOIN disputes ON charges.id = disputes.charge_id WHERE disputes.reason = ’fraudulent’ GROUP BY charges.payment_method_type ) SELECT payment_method_type, total_fraudulent_transactions, SUM(total_fraudulent_amount) / 100.0 AS total_fraudulent_amount_usd FROM fraudulent_transactions GROUP BY payment_method_type, total_fraudulent_transactions ORDER BY total_fraudulent_transactions DESC	按支付方式提供欺诈交易明细。
支付转化漏斗	优化创收，提升用户体验，明确流程改进要点，评估营销效果，发现异常或问题，并保持绩效对标。通过持续监控和优化转化漏斗，运营团队可以推动增长、提高客户满意度并实现业务成功的最大化。	WITH cart_sessions AS ( SELECT COUNT(*) AS total_sessions FROM checkout_sessions WHERE created >= DATE_TRUNC(’month’, CURRENT_DATE) AND created < DATE_ADD(’month’, 1, DATE_TRUNC(’month’, CURRENT_DATE)) ), successful_transactions AS ( SELECT	本月从购物车到成功交易的转化率是多少？

How to combine Stripe data with other data sources using Data Pipeline

Stripe Data Pipeline seamlessly connects your Stripe account to your data warehouse or cloud storage account, where you can analyze Stripe data in combination with data from your other systems. From there, you can then access additional business insights that can improve the following:

Customer journey analysis

Analyzing customer interactions, purchase behavior, and support interactions from CRM data alongside transactional Stripe data provides a holistic view of the customer experience. You can identify patterns, improve customer engagement, and enhance conversion rates.

ChowNow, a commission-free online food ordering platform, used Data Pipeline to combine Stripe data with other business data. This resulted in a clear overview of the customer journey for its restaurants and dining customers. In-depth insights into the restaurant onboarding process and diners’ ordering decisions helped ChowNow’s marketing team fine-tune advertising spending and decrease customer acquisition cost.

Financial planning and reporting

By analyzing revenue data alongside expenses, cash flow, and profitability metrics, you can enhance business operations. For example, you can more effectively monitor overall financial performance, create revenue forecasts, refine financial planning, and make data-informed decisions to drive financial growth.

When Lime adopted Stripe Data Pipeline and matched Stripe data with its own reports, the company gained the ability to track refunds in near-real time. Its finance team can now confidently report on Lime’s cash status.

Operational efficiency

Analyzing Stripe transactions alongside inventory data helps you maintain optimal stock levels, reduce inventory costs, and improve overall operational efficiency—including streamlining supply chain performance.

Churn prevention

By analyzing customer behavior alongside transaction history and support interactions, you can identify at-risk customers, personalize retention strategies, and enhance customer loyalty.

Marketing campaign effectiveness

Integrating Stripe data with marketing data allows you to analyze the impact of marketing campaigns on customer acquisition, conversion rates, and revenue. You can measure the return on investment (ROI) of marketing efforts, identify successful campaigns, target personalized content for user profiles that are most interested in buying certain products, and allocate resources effectively to enhance marketing strategies.

Once you’ve identified the most important metrics for your business, you can easily save them for future reference—or schedule them in advance so you have the reports you need, when you need them. For Sigma users, this docs page has more information on how to schedule queries. For Data Pipeline users, you can easily find the “save” and “schedule” buttons in Redshift and Snowflake.

Improve decision-making with data insights

Stripe offers a comprehensive platform that helps you not only process payments, but also gain valuable insights to improve your business operations and bottom line. Our tools empower more teams across your organization—including finance, product, operations, sales, marketing, and fraud teams—to make data-driven decisions every day.

Get started with a free 30-day trial of Sigma or Data Pipeline today.

全球支付

资金管理

收入和财务自动化

更多

按阶段

按业务模式

按用例

生态系统

开始

指南