Posts in Tips and Techniques
HubSpot Data Actions, Harvest Analytical Workflows and Looker Data Platform

At Rittman Analytics we use a number of SaaS applications to run our back office operations including HubSpot CRM for sales tracking, Harvest for timesheets and resource planning, Jira for project management and Xero for invoicing and accounting. Back in May 2019 I blogged about how we used services from Stitch, Fivetran and dbt to extract, transform and combine data from those services into a data warehouse running on Google BigQuery and since then we’ve built-out a number of finance, operations and customer success dashboards that provide our back office team with valuable insights into the performance of our business.

dashboards.png

But Looker is more than just an ad-hoc query and dashboarding tool, it’s a full data platform that integrates with SaaS applications such as Harvest, HubSpot, Xero and Salesforce at multiple levels:

  • At a data-source level, querying and analyzing data from those application sources

  • Within the Looker explore UI using features such as links, data actions and the Action Hub

  • Within business workflows, providing analytical insights and trusted metrics through the Looker API

Adding Links to HubSpot Deal and Company Pages as Explore Menu Items

One example of this is how we’ve added menu options in the Looker explore to provide direct links into HubSpot for viewing the details of a deal or a company within that application, and for our sales manager to request an update on a deal where there’s been no contact from either side for a while.

hubspot1.png

This type of simple integration is done through the “link” property in LookML that you can add to dimension and measure definitions, as I’ve done in the example below for our HubSpot opportunity name dimension.

dimension: opportunity_name {
    type: string
    sql: $.dealname ;;
    link: {
      label: "View Deal in Hubspot"
      url: "https://app.hubspot.com/contacts/4402794/deal/{  }/"
      icon_url: "http://app.hubspot.com/favicon.ico"
    }
}

Then when a user clicks on a field in an explore or a dashboard tile they’ll be able to click-through to HubSpot to view all of the context of the deal; if like us you use Google Apps authentication for both Looker and our SaaS applications you’ll be taken straight into HubSpot without having to login first.

link.png

Creating Looker Data Actions to Update HubSpot Deal Properties

A more advanced form of integration between Looker and HubSpot that we’ve setup is the ability for users to update details of a HubSpot deal directly from within Looker, a good example of how you can make your Looker content more “actionable”.

data_actions.gif

This type of integration involves another type of LookML dimension and measure property called an “action”. I first talked about actions in a blog post titled “Using Looker Data Actions to Make Monzo Spend Analysis More Interactive … and Actionable” back in 2018 where I used the feature to call services such as Google Maps, Google My Business and via Zapier, Google Tasks; in the case of our HubSpot integration we’ve again use Zapier for the webhook service that our data actions call, setting up a three stage Zapier “Zap” that takes the details of the deal and the values that need changing from the webhook payload, then searches for the deal in HubSpot that needs updating and then applies those changes to the deal record.

dealstage.png

Adding Data Action Menu Items to Request HubSpot Deal Updates via GMail

Another example of this type of data action integration we’ve setup is between Looker, HubSpot and GMail, giving us the ability to send an email to request a status update from the team directly from the Looker dashboard.

dbm.png

Leveraging the Looker API to Provide KPIs for Harvest Workflows Automated using Zapier

As well as providing hooks into HubSpot for managing the deal process directly from within Looker, we also use Looker to inform and provide trusted business key performance indicators (KPIs) for our automated workflows. An example of this is when we’ve closed a new deal and the salesperson or operations team then sets up a new project in Harvest to record our time and expenses against, and something we want to keep a close eye on is situations where a salesperson has closed the deal but done so by agreeing a daily rate below the rate we’d normally charge.

To keep an eye on this type of deal being agreed and to make sure the salesperson involved knows that the discount will be coming out of their commission, we’ve setup another Zapier workflow that detects new Harvest projects being created, runs a look within Looker that returns the average rate charged across all projects over the past three months and sends a message to the salesperson concerned if the deal rate is below this average.

revenuebot.png

In the future we can change how this average rate is calculated, introduce other data and analytics into the calculation and by using Looker we’re making sure these analytic insights are governed and delivered “as-a-service” to the requesting business process. If you’re looking to use Looker Data Platform to integrate, inform and help orchestrate your business processes then drop us an email and we’d be happy to share our experiences so far.

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.

g-accon.png

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.

bq.png

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.

dbt.png

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).

pandl.png

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.

dashboard.png

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.