06 - Lead Email Validation Workflow

{
  "id": "CbuL77uh6tA3fVsy",
  "meta": {
    "instanceId": "c91c5b6efe2709e07c37996245857ac5d863d575d07e0072127351337c204c40",
    "templateCredsSetupCompleted": true
  },
  "name": "06 - Lead Email Validation Workflow",
  "tags": [],
  "nodes": [
    {
      "id": "5722ab46-2a4a-4f51-97ca-e5dc97a58fa1",
      "name": "📥 Fetch Emails from Source Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -40,
        -20
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/{{YOUR_INPUT_SHEET_ID}}/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "{{YOUR_INPUT_SHEET_ID}}",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/{{YOUR_INPUT_SHEET_ID}}/edit?usp=drivesdk",
          "cachedResultName": "Emails"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "LJQH5glroAM1SiFe",
          "name": "Google Sheets OAuth2"
        }
      },
      "typeVersion": 4
    },
    {
      "id": "84ba1906-ec77-4890-8499-81521c215ddf",
      "name": "🧹 Clean & Prepare Email Data",
      "type": "n8n-nodes-base.code",
      "position": [
        180,
        -20
      ],
      "parameters": {
        "jsCode": "// Process email data and prepare for verification\nconst items = [];\n\nfor (const item of $input.all()) {\n  const email = item.json.Email || item.json.email;\n\n  if (email && email.includes('@')) {\n    items.push({\n      json: {\n        email: email,\n        originalRowIndex: item.json.row_number || items.length + 2,\n        firstName: item.json.FirstName || '',\n        lastName: item.json.LastName || '',\n        company: item.json.Company || ''\n      }\n    });\n  }\n}\n\nreturn items;"
      },
      "typeVersion": 2
    },
    {
      "id": "330e8cf0-dfc3-49d7-bcad-8343fa329740",
      "name": "🕵️‍♂️ Hunter.io Email Verifier",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        400,
        -20
      ],
      "parameters": {
        "url": "https://api.hunter.io/v2/email-verifier",
        "options": {},
        "sendQuery": true,
        "queryParameters": {
          "parameters": [
            {
              "name": "email",
              "value": "={{ $json.email }}"
            },
            {
              "name": "api_key",
              "value": "{{HUNTER_API_KEY}}"
            }
          ]
        }
      },
      "typeVersion": 4
    },
    {
      "id": "0b103e51-5df5-4825-8e4d-28498fa127b2",
      "name": "🧠 Format Hunter Response",
      "type": "n8n-nodes-base.code",
      "position": [
        620,
        -20
      ],
      "parameters": {
        "jsCode": "// Process Hunter.io verification results\nconst originalData = $('🧹 Clean & Prepare Email Data').all();\nconst hunterResults = $('🕵️‍♂️ Hunter.io Email Verifier').all();\n\nconst results = [];\n\nfor (let i = 0; i < originalData.length; i++) {\n  const original = originalData[i].json;\n  const hunter = hunterResults[i]?.json?.data || {};\n\n  const result = {\n    email: original.email,\n    firstName: original.firstName,\n    lastName: original.lastName,\n    company: original.company,\n    rowIndex: original.originalRowIndex,\n\n    // Key Hunter.io verification fields\n    status: hunter.status || 'unknown',\n    score: hunter.score || 0,\n    smtpCheck: hunter.smtp_check || false,\n    acceptAll: hunter.accept_all || false,\n    sourceCount: Array.isArray(hunter.sources) ? hunter.sources.length : 0,\n\n    // Optional extras if needed later\n    regex: hunter.regex || false,\n    gibberish: hunter.gibberish || false,\n    disposable: hunter.disposable || false,\n    webmail: hunter.webmail || false,\n    mxRecords: hunter.mx_records || false,\n    smtpServer: hunter.smtp_server || false,\n    block: hunter.block || false,\n\n    // Human-friendly summary\n    statusSummary: `${hunter.status || 'unknown'} (${hunter.score || 0}% confidence)`\n  };\n\n  results.push({ json: result });\n}\n\nreturn results;"
      },
      "typeVersion": 2
    },
    {
      "id": "b999f5c9-9001-435d-bf33-05861d891b54",
      "name": "📤 Write Results to Output Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        840,
        -20
      ],
      "parameters": {
        "columns": {
          "value": {
            "email": "={{ $json.email }}",
            "score": "={{ $json.score }}",
            "status": "={{ $json.status }}",
            "company": "={{ $json.company }}",
            "lastName": "={{ $json.lastName }}",
            "firstName": "={{ $json.firstName }}",
            "smtp_check": "={{ $json.smtpCheck }}",
            "statusSummary": "={{ $json.statusSummary }}"
          },
          "schema": [
            {
              "id": "firstName",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "firstName",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "lastName",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "lastName",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "email",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "company",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "company",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "status",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "status",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "score",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "score",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "smtp_check",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "smtp_check",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "statusSummary",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "statusSummary",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "email"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/{{YOUR_OUTPUT_SHEET_ID}}/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "{{YOUR_OUTPUT_SHEET_ID}}",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/{{YOUR_OUTPUT_SHEET_ID}}edit?usp=drivesdk",
          "cachedResultName": "Email Verifier"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "LJQH5glroAM1SiFe",
          "name": "Google Sheets OAuth2"
        }
      },
      "typeVersion": 4
    },
    {
      "id": "d6d80276-4911-455a-b0c5-4196aec824e5",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -300,
        -320
      ],
      "parameters": {
        "width": 420,
        "height": 560,
        "content": "## Start & Input Source\n\n**Workflow begins manually using the trigger.\n\nEmails are read from the source Google Sheet (Sheet1).\n\nInput data is expected to include columns like Email, FirstName, LastName, and Company.**"
      },
      "typeVersion": 1
    },
    {
      "id": "3472c014-1657-4f35-92fd-b6b6775cbb6b",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        140,
        -320
      ],
      "parameters": {
        "width": 420,
        "height": 560,
        "content": "## Clean & Verify Emails\n\n**Filters out invalid or malformed emails.\n\nExtracts fields for each contact.\n\nSends each email to Hunter.io for verification using /v2/email-verifier.\n\nReturns rich metadata: status, score, smtp_check, disposable, etc.**"
      },
      "typeVersion": 1
    },
    {
      "id": "51f38886-34e7-418e-b655-4a2b0a3b8eb7",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        580,
        -320
      ],
      "parameters": {
        "width": 400,
        "height": 560,
        "content": "## Post-Processing & Output\n\n**Parses Hunter.io API results and maps them to a clean format.\n\nAdds a readable statusSummary (like: valid (96% confidence)).\n\nAppends or updates the output Google Sheet (Email Verifier → Sheet1) using email as the match key.**\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "9ce17c7f-221c-4268-8438-f95c99c39e09",
      "name": "⏯️ Triggers Everyday",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -260,
        -20
      ],
      "parameters": {
        "rule": {
          "interval": [
            {}
          ]
        }
      },
      "typeVersion": 1.2
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "16b71444-6e2f-4b01-8714-b5669a5c6d56",
  "connections": {
    "⏯️ Triggers Everyday": {
      "main": [
        [
          {
            "node": "📥 Fetch Emails from Source Sheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "🧠 Format Hunter Response": {
      "main": [
        [
          {
            "node": "📤 Write Results to Output Sheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "🧹 Clean & Prepare Email Data": {
      "main": [
        [
          {
            "node": "🕵️‍♂️ Hunter.io Email Verifier",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "📥 Fetch Emails from Source Sheet": {
      "main": [
        [
          {
            "node": "🧹 Clean & Prepare Email Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "🕵️‍♂️ Hunter.io Email Verifier": {
      "main": [
        [
          {
            "node": "🧠 Format Hunter Response",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}