Email List Hygiene Automation
Description
Categories
🤖 AI & Machine Learning
Nodes Used
n8n-nodes-base.ifn8n-nodes-base.coden8n-nodes-base.coden8n-nodes-base.coden8n-nodes-base.gmailn8n-nodes-base.mergen8n-nodes-base.stickyNoten8n-nodes-base.stickyNoten8n-nodes-base.stickyNoten8n-nodes-base.stickyNote
PriceGratis
Views0
Last Updated11/28/2025
workflow.json
{
"id": "ZquoLLFP4qaudR5P",
"meta": {
"instanceId": "",
"templateCredsSetupCompleted": true
},
"name": "Email List Hygiene Automation",
"tags": [],
"nodes": [
{
"id": "8a19fa06-c280-4907-a551-3b34ca0f993c",
"name": "Calculate Statistics",
"type": "n8n-nodes-base.code",
"position": [
656,
-320
],
"parameters": {
"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;"
},
"typeVersion": 2
},
{
"id": "d6827158-4e8d-4806-a04b-7f9f35384532",
"name": "Weekly Schedule (Friday 5PM)",
"type": "n8n-nodes-base.scheduleTrigger",
"position": [
0,
0
],
"parameters": {
"rule": {
"interval": [
{
"field": "cronExpression",
"expression": "0 17 * * 5"
}
]
}
},
"typeVersion": 1.2
},
{
"id": "0084d5ab-cdf7-4f5e-969e-711eee3bbcd7",
"name": "Read Email List",
"type": "n8n-nodes-base.googleSheets",
"position": [
208,
0
],
"parameters": {
"options": {},
"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"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "{{$credentials.googleSheetsOAuth2Api}}",
"name": "Google Sheets Account"
}
},
"typeVersion": 4.7
},
{
"id": "6d5bacd8-88cc-4979-a445-eba35f7b60f6",
"name": "Process Each Email",
"type": "n8n-nodes-base.splitInBatches",
"position": [
416,
0
],
"parameters": {
"options": {}
},
"typeVersion": 3
},
{
"id": "e2bf73b2-46f6-4661-8c74-5297b37318ad",
"name": "Validate Email Address",
"type": "n8n-nodes-verifiemail.verifiEmail",
"position": [
672,
-96
],
"parameters": {
"email": "={{ $json.email }}"
},
"credentials": {
"verifiEmailApi": {
"id": "{{$credentials.verifiEmailApi}}",
"name": "VerifiEmail Account"
}
},
"typeVersion": 1
},
{
"id": "63fdab5f-ce90-4700-a343-f5823f17a236",
"name": "Check Validation Result",
"type": "n8n-nodes-base.if",
"position": [
896,
-96
],
"parameters": {
"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": ""
}
]
}
},
"typeVersion": 2.2
},
{
"id": "e71190c0-fdc1-49ac-ae76-742dbea0782d",
"name": "Process Valid Email",
"type": "n8n-nodes-base.code",
"position": [
1248,
-240
],
"parameters": {
"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};"
},
"typeVersion": 2
},
{
"id": "08f26195-97eb-4cf0-989e-2b686c7e4bd7",
"name": "Process Invalid Email",
"type": "n8n-nodes-base.code",
"position": [
1232,
-48
],
"parameters": {
"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};"
},
"typeVersion": 2
},
{
"id": "5e98a36d-31c7-446b-a1ed-9988d3cb3ef3",
"name": "Update Valid Status",
"type": "n8n-nodes-base.googleSheets",
"position": [
1520,
-240
],
"parameters": {
"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": {},
"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"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "aFQXpCszpua9MQeO",
"name": "Google Sheets account - Akshita"
}
},
"typeVersion": 4.7
},
{
"id": "790a9728-2083-402d-8334-8254924c2e29",
"name": "Update Invalid Status",
"type": "n8n-nodes-base.googleSheets",
"position": [
1520,
-48
],
"parameters": {
"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": {},
"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"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "aFQXpCszpua9MQeO",
"name": "Google Sheets account - Akshita"
}
},
"typeVersion": 4.7
},
{
"id": "11a7ea29-2e24-4f9c-91dc-27afa0ca46f7",
"name": "Send Weekly Report",
"type": "n8n-nodes-base.gmail",
"position": [
928,
-320
],
"webhookId": "",
"parameters": {
"sendTo": "[email protected]",
"message": "=<!DOCTYPE html>\n<html>\n<head>\n <meta charset=\"UTF-8\">\n <style>\n body { \n font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Arial, sans-serif;\n line-height: 1.6; \n color: #333;\n margin: 0;\n padding: 0;\n background-color: #f5f5f5;\n }\n .container { \n max-width: 650px; \n margin: 20px auto; \n background: white;\n border-radius: 12px;\n overflow: hidden;\n box-shadow: 0 4px 6px rgba(0,0,0,0.1);\n }\n .header { \n background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);\n color: white; \n padding: 40px 30px; \n text-align: center;\n }\n .header h1 {\n margin: 0 0 10px 0;\n font-size: 28px;\n font-weight: 600;\n }\n .header p {\n margin: 0;\n opacity: 0.9;\n font-size: 16px;\n }\n .content { \n padding: 30px;\n }\n .health-score {\n background: {{ $json.health_color }};\n color: white;\n padding: 30px;\n border-radius: 12px;\n text-align: center;\n margin: 0 0 30px 0;\n box-shadow: 0 4px 12px rgba(0,0,0,0.15);\n }\n .health-score-label {\n font-size: 14px;\n text-transform: uppercase;\n letter-spacing: 1px;\n opacity: 0.9;\n margin-bottom: 10px;\n }\n .health-score-number {\n font-size: 56px;\n font-weight: bold;\n margin: 15px 0;\n line-height: 1;\n }\n .health-score-status {\n font-size: 18px;\n font-weight: 500;\n opacity: 0.95;\n }\n .stats-grid {\n display: grid;\n grid-template-columns: repeat(2, 1fr);\n gap: 15px;\n margin: 30px 0;\n }\n .stat-card {\n background: #f8f9fa;\n padding: 20px;\n border-radius: 10px;\n text-align: center;\n border: 2px solid #e9ecef;\n transition: transform 0.2s;\n }\n .stat-card:hover {\n transform: translateY(-2px);\n box-shadow: 0 4px 8px rgba(0,0,0,0.1);\n }\n .stat-label {\n color: #6c757d;\n font-size: 13px;\n text-transform: uppercase;\n letter-spacing: 0.5px;\n margin-bottom: 8px;\n font-weight: 600;\n }\n .stat-number {\n font-size: 32px;\n font-weight: bold;\n margin: 8px 0;\n line-height: 1;\n }\n .stat-percentage {\n color: #6c757d;\n font-size: 14px;\n }\n .valid { color: #28a745; }\n .invalid { color: #dc3545; }\n .risky { color: #fd7e14; }\n .action-section {\n background: #fff3cd;\n border-left: 4px solid #ffc107;\n padding: 20px;\n margin: 25px 0;\n border-radius: 8px;\n }\n .action-section h3 {\n margin: 0 0 15px 0;\n color: #856404;\n font-size: 18px;\n }\n .action-section ul {\n margin: 10px 0;\n padding-left: 20px;\n }\n .action-section li {\n margin: 8px 0;\n color: #856404;\n }\n .btn {\n display: inline-block;\n background: #667eea;\n color: white !important;\n padding: 14px 32px;\n text-decoration: none;\n border-radius: 8px;\n margin: 20px 0;\n font-weight: 600;\n font-size: 16px;\n box-shadow: 0 4px 12px rgba(102, 126, 234, 0.4);\n transition: all 0.3s;\n }\n .btn:hover {\n background: #5568d3;\n transform: translateY(-2px);\n box-shadow: 0 6px 16px rgba(102, 126, 234, 0.5);\n }\n .summary-box {\n background: white;\n padding: 20px;\n border-radius: 10px;\n margin: 20px 0;\n border: 2px solid #e9ecef;\n }\n .summary-box h3 {\n margin: 0 0 15px 0;\n font-size: 18px;\n color: #495057;\n }\n .summary-box pre {\n white-space: pre-wrap;\n font-family: 'SF Mono', 'Monaco', 'Courier New', monospace;\n font-size: 14px;\n line-height: 1.8;\n margin: 0;\n color: #495057;\n }\n .insights-box {\n background: linear-gradient(135deg, #e3f2fd 0%, #f3e5f5 100%);\n padding: 25px;\n border-radius: 10px;\n margin: 25px 0;\n }\n .insights-box h3 {\n margin: 0 0 15px 0;\n color: #1976d2;\n font-size: 18px;\n }\n .insight-item {\n margin: 12px 0;\n padding-left: 25px;\n position: relative;\n }\n .insight-item:before {\n content: '✓';\n position: absolute;\n left: 0;\n color: #1976d2;\n font-weight: bold;\n font-size: 18px;\n }\n .email-list {\n background: #f8f9fa;\n padding: 15px;\n border-radius: 8px;\n margin: 15px 0;\n max-height: 200px;\n overflow-y: auto;\n }\n .email-list h4 {\n margin: 0 0 10px 0;\n font-size: 14px;\n color: #495057;\n text-transform: uppercase;\n letter-spacing: 0.5px;\n }\n .email-item {\n padding: 8px 12px;\n margin: 5px 0;\n background: white;\n border-radius: 6px;\n font-size: 13px;\n border-left: 3px solid #dee2e6;\n }\n .email-item.invalid {\n border-left-color: #dc3545;\n }\n .email-reason {\n color: #6c757d;\n font-size: 12px;\n margin-top: 4px;\n }\n .footer {\n background: #f8f9fa;\n text-align: center;\n color: #6c757d;\n font-size: 13px;\n padding: 25px;\n border-top: 1px solid #dee2e6;\n }\n .footer p {\n margin: 5px 0;\n }\n @media only screen and (max-width: 600px) {\n .stats-grid {\n grid-template-columns: 1fr;\n }\n .container {\n margin: 10px;\n }\n .content {\n padding: 20px;\n }\n }\n </style>\n</head>\n<body>\n <div class=\"container\">\n <div class=\"header\">\n <h1>🧹 Email List Hygiene Report</h1>\n <p>Automated Weekly Validation Results</p>\n </div>\n \n <div class=\"content\">\n <!-- Health Score Section -->\n <div class=\"health-score\">\n <div class=\"health-score-label\">List Health Score</div>\n <div class=\"health-score-number\">{{ $json.health_score }}/100</div>\n <div class=\"health-score-status\">{{ $json.health_status }}</div>\n </div>\n\n <!-- Statistics Grid -->\n <div class=\"stats-grid\">\n <div class=\"stat-card\">\n <div class=\"stat-label\">Valid Emails</div>\n <div class=\"stat-number valid\">{{ $json.valid }}</div>\n <div class=\"stat-percentage\">{{ $json.valid_percentage }}%</div>\n </div>\n \n <div class=\"stat-card\">\n <div class=\"stat-label\">Invalid Emails</div>\n <div class=\"stat-number invalid\">{{ $json.invalid }}</div>\n <div class=\"stat-percentage\">{{ $json.invalid_percentage }}%</div>\n </div>\n \n <div class=\"stat-card\">\n <div class=\"stat-label\">Risky Emails</div>\n <div class=\"stat-number risky\">{{ $json.risky }}</div>\n <div class=\"stat-percentage\">{{ $json.risky_percentage }}%</div>\n </div>\n \n <div class=\"stat-card\">\n <div class=\"stat-label\">Total Processed</div>\n <div class=\"stat-number\">{{ $json.total }}</div>\n <div class=\"stat-percentage\">100%</div>\n </div>\n </div>\n\n <!-- Action Required Section -->\n <div class=\"action-section\">\n <h3>⚡ Action Required</h3>\n <ul>\n <li><strong>{{ $json.invalid }}</strong> invalid email(s) detected and flagged</li>\n <li><strong>{{ $json.risky }}</strong> risky email(s) found (disposable/catch-all)</li>\n <li>All email statuses have been automatically updated in your sheet</li>\n <li>Review flagged emails and consider removal from active campaigns</li>\n </ul>\n </div>\n\n <!-- Invalid Emails List -->\n {{ $json.invalid > 0 ? '<div class=\"email-list\"><h4>❌ Invalid Emails Detected</h4>' : '' }}\n {{ $json.email_details.invalid_emails.map(e => `<div class=\"email-item invalid\"><strong>${e.email}</strong><div class=\"email-reason\">${e.reason}</div></div>`).join('') }}\n {{ $json.invalid > 0 ? '</div>' : '' }}\n\n <!-- CTA Button -->\n <div style=\"text-align: center;\">\n <a href=\"{{ $json.sheet_url }}\" class=\"btn\">📊 View Full Report in Google Sheets</a>\n </div>\n\n <!-- Text Summary -->\n <div class=\"summary-box\">\n <h3>📋 Quick Summary</h3>\n <pre>{{ $json.summary }}</pre>\n </div>\n\n <!-- Insights Section -->\n <div class=\"insights-box\">\n <h3>💡 What This Means for Your Campaigns</h3>\n <div class=\"insight-item\">\n <strong>Improved Deliverability:</strong> Valid emails ensure better inbox placement and sender reputation\n </div>\n <div class=\"insight-item\">\n <strong>Cost Savings:</strong> Reduced bounces mean lower email service costs and fewer blacklist risks\n </div>\n <div class=\"insight-item\">\n <strong>Better Metrics:</strong> Clean lists lead to higher open rates, click rates, and engagement\n </div>\n <div class=\"insight-item\">\n <strong>Compliance Ready:</strong> Maintain GDPR/CAN-SPAM compliance with verified contacts\n </div>\n </div>\n </div>\n\n <!-- Footer -->\n <div class=\"footer\">\n <p>🤖 <strong>Automated by n8n Email Hygiene System</strong></p>\n <p>Report generated: {{ $json.processed_at }}</p>\n <p style=\"margin-top: 15px; opacity: 0.7;\">This report runs automatically every Friday at 5:00 PM</p>\n </div>\n </div>\n</body>\n</html>",
"options": {},
"subject": "=📧 Email Hygiene Report - {{ $now.format(\"MMM DD, YYYY\") }}"
},
"credentials": {
"gmailOAuth2": {
"id": "{{$credentials.gmailOAuth2}}",
"name": "Gmail Account"
}
},
"typeVersion": 2.1
},
{
"id": "d30f5ad6-778c-4891-90fc-c58fa49612e9",
"name": "Merge1",
"type": "n8n-nodes-base.merge",
"position": [
1776,
-64
],
"parameters": {},
"typeVersion": 3.2
},
{
"id": "f48744cc-7449-43fa-8fbd-dfda525f22b8",
"name": "Sticky Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
-480,
-560
],
"parameters": {
"color": 7,
"width": 336,
"height": 576,
"content": "## EMAIL LIST HYGIENE AUTOMATION\n\nAutomatically validates your email list **every Friday at 5 PM**.\n\n✅ What it does:\n- **Reads emails** from **Google Sheets**\n- Validates each email (format, MX records, deliverability)\n- Categorizes as **Valid**, **Invalid**, or **Risky**\n- Updates sheet with **validation status** and **timestamp**\n- Generates **health score** and **statistics**\n- Sends **professional HTML report** via **email**\n\n📈 Benefits:\n- Improved **deliverability**(reduce 15%+ bounce rates to <2%)\n- Better sender reputation with **ISPs**\n- Higher campaign **ROI and engagement**\n- **GDPR/CAN-SPAM compliance** ready\n\n⏱️ Runs: **Every Friday at 5:00 PM**\n📧 Processes: **Unlimited emails**(loops through all)"
},
"typeVersion": 1
},
{
"id": "071eceb8-e834-45ee-926e-cbab44953cfd",
"name": "Sticky Note1",
"type": "n8n-nodes-base.stickyNote",
"position": [
-480,
176
],
"parameters": {
"color": 7,
"width": 320,
"height": 736,
"content": "## SETUP REQUIRED (5 minutes)\n\nBefore activating this workflow:\n\n1. **Google Sheets** Setup:\n Create sheet with these exact columns:\n • row_number (auto-generated by Sheets)\n • name\n • email\n • status **(leave blank - auto-filled)**\n • checked_at **(leave blank - auto-filled)**\n • notes **(leave blank - auto-filled)**\n\n2. Credentials Needed:\n • Google Sheets OAuth2\n • Email validation API **(VerifiEmail)** at **https://verifi.email**\n • Gmail OAuth2 (for sending reports)\n\n3. Configure Email Recipient:\n • Open **\"Send Email Report\"** node\n • Change **\"sendTo\"** from **\"[email protected]\"**\n • Use: **[email protected]** or **[email protected]**\n • Optional: Add CC/BCC for multiple recipients\n\n4. Test Before Activating:\n • Add **3-5 test emails** to your **sheet**\n • Click \"Execute Workflow\" button\n • Verify sheet updates correctly\n • Check **email report arrives**\n • Then toggle **\"Active\"** switch"
},
"typeVersion": 1
},
{
"id": "2517b984-ed20-4930-85b1-33d303e0b714",
"name": "Sticky Note2",
"type": "n8n-nodes-base.stickyNote",
"position": [
368,
160
],
"parameters": {
"color": 7,
"width": 384,
"height": 608,
"content": "## EMAIL VALIDATION PROCESS\n\nProcesses each email **individually** in a **loop**:\n\n**STEP 1**: Loop starts\n → Takes one email from sheet\n\n**STEP 2**: Validation checks\n ✓ Email format (RFC 5322 compliance)\n ✓ Domain has mail server (MX records)\n ✓ Mailbox exists (SMTP verification)\n ✓ Not disposable email service\n ✓ Not catch-all domain\n\n**STEP 3**: Categorization\n ✅ VALID - Passes all checks, safe to use\n ❌ INVALID - Failed checks, remove from list\n ⚠️ RISKY - Disposable or catch-all, review\n\n**STEP 4**: Update sheet\n • Writes status to row\n • Adds timestamp\n • Includes reason/notes\n\n**STEP 5**: Loop continues\n → Processes next email\n → Repeats until all done\n\n**Speed**: ~1-2 emails per second\nHandles: **1000+ emails ** automatically"
},
"typeVersion": 1
},
{
"id": "7c8c9273-b54f-4a3f-8131-7031cf23c806",
"name": "Sticky Note3",
"type": "n8n-nodes-base.stickyNote",
"position": [
992,
160
],
"parameters": {
"color": 7,
"width": 368,
"height": 688,
"content": "## TRUE/FALSE BRANCHING\n\n**IF node splits workflow** based on **validation**:\n\n✅ TRUE BRANCH (Valid Emails):\n Condition: validation.valid === true\n ↓\n Process Valid Email code:\n • Status: \"valid\"\n • Note: **\"Email verified successfully\"**\n • Check for disposable flag\n ↓\n Update Valid Status:\n • Updates Google Sheet row\n • Writes status, timestamp, notes\n ↓\n **Merge Input 1**:\n • Collects valid email data\n\n❌ FALSE BRANCH **(Invalid Emails)**:\n Condition: validation.valid === false\n ↓\n Process Invalid Email code:\n • Status: \"invalid\"\n • Note: Specific reason (no MX, bad format, etc.)\n ↓\n Update Invalid Status:\n • Updates Google Sheet row\n • Writes status, timestamp, notes\n ↓\n **Merge Input 2**:\n • Collects invalid email data\n\n🔗 Merge combines both branches → loops back"
},
"typeVersion": 1
},
{
"id": "f30a760a-0560-443c-a352-4ae7e16733a8",
"name": "Sticky Note4",
"type": "n8n-nodes-base.stickyNote",
"position": [
1984,
-112
],
"parameters": {
"color": 7,
"width": 368,
"height": 576,
"content": "## MERGE & LOOP COMPLETION\n\nThe **Merge node** is critical for data flow:\n\n**INPUT 1**: Valid emails from **TRUE** branch\n**INPUT 2**: Invalid emails from **FALSE** branch\n\nWhat Merge does:\n- Combines both data streams\n- Creates single unified output\n- Feeds back to **Loop Over Items**\n- Loop accumulates all processed emails internally\n\nLoop Behavior:\n- Continues processing next email\n- Repeats **validation → branch → merge → loop**\n- Tracks all items internally\n- When complete, \"done\" output fires\n\n**DONE** Output:\n- Triggers only after ALL emails processed\n- Sends accumulated data to Calculate Statistics\n- Contains complete results from all iterations\n\nThis ensures every email is validated and collected\nbefore generating the final report."
},
"typeVersion": 1
},
{
"id": "dc616d16-1e8d-49e7-9ef8-0bc7234a7ea0",
"name": "Sticky Note5",
"type": "n8n-nodes-base.stickyNote",
"position": [
656,
-1056
],
"parameters": {
"color": 7,
"width": 384,
"height": 720,
"content": "## ANALYTICS & REPORTING\n\nAfter all emails processed:\n\n1️⃣ **Calculate Statistics**:\n Receives all accumulated emails from loop\n \n **Calculates**:\n • **Total emails** processed\n • **Valid count** & **percentage** \n • **Invalid count** & **percentage** \n • **Risky count** & **percentage**\n \n **Health Score Formula** (0-100):\n = **(Valid% × 100) - (Invalid% × 20) - (Risky% × 10)**\n \n **Scoring**:\n • **80-100: Excellent ✅ (Green)**\n • **60-79: Good ⚠️ (Orange)**\n • **0-59: Needs Attention ❌ (Red)**\n \n **Creates**:\n • Summary text\n • Email lists by category\n • Invalid emails with reasons\n\n2️⃣ **Send Email Report**:\n Beautiful **HTML email** with:\n • **Color-coded health score badge**\n • **4 stat cards (valid/invalid/risky/total)**\n • **Action items list**\n • **Invalid emails with detailed reasons**\n • **Direct link to Google Sheet**\n • Campaign impact insights\n • Professional branding\n\n**Sent to**: Configurable recipients\n📅 Frequency: **Every Friday at 5:00 PM**"
},
"typeVersion": 1
},
{
"id": "dc73ce02-5bfb-4d98-9f18-3d8753f5acf1",
"name": "Sticky Note6",
"type": "n8n-nodes-base.stickyNote",
"position": [
1568,
-1152
],
"parameters": {
"color": 7,
"width": 384,
"height": 832,
"content": "## CUSTOMIZATION OPTIONS\n\nEasy modifications:\n\n⏰ Change Schedule:\n • **Edit \"Weekly Schedule\" node**\n • Current: 0 17 * * 5 (Friday 5PM)\n • Examples:\n - Daily 9AM: 0 9 * * *\n - Every Monday: 0 17 * * 1\n - First of month: 0 9 1 * *\n\n📧 Multiple Recipients:\n • \"Send Email Report\" node → Add options\n\n • Comma-separate: [email protected],[email protected]\n\n📊 Add Slack Notifications:\n • Add Slack node after email\n • Channel: #marketing\n • Quick text summary for instant alerts\n\n🗃️ Archive Invalid Emails:\n • Add Google Sheets node on FALSE branch\n • Operation: Append\n • Sheet: Create \"Invalid_Archive\" tab\n • Keeps historical record\n\n📈 Export to CRM:\n • Add HTTP Request/Webhook node\n • Push validated emails to HubSpot/Salesforce\n • Keep CRM automatically synced\n\n⚡ Rate Limiting (if needed):\n • Add \"Wait\" node after validation\n • Wait: 1-2 seconds\n • Prevents API throttling for large lists\n\n🎨 Customize Email Design:\n • Edit HTML in \"Send Email Report\"\n • Change colors, fonts, branding\n • Add company logo"
},
"typeVersion": 1
},
{
"id": "2816c5ba-112d-4073-b56c-5083ba104b4e",
"name": "Sticky Note7",
"type": "n8n-nodes-base.stickyNote",
"position": [
1936,
688
],
"parameters": {
"color": 7,
"width": 464,
"height": 896,
"content": "## TROUBLESHOOTING GUIDE\n\n**Common issues & solutions**:\n\n❌ \"Column not found\" error:\n **Fix**: Check **Google Sheet** has exact column names\n • Names are **case-sensitive**\n • Required: row_number, name, email, status, checked_at, notes\n\n❌ **Loop processes only 1 email**:\n **Fix**: Check Google Sheets node returns multiple rows\n • Verify \"Range\" field is empty or set to A:F\n • Check \"Use Header Row\" is enabled\n\n❌ **Statistics show wrong counts**:\n **Fix**: Enable \"Execute Once\" in Calculate Statistics\n • Click gear icon on node\n • Toggle \"Execute Once\" to ON\n • This processes all items together, not one-by-one\n\n❌ **Email doesn't arrive**:\n **Fix**: Check several things\n • Gmail spam/promotions folder\n • Gmail credential is authorized\n • Recipient email is correct\n • Try different recipient to test\n\n❌ **Validation API errors**:\n **Fix**: Verify API access\n • Check API key is valid\n • Verify quota not exceeded\n • Test with known email: [email protected]\n\n❌ **Merge node shows \"missing input\"**:\n **Fix**: Both branches must connect to Merge\n • TRUE branch → Merge Input 1\n • FALSE branch → Merge Input 2\n • Both Update nodes must complete\n\n💡 Debug Tips:\n • Click individual nodes → \"Execute Node\"\n • Check OUTPUT panel for each step\n • Use console.log() in code nodes\n • Test with 3-5 emails before full list\n • Check execution log (bottom panel) for errors"
},
"typeVersion": 1
}
],
"active": false,
"pinData": {},
"settings": {
"executionOrder": "v1"
},
"versionId": "b747883f-8107-45ba-be73-ac39e8076e88",
"connections": {
"Merge1": {
"main": [
[
{
"node": "Process Each Email",
"type": "main",
"index": 0
}
]
]
},
"Read Email List": {
"main": [
[
{
"node": "Process Each Email",
"type": "main",
"index": 0
}
]
]
},
"Process Each Email": {
"main": [
[
{
"node": "Calculate Statistics",
"type": "main",
"index": 0
}
],
[
{
"node": "Validate Email Address",
"type": "main",
"index": 0
}
]
]
},
"Process Valid Email": {
"main": [
[
{
"node": "Update Valid Status",
"type": "main",
"index": 0
}
]
]
},
"Update Valid Status": {
"main": [
[
{
"node": "Merge1",
"type": "main",
"index": 0
}
]
]
},
"Calculate Statistics": {
"main": [
[
{
"node": "Send Weekly Report",
"type": "main",
"index": 0
}
]
]
},
"Process Invalid Email": {
"main": [
[
{
"node": "Update Invalid Status",
"type": "main",
"index": 0
}
]
]
},
"Update Invalid Status": {
"main": [
[
{
"node": "Merge1",
"type": "main",
"index": 1
}
]
]
},
"Validate Email Address": {
"main": [
[
{
"node": "Check Validation Result",
"type": "main",
"index": 0
}
]
]
},
"Check Validation Result": {
"main": [
[
{
"node": "Process Valid Email",
"type": "main",
"index": 0
}
],
[
{
"node": "Process Invalid Email",
"type": "main",
"index": 0
}
]
]
},
"Weekly Schedule (Friday 5PM)": {
"main": [
[
{
"node": "Read Email List",
"type": "main",
"index": 0
}
]
]
}
}
}