Weekly outbound prospect list builder for HubSpot

Every Wednesday at 7am America/New_York (cron trigger), build a fresh outbound prospect list for my target accounts and push the new contacts into HubSpot.

Step 1: Read my target-accounts tab in Google Sheets using Get Values. The tab has these columns: company_domain, persona_titles (a Boolean title expression like 'VP Sales OR Head of Revenue'), location, and account_owner. Treat the first row as headers.

Step 2: For each target-account row, call Waterfall.io Prospector with: domain = company_domain, title_filters = persona_titles (normalize it into valid Boolean syntax with uppercase AND/OR/NOT if the sheet uses lowercase or commas), location = location, verified_only = true, and a limit between 5 and 10. Prospector is async, so launch the job and poll the finder until status is no longer RUNNING.

Step 3: If Prospector returns zero verified contacts for an account, skip that account, remember it, and move on. Do not write anything to HubSpot or the audit sheet for skipped accounts.

Step 4: Collect every returned contact's professional email across all accounts, then call HubSpot Batch Read Contacts using email as the unique property to check which ones already exist in the CRM. Filter the Prospector results down to only contacts whose email is not already in HubSpot.

Step 5: Push the remaining new contacts into HubSpot using Batch Upsert Contacts, matched by email. Set first name, last name, email, jobtitle, company (from Prospector's company_name), website (from company_domain), and hubspot_owner_id derived from the account_owner column on that account's row in the sheet.

Step 6: For every contact actually upserted, append one row to a 'prospects-added' tab in the same Google Sheet using Append Values. Columns in order: account (company name or domain), prospect name, title, email, date (today's date in YYYY-MM-DD).

Step 7: Return a short end-of-run summary that lists: total accounts processed, total new contacts added, total contacts skipped as duplicates, and the names of any accounts that returned zero verified contacts so I can decide whether to requeue or replace them.

Reasoning notes for the agent: persona_titles from the sheet may be messy (commas, lowercase operators, stray quotes), so normalize before calling Prospector. If Prospector returns a 429 rate limit, respect Retry-After and back off. Batch HubSpot calls in groups of up to 100 contacts. Treat an account as 'exhausted for this week' only if Prospector returns zero results with the original filters; do not auto-broaden filters without surfacing it in the summary.

Additional information

What does this prompt do?

Reads your target-accounts tab in Google Sheets every Wednesday morning and turns each row into a fresh prospecting job.
Pulls 5 to 10 verified contacts per account by company domain, persona titles, and location.
Checks each contact against HubSpot first so you never re-add someone who is already in your CRM, then upserts the new ones with the right account owner.
Logs every new prospect to a prospects-added tab and ends with a short summary of which accounts came back empty.

What do I need to use this?

A Google Sheet with a target-accounts tab that has columns for company domain, persona titles, and location.
A Waterfall.io account to source verified contacts at each company.
A HubSpot login with permission to read and create contacts.
An account_owner value in your sheet that matches the HubSpot owner you want assigned.

How can I customize it?

Change the cadence or send time, for example daily instead of weekly, or Monday morning instead of Wednesday.
Adjust how many contacts you want per account, or expand the persona titles to cover more buying-committee roles.
Add extra filters like seniority, department, or excluded titles to keep the list focused.
Route brand-new contacts into a specific HubSpot list or lifecycle stage so your SDRs can pick them up immediately.

Frequently asked questions

What does my target-accounts tab need to look like?

At minimum a column for the company domain, a column for the persona titles you want to reach (for example 'VP Sales OR Head of Revenue'), and a column for location. An account_owner column is recommended so each new contact lands with the right rep in HubSpot.

Will this create duplicates in HubSpot?

No. Before pushing anything, the agent checks each email against your existing HubSpot contacts and only sends the new ones through. Existing contacts are left alone.

What happens if an account has no verified contacts?

That account is skipped, nothing is written to your CRM, and the account name shows up in the end-of-run summary so you can decide whether to retry it next week or replace it.

Can I run this more often than weekly?

Yes. The schedule is a knob you control. You can switch it to daily, twice a week, or any other cadence when you set the workflow up.

Do I need a separate audit log, or does the sheet handle it?

The sheet handles it. The workflow appends a new row to a prospects-added tab for every contact it pushes, including the account, name, title, email, and date, so you always have a record.