How to build an interactive MRR report using Quickbooks data cover

How to build an interactive MRR report using Quickbooks data

Hassan Syyid profile image

by Hassan Syyid

Feb 23rd 2021
Combining’s embedded analytics platform with a hotglue Quickbooks data integration pipeline

Often, companies want to use data they have to analyze trends. For example, if you want to look at accounting trends, common questions are:

  • how is my monthly recurring revenue (MRR) trending?
  • what does the churn look like this month? what about last month?

Final Dashboard

Whether you are building a SaaS platform that analyzes customer data, or you are analyzing internal company data, and hotglue can help.

In this article, we will use hotglue to pull Invoice data from Quickbooks, process that data using a Python script, and push it to a PostgreSQL database. From there, we’ll use to create the interactive dashboard pictured to the left and embed it directly into a website.

Let’s jump in!

See the live demo

Before we begin, you can see and interact with a live demo of the final product here, and view the source directly on GitHub.

Get the data from Quickbooks

In hotglue, each “type” of data you need you want to import is called a flow. For this article, we want to import Invoices from an ERP like Quickbooks, so we’ll create an Invoices flow. Each flow has a set of sources and targets, both of which are fully configurable. Our Invoices flow will have a Quickbooks source, and a PostgreSQL target.​

For brevity, I will omit the process of creating a hotglue flow — feel free to watch a tutorial or read about this process in detail on the hotglue docs.

Configure the Quickbooks

Let’s start by configuring the Quickbooks source. To do this, you will need to create an OAuth app on Quickbooks and save your client id and client secret and provide it to hotglue.​

Quickbooks Config

Once you provide these credentials and login with your Quickbooks account, we can select what data we want. Since we only care about invoices to monitor MRR that’s the only thing we select.

QB Fieldmap

Configure the PostgreSQL target

Since has native support for PostgreSQL, we’ll select that as our target.

hotglue PostgreSQL configurationThis process is quite straightforward, just provide your database access credentials to hotglue — don’t worry about table names here.​


If you’d like to follow along with this article but don’t have a PostgreSQL database, you can provision one for free from ElephantSQL.

Process the data

Great! Now that the flow is configured we can process the invoice data from Quickbooks to produce the MRR and churn metrics. hotglue provides a JupyterLab workspace directly in the admin panel, which is where I’ll be writing this script. The script will automatically run whenever new data is synced from Quickbooks, so our dashboard will always be up to date!

Start Jupyter

Launch Jupyter directly from hotglue by selecting the Python option under the Quickbooks source we configured earlier.


Write the script

If you’re not very technical, you can get the script I’ll use in this article from GitHub below. The script produces two CSV files — a churn.csv and mrr.csv.​

Note: For this example, I am using the table names “churn” and “mrr” — if you wish to use different ones, merely change the name of the output files.

Once you’re done, you can deploy the script to hotglue directly via JupyterLab using the hotglue option in the toolbar.


Create the dashboard

Create the tables in PostgreSQL

Before we start creating the dashboard, let’s configure our PostgreSQL database a bit more by creating the tables we want to use.​I’ll create an mrr and churn table like so:



MRR_Curr double precision,

MRR_Pri double precision





New double precision,

Expansion double precision,

Loss double precision,

Contraction double precision



Add the datasets in

In, we’ll configure the PostgreSQL connector by providing our database credentials (just like we did with hotglue). PostgreSQL configurationIf everything goes well, our two new tables should appear in the options for datasets. Simply connect both churn and mrr, and we are good to go!


From here, we’ll create a new dashboard in and add two items: a line chart to analyze MRR trends, and a stacked box plot to analyze Churn trends. ​

From there we can connect our mrr and churn datasets, respectively. The x-axis for both charts should be month and the y-axis should be the other columns.


Embed hotglue and

Now to the fun part! Let’s embed hotglue and into the same web app to get a great experience.

In, select the share icon at the top of your dashboard and follow their embed instructions. You’ll need to follow their developer documentation to generate an authorization token that grants access to your datasets and dashboard. To get more details, read the docs.

​Embedding hotglue follows a similar process — access the embed instructions directly from settings. To get more details, read the hotglue docs.

Once you have both embedded, let’s populate our dashboard. Simply open the hotglue widget, connect the Quickbooks source by logging, and press run job.

hotglue run job

This will tell hotglue to sync any invoice data from Quickbooks, run our script, and output the data to PostgreSQL.​

Once the job completes, we can see our dashboard update!


That’s all there is to it! Whenever you get new invoices you can just run another hotglue job and the data will update automatically in your dashboard — you can even set a sync schedule so it stays up to date automatically.


By combining’s powerful embeddable analytics with hotglue’s data integration platform, we are able to connect, process, and analyze data from virtually any data source!

Want to see the whole thing in action? Check out the demo video and the live site!

Thanks for reading! Feel free to leave comments and questions below.