Anreicherung von Unternehmensdomains mit Geschäftsdaten mithilfe von Perplexity AI und Google Sheets

• •Möglicherweise sind Ziel-API-Anmeldedaten erforderlich
• •Google Sheets API-Anmeldedaten

{
  "meta": {
    "instanceId": "aff7017841e089b12fdff439d182db1ce6e6151977703d9243f6be0543724481",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "bffa5202-a56e-4f05-8b01-e8c4329915f3",
      "name": "Manueller Trigger",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -480,
        56
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "bc1ce2a3-f4b0-4cbe-a406-49ac2e339924",
      "name": "Ungesicherte Domains abrufen",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -256,
        56
      ],
      "parameters": {
        "options": {},
        "filtersUI": {
          "values": [
            {
              "lookupValue": "=",
              "lookupColumn": "processed"
            }
          ]
        },
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1737567569,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1bdK8xskt-qfLlDwdzolM0zFyo9KxZ-HHpTVxcEw3ZMY/edit#gid=1737567569",
          "cachedResultName": "Data"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1bdK8xskt-qfLlDwdzolM0zFyo9KxZ-HHpTVxcEw3ZMY",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1bdK8xskt-qfLlDwdzolM0zFyo9KxZ-HHpTVxcEw3ZMY/edit?usp=drivesdk",
          "cachedResultName": "Get Addresses"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "Y43YNvASDhLxJg65",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "e8d61653-67ef-45bd-8640-f3317c859516",
      "name": "Domains stapelverarbeiten",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        -32,
        56
      ],
      "parameters": {
        "options": {},
        "batchSize": 10
      },
      "typeVersion": 3
    },
    {
      "id": "4946ef7e-852b-4860-bef0-aa44bc0c5221",
      "name": "Perplexity AI-Recherche",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        192,
        -16
      ],
      "parameters": {
        "url": "https://api.perplexity.ai/chat/completions",
        "method": "POST",
        "options": {},
        "jsonBody": "={\n  \"model\": \"sonar\",\n  \"messages\": [\n    {\n      \"role\": \"user\",\n      \"content\": \"For each of the following 10 companies, return only German addresses and provide: address split as \\\"address\\\", \\\"city\\\", \\\"state\\\", \\\"postal_code\\\", \\\"country\\\"; phone (international format); latest employee count; latest annual revenue (numbers only in USD); industry (e.g., LinkedIn industry category if available); company LinkedIn URL; and a reliable source URL as \\\"source_url\\\". Output results as an array of 10 JSON objects. If any field can't be found reliably, set it to null and don't make things. Companies: ={{ $input.all().map(item => item.json.domain) }}\"\n    }\n  ],\n  \"response_format\": {\n    \"type\": \"json_schema\",\n    \"json_schema\": {\n      \"schema\": {\n        \"type\": \"object\",\n        \"properties\": {\n          \"companies\": {\n            \"type\": \"array\",\n            \"items\": {\n              \"type\": \"object\",\n              \"properties\": {\n                \"domain\": { \"type\": \"string\" },\n                \"company\": { \"type\": \"string\" },\n                \"address\": { \"type\": \"string\" },\n                \"city\": { \"type\": \"string\" },\n                \"state\": { \"type\": \"string\" },\n                \"postal_code\": { \"type\": \"string\" },\n                \"country\": { \"type\": \"string\" },\n                \"phone\": { \"type\": \"string\" },\n                \"employees\": { \"type\": \"integer\", \"nullable\": true },\n                \"revenue\": { \"type\": \"number\", \"nullable\": true },\n                \"industry\": { \"type\": \"string\", \"nullable\": true },\n                \"linkedin_url\": { \"type\": \"string\", \"nullable\": true },\n                \"source_url\": { \"type\": \"string\" }\n              },\n              \"required\": [\n                \"domain\",\n                \"company\",\n                \"address\",\n                \"city\",\n                \"state\",\n                \"postal_code\",\n                \"country\",\n                \"phone\",\n                \"employees\",\n                \"revenue\",\n                \"industry\",\n                \"linkedin_url\",\n                \"source_url\"\n              ]\n            }\n          }\n        },\n        \"required\": [\"companies\"]\n      }\n    }\n  }\n}\n",
        "sendBody": true,
        "specifyBody": "json",
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "perplexityApi"
      },
      "credentials": {
        "perplexityApi": {
          "id": "IRlUSoKLmF6KUhug",
          "name": "Perplexity account"
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "9b4c6512-e43b-4568-8bbb-144ef684bb5a",
      "name": "AI-Antwort parsen",
      "type": "n8n-nodes-base.code",
      "position": [
        416,
        -16
      ],
      "parameters": {
        "jsCode": "// Step 1: Get the raw content string\nlet rawContent = $input.first().json.choices[0].message.content;\n\n// Step 2: Try to extract JSON block inside ```json ... ```\nconst match = rawContent.match(/```json\\s*([\\s\\S]*?)\\s*```/i);\n\nif (match) {\n  rawContent = match[1];\n}\n\n// Step 3: Try to parse first-level JSON\nlet parsed;\ntry {\n  parsed = JSON.parse(rawContent);\n\n  // If it's an object with a \"companies\" key, extract the array\n  if (parsed && parsed.companies && Array.isArray(parsed.companies)) {\n    parsed = parsed.companies;\n  }\n  \n  // If it's still a string (stringified JSON inside JSON), parse again\n  if (typeof parsed === 'string') {\n    parsed = JSON.parse(parsed);\n\n    if (parsed && parsed.companies && Array.isArray(parsed.companies)) {\n      parsed = parsed.companies;\n    }\n  }\n\n  // If it's not an array at this point, return warning\n  if (!Array.isArray(parsed)) {\n    return [{\n      json: {\n        warning: \"Parsed result is not an array.\",\n        type: typeof parsed,\n        preview: JSON.stringify(parsed).slice(0, 300)\n      }\n    }];\n  }\n\n} catch (err) {\n  const message = err instanceof Error ? err.message : String(err);\n  return [{\n    json: {\n      error: \"Failed to parse JSON\",\n      message,\n      preview: rawContent.slice(0, 300),\n      rawLength: rawContent.length\n    }\n  }];\n}\n\n// Step 4: Return each company as a separate item\nreturn parsed.map(company => ({ json: company }));\n"
      },
      "typeVersion": 2
    },
    {
      "id": "7d2016fd-3385-4c78-a8f0-37b4dd5df146",
      "name": "Angereicherte Daten speichern",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        640,
        56
      ],
      "parameters": {
        "columns": {
          "value": {
            "city": "={{ $json.city }}",
            "phone": "={{ $json.phone.replace(\"+\", \"'+\") }}",
            "state": "={{ $json.state }}",
            "domain": "={{ $json.domain }}",
            "address": "={{ $json.address }}",
            "company": "={{ $json.company }}",
            "country": "={{ $json.country }}",
            "revenue": "={{ $json.revenue }}",
            "industry": "={{ $json.industry }}",
            "postCode": "={{ $json.postal_code }}",
            "employees": "={{ $json.employees }}",
            "processed": "true",
            "source_url": "={{ $json.source_url }}",
            "companyLinkedinUrl": "={{ $json.linkedin_url }}"
          },
          "schema": [
            {
              "id": "domain",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "domain",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "company",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "company",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "address",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "address",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "city",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "city",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "state",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "state",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "postCode",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "postCode",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "country",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "country",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "phone",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "phone",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "employees",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "employees",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "revenue",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "revenue",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "industry",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "industry",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "companyLinkedinUrl",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "companyLinkedinUrl",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "source_url",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "source_url",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "processed",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "processed",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "row_number",
              "type": "number",
              "display": true,
              "removed": true,
              "readOnly": true,
              "required": false,
              "displayName": "row_number",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "domain"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "update",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1737567569,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1bdK8xskt-qfLlDwdzolM0zFyo9KxZ-HHpTVxcEw3ZMY/edit#gid=1737567569",
          "cachedResultName": "Data"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1bdK8xskt-qfLlDwdzolM0zFyo9KxZ-HHpTVxcEw3ZMY",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1bdK8xskt-qfLlDwdzolM0zFyo9KxZ-HHpTVxcEw3ZMY/edit?usp=drivesdk",
          "cachedResultName": "Get Addresses"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "Y43YNvASDhLxJg65",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.6,
      "alwaysOutputData": true
    },
    {
      "id": "5ea0a567-0a88-4724-acc7-f654930c3ae7",
      "name": "Haftnotiz",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -304,
        -352
      ],
      "parameters": {
        "width": 896,
        "height": 656,
        "content": "🏢 COMPANY DATA ENRICHMENT SYSTEM\n━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n\n🎯 Automatically enriches company domains with detailed business data using Perplexity AI\n\n📊 PROCESS FLOW:\n1. Reads unprocessed domains from Google Sheets\n2. Batches domains (10 per request) for cost efficiency  \n3. Uses Perplexity AI to research German addresses + business data\n4. Parses AI response and saves enriched data back to sheets\n5. Marks domains as \"processed\" to avoid duplicates\n\n⏱️ Runtime: ~2-3 minutes per batch | 💰 Cost: ~$0.005 per 10 domains\n🔄 Resumable: Only processes unprocessed domains | 🌍 Focus: German addresses (can be customized to HQ addresses)"
      },
      "typeVersion": 1
    },
    {
      "id": "00024d69-8567-4c12-bf92-d54bb5686bd6",
      "name": "Haftnotiz1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -864,
        -352
      ],
      "parameters": {
        "color": 5,
        "width": 544,
        "height": 656,
        "content": "🚀 SETUP CHECKLIST\n━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n\n📋 GOOGLE SHEETS:\n🔗 [MAKE A COPY of this template](https://docs.google.com/spreadsheets/d/1bdK8xskt-qfLlDwdzolM0zFyo9KxZ-HHpTVxcEw3ZMY/edit?usp=sharing)\n⚠️ Update Sheet ID in both Google Sheets nodes after copying\n\n📝 Required columns: domain, processed\n📊 Tab: Data | Filter: processed = \"\" (empty only)\n\n🔐 API CREDENTIALS NEEDED:\n✅ Perplexity AI API key\n✅ Google Sheets OAuth2 authentication\n\n⚙️ Batch size: 10 domains (adjustable in \"Batch Process Domains\" node)"
      },
      "typeVersion": 1
    },
    {
      "id": "0609a728-9743-4a92-b64d-a63eec993333",
      "name": "Haftnotiz2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        608,
        -352
      ],
      "parameters": {
        "color": 4,
        "width": 544,
        "height": 656,
        "content": "📊 OUTPUT DATA FIELDS\n━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n\n✅ ENRICHED DATA SAVED:\n- Company name, complete address\n- Phone (international), employee count, revenue (USD)\n- Industry, LinkedIn URL, source URL\n- processed = \"true\" (tracking field)\n\n🛡️ ERROR HANDLING:\n- Parse failures → Detailed error info in output\n- Missing data → AI sets fields to null (no fake data)\n- Rate limiting → Reduce batch size if needed\n\n📈 MONITORING:\n- Check \"processed\" column for completion status\n- Review \"source_url\" for data reliability\n- Monitor costs in Perplexity dashboard"
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "bffa5202-a56e-4f05-8b01-e8c4329915f3": {
      "main": [
        [
          {
            "node": "bc1ce2a3-f4b0-4cbe-a406-49ac2e339924",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "9b4c6512-e43b-4568-8bbb-144ef684bb5a": {
      "main": [
        [
          {
            "node": "7d2016fd-3385-4c78-a8f0-37b4dd5df146",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "7d2016fd-3385-4c78-a8f0-37b4dd5df146": {
      "main": [
        [
          {
            "node": "e8d61653-67ef-45bd-8640-f3317c859516",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "e8d61653-67ef-45bd-8640-f3317c859516": {
      "main": [
        [],
        [
          {
            "node": "4946ef7e-852b-4860-bef0-aa44bc0c5221",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "4946ef7e-852b-4860-bef0-aa44bc0c5221": {
      "main": [
        [
          {
            "node": "9b4c6512-e43b-4568-8bbb-144ef684bb5a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "bc1ce2a3-f4b0-4cbe-a406-49ac2e339924": {
      "main": [
        [
          {
            "node": "e8d61653-67ef-45bd-8640-f3317c859516",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}