Weekly outbound prospect list from your target accounts sheet

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.