Real-time subscription revenue analytics in Metabase
Sequin replicates all your Stripe data to a Postgres database in real-time. For business operations, finance, data analysts, and product managers this means you can analyze and work with your Stripe data in completely new ways using the tools and SQL you already know.
To get started, this tutorial shows you how to build a real-time revenue dashboard using SQL with Metabase.
Sequin enables you to work with up-to-the-second Stripe data in a Postgres database. There are a couple benefits.
First, you can use SQL to work with your data. You can query any table or column as you please. You aren't locked in to any prebuilt metrics or visualizations that don't work for your business.
Second, all your data is synced in real-time (unlike the 24 hour delay in Stripe Sigma or other ETLs) so you can analyze your financial data as it changes every second.
Third, you get the credentials to your Sequin postgres database, so you can connect your Stripe data to virtually any BI tool you can think of. You can use the tools your team is already familiar with.
And finally, you have the option to sync your Stripe data to a schema in an existing Postgres database, so you can query across your core data sets and Stripe with ease. This means you can match customer behavior with revenue to create metrics tailored to your business.
We'll use Metabase in this tutorial. Metabase is a powerful, flexible, BI platform with first class support for Postgres.
As mentioned earlier, you can use virtually any BI platform under the sun with your Sequin database. From PowerBI, to Tableau, and Looker. But what makes Metabase great for this tutorial is that it is open source and offers a free plan.
You'll need a Stripe account that contains some active subscriptions data. If you don't have any active subscriptions in your LIVE Stripe account, you can easily add some fake ones in your TEST account:
Create test subscriptions
To get going, add a couple test subscriptions to your Stripe account:
Step 1: Login to your Stripe dashboard and put your account into TEST MODE by flipping the View test data switch.
Step 2: Create a recurring product by going to the product page, clicking + Add Product, and filling out the form to create a new product. Double check that the product is configured to be Recurring:
Step 3: Finally, create a new customer with a subscription to the product you just created. To do so, click the Actions button on the customer page and select Create Subscription:
Repeat the process by creating a couple more customers with recurring subscriptions.
Generate a restricted Stripe API key
Sequin requires an API key to sync all your Stripe data in real-time. As a best practice, we recommend providing Sequin with a restricted API key. To generate a restricted API key:
Step 1: Navigate to the Stripe API keys page by selecting Developers in the left navigation and clicking API keys. Then click the + Create restricted key button.
Note: If you want to use a test API key, ensure you have the View test data switch turned on while generating the API key.
Step 2: Give your key a name (something like "sequin" will do just fine). Then provide this new key with the following permissions:
- READ access to everything
- READ & WRITE access to Webhooks
- No access to the CLI
You can get more details by reading Sequin's reference for Stripe.
Step 3: Finally, click the Create Key button and keep this new restricted API key handy as you move on to set up Sequin.
With your Stripe API key created, you can now setup Sequin to replicate Stripe to a Postgres database:
Step 1: Create or Login to your Sequin account.
Step 2: Connect your Stripe account to Sequin by going through the tutorial or clicking the Add database button and selecting Stripe.
Step 3: You'll be prompted to enter your Stripe API key. Then, in the destination section, select to have a New Sequin database generated. Finally, click Create.
Step 4: Sequin will immediately provision you a Postgres database and begin syncing all your Stripe data to it (if you're using a TEST API key, then Sequin will only sync your TEST data for free, forever). You'll be provided with credentials for you new database:
To get you started, we'll us Metabase cloud (which comes with a nice 14 day trial) in this tutorial.
Simply go to https://www.metabase.com/ and create a Metabase Cloud account by clicking Get Metabase and selecting to start a free trial:
You'll go through several steps to create an account, select your cloud url, and add payment information (you can cancel at any time in the trial).
Metabase will then spin up your cloud instance and email you in a couple minutes when everything is ready.
Then, just login to your new Metabase account.
Connect Sequin to Metabase
You'll add your Sequin Postgres database to Metabase just as you would any other Postgres database:
Step 1: Click the gear icon in the top right corner and select Admin:
Step 2: On the Metabase Admin page, select the Databases tab and click the blue Add database button:
Step 3: On the add database page, select PostgreSQL as the database type and give your new database a name - in this case, something like "Stripe." Then, enter the Host, Port, Database Name, Username, and Password for your Sequin database (in case you closed the tab, you can find all this information by clicking the Connect button next to the resource in the Sequin console). Lastly, toggle on SSL and click the blue Save button at the bottom of the page:
Step 4: Metabase will confirm it can connect to your Sequin Postgres database and present you with a success modal. Click the I'm good thanks link to close the modal:
Step 5: You'll see that your new database has been added! You can now exit the admin page by clicking the gear icon in the top right corner and selecting Exit admin:
Ask Your First Question
Metabase calls metrics and visualizations you derive from your data questions. Let's start with a simple metric that asks the question "what is the total amount of money I've made today?" This metric is often called Gross volume and is a great example of a real-time metric made easy to calculate using Sequin.
To create your metric, click Ask question in the navigation bar and then select Native query. On the new question page, select the Stripe database you just added:
You can now write the SQL expression that will calculate the gross volume. To make this metric more visual, let's show a hourly running total:
with data as (
date_trunc('hour', charge.created) as hour,
sum(charge.amount_captured/100.00)::money as volume
where charge.created::date = current_date
group by 1
sum(volume) over (order by hour asc rows between unbounded preceding and current row)
Stepping through this SQL statement:
- You are using
withto create a common table expression. This is a temporary view used just for this SQL query. This helps you generate the raw data you need before creating a running total.
- You are then using a
SELECTstatement to retrieve the data you need. In this case, you are using
date_truncto extract and group the the value of each charge by every hour of the day.
- Stripe stores money values in the smallest unit possible as integers (since we are using USD - this means the values are stored in cents). You use
sum(charge.amount_captured/100.00)::moneyto convert the value to dollars and then format it as currency.
- In the last select statement, you generate the running total.
Run the query by clicking the blue play button. You'll see a table generated with the results of your query:
Turn the table into a graph by clicking the Visualization button and selecting the Line chart.
Metabase gives you different tools to adjust how the graph appears.For this tutorial, we'll keep it simple and just adjust the data values to ensure they appear as currency.
To do so so, go to the Data tab under settings and click the gear next to the y-axis. This will bring up the settings for how this series of data is displayed. Since you are showing dollar values, adjust the style to be Currency:
Great, you can now see how your gross volume of sales grows (hopefully!) throughout the day. Now, share it with your team by saving it to a new dashboard.
To do so, click the Save link and name your question something like
Gross Volume. You'll be asked if you want to add this question to a dashboard. Click Yes please! and then select to Create a new dashboard. Name your dashboard
Real-time Revenue and then click Create. Your Gross Volume chart will be instantly added to a new dashboard. Position the graph to make it look good and be sure to click save:
Build Your Dashboard
You've created your first metric on your dashboard. You know how to create a new question, query your Stripe data in SQL, visualize the results and share your graph to your dashboard.
Now, let's repeat this construction pattern to add a couple more metrics to our dashboard.
Since the purpose of the dashboard is to help your team see real-time revenue data, lets add metrics around new customers, the products being purchased, and your net Stripe balance.
This question will help you see how many new customers you've gained through the day.
Following the same flow you just went through, create a new question using a native query.
Enter the following SQL statement for your query:
date_trunc('hour', customer.created) as hour,
count(customer.id) as new_customer
where customer.created::date = current_date
group by 1;
Instead of a rolling sum as you did with gross volume, here you'll just see how many new customers are created hour by hour.
You can visualize this data as a bar chart:
Then save it and add it to your Real-time Revenue dashboard.
Now, let's see which products these new customers are buying to generate this revenue.
Create another native query question and enter the following SQL statement:
sum(line_item.quantity) as quantity,
sum(line_item.amount/100.00)::money as sales
left join invoice
on charge.invoice_id = invoice.id
left join invoice_line_item_map
on invoice.id = invoice_line_item_map.invoice_id
left join line_item
on invoice_line_item_map.line_item_id = line_item.id
left join price
on line_item.price_id = price.id
left join product
on price.product_id = product.id
where charge.created::date = current_date
group by product.name;
This SQL query joins together several tables to in order to show which products are associated to today's sales. It returns both the quantity and the total sale value for each product.
You can visualize this query as another bar chart:
Then, just as before, save this metric and add it to your dashboard.
Finally, to round out this dashboard, let's display the total Stripe balance that is awaiting payout. This represents the net revenue your company has earned so far for the day.
Just as before, create another native query question and enter the following SQL query:
where balance_transaction.created::date = current_date;
This query will return the expected value for your next stripe payout. Because the
balance_transaction table acts as a ledger that includes any new balance awaiting payout in addition to fees, and simple sum is all you need.
You can display this metric as a simple value - just format it to appear as a currency:
Finally, save and add this metric to your dashboard.
You've now created a dashboard that shows you how your revenue is changing throughout the day:
As a last step, set your dashboard to automatically refresh so you pull in the latest data every several minutes.
To do so, open your Real-time Revenue dashboard, click the clock icon in the top left and select a refresh interval. Your Sequin database will update data in real-time - so you can set your refresh to be as fast as Metabase will allow — 1 Minute.
You've just built a real-time revenue dashboard on your Stripe data using Sequin and SQL. This is just the beginning.
From here, you can continue to build more metrics that are fitted to your business and the way it operates. You can configure Sequin to sync your Stripe data right into a schema in your production database so you can build metrics that combine your core business data with Stripe. Then, you can make your dashboard more interactive with filters and drill-throughs. You can also automate notifications around your dashboard over slack and email.
We'd love to help you craft the queries and metrics that help your business run on Stripe. So please send us a note if you have any questions.