I’ve written about how we use Metabase at Reforge, and how I’m a big fan. It has allowed us to make data accessible to anyone in the organization, whether it’s for a deep analysis or for a quick status update on an important initiative.
We use Segment as one of the key pieces of data our data infrastructure, and I recently turned on the Stripe integration. I was pleasantly surprised by how well it works. I authenticated into our Stripe account to configure it as a source within Segment. Segment then updates tables in our data warehouse with the latest and greatest data from Stripe. This is a screenshot of our database and the tables under the Stripe schema:
On one of our key dashboards showing our progress in generating revenue, I wanted a cumulative revenue chart. This is helpful to see how quickly we’re generating revenue, what our total revenue in a period of time is, and how our revenue growth compares to previous periods.
This is a sample chart that’s easy to setup in Metabase:
To generate it, this is the SQL I wrote to generate it:
The way it works:
- The first statement generates a table (that’s the generate_series function) that has a start date and end date
- I join from that table with a left join to the stripe payments table. This ensures that if we don’t have a day of revenue, the day still shows up in our table. This table gives me a total amount of revenue per day.
- In the final part of the statement, I use a window function to do a cumulative sum of all of the revenue per day, so that day 2 has all of day 1’s revenue along with day 2’s revenue added in.
I hope this helps you create similar charts of revenue per day or cumulative revenue reports. I’ve worked with teams where we’ve manually hit the Stripe API to pull all of this information, which is always a pain in the butt. The nice part of this solution is that Segment keeps the table up to date automatically, and then anyone can run these reports on the latest values in the data warehouse.
While we’re still an early stage company, the type of integration is super powerful. There are a lot of interesting applications of being able to access failed charges, successful charges, refunds, and the various types of paying customers when joined to other data sources.