通过AI分析HubSpot和Google Sheets数据预测客户流失

• •HubSpot API Key
• •HTTP Webhook 端点（n8n 会自动生成）
• •可能需要目标 API 的认证凭证
• •Google Sheets API 凭证
• •OpenAI API Key

{
  "nodes": [
    {
      "id": "c00e5d2a-415e-40c1-9e9b-60f34aa3d5b4",
      "name": "便签",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -240,
        0
      ],
      "parameters": {
        "width": 992,
        "height": 368,
        "content": "# 客户健康评分与流失预警"
      },
      "typeVersion": 1
    },
    {
      "id": "9e0e813a-b908-4025-b57d-a724289673ae",
      "name": "便签1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -240,
        1232
      ],
      "parameters": {
        "color": 6,
        "width": 432,
        "height": 176,
        "content": "## 联系我"
      },
      "typeVersion": 1
    },
    {
      "id": "50f33598-bcb5-4afd-8792-9368cfd0ce70",
      "name": "便签2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        672,
        1200
      ],
      "parameters": {
        "color": 7,
        "height": 272,
        "content": "此工具调用此工作流自身的Webhook触发器。它一次性发送所有待处理的工单进行情感分析，并返回一个综合情感评分。"
      },
      "typeVersion": 1
    },
    {
      "id": "fec2e35b-2cb4-43ae-82c8-af9f6ffec53b",
      "name": "便签3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1584,
        816
      ],
      "parameters": {
        "color": 7,
        "width": 352,
        "height": 272,
        "content": "此AI提示检查三个流失信号："
      },
      "typeVersion": 1
    },
    {
      "id": "34112bf3-f326-454d-a34f-cc59583e4ded",
      "name": "手动触发器：运行流失分析",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -192,
        960
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "124ca622-1418-4c72-abdf-1026dd1fdb53",
      "name": "HubSpot: 获取所有商机",
      "type": "n8n-nodes-base.hubspot",
      "position": [
        32,
        960
      ],
      "parameters": {
        "filters": {},
        "resource": "deal",
        "operation": "getAll",
        "authentication": "oAuth2"
      },
      "typeVersion": 2.1
    },
    {
      "id": "10109472-0242-4d47-bb89-b39683107f91",
      "name": "开始循环：对每个商机",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        320,
        960
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 3
    },
    {
      "id": "b76656fb-f5e9-4aff-bb49-39ed6b6edc1f",
      "name": "设置：隔离当前商机ID 导出到Sheets",
      "type": "n8n-nodes-base.set",
      "position": [
        608,
        960
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "a587d50b-5a8f-4650-a803-f9459d4f2342",
              "name": "dealId",
              "type": "string",
              "value": "={{ $json.dealId }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "2a4d7904-4375-47a8-9797-1715b3596484",
      "name": "触发器：接收待评分工单",
      "type": "n8n-nodes-base.webhook",
      "position": [
        -192,
        608
      ],
      "webhookId": "9696956a-460a-4c45-aa3c-e5f83ce95e54",
      "parameters": {
        "path": "9696956a-460a-4c45-aa3c-e5f83ce95e54",
        "options": {},
        "httpMethod": "POST",
        "responseMode": "responseNode"
      },
      "typeVersion": 2.1
    },
    {
      "id": "e9507eaa-294f-4ba8-a1d9-759f6ae58a5d",
      "name": "代码：格式化工单以供分析",
      "type": "n8n-nodes-base.code",
      "position": [
        32,
        608
      ],
      "parameters": {
        "jsCode": "// Loop over input items and add a new field called 'myNewField' to the JSON of each one\n\nlet tickets = JSON.parse($input.first().json.body.tickets) \nlet tickets_text = []\nfor(var ticket of tickets) {\n  if(ticket.properties) tickets_text.push(ticket.properties.subject.value + \"\\n\" + ticket.properties.content.value)\n  else tickets_text.push(ticket.subject + \"\\n\" + ticket.content)\n  \n}\n\nreturn {tickets_text}"
      },
      "typeVersion": 2
    },
    {
      "id": "c34f65fd-2c46-47c0-9b06-87b090feca10",
      "name": "循环：对每个工单",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        256,
        608
      ],
      "parameters": {
        "options": {},
        "fieldToSplitOut": "tickets_text"
      },
      "typeVersion": 1
    },
    {
      "id": "173c07bc-884e-48d3-b927-31cdc800c5b2",
      "name": "AI：分析工单情感",
      "type": "@n8n/n8n-nodes-langchain.sentimentAnalysis",
      "position": [
        480,
        480
      ],
      "parameters": {
        "options": {},
        "inputText": "={{ $json.tickets_text }}"
      },
      "typeVersion": 1.1
    },
    {
      "id": "db3e0604-ba4f-4f09-b281-42848441a5c6",
      "name": "合并：整合结果",
      "type": "n8n-nodes-base.merge",
      "position": [
        832,
        592
      ],
      "parameters": {
        "numberInputs": 3
      },
      "typeVersion": 3.2
    },
    {
      "id": "2be479b2-d019-43d9-a06a-052991743f4d",
      "name": "代码：将情感转换为分数",
      "type": "n8n-nodes-base.code",
      "position": [
        1248,
        608
      ],
      "parameters": {
        "mode": "runOnceForEachItem",
        "jsCode": "// Loop over input items and add a new field called 'myNewField' to the JSON of each one\n \nreturn  {\n    json: {\n      score:\n        $json.sentimentAnalysis.category === 'Positive'\n          ? 10\n          : $json.sentimentAnalysis.category === 'Neutral'\n          ? 5\n          : -10\n    }\n  }"
      },
      "typeVersion": 2
    },
    {
      "id": "a52e3b3c-a73d-459e-9d68-9dc201383860",
      "name": "代码：汇总所有工单分数",
      "type": "n8n-nodes-base.code",
      "position": [
        1648,
        608
      ],
      "parameters": {
        "jsCode": "// Loop over input items and add a new field called 'myNewField' to the JSON of each one\nlet total = 0;\nfor (const item of $input.all()) {\n  total += item.json.score;\n}\nreturn [{ json: { totalScore: total } }];"
      },
      "typeVersion": 2
    },
    {
      "id": "87806b1f-1246-4c82-9fec-71bd668368e9",
      "name": "响应：返回总分数 导出到Sheets",
      "type": "n8n-nodes-base.respondToWebhook",
      "position": [
        1952,
        608
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 1.4
    },
    {
      "id": "5099176a-7e74-4cfe-9e2e-82e7144db3b1",
      "name": "AI代理：收集客户数据",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        960,
        960
      ],
      "parameters": {
        "text": "=You collect data for a customer scoring.\n1.\tDeal data\n\t•\tGet the info for the deal id \"{{ $json.dealId }}\" using the \"Tool: Get HubSpot Data\".\n\t•\tReturn the default fields useful for customer scoring.\n    •   Extract the `dealname` and return it as `companyName`.\n\t•\tEnsure `associatedVids` is included.\n    •   Also return the timestamp of the closing of the deal which is in `hs_is_closed.timestamp`.\n2.\tContact email\n\t•\tFrom the `associatedVids`, fetch the primary contact using the \"Tool: Get HubSpot Data\".\n\t•\tReturn the contact’s email address.\n3. Ticket search and scoring\n    • Find all the tickets by using the tool \"Tool: Get HubSpot Data\".\n    • Then send all the tickets at once to the tool \"Tool: Calculate Sentiment Score\".\n4. Feature search\n    • Then search for **ALL** the features data usage for the contact’s email address in the google sheet tool \"Tool: Get Feature Usage from Sheets\".",
        "options": {
          "returnIntermediateSteps": false
        },
        "promptType": "define",
        "hasOutputParser": true
      },
      "typeVersion": 2.2
    },
    {
      "id": "37a5efac-6dbf-4af0-830e-466e31561113",
      "name": "配置：为代理和链设置LLM",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        544,
        736
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-5-mini",
          "cachedResultName": "gpt-5-mini"
        },
        "options": {}
      },
      "typeVersion": 1.2
    },
    {
      "id": "536718ef-3912-49fd-95c2-d172902e1b3a",
      "name": "工具：获取HubSpot数据",
      "type": "@n8n/n8n-nodes-langchain.mcpClientTool",
      "position": [
        944,
        1312
      ],
      "parameters": {
        "options": {},
        "serverTransport": "httpStreamable"
      },
      "typeVersion": 1.1
    },
    {
      "id": "6c13beac-207b-48fd-8514-242c2ed30c87",
      "name": "工具：计算情感评分",
      "type": "n8n-nodes-base.httpRequestTool",
      "position": [
        784,
        1312
      ],
      "parameters": {
        "method": "POST",
        "options": {},
        "sendBody": true,
        "bodyParameters": {
          "parameters": [
            {
              "name": "={{ /*n8n-auto-generated-fromAI-override*/ $fromAI('parameters0_Name', ``, 'string') }}",
              "value": "={{ /*n8n-auto-generated-fromAI-override*/ $fromAI('parameters0_Value', ``, 'string') }}"
            }
          ]
        },
        "toolDescription": "Send all the info and return the score"
      },
      "typeVersion": 4.2
    },
    {
      "id": "4584effb-8688-4f06-a495-e2b4b7ebfd2e",
      "name": "工具：从Sheets获取功能使用情况",
      "type": "n8n-nodes-base.googleSheetsTool",
      "position": [
        1104,
        1312
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "",
          "cachedResultName": ""
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": ""
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "7f03cc9a-09a2-4d19-b386-09302bcbd699",
      "name": "AI：构建代理发现结果 导出到Sheets",
      "type": "@n8n/n8n-nodes-langchain.outputParserStructured",
      "position": [
        1264,
        1312
      ],
      "parameters": {
        "jsonSchemaExample": "{\n  \"companyName\": \"\",\n  \"email_of_contact\": \"\",\n  \"dealstage\": \"\",\n  \"amount_in_home_currency\": \"\",\n  \"hs_forecast_amount\": \"\",\n  \"Is closed status\": \"\",\n  \"hs_is_closed_timestamp\": 12345,\n  \"associatedVids\": [\"1234\"],\n  \"score\": 10,\n  \"tickets\": [{\n    \"hs_client_id\": 123456,\n    \"content\": \"\",\n    \"subject\": \"\"\n  }],\n  \"feature\": [{\n    \"Feature name\": \"\",\n    \"Monthly Usage Frequency\": 12,\n    \"Number of Users\": 12,\n    \"Date\": \"29/09/2025\"\n  }]\n}"
      },
      "typeVersion": 1.3
    },
    {
      "id": "1147efd1-d880-4c75-a2da-7f6b74df3c43",
      "name": "代码：按名称分组功能数据",
      "type": "n8n-nodes-base.code",
      "position": [
        1424,
        960
      ],
      "parameters": {
        "jsCode": "// Loop over input items and add a new field called 'myNewField' to the JSON of each one\nlet features = $input.all()[0].json.output['feature']\nconsole.log($input.all()[0].json.output['feature'])\n\nlet acc = {}\nfor (const item of features) {\n  if (!acc[item[\"Feature name\"]]) {\n    acc[item[\"Feature name\"]] = [];\n  }\n  acc[item[\"Feature name\"]].push(item)\n}\n\nreturn { res: JSON.stringify(acc)};\n"
      },
      "typeVersion": 2
    },
    {
      "id": "e1f8b15e-3be2-4727-aaba-040d18016057",
      "name": "AI链：分析流失风险",
      "type": "@n8n/n8n-nodes-langchain.chainLlm",
      "position": [
        1648,
        960
      ],
      "parameters": {
        "text": "=You are an AI assistant for Customer Success teams. Your role is to generate clear, actionable email alerts when signs of potential customer churn are detected.\n \nTrigger conditions:\nGenerate an alert titled “Customer Churn Factors Detected” if any of the following are true:\n \nThe time since {{ $('AI Agent: Gather Customer Data').item.json.output.hs_is_closed_timestamp }} is more than 1 year.\nThe score {{ $('AI Agent: Gather Customer Data').item.json.output.score }} is below 0.\nMonthly usage or number of users has decreased according to this data: {{ $json.res }}.\n\nEmail Output Instructions\nIf an alert is triggered, write a short, professional email for the Customer Success Manager.\nYour goal is to help them act quickly, not overwhelm them with raw data.\nDo not include any signature, sign-off, or sender name at the end of the email body.\n\nTone:\nClear, confident, and proactive.\nFriendly but concise — suitable for internal use.\nAvoid data dumps; summarize what matters.\n\nEmail structure:\nSubject:\n“⚠️ Customer Churn Alert: [Company Name] – Thresholds Exceeded”\n\nBody:\nContext / Summary — Briefly explain that churn risk indicators crossed a threshold (e.g. usage drop, low score, long inactivity).\nKey Signals (1–3 bullets) — Summarize the main patterns detected (e.g. “Usage dropped 25% MoM”, “No engagement since July”, “Score below 0 threshold”).\nImpact / Risk Level — Classify risk as Low / Medium / High depending on how many thresholds were exceeded.\nRecommended Actions — Suggest 2–3 concrete next steps to help retain the customer, such as:\n* Schedule a check-in or QBR\n* Offer a training or usage review session\n* Share a new feature relevant to their goals\n* Investigate potential technical or value gaps\n\nClose:\nEnd with a short motivational note (“Addressing this now can help restore engagement before renewal.”).\n\nNote: Use the word “Threshold” naturally 2–3 times throughout to signal data awareness (e.g., “Usage has dropped below the activity threshold”).\n \n\n💡 Example Output\nSubject: ⚠️ Customer Churn Alert: Acme Corp – Usage Threshold Exceeded\nBody:\nHi Team,\nOur system detected that Acme Corp’s engagement has dropped below several key thresholds.\n\nSignals:\n* Product usage fell 28% over the last 30 days\n* Active users decreased from 45 → 32\n* Health score is currently –0.4 (below threshold)\n\nRisk Level: High\n\nRecommended Actions:\n* Reach out for a check-in to understand usage drop-off\n* Share recent case studies or new feature releases\n* Offer a short workflow review to identify blockers\n\nTaking action this week could help recover engagement before renewal.",
        "batching": {},
        "promptType": "define",
        "hasOutputParser": true
      },
      "typeVersion": 1.7
    },
    {
      "id": "c20a8716-68ce-4d76-911e-576a86ce5f7f",
      "name": "AI：构建预警邮件",
      "type": "@n8n/n8n-nodes-langchain.outputParserStructured",
      "position": [
        1792,
        1168
      ],
      "parameters": {
        "jsonSchemaExample": "{\n\t\"subject\": \"\",\n\t\"message\": \"\"\n}"
      },
      "typeVersion": 1.3
    },
    {
      "id": "78bb2588-c4e0-4737-b21d-36889df26a28",
      "name": "格式化：将预警转换为HTML",
      "type": "n8n-nodes-base.markdown",
      "position": [
        2016,
        960
      ],
      "parameters": {
        "mode": "markdownToHtml",
        "options": {},
        "markdown": "={{ $json.output.message }}",
        "destinationKey": "message"
      },
      "typeVersion": 1
    },
    {
      "id": "62eb0b37-a18b-4d49-b321-948ae60b8e79",
      "name": "邮件：发送流失预警 导出到Sheets",
      "type": "n8n-nodes-base.emailSend",
      "position": [
        2224,
        960
      ],
      "webhookId": "e2394524-9bc4-4d11-9de4-512bc7d1a66f",
      "parameters": {
        "html": "={{ $json.message }}",
        "options": {
          "appendAttribution": false
        },
        "subject": "={{ $json.output.subject }}"
      },
      "credentials": {
        "smtp": {
          "id": "kBYvnWkAZ6TejrZw",
          "name": "Postale mateo@pollup.net"
        }
      },
      "typeVersion": 2.1
    }
  ],
  "connections": {
    "Loop: For Each Ticket": {
      "main": [
        [
          {
            "node": "AI: Analyze Ticket Sentiment",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "HubSpot: Get All Deals": {
      "main": [
        [
          {
            "node": "Start Loop: For Each Deal",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Tool: Get HubSpot Data": {
      "ai_tool": [
        [
          {
            "node": "AI Agent: Gather Customer Data",
            "type": "ai_tool",
            "index": 0
          }
        ]
      ]
    },
    "AI: Structure Alert Email": {
      "ai_outputParser": [
        [
          {
            "node": "AI Chain: Analyze for Churn Risk",
            "type": "ai_outputParser",
            "index": 0
          }
        ]
      ]
    },
    "Start Loop: For Each Deal": {
      "main": [
        [],
        [
          {
            "node": "Set: Isolate Current Deal ID  Export to Sheets",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge: Consolidate Results": {
      "main": [
        [
          {
            "node": "Code: Convert Sentiment to Score",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Code: Sum All Ticket Scores": {
      "main": [
        [
          {
            "node": "Respond: Return Total Score  Export to Sheets",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "AI: Analyze Ticket Sentiment": {
      "main": [
        [
          {
            "node": "Merge: Consolidate Results",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Merge: Consolidate Results",
            "type": "main",
            "index": 1
          }
        ],
        [
          {
            "node": "Merge: Consolidate Results",
            "type": "main",
            "index": 2
          }
        ]
      ]
    },
    "Format: Convert Alert to HTML": {
      "main": [
        [
          {
            "node": "Email: Send Churn Alert  Export to Sheets",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "AI Agent: Gather Customer Data": {
      "main": [
        [
          {
            "node": "Code: Group Feature Data by Name",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Tool: Calculate Sentiment Score": {
      "ai_tool": [
        [
          {
            "node": "AI Agent: Gather Customer Data",
            "type": "ai_tool",
            "index": 0
          }
        ]
      ]
    },
    "AI Chain: Analyze for Churn Risk": {
      "main": [
        [
          {
            "node": "Format: Convert Alert to HTML",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Code: Convert Sentiment to Score": {
      "main": [
        [
          {
            "node": "Code: Sum All Ticket Scores",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Code: Group Feature Data by Name": {
      "main": [
        [
          {
            "node": "AI Chain: Analyze for Churn Risk",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Code: Format Tickets for Analysis": {
      "main": [
        [
          {
            "node": "Loop: For Each Ticket",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Config: Set LLM for Agent & Chains": {
      "ai_languageModel": [
        [
          {
            "node": "AI: Analyze Ticket Sentiment",
            "type": "ai_languageModel",
            "index": 0
          },
          {
            "node": "AI Agent: Gather Customer Data",
            "type": "ai_languageModel",
            "index": 0
          },
          {
            "node": "AI Chain: Analyze for Churn Risk",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "Manual Trigger: Run Churn Analysis": {
      "main": [
        [
          {
            "node": "HubSpot: Get All Deals",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Tool: Get Feature Usage from Sheets": {
      "ai_tool": [
        [
          {
            "node": "AI Agent: Gather Customer Data",
            "type": "ai_tool",
            "index": 0
          }
        ]
      ]
    },
    "Trigger: Receive Tickets for Scoring": {
      "main": [
        [
          {
            "node": "Code: Format Tickets for Analysis",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Email: Send Churn Alert  Export to Sheets": {
      "main": [
        [
          {
            "node": "Start Loop: For Each Deal",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set: Isolate Current Deal ID  Export to Sheets": {
      "main": [
        [
          {
            "node": "AI Agent: Gather Customer Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "AI: Structure Agent's Findings  Export to Sheets": {
      "ai_outputParser": [
        [
          {
            "node": "AI Agent: Gather Customer Data",
            "type": "ai_outputParser",
            "index": 0
          }
        ]
      ]
    }
  }
}