Posts tagged Data Visualization
Xero Financial Reporting in Looker using G-Accon for Xero, BigQuery and dbt

We use Xero as our accounting package at Rittman Analytics and thought it’d be useful to bring in some of our key financial and performance metrics from Xero’s Profit and Loss report (Income Statement for our US readers) into our Looker environment, so that we can monitor gross and net revenue trends over time, check expenses and staff costs are within budget and keep an eye on our most important financial KPI, Net Margin %.

Although Xero is a supported data source for Stitch, Fivetran and most other data pipelines-as-a-service, trying to reconstruct a P&L report from all of the Xero API tables whilst applying manual journals, categorising accounts into revenue or costs and coming-up with the same numbers as Xero’s own reports is fairly tricky; another approach that we’ve used with success is to use a Google Sheets add-in such as G-Accon for Xero to schedule exports of Xero’s P&L report into a Google Sheet and then access the results using a Google BigQuery external table.


When you’ve set the Xero report export up within Google Sheets and G-Accon for Xero, the next step is to setup a federated table in Google BigQuery that maps to the Google Sheets sheet that you’ve just setup. Remember to share the Google Sheets file with the GCP service account that your Looker connection uses when accessing Google BigQuery data otherwise you’ll get a permissions issue when trying to query the Sheets data from Looker.


Our next step was then to use dbt (“Data Build Tool”) to transform Xero’s account-level P&L numbers as landed into our base BigQuery federated tables into aggregated and derived revenue, cost of sales and overheads KPIs along with net and gross profit, margins and so on.


Now our financial numbers and P&L account details are in Looker as additional views within our main operational analytics LookML model we can start by reproducing the original Profit & Loss report using Looker’s new beta-release Table-Next visualization, giving us subtotals for the account categories as you’d normally expect to see in a P&L-style financial report (numbers obfuscated, in-case you were wondering).


Then to accompany the detail-level P&L report we then created a financial performance dashboard, some of the contents coming from the derived and aggregated financial KPIs table we created beforehand in dbt, some of the numbers (discretionary expense tracking over time for example) coming from the base P&L report numbers. Note how we’ve used Looker’s recently-added trellis charting feature to break expenses spending out by the discretionary spend categories we’re most interested in, I think this works well for this type of analysis.


Whether you go down the path of full Xero data integration into your warehouse using a service such as Stitch or Fivetran or if you choose the report-based export approach we used here, really comes down to whether you need granular access to every bit of your accounting data or some high-level (and processed) numbers as we did in this particular example.

We’ve done both on projects for customers and ourselves in the past, so if you’re interested in bringing in Xero or other financial accounting data into your Looker analytics platform, send us an email or call our office on +44 1273 234690 and we’ll be happy to talk through the various options with you.

Graph Analysis in the “How a Tweet Went Viral” Conference Presentation

Earlier this week I presented a session at the BIWA 2017 conference in San Francisco on using Oracle Big Data Spatial & Graph to understand how my WiFi kettle tweet went viral back in October last year, by using graph analysis and data visualization tools like Cytoscape and Tom Sawyer Perspectives.


I’ve uploaded the slides and embedded the demos as a series of Youtube videos within the slides, so you can see the new Timeslice-analysis feature in the Oracle Cytoscape plugin we developed for the presentation along with the mapping and analysis features in Tom Sawyer Perspectives that helped us work out exactly how the story went viral and who helped this happen … as you’ll find out in the slides.