For some time now I was looking a good way tracking and understanding how Blendo operates as a business and more about people that become customers. In this post, we will go through how to get and analyze payments data from one of the most popular platforms for payments, Stripe.
I have included a version of SQL code to calculate the most important payment metrics from Stripe data which you can use as is or get inspired to build upon it.
You can apply the same approach chart your customer support KPIs, Facebook Ads KPIs, customer success KPIs or email marketing KPIs.
There are standalone tools and Stripe with Sigma that will help you create quick dashboards but there is a certain value to have all your data in one place. Especially when no engineering or development resources or time are needed to succeed this.
Why all my data in one place?
Having all of your sales, marketing or support data in a data warehouse gives you the flexibility and independence to express any question or SQL query or build and analysis of your business data. Instead of having the default, pre-built reports, you get the versatility perk.
To succeed in having your data in one place you will need a data integration tool to integrate and sync all your data from Stripe into a database, prepare the data model for you and that will require no maintenance, no integration pain and no development time. Then a tool to explore your Stripe data and graph it.
The SQL code here can be used as-is if you use the data model that Blendo creates automatically for you in your database. The charts are made with one of the tools from our BI tools and dashboards partners, Chartio.
![]() |
![]() |
Sync your billing and subscription data from Stripe to any data warehouse. Analytics-ready data with no hassle. | Integrate Now |
What are the Top KPIs in Stripe?
So you needed a few minutes to integrate Stripe with your data warehouse and you are ready to think of some SQL queries to start exploring your data. Where can you start?
Here are some of the most important KPIs for Stripe including the SQL code you need to get started right away. These KPIs are categorized as:
Payments & Charges
- Failed Charges
- Charges status
- Charge Failure Reason
- Delinquent Customers
- Total Unpaid Invoices
Gross Charges vs Refunds
- Gross Charges vs Refunds
- Number of Charges over Time
- Total Charges
- Total Refund Count
- Total Gross Charges
- Total Amount Refunded
Cashflow
- Cashflow
MRR
- MRR
Customers
- Disputes & Fraud
- Number of disputes
- Status of disputes
- Type of disputes
- New Customers over time
- New Customers
- Most Profitable Users with Total Spend
- Card Type
- Country
Payments & Charges
Failed Charges
For a variety of reasons, some of your charges may fail to succeed. Whether it is a bank-declined payment, a blocked by Stripe payment or just an invalid API call, you should be aware of the total declined amount and of their type in order to proceed to the appropriate actions. For a detailed explanation, you can visit Stripe’s Documentation.
Past 6 Months: Total Failed Charges
SELECT sum(amount/100) FROM stripe_charges WHERE status = 'failed' AND now()-created < interval '6' MONTH
Past 6 months: Charges status
SELECT status, count(*) FROM stripe_charges WHERE now()-created < interval '6' MONTH GROUP BY status
Charge Failure Reason
Past 6 Month: Charge Failure Reason
SELECT failure_message, count(*), sum((amount-amount_refunded)/100) FROM stripe_charges WHERE status = 'failed' AND now()-created < interval '6' MONTH GROUP BY failure_message
Delinquent Customers
Customers whose latest invoice payment failed are marked as delinquent customers. Monitor their current number along with the indicators mentioned above allows you to control and deal with unpaid invoices.
Total Delinquent Customers
SELECT count(*) FROM stripe_customers WHERE delinquent != FALSE
Total Unpaid Invoices
Along with the failed charges mentioned above, another important indicator you need to monitor is the unpaid invoices. Unpaid invoices indicate some kind of problem with the connected payment source, such as Actual lack of payment source, invalid or expired payment sources.
Total Unpaid Invoices
SELECT count(*)-sum(paid::int) FROM stripe_charges
Gross Charges vs Refunds
When talking about Gross Charges we usually refer to the amount we get when subtracting the cost of goods sold from the revenue. Although Stripe doesn’t include information about products you can enrich the following query with data from other sources such as an ERP system.
Along with Gross charges, refunds are also being monitored.
Past 6 Months: Gross Charges vs Refunds
SELECT left(date_trunc('month', stripe_charges.created)::text, 7), sum((stripe_charges.amount-stripe_charges.amount_refunded)/100) AS charge, (-1)*sum(stripe_refunds.amount/100) AS refund FROM stripe_charges LEFT JOIN stripe_refunds ON stripe_charges.id = stripe_refunds.charge WHERE (now()-stripe_charges.created < interval '6' MONTH) AND stripe_charges.status != 'failed' GROUP BY left(date_trunc('month', stripe_charges.created)::text, 7) ORDER BY left(date_trunc('month', stripe_charges.created)::text, 7)
Number of Charges over Time
Past 6 Months: Number of Charges over Time
SELECT left(date_trunc('month', created)::text, 7), count(*), sum((amount-amount_refunded)/100) FROM stripe_charges WHERE now()-created < interval '6' MONTH AND status != 'failed' GROUP BY left(date_trunc('month', created)::text, 7) ORDER BY left(date_trunc('month', created)::text, 7)
Total Charges
Past 6 Months: Total Charges
SELECT count(*) FROM stripe_charges WHERE status != 'failed' AND now()-created < interval '6' MONTH
Past 6 Months: Total Refund Count
SELECT count(*) FROM stripe_refunds WHERE now()-created < interval '6' MONTH
Past 6 Months: Total Gross Charges
SELECT sum((stripe_charges.amount-stripe_charges.amount_refunded)/100) AS charge FROM stripe_charges LEFT JOIN stripe_refunds ON stripe_charges.id = stripe_refunds.charge WHERE (now()-stripe_charges.created < interval '6' MONTH) AND stripe_charges.status != 'failed'
Past 6 Months: Total Amount Refunded
SELECT sum(amount/100) FROM stripe_refunds WHERE now()-created < interval '6' MONTH
![]() |
![]() |
Sync your billing and subscription data from Stripe to any data warehouse. Analytics-ready data with no hassle. | Integrate Now |
Cashflow
Even the simplest financial dashboard would be incomplete without a query that measures cashflow. Cashflow reflects the money coming in every month and can be calculated like this :
Cashflow
SELECT MONTH, amount-refund AS cashflow FROM (SELECT amount.*, (CASE WHEN refund IS NULL THEN 0 ELSE refund END)refund FROM (SELECT date_trunc('month', created) AS MONTH, sum(data_object_amount/100) AS amount FROM stripe_events WHERE data_object_amount IS NOT NULL AND data_object_description != 'STRIPE TRANSFER' AND data_object_status = 'succeeded' AND date_trunc('month', created) >= '12/1/2016' AND date_trunc('month', created) < date_trunc('month', now()) AND data_object_amount_refunded = 0 GROUP BY date_trunc('month', created) ORDER BY MONTH)amount LEFT JOIN (SELECT date_trunc('month', created) AS MONTH, sum(data_object_amount_refunded/100) AS refund FROM stripe_events WHERE data_object_amount IS NOT NULL AND data_object_description != 'STRIPE TRANSFER' AND data_object_status = 'succeeded' AND date_trunc('month', created) >= '12/1/2016' AND date_trunc('month', created) < date_trunc('month', now()) AND data_object_amount_refunded > 0 GROUP BY date_trunc('month', created) ORDER BY MONTH)refund ON amount.month = refund.month) REF
MRR
The way MRR is calculated can vary significantly from company to company. For a subscription service, in the simplest of all cases, when assuming that customers pay month by month the amount of their subscription without applying discounts or prepaying months, MRR is equal to cashflow.
Unfortunately, the common case is quite more complicated and so, for computing the MRR you should take into account what applies best to your business case.
Customers
Disputes & Fraud
When it comes to disputes it is important to monitor both their number and their status along with their type. Depending on the dispute’s type you may perform certain actions and adduce certain evidence. You can learn more about the different dispute types here.
Past 6 Months: Number of disputes
SELECT count(*) FROM stripe_disputes WHERE now()-created< interval '6' MONTH
Past 6 Months: Status of disputes
SELECT status, count(*) FROM stripe_disputes WHERE now()-created< interval '6' MONTH GROUP BY status
Type of disputes
SELECT TYPE, count(*) FROM stripe_disputes WHERE now()-created< interval '6' MONTH GROUP BY TYPE
![]() |
![]() |
Sync your billing and subscription data from Stripe to any data warehouse. Analytics-ready data with no hassle. | Integrate Now |
New Customers over time
The steady growth of your customer base in order to maximize its revenue is the ulterior goal of every company. The following query helps you monitoring exactly this.
Past 6 Months: New Customers
SELECT left(date_trunc('month', created)::text, 7), count(*) FROM stripe_customers WHERE now()-created < interval '6' MONTH GROUP BY left(date_trunc('month', created)::text, 7) ORDER BY left(date_trunc('month', created)::text, 7)
Top Customers with Total Spend
Knowing your most valuable and delighted customers is important for every company. Good customers will make big purchases and generate a substantial portion of your revenues.
Most Profitable Users with Total Spend
SELECT email AS "Customer Email", (amount-REF) AS "Total Amount" FROM (SELECT left(RIGHT(description::text, length(description::text)-46),length(RIGHT(description::text, length(description::text)-46))-1) AS email, sum(amount/100) AS amount, sum(amount_refunded/100) AS REF FROM stripe_charges WHERE stripe_charges.status != 'failed' GROUP BY left(RIGHT(description::text, length(description::text)-46),length(RIGHT(description::text, length(description::text)-46))-1))tmp ORDER BY "Total Amount" DESC LIMIT 10
Card Type
A supplementary information that Stripe tracks is the card type of each of your customers. When correlated with failed charges, can lead you to useful conclusions regarding which type of cards tend to face invoice failures more often.
Card Type
SELECT source_brand, count(*) FROM stripe_charges GROUP BY source_brand
Country
Monitoring the countries your customers come from can help you target them better in the future and here is a simple query that can help you with this:
Country
SELECT country, count(*) FROM stripe_customer_sources GROUP BY country
Easy to get started tracking those KPIs
Here is what we used to get our Stripe data and chart them:
- A Stripe account (obviously)
- Blendo, with a 1-click Stripe integration and of your payment data to a data warehouse. Blendo is an official Stripe partner.
- A data warehouse easy to integrate with.
![]() |
![]() |
Sync your billing and subscription data from Stripe to any data warehouse. Analytics-ready data with no hassle. | Integrate Now |
It’s important not to be afraid that you need immense resources in order to get all your business data in one place to build your own analytics data stack. Certainly, many businesses find success when they adopt a data-driven approach.
It begins with defining the goals, your KPIs and then an accurate way to track them.
The attraction of using SQL is not customization only, but querying across data sources allowing more advanced and actionable insights.
So if you are serious about your Stripe KPI data, Sign Up for Blendo. We offer 14-days free trial, allowing you to integrate Stripe and sync your data with your data warehouse saving time and money in resources from your IT budget.
Images
- Blog Post image made with Pablo by Buffer
- Source Image: Unsplash