Sync Inc

Real-time subscription revenue analytics in Metabase

Sync Inc 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.

Final dashboard

Why Sync Inc

Sync Inc 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.

Date flow

Third, you get the credentials to your Sync Inc 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.

Why Metabase

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 Sync Inc 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.

Stripe Setup

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.

Activate Stripe Test Mode

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:

Create recurring product

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:

Create recurring product

Repeat the process by creating a couple more customers with recurring subscriptions.

Generate a restricted Stripe API key

Sync Inc requires an API key to sync all your Stripe data in real-time. As a best practice, we recommend providing Sync Inc 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.

Create restricted API key

Step 2: Give your key a name (something like "sync inc" 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 Sync Inc'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 Sync Inc.

Sync Inc Setup

With your Stripe API key created, you can now setup Sync Inc to replicate Stripe to a Postgres database:

Step 1: Create or Login to your Sync Inc account.

Step 2: Connect your Stripe account to Sync Inc 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 Sync Inc database generated. Finally, click Create.

Step 4: Sync Inc 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 Sync Inc will only sync your TEST data for free, forever). You'll be provided with credentials for you new database:

Sync Ind DB Created

Metabase Setup

With an Open Source license, you can choose to install Metabase for free if you wish. Or, you can pay to use the hosted version of Metabase - known as Metabase cloud.

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:

Create metabase account

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 Sync Inc to Metabase

You'll add your Sync Inc 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:

Go to the Metabase admin

Step 2: On the Metabase Admin page, select the Databases tab and click the blue Add database button:

Click to add a database

'

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 Sync Inc database (in case you closed the tab, you can find all this information by clicking the Connect button next to the resource in the Sync Inc console). Lastly, toggle on SSL and click the blue Save button at the bottom of the page:

Configure your database

Step 4: Metabase will confirm it can connect to your Sync Inc Postgres database and present you with a success modal. Click the I'm good thanks link to close the modal:

Confirm 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:

Exit the 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 Sync Inc.

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:

Create questions

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 (
select
date_trunc('hour', charge.created) as hour,
sum(charge.amount_captured/100.00)::money as volume
from charge
where charge.created::date = current_date
group by 1
)
select
hour,
sum(volume) over (order by hour asc rows between unbounded preceding and current row)
from data
;

Stepping through this SQL statement:

  • You are using `with` to 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 `SELECT` statement to retrieve the data you need. In this case, you are using `date_trunc` to 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)::money` to 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:

Gross volume 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:

Chart settings

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:

Add chart to dashboard

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.

New customers

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:

select
date_trunc('hour', customer.created) as hour,
count(customer.id) as new_customer
from 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:

New customer

Then save it and add it to your Real-time Revenue dashboard.

Products

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:

select
product.name,
sum(line_item.quantity) as quantity,
sum(line_item.amount/100.00)::money as sales
from charge
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:

Products sold

Then, just as before, save this metric and add it to your dashboard.

Balance

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:

select
sum(balance_transaction.amount/100.00)::money
from balance_transaction
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:

Balance

Finally, save and add this metric to your dashboard.

Setup refresh

You've now created a dashboard that shows you how your revenue is changing throughout the day:

Final dashboard

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 Sync Inc database will update data in real-time - so you can set your refresh to be as fast as Metabase will allow β€” 1 Minute.

Refresh

Next Steps

You've just built a real-time revenue dashboard on your Stripe data using Sync Inc 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 Sync Inc 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.

Retool
Retool

Was this helpful?