{
  "name": "Automate email bounce & invalid detection with Gmail, Google Sheets & Slack",
  "nodes": [
    {
      "id": "bff28463-72c1-415a-aac0-8d19808addcf",
      "name": "When clicking ‘Execute workflow’",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        384,
        1104
      ]
    },
    {
      "id": "3b5dabd0-e0ad-4b27-86d3-fc326edc2842",
      "name": "Parse Bounced Email Addresses",
      "type": "n8n-nodes-base.function",
      "position": [
        1184,
        1120
      ]
    },
    {
      "id": "bde866db-a655-4cb3-bba1-81dbda49e09c",
      "name": "Fetch All Email Contacts",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        736,
        1296
      ]
    },
    {
      "id": "ac79fbd2-381e-4de2-b5e2-e7502752978d",
      "name": "Write Status Back to Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1904,
        1232
      ]
    },
    {
      "id": "7f7c1971-5591-4f6c-a3dd-06c3e3e20bb6",
      "name": "Daily 7PM Report Trigger",
      "type": "n8n-nodes-base.cron",
      "position": [
        304,
        1904
      ]
    },
    {
      "id": "36ffd609-d7c2-461f-82c1-c7303f3e5f46",
      "name": "Read Updated Sheet Data",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        608,
        1904
      ]
    },
    {
      "id": "4ad00c44-5e60-452a-b4ab-89cc23b38c7c",
      "name": "Send Slack Daily Summary",
      "type": "n8n-nodes-base.slack",
      "position": [
        1120,
        1904
      ]
    },
    {
      "id": "c3984c6c-4861-46fe-8b6b-68bf842f3fa4",
      "name": "Fetch Bounce Notifications",
      "type": "n8n-nodes-base.gmail",
      "position": [
        672,
        1120
      ]
    },
    {
      "id": "ca28e3bf-d30f-4e54-affc-5b36be6b928e",
      "name": "Get Latest 5 Bounces",
      "type": "n8n-nodes-base.code",
      "position": [
        880,
        1120
      ]
    },
    {
      "id": "a7f0f64f-1af0-4ad9-814d-49a1cff65699",
      "name": "Combine Bounce & Contact Data",
      "type": "n8n-nodes-base.merge",
      "position": [
        1424,
        1232
      ]
    },
    {
      "id": "84ff56ce-1a6f-472f-b4ab-bf50705d87f9",
      "name": "Match & Update Contact Status",
      "type": "n8n-nodes-base.code",
      "position": [
        1616,
        1232
      ]
    },
    {
      "id": "d020cba9-9eaa-406c-9bc6-d6d3c20ba1db",
      "name": "Calculate Summary Statistics",
      "type": "n8n-nodes-base.code",
      "position": [
        848,
        1904
      ]
    },
    {
      "id": "5ae11ced-5c43-4a24-a8b2-2a49757f7e4e",
      "name": "Sticky Note 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 fr"
      }
    },
    {
      "id": "1615d0f0-4afd-4da2-bb0d-9bd41db897cc",
      "name": "Sticky Note 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   -"
      }
    },
    {
      "id": "d795adbb-b531-4b9c-8e0d-eda50037f4e1",
      "name": "Sticky Note 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 mai"
      }
    },
    {
      "id": "32e515c9-4c0a-40c9-a97f-e06aea785971",
      "name": "Sticky Note 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 containin"
      }
    },
    {
      "id": "5afaf827-064a-4149-a855-61cb71c9d5d3",
      "name": "Sticky Note 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:"
      }
    },
    {
      "id": "fa7f3745-4b40-4b7c-be33-5ebeceb7352b",
      "name": "Sticky Note 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": "1c0510ae-68e7-4c42-ac92-5523fe4b7978",
      "name": "Sticky Note 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 "
      }
    },
    {
      "id": "62be1676-694b-4328-b126-1849078b9b91",
      "name": "Sticky Note 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 com"
      }
    },
    {
      "id": "bbb6ffaa-74da-47f7-853f-5d1969fa0214",
      "name": "Sticky Note 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:"
      }
    },
    {
      "id": "db166829-b496-43af-a332-79ddf6de2528",
      "name": "Sticky Note 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 whi"
      }
    },
    {
      "id": "a89cbaf1-3665-4e51-97a6-29d55123ce1f",
      "name": "Sticky Note 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"
      }
    },
    {
      "id": "b653e1c7-0247-4d6f-a732-eec9050795ce",
      "name": "Sticky Note 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"
      }
    },
    {
      "id": "350dc62c-4f26-4b8d-8914-9821b38b3fb5",
      "name": "Sticky Note 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 format"
      }
    },
    {
      "id": "f2fd8aa0-3e40-4df0-85fa-3a22e5f019d5",
      "name": "Sticky Note 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"
      }
    }
  ],
  "connections": {
    "Get Latest 5 Bounces": {
      "main": [
        [
          {
            "node": "Parse Bounced Email Addresses",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Updated Sheet Data": {
      "main": [
        [
          {
            "node": "Calculate Summary Statistics",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Daily 7PM Report Trigger": {
      "main": [
        [
          {
            "node": "Read Updated Sheet Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch All Email Contacts": {
      "main": [
        [
          {
            "node": "Combine Bounce & Contact Data",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Fetch Bounce Notifications": {
      "main": [
        [
          {
            "node": "Get Latest 5 Bounces",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Calculate Summary Statistics": {
      "main": [
        [
          {
            "node": "Send Slack Daily Summary",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Combine Bounce & Contact Data": {
      "main": [
        [
          {
            "node": "Match & Update Contact Status",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Match & Update Contact Status": {
      "main": [
        [
          {
            "node": "Write Status Back to Sheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Parse Bounced Email Addresses": {
      "main": [
        [
          {
            "node": "Combine Bounce & Contact Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "When clicking ‘Execute workflow’": {
      "main": [
        [
          {
            "node": "Fetch Bounce Notifications",
            "type": "main",
            "index": 0
          },
          {
            "node": "Fetch All Email Contacts",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}