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.

Métrica	Beneficio	Ejemplo de consulta SQL	Prueba preguntarle al Asistente de Sigma
Ingresos totales	Comprende cuántos ingresos generó tu empresa durante un período de tiempo específico para obtener información sobre el rendimiento del negocio.	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	¿Cuántos ingresos obtuve en los últimos tres meses?
Valor medio de las transacciones	Determina el valor medio de cada transacción procesada a través de Stripe para analizar las estrategias de precios y los patrones de gasto de los clientes.	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;	¿Cuál es el valor medio en dólares de las transacciones de los clientes por país?
MRR	Mide los ingresos previsibles y recurrentes generados por los productos o servicios basados en suscripciones a lo largo de un mes.	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	Plantilla de Sigma: total de ingresos recurrentes mensuales (MRR)
Declaraciones fiscales	Garantiza el cumplimiento de la normativa de las obligaciones fiscales mediante el seguimiento de las obligaciones fiscales por ubicación del cliente.	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	Plantilla de Sigma: monto de las obligaciones tributarias por ubicación del cliente
Vencimiento de cuentas por cobrar/cobros/facturas no pagadas	Analiza el vencimiento de las cuentas por cobrar para monitorear las facturas pendientes e identificar posibles problemas con los pagos y cobros de los clientes.	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;	Identifica a los 10 clientes con las facturas pendientes más elevadas.

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.

Métrica	Beneficio	Ejemplo de consulta SQL	Prueba preguntarle al Asistente de Sigma
Segmentación de clientes	Identifica segmentos de clientes, como los que generan mayores ingresos o realizan compras más frecuentes, para conocer los perfiles de los clientes fieles. Monitorea las tasas de retención de clientes para identificar a los clientes fieles y desarrollar estrategias para retenerlos.	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	Muéstrame los 10 clientes con mayor frecuencia de compra.
MRR por producto	Sigue el crecimiento de los ingresos recurrentes por productos distintos.	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	Plantilla de Sigma: total de ingresos recurrentes mensuales (MRR) por producto
Abandono de clientes	Mide el ritmo al que los clientes dejan de utilizar tus productos o servicios. Analiza la tasa de pérdida de clientes por segmento de clientes o categoría de producto para identificar áreas de preocupación.	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;	Plantillas de Sigma: ingresos o suscriptores perdidos al día por abandono de clientes
Popularidad/estacionalidad de diferentes productos o suscripciones	Identifica los productos o servicios más populares en función del volumen de ventas o ingresos. Sigue la popularidad de los lanzamientos de nuevos productos o funcionalidades.	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;	¿Qué productos fueron los más populares cada año?
Impacto de los descuentos y cupones	Mide la eficacia de descuentos específicos para determinar la elasticidad de los precios y establecer la estrategia de precios.	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	¿Cuál es el valor medio de los pedidos de las transacciones que utilizan códigos de descuento frente a las transacciones sin códigos de descuento?

Operations

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

Métrica	Beneficio	Ejemplo de consulta SQL	Prueba preguntarle al Asistente de Sigma
Actividad diaria (por ejemplo, pagos totales, reembolsos, disputas)	Monitorea el estado de las operaciones cotidianas para identificar posibles amenazas o problemas.		¿Cuántas disputas tuvimos hoy?
Identificación de transacciones fraudulentas	Minimiza las pérdidas financieras, mantén la confianza de los clientes, protégete contra los contracargos, preserva la seguridad de los datos, garantiza el cumplimiento de la normativa y obtén información valiosa para la mejora continua de las medidas de prevención de fraude.	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	Proporciona un desglose de las transacciones fraudulentas por método de pago.
Canal de conversión de pagos	Optimiza la generación de ingresos, mejora la experiencia de usuario, identifica áreas de mejora de procesos, mide la eficacia del marketing, identifica anomalías o problemas y evalúa el rendimiento. Mediante el monitoreo y la optimización continua del canal de conversión, el equipo de operaciones puede acelerar el crecimiento, mejorar la satisfacción del cliente y maximizar el éxito empresarial.		¿Cuál es la tasa de conversión del carrito a una transacción exitosa este mes?

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.

Pagos internacionales

Finanzas y pagos integrados

Paquete de automatización contable

Más

Por etapa

Por modelo de negocio

Por caso de uso

Ecosistema

Empezar

Guías