Top 20 Stripe KPIs to track your Sales and Payments (including SQL code)

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.

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

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

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.

• An SQL editor or a BI tool like Chartio for the graph part.

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