Predict Employee Attrition Risk with Google Sheets, Openai, Gmail, and Telegram

The workflow JSON

Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →

{
  "id": "Nzq9nIxEpPdUau8d",
  "meta": {
    "templateCredsSetupCompleted": true
  },
  "name": "AI-Powered Employee Attrition Prediction & Retention Analytics",
  "tags": [],
  "nodes": [
    {
      "id": "bd118468-cae4-45c7-aecf-985509573e75",
      "name": "Section \u2013 Error Handling",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        3392,
        3376
      ],
      "parameters": {
        "color": 7,
        "width": 652,
        "height": 456,
        "content": "## \u26a0\ufe0f Error Handling\nA workflow-level error trigger catches any node failure and posts an alert to Slack. Replace `YOUR_SLACK_CHANNEL_ID` with your ops or alerts channel before going live."
      },
      "typeVersion": 1
    },
    {
      "id": "ea5ae469-6d6c-4ebe-a648-584846ac31f8",
      "name": "On Workflow Error",
      "type": "n8n-nodes-base.errorTrigger",
      "position": [
        3568,
        3584
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "e4dd7fa3-56e3-4b9d-953e-ec034d7aa046",
      "name": "Slack \u2013 Post Workflow Error Alert",
      "type": "n8n-nodes-base.slack",
      "position": [
        3840,
        3584
      ],
      "parameters": {
        "text": "=\u26a0\ufe0f *AI Crisis Management Agent Error*\n\n*Workflow:* {{ $workflow.name }}\n*Failed Node:* {{ $execution.lastNodeExecuted }}\n*Time:* {{ $now.format('MMM D, YYYY \u2013 HH:mm') }}\n*Execution ID:* {{ $execution.id }}",
        "select": "channel",
        "channelId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_SLACK_CHANNEL_ID",
          "cachedResultName": "your-alerts-channel"
        },
        "otherOptions": {},
        "authentication": "oAuth2"
      },
      "typeVersion": 2.3
    },
    {
      "id": "528e552f-881a-4ef4-82f5-10461da0db4a",
      "name": "\ud83d\udccc Overview \u2013 How This Workflow Works",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        848,
        1488
      ],
      "parameters": {
        "color": 2,
        "width": 936,
        "height": 760,
        "content": "## \ud83d\udcca AI Employee Attrition Prediction & Retention Analytics\n\n### How it works\nEvery week, the workflow pulls five data sources from Google Sheets \u2014 employee master data, attendance records, engagement survey scores, performance reviews, and 1:1 meeting notes \u2014 and merges them into a single profile per employee. A rule-based engine computes early risk signals (absenteeism, low engagement, no recent 1:1, declining reviews). Each profile is then sent to an OpenAI model which returns a structured flight-risk assessment with a severity rating and recommended HR action. The composite score (rules + LLM) is evaluated against a threshold: high-risk employees trigger a Telegram alert to HR and get flagged in the Risk Dashboard sheet. Standard cases are logged and all outcomes merge into one audit row. A separate daily branch reads the day's results and emails an HTML digest to the HR team.\n\n### Setup steps\n1. Connect **Google Sheets OAuth2** and replace `YOUR_GOOGLE_SHEET_ID` in every Sheets node. Create tabs: `Employees_Master`, `Attendance`, `Engagement_Survey`, `Performance_Reviews`, `OneOnOne_Notes`, `Risk_Dashboard`.\n2. Connect **OpenAI API** to the LLM analysis node.\n3. Connect **Gmail OAuth2** and update the recipient email in the Send Weekly HR Digest Email node.\n4. Connect **Telegram Bot API** and replace `YOUR_TELEGRAM_CHAT_ID` in both Telegram nodes.\n5. Adjust the high-risk threshold (default: 70) in the Check High Risk Threshold node.\n6. Activate the weekly schedule trigger."
      },
      "typeVersion": 1
    },
    {
      "id": "1390ae0b-88c5-4c60-ac7c-d0ebc4bc2804",
      "name": "Section \u2013 Trigger & Data Fetch",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2064,
        2112
      ],
      "parameters": {
        "color": 7,
        "width": 880,
        "height": 1388,
        "content": "## \u23f0 Weekly Trigger & Multi-Source Data Fetch\nFires every week. Reads five Google Sheets in parallel \u2014 master employee list, attendance, engagement surveys, performance reviews, and 1:1 notes \u2014 before merging them into one unified employee profile per person."
      },
      "typeVersion": 1
    },
    {
      "id": "d08d70ce-4412-4ff0-a74c-fb907247693e",
      "name": "Section \u2013 Rule-Based Risk Engine",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        3024,
        2608
      ],
      "parameters": {
        "color": 7,
        "width": 1064,
        "height": 556,
        "content": "## \ud83d\udd22 Rule-Based Risk Engine\nMerges all five data sources per employee, then calculates early warning signals in code: high absenteeism rate, low engagement score, no 1:1 in the last 30 days, and a declining performance trend. Each rule contributes to a baseline risk score."
      },
      "typeVersion": 1
    },
    {
      "id": "f1d22d34-8529-4695-bcf7-0c3672cd52c8",
      "name": "Section \u2013 LLM Flight Risk Analysis",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        4192,
        2448
      ],
      "parameters": {
        "color": 7,
        "width": 976,
        "height": 588,
        "content": "## \ud83e\udd16 LLM Flight Risk Analysis\nBuilds a structured prompt per employee and sends it to GPT-4o-mini. The model returns a severity rating (Low/Medium/High/Critical), a primary risk factor, and a recommended HR action. If the LLM call fails, the workflow falls back to the rule-based score only and notifies the admin on Telegram."
      },
      "typeVersion": 1
    },
    {
      "id": "05cfd530-3862-4458-9bd7-162d5228c6fd",
      "name": "Section \u2013 Score Merging & High-Risk Escalation",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        5264,
        2320
      ],
      "parameters": {
        "color": 7,
        "width": 2024,
        "height": 812,
        "content": "## \ud83d\udcc8 Score Merging & High-Risk Escalation\nCombines rule-based and LLM signals into a composite score (0\u2013100). Scores above the threshold (default 70) trigger a Telegram alert to HR and flag the record in the Risk Dashboard sheet. All outcomes \u2014 escalated and standard \u2014 are merged into a single audit row."
      },
      "typeVersion": 1
    },
    {
      "id": "8e4abdfc-c191-4c83-9676-b0fbb009ec25",
      "name": "Section \u2013 Weekly HR Digest Email",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        4352,
        3264
      ],
      "parameters": {
        "color": 7,
        "width": 936,
        "height": 444,
        "content": "## \ud83d\udce7 Weekly HR Digest\nRuns on the same weekly schedule. Reads all risk results logged that day, builds an HTML summary report with employee names, risk levels, and recommended actions, then emails it to the HR team."
      },
      "typeVersion": 1
    },
    {
      "id": "a27bd52b-68f9-4bab-bded-552f60a48c98",
      "name": "\ud83d\udd11 Credentials & Security",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        6752,
        3264
      ],
      "parameters": {
        "color": 3,
        "width": 496,
        "height": 364,
        "content": "## \ud83d\udd11 Credentials Required\n- **Google Sheets OAuth2** \u2014 all 7 Sheets nodes\n- **OpenAI API** \u2014 LLM risk analysis\n- **Gmail OAuth2** \u2014 weekly HR digest email\n- **Telegram Bot API** \u2014 high-risk alert + LLM failure admin alert\n\nNever hardcode sheet IDs, emails, or chat IDs in shared templates."
      },
      "typeVersion": 1
    },
    {
      "id": "2320d176-829a-4453-b898-9d6a0b465818",
      "name": "Parse LLM Risk Assessment",
      "type": "n8n-nodes-base.code",
      "position": [
        4816,
        2688
      ],
      "parameters": {
        "jsCode": "const aiOutput = items[0].json;\nconst original = $('Build Local LLM Prompt').item.json;\n\nlet parsed;\ntry {\n  let raw = (aiOutput.message && aiOutput.message.content ? aiOutput.message.content : '').trim();\n  raw = raw.replace(/^```json/i, '').replace(/^```/, '').replace(/```$/, '').trim();\n  parsed = JSON.parse(raw);\n} catch (e) {\n  parsed = null;\n}\n\nconst base = {\n  employee_id: original.employee_id,\n  full_name: original.full_name,\n  email: original.email,\n  department: original.department,\n  manager: original.manager,\n  rule_based_score: original.rule_based_score,\n  rule_based_factors: original.rule_based_factors\n};\n\nif (!parsed || typeof parsed.risk_score !== 'number') {\n  return [{ json: { ...base, llm_parse_status: 'failed', llm_risk_score: null, llm_risk_level: null, llm_key_factors: [], llm_recommended_action: '' } }];\n}\n\nreturn [{ json: {\n  ...base,\n  llm_parse_status: 'success',\n  llm_risk_score: Math.max(0, Math.min(100, Math.round(parsed.risk_score))),\n  llm_risk_level: parsed.risk_level || 'Medium',\n  llm_key_factors: parsed.key_factors || [],\n  llm_recommended_action: parsed.recommended_action || ''\n} }];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "03c50904-2017-470c-aa2c-1602d890ad0c",
      "name": "Log Standard Risk Entry",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        6672,
        2896
      ],
      "parameters": {
        "columns": {
          "value": {
            "run_date": "={{ $json.run_date }}",
            "full_name": "={{ $json.full_name }}",
            "department": "={{ $json.department }}",
            "risk_level": "={{ $json.risk_level }}",
            "employee_id": "={{ $json.employee_id }}",
            "llm_risk_score": "={{ $json.llm_risk_score }}",
            "key_risk_factors": "={{ $json.rule_based_factors }}{{ $json.llm_key_factors ? ' | ' + $json.llm_key_factors : '' }}",
            "llm_parse_status": "={{ $json.llm_parse_status }}",
            "rule_based_score": "={{ $json.rule_based_score }}",
            "recommended_action": "={{ $json.llm_recommended_action }}",
            "composite_risk_score": "={{ $json.composite_risk_score }}",
            "escalated_to_manager": "No"
          },
          "schema": [
            {
              "id": "run_date",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "run_date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "employee_id",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "employee_id",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "full_name",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "full_name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "department",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "department",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "rule_based_score",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "rule_based_score",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "llm_risk_score",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "llm_risk_score",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "composite_risk_score",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "composite_risk_score",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "risk_level",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "risk_level",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "key_risk_factors",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "key_risk_factors",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "recommended_action",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "recommended_action",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "escalated_to_manager",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "escalated_to_manager",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "llm_parse_status",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "llm_parse_status",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1642947916,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit#gid=1642947916",
          "cachedResultName": "Risk_Dashboard"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_GOOGLE_SHEET_ID",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit?usp=drivesdk",
          "cachedResultName": "HR Data Sheet"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "f1fc599e-d622-400a-bcaa-0e06b0d76ae5",
      "name": "Merge Score Paths",
      "type": "n8n-nodes-base.merge",
      "position": [
        5968,
        2704
      ],
      "parameters": {},
      "typeVersion": 3
    },
    {
      "id": "d41d3f50-4512-42e9-a2ed-6a8cf21fe4a7",
      "name": "Calculate Composite Risk Score",
      "type": "n8n-nodes-base.code",
      "position": [
        5552,
        2512
      ],
      "parameters": {
        "jsCode": "const d = items[0].json;\nconst composite = Math.round((d.rule_based_score * 0.4) + (d.llm_risk_score * 0.6));\nlet level = 'Low';\nif (composite >= 70) level = 'High';\nelse if (composite >= 40) level = 'Medium';\n\nreturn [{ json: {\n  employee_id: d.employee_id,\n  full_name: d.full_name,\n  email: d.email,\n  department: d.department,\n  manager: d.manager,\n  rule_based_score: d.rule_based_score,\n  rule_based_factors: (d.rule_based_factors || []).join('; '),\n  llm_risk_score: d.llm_risk_score,\n  llm_key_factors: (d.llm_key_factors || []).join('; '),\n  llm_recommended_action: d.llm_recommended_action,\n  composite_risk_score: composite,\n  risk_level: level,\n  llm_parse_status: d.llm_parse_status,\n  run_date: new Date().toISOString().slice(0,10)\n} }];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "a1cf0327-4bbd-4751-9b71-293ac1314d96",
      "name": "Merge Data Fetch Results",
      "type": "n8n-nodes-base.merge",
      "position": [
        3136,
        2784
      ],
      "parameters": {
        "numberInputs": 5
      },
      "typeVersion": 3.2
    },
    {
      "id": "fe662541-eb5a-4a99-ae5b-728cf0c3bf14",
      "name": "Local LLM Flight Risk Analysis",
      "type": "@n8n/n8n-nodes-langchain.openAi",
      "position": [
        4448,
        2688
      ],
      "parameters": {
        "modelId": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4o-mini",
          "cachedResultName": "GPT-4O-MINI"
        },
        "options": {},
        "messages": {
          "values": [
            {
              "role": "system",
              "content": "You are an HR analytics assistant running strictly on a local, private model. You will be given anonymized employee signal data (no name/email is ever sent to you). Analyze it for flight-risk / attrition signals and respond with ONLY valid JSON, no markdown fences, no commentary, in this exact shape:\n{\"risk_score\": <0-100 integer>, \"risk_level\": \"Low\" | \"Medium\" | \"High\", \"key_factors\": [\"...\"], \"recommended_action\": \"...\"}\n\nWeigh engagement trend, attendance pattern, eNPS response, 1:1 note sentiment, and performance trajectory together rather than any single factor in isolation."
            },
            {
              "content": "={{ $json.llm_prompt }}"
            }
          ]
        }
      },
      "typeVersion": 1.8
    },
    {
      "id": "4d764ac4-7fdc-4920-916b-149141a4c9fe",
      "name": "Send Weekly HR Digest Email",
      "type": "n8n-nodes-base.gmail",
      "position": [
        4992,
        3472
      ],
      "parameters": {
        "sendTo": "user@example.com",
        "message": "={{ $json.digest_html }}",
        "options": {},
        "subject": "=Weekly Employee Retention Risk Digest \u2014 {{ $json.run_date }}"
      },
      "typeVersion": 2.1
    },
    {
      "id": "a0e45dda-7ea6-46bc-ae93-efd5a46f89f3",
      "name": "Build Weekly Summary Report",
      "type": "n8n-nodes-base.code",
      "position": [
        4720,
        3472
      ],
      "parameters": {
        "jsCode": "const rows = items.map(i => i.json);\nconst today = new Date().toISOString().slice(0,10);\nconst todays = rows.filter(r => r.run_date === today);\n\nconst total = todays.length;\nconst highRisk = todays.filter(r => r.risk_level === 'High');\nconst mediumRisk = todays.filter(r => r.risk_level === 'Medium');\n\nconst byDept = {};\nhighRisk.forEach(r => { byDept[r.department] = (byDept[r.department] || 0) + 1; });\n\nconst factorCounts = {};\ntodays.forEach(r => {\n  (String(r.rule_based_factors || '').split(';')).concat(String(r.llm_key_factors || '').split(';'))\n    .map(f => f.trim()).filter(Boolean)\n    .forEach(f => { factorCounts[f] = (factorCounts[f] || 0) + 1; });\n});\nconst topFactors = Object.entries(factorCounts).sort((a,b) => b[1]-a[1]).slice(0,5).map(([f,c]) => `${f} (${c})`);\n\nconst deptSummary = Object.entries(byDept).map(([d,c]) => `${d}: ${c}`).join(', ') || 'None';\n\nconst highRiskList = highRisk.map(r => `- ${r.full_name} (${r.department}, score ${r.composite_risk_score}) \u2014 manager: ${r.manager}`).join('\\n') || 'No high-risk employees this run.';\n\nconst html = `\n  <h2>Weekly Employee Retention Risk Digest</h2>\n  <p><b>Run date:</b> ${today}</p>\n  <p><b>Employees analyzed:</b> ${total}</p>\n  <p><b>High risk:</b> ${highRisk.length} &nbsp; <b>Medium risk:</b> ${mediumRisk.length}</p>\n  <p><b>High risk by department:</b> ${deptSummary}</p>\n  <p><b>Top recurring risk factors this run:</b><br/>${topFactors.join('<br/>')}</p>\n  <p><b>Employees flagged for manager follow-up:</b></p>\n  <pre>${highRiskList}</pre>\n  <p style=\"color:#888;font-size:12px;\">Generated by RetainOS AI. All flight-risk scoring for this run was computed by a locally-hosted LLM \u2014 no HR data left your network.</p>\n`;\n\nreturn [{ json: {\n  run_date: today,\n  total_analyzed: total,\n  high_risk_count: highRisk.length,\n  medium_risk_count: mediumRisk.length,\n  department_breakdown: deptSummary,\n  top_factors: topFactors.join(', '),\n  digest_html: html\n} }];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "f2e49a62-c858-4920-8eda-e841bcae3132",
      "name": "Get Todays Risk Results",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        4464,
        3472
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1642947916,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit#gid=1642947916",
          "cachedResultName": "Risk_Dashboard"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_GOOGLE_SHEET_ID",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit?usp=drivesdk",
          "cachedResultName": "HR Data Sheet"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "6fa98a19-64da-4cb9-b658-57c2abc77a49",
      "name": "Merge Risk Outcomes",
      "type": "n8n-nodes-base.merge",
      "position": [
        7088,
        2880
      ],
      "parameters": {
        "mode": "combine",
        "options": {}
      },
      "typeVersion": 3
    },
    {
      "id": "a31083b7-d1fc-4552-bf94-1eaf59e7e52a",
      "name": "Flag Escalation in Dashboard",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        6832,
        2672
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [
            {
              "id": "run_date",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "run_date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "employee_id",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "employee_id",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "full_name",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "full_name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "department",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "department",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "rule_based_score",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "rule_based_score",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "llm_risk_score",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "llm_risk_score",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "composite_risk_score",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "composite_risk_score",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "risk_level",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "risk_level",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "key_risk_factors",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "key_risk_factors",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "recommended_action",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "recommended_action",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "escalated_to_manager",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "escalated_to_manager",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "llm_parse_status",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "llm_parse_status",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1642947916,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit#gid=1642947916",
          "cachedResultName": "Risk_Dashboard"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_GOOGLE_SHEET_ID",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit?usp=drivesdk",
          "cachedResultName": "HR Data Sheet"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "2c7ef3d9-fbe3-4ddd-bf12-1d23543296ea",
      "name": "Send High Risk Alert to HR",
      "type": "n8n-nodes-base.telegram",
      "position": [
        6576,
        2672
      ],
      "parameters": {
        "text": "=\ud83d\udea8 HIGH FLIGHT RISK: {{ $json.full_name }} ({{ $json.employee_id }}) \u2014 {{ $json.department }}\nComposite risk score: {{ $json.composite_risk_score }}/100\nRule-based factors: {{ $json.rule_based_factors }}\nLLM factors: {{ $json.llm_key_factors }}\nRecommended action: {{ $json.llm_recommended_action }}\nManager: {{ $json.manager }}",
        "chatId": "YOUR_TELEGRAM_CHAT_ID",
        "additionalFields": {}
      },
      "typeVersion": 1.2
    },
    {
      "id": "6276ad6a-2898-4141-b671-22c03c9f39f9",
      "name": "Check High Risk Threshold",
      "type": "n8n-nodes-base.if",
      "position": [
        6208,
        2688
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "257c0eb9-1f33-4255-a152-fe7ea43c74cf",
              "operator": {
                "type": "number",
                "operation": "gte"
              },
              "leftValue": "={{ $json.composite_risk_score }}",
              "rightValue": 70
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "8723c174-09f0-46d8-9cb2-40ef2f0b87f9",
      "name": "Fallback Rule-Based Only Score",
      "type": "n8n-nodes-base.code",
      "position": [
        5664,
        2848
      ],
      "parameters": {
        "jsCode": "const d = items[0].json;\nconst composite = d.rule_based_score;\nlet level = 'Low';\nif (composite >= 70) level = 'High';\nelse if (composite >= 40) level = 'Medium';\n\nreturn [{ json: {\n  employee_id: d.employee_id,\n  full_name: d.full_name,\n  email: d.email,\n  department: d.department,\n  manager: d.manager,\n  rule_based_score: d.rule_based_score,\n  rule_based_factors: (d.rule_based_factors || []).join('; '),\n  llm_risk_score: null,\n  llm_key_factors: '',\n  llm_recommended_action: 'Local LLM unavailable this run - rule-based score only. Review manually.',\n  composite_risk_score: composite,\n  risk_level: level,\n  llm_parse_status: 'failed',\n  run_date: new Date().toISOString().slice(0,10)\n} }];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "fde9666f-44e1-4f8b-82e4-3612b707f06c",
      "name": "Notify Admin - LLM Failure",
      "type": "n8n-nodes-base.telegram",
      "position": [
        5408,
        2848
      ],
      "parameters": {
        "text": "=\u26a0\ufe0f Local LLM did not return a valid response for employee {{ $json.employee_id }} during this retention analysis run. Falling back to rule-based score only. Check that Ollama (or your local model server) is running and reachable.",
        "chatId": "YOUR_TELEGRAM_CHAT_ID",
        "additionalFields": {}
      },
      "typeVersion": 1.2
    },
    {
      "id": "a5531a48-5b34-4276-b380-ad4b04f11945",
      "name": "Check LLM Parse Success",
      "type": "n8n-nodes-base.if",
      "position": [
        5024,
        2688
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 1,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "loose"
          },
          "combinator": "and",
          "conditions": [
            {
              "operator": {
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.llm_parse_status }}",
              "rightValue": "success"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "0559f19b-faa6-44eb-a318-4ff1e7235e2f",
      "name": "Build Local LLM Prompt",
      "type": "n8n-nodes-base.code",
      "position": [
        4240,
        2688
      ],
      "parameters": {
        "jsCode": "const d = items[0].json;\nconst payload = d.llm_payload;\n\nconst prompt = `You are an HR analytics assistant running strictly on a local, private model. Analyze this anonymized employee signal data and output ONLY valid JSON (no markdown, no commentary) with this exact shape:\n{\"risk_score\": <0-100 integer>, \"risk_level\": \"Low|Medium|High\", \"key_factors\": [\"...\"], \"recommended_action\": \"...\"}\n\nEmployee signal data:\n${JSON.stringify(payload, null, 2)}\n\nConsider engagement trend, attendance pattern, eNPS, 1:1 note sentiment, and performance trajectory together rather than any single factor. Return JSON only, no other text.`;\n\nreturn [{ json: { ...d, llm_prompt: prompt } }];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "c8784a24-5b8f-40fd-b683-6405d19992fc",
      "name": "Loop Through Employees",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        3920,
        2832
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 3
    },
    {
      "id": "ef427c6a-68bd-4ada-ae2e-5fe7d5235e7b",
      "name": "Compute Rule-Based Risk Signals",
      "type": "n8n-nodes-base.code",
      "position": [
        3664,
        2832
      ],
      "parameters": {
        "jsCode": "// Rule-based flight-risk scoring (0-100) + build an ANONYMIZED payload\n// (no name/email) that is all the local LLM step will ever see.\nreturn items.map(item => {\n  const d = item.json;\n  let score = 0;\n  const factors = [];\n\n  const decline = d.engagement_score_previous_qtr - d.engagement_score_current;\n  if (decline >= 25) { score += 30; factors.push('Sharp engagement score decline'); }\n  else if (decline >= 10) { score += 15; factors.push('Moderate engagement score decline'); }\n\n  if (d.attendance_rate_pct < 0.85) { score += 20; factors.push('Attendance below 85%'); }\n  else if (d.attendance_rate_pct < 0.92) { score += 10; factors.push('Attendance trending down'); }\n  if (d.attendance_trend === 'Declining') { score += 10; factors.push('Declining attendance trend'); }\n\n  if (d.enps_response === 'Detractor') { score += 15; factors.push('eNPS Detractor'); }\n  if (d.rating_trend === 'Declining') { score += 15; factors.push('Performance rating declining'); }\n  if (d.note_sentiment === 'Negative') { score += 10; factors.push('Negative sentiment in latest 1:1'); }\n\n  score = Math.min(100, score);\n\n  return {\n    json: {\n      ...d,\n      rule_based_score: score,\n      rule_based_factors: factors,\n      llm_payload: {\n        anon_id: d.employee_id,\n        tenure_months: d.tenure_months,\n        engagement_score_current: d.engagement_score_current,\n        engagement_score_previous_qtr: d.engagement_score_previous_qtr,\n        manager_relationship_score: d.manager_relationship_score,\n        career_growth_score: d.career_growth_score,\n        workload_score: d.workload_score,\n        enps_response: d.enps_response,\n        attendance_rate_pct: d.attendance_rate_pct,\n        unplanned_absences: d.unplanned_absences,\n        attendance_trend: d.attendance_trend,\n        note_summary: d.note_summary,\n        rating_trend: d.rating_trend,\n        goals_met_pct: d.goals_met_pct,\n        peer_feedback_score: d.peer_feedback_score\n      }\n    }\n  };\n});\n"
      },
      "typeVersion": 2
    },
    {
      "id": "b360c329-86ad-4017-bbd8-b458ffb5fc6e",
      "name": "Merge Employee Data Sources",
      "type": "n8n-nodes-base.code",
      "position": [
        3376,
        2832
      ],
      "parameters": {
        "jsCode": "// Combine all 5 HR data sources into a single record per employee.\n// Using $('NodeName').all() so this works regardless of which branch\n// physically feeds this node's input first.\nconst master = $('Get Employee Master Data').all().map(i => i.json);\nconst survey = $('Get Engagement Survey Data').all().map(i => i.json);\nconst attendance = $('Get Attendance Data').all().map(i => i.json);\nconst notes = $('Get 1:1 Meeting Notes').all().map(i => i.json);\nconst reviews = $('Get Performance Review Data').all().map(i => i.json);\n\nfunction findBy(arr, id) {\n  return arr.find(r => r.employee_id === id) || {};\n}\n\nconst merged = master.map(emp => {\n  const id = emp.employee_id;\n  const s = findBy(survey, id);\n  const a = findBy(attendance, id);\n  const n = findBy(notes, id);\n  const r = findBy(reviews, id);\n  return {\n    employee_id: id,\n    full_name: emp.full_name,\n    email: emp.email,\n    department: emp.department,\n    manager: emp.manager,\n    title: emp.title,\n    tenure_months: Number(emp.tenure_months || 0),\n    engagement_score_current: Number(s.engagement_score_current || 0),\n    engagement_score_previous_qtr: Number(s.engagement_score_previous_qtr || 0),\n    score_delta: Number(s.score_delta || 0),\n    manager_relationship_score: Number(s.manager_relationship_score || 0),\n    career_growth_score: Number(s.career_growth_score || 0),\n    workload_score: Number(s.workload_score || 0),\n    enps_response: s.enps_response || '',\n    attendance_rate_pct: Number(a.attendance_rate_pct || 1),\n    unplanned_absences: Number(a.unplanned_absences || 0),\n    late_arrivals: Number(a.late_arrivals || 0),\n    avg_daily_login_hours: Number(a.avg_daily_login_hours || 8),\n    attendance_trend: a.trend_vs_prior_period || 'Stable',\n    note_summary: n.note_summary || '',\n    note_sentiment: n.sentiment_tag || 'Neutral',\n    rating_current: r.rating_current || '',\n    rating_previous: r.rating_previous || '',\n    rating_trend: r.rating_trend || 'Stable',\n    goals_met_pct: Number(r.goals_met_pct || 0),\n    peer_feedback_score: Number(r.peer_feedback_score || 0)\n  };\n});\n\nreturn merged.map(m => ({ json: m }));\n"
      },
      "typeVersion": 2
    },
    {
      "id": "5f735685-0c3f-40a9-88f7-879db38cba86",
      "name": "Get Performance Review Data",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        2448,
        3216
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 512918503,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit#gid=512918503",
          "cachedResultName": "Performance_Reviews"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_GOOGLE_SHEET_ID",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit?usp=drivesdk",
          "cachedResultName": "HR Data Sheet"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "d4f2bf4a-b008-4afc-862f-28883b3cac0e",
      "name": "Get 1:1 Meeting Notes",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        2448,
        3024
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1305511280,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit#gid=1305511280",
          "cachedResultName": "OneOnOne_Notes"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_GOOGLE_SHEET_ID",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit?usp=drivesdk",
          "cachedResultName": "HR Data Sheet"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "76e5b37c-4181-4c9b-9cd7-d7cb8faca233",
      "name": "Get Attendance Data",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        2448,
        2800
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1809290042,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit#gid=1809290042",
          "cachedResultName": "Attendance"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_GOOGLE_SHEET_ID",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit?usp=drivesdk",
          "cachedResultName": "HR Data Sheet"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "31042744-2f92-42b8-9208-410ddd9380bc",
      "name": "Get Engagement Survey Data",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        2448,
        2576
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 849989212,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit#gid=849989212",
          "cachedResultName": "Engagement_Survey"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_GOOGLE_SHEET_ID",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit?usp=drivesdk",
          "cachedResultName": "HR Data Sheet"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "dfd9891d-f745-4aeb-a949-cb0cdfb4519f",
      "name": "Get Employee Master Data",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        2448,
        2352
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 123131184,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit#gid=123131184",
          "cachedResultName": "Employees_Master"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_GOOGLE_SHEET_ID",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit?usp=drivesdk",
          "cachedResultName": "HR Data Sheet"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "8ce32b23-c421-4fa8-99c1-8aa4c1ff09ae",
      "name": "Weekly Retention Analysis Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        2128,
        2800
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "cronExpression",
              "expression": "0 8 * * 1"
            }
          ]
        }
      },
      "typeVersion": 1.2
    }
  ],
  "active": false,
  "settings": {
    "binaryMode": "separate",
    "availableInMCP": false,
    "executionOrder": "v1"
  },
  "versionId": "bb21a167-9ee5-437f-af51-5b73238f9084",
  "nodeGroups": [],
  "connections": {
    "Merge Score Paths": {
      "main": [
        [
          {
            "node": "Check High Risk Threshold",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "On Workflow Error": {
      "main": [
        [
          {
            "node": "Slack \u2013 Post Workflow Error Alert",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Attendance Data": {
      "main": [
        [
          {
            "node": "Merge Data Fetch Results",
            "type": "main",
            "index": 2
          }
        ]
      ]
    },
    "Merge Risk Outcomes": {
      "main": [
        [
          {
            "node": "Loop Through Employees",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get 1:1 Meeting Notes": {
      "main": [
        [
          {
            "node": "Merge Data Fetch Results",
            "type": "main",
            "index": 3
          }
        ]
      ]
    },
    "Build Local LLM Prompt": {
      "main": [
        [
          {
            "node": "Local LLM Flight Risk Analysis",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Loop Through Employees": {
      "main": [
        [
          {
            "node": "Get Todays Risk Results",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Build Local LLM Prompt",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Check LLM Parse Success": {
      "main": [
        [
          {
            "node": "Calculate Composite Risk Score",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Notify Admin - LLM Failure",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Todays Risk Results": {
      "main": [
        [
          {
            "node": "Build Weekly Summary Report",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Log Standard Risk Entry": {
      "main": [
        [
          {
            "node": "Merge Risk Outcomes",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Get Employee Master Data": {
      "main": [
        [
          {
            "node": "Merge Data Fetch Results",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge Data Fetch Results": {
      "main": [
        [
          {
            "node": "Merge Employee Data Sources",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Check High Risk Threshold": {
      "main": [
        [
          {
            "node": "Send High Risk Alert to HR",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Log Standard Risk Entry",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Parse LLM Risk Assessment": {
      "main": [
        [
          {
            "node": "Check LLM Parse Success",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Engagement Survey Data": {
      "main": [
        [
          {
            "node": "Merge Data Fetch Results",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Notify Admin - LLM Failure": {
      "main": [
        [
          {
            "node": "Fallback Rule-Based Only Score",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Send High Risk Alert to HR": {
      "main": [
        [
          {
            "node": "Flag Escalation in Dashboard",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Build Weekly Summary Report": {
      "main": [
        [
          {
            "node": "Send Weekly HR Digest Email",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Performance Review Data": {
      "main": [
        [
          {
            "node": "Merge Data Fetch Results",
            "type": "main",
            "index": 4
          }
        ]
      ]
    },
    "Merge Employee Data Sources": {
      "main": [
        [
          {
            "node": "Compute Rule-Based Risk Signals",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Flag Escalation in Dashboard": {
      "main": [
        [
          {
            "node": "Merge Risk Outcomes",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Calculate Composite Risk Score": {
      "main": [
        [
          {
            "node": "Merge Score Paths",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fallback Rule-Based Only Score": {
      "main": [
        [
          {
            "node": "Merge Score Paths",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Local LLM Flight Risk Analysis": {
      "main": [
        [
          {
            "node": "Parse LLM Risk Assessment",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Compute Rule-Based Risk Signals": {
      "main": [
        [
          {
            "node": "Loop Through Employees",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Weekly Retention Analysis Trigger": {
      "main": [
        [
          {
            "node": "Get Performance Review Data",
            "type": "main",
            "index": 0
          },
          {
            "node": "Get 1:1 Meeting Notes",
            "type": "main",
            "index": 0
          },
          {
            "node": "Get Attendance Data",
            "type": "main",
            "index": 0
          },
          {
            "node": "Get Engagement Survey Data",
            "type": "main",
            "index": 0
          },
          {
            "node": "Get Employee Master Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

About this workflow

This workflow runs weekly to pull HR datasets from Google Sheets, calculate rule-based attrition risk signals, and enrich them with an OpenAI (GPT-4o-mini) risk assessment. It logs results back to Google Sheets, sends high-risk alerts via Telegram, emails an HTML digest via…

Source: https://n8n.io/workflows/17601/ — original creator credit. Request a take-down →

More Slack & Telegram workflows → · Browse all categories →

Related workflows

Workflows that share integrations, category, or trigger type with this one. All free to copy and import.