How our marketing ops team built a realtime lead-to-account matching solution with Hasura, BigQuery, Zapier and Salesforce.
Tags: bigquery
Published: 2021-09-13 13:26:40

---
title: How our marketing ops team built a realtime lead-to-account matching solution with Hasura, BigQuery, Zapier and Salesforce.
published: true
date: 2021-08-24 15:19:57 UTC
tags: BigQuery
canonical_url: https://hasura.io/blog/how-our-marketing-ops-team-built-a-realtime-lead-to-account-matching-solution-with-hasura-bigquery-zapier-and-salesforce/
---

 ![How our marketing ops team built a realtime lead-to-account matching solution with Hasura, BigQuery, Zapier and Salesforce.](https://hasura.io/blog/content/images/2021/08/lead-to-account.png)

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](https://hasura.io/docs/latest/graphql/core/databases/bigquery/getting-started.html), our Marketing Ops team was able to automate this workflow by using Hasura’s instant [GraphQL & REST APIs on BigQuery](https://hasura.io/graphql/database/google-bigquery/) and powerful querying abilities and make this process real-time.

![How our marketing ops team built a realtime lead-to-account matching solution with Hasura, BigQuery, Zapier and Salesforce.](https://hasura.io/blog/content/images/2021/08/image-4.png)

In this tutorial, we will walk through how this was set up using [Hasura Cloud](https://hasura.io/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!

## Tutorial

### Setup Access for BigQuery  

On Google Cloud Console, [create a service account](https://cloud.google.com/iam/docs/service-accounts).

Follow steps on [pre-requisites](https://hasura.io/docs/latest/graphql/core/databases/bigquery/getting-started.html#pre-requisites).

Go to IAM & Admin > Service Accounts > Create Service Account

![How our marketing ops team built a realtime lead-to-account matching solution with Hasura, BigQuery, Zapier and Salesforce.](https://lh4.googleusercontent.com/9YRJxtlvV10_euXkmGh87gnh_0ua_fUgi3jWu-BHx0Vwj7WH7HfaOzw41VxjaypNHWsjlYaytD3QpGMRwzIP3o_9xMGorNwe7AgYgDrhmT-V_aPLjLBj6ZPSAUg-5J2vdBfMXFUb)

Give it a name, and under roles, and grant these 3 roles: BigQuery Metadata Viewer, BigQuery Data Viewer and BigQuery Job User.

![How our marketing ops team built a realtime lead-to-account matching solution with Hasura, BigQuery, Zapier and Salesforce.](https://lh5.googleusercontent.com/zGINNJ5LBgl6zKqBDMg_fBezvwbQvlouf1nh1vIMQLV5EP1SgpgKvtvllSt8kUyUSllDVah1PigdE9sNIbXSC6SPc6LsYBl9GMvPkPj6XD2MOZV0hGsXKAXAqQcMoDR-SuWUKwtY)

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.

![How our marketing ops team built a realtime lead-to-account matching solution with Hasura, BigQuery, Zapier and Salesforce.](https://lh4.googleusercontent.com/2nEcTZ_otL07KyBzrot5Z-qVpInXhy2Cu-94SE9m0-5shEVQXZS7GFRUcXF3-2xjAkqzVxoXfH_FYDwYFtK2T6LH4Rwj-5jZ9T18fSzRwXl6BaZ1aq6iqCJIPxduRlM6WmVAnTXS)

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

![How our marketing ops team built a realtime lead-to-account matching solution with Hasura, BigQuery, Zapier and Salesforce.](https://lh5.googleusercontent.com/rhQaHoh_ZqqHVU6gEykwsaUrVQGzOUHE-1qNvJ8lRRIDsd86OH150t9U000O0MjJQsIt2NZK2d3KpARx4gSW7fgQF7nFR0WuFB9pNyPISqbuKl8PHSAq0MGdXljXYSBhVaaYMSfj)

_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](https://cloud.google.com/bigquery/external-data-sources), 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](https://cloud.hasura.io), create a project (if not created yet), then within the Console, go to Data tab.

![How our marketing ops team built a realtime lead-to-account matching solution with Hasura, BigQuery, Zapier and Salesforce.](https://lh6.googleusercontent.com/Vu9CDWeSwVr5caNnw9-jMWamtXsBlQAhu_HYgtRpPDHnDcL72fYpEMruJ0JYR48RoBrnveO_821UA3oafADWxcvYMsvy1NCYdNEiR5wLmqSxOJAmLhJWhmgb2LgF0jPo0JiDbSWm)

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.

![How our marketing ops team built a realtime lead-to-account matching solution with Hasura, BigQuery, Zapier and Salesforce.](https://lh4.googleusercontent.com/lca6vt8z8QGiEz0ip5GtbKsgJ9e9ZTOU0gwAQFWWJQS5VIc_Wn_ODlUi8dUv1sObrsb8rc8ckALdlaokhafFcZFNlcsIs6MZlHNVUqVbUWcdr7NZF5GdtwnvKKeJMEI-5B_l6uMf)

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

![How our marketing ops team built a realtime lead-to-account matching solution with Hasura, BigQuery, Zapier and Salesforce.](https://lh5.googleusercontent.com/pFu3s8HAylXdgGmQQJhQ2pQjDW7yZ21d7bS3NEJZlycKMc0o378PDWEYoTuZblTMrlGVPTfIpd40xBBvlS8iVVps-Ws_hH67VddDFSMxA_-5AzrCfVoJasC_WIf0nBIXlt7gzIxl)

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.

![How our marketing ops team built a realtime lead-to-account matching solution with Hasura, BigQuery, Zapier and Salesforce.](https://lh3.googleusercontent.com/qj2Xp603mdmj85KPcV1cwquY4FF7EO7ajXF13VY7tVmTMYrxkzAYiQPOyC32UmgShGflg9bF-F41GmgUaMrFOalQvwlmF_rayreIOcMn1bcYBKYT8YWeYre7IHb_O1y4SttvsdyM)

### Set up Zapier  

Create a Zap that’s triggered by Retrieve Poll.

![How our marketing ops team built a realtime lead-to-account matching solution with Hasura, BigQuery, Zapier and Salesforce.](https://lh3.googleusercontent.com/991HR92EakUfnhfIz1PN_28N_pQwQlHd9SgyFo850Oe2N3wbFcHSqzmCmGlAXuH9zi7pqxNg1lcQmciJcPDRzUJuvIJEnIUhQl6Vq629qYfOOIyJt1v9qTU5UWkz5pmrc9FnUQnY)

Set up a trigger.

![How our marketing ops team built a realtime lead-to-account matching solution with Hasura, BigQuery, Zapier and Salesforce.](https://lh6.googleusercontent.com/_XGiGwNTVZZb5rWVQXOD5XC_K5fV4Hp4WYXuSZSiyeReKObDiSfQargNm_wo148TetoByTv3w2cVIoh8EeK3_rgdcTMXV7ZRNDj9oXkUvEWSRXnky6nptbrynnuycps7PaXWEoWe)

URL is the webhook URL created in Hasura REST Endpoint.  
Key is this part of query result.

![How our marketing ops team built a realtime lead-to-account matching solution with Hasura, BigQuery, Zapier and Salesforce.](https://hasura.io/blog/content/images/2021/08/image.png)

Test the trigger then you will see the result of the query.

![How our marketing ops team built a realtime lead-to-account matching solution with Hasura, BigQuery, Zapier and Salesforce.](https://lh5.googleusercontent.com/BdEz5JcQFi6Cp_NzSbKdvJNp7Lx9VnGwImYp94mF7XjyKFDRuOSeQ2nMpV-xvMkOawoWttxutuFXRa5zoeVV-6j2ENwaeA6I70z5279QRoG1ULDKU_ZktrfCPSczy78D-ei0OpaK)

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.

![How our marketing ops team built a realtime lead-to-account matching solution with Hasura, BigQuery, Zapier and Salesforce.](https://hasura.io/blog/content/images/2021/08/image-1.png)

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

![How our marketing ops team built a realtime lead-to-account matching solution with Hasura, BigQuery, Zapier and Salesforce.](https://lh3.googleusercontent.com/Rzs8YeavnyHor0teION5HpLPOKvCVQDNmHcna8ijvEFtZlhZG_kaQoXJhBIs3sS_jZGcxUgPiKZDYTu8Rs-iPbzXBpsf2ol3dwZJw8LiwVasCf0Uplapk7-NqT8ChKJESGhahh04)

### Result

![How our marketing ops team built a realtime lead-to-account matching solution with Hasura, BigQuery, Zapier and Salesforce.](https://lh4.googleusercontent.com/nqlI2BuiexkbgCaKuAAsc97iuVsjMV-Y3zncH6Ja-DWfvqlIK2CIz-Qd_fbm_EIz_G3sV4OVonFDLwT0w0QNpGWEbrYfIYLya2jPwqIxvooc0u9gMNvBjtRyIDmligDllgAPdXqd)

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.