How To Build an Interactive MRR Report Using Quickbooks Data
A tutorial on how to combine Cumul.io’s embedded analytics platform with a hotglue Quickbooks data integration pipeline for an interactive MRR Report.
Join the DZone community and get the full member experience.Join For Free
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?
Let’s jump in!
See the Live Demo
Get the Data From Quickbooks
In hotglue, each “type” of data 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 source
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.
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. 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
churn table like so:
Add the Datasets in Cumul.io
In Cumul.io, we’ll configure the PostgreSQL connector by providing our database credentials (just like we did with hotglue).
If 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
churn datasets, respectively. The x-axis for both charts should be
month and the y-axis should be the other columns.
You can easily customize the colors and other aspects of your dashboard so it suits your needs.
Embed hotglue and Cumul.io
Now to the fun part! Let’s embed hotglue and Cumul.io into the same web app to get a great experience.
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.
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 Cumul.io dashboard. Simply open the hotglue widget, connect the Quickbooks source by logging, and press run job.
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.
By combining Cumul.io’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 below!
Thanks for reading! Feel free to leave comments and questions below.
Published at DZone with permission of Hassan Syyid. See the original article here.
Opinions expressed by DZone contributors are their own.