Render takes your infrastructure problems away by providing a powerful, battle-tested, cost-effective cloud with an outstanding developer experience. Stay focused on building great apps, shipping them fast, and delighting your customers, and leave your cloud infrastructure to us.
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:
- What are my most recent transactions?
- What are the details of last month’s transactions?
- What are the details of quarter-to-date transactions?
- What were the most recent payouts to my bank account?
- How does cash flow change month-to-month?
- How much spend each month is from new customers vs. existing customers?
- How many charge attempts per customer are required until we get a successful payment?
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.
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.
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;
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 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;
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;
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;
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.
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.
Follow the instructions in Render’s Quickstart guide for Metabase to deploy it.
Now let’s start exploring the data visually!
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;
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;
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;
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.