Big Screen (and Apple Watch) KPIs and Metrics using BigQuery, dbt and Numerics.app

Mark Rittman

Rittman Analytics has just moved into new offices in Brighton and we took the opportunity to buy a new 55” flat screen TV to use for presentations, screen sharing and an team KPI dashboard.

Although we use Looker for developing client dashboards and working on analyses on our laptops, for our big screen dashboard we use a tvOS app called Numerics that connects directly to our internal data warehouse running on Google BigQuery and dbt (“Data Build Tool”).


IMG_1322.jpegIMG_1322.jpeg

We land raw data from our various CRM, sales, project management and other SaaS apps into Google BigQuery using Stitch and Fivetran, then transform and integrate those data sources into a conformed dimensional data warehouse schema using dbt – the screenshot below shows the transformation steps our data needs to go through before providing the data for our current weighted pipeline KPI against target KPI.


dag.pngdag.png

To create the data visualizations that go into our big screen dashboard we then use dbt to create another set of downstream, summary-level BigQuery tables that we can then query using simple “SELECT * FROM …” query definitions in Numerics – note that if you try this yourself you’ll need to use LegacySQL rather than StandardSQL queries when defining the datasources for your Numerics queries, which is why we chose to materialize all our summary-level queries in dbt as tables rather than Standard SQL-dialect views.


ios.pngios.png

Numerics uses iCloud in the background to sync dashboard visualizations between all of your iOS devices, so the same metrics come up on my Apple Watch as alerts and metric summaries, and you can even query them using Siri Shortcuts (“how many clients do we have right now”, for example)


watch2.pngwatch2.png

But our main use of Numerics is for the office dashboard, with our BigQuery warehouse and summary tables updating every 30 minutes via dbt and our key business metrics displayed nice and clearly in a native iOS/tvOS app on our office Apple TV.


IMG_5855.JPGIMG_5855.JPG

We’re not a partner with the company behind Numerics nor have we used it on client projects but their software’s pretty cool, and connects to lots of other data sources beyond BigQuery – check it out on the various app stores on your iOS, iPadOS, WatchOS and tvOs devices..