{ "edges": [ { "to": "6d5bacd8-88cc-4979-a445-eba35f7b60f6", "from": "d30f5ad6-778c-4891-90fc-c58fa49612e9", "toPort": "0", "fromPort": "0" }, { "to": "6d5bacd8-88cc-4979-a445-eba35f7b60f6", "from": "0084d5ab-cdf7-4f5e-969e-711eee3bbcd7", "toPort": "0", "fromPort": "0" }, { "to": "8a19fa06-c280-4907-a551-3b34ca0f993c", "from": "6d5bacd8-88cc-4979-a445-eba35f7b60f6", "toPort": "0", "fromPort": "0" }, { "to": "e2bf73b2-46f6-4661-8c74-5297b37318ad", "from": "6d5bacd8-88cc-4979-a445-eba35f7b60f6", "toPort": "0", "fromPort": "1" }, { "to": "5e98a36d-31c7-446b-a1ed-9988d3cb3ef3", "from": "e71190c0-fdc1-49ac-ae76-742dbea0782d", "toPort": "0", "fromPort": "0" }, { "to": "d30f5ad6-778c-4891-90fc-c58fa49612e9", "from": "5e98a36d-31c7-446b-a1ed-9988d3cb3ef3", "toPort": "0", "fromPort": "0" }, { "to": "11a7ea29-2e24-4f9c-91dc-27afa0ca46f7", "from": "8a19fa06-c280-4907-a551-3b34ca0f993c", "toPort": "0", "fromPort": "0" }, { "to": "790a9728-2083-402d-8334-8254924c2e29", "from": "08f26195-97eb-4cf0-989e-2b686c7e4bd7", "toPort": "0", "fromPort": "0" }, { "to": "d30f5ad6-778c-4891-90fc-c58fa49612e9", "from": "790a9728-2083-402d-8334-8254924c2e29", "toPort": "1", "fromPort": "0" }, { "to": "63fdab5f-ce90-4700-a343-f5823f17a236", "from": "e2bf73b2-46f6-4661-8c74-5297b37318ad", "toPort": "0", "fromPort": "0" }, { "to": "e71190c0-fdc1-49ac-ae76-742dbea0782d", "from": "63fdab5f-ce90-4700-a343-f5823f17a236", "toPort": "0", "fromPort": "0" }, { "to": "08f26195-97eb-4cf0-989e-2b686c7e4bd7", "from": "63fdab5f-ce90-4700-a343-f5823f17a236", "toPort": "0", "fromPort": "1" }, { "to": "0084d5ab-cdf7-4f5e-969e-711eee3bbcd7", "from": "d6827158-4e8d-4806-a04b-7f9f35384532", "toPort": "0", "fromPort": "0" } ], "nodes": [ { "id": "8a19fa06-c280-4907-a551-3b34ca0f993c", "name": "Calculate Statistics", "type": "code", "params": { "jsCode": "// Get all processed items from the loop\nconst items = $input.all();\n\n// Log for debugging\nconsole.log(`Processing ${items.length} items for statistics`);\n\n// Initialize counters\nconst stats = {\n total: items.length,\n valid: 0,\n invalid: 0,\n risky: 0,\n unknown: 0,\n processed_at: new Date().toLocaleString('en-IN', {\n dateStyle: 'full',\n timeStyle: 'short'\n }),\n sheet_url: $('Read Email List').params?.documentId?.__rl?.cachedResultUrl || 'YOUR_GOOGLE_SHEET_URL'\n};\n\n// Count each category\nitems.forEach(item => {\n const status = item.json.status;\n \n if (status === 'valid') {\n stats.valid++;\n } else if (status === 'invalid') {\n stats.invalid++;\n } else if (status === 'risky') {\n stats.risky++;\n } else {\n stats.unknown++;\n }\n});\n\n// Calculate percentages (handle division by zero)\nif (stats.total > 0) {\n stats.valid_percentage = ((stats.valid / stats.total) * 100).toFixed(1);\n stats.invalid_percentage = ((stats.invalid / stats.total) * 100).toFixed(1);\n stats.risky_percentage = ((stats.risky / stats.total) * 100).toFixed(1);\n stats.unknown_percentage = ((stats.unknown / stats.total) * 100).toFixed(1);\n} else {\n stats.valid_percentage = '0.0';\n stats.invalid_percentage = '0.0';\n stats.risky_percentage = '0.0';\n stats.unknown_percentage = '0.0';\n}\n\n// Calculate list health score (0-100)\n// Formula: \n// - Valid emails: +100 points per email (percentage of total)\n// - Invalid emails: -20 points per email (penalty)\n// - Risky emails: -10 points per email (smaller penalty)\nstats.health_score = Math.max(0, Math.min(100, Math.round(\n (stats.valid / stats.total) * 100 - \n (stats.invalid / stats.total) * 20 - \n (stats.risky / stats.total) * 10\n)));\n\n// Determine health status\nif (stats.health_score >= 80) {\n stats.health_status = 'Excellent ✅';\n stats.health_color = '#4CAF50';\n} else if (stats.health_score >= 60) {\n stats.health_status = 'Good ⚠️';\n stats.health_color = '#ff9800';\n} else {\n stats.health_status = 'Needs Attention ❌';\n stats.health_color = '#f44336';\n}\n\n// Create summary text for notifications\nstats.summary = `📊 Email List Validation Complete\n\n✅ Valid: ${stats.valid} (${stats.valid_percentage}%)\n❌ Invalid: ${stats.invalid} (${stats.invalid_percentage}%)\n⚠️ Risky: ${stats.risky} (${stats.risky_percentage}%)\n❓ Unknown: ${stats.unknown} (${stats.unknown_percentage}%)\n\n📈 List Health Score: ${stats.health_score}/100 - ${stats.health_status}\n📧 Total Processed: ${stats.total} emails\n🕐 Completed: ${stats.processed_at}`;\n\n// Create detailed lists for the report\nstats.email_details = {\n valid_emails: items\n .filter(item => item.json.status === 'valid')\n .map(item => item.json.email),\n \n invalid_emails: items\n .filter(item => item.json.status === 'invalid')\n .map(item => ({\n email: item.json.email,\n reason: item.json.notes\n })),\n \n risky_emails: items\n .filter(item => item.json.status === 'risky')\n .map(item => ({\n email: item.json.email,\n reason: item.json.notes\n }))\n};\n\n// Log summary for debugging\nconsole.log(stats.summary);\n\n// Return the complete statistics object\nreturn stats;" }, "position": [ 656, -320 ] }, { "id": "d6827158-4e8d-4806-a04b-7f9f35384532", "name": "Weekly Schedule (Friday 5PM)", "type": "trigger", "params": { "kind": "n8n-nodes-base.scheduleTrigger", "rule": { "interval": [ { "field": "cronExpression", "expression": "0 17 * * 5" } ] } }, "position": [ 0, 0 ] }, { "id": "0084d5ab-cdf7-4f5e-969e-711eee3bbcd7", "name": "Read Email List", "type": "integration", "params": { "options": {}, "service": "googleSheets", "operation": "read", "sheetName": { "__rl": true, "mode": "list", "value": "gid=0", "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1HjIuSDqm2e8k0TUZuFqOdm4MZ4vzvnLtKRSFsiKVtYk/edit#gid=0", "cachedResultName": "Sheet1" }, "documentId": { "__rl": true, "mode": "list" } }, "position": [ 208, 0 ] }, { "id": "6d5bacd8-88cc-4979-a445-eba35f7b60f6", "name": "Process Each Email", "type": "passthrough", "params": { "mode": "noOp", "options": {} }, "position": [ 416, 0 ] }, { "id": "e2bf73b2-46f6-4661-8c74-5297b37318ad", "name": "Validate Email Address", "type": "integration", "params": { "email": "={{ $json.email }}", "service": "verifiemail", "operation": "run", "__originalType": "n8n-nodes-verifiemail.verifiEmail" }, "position": [ 672, -96 ] }, { "id": "63fdab5f-ce90-4700-a343-f5823f17a236", "name": "Check Validation Result", "type": "if", "params": { "options": {}, "conditions": { "options": { "version": 2, "leftValue": "", "caseSensitive": true, "typeValidation": "strict" }, "combinator": "and", "conditions": [ { "id": "a3f7a8d6-9219-44b7-be4b-0f8d5d5aa5c5", "operator": { "type": "boolean", "operation": "true", "singleValue": true }, "leftValue": "={{ $json.valid }}", "rightValue": "" } ] } }, "position": [ 896, -96 ] }, { "id": "e71190c0-fdc1-49ac-ae76-742dbea0782d", "name": "Process Valid Email", "type": "code", "params": { "jsCode": "// Get the validation result from current input\nconst validation = $input.first().json;\n\n// Get the ORIGINAL data from Loop Over Items node\nconst loopData = $('Process Each Email').item.json;\n\n// Process valid email\nlet status = 'valid';\nlet notes = 'Email verified successfully';\n\n// Check for any warnings even if valid\nif (validation.details?.disposable === true) {\n status = 'risky';\n notes = 'Valid but disposable email address';\n}\n\nconst checkedAt = new Date().toLocaleString('en-IN', { \n year: 'numeric',\n month: '2-digit',\n day: '2-digit',\n hour: '2-digit',\n minute: '2-digit',\n hour12: true\n});\n\n// Return merged data - combining Loop data + validation results\nreturn {\n row_number: loopData.row_number,\n name: loopData.name,\n email: loopData.email,\n status: status,\n checked_at: checkedAt,\n notes: notes,\n is_valid: true\n};" }, "position": [ 1248, -240 ] }, { "id": "08f26195-97eb-4cf0-989e-2b686c7e4bd7", "name": "Process Invalid Email", "type": "code", "params": { "jsCode": "// Get the validation result\nconst validation = $input.first().json;\n\n// Get original data from Loop Over Items\nconst loopData = $('Process Each Email').item.json;\n\n// Process invalid email\nlet status = 'invalid';\nlet notes = 'Email validation failed';\n\n// Get more specific reason from validation details\nif (validation.details) {\n if (validation.details.validMxRecord === false) {\n notes = 'Invalid - No mail server found';\n } else if (validation.details.rfcCompliant === false) {\n notes = 'Invalid - Incorrect email format';\n } else if (validation.details.spoofFree === false) {\n notes = 'Invalid - Possible spoof/fake email';\n }\n}\n\nconst checkedAt = new Date().toLocaleString('en-IN', { \n year: 'numeric',\n month: '2-digit',\n day: '2-digit',\n hour: '2-digit',\n minute: '2-digit',\n hour12: true\n});\n\n// Return merged data\nreturn {\n row_number: loopData.row_number,\n name: loopData.name,\n email: loopData.email,\n status: status,\n checked_at: checkedAt,\n notes: notes,\n is_valid: false\n};" }, "position": [ 1232, -48 ] }, { "id": "5e98a36d-31c7-446b-a1ed-9988d3cb3ef3", "name": "Update Valid Status", "type": "integration", "params": { "columns": { "value": { "name": "={{ $json.name }}", "email": "={{ $json.email }}", "notes": "={{ $json.notes }}", "status": "={{ $json.status }}", "checked_at": "={{ $json.checked_at }}", "row_number": "={{ $json.row_number }}" }, "schema": [ { "id": "name", "type": "string", "display": true, "required": false, "displayName": "name", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "email", "type": "string", "display": true, "required": false, "displayName": "email", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "status", "type": "string", "display": true, "required": false, "displayName": "status", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "checked_at", "type": "string", "display": true, "required": false, "displayName": "checked_at", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "notes", "type": "string", "display": true, "required": false, "displayName": "notes", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "row_number", "type": "number", "display": true, "removed": false, "readOnly": true, "required": false, "displayName": "row_number", "defaultMatch": false, "canBeUsedToMatch": true } ], "mappingMode": "defineBelow", "matchingColumns": [ "row_number" ], "attemptToConvertTypes": false, "convertFieldsToString": false }, "options": {}, "service": "googleSheets", "operation": "update", "sheetName": { "__rl": true, "mode": "list", "value": "gid=0", "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1HjIuSDqm2e8k0TUZuFqOdm4MZ4vzvnLtKRSFsiKVtYk/edit#gid=0", "cachedResultName": "Sheet1" }, "documentId": { "__rl": true, "mode": "list" } }, "position": [ 1520, -240 ] }, { "id": "790a9728-2083-402d-8334-8254924c2e29", "name": "Update Invalid Status", "type": "integration", "params": { "columns": { "value": { "name": "={{ $json.name }}", "email": "={{ $json.email }}", "notes": "={{ $json.notes }}", "status": "={{ $json.status }}", "checked_at": "={{ $json.checked_at }}", "row_number": "={{ $json.row_number }}" }, "schema": [ { "id": "name", "type": "string", "display": true, "required": false, "displayName": "name", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "email", "type": "string", "display": true, "required": false, "displayName": "email", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "status", "type": "string", "display": true, "required": false, "displayName": "status", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "checked_at", "type": "string", "display": true, "required": false, "displayName": "checked_at", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "notes", "type": "string", "display": true, "required": false, "displayName": "notes", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "row_number", "type": "number", "display": true, "removed": false, "readOnly": true, "required": false, "displayName": "row_number", "defaultMatch": false, "canBeUsedToMatch": true } ], "mappingMode": "defineBelow", "matchingColumns": [ "row_number" ], "attemptToConvertTypes": false, "convertFieldsToString": false }, "options": {}, "service": "googleSheets", "operation": "update", "sheetName": { "__rl": true, "mode": "list", "value": "gid=0", "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1HjIuSDqm2e8k0TUZuFqOdm4MZ4vzvnLtKRSFsiKVtYk/edit#gid=0", "cachedResultName": "Sheet1" }, "documentId": { "__rl": true, "mode": "list" } }, "position": [ 1520, -48 ] }, { "id": "11a7ea29-2e24-4f9c-91dc-27afa0ca46f7", "name": "Send Weekly Report", "type": "integration", "params": { "sendTo": "user@example.com", "message": "=\n\n\n \n \n\n\n

\n \n \n

\n \n

List Health Score

{{ $json.health_score }}/100

\n\n \n

Valid Emails

{{ $json.valid_percentage }}%

\n \n

Invalid Emails

{{ $json.invalid_percentage }}%

\n \n

Risky Emails

{{ $json.risky_percentage }}%

\n \n

Total Processed

100%

\n\n \n

⚡ Action Required

{{ $json.invalid }} invalid email(s) detected and flagged
{{ $json.risky }} risky email(s) found (disposable/catch-all)
All email statuses have been automatically updated in your sheet
Review flagged emails and consider removal from active campaigns

\n\n \n {{ $json.invalid > 0 ? '

❌ Invalid Emails Detected

' : '' }}\n {{ $json.email_details.invalid_emails.map(e => `

${e.email}

${e.reason}

`).join('') }}\n {{ $json.invalid > 0 ? '

' : '' }}\n\n \n

\n 📊 View Full Report in Google Sheets\n

\n\n \n

📋 Quick Summary

{{ $json.summary }}

\n\n \n

💡 What This Means for Your Campaigns

\n Improved Deliverability: Valid emails ensure better inbox placement and sender reputation\n

\n Cost Savings: Reduced bounces mean lower email service costs and fewer blacklist risks\n

\n Better Metrics: Clean lists lead to higher open rates, click rates, and engagement\n

\n Compliance Ready: Maintain GDPR/CAN-SPAM compliance with verified contacts\n

\n\n \n \n

\n\n", "options": {}, "service": "gmail", "subject": "=📧 Email Hygiene Report - {{ $now.format(\"MMM DD, YYYY\") }}", "operation": "read" }, "position": [ 928, -320 ] }, { "id": "d30f5ad6-778c-4891-90fc-c58fa49612e9", "name": "Merge1", "type": "merge", "params": {}, "position": [ 1776, -64 ] } ] }

🧹 Email List Hygiene Report

⚡ Action Required

❌ Invalid Emails Detected

📋 Quick Summary

💡 What This Means for Your Campaigns

Validate email lists weekly with Google Sheets, VerifiEmail and Gmail reports