Automatisierter E-Mail-Abmeldeprozess: Outlook-BigQuery-Integration

• •Keine besonderen Voraussetzungen, sofort nach Import nutzbar

{
  "meta": {
    "instanceId": "efb474b59b0341d7791932605bd9ff04a6c7ed9941fdd53dc4a2e4b99a6f9439"
  },
  "nodes": [
    {
      "id": "3282f715-dba9-4078-93fd-a168340d2274",
      "name": "Notiz8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -2280,
        2560
      ],
      "parameters": {
        "width": 1720,
        "height": 860,
        "content": ""
      },
      "typeVersion": 1
    },
    {
      "id": "d9e29f8b-982b-4dcb-b4ab-e73d3fdf20aa",
      "name": "Notiz9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -2280,
        2420
      ],
      "parameters": {
        "color": 5,
        "width": 1720,
        "height": 120,
        "content": "## Email Unsubscribe Handler for Outlook \n\n** Feel free to contact me if you need help implementing (rbreen@ynteractive.com) **"
      },
      "typeVersion": 1
    },
    {
      "id": "7b151f06-fbe0-4103-a8cd-acd441086ad0",
      "name": "Notiz10",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -3100,
        2420
      ],
      "parameters": {
        "width": 780,
        "height": 1000,
        "content": "## How to Implement This n8n Workflow\n\nFollow these steps to configure and deploy the workflow:\n\n1. Connect Your Outlook Account\nIn n8n, go to Credentials > Microsoft Outlook OAuth2.\n\nLog in with the Outlook account that receives replies from your leads.\n\nAssign this credential to the Outlook node that fetches emails.\n\n2. Set Up Google BigQuery\nGo to 👉 https://console.cloud.google.com/\n\nCreate or select a project.\n\nSet up two tables in BigQuery:\n\nunsubscribes (should include at minimum: email, timestamp)\n\nleads (ensure it includes email as a field so you can match and delete)\n\nIn n8n, go to Credentials > Google BigQuery OAuth2 and add your credentials.\n\nAssign this credential to all BigQuery nodes in the workflow.\n\n3. Schedule the Workflow\nUse an Interval node or Cron trigger.\n\nSet it to run every 4 hours (or adjust to your preferred frequency).\n\n✅ You’re All Set!\nEvery time the workflow runs:\n\nIt pulls the last 7 days of email replies from Outlook.\n\nFilters messages that include \"unsubscribe\" (case-insensitive).\n\nSaves the email address to your BigQuery unsubscribes table.\n\nDeletes that email from your BigQuery leads table.\n\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "737b8659-c432-4541-be15-6875136ac804",
      "name": "Elemente durchlaufen1",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        -1100,
        2900
      ],
      "parameters": {
        "options": {},
        "batchSize": "=1"
      },
      "typeVersion": 3
    },
    {
      "id": "e93b95e9-b476-4bc9-af5f-f606b2ec1efa",
      "name": "Zusammenfassen",
      "type": "n8n-nodes-base.summarize",
      "position": [
        -960,
        2640
      ],
      "parameters": {
        "options": {},
        "fieldsToSummarize": {
          "values": [
            {
              "field": "1"
            }
          ]
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "746f69f6-1180-4bbc-ada5-5e797ce75527",
      "name": "Felder bearbeiten",
      "type": "n8n-nodes-base.set",
      "position": [
        -1580,
        2600
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "e67355be-e522-4b1f-948e-0d4a68cc4092",
              "name": "email",
              "type": "string",
              "value": "={{ $('Filter for Unsubscribes').item.json.sender.emailAddress.address }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "29eb258f-2c62-47a8-a79e-17f9c4b02818",
      "name": "Felder bearbeiten1",
      "type": "n8n-nodes-base.set",
      "position": [
        -1580,
        2920
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "22d29a78-ada2-41c4-915f-ece2da260703",
              "name": "queryemail",
              "type": "string",
              "value": "={{ $json.email }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "6831435f-b8f2-4c09-8d89-e6fca412da83",
      "name": "Alle 4 Stunden ausführen",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -2240,
        2920
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "hours",
              "hoursInterval": 4
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "0d20cc6c-0a1e-4d1b-aee8-0700fcb8e164",
      "name": "BigQuery nach allen Abbestellungen abfragen",
      "type": "n8n-nodes-base.googleBigQuery",
      "position": [
        -1960,
        3020
      ],
      "parameters": {
        "options": {},
        "sqlQuery": "SELECT * FROM `n8nautomation-453001.email_leads_schema.Unsubscribes` ",
        "projectId": {
          "__rl": true,
          "mode": "list",
          "value": "n8nautomation-453001",
          "cachedResultUrl": "https://console.cloud.google.com/bigquery?project=n8nautomation-453001",
          "cachedResultName": "n8nAutomation"
        }
      },
      "credentials": {
        "googleBigQueryOAuth2Api": {
          "id": "92PxWUCndZ2LZK34",
          "name": "Google BigQuery account"
        }
      },
      "typeVersion": 2.1,
      "alwaysOutputData": true
    },
    {
      "id": "ad0f0314-caf8-48a6-9854-a537ab929515",
      "name": "Heute & vor 7 Tagen",
      "type": "n8n-nodes-base.code",
      "position": [
        -2020,
        2780
      ],
      "parameters": {
        "jsCode": "const pad = (n, width = 2) => String(n).padStart(width, '0');\n\nconst formatDate = (date) => {\n  const offsetMinutes = date.getTimezoneOffset();\n  const sign = offsetMinutes <= 0 ? '+' : '-';\n  const absOffset = Math.abs(offsetMinutes);\n  const offsetHours = pad(Math.floor(absOffset / 60));\n  const offsetMins = pad(absOffset % 60);\n  const timezone = `${sign}${offsetHours}:${offsetMins}`;\n\n  return `${date.getFullYear()}-${pad(date.getMonth() + 1)}-${pad(date.getDate())}T${pad(date.getHours())}:${pad(date.getMinutes())}:${pad(date.getSeconds())}.0000000${timezone}`;\n};\n\nconst now = new Date();\nconst sevenDaysAgo = new Date(now);\nsevenDaysAgo.setDate(now.getDate() - 5);\n\nreturn [\n  {\n    json: {\n      now: formatDate(now),\n      sevenDaysAgo: formatDate(sevenDaysAgo),\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "633e7061-39f9-4b30-95e1-137eb7e9e9ec",
      "name": "E-Mails der letzten 7 Tage abrufen",
      "type": "n8n-nodes-base.microsoftOutlook",
      "position": [
        -1840,
        2620
      ],
      "webhookId": "ddf2403c-d5c1-4ac0-8136-293fa750e0ba",
      "parameters": {
        "fields": [
          "body",
          "createdDateTime",
          "from",
          "sender"
        ],
        "output": "fields",
        "options": {},
        "folderId": {
          "__rl": true,
          "mode": "list",
          "value": "AQMkADZhNTY3Y2E5LTU4NmMtNGMwNy04MTJiLThkNjkyMzJhMmNkNQAuAAADXiC9rHEGmkKKceK-QpabwAEAw3C9jVjaFUip7UVu8fMd2gAAAgEMAAAA",
          "cachedResultUrl": "https://outlook.office365.com/mail/AQMkADZhNTY3Y2E5LTU4NmMtNGMwNy04MTJiLThkNjkyMzJhMmNkNQAuAAADXiC9rHEGmkKKceK%2FQpabwAEAw3C9jVjaFUip7UVu8fMd2gAAAgEMAAAA",
          "cachedResultName": "Inbox"
        },
        "resource": "folderMessage",
        "filtersUI": {
          "values": {
            "filters": {
              "receivedAfter": "={{ $json.sevenDaysAgo }}",
              "receivedBefore": "={{ $json.now }}"
            }
          }
        },
        "returnAll": true
      },
      "credentials": {
        "microsoftOutlookOAuth2Api": {
          "id": "VmTDwKgAl78H8pek",
          "name": "Microsoft Outlook account"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "8af28462-0892-40c4-8cf5-09c1e1fe31d7",
      "name": "Nach Abbestellungen filtern",
      "type": "n8n-nodes-base.filter",
      "position": [
        -1700,
        2800
      ],
      "parameters": {
        "options": {
          "ignoreCase": true
        },
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": false,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "5a99230c-6b9a-499b-8973-62d0c6daea37",
              "operator": {
                "type": "string",
                "operation": "contains"
              },
              "leftValue": "={{ $json.body.content }}",
              "rightValue": "unsubscribe"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "0b2e4262-5718-4c6f-b1a4-6884e385e277",
      "name": "Nur neue Abbestellungen behalten",
      "type": "n8n-nodes-base.merge",
      "position": [
        -1400,
        2700
      ],
      "parameters": {
        "mode": "combine",
        "options": {},
        "advanced": true,
        "joinMode": "keepNonMatches",
        "mergeByFields": {
          "values": [
            {
              "field1": "=email",
              "field2": "queryemail"
            }
          ]
        },
        "outputDataFrom": "input1"
      },
      "typeVersion": 3.1
    },
    {
      "id": "8fc0c4ef-6e1b-4626-ae2f-2c6911a30970",
      "name": "Auf E-Mail-Ebene aggregieren",
      "type": "n8n-nodes-base.summarize",
      "position": [
        -1240,
        2580
      ],
      "parameters": {
        "options": {},
        "fieldsToSplitBy": "email",
        "fieldsToSummarize": {
          "values": [
            {
              "field": "1"
            }
          ]
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "f79c5b11-a65d-4dd6-a07c-7910f03fb6d9",
      "name": "Abbestellungen zur Tabelle hinzufügen",
      "type": "n8n-nodes-base.googleBigQuery",
      "position": [
        -800,
        3060
      ],
      "parameters": {
        "options": {},
        "sqlQuery": "  MERGE INTO `n8nautomation-453001.email_leads_schema.Unsubscribes` AS target\nUSING (\n  SELECT '{{ $json.email }}' AS email\n) AS source\nON target.email = source.email\nWHEN NOT MATCHED THEN\n  INSERT(email) VALUES(source.email);",
        "projectId": {
          "__rl": true,
          "mode": "list",
          "value": "n8nautomation-453001",
          "cachedResultUrl": "https://console.cloud.google.com/bigquery?project=n8nautomation-453001",
          "cachedResultName": "n8nAutomation"
        }
      },
      "credentials": {
        "googleBigQueryOAuth2Api": {
          "id": "92PxWUCndZ2LZK34",
          "name": "Google BigQuery account"
        }
      },
      "typeVersion": 2.1,
      "alwaysOutputData": true
    }
  ],
  "pinData": {},
  "connections": {
    "e93b95e9-b476-4bc9-af5f-f606b2ec1efa": {
      "main": [
        []
      ]
    },
    "746f69f6-1180-4bbc-ada5-5e797ce75527": {
      "main": [
        [
          {
            "node": "0b2e4262-5718-4c6f-b1a4-6884e385e277",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "29eb258f-2c62-47a8-a79e-17f9c4b02818": {
      "main": [
        [
          {
            "node": "0b2e4262-5718-4c6f-b1a4-6884e385e277",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "737b8659-c432-4541-be15-6875136ac804": {
      "main": [
        [
          {
            "node": "e93b95e9-b476-4bc9-af5f-f606b2ec1efa",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "f79c5b11-a65d-4dd6-a07c-7910f03fb6d9",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "6831435f-b8f2-4c09-8d89-e6fca412da83": {
      "main": [
        [
          {
            "node": "ad0f0314-caf8-48a6-9854-a537ab929515",
            "type": "main",
            "index": 0
          },
          {
            "node": "0d20cc6c-0a1e-4d1b-aee8-0700fcb8e164",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "ad0f0314-caf8-48a6-9854-a537ab929515": {
      "main": [
        [
          {
            "node": "633e7061-39f9-4b30-95e1-137eb7e9e9ec",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "0b2e4262-5718-4c6f-b1a4-6884e385e277": {
      "main": [
        [
          {
            "node": "8fc0c4ef-6e1b-4626-ae2f-2c6911a30970",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "8af28462-0892-40c4-8cf5-09c1e1fe31d7": {
      "main": [
        [
          {
            "node": "746f69f6-1180-4bbc-ada5-5e797ce75527",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "8fc0c4ef-6e1b-4626-ae2f-2c6911a30970": {
      "main": [
        [
          {
            "node": "737b8659-c432-4541-be15-6875136ac804",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "f79c5b11-a65d-4dd6-a07c-7910f03fb6d9": {
      "main": [
        [
          {
            "node": "737b8659-c432-4541-be15-6875136ac804",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "633e7061-39f9-4b30-95e1-137eb7e9e9ec": {
      "main": [
        [
          {
            "node": "8af28462-0892-40c4-8cf5-09c1e1fe31d7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "0d20cc6c-0a1e-4d1b-aee8-0700fcb8e164": {
      "main": [
        [
          {
            "node": "29eb258f-2c62-47a8-a79e-17f9c4b02818",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}