Integrate Clearbit Prospector with Google Sheets Using Zapier

Last Updated: June 3, 2022

For pricing information, please fill out the demo-request form on our pricing page to get in touch with a sales representative.

The Clearbit platform automatically gives you visibility into the companies that are viewing your website by revealing their ip address. We’re going to walk through an automation workflow that uses the domains of those companies to search for high-value prospects using Clearbit Prospector and save their information in Google Sheets.

Set Up Instructions

  1. Install the Clearbit Tag.
  2. Jump over to the Companies section of Clearbit.
  3. Click the New collection button if you haven’t created one already.
  4. Click ... next to your collection and select new audience.
  5. Add a condition that filters your traffic to companies that have any page on your website 1 time in the last week.
    Screen_Shot_2022-05-23_at_3.26.29_PM.png
    📝  Please note: See Set Up Audiences in Clearbit for more detailed instructions about this step, if needed.
  6. Once the audience is created, use a webhook destination to start configuring the webhook that will send the information on the company visiting your website over to Zapier. For the Trigger section, select the When a company enters the audience trigger option. This will send information over to Zapier as soon as a new company enters the audience.
  7. For the Configure webhook options section, you'll need to jump over to Zapier and create a new zap using the Webhooks by Zapier app with a Catch Hook trigger event.
    Screen_Shot_2022-05-23_at_3.47.55_PM.png
    Copy the provided custom webhook URL and paste it back in your Clearbit audience webhook destination.
    Screen_Shot_2022-05-23_at_3.49.06_PM.png
    Screen_Shot_2022-05-23_at_3.54.48_PM.png
  8. Notice the Enable delivery suppression checkbox. Use this option if you’d like to avoid receiving data on the same companies in a short period of time. In this example, we’re only including companies we haven’t been notified on before in the past month.
  9. For the Customize your Payload section, we’re interested in the company’s name and domain, so we’ll select Company Name and Company Domain. These two attributes will help us search for prospects using the Clearbit Prospector API in the following step.Screen_Shot_2022-05-23_at_4.10.35_PM.png
  10. After you’re done customizing the payload, click the Run test button and test your trigger in Zapier. A successful request should look like similar to this:
    Screen_Shot_2022-05-26_at_12.47.25_PM.png
  11. Now we'll call the Prospector API using Zapier. Clearbit’s Prospector API lets you fetch contacts associated with a company. We’ll handle this task using Zapier’s code action. Jump back to our zap and hit on the plus sign to add an action to it. Look for the Code by Zapier app and choose the Run Python action event.
    In the action setup, will create a variable domain and set it equal to the domain we collected from Clearbit in the previous step, Company Domain: [example.com]. Then in the code section, copy and paste the snippet below:
    from requests.auth import HTTPBasicAuth
    prospector_url = "https://prospector.clearbit.com/v1/people/search?"
    clearbit_api_key = "your-clearbit-secret-api-key"
    prospector_url += "domain=" + input_data["domain"]
    prospector_url += "&page_size=20&query=seniority:executive"
    response = requests.get(prospector_url, auth=HTTPBasicAuth(clearbit_api_key,''))
    leads = response.json()["results"]
    return leads
    This code snippet searches for prospects at the company that have seniority:executive. If you’d like to add additional criteria to the query, such as by role or subrole, you can do so by modifying line 7. Review the “Queries” section of the Prospector API documentation as well as the standardized list of values you can check against. For example, searching for executives in the engineering department would look like this → "&page_size=20&query=seniority:executive role:engineering". Lastly, make sure you update the placeholder value on line 4 with your account’s secret API key, which you can find by going here https://dashboard.clearbit.com/api.Screen_Shot_2022-05-26_at_12.49.14_PM.png
  12. Test your action and move on to the next step once your request is successful.Screen_Shot_2022-05-24_at_10.36.31_AM.png
  13. (Optional) Fetch the full person’s profile using the Enrichment API. Clearbit returns a limited number of attributes on a person whenever you use the Prospector API. These are: Given name, Family name, Full name, Title, Role, Sub Role, Seniority, Company name, Email, and Phone. If you’d like any other attributes, you’ll have to call Clearbit’s Enrichment API using the person’s email address. Add another action to your zap and select the Code by Zapier app with a Run Python action event again.
    This time we want to create a variable email and set it equal to the email collected in the previous step, Email: [example@email.com]. Then copy & paste the following snippet in the code section, making sure you update the placeholder value on line 3 with your account’s secret API key, which you can find by going here https://dashboard.clearbit.com/api.
    from requests.auth import HTTPBasicAuth 
    enrichment_url = "https://person.clearbit.com/v2/combined/find?"
    clearbit_api_key = "your-clearbit-secret-api-key"
    enrichment_url += "email=" + input_data["email"]
    enrichment_url += "&cached=true"
    response = requests.get(enrichment_url, auth=HTTPBasicAuth(clearbit_api_key,""))
    lead = response.json()
    return lead
  14. If your test is successful, you should see an enriched profile like the one shown below. Keep in mind that the attributes returned will vary by person.Screen_Shot_2022-05-25_at_2.19.39_PM.png
  15. To add the prospect’s information over to Google Sheets, let’s add one final action to our zap. Select the Google Sheets app with a Create Spreadsheet Row(s) action event. Afterwards, connect your Google account, select the Drive where your spreadsheet resides, the spreadsheet itself, and the worksheet where you want to add the data.Screen_Shot_2022-05-26_at_12.56.32_PM.png
  16. Next, we’ll want to match each value found by Clearbit to the appropriate column in our spreadsheet. You are welcome to add save as many Clearbit attributes as you’d like, but this specific worksheet contains the following columns: company_name, first_name, last_name, email, linkedin, title, logo, website, and added_by_clearbit.Screen_Shot_2022-06-01_at_12.44.31_PM.png
  17. Test your trigger and verify the data was added to your Google Sheet successfully. Congrats on the good work! 🎉Screen_Shot_2022-05-26_at_12.57.46_PM.pngScreen_Shot_2022-05-26_at_1.01.32_PM.png

Additional Resources