Combining Cumul.io’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?
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 Cumul.io to create the interactive dashboard pictured to the left and embed it directly into a website.
Let’s jump in!
See the live demo
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.
Configure the Quickbooks
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.
Configure the PostgreSQL target
Since Cumul.io 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 Cumul.io dashboard will always be up to date!
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 Cumul.io dashboard
Create the tables in PostgreSQL
Before we start creating the Cumul.io 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:
CREATE TABLE mrr(
Month DATE PRIMARY KEY,
MRR_Curr double precision,
MRR_Pri double precision
CREATE TABLE churn(
Month DATE PRIMARY KEY,
New double precision,
Expansion double precision,
Loss double precision,
Contraction double precision
Add the datasets in Cumul.io
Cumul.io 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 Cumul.io 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 Cumul.io
In Cumul.io, 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 Cumul.io docs.
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 Cumul.io 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 Cumul.io dashboard — you can even set a sync schedule so it stays up to date automatically.
Thanks for reading! Feel free to leave comments and questions below.