08 - KI-gesteuerte Kontaktintelligenzanalyse

• •HubSpot API Key
• •Airtable API Key
• •Google Sheets API-Anmeldedaten
• •OpenAI API Key

{
  "name": "08 - AI-Powered Contact Intelligence",
  "tags": [],
  "nodes": [
    {
      "id": "09b19752-a5fe-4786-8477-2e8605d62330",
      "name": "⏰ Stündlich ausführen",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -1880,
        100
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "hours"
            }
          ]
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "308fbf64-8376-4168-9ac3-19e1f85fc654",
      "name": "📄 Ausstehende Kontakte lesen",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -1660,
        100
      ],
      "parameters": {
        "options": {
          "returnAllMatches": "returnFirstMatch"
        },
        "filtersUI": {
          "values": [
            {
              "lookupValue": "Pending",
              "lookupColumn": "Status"
            }
          ]
        },
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_GOOGLE_SHEET_ID",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit",
          "cachedResultName": "Data"
        },
        "combineFilters": "AND"
      },
      "typeVersion": 4
    },
    {
      "id": "a865ab5b-0bf7-4bb9-b2b8-50fd628ed563",
      "name": "🧪 Name & E-Mail filtern",
      "type": "n8n-nodes-base.filter",
      "position": [
        -1440,
        100
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 1,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "has-name",
              "operator": {
                "type": "string",
                "operation": "notEmpty"
              },
              "leftValue": "={{ $json.Name }}",
              "rightValue": ""
            },
            {
              "id": "has-email",
              "operator": {
                "type": "string",
                "operation": "notEmpty"
              },
              "leftValue": "={{ $json.Email }}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "990bf325-2704-4fbb-af01-e0c9518b0c4b",
      "name": "🧹 Kontaktdaten bereinigen",
      "type": "n8n-nodes-base.code",
      "position": [
        -1220,
        100
      ],
      "parameters": {
        "jsCode": "const items = [];\n\nfor (const item of $input.all()) {\n  const raw = item.json;\n\n  // Clean up field names by trimming spaces\n  const contact = {};\n  for (const key in raw) {\n    contact[key.trim()] = raw[key];\n  }\n\n  items.push({\n    json: {\n      name: contact.Name || '',\n      email: contact.Email || '',\n      title: contact.Title || 'Not provided',\n      company: contact.Company || 'Not provided',\n      phone: contact.Phone || 'Not provided',\n      linkedin: contact.LinkedIn || 'Not provided',\n      notes: contact.Notes || 'Not provided'\n    }\n  });\n}\n\nreturn items;"
      },
      "typeVersion": 2
    },
    {
      "id": "6a728a8e-d85b-49a1-ae41-a5c086fefbf5",
      "name": "🧠 LLM - GPT-4o-mini",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        -1000,
        100
      ],
      "parameters": {
        "text": "=You are a professional recruiter assistant. Based on the contact information below, categorize the person by:\n\nfunction (e.g., Sales, Marketing, Engineering, HR, Finance, Product, Customer Support, Executive, Legal, Operations, Other)\n\nseniority (e.g., Entry Level, Junior, Mid Level, Senior Level, Manager, Director, VP, C-Level, Founder)\n\ndepartment if clearly known (else return \"Unknown\")\n\nassign a confidence_score from 0 to 100 (integer) based on how clearly the contact's role can be inferred\n\nexplain your reasoning in 1–2 lines\n\nReturn your response strictly in JSON format like this:\n\n{\n  \"function\": \"Sales\",\n  \"seniority\": \"Manager\",\n  \"department\": \"Revenue\",\n  \"confidence_score\": 87,\n  \"reasoning\": \"Title contains 'Account Manager' which indicates a mid-level sales role.\"\n}\n\nUse this contact data:\n\nName: {{ $json.name }}\n\nEmail: {{ $json.email }}\n\nTitle: {{ $json.title }}\n\nCompany: {{ $json.company }}\n\nPhone: {{ $json.phone }}\n\nLinkedIn: {{ $json.linkedin }}\n\nNotes: {{ $json.notes }}\n",
        "options": {},
        "promptType": "define"
      },
      "typeVersion": 2
    },
    {
      "id": "bea8cbb6-bc03-4360-94a0-7ede08702e31",
      "name": "🧩 Rollen-Tags parsen oder Fallback",
      "type": "n8n-nodes-base.code",
      "position": [
        -624,
        100
      ],
      "parameters": {
        "jsCode": "const items = [];\n\nfor (let i = 0; i < $input.all().length; i++) {\n  const aiItem = $input.all()[i];\n  const originalContact = $('🧹 Clean Contact Data').all()[i].json;\n  \n  let analysis;\n  try {\n    // AI Agent returns response in output field\n    const aiResponse = aiItem.json.output || aiItem.json.text || '';\n    \n    // Try to extract JSON from the response\n    const jsonMatch = aiResponse.match(/\\{[^}]+\\}/s);\n    if (jsonMatch) {\n      analysis = JSON.parse(jsonMatch[0]);\n    } else {\n      throw new Error('No JSON found in response');\n    }\n  } catch (error) {\n    console.log('Failed to parse AI Agent response:', error);\n    console.log('AI Response:', aiItem.json);\n    \n    // Fallback analysis based on title keywords\n    const title = originalContact.title.toLowerCase();\n    let fallbackFunction = 'Other';\n    let fallbackSeniority = 'Mid Level';\n    \n    // Simple keyword matching for fallback\n    if (title.includes('sales') || title.includes('account')) fallbackFunction = 'Sales';\n    else if (title.includes('marketing')) fallbackFunction = 'Marketing';\n    else if (title.includes('engineer') || title.includes('developer')) fallbackFunction = 'Engineering';\n    else if (title.includes('hr') || title.includes('people')) fallbackFunction = 'HR';\n    else if (title.includes('finance') || title.includes('accounting')) fallbackFunction = 'Finance';\n    \n    if (title.includes('ceo') || title.includes('cto') || title.includes('cfo')) fallbackSeniority = 'C-Level';\n    else if (title.includes('vp') || title.includes('vice president')) fallbackSeniority = 'VP';\n    else if (title.includes('director')) fallbackSeniority = 'Director';\n    else if (title.includes('manager')) fallbackSeniority = 'Manager';\n    else if (title.includes('senior')) fallbackSeniority = 'Senior Level';\n    else if (title.includes('junior') || title.includes('entry')) fallbackSeniority = 'Entry Level';\n    \n    analysis = {\n      function: fallbackFunction,\n      seniority: fallbackSeniority,\n      department: 'Unknown',\n      confidence_score: 0.3,\n      reasoning: 'Fallback analysis due to AI parsing error'\n    };\n  }\n  \n  const enrichedContact = {\n    ...originalContact,\n    function: analysis.function || 'Other',\n    seniority: analysis.seniority || 'Mid Level',\n    department: analysis.department || 'Unknown',\n    confidence_score: analysis.confidence_score || 0.5,\n    reasoning: analysis.reasoning || '',\n    analyzed_at: new Date().toISOString()\n  };\n  \n  items.push({ json: enrichedContact });\n}\n\nreturn items;"
      },
      "typeVersion": 2
    },
    {
      "id": "7a092066-611a-49ce-8b0e-97645761372f",
      "name": "🧠 Rolle & Seniorität klassifizieren",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        -912,
        320
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4o-mini"
        },
        "options": {}
      },
      "typeVersion": 1.2
    },
    {
      "id": "4ec658e5-df8a-4cec-ba25-a6b0db684c7b",
      "name": "📊 Kontakt in Tabelle aktualisieren",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -404,
        -100
      ],
      "parameters": {
        "columns": {
          "value": {
            "Email": "={{ $('📄 Read Pending Contacts').item.json.Email }}",
            "Status": "Reviewed",
            "Function": "={{ $json.function }}",
            "Seniority": "={{ $json.seniority }}",
            "Confidence Score": "={{ $json.confidence_score }}"
          },
          "schema": [
            {
              "id": "Name",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Email",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Title",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Title",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Company",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Company",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Phone ",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Phone ",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "LinkedIn",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "LinkedIn",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Notes",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Notes",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Function",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Function",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Seniority",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Seniority",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Confidence Score",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Confidence Score",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Status",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Status",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "Email"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_GOOGLE_SHEET_ID",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit",
          "cachedResultName": "Data"
        }
      },
      "typeVersion": 4
    },
    {
      "id": "ad2c304c-c36c-4be0-80cd-726baa0b2e47",
      "name": "🔁 Synchronisieren mit Airtable",
      "type": "n8n-nodes-base.airtable",
      "position": [
        -404,
        100
      ],
      "parameters": {
        "base": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_AIRTABLE_BASE_ID",
          "cachedResultUrl": "https://airtable.com/YOUR_AIRTABLE_BASE_ID",
          "cachedResultName": "Your Airtable Base"
        },
        "table": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_AIRTABLE_TABLE_ID",
          "cachedResultUrl": "https://airtable.com/YOUR_AIRTABLE_BASE_ID/YOUR_AIRTABLE_TABLE_ID",
          "cachedResultName": "Your Table"
        },
        "columns": {
          "value": {
            "Name": "={{ $json.name }}",
            "Email": "={{ $json.email }}",
            "Phone": "={{ $json.phone }}",
            "Title": "={{ $json.title }}",
            "Company": "={{ $json.company }}",
            "Function": "={{ $json.function }}",
            "LinkedIn": "={{ $json.linkedin }}",
            "Seniority": "={{ $json.seniority }}",
            "Department": "={{ $json.department }}",
            "Confidence Score": "={{ $json.confidence_score }}"
          },
          "schema": [
            {
              "id": "id",
              "type": "string",
              "display": true,
              "removed": true,
              "readOnly": true,
              "required": false,
              "displayName": "id",
              "defaultMatch": true
            },
            {
              "id": "Name",
              "type": "string",
              "display": true,
              "removed": false,
              "readOnly": false,
              "required": false,
              "displayName": "Name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Email",
              "type": "string",
              "display": true,
              "removed": false,
              "readOnly": false,
              "required": false,
              "displayName": "Email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Title",
              "type": "string",
              "display": true,
              "removed": false,
              "readOnly": false,
              "required": false,
              "displayName": "Title",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Company",
              "type": "string",
              "display": true,
              "removed": false,
              "readOnly": false,
              "required": false,
              "displayName": "Company",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Phone",
              "type": "string",
              "display": true,
              "removed": false,
              "readOnly": false,
              "required": false,
              "displayName": "Phone",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "LinkedIn",
              "type": "string",
              "display": true,
              "removed": false,
              "readOnly": false,
              "required": false,
              "displayName": "LinkedIn",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Function",
              "type": "string",
              "display": true,
              "removed": false,
              "readOnly": false,
              "required": false,
              "displayName": "Function",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Seniority",
              "type": "string",
              "display": true,
              "removed": false,
              "readOnly": false,
              "required": false,
              "displayName": "Seniority",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Department",
              "type": "string",
              "display": true,
              "removed": false,
              "readOnly": false,
              "required": false,
              "displayName": "Department",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Confidence Score",
              "type": "number",
              "display": true,
              "removed": false,
              "readOnly": false,
              "required": false,
              "displayName": "Confidence Score",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "Email"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "upsert",
        "authentication": "appToken"
      },
      "typeVersion": 2
    },
    {
      "id": "29fd44b3-50ae-4259-a70f-101c3ed62a81",
      "name": "📬 Übertragen an HubSpot",
      "type": "n8n-nodes-base.hubspot",
      "position": [
        -404,
        300
      ],
      "parameters": {
        "email": "={{ $json.email }}",
        "options": {},
        "authentication": "appToken",
        "additionalFields": {}
      },
      "typeVersion": 2
    },
    {
      "id": "56bf9c8a-37b3-4d7e-8bbb-27b14ba9a3dd",
      "name": "Haftnotiz",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1940,
        -120
      ],
      "parameters": {
        "color": 5,
        "width": 220,
        "height": 460,
        "content": "## Step-1\n\n**This is the automated schedule to trigger the workflow every hour to fetch and process new contacts.**\n"
      },
      "typeVersion": 1
    },
    {
      "id": "2dd459b2-a6dc-49e6-ae65-8b9f82c42599",
      "name": "Haftnotiz 1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1700,
        -120
      ],
      "parameters": {
        "color": 2,
        "width": 420,
        "height": 460,
        "content": "## Step-2\n\n**Reads rows from Google Sheets where Status = \"Pending\" and filters out any row missing essential fields like Name or Email.**"
      },
      "typeVersion": 1
    },
    {
      "id": "8a157923-e604-4a67-80be-a1f66922a5ff",
      "name": "Haftnotiz 2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1260,
        -120
      ],
      "parameters": {
        "color": 3,
        "width": 200,
        "height": 460,
        "content": "## Step-3\n\n**Cleans up field names and fills in defaults (Title, Company, LinkedIn, etc.)**"
      },
      "typeVersion": 1
    },
    {
      "id": "d43ba817-75fc-4064-8d7a-9adeea81fe4c",
      "name": "Haftnotiz 3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1040,
        -120
      ],
      "parameters": {
        "color": 4,
        "width": 540,
        "height": 600,
        "content": "## Step-4\n\n**Classifies contact by Function, Seniority, and Department using GPT-4o-mini.\nParses AI response; if it fails, applies keyword-based fallback.\nAdds analyzed_at timestamp..**"
      },
      "typeVersion": 1
    },
    {
      "id": "4189b779-9569-4890-aadd-d88b046d4e33",
      "name": "Haftnotiz 4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -480,
        -260
      ],
      "parameters": {
        "color": 6,
        "width": 460,
        "height": 740,
        "content": "## Step-5\n\n**Distributes enriched contact to:\n✅ Google Sheet (adds function, seniority, marks Reviewed)\n✅ Airtable CRM (upserts by email)\n✅ HubSpot CRM (creates/updates contact)**"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "connections": {
    "09b19752-a5fe-4786-8477-2e8605d62330": {
      "main": [
        [
          {
            "node": "308fbf64-8376-4168-9ac3-19e1f85fc654",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "6a728a8e-d85b-49a1-ae41-a5c086fefbf5": {
      "main": [
        [
          {
            "node": "bea8cbb6-bc03-4360-94a0-7ede08702e31",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "990bf325-2704-4fbb-af01-e0c9518b0c4b": {
      "main": [
        [
          {
            "node": "6a728a8e-d85b-49a1-ae41-a5c086fefbf5",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "a865ab5b-0bf7-4bb9-b2b8-50fd628ed563": {
      "main": [
        [
          {
            "node": "990bf325-2704-4fbb-af01-e0c9518b0c4b",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "308fbf64-8376-4168-9ac3-19e1f85fc654": {
      "main": [
        [
          {
            "node": "a865ab5b-0bf7-4bb9-b2b8-50fd628ed563",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "7a092066-611a-49ce-8b0e-97645761372f": {
      "ai_languageModel": [
        [
          {
            "node": "6a728a8e-d85b-49a1-ae41-a5c086fefbf5",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "bea8cbb6-bc03-4360-94a0-7ede08702e31": {
      "main": [
        [
          {
            "node": "4ec658e5-df8a-4cec-ba25-a6b0db684c7b",
            "type": "main",
            "index": 0
          },
          {
            "node": "ad2c304c-c36c-4be0-80cd-726baa0b2e47",
            "type": "main",
            "index": 0
          },
          {
            "node": "29fd44b3-50ae-4259-a70f-101c3ed62a81",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}