Tableau 10 and Quickbooks Online
Tableau 10 has some great new features. Cross Database joins and Cluster Analysis has probably got the most press. But there are also some new data connectors. Over time I suspect the new Quickbooks Online connector is going to introduce a lot more small business owners to Tableau.
Figure 1 shows the desktop version of Quickbooks. I’ve loaded the ‘Larrys Landscaping & Garden Supply’ sample company file so that we can see what we can do with it in Tableau.
Figure 1: Desktop Quickbooks
Tableau only has a Quickbooks connector for Quickbooks Online so we’re going to need to get our company data into the Quickbooks cloud before we can play with it from within Tableau.
Choose File->Export->To Quickbooks Online to export the company file, see Figure 2.
Figure 2: Export to Quickbooks Online
Quickbooks Online will ask you to sign in to your Quickbooks online account before it will upload the data. Once the data has been uploaded the company information can be seen in Figure 3.
Figure 3: Quickbooks Online interface
For many people the Quickbooks Online view into your company data is more than enough. But there are limitations. Perhaps you want to include some information from your employee timesheet databases or your customer databases or CRM. You’re probably not going to be able to do that without a lot of effort. Or maybe you simply want to slice the data in a way that’s not possible in the standard or custom Quickbooks Online reports. Whatever your reasons in Tableau 10 it’s now very simple to visualize the data in Tableau.
Open Tableau and under Connect & To a Server click on the Quickbooks Online link, see Figure 4.
Figure 4: Connect to Quickbooks Online
You will be prompted to login to Quickbooks online, then choose Larry’s Landscaping and Gardening Supply company, see Figure 5.
Figure 5: Choose Sample Company
Finally authorize Tableau to connect to your Quickbooks Online data, see Figure 6.
Figure 6: Authorize Tableau app to access data
Once connected, we can see 4 tables, Accounts, Payments, Sales and Expenses and finally Sales and Expenses with line items, see Figure 7. Let’s look at the data in the tables.
Figure 7: Quickbooks data in Tableau
If you want to find out who your best Customers, we can set up a treemap similar to Figure 8. Chose Payments data table and in a worksheet drag the ‘Total Amount’ dimension to Rows and ’Customer Name’ measure to columns which will give you a bar chart of Larry’s best customers. Next click on Show Me and choose the Treemap chart type.
Figure 8: Best Customer Treemap
You might also want to filter by date. Drag the ’Transaction Date’ to the filter pane and choose the year 2018. Not sure why but for some reason the Quickbooks sample file is in the future with most of the transactions in 2018.
Right behind Sales of course is what the company is spending money on. We can see this really easily looking at the data in Tableau.
Personally I’m usually just as interested in what I’m spending money on as what we’re selling so let’s see where we can find that information.
Choose the ‘Sales and Expenses’ table in the Data tab, then open a worksheet. Drag the ‘Total Amount’ dimension over to the Rows and then drag the ‘Associated Entity Name’ measure to pick up your vendor names. You should also filter on Transaction Type of ‘Purchase’ so you’re pulling in what Larry is buying, see Figure 9.
Figure 9: Larry’s Expenses
Note that the schema names are a bit odd in Quickbooks, but don’t let that put you off. If you get lost refer back to the Desktop version and make sure they match.
One of the great things about Tableau is we can drill down into the data to see exactly what’s going on. If you drill down into Great Statewide Bank data you can see that the transaction are going from Savings to Checking and then back again so you may want to add that filter too.
The new Quickbooks connector brings the power of Tableau to the small business owner to help them quickly get to grips with what is happening in his or her company in an almost realtime basis. The superior visualizations allow the business owner to see what’s really going on in their company and zero in on a lot more potential problem areas or opportunities that may not be as obvious using Quickbooks Desktop or Quickbooks Online.
Existing Tableau users will love Cross database joins and Clustering but I suspect the Quickbooks Connector is going to be a lot more interesting for prospective new Tableau business owners and financial officers.