How revamping our reporting with dbt and Zuora saved us 10 hours a week

Up until a few months ago, we were working with a balky financial reporting system here at Stitch. We were comfortable with the numbers we were getting from it, but its complexity made it hard to audit, and our operations team – namely me – had to spend several hours a week making manual adjustments. It wasn't great, but it worked.

We overhauled our financial reporting infrastructure, and it's now more useful, easier to understand, and requires effectively zero ongoing maintenance. I love spending my time improving the system rather than keeping it running. I wanted to share the story of how we did it and share our code, so that anyone else struggling with this can improve their reporting.

From Zuora to dashboard

We use Zuora, a SaaS platform for subscription billing, and we use Stitch to ETL our Zuora data into our data warehouse for financial reporting. Historically, our financial reporting relied mostly on data from four objects: accounts, subscriptions, rate plans, and rate plan charges. In Zuora, each account (customer) has one or more subscriptions, each subscription is on a rate plan, and each rate plan imposes different charges depending on how you set them up. We used the accounts and charges objects to determine monthly recurring revenue (MRR).

To prepare our data, we use dbt, a command-line tool that uses SQL statements to run in-database transformations and create data models, which are dbt's name for transformed views of raw data. We then use those models as building blocks to further join related data, until we have a view of the database that our reporting tool uses to display MRR.

Our reporting system had some flaws, however. It was cumbersome in handling cases we hadn't planned for – when clients left Stitch then later reactivated their accounts, when a customer upgraded and then downgraded in the same billing period, and when we created custom contracts for Enterprise clients. In those cases and others, our models produced misleading numbers, so we had to put in manual work to show what was really taking place. As we grew, our need to do manual checking and fixing grew too. On top of that, we started using Zuora to track clients on our free plans and free trials, which added more data our models weren't designed for.

We needed a solid source of truth that told us what pricing plan all of our clients were on, how they were using Stitch, and how they were paying us. We needed simplicity at each step to build a strong foundation for our financial reporting.

To get that, we turned to one of our implementation partners, Fishtown Analytics, which not coincidentally is the developer of dbt. Fishtown looked at what we were doing and proposed a new direction: Instead of looking at rate plan charges, they showed us that relying on invoice data would give us a better view of our business. Once we did that, our MRR would correlate with actual payments, which would give us more accurate information than we'd been getting.

A new perspective

We now use data from only two objects: Invoice and Invoice Items. This might sound like a small change, but the implications have been huge. Now that we're using actual invoice data, the weekly manual intervention I'd been doing went from six to 10 hours a week down to zero.

The Invoice and Invoice Items objects give us access to the data about what our customers are actually charged for using Stitch. Rate plan charges are one step removed; just because the customer has a rate plan charge for a certain dollar amount doesn't mean that we actually charged them, or that they even stayed on that plan long enough to get an invoice.

Fishtown built us a new dbt layer that includes information about the status of each client in every month, such as subscription revenue, overage revenue, and pricing plan. They also built us two dashboards for monitoring our financial performance: one focused on historical performance and one that helps us monitor our progress in the current month and quarter. These were incredibly helpful, and I've since extended them to include an automatic ongoing comparison of our budget to our actual performance.

I keep a copy of our budget in a Google Sheet (template here). I send that data to the Stitch Import API using this script, which loads it into our data warehouse. I created a dbt view that creates a clean view of the data by doing things like casting datatypes appropriately and renaming columns, then used dbt to make models that combined data from both sources for reporting.

I also added pacing information to the report, to show where we are versus where we should be for the current day of the month according to our budget, and display a red flag when those numbers fall short of expectations. The report also shows where we were on the same day in the previous month. Here's a look at what we see now, with some sample data.

If you're interested in how we refined our raw Zuora data with dbt, we've made the base models for the Stitch-provisioned Zuora data available in GitHub. If you have any questions or comments about what we've done, leave me a comment.

Image credit: charity:water