This automated n8n workflow transforms raw CSV data into qualified, enriched lead databases with verified emails and AI…
This automated n8n workflow transforms raw CSV data into qualified, enriched lead databases with verified emails and AI-generated personalization. It processes uploaded CSV files through multiple validation, enrichment, and sorting stages before delivering organized results via Google Drive, WhatsApp, or Google Sheets. How It Works Stage 1: Data Intake & Normalization Form Submission: User uploads a CSV/XLSX file and selects: Delivery method (Google Drive, WhatsApp, or Google Sheets with processing) Two personalization prompts for AI-generated messages Column Mapping: The workflow automatically recognizes and standardizes 100+ column name variations into consistent fields Deduplication: Removes duplicate entries by website URL and contact name Initial Filtering: Identifies and flags invalid websites (social media profiles, link shorteners) Stage 2: Data Quality Routing The workflow analyzes each record and routes it based on available data: Route A: Invalid Website → Sends directly to "x Website" sheet Route B: No Website → Verifies existing email and categorizes by validity Route C: Valid Website + Email → Proceeds to email verification and enrichment Route D: Valid Website - Email → Scrapes website to find email addresses. Stage 3: Email Discovery & Verification For records needing enrichment: Website Scraping: Fetches HTML content from company websites Email Extraction: Uses regex patterns to identify email addresses AI Selection: GPT-5-NANO chooses the most relevant email from multiple options Dual Verification: Primary check via Reoon API (deliverability, SMTP, safety) Backup verification if primary fails Personal Email Processing: Separately validates personal emails when present Stage 4: Website Intelligence Gathering Parallel to email processing: Extracts HTML title tags for company context Captures meta descriptions for business understanding Pulls H1 headings for content insight Handles scraping failures gracefully with empty placeholders Stage 5: Email Categorization Logic Records are sorted into buckets: +1 Email**: Has exactly one verified email (business OR personal OR newly found) +2 Email**: Has two or more verified emails Single Source Records**: Only business email OR only personal email Stage 6: AI Personalization For records with verified emails: Context Assembly: Combines name, company, job title, website metadata, industry, keywords AI Processing: Sends to GPT-5-NANO or Gemini with user's custom prompts Structured Output: Generates: Cleaned contact name Shortened company name Personalization message 1 (per user prompt) Personalization message 2 (per user prompt) Stage 7: Final Delivery Based on user's initial selection: Google Drive**: Uploads timestamped CSV, sends email with link WhatsApp**: Converts to XLSX and sends document Google Sheets**: Populates separate sheets by category with completion email How To Use Active n8n instance with required credentials: Google Drive API access Google Sheets API access Gmail API access (for notifications) WhatsApp Business API credentials Reoon Email Verifier API keys OpenAI API key (GPT-5-NANO) Optional: Google Gemini API key Setup Steps 1. Configure Google Sheets Destinations Create or use existing Google Sheets for data categorization: +1 Email Sheet**: Update document ID in "+1 Email" node +2 Email Sheet**: Update document ID in "+2 Email" node x Website Sheet**: Update document ID in "x Website Data" node No Website Sheet**: Update document ID in "- Website +- Email +- Phone Number" node Ensure all sheets have matching column headers as defined in the workflow. 2. Set Up Google Drive Folder Create a destination folder in Google Drive Update the folder ID in the "Upload file" node Ensure the service account has write permissions 3. Configure WhatsApp Add your WhatsApp Business Phone Number ID in the "Send CSV" node Update the recipient phone number (format: +country code) 4. Customize Email Notifications Update sender and recipient emails in "Send a message" and "Send a Success Message" nodes Modify email templates as needed 5. Verify API Keys Check that all API credentials are active: Reoon Email Verifier keys (two instances for redundancy) OpenAI API key for GPT-5-NANO All Google API credentials. An n8n automation workflow template by Bhavy Shekhaliya.
Bhavy Shekhaliya
web
free
Others in the same category, ranked by how often they are opened.
