Rückläufer- und ungültige Detektion (Gmail-Trigger)

• •Google-Konto + Gmail API-Anmeldedaten
• •Slack Bot Token oder Webhook URL
• •Google Sheets API-Anmeldedaten

{
  "id": "BwfB2WsAGCX5rTFB",
  "meta": {
    "instanceId": "8443f10082278c46aa5cf3acf8ff0f70061a2c58bce76efac814b16290845177",
    "templateCredsSetupCompleted": true
  },
  "name": "Bounce & Invalid Detection (Gmail Trigger)",
  "tags": [],
  "nodes": [
    {
      "id": "bff28463-72c1-415a-aac0-8d19808addcf",
      "name": "Bei Klick auf 'Workflow ausführen'",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        384,
        1104
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "3b5dabd0-e0ad-4b27-86d3-fc326edc2842",
      "name": "Retouradressen analysieren",
      "type": "n8n-nodes-base.function",
      "position": [
        1184,
        1120
      ],
      "parameters": {
        "functionCode": "// Input: items array (from Gmail node output)\n// Output: one item per failed email address\n\nreturn items\n  .map(item => {\n    const snippet = item.json.snippet || \"\";\n    const match = snippet.match(/wasn't delivered to\\s+([\\w.-]+@[\\w.-]+\\.\\w+)/i);\n    if (match) {\n      return {\n        json: {\n          failedEmail: match[1],\n          subject: item.json.Subject || \"\",\n          id: item.json.id,\n          snippet: snippet\n        }\n      };\n    }\n    return null;\n  })\n  .filter(item => item !== null);\n"
      },
      "typeVersion": 1
    },
    {
      "id": "bde866db-a655-4cb3-bba1-81dbda49e09c",
      "name": "Alle E-Mail-Kontakte abrufen",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        736,
        1296
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1-4TaBE0cOTO1iwrrah5y822s8KXrWWkxFmJYiGCfWx4/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1-4TaBE0cOTO1iwrrah5y822s8KXrWWkxFmJYiGCfWx4",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1-4TaBE0cOTO1iwrrah5y822s8KXrWWkxFmJYiGCfWx4/edit?usp=drivesdk",
          "cachedResultName": "Fraud Email"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "s4dP1fNuVZ2gWvs3",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "ac79fbd2-381e-4de2-b5e2-e7502752978d",
      "name": "Status zurück in Sheet schreiben",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1904,
        1232
      ],
      "parameters": {
        "options": {},
        "fieldsUi": {
          "values": [
            {
              "column": "Status",
              "fieldValue": "={{ $json.Status }}"
            },
            {
              "column": "Last Updated",
              "fieldValue": "={{ $json[\"Last Updated\"] }}"
            }
          ]
        },
        "operation": "update",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1-4TaBE0cOTO1iwrrah5y822s8KXrWWkxFmJYiGCfWx4/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1-4TaBE0cOTO1iwrrah5y822s8KXrWWkxFmJYiGCfWx4",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1-4TaBE0cOTO1iwrrah5y822s8KXrWWkxFmJYiGCfWx4/edit?usp=drivesdk",
          "cachedResultName": "Fraud Email"
        },
        "valueToMatchOn": "={{ $json.Name }}",
        "columnToMatchOn": "Name"
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "s4dP1fNuVZ2gWvs3",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "7f7c1971-5591-4f6c-a3dd-06c3e3e20bb6",
      "name": "Täglicher 19 Uhr Report-Trigger",
      "type": "n8n-nodes-base.cron",
      "position": [
        304,
        1904
      ],
      "parameters": {
        "triggerTimes": {
          "item": [
            {
              "hour": 19
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "36ffd609-d7c2-461f-82c1-c7303f3e5f46",
      "name": "Aktualisierte Sheet-Daten lesen",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        608,
        1904
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1-4TaBE0cOTO1iwrrah5y822s8KXrWWkxFmJYiGCfWx4/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1-4TaBE0cOTO1iwrrah5y822s8KXrWWkxFmJYiGCfWx4",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1-4TaBE0cOTO1iwrrah5y822s8KXrWWkxFmJYiGCfWx4/edit?usp=drivesdk",
          "cachedResultName": "Fraud Email"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "s4dP1fNuVZ2gWvs3",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "4ad00c44-5e60-452a-b4ab-89cc23b38c7c",
      "name": "Slack Tageszusammenfassung senden",
      "type": "n8n-nodes-base.slack",
      "position": [
        1120,
        1904
      ],
      "parameters": {
        "text": "=📢 *Daily Bounce Cleanup Report*  \n📧 Invalid Marked: {{$json[\"Invalid emails\"]}}  \n📭 No Action Marked: {{$json[\"No activity\"]}}  \n✅ Keep your lists healthy 💪\n",
        "channel": "#email-cleanup",
        "attachments": [],
        "otherOptions": {}
      },
      "credentials": {
        "slackApi": {
          "id": "rNqvWj9TfChPVRYY",
          "name": "Slack account vivek"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "c3984c6c-4861-46fe-8b6b-68bf842f3fa4",
      "name": "Bounce-Benachrichtigungen abrufen",
      "type": "n8n-nodes-base.gmail",
      "position": [
        672,
        1120
      ],
      "webhookId": "c4ef7681-fe33-4c55-952d-db9916b6ee6e",
      "parameters": {
        "filters": {
          "sender": "mailer-daemon@googlemail.com"
        },
        "operation": "getAll",
        "returnAll": true
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "RchiXdmY8WaQhOSJ",
          "name": "Gmail account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "ca28e3bf-d30f-4e54-affc-5b36be6b928e",
      "name": "Letzte 5 Bounces abrufen",
      "type": "n8n-nodes-base.code",
      "position": [
        880,
        1120
      ],
      "parameters": {
        "jsCode": "// Get all Gmail messages\nconst emails = items.map(item => item.json);\n\n// Sort by internalDate (descending → newest first)\nemails.sort((a, b) => Number(b.internalDate) - Number(a.internalDate));\n\n// Slice the top 5 messages\nconst latestFive = emails.slice(0, 5);\n\n// Return each of the top 5 as separate items\nreturn latestFive.map(email => ({ json: email }));\n"
      },
      "typeVersion": 2
    },
    {
      "id": "a7f0f64f-1af0-4ad9-814d-49a1cff65699",
      "name": "Bounce- und Kontaktdaten zusammenführen",
      "type": "n8n-nodes-base.merge",
      "position": [
        1424,
        1232
      ],
      "parameters": {},
      "typeVersion": 3.2
    },
    {
      "id": "84ff56ce-1a6f-472f-b4ab-bf50705d87f9",
      "name": "Kontaktstatus abgleichen und aktualisieren",
      "type": "n8n-nodes-base.code",
      "position": [
        1616,
        1232
      ],
      "parameters": {
        "jsCode": "// Separate bounce reports and contact rows\nconst failedEmails = items\n  .filter(i => i.json.failedEmail)\n  .map(i => i.json.failedEmail);\n\nconst contacts = items.filter(i => i.json.Email);\n\n// Current timestamp\nconst now = new Date().toISOString();\n\n// Update contacts based on whether they appear in failedEmails\nconst updatedContacts = contacts.map(item => {\n  const email = item.json.Email;\n  const isFailed = failedEmails.includes(email);\n\n  return {\n    json: {\n      row_number: item.json.row_number,\n      Name: item.json.Name,\n      Email: email,\n      Status: isFailed ? \"Not Found\" : \"Not Sent\",\n      \"Last Updated\": now\n    }\n  };\n});\n\nreturn updatedContacts;\n"
      },
      "typeVersion": 2
    },
    {
      "id": "d020cba9-9eaa-406c-9bc6-d6d3c20ba1db",
      "name": "Zusammenfassungsstatistiken berechnen",
      "type": "n8n-nodes-base.code",
      "position": [
        848,
        1904
      ],
      "parameters": {
        "jsCode": "// Extract all contact rows\nconst contacts = items.map(item => item.json);\n\n// Count based on status\nlet invalidCount = 0;\nlet noActivityCount = 0;\n\nfor (const contact of contacts) {\n  const status = (contact.Status || \"\").toLowerCase();\n  if (status === \"not found\") invalidCount++;\n  if (status === \"not sent\") noActivityCount++;\n}\n\n// Return clean summary as one item\nreturn [\n  {\n    json: {\n      \"Invalid emails\": invalidCount,\n      \"No activity\": noActivityCount\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "5ae11ced-5c43-4a24-a8b2-2a49757f7e4e",
      "name": "Notizzettel 1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -320,
        960
      ],
      "parameters": {
        "width": 446,
        "height": 456,
        "content": "## 🎯 MAIN WORKFLOW - Email Bounce Detection\n\n**Purpose:** Detects bounced emails from Gmail and updates Google Sheets\n\n**Flow:**\n1. Manually trigger or schedule to run\n2. Fetch bounce notifications from Gmail (mailer-daemon)\n3. Get latest 5 bounces to process\n4. Parse email addresses from bounce messages\n5. Fetch all contacts from Google Sheet\n6. Merge bounce data with contact list\n7. Match emails and update status (\"Not Found\" if bounced)\n8. Write updated status back to sheet\n\n**Sheet Columns Updated:**\n- Status: \"Not Found\" (bounced) or \"Not Sent\" (no bounce)\n- Last Updated: Current timestamp"
      },
      "typeVersion": 1
    },
    {
      "id": "1615d0f0-4afd-4da2-bb0d-9bd41db897cc",
      "name": "Notizzettel 2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -352,
        1728
      ],
      "parameters": {
        "width": 446,
        "height": 317,
        "content": "## 📊 DAILY REPORTING WORKFLOW\n\n**Purpose:** Sends daily summary to Slack at 7 PM\n\n**Flow:**\n1. Cron trigger fires daily at 19:00 (7 PM)\n2. Read all data from Google Sheet\n3. Calculate statistics:\n   - Count \"Not Found\" status (invalid emails)\n   - Count \"Not Sent\" status (no activity)\n4. Format and send summary to Slack channel\n\n**Slack Channel:** #email-cleanup"
      },
      "typeVersion": 1
    },
    {
      "id": "d795adbb-b531-4b9c-8e0d-eda50037f4e1",
      "name": "Notizzettel 3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        224,
        768
      ],
      "parameters": {
        "width": 237,
        "height": 300,
        "content": "## 🚀 START HERE\n\n**Two Ways to Run:**\n\n1. **Manual:** Click \"Execute workflow\" button for testing\n\n2. **Automatic:** Set up a schedule trigger (replace manual trigger)\n\n**Note:** This triggers the main bounce detection workflow"
      },
      "typeVersion": 1
    },
    {
      "id": "32e515c9-4c0a-40c9-a97f-e06aea785971",
      "name": "Notizzettel 4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        560,
        736
      ],
      "parameters": {
        "width": 237,
        "height": 348,
        "content": "## 📬 FETCH BOUNCES\n\n**What it does:**\nRetrieves ALL bounce notification emails from Gmail\n\n**Filter:**\n- Sender: mailer-daemon@googlemail.com\n\n**Output:**\nList of bounce emails with snippets containing failed addresses"
      },
      "typeVersion": 1
    },
    {
      "id": "5afaf827-064a-4149-a855-61cb71c9d5d3",
      "name": "Notizzettel 5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        896,
        704
      ],
      "parameters": {
        "width": 237,
        "height": 380,
        "content": "## 🔢 LIMIT PROCESSING\n\n**What it does:**\nSorts bounces by date and keeps only the 5 newest\n\n**Why:**\n- Prevents overwhelming the workflow\n- Focuses on recent bounces\n- Improves performance\n\n**Sorting:** Newest first (descending by internalDate)"
      },
      "typeVersion": 1
    },
    {
      "id": "fa7f3745-4b40-4b7c-be33-5ebeceb7352b",
      "name": "Notizzettel 6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1264,
        704
      ],
      "parameters": {
        "width": 237,
        "height": 380,
        "content": "## 🔍 EXTRACT EMAILS\n\n**What it does:**\nParses bounce message snippets to extract failed email addresses\n\n**Regex Pattern:**\n`wasn't delivered to [email]`\n\n**Output Fields:**\n- failedEmail\n- subject\n- id\n- snippet"
      },
      "typeVersion": 1
    },
    {
      "id": "1c0510ae-68e7-4c42-ac92-5523fe4b7978",
      "name": "Notizzettel 7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        896,
        1360
      ],
      "parameters": {
        "width": 237,
        "height": 412,
        "content": "## 📋 GET CONTACTS\n\n**What it does:**\nReads ALL rows from Google Sheet\n\n**Sheet:** Fraud Email\n**Tab:** Sheet1\n\n**Expected Columns:**\n- Name\n- Email\n- Status\n- Last Updated\n\n**Note:** Runs in parallel with bounce fetch"
      },
      "typeVersion": 1
    },
    {
      "id": "62be1676-694b-4328-b126-1849078b9b91",
      "name": "Notizzettel 8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1296,
        1408
      ],
      "parameters": {
        "width": 237,
        "height": 284,
        "content": "## 🔗 MERGE DATA\n\n**What it does:**\nCombines two data streams:\n1. Bounced email addresses\n2. All contacts from sheet\n\n**Result:**\nSingle data stream containing both bounce info and contact list for comparison"
      },
      "typeVersion": 1
    },
    {
      "id": "bbb6ffaa-74da-47f7-853f-5d1969fa0214",
      "name": "Notizzettel 9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1664,
        1408
      ],
      "parameters": {
        "width": 237,
        "height": 412,
        "content": "## ✅ UPDATE STATUS\n\n**What it does:**\nMatches emails and sets status:\n\n- **\"Not Found\"** if email appears in bounce list\n- **\"Not Sent\"** if email NOT in bounce list\n\n**Also Updates:**\n- Last Updated: Current timestamp\n\n**Output:** Prepared data for sheet update"
      },
      "typeVersion": 1
    },
    {
      "id": "db166829-b496-43af-a332-79ddf6de2528",
      "name": "Notizzettel 10",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2176,
        1200
      ],
      "parameters": {
        "width": 236.93062200956936,
        "height": 187.9942396313364,
        "content": "## 💾 SAVE TO SHEET\n\n**What it does:**\nWrites updated status back to Google Sheet\n\n**Update Method:**\n- Match on: Name column\n- Update: Status & Last Updated columns\n\n**Result:**\nSheet now reflects which emails bounced"
      },
      "typeVersion": 1
    },
    {
      "id": "a89cbaf1-3665-4e51-97a6-29d55123ce1f",
      "name": "Notizzettel 11",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        176,
        2048
      ],
      "parameters": {
        "width": 237,
        "height": 300,
        "content": "## ⏰ DAILY SCHEDULE\n\n**What it does:**\nAutomatically triggers daily report\n\n**Schedule:**\nEvery day at 19:00 (7 PM)\n\n**Triggers:** Daily summary workflow to send Slack notification"
      },
      "typeVersion": 1
    },
    {
      "id": "b653e1c7-0247-4d6f-a732-eec9050795ce",
      "name": "Notizzettel 12",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        448,
        1584
      ],
      "parameters": {
        "width": 237,
        "height": 300,
        "content": "## 📖 READ SHEET\n\n**What it does:**\nFetches current data from sheet for reporting\n\n**Reads:**\nAll rows with current status values\n\n**Purpose:**\nProvide data for statistics calculation"
      },
      "typeVersion": 1
    },
    {
      "id": "350dc62c-4f26-4b8d-8914-9821b38b3fb5",
      "name": "Notizzettel 13",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        768,
        2080
      ],
      "parameters": {
        "width": 237,
        "height": 332,
        "content": "## 🧮 CALCULATE STATS\n\n**What it does:**\nCounts contacts by status:\n\n- **Invalid emails:** Count of \"Not Found\" status\n- **No activity:** Count of \"Not Sent\" status\n\n**Output:**\nSingle item with formatted statistics"
      },
      "typeVersion": 1
    },
    {
      "id": "f2fd8aa0-3e40-4df0-85fa-3a22e5f019d5",
      "name": "Notizzettel 14",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1312,
        1792
      ],
      "parameters": {
        "width": 237,
        "height": 316,
        "content": "## 💬 SEND TO SLACK\n\n**What it does:**\nPosts formatted summary to Slack\n\n**Channel:** #email-cleanup\n\n**Message Includes:**\n- 📧 Invalid email count\n- 📭 No activity count\n- Timestamp of report"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "1ad9a2ea-ab34-4d93-bc86-e5cc5e4453f3",
  "connections": {
    "ca28e3bf-d30f-4e54-affc-5b36be6b928e": {
      "main": [
        [
          {
            "node": "3b5dabd0-e0ad-4b27-86d3-fc326edc2842",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "36ffd609-d7c2-461f-82c1-c7303f3e5f46": {
      "main": [
        [
          {
            "node": "d020cba9-9eaa-406c-9bc6-d6d3c20ba1db",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "7f7c1971-5591-4f6c-a3dd-06c3e3e20bb6": {
      "main": [
        [
          {
            "node": "36ffd609-d7c2-461f-82c1-c7303f3e5f46",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "bde866db-a655-4cb3-bba1-81dbda49e09c": {
      "main": [
        [
          {
            "node": "a7f0f64f-1af0-4ad9-814d-49a1cff65699",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "c3984c6c-4861-46fe-8b6b-68bf842f3fa4": {
      "main": [
        [
          {
            "node": "ca28e3bf-d30f-4e54-affc-5b36be6b928e",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "d020cba9-9eaa-406c-9bc6-d6d3c20ba1db": {
      "main": [
        [
          {
            "node": "4ad00c44-5e60-452a-b4ab-89cc23b38c7c",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "a7f0f64f-1af0-4ad9-814d-49a1cff65699": {
      "main": [
        [
          {
            "node": "84ff56ce-1a6f-472f-b4ab-bf50705d87f9",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "84ff56ce-1a6f-472f-b4ab-bf50705d87f9": {
      "main": [
        [
          {
            "node": "ac79fbd2-381e-4de2-b5e2-e7502752978d",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "3b5dabd0-e0ad-4b27-86d3-fc326edc2842": {
      "main": [
        [
          {
            "node": "a7f0f64f-1af0-4ad9-814d-49a1cff65699",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "bff28463-72c1-415a-aac0-8d19808addcf": {
      "main": [
        [
          {
            "node": "c3984c6c-4861-46fe-8b6b-68bf842f3fa4",
            "type": "main",
            "index": 0
          },
          {
            "node": "bde866db-a655-4cb3-bba1-81dbda49e09c",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}