SQL InsightsStripe App is Live

By Chris Castle

Render is a unified, full-stack development platform where you define what’s possible. Whether you’re a solo developer, growing startup, or established enterprise, Render provides just what you need to bring your ideas to life.

Stripe announced their App Marketplace at the end of May, and today it’s open to all Stripe merchants. We’re excited to share that our SQL Insights app for Stripe is in public beta.

While Stripe was planning the launch of their App Marketplace, they asked if we were interested in contributing an app focused on the needs of developers. We decided to take on the challenge, but we also wanted to expand the audience to include other data-savvy users.

Using SQL Insights you can query data about your business in real-time using standard SQL. Your Stripe data is automatically synchronized to an encrypted PostgreSQL database so that Engineering, Finance, Product Management, Marketing, and Business Operations teams can query it using a wide range of tools – from the psql command line client, to visual data exploration tools like Metabase, Mode Analytics, and Tableau, to data pipeline management tools like Apache Airflow and Dagster.

Let’s look at how how to set up Render’s SQL Insights app, and then how to answer some common business questions using the data:

  1. What are my most recent transactions?
  2. What are the details of last month’s transactions?
  3. What are the details of quarter-to-date transactions?
  4. What were the most recent payouts to my bank account?
  5. How does cash flow change month-to-month?
  6. How much spend each month is from new customers vs. existing customers?
  7. How many charge attempts per customer are required until we get a successful payment?

Setting up SQL Insights

First, you’ll need to set up the SQL Insights app in the Stripe App marketplace. Here are some instructions to help you out.

During the setup process, you will be asked to select an existing Render PostgreSQL database or create a new one. Then Render will copy all your Stripe data into that database and keep it up to date. The app takes care of copying your Stripe data to the appropriate SQL tables and managing the entire synchronization process, reducing any DevOps burden on you or your team.

Exploring the Data

Once the data synchronization has finished, you can immediately start running SQL queries against it. Grab the External Connection String for the PostgreSQL database from the Render Dashboard and provide it to your preferred SQL query tool. psql is a common PostgreSQL command-line client, but you can use any tool that is compatible with PostgreSQL.

What are my most recent transactions?

Let’s start with a simple query to look at the five most recent transactions.

SELECT
  to_timestamp(created) AS created_at,
  id,
  amount,
  type,
  currency,
  source_id
FROM stripe_balance_transactions
ORDER BY created desc
LIMIT 5;
created_atidamounttypecurrencysource_id
2022-07-09 01:16:27+00txn_1Kl31e0C2xjz0U1l3YPzP043-580payoutusdpo_1KcJAJ8wS98Cm5uGDtsLRgKp
2022-07-07 23:17:39+00txn_3KbzSw6ozmwjnuaVnbLrgVSx330chargeusdch_3KdCdLTFn3hX1KgXnqwZIDoJ
2022-07-07 23:11:34+00txn_3KRqW5sJRzS960lZTMVfr994330chargeusdch_3KAsL17UyXaiWlnVkgr9IPlP
2022-07-05 01:31:41+00txn_1Ki6FxQ739wcs6XrlOMvI1rw-53payoutusdpo_1K93konwYNDMlpKJXGNa1Boa
2022-07-04 14:59:19+00txn_3Kv9GPvBNZggo1efWg4MbydE85chargeusdch_3KeR463CSKb4OdNSmueZI2n0

Notice that we’re using PostgreSQL’s to_timestamp() function to convert the Unix time provided by Stripe to a human-readable (and PostgreSQL’s native) timestamp. This is one of the benefits of having your Stripe transaction data in a SQL database. Not only do you get to use standard SQL to interact with the data but you also get convenience functions like to_timestamp() – and many others.

What are the details of last month’s transactions?

What if instead, we want to view all transactions from last month to understand what contributed to our meeting last month’s revenue goal? PostgreSQL’s interval syntax makes that easy.

SELECT
  to_timestamp(created) AS created_at,
  id,
  amount,
  type,
  currency,
  source_id
FROM stripe_balance_transactions
WHERE to_timestamp(created) > current_date - interval '1 month'
ORDER BY created desc;
created_atidamounttypecurrencysource_id
2022-06-14 01:13:51+00txn_1KMDHeXi6CQnVgieWubDGmmb-67payoutusdpo_1KtQhAYYNPe1WDnV1AliMgz1
2022-06-11 18:39:39+00txn_3KzcmI2eYfEh84Txy0UqpNjr100chargeusdch_3KgSzJbbYfH8PzlHljj6b2qS
2022-06-01 01:09:32+00txn_1K320SbTb6gZDcmugoxuJjNu-1186payoutusdpo_1KIs46MfFen5lPasGxHgHSJl

What are the details of quarter-to-date transactions?

Similarly, PostgreSQL also makes it easy to view all transactions quarter-to-date to understand if we’re on track toward our quarterly revenue goal.

SELECT
  to_timestamp(created) AS created_at,
  id,
  amount,
  type,
  currency,
  source_id
FROM stripe_balance_transactions
WHERE extract(quarter from to_timestamp(created)) = extract(quarter from current_date)
  AND extract(year from to_timestamp(created)) = extract(year from current_date)
ORDER BY created desc;
created_atidamounttypecurrencysource_id
2022-07-01 23:11:34+00txn_3KIBfcP4saWP28viL8lg6ZgJ330chargeusdch_3K5XP25f7qS2WZHtgA1SzWFd
2022-07-05 01:31:41+00txn_1KDPOj6hjvv6Ro5IxiHIuMHM-53payoutusdpo_1KuqJTpPZoq1J0OsUI3wryI3
2022-07-04 14:59:19+00txn_3K3NMZe7uATx9d5hGljjPcRS85chargeusdch_3KvVv64Zl56yt3vkqXVBWZIP

What were the most recent payouts to my bank account?

We might also want to see how frequently funds are being paid out to our bank account in the current quarter. We can refine the previous query a bit to show us only the transactions that were payouts.

SELECT
  to_timestamp(created) AS created_at,
  id,
  amount,
  type,
  currency,
  source_id
FROM stripe_balance_transactions
WHERE type = 'payout'
  AND extract(quarter from to_timestamp(created)) = extract(quarter from current_date)
  AND extract(year from to_timestamp(created)) = extract(year from current_date)
ORDER BY created desc;
created_atidamounttypecurrencysource_id
2022-07-03 01:16:27+00txn_1KyiPpfbSAaO6FRlkPXiM6M7-580payoutusdpo_1K69xzo6HkiusAKUEPuZkc8y
2022-07-05 01:31:41+00txn_1KLp2JYq0CwgeqmE8flJCwVC-53payoutusdpo_1K2JUlang77ETJVXdyt2TyiX

But what happens as our queries become more complex? It’s useful to be able to save and name them so you or others at your company can run them regularly. Additionally, charts can help identify trends and uncover insights not readily apparent in a large table of data.

Visualize Stripe Data

Metabase is an open source, web-based SQL querying and charting tool that takes just a few clicks to deploy to Render. We can use it to easily save, name, and visualize even the most complex SQL queries. But if writing and debugging complex SQL worries you, Metabase can help there too. Its point-and-click query and chart builder makes data accessible to those who aren’t SQL pros.

Here’s what Metabase looks like right after deploying it and connecting it to our Stripe data. It helps you start exploring even if you don’t know the exact question you want to answer.

A screenshot of the initial screen in Metabase after connecting a database containing Stripe data.
The initial screen shown by Metabase after connecting your Stripe database

Follow the instructions in Render’s Quickstart guide for Metabase to deploy it.

Now let’s start exploring the data visually!

How does cash flow change month-to-month?

Here’s a query we can save in Metabase and then visualize with a line chart, helping us better understand current cash flow relative to past months.

WITH charges_timezone_conversion AS (
  SELECT
    to_timestamp(created) AT time zone 'America/Los_Angeles' AS timestamp_pacific,
    currency,
    amount
  FROM stripe_charges
  WHERE captured -- filter out uncaptured charges
)

SELECT
  to_char(timestamp_pacific, 'YYYY-MM') as month,
  currency,
  round(sum(amount)/100.0, 2) AS gross_charges
FROM charges_timezone_conversion
WHERE timestamp_pacific >= current_timestamp AT time zone 'America/Los_Angeles' - interval '24 months'
GROUP BY 1, 2
ORDER BY 1 DESC, 2;
A screenshot showing a Metabase line chart of cash flow month-to-month.
Cash flow month-to-month in Metabase

How much spend each month is from new customers vs. existing customers?

For subscription-based businesses, understanding if growth is coming from new or existing customers can be useful to optimize growth investments. The following query categorizes a customer as new if they’ve only ever received one invoice from you. It then shows revenue breakout from those new customers compared to existing customers.

-- if a customer has only one invoice, categorize them as a new customer
WITH customer_status AS (
  SELECT
    case when count(id) > 1 then false else true end AS new_customer,
    customer_id
  FROM stripe_invoices
  GROUP BY 2
)

SELECT
  concat(stripe_invoices.metadata->>'PeriodYear','-',lpad(stripe_invoices.metadata->>'PeriodMonth',2,'0')) AS invoice_month,
  customer_status.new_customer,
  round(sum(stripe_invoices.subtotal)/100.0, 2) AS invoiced_subtotal
FROM stripe_invoices
JOIN customer_status
  ON customer_status.customer_id = stripe_invoices.customer_id
GROUP BY 1, 2;
A screenshot showing a Metabase stacked area chart of spend by existing vs. new customers.
Comparing spend of existing vs. new customers in Metabase

How many charge attempts per customer are required until we get a successful payment?

Having to attempt a charge on a customer’s credit card multiple times delays your business receiving those funds. Knowing the portion of invoices that required more than one charge attempt each month can help you understand the efficiency of your billing process. Here’s a query and a chart to look at that.

SELECT
  concat(stripe_invoices.metadata->>'PeriodYear','-',lpad(stripe_invoices.metadata->>'PeriodMonth',2,'0')) AS invoice_month,
  count(1) AS total_invoices,
  sum(case when attempt_count > 1 then 1 else 0 end) AS multiple_attempt_invoices,
  100.0*sum(case when attempt_count > 1 then 1 else 0 end)/count(1) AS percent_multiple_attempts
FROM stripe_invoices
GROUP BY 1;
A screenshot showing a Metabase line chart showing percent of transactions requiring more than one charge attempt.
Percent of invoices requiring more than one charge attempt in Metabase

SQL Insights + Stripe

Render SQL Insights + Stripe helps you speed through the tedious integration work often required to set up a business intelligence solution, reducing the time required before you can get meaningful insights about your business.

And Render can grow with you. Render can support not only one-off queries like the ones above but also more complex use cases as your business grows. We have a wide selection of Quickstart guides for business tools that can help you interact with your data. And you can easily upgrade your database’s storage, memory, or processing power as your data needs grow in volume and complexity.

Install Render SQL Insights here.

Interested in working with Chris at Render? Check out our open roles!

Chris Castle

Chris Castle formerly headed up Developer Relations for Render.

Subscribe to our newsletter for monthly product updates.

Discover More

  1. How BeerMenus Elevated its Craft with a Seamless Move to Render

    Issues with Heroku mounted, so BeerMenus founders migrated to Render. They reduced costs, gained better Support, and adopted game-changing features like Preview Environments....

    - Rosalind Benoit

  2. OpenSSL Patch

    Render services are not affected by the CVEs announced for OpenSSL 3.0 earlier today. We recommend that organizations patch OpenSSL 3.0.0 and above....

    - Ed Ropple

  3. Render Newsletter Vol. 4 - October 2022

    This periodical for the Render community keeps you up to date on new guides and content, product updates, and developer news....

    - Rosalind Benoit

  4. Shipping Monorepo Support

    Monorepo Support is now generally available for all Render customers. Your feedback has shaped what we've shipped and allowed us to design flexible features to help you define how...

    - Shantanu Joshi