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.

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.

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: stringsql: $.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.

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

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.

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.

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.

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.