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.

Statistik	Fördel	Exempel på SQL-fråga	Prova att fråga Sigma-assistenten
Totala intäkter	Lär dig mer om hur stora intäkter ditt företag har genererat under en viss tidsperiod för att få insikt i företagets resultat.	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	Hur stora intäkter hade jag under de senaste tre månaderna?
Genomsnittligt transaktionsvärde	Fastställ det genomsnittliga värdet för varje transaktion som behandlas via Stripe för att analysera prisstrategier och kundernas utgiftsmönster.	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;	Vilket är det genomsnittliga transaktionsvärdet i dollar för kunder per land?
MRR	Mät de förutsägbara och återkommande intäkter som genereras av abonnemangsbaserade produkter eller tjänster under en månad.	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-mall: Totala månatliga återkommande intäkter (MRR)
Skatterapportering	Säkerställ efterlevnad av skattskyldighet genom att spåra skattskyldighet per kundplats.	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-mall: Skattskyldighet efter kundens plats
Åldersanalys av förfallna kundfordringar/inkasseringar/obetalda fakturor	Gör en åldersanalys av förfallna kundfordringar för att övervaka utestående fakturor och identifiera potentiella problem med kundbetalningar och inkasseringar.	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;	Identifiera de tio kunder som har de största utestående fakturorna.

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.

Statistik	Fördel	Exempel på SQL-fråga	Prova att fråga Sigma-assistenten
Kundsegmentering	Identifiera olika kundsegment, exempelvis de som genererar de största intäkterna eller gör de mest frekventa köpen, för att få en förståelse för profilen hos lojala kunder. Övervaka kundretentionen för att identifiera lojala kunder och utveckla strategier för hur du kan behålla dem.	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	Visa mig de tio kunder som har den största köpfrekvensen.
MRR per produkt	Följ upp ökningen av återkommande intäkter vad gäller specifika produkter.	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-mall: Totala månatliga återkommande intäkter (MRR) per produkt
Kundbortfall	Mät i vilken takt kunder slutar använda dina produkter eller tjänster. Analysera kundbortfallet per kundsegment eller produktkategori för att identifiera problemområden.	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-mallar: Intäktsbortfall per dag eller bortfallna abonnenter per dag
Popularitet/säsongsvariationer för olika produkter eller abonnemang	Identifiera de mest populära produkterna eller tjänsterna baserat på försäljningsvolym eller intäkter. Följ upp hur populära nya produktlanseringar eller funktioner är.	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;	Vilka produkter har varit mest populära varje år?
Effekt av rabatter och kuponger	Mät effektiviteten hos specifika rabatter för att fastställa priselasticiteten och lägga en grund för prisstrategin.	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	Vilket är det genomsnittliga beställningsvärdet för transaktioner vid användning av rabattkoder jämfört med transaktioner utan några rabattkoder?

Operations

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

Statistik	Fördel	Exempel på SQL-fråga	Prova att fråga Sigma-assistenten
Daglig aktivitet (t.ex. totalt antal betalningar, återbetalningar, tvister)	Övervaka status för den dagliga verksamheten för att identifiera potentiella hot eller problem.		Hur många tvister hade vi idag?
Identifiering av bedrägliga transaktioner	Minimera de ekonomiska förlusterna, behåll kundernas förtroende, skydda dig mot chargebacks, behåll datasäkerheten, säkerställ efterlevnaden av regelverk och få värdefulla insikter för att kontinuerligt kunna förbättra åtgärderna för att förhindra bedrägerier.	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	Ta fram en specifikation av bedrägliga transaktioner per betalningsmetod.
Konverteringstratt för betalningar	Optimera intäktsgenereringen, förbättra användarupplevelsen, identifiera områden för processförbättring, mät marknadsföringens effektivitet, identifiera avvikelser eller problem och jämför resultat. Genom att kontinuerligt övervaka och optimera konverteringstratten kan det operativa teamet få tillväxten att öka, förbättra kundnöjdheten och maximera företagets framgångar.		Hur ser konverteringsgraden ut från kundvagn till genomförd transaktion den här månaden?

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.

Globala betalningar

Penninghantering

Intäkts- och ekonomiautomatisering

Mer

Efter fas

Efter affärsmodell

Efter användningsfall

Ecosystem

Kom igång

Guider