中文网

自动邮件退订处理：Outlook与BigQuery集成

中级

这是一个Social Media领域的自动化工作流，包含 15 个节点。主要使用 Set、Code、Merge、Filter、Summarize 等节点。 自动邮件退订处理：Outlook与BigQuery集成

前置要求
  • •无特殊前置要求，导入即可使用

使用的节点 (15 个)

Set
Code
Merge
Filter
Summarize
StickyNote
GoogleBigQuery
SplitInBatches
ScheduleTrigger
MicrosoftOutlook

分类

Social Media
工作流预览
可视化展示节点连接关系，支持缩放和平移
React Flow
导出工作流
复制以下 JSON 配置到 n8n 导入，即可使用此工作流
{
  "meta": {
    "instanceId": "efb474b59b0341d7791932605bd9ff04a6c7ed9941fdd53dc4a2e4b99a6f9439"
  },
  "nodes": [
    {
      "id": "3282f715-dba9-4078-93fd-a168340d2274",
      "name": "便签8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -2280,
        2560
      ],
      "parameters": {
        "width": 1720,
        "height": 860,
        "content": ""
      },
      "typeVersion": 1
    },
    {
      "id": "d9e29f8b-982b-4dcb-b4ab-e73d3fdf20aa",
      "name": "便签9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -2280,
        2420
      ],
      "parameters": {
        "color": 5,
        "width": 1720,
        "height": 120,
        "content": "## Outlook 邮件退订处理器"
      },
      "typeVersion": 1
    },
    {
      "id": "7b151f06-fbe0-4103-a8cd-acd441086ad0",
      "name": "便签10",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -3100,
        2420
      ],
      "parameters": {
        "width": 780,
        "height": 1000,
        "content": "## 如何实施此 n8n 工作流"
      },
      "typeVersion": 1
    },
    {
      "id": "737b8659-c432-4541-be15-6875136ac804",
      "name": "循环遍历项目1",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        -1100,
        2900
      ],
      "parameters": {
        "options": {},
        "batchSize": "=1"
      },
      "typeVersion": 3
    },
    {
      "id": "e93b95e9-b476-4bc9-af5f-f606b2ec1efa",
      "name": "总结",
      "type": "n8n-nodes-base.summarize",
      "position": [
        -960,
        2640
      ],
      "parameters": {
        "options": {},
        "fieldsToSummarize": {
          "values": [
            {
              "field": "1"
            }
          ]
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "746f69f6-1180-4bbc-ada5-5e797ce75527",
      "name": "编辑字段",
      "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": "编辑字段1",
      "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": "每 4 小时运行",
      "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 获取所有退订记录",
      "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": "今天和 7 天前",
      "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": "获取过去 7 天的邮件",
      "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": "过滤退订邮件",
      "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": "仅保留新退订",
      "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": "聚合到电子邮件级别",
      "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": "添加退订到表格",
      "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": {
    "Summarize": {
      "main": [
        []
      ]
    },
    "Edit Fields": {
      "main": [
        [
          {
            "node": "Keep Only New Unsubs",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Edit Fields1": {
      "main": [
        [
          {
            "node": "Keep Only New Unsubs",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Loop Over Items1": {
      "main": [
        [
          {
            "node": "Summarize",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Add unsubscribes to Table",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Run Ever 4 Hours": {
      "main": [
        [
          {
            "node": "Today & 7 Days Ago",
            "type": "main",
            "index": 0
          },
          {
            "node": "Query Bigquery for all Unsubscribes",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Today & 7 Days Ago": {
      "main": [
        [
          {
            "node": "Get Emails in Past 7 Days",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Keep Only New Unsubs": {
      "main": [
        [
          {
            "node": "Aggregate to email level",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Filter for Unsubscribes": {
      "main": [
        [
          {
            "node": "Edit Fields",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Aggregate to email level": {
      "main": [
        [
          {
            "node": "Loop Over Items1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Add unsubscribes to Table": {
      "main": [
        [
          {
            "node": "Loop Over Items1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Emails in Past 7 Days": {
      "main": [
        [
          {
            "node": "Filter for Unsubscribes",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Query Bigquery for all Unsubscribes": {
      "main": [
        [
          {
            "node": "Edit Fields1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}
常见问题

如何使用这个工作流？

复制上方的 JSON 配置代码，在您的 n8n 实例中创建新工作流并选择「从 JSON 导入」，粘贴配置后根据需要修改凭证设置即可。

这个工作流适合什么场景？

这是一个中级难度的工作流，适用于Social Media等场景。适合有一定经验的用户，包含 6-15 个节点的中等复杂度工作流

需要付费吗？

本工作流完全免费，您可以直接导入使用。但请注意，工作流中使用的第三方服务（如 OpenAI API）可能需要您自行付费。

相关工作流推荐

AI生成LinkedIn帖子含人工审批
使用GPT-4、GoToHuman和Blotato的AI生成LinkedIn帖子含人工审批
Code
Merge
Filter
+10
19 节点Robert Breen
Social Media
使用AI全自动分类Outlook邮件
使用Outlook和GPT-4o自动分类和组织邮件
If
Set
Code
+10
23 节点Can KURT
Miscellaneous
初学者数据分析：使用 GPT-4o 在 Google Sheets 中合并、筛选和汇总
初学者数据分析：使用 GPT-4o 在 Google Sheets 中合并、筛选和汇总
If
Set
Code
+9
21 节点Robert Breen
Document Extraction
使用GPT-4.1、Outlook和Mem.ai自动化Microsoft Teams会议分析
使用GPT-4.1、Outlook和Mem.ai自动化Microsoft Teams会议分析
If
Set
Code
+19
61 节点Wayne Simpson
HR
在可视化参考库中探索n8n节点
在可视化参考库中探索n8n节点
If
Ftp
Set
+93
113 节点I versus AI
Other
使用Gemini AI、SQL和Outlook生成月度财务报告
使用Gemini AI、SQL和Outlook生成月度财务报告
Set
Code
Html
+13
30 节点Amjid Ali
Finance
工作流信息
难度等级
中级
节点数量15
分类1
节点类型10
难度说明

适合有一定经验的用户，包含 6-15 个节点的中等复杂度工作流

作者
Robert Breen

Robert Breen

@rbreen

Professional services consultant with over 10 years of experience solving complex business problems across industries. I specialize in n8n and process automation—designing custom workflows that integrate tools like Google Calendar, Airtable, GPT, and internal systems. Whether you need to automate scheduling, sync data, or streamline operations, I build solutions that save time and drive results.

外部链接
在 n8n.io 上查看 →

分享此工作流