Seguimiento de retención post contratación

• •Cuenta de Google y credenciales de API de Gmail
• •Credenciales de API de Google Sheets
• •Clave de API de OpenAI

{
  "id": "T8iqiPFY6WdVtlC0",
  "meta": {
    "instanceId": "8443f10082278c46aa5cf3acf8ff0f70061a2c58bce76efac814b16290845177"
  },
  "name": "Retention Tracking Post-Hire",
  "tags": [],
  "nodes": [
    {
      "id": "c315357f-e9ad-43c8-89f3-e0a030fb6308",
      "name": "Al hacer clic en 'Ejecutar flujo de trabajo'",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -544,
        -32
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "5703baf8-53a0-46a7-b8fe-1bddb3da80a5",
      "name": "Obtención de Datos del Candidato",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -256,
        64
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1454922719,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1Uldk_4BxWbdZTDZxFUeohIfeBmGHHqVEl9Ogb0l6R8Y/edit#gid=1454922719",
          "cachedResultName": "Retention Summary)"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1Uldk_4BxWbdZTDZxFUeohIfeBmGHHqVEl9Ogb0l6R8Y",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1Uldk_4BxWbdZTDZxFUeohIfeBmGHHqVEl9Ogb0l6R8Y/edit?usp=drivesdk",
          "cachedResultName": "Interviewer Brief Pack "
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "kpPEOLCGn963qpoh",
          "name": "automations@techdome.ai"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "76e5ed93-981c-402f-ba97-0f6c07b4642e",
      "name": "Nota Adhesiva",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        880,
        464
      ],
      "parameters": {
        "height": 384,
        "content": "## ⚠️ Error Handling Logic (Google Sheets – Error Log)  \n\n**Action:**  \n- Appends failed runs into the *Error Log Sheet*.  \n\n**Description:**  \n- Records details like error_id and error message.  \n- Provides visibility into workflow issues.  \n- Ensures graceful handling of bad data without breaking the workflow.  \n"
      },
      "typeVersion": 1
    },
    {
      "id": "d48ac595-d48e-401d-88bd-a31c3cd596d4",
      "name": "Nota Adhesiva1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        592,
        176
      ],
      "parameters": {
        "height": 448,
        "content": "## ✅ Data Validation   \n\n**Action:**  \n- Validates whether both candidate and trait arrays contain records.  \n\n**Description:**  \n- Condition checks: `candidates.length > 0` AND `traits.length > 0`.  \n- If TRUE → proceeds to LLM digest generation.  \n- If FALSE → workflow routes to error logging.  \n- Prevents empty datasets from reaching AI/email stages.  \n"
      },
      "typeVersion": 1
    },
    {
      "id": "bddb3bf5-c3bb-4899-b6ff-aa22b953b366",
      "name": "Nota Adhesiva2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        304,
        -496
      ],
      "parameters": {
        "width": 304,
        "height": 464,
        "content": "## 🧮 Candidate Scoring & Data Normalization (Code Node)  \n\n**Action:**  \n- Cleans, normalizes, and enriches the merged dataset.  \n\n**Description:**  \n- Splits rows into two arrays: `candidates[]` and `traits[]`.  \n- Normalizes headers, trims spaces, and standardizes data.  \n- Builds a trait → weight lookup map from the Retention Summary.  \n- Calculates each candidate’s `Candidate_Score` based on their traits.  \n- Outputs clean JSON with both candidates and traits.  \n"
      },
      "typeVersion": 1
    },
    {
      "id": "549cdd14-5514-4413-9e91-ec3ddd9aa771",
      "name": "Nota Adhesiva3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        80,
        192
      ],
      "parameters": {
        "width": 272,
        "height": 384,
        "content": "## 🔀 Merge Candidate + Trait Data  \n\n**Action:**  \n- Combines candidate-level and trait-level rows into one dataset.  \n\n**Description:**  \n- Unifies inputs from both sheets into a single stream.  \n- Ensures both granular (candidates) and aggregated (traits) data are processed together.  \n- Passes consolidated data into the scoring and enrichment step.  \n"
      },
      "typeVersion": 1
    },
    {
      "id": "b9f7939e-a01f-400f-b5b1-45b9202f6bc4",
      "name": "Nota Adhesiva4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -352,
        -576
      ],
      "parameters": {
        "width": 288,
        "height": 400,
        "content": "## 📑 Trait Summary Fetch (Google Sheets – Retention Summary)  \n\n**Action:**  \n- Retrieves aggregated trait-level data from the Retention Summary sheet.  \n\n**Description:**  \n- Collects retention stats like hires, stayed, left, retention %, and weight adjustment.  \n- Identifies which traits are positively or negatively correlated with retention.  \n- Feeds into candidate scoring logic to adjust weights dynamically.  \n"
      },
      "typeVersion": 1
    },
    {
      "id": "a5260ee9-3a6a-4cc1-ae50-426cb4e7d372",
      "name": "Nota Adhesiva5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -336,
        240
      ],
      "parameters": {
        "width": 272,
        "height": 416,
        "content": "## 📑 Candidate Data Fetch (Google Sheets – Hires Tracking)  \n\n**Action:**  \n- Retrieves the candidate’s details from the designated Google Sheet.  \n\n**Description:**  \n- Pulls structured information such as candidate name, role, traits, start date, and retention status.  \n- This sheet acts as the source of truth for post-hire outcomes.  \n- Ensures accurate and up-to-date records for downstream scoring and reporting.  \n"
      },
      "typeVersion": 1
    },
    {
      "id": "5b4a9840-0dd7-4fcc-b6d1-a2ca015d5daf",
      "name": "Nota Adhesiva6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1168,
        192
      ],
      "parameters": {
        "height": 384,
        "content": "## 🧠 AI Processing Backend (Azure OpenAI Node)  \n\n**Action:**  \n- Executes GPT processing using Azure OpenAI.  \n\n**Description:**  \n- Takes candidate + trait JSON input.  \n- Applies strict prompting rules (no hallucination, only dataset values).  \n- Returns formatted HTML insights for downstream use.  \n"
      },
      "typeVersion": 1
    },
    {
      "id": "94d3d781-c9a4-4778-8029-440612132c7f",
      "name": "Nota Adhesiva7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1024,
        -512
      ],
      "parameters": {
        "width": 368,
        "height": 464,
        "content": "## 🤖 Retention Digest Generator (LLM Chain)  \n\n**Action:**  \n- Generates an HTML Retention Digest using Azure OpenAI.  \n\n**Description:**  \n- Summarizes retention insights into a structured email.  \n- Sections include:  \n  - TL;DR Summary  \n  - Top Traits (positive)  \n  - Weak Traits (negative)  \n  - Candidate Highlights with scores  \n  - 3 Actionable JD Refinement Tips  \n- Ensures valid, styled HTML output ready for email.  \n"
      },
      "typeVersion": 1
    },
    {
      "id": "b6bf37e0-ebfd-48d4-a47b-66d6bbaa2991",
      "name": "Nota Adhesiva8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1504,
        -496
      ],
      "parameters": {
        "height": 432,
        "content": "## 📧 Email Delivery (Gmail – Send Digest)  \n\n**Action:**  \n- Sends the Retention Digest via Gmail.  \n\n**Description:**  \n- Uses the HTML generated by the LLM as the email body.  \n- Subject: *Retention Analysis Digest – Weekly Update*.  \n- Recipients: Hiring managers / stakeholders.  \n- Automates communication of insights directly to decision-makers.  \n"
      },
      "typeVersion": 1
    },
    {
      "id": "82cb57ca-7214-4789-ba0e-c49871938f13",
      "name": "Obtención de Resumen de Rasgos",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -256,
        -144
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 834845387,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1Uldk_4BxWbdZTDZxFUeohIfeBmGHHqVEl9Ogb0l6R8Y/edit#gid=834845387",
          "cachedResultName": "Hires Tracking"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1Uldk_4BxWbdZTDZxFUeohIfeBmGHHqVEl9Ogb0l6R8Y",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1Uldk_4BxWbdZTDZxFUeohIfeBmGHHqVEl9Ogb0l6R8Y/edit?usp=drivesdk",
          "cachedResultName": "Interviewer Brief Pack "
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "kpPEOLCGn963qpoh",
          "name": "automations@techdome.ai"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "41c489c1-9aa2-4ea7-a000-c57e0cd237f0",
      "name": "Fusión de Datos de Candidato + Rasgos",
      "type": "n8n-nodes-base.merge",
      "position": [
        128,
        0
      ],
      "parameters": {},
      "typeVersion": 3.2
    },
    {
      "id": "88119bb1-b48a-4d71-95da-6f012e87830e",
      "name": "Puntuación del Candidato y Normalización de Datos",
      "type": "n8n-nodes-base.code",
      "position": [
        416,
        0
      ],
      "parameters": {
        "jsCode": "// ---- STEP 1: Separate inputs ----\nconst allRows = $input.all();\n\n// Identify rows with candidate info (Hire Tracking) vs traits (Retention Summary)\nconst candidateRaw = allRows.filter(r => r.json[\"Candidate \"] || r.json.Candidate);\nconst traitsRaw = allRows.filter(r => r.json.Trait);\n\n// ---- STEP 2: Normalize Candidate Data ----\nconst candidates = candidateRaw.map(c => {\n  return {\n    row_number: c.json.row_number,\n    Candidate: (c.json[\"Candidate \"] || c.json.Candidate || \"\").trim(),\n    Role: (c.json[\"Role              \"] || c.json.Role || \"\").trim(),\n    Traits: (c.json[\"Traits                   \"] || c.json.Traits || \"\").trim(),\n    Start_Date: c.json[\"Start Date\"] || null,\n    Status: (c.json[\"Status   \"] || c.json.Status || \"\").trim(),\n    Retention_30: c.json[\"Retention_30 \"] ?? c.json.Retention_30 ?? null,\n    Retention_90: c.json[\"Retention_90\"] ?? null,\n    Candidate_Score: c.json[\"Candidate_Score\"] || 0\n  };\n});\n\n// ---- STEP 3: Normalize Traits Summary ----\nconst traits = traitsRaw.map(t => {\n  return {\n    Trait: t.json.Trait,\n    Total_Hires: parseInt(t.json.Total_Hires, 10) || 0,\n    Stayed_90: parseInt(t.json.Stayed_90, 10) || 0,\n    Left_90: parseInt(t.json.Left_90, 10) || 0,\n    \"Retention_%\": parseFloat(t.json[\"Retention_%\"]) || 0,\n    Weight_Adjust: parseInt(t.json.Weight_Adjust, 10) || 0,\n    Candidate_Score: t.json.Candidate_Score || 0\n  };\n});\n\n// ---- STEP 4: Build Trait Weight Lookup ----\nconst weightMap = {};\nfor (const t of traits) {\n  weightMap[t.Trait] = t.Weight_Adjust;\n}\n\n// ---- STEP 5: Calculate Candidate Scores ----\nfor (const c of candidates) {\n  let score = 0;\n  const candidateTraits = (c.Traits || \"\").split(\",\").map(t => t.trim());\n  candidateTraits.forEach(trait => {\n    score += weightMap[trait] ?? 0;\n  });\n  c.Candidate_Score = score;\n}\n\n// ---- STEP 6: Final Combined Output ----\nreturn [\n  {\n    json: {\n      candidates,\n      traits\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "b563b0b1-393d-48a3-8f8f-d8c55fa1d8b7",
      "name": "Validación de Datos",
      "type": "n8n-nodes-base.if",
      "position": [
        672,
        0
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "01f729d5-7169-4f69-89cc-90bc194d11b5",
              "operator": {
                "type": "number",
                "operation": "gt"
              },
              "leftValue": "={{ $json.candidates.length }}",
              "rightValue": 0
            },
            {
              "id": "ef01e17b-4779-4819-a879-742246d8a3f4",
              "operator": {
                "type": "number",
                "operation": "gt"
              },
              "leftValue": "={{ $json.traits.length }}",
              "rightValue": 0
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "71be08e3-2031-416a-851f-9833953b1e25",
      "name": "Lógica de Manejo de Errores",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        928,
        304
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [
            {
              "id": "error_id",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "error_id",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "error",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "error",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "error_id"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1338537721,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1Uldk_4BxWbdZTDZxFUeohIfeBmGHHqVEl9Ogb0l6R8Y/edit#gid=1338537721",
          "cachedResultName": "error log sheet"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1Uldk_4BxWbdZTDZxFUeohIfeBmGHHqVEl9Ogb0l6R8Y",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1Uldk_4BxWbdZTDZxFUeohIfeBmGHHqVEl9Ogb0l6R8Y/edit?usp=drivesdk",
          "cachedResultName": "Interviewer Brief Pack "
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "kpPEOLCGn963qpoh",
          "name": "automations@techdome.ai"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "62e4914b-c97a-46b7-bd66-1fc1cbd4d0df",
      "name": "Generador de Resumen de Retención",
      "type": "@n8n/n8n-nodes-langchain.chainLlm",
      "position": [
        1104,
        -16
      ],
      "parameters": {
        "text": "=Here is the retention dataset:\n\n{{ JSON.stringify($json, null, 2) }}\n\nGenerate one **Retention Digest** email using this dataset. \nThe HTML should include the following sections:\n\n1. **TL;DR Summary** – one short paragraph summarizing retention insights.  \n2. **Top Traits (Strong Retention + Positive Weights)** – list traits with Retention_% = 1 and Weight_Adjust > 0.  \n3. **Weak Traits (Poor Retention + Negative Weights)** – list traits with Retention_% = 0 or Weight_Adjust < 0.  \n4. **Candidate Highlights** – list each candidate, their traits, retention status, and Candidate_Score (positive/negative).  \n5. **Actionable Tips** – provide 3 practical JD refinement recommendations based only on this dataset.  \n\n📌 **Formatting requirements**:  \n- Blue header bar (#0073e6) with white bold title “Retention Insights Digest”.  \n- White card-style container with light shadow + rounded corners.  \n- Section headings:  \n   • Blue (#0073e6) for TL;DR and Top Traits  \n   • Red (#d9534f) for Weak Traits  \n   • Green (#28a745) for Actionable Tips  \n- Candidate list in a table (Name, Traits, Score, Retention_90).  \n- Green CTA button (#28a745) at the bottom labeled “View Full Report”.  \n- Output only valid HTML, no markdown or code fences.  \n",
        "batching": {},
        "messages": {
          "messageValues": [
            {
              "message": "You are an HR Analytics Assistant.  STRICT RULES:  - Use ONLY the traits and candidate data from the provided dataset.  - Do not invent or hallucinate new traits, values, or candidates.  - Always echo the exact Retention_% and Weight_Adjust values.  - Show candidate scores exactly as calculated.  - Output must be valid, production-ready HTML email with inline CSS styling (email-safe). - Do not include markdown or code fences (no ```html)."
            }
          ]
        },
        "promptType": "define"
      },
      "typeVersion": 1.7
    },
    {
      "id": "9fc86f99-9bc5-4161-9145-9cce505d808c",
      "name": "Backend de Procesamiento con IA",
      "type": "@n8n/n8n-nodes-langchain.lmChatAzureOpenAi",
      "position": [
        1072,
        160
      ],
      "parameters": {
        "model": "gpt-4o-mini",
        "options": {}
      },
      "credentials": {
        "azureOpenAiApi": {
          "id": "C3WzT18XqF8OdVM6",
          "name": "Azure Open AI account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "489d85e3-e51c-4db9-ab20-e466db61aa1e",
      "name": "Entrega por Correo Electrónico",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1552,
        -16
      ],
      "parameters": {
        "toList": [
          "newscctv22@gmail.com"
        ],
        "message": " Weekly Update",
        "subject": "=Retention Analysis Digest - Weekly Update\n",
        "resource": "message",
        "htmlMessage": "={{ $json.text }}",
        "includeHtml": true,
        "additionalFields": {
          "ccList": []
        }
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "gEIaWCTvGfYjMSb3",
          "name": "Gmail credentials"
        }
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "1c0b5bac-8ce0-4e48-a2e1-b9bf0868d16a",
  "connections": {
    "b563b0b1-393d-48a3-8f8f-d8c55fa1d8b7": {
      "main": [
        [
          {
            "node": "62e4914b-c97a-46b7-bd66-1fc1cbd4d0df",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "71be08e3-2031-416a-851f-9833953b1e25",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "5703baf8-53a0-46a7-b8fe-1bddb3da80a5": {
      "main": [
        [
          {
            "node": "41c489c1-9aa2-4ea7-a000-c57e0cd237f0",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "82cb57ca-7214-4789-ba0e-c49871938f13": {
      "main": [
        [
          {
            "node": "41c489c1-9aa2-4ea7-a000-c57e0cd237f0",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "9fc86f99-9bc5-4161-9145-9cce505d808c": {
      "ai_languageModel": [
        [
          {
            "node": "62e4914b-c97a-46b7-bd66-1fc1cbd4d0df",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "62e4914b-c97a-46b7-bd66-1fc1cbd4d0df": {
      "main": [
        [
          {
            "node": "489d85e3-e51c-4db9-ab20-e466db61aa1e",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "41c489c1-9aa2-4ea7-a000-c57e0cd237f0": {
      "main": [
        [
          {
            "node": "88119bb1-b48a-4d71-95da-6f012e87830e",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "c315357f-e9ad-43c8-89f3-e0a030fb6308": {
      "main": [
        [
          {
            "node": "5703baf8-53a0-46a7-b8fe-1bddb3da80a5",
            "type": "main",
            "index": 0
          },
          {
            "node": "82cb57ca-7214-4789-ba0e-c49871938f13",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "88119bb1-b48a-4d71-95da-6f012e87830e": {
      "main": [
        [
          {
            "node": "b563b0b1-393d-48a3-8f8f-d8c55fa1d8b7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}