SEO Competitor Keyword Tracker via Decodo + Google Sheets + OpenAI GPT-4.1-mini

{
  "id": "kdSLYBTklfxlAMSS",
  "meta": {
    "instanceId": "885b4fb4a6a9c2cb5621429a7b972df0d05bb724c20ac7dac7171b62f1c7ef40",
    "templateCredsSetupCompleted": true
  },
  "name": "SEO Competitor Keyword Tracker via Decodo + Google Sheets + OpenAI GPT-4.1-mini",
  "tags": [
    {
      "id": "Kujft2FOjmOVQAmJ",
      "name": "Engineering",
      "createdAt": "2025-04-09T01:31:00.558Z",
      "updatedAt": "2025-04-09T01:31:00.558Z"
    },
    {
      "id": "ZOwtAMLepQaGW76t",
      "name": "Building Blocks",
      "createdAt": "2025-04-13T15:23:40.462Z",
      "updatedAt": "2025-04-13T15:23:40.462Z"
    },
    {
      "id": "ddPkw7Hg5dZhQu2w",
      "name": "AI",
      "createdAt": "2025-04-13T05:38:08.053Z",
      "updatedAt": "2025-04-13T05:38:08.053Z"
    }
  ],
  "nodes": [
    {
      "id": "ac0b8094-56aa-4b48-bf48-9e8f1456c2f5",
      "name": "Google Sheets Append Row",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        848,
        -272
      ],
      "parameters": {
        "options": {},
        "dataMode": "autoMapInputData",
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1v4vjL5K1tMDUyswcFaLPA1vraViwnPSbqxkVeld9Dk0/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1v4vjL5K1tMDUyswcFaLPA1vraViwnPSbqxkVeld9Dk0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1v4vjL5K1tMDUyswcFaLPA1vraViwnPSbqxkVeld9Dk0/edit?usp=drivesdk",
          "cachedResultName": "SEO Competitor Keyword Tracker via Decodo"
        },
        "columnToMatchOn": "keyword_density_summary"
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "Zjoxh2BUZ6VXGQhA",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "badc8b6c-e8fe-4342-8f05-a42c6c10e1f8",
      "name": "When clicking ‘Execute workflow’",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -496,
        -272
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "3dde33f7-77ae-45d3-85c3-3c7267c4d9ad",
      "name": "Decodo",
      "type": "@decodo/n8n-nodes-decodo.decodo",
      "position": [
        -48,
        -272
      ],
      "parameters": {
        "geo": "={{ $json.geo }}",
        "url": "={{ $json.url }}"
      },
      "credentials": {
        "decodoApi": {
          "id": "7xLvINFuwxDiyBde",
          "name": "Decodo Credentials account"
        }
      },
      "retryOnFail": true,
      "typeVersion": 1
    },
    {
      "id": "79541046-7915-4985-9fe7-4ac7663ed7fb",
      "name": "Set the Input Fields",
      "type": "n8n-nodes-base.set",
      "position": [
        -272,
        -272
      ],
      "parameters": {
        "values": {
          "string": [
            {
              "name": "url",
              "value": "https://dev.to"
            },
            {
              "name": "geo",
              "value": "france"
            }
          ]
        },
        "options": {
          "dotNotation": false
        }
      },
      "typeVersion": 1
    },
    {
      "id": "8f75d611-9e19-4b77-ae87-70981e542ed2",
      "name": "Analyze Keywords",
      "type": "@n8n/n8n-nodes-langchain.chainLlm",
      "position": [
        288,
        -272
      ],
      "parameters": {
        "text": "=You are an SEO analyzer. Analyze the following webpage content and return a concise JSON output.\n\nWeb page content:\n\n{{ $json.data.results[0].content }}\n\nOutput JSON:\n{\n  \"primary_keywords\": [ \"list of top 10 focus keywords\" ],\n  \"secondary_keywords\": [ \"list of supporting keywords and synonyms\" ],\n  \"keyword_density_summary\": \"summary of keyword usage and density trends\",\n  \"focus_topics\": [ \"main content themes or topic clusters\" ],\n  \"semantic_entities\": [ \"notable named entities or concepts detected\" ],\n  \"seo_strength_score\": 0-100,\n  \"readability_score\": 0-100,\n  \"content_type\": \"e.g., blog article, landing page, product page\",\n  \"target_audience\": \"intended audience segment inferred from content\",\n  \"content_tone\": \"tone style such as informative, persuasive, technical\",\n  \"optimization_recommendations\": [\n    \"specific and actionable SEO improvement suggestions\"\n  ],\n  \"keyword_opportunity_gaps\": [\n    \"topics or keywords competitors target but are missing here\"\n  ],\n  \"internal_link_suggestions\": [\n    \"suggested anchor text or internal link topics to boost SEO\"\n  ],\n  \"metadata_insights\": {\n    \"title_tag_length\": \"characters count and optimization status\",\n    \"meta_description_length\": \"characters count and optimization status\",\n    \"h1_presence\": true,\n    \"image_alt_tags\": \"status summary of alt tag usage\"\n  },\n  \"content_summary\": \"short plain-language summary of the webpage\",\n  \"sentiment\": \"overall sentiment of the content (positive/neutral/negative)\",\n  \"language_detected\": \"ISO language code or detected language name\",\n  \"last_analyzed\": \"timestamp of when analysis was performed\"\n}\n",
        "batching": {},
        "promptType": "define"
      },
      "retryOnFail": true,
      "typeVersion": 1.7,
      "alwaysOutputData": true
    },
    {
      "id": "22558b6c-c600-4fc6-9680-07272c3da1b4",
      "name": "OpenAI Chat Model for Keyword Analysis",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        368,
        -48
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4o-mini",
          "cachedResultName": "gpt-4o-mini"
        },
        "options": {}
      },
      "credentials": {
        "openAiApi": {
          "id": "vPKynKbDzJ5ZU4cU",
          "name": "OpenAi account"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "0300b558-8e4b-476f-a386-fcd7693cee42",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -496,
        -576
      ],
      "parameters": {
        "color": 7,
        "width": 384,
        "height": 272,
        "content": "![Logo](https://cdn.brandfetch.io/idIeG9_eXK/w/100/h/100/theme/dark/icon.jpeg?c=1bxid64Mup7aczewSAYMX&t=1756483136894)\n\nAutomatically scrapes competitor page using the Decodo Web Scraping API, extracts SEO keywords and metadata, summarizes keyword opportunities with OpenAI GPT-4.1-mini, and stores the structured data in Google Sheets for trend tracking."
      },
      "typeVersion": 1
    },
    {
      "id": "00428ffe-a747-4a23-9a2a-65ece9b98c17",
      "name": "Extract Structured JSON",
      "type": "n8n-nodes-base.code",
      "position": [
        624,
        -272
      ],
      "parameters": {
        "jsCode": "let text =  $input.first().json.text;\nconst output = [];\n\n// Remove ```json ... ``` or ``` ... ``` wrappers\ntext = text\n      .replace(/```json\\s*/gi, '')\n      .replace(/```/g, '')\n      .trim();\n\n// Parse the cleaned JSON text\nconst parsed = JSON.parse(text);\noutput.push({ json: parsed });\n\nreturn output;"
      },
      "typeVersion": 2
    },
    {
      "id": "f8185be1-060e-47b4-8ab7-6dd312121c4e",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -496,
        -96
      ],
      "parameters": {
        "width": 704,
        "height": 640,
        "content": "**Purpose:**\n\nAutomates competitive SEO research by scraping competitor pages, analyzing keyword patterns using GPT, and storing structured insights in Google Sheets.\n\n**Flow Summary:**\n1️⃣ **Trigger:** Start manually or schedule via Cron.\n2️⃣ **Set Input Fields:** Define target `url` and `geo`.\n3️⃣ **Decodo Node:** Extract webpage text and metadata.\n4️⃣ **GPT-4.1-mini (LangChain):**\n • Analyzes content for SEO strength, keywords, tone, and topics.\n • Returns structured JSON insights.\n5️⃣ **Code Node:** Cleans and parses GPT JSON.\n6️⃣ **Google Sheets Node:** Appends analyzed data for tracking and visualization.\n\n**Key Metrics Captured:**\n\n* `primary_keywords`\n* `focus_topics`\n* `keyword_density_summary`\n* `seo_strength_score`\n* `readability_score`\n* `keyword_opportunity_gaps`\n* `content_summary`\n* `metadata_insights` (title, meta, h1, alt tags)\n\n**Integrations Used:**\n\n* **Decodo API:** Web content extraction\n* **OpenAI GPT-4.1-mini:** SEO analysis\n* **Google Sheets:** Storage & trend tracking\n\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "7ab1d533-a5a7-47a8-81e3-106a0bce85db",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        224,
        -400
      ],
      "parameters": {
        "color": 4,
        "width": 560,
        "height": 496,
        "content": "## Structured Data Extractor\n"
      },
      "typeVersion": 1
    },
    {
      "id": "b8f6a584-5ae0-4954-8bc7-e95f576809cd",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        800,
        -400
      ],
      "parameters": {
        "color": 3,
        "width": 272,
        "height": 320,
        "content": "## Export Data Handling"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "fbea6b00-a703-494b-bc91-302df44718b8",
  "connections": {
    "Decodo": {
      "main": [
        [
          {
            "node": "Analyze Keywords",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Analyze Keywords": {
      "main": [
        [
          {
            "node": "Extract Structured JSON",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set the Input Fields": {
      "main": [
        [
          {
            "node": "Decodo",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Extract Structured JSON": {
      "main": [
        [
          {
            "node": "Google Sheets Append Row",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "When clicking ‘Execute workflow’": {
      "main": [
        [
          {
            "node": "Set the Input Fields",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "OpenAI Chat Model for Keyword Analysis": {
      "ai_languageModel": [
        [
          {
            "node": "Analyze Keywords",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    }
  }
}