How our marketing ops team built a realtime lead-to-account matching solution with Hasura, BigQuery, Zapier and Salesforce.
Hasura’s Martech stack includes Marketo, Salesforce and BigQuery primarily, and several other tools to fill in the missing pieces of the puzzle. One part of the process is mapping leads to accounts in Salesforce. For example, if we have several folks aggregated in Salesforce from company X, they would need to be tied into Company X’s Account, and not appear as individual leads.
As we use information in Salesforce as well as outside of Salesforce, we have the logic stored in Google BigQuery as a view, and it was uploaded into Salesforce at a monthly cadence for us to be able to map every relevant lead to the matching account.
With Hasura’s recently announced support for BigQuery, our Marketing Ops team was able to automate this workflow by using Hasura’s instant GraphQL & REST APIs on BigQuery and powerful querying abilities and make this process real-time.
In this tutorial, we will walk through how this was set up using Hasura Cloud! Follow along, and do let us know if you have any questions. If you’re looking to try Hasura out on your BigQuery database, get started instantly here. It takes <1 min to get a GraphQL API on your existing data!
Go to IAM & Admin > Service Accounts > Create Service Account
Give it a name, and under roles, and grant these 3 roles: BigQuery Metadata Viewer, BigQuery Data Viewer and BigQuery Job User.
Click on the created service account, Keys > ADD KEY > Create New Key > JSON > Create. This will download a service account file on your computer.
Salesforce Setup
Create a lookup field to Account on Lead object.
Create a BigQuery View
Create a view in BigQuery with lead id to update with a potential matching account.
At Hasura, we use website addresses to match leads to accounts. Additionally, we prefer if the lead's country name matches the account country.
Tip: NET.REG_DOMAIN to standardize the website address when you are doing the matching.
So if we have an account for Hasura (country = United States) and also account Hasura (India), and if we find a lead with hasura.io website in the United States, we associate this lead to Hasura account in the United States, not in India.
Also since one company may have multiple website addresses, we maintain a BigQuery table with 2 columns. 1 column with main account website, and another column with website address that Hasura’s sales team consider as the same account/company. (ie. disneyplus.com is part of the Disney account.)
Tip: One thing to keep in mind if you are setting up something similar, if you create a BigQuery table from Google sheet or using an external BigQuery table, you will not be able to read this table from Hasura.
Set it so that query result of this view contains Lead ID for the Salesforce record and Account ID that Lead is matched to.
Hasura Database Connection
Go to Hasura Cloud dashboard, create a project (if not created yet), then within the Console, go to Data tab.
Database Display Name: Name the database Data Source Driver: BigQuery Connect Database Via: Connection Parameters Service Account Key: Copy and paste the json you got from creating a service account in BigQuery. Copy and paste Project Id, Datasets from BigQuery. I left Global Select Limit as 1000 which was the default value.
Set it to track the view you have created in BigQuery.
Go to the API tab then find the table that was created from the BigQuery view in Explore, then select the columns with the result. (Salesforce Lead ID and matched Account ID.)
Click on "Rest" button then Create Endpoint.
Name the Endpoint, add description and use location field to set the webhook URL to be used in Zapier.
Leave "GET" checked as Methods.
Set up Zapier
Create a Zap that’s triggered by Retrieve Poll.
Set up a trigger.
URL is the webhook URL created in Hasura REST Endpoint. Key is this part of query result.
Test the trigger then you will see the result of the query.
Turn the Zap on then it will run every hour if there is a new match to the query.
Then add a step to update record in Salesforce. Map Lead id to Record to Update field, then Account ID to the field that was created as lookup field.
Success!
It checks the result of the query every hour and update records if they find new entry in the query. (It will only run for the new result added to the query.)
Result
With this setup, we are able to answer questions like
How much activity do we have from this prospect account?
We can see activities from both Leads and Contacts, without converting all the leads into an account.
Who is the right rep / SDR for the lead?
We are able to make decisions on lead routing with combined information, not just the information on the lead.
