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 アシスタントに質問してみる
総収入	特定の期間にビジネスが獲得した収入を把握して、業績の分析情報を取得します。	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	サブスクリプションベースの商品やサービスによって 1 カ月間に生み出される予測可能な経常収益を測定します。	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 アシスタントに質問してみる
顧客セグメント	最も売上の大きい顧客セグメントや最も購入頻度の高い顧客セグメントなどを特定して、ロイヤルティの高い顧客のプロフィールを把握します。顧客維持率をモニタリングしてロイヤルティの高い顧客を特定し、そのような顧客を維持するための戦略を策定できるようにします。	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 テンプレート: 1 日あたりの解約された売上、または 1 日あたりのサブスクリプション解約者数
各製品やサブスクリプションの人気度 / 季節性	販売量や収入に基づいて最も人気があった商品やサービスを特定します。新しい製品や機能のリリースの人気度を追跡します。	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 アシスタントに質問してみる
毎日のアクティビティー (支払い、返金、不審請求の申し立ての各合計件数など)	日々の運用のステータスをモニタリングして、潜在的な脅威や問題を特定します。	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.

グローバル決済プラットフォーム

資金管理

収益・財務オートメーション

その他

企業規模別

ビジネスモデル別

ユースケース別

エコシステム

利用を始める

ガイド