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.
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.
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.
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.
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:
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.
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.
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.
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:
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.
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:
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 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:
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:
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:
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:
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 (selectdate_trunc('hour', charge.created) as hour,sum(charge.amount_captured/100.00)::money as volumefrom chargewhere charge.created::date = current_dategroup by 1)selecthour,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:
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:
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:
selectdate_trunc('hour', customer.created) as hour,count(customer.id) as new_customerfrom customerwhere customer.created::date = current_dategroup 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:
selectproduct.name,sum(line_item.quantity) as quantity,sum(line_item.amount/100.00)::money as salesfrom chargeleft join invoiceon charge.invoice_id = invoice.idleft join invoice_line_item_mapon invoice.id = invoice_line_item_map.invoice_idleft join line_itemon invoice_line_item_map.line_item_id = line_item.idleft join priceon line_item.price_id = price.idleft join producton price.product_id = product.idwhere charge.created::date = current_dategroup 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:
selectsum(balance_transaction.amount/100.00)::moneyfrom balance_transactionwhere 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 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.
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.