How to query your Xero data from Looker Studio
Xero is a sophisticated and comprehensive cloud-based accounting platform. For many, the built-in reports and analysis that Xero offers is enough for our day-to-day needs. However, if you need something a little more customized, or you need to aggregate your data over multiple Xero accounts, or you need to cross-reference your Xero data with other cloud services, then you’re going to have to build it yourself. And what better way than using the tool you’re already familiar with - Looker Studio (formerly Google Data Studio).
In this article, I’ll take you through the steps to connect Looker to your Xero data, and get started customizing the reports you need.
First - grab your SyncHub Credentials
If you don’t already have a SyncHub account, you can grab a free trial here - go on, I’ll wait - it only takes a minute and you can cancel as soon as you’ve finished this tutorial if you like.
Ready? Now, a quick reminder - SyncHub works by staging your Xero data in a relational database. This gives us a huge advantage over other connectors which query the Xero API directly, but I won’t go in to them here (check this blog post if you need convincing). The point is, once you have connected your Xero account, querying from Looker is trivial as you are just using it’s native SQL Server Connector.
So, once you’re connected, go to your SyncHub Dashboard and grab your new database credentials:
As you can see, there are four main parts to your connection - the server and database, and the username/password. You’ll need them all for the next parts.
Reading your Xero data from Looker Studio
We now just need to tell Looker where to find your data, and for this we use the built-in SQL Server connector - easy! Hit the Add Data button, then search for “SQL Server”:
Now, plug in the credentials which you downloaded from SyncHub earlier:
Once authenticated, you can write a custom query to extract just the view you need. Here, we’re grabbing the top few records from our Xero Accounts table:
Please note: Unfortunately, if you are using the free database that SyncHub provides as part of your licence, you won’t be able to use the Table Browser here, as Looker doesn’t support the enumeration of SQL Azure tables. This means you must write a custom query, but fear not - you only need to write them once and if you need some help understanding your data model, just check out our Data Model or our Query Editor playground.
An efficient alternative to custom queries
Rather than building complex queries within your Looker connection, we recommend using our Insights platform. Insights allows you to write and pre-execute complex queries and store them in a dedicated database table suitable for high-frequency reporting.
Creating Xero reports in Looker Studio
From here, it’s easy. Just use your regular Looker skills to slice and dice the data as required for your reports. Here, we’re simply listing the Code and Name of our results in a table view. You can see the familiar Data Navigator down the left-hand-side, which lets you filter/group/slice/dice/whatever data you need, as usual.
As you can see, Looker does all the heavy lifting for you - all you need is the right data in the first place. With SyncHub, you can pull down information from different tables and join according to our data model. For example, you might build a report to show Payments by Customer, just by using lookups between the Payments table and the Invoice table, and then to the Customer table. With the raw data provided by SyncHub, your options are almost unlimited.
Keeping your data up-to-date
SyncHub updates it’s staged data from Xero in near-realtime, so it’s always available. However, Looker offers it’s own cache which may have a different refresh setting. To adjust the freshness of your data, simply Edit your connection and then the Data Freshness value:
Beyond Xero
Xero-specific reports are essential, but the true power of SyncHub comes when you augment your Xero data with additional information:
SyncHub allows you to pull in data from multiple Xero accounts, and compare/report/aggregate from within the same Looker report
Most businesses use multiple cloud platforms. SyncHub provides connectors to a wide range of popular cloud platforms - Deputy, Tanda, Unleashed, Pipedrive, Teamwork, simPRO, Wrike….and many many more. Imagine the insights you could gather by consolidating this information into a single dashboard (or see this case study for real-world examples).
So what are you waiting for? Grab a free trial of SyncHub here and see what you can do. In ten minutes from now, you could be reporting against your Xero data and taking your first steps towards a data-driven business.
