See llms.txt for all machine-readable content.

Back to Templates

Score B2B leads and draft cold emails with Google Sheets, Apify and OpenAI

Created by

Created by: Kseniia || xenikii
Kseniia

Last update

Last update 6 hours ago

Categories

Quick overview

This workflow runs every 30 minutes (or manually) to pull B2B leads from Google Sheets, enrich them via Apify (domain/tech/MX, hiring signals, and Google Ads activity), score fit and intent, and use OpenAI to draft outreach emails that are saved as Gmail drafts and synced back to Sheets.

How it works

  1. Runs on a 30-minute schedule (or manual trigger), loads the sender/ICP configuration, and reads lead rows from Google Sheets.
  2. Selects a batch of new or stale leads (30-day TTL) and retryable items (up to the max retry count), then locks them by setting Status to Processing in Google Sheets.
  3. Calls the Apify Company Domain Finder, Tech Stack & MX/DNS Enricher (https://apify.com/xenikii/company-domain-tech-stack-enricher) and separates accounts into Verified + MX, No MX (LinkedIn / Call Only), Needs Review (Low Confidence), Needs Review / Retry (bounded retry count), and Unresolved Domain.
  4. For verified domains with MX, queries Apify B2B Hiring Signals & Multi-ATS Job Scraper (https://apify.com/xenikii/company-career-ats-job-scraper) and Google Ads Transparency Scraper (https://apify.com/xenikii/google-ads-transparency-scraper) in parallel (with alwaysOutputData and automatic retry handling on transient errors).
  5. Computes Fit_Score (0–50), Intent_Score (0–50), Total_Score (0–100: Hot >= 75, Warm 55–74, Cold < 55), and Score_Breakdown, and prioritizes sales/commercial role emails while excluding legal/support addresses.
  6. For Warm and Hot leads (Total_Score >= 55) in target geographies, uses OpenAI (gpt-4o-mini) to generate a grounded, language-matched 3-sentence cold email draft with a code-enforced GDPR opt-out footer.
  7. Creates a Gmail draft for leads with a valid primary email and syncs the final scores, enrichment data, and draft status back to Google Sheets.

Setup

  1. Create a Google Sheet with columns: Company, Country, Status, Retry_Count, Outreach_Status, Resolved_Name, Domain, Website, Fit_Score, Intent_Score, Total_Score, Intent_Tier, Score_Breakdown, Outreach_Channel, Email_Provider, DMARC_Policy, Tech_Stack, ATS_Provider, Open_Jobs, New_Jobs_30d, Hiring_Focus, Sample_Roles, Active_Google_Ads, Evergreen_Ads, Primary_Email, Role_Emails, Phones, LinkedIn_URL, Description, AI_Email_Subject, AI_Email_Body, Last_Enriched_At.
  2. Add an Apify API token using HTTP Header Auth (Authorization: Bearer <YOUR_APIFY_TOKEN>) for the three Apify HTTP requests.
  3. Add an OpenAI credential and confirm the selected model (gpt-4o-mini) is available in your OpenAI account.
  4. Add a Gmail OAuth2 credential for creating drafts and ensure the Google Sheet document and sheet names/IDs point to your own spreadsheet.
  5. Open the Sender & ICP Config node to set your senderName (must be changed from YOUR_NAME_AND_COMPANY to enable Gmail draft creation), offerSummary, callToAction, targetCountries, and targetTechKeywords before activating the schedule.

Requirements

  • Google Sheets OAuth2, Gmail OAuth2, OpenAI API key, and Apify API token (HTTP Header Auth: Authorization: Bearer YOUR_APIFY_TOKEN).

Customization

  • Adjust batchSize (default 10 rows per 30 min = 480 leads/day), warmScoreThreshold (55), hotScoreThreshold (75), targetCountries, and targetTechKeywords in the Sender & ICP Config node.

Additional info

Uses three Apify intelligence signals: Company Domain Finder, Tech Stack & MX/DNS Enricher (https://apify.com/xenikii/company-domain-tech-stack-enricher), B2B Hiring Signals & Multi-ATS Job Scraper (https://apify.com/xenikii/company-career-ats-job-scraper), and Google Ads Transparency Scraper (https://apify.com/xenikii/google-ads-transparency-scraper).