For some time now the marketing guys here at SYNQ have been asking me to help them get some very specific usage data into HubSpot, data that normally resides in our database.
Update (May 22, 2018): Since the writing of this post, our product has evolved and no longer makes use of Metabase and Zapier.
For those of you that don’t know, HubSpot is a CRM, marketing and sales tool. It, amongst other things, allows us to set up workflows that our customers flow through and lets us react to their interactions with our service and our communication with them.
Being a Video API you can imagine that marketing and sales would like to be able to react to a new user that uploaded a video. It makes no sense to send a new user that has downloaded one of our SDKs and uploaded a video an intro mail explaining how to do that. Instead we would like to send that user some more advanced examples, showing off fancy stuff like programmable webhooks.
So how do we get data stored in our database to HubSpot with minimal amount of developer effort? Creating any separate endpoints or services was out of the question for this kind of task.
The idea struck when reading the documentation for Metabase trying to solve some unrelated issue. We were already using Metabase to monitor our database and let non-devs look up statistics. Metabase claims that it “[...] is the easy, open source way for everyone in your company to ask questions and learn from data.”, and they are right. Internally, Metabase has an API backend that their front-end queries for data, and this API is available to anyone! Awesome! That means we can create any parameterised question in Metabase and query it from somewhere else.
One thing to note is that Metabase is currently discussing the possibility of adding a public versioned REST API.
So we now have something that wants data, HubSpot. Something that makes that data available, Metabase. But we are missing the final piece of the puzzle, something to connect these two, HubSpot has a nice API but no way to query a data source. Enter Zapier! A tool that lets you integrate any services that have public APIs.
Zapier comes with a wide array of integrations allowing you to build what they call Zaps, a Zap usually consists of a trigger, and one or more actions. The trigger is generally some service that sends data to Zapier, for example through a webhook. Actions are generally POST requests made to another service using data gathered from the trigger or a previous action. Sweet! Sounds exactly like what we want.
The complete plan then comes out to these steps:
- Allow our HubSpot administrators to create workflows that trigger the Zap by webhook.
- The Zap's next action is to get data from Metabase through the API.
- A new action updates the customer in HubSpot with the data gathered in step 2.
A normal webhook trigger should solve step 1 and Zapier comes with a HubSpot integration that has actions for updating a customer so that solves step number 2. Unfortunately it does not have a Metabase integration, but what it does have is an easy way to create your own!
Zapier provides great documentation and examples for making your own integrations. And I spent most of my time looking at their example apps. The authentication that we want to do for Metabase is pretty much the same as in the SimplyBook (session auth) example, except for a few minor changes. So I would suggest following that example and then implementing the few changes outlined here:
- Instead of adding a company name as an auth field, add “url”.
- You need to adapt the scripting API to the json structure that Metabase uses for authentication.
- For a complete example see this gist: https://gist.github.com/halvardos/1a4366f835d2c939b49def2d988d85c0
- When authentication is done all we need to do is add the action.
For our example the action connects to the a card we made that returns the number of videos a customer has uploaded to our service given an internal ID representing the customer.
So we create a new action with the key “uploaded_videos”, it should have a single action field named customerID. Action fields are essentially data you want piped into the scripting API from a previous step. In this case we get that in the webhook that HubSpot sends out to trigger the Zap. For the post url I used this: “/api/card/9999/query” where 9999 is the card number from HubSpot.
Then all we have to do is add a bit of data parsing to the scripting API to massage the data into the correct formats.
Given the names above the current functions should be added to the scripting API:
Zapiers scripting docs are really good, and I would strongly recommend reading them if you plan to do something similar. That concludes the hard part. For composing this into a complete zap all we have to do is:
- Setup a standard webhook trigger that HubSpot calls.
- Setup our new Metabase Get uploaded videos action to use the customerID we got from step one.
- Send the results into a create or Update contact integration for HubSpot.
I’m really looking forward to using Zapier to solve more issues like this, where we spend time doing manual work that can easily be automated.