Daily Monitor Position with Google sheet queries

{
  "id": "vcSpTA8efH86xwYi",
  "meta": {
    "instanceId": "c24388df44432e8ff2c4acecd7ab0dd2faec628bd83c70beb384cea105f7a50a",
    "templateCredsSetupCompleted": true
  },
  "name": "Daily Monitor Position with Google sheet queries",
  "tags": [
    {
      "id": "RhAmCaLYc9EkF42I",
      "name": "n8n",
      "createdAt": "2025-08-24T08:04:35.027Z",
      "updatedAt": "2025-08-24T08:04:35.027Z"
    }
  ],
  "nodes": [
    {
      "id": "12b77eba-974e-438f-bed3-d282010a51fe",
      "name": "Schedule Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -120,
        -180
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "triggerAtHour": 8
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "30693f14-7ea0-41e9-8a55-70279c1ff4c9",
      "name": "Split Out",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        60,
        260
      ],
      "parameters": {
        "options": {},
        "fieldToSplitOut": "tasks[0].result"
      },
      "typeVersion": 1
    },
    {
      "id": "8700a445-a671-4ccf-ab5d-b49581999bc2",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        120,
        -560
      ],
      "parameters": {
        "color": 5,
        "width": 280,
        "height": 720,
        "content": "## 📄 **Fetch Keyword List (Google Sheets)**\n\nReads your keyword list directly from Google Sheets.  \n👉 Example sheet: [Keyword List](https://docs.google.com/spreadsheets/d/1ShdLc4td6MSQf49l4tDlVohRlFxNO0SdkG0bHQ5LJmE/edit?usp=sharing)\n\nEach row should include a `query` column — that’s the keyword to check daily.  \nYou can also add extra columns like `country` or `device` if needed later.\n\n⚙️ Make sure:\n- The sheet name matches the one in this node.  \n- Your Google account is connected and has edit access.  \n- Column headers are spelled exactly as expected.\n\n💡 This node provides the input list that DataForSEO will process next.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "74506eb2-5e79-46a7-b484-00041afe6743",
      "name": "Fetch Keyword List (Google Sheets)",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        220,
        -40
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1ShdLc4td6MSQf49l4tDlVohRlFxNO0SdkG0bHQ5LJmE/edit#gid=0",
          "cachedResultName": "queries"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1ShdLc4td6MSQf49l4tDlVohRlFxNO0SdkG0bHQ5LJmE",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1ShdLc4td6MSQf49l4tDlVohRlFxNO0SdkG0bHQ5LJmE/edit?usp=drivesdk",
          "cachedResultName": "Daily Monitor Position | n8n"
        },
        "authentication": "serviceAccount"
      },
      "credentials": {
        "googleApi": {
          "id": "A3ebjIB2M27srsju",
          "name": "Nima40"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "bed29055-a0f1-4739-b65b-6d8b612e39f0",
      "name": "Fetch SERP Data (DataForSEO API)",
      "type": "n8n-nodes-dataforseo.dataForSeo",
      "position": [
        660,
        0
      ],
      "parameters": {
        "depth": 10,
        "keyword": "={{ $json.query }}",
        "resource": "serp",
        "language_name": "English",
        "location_name": "United States"
      },
      "credentials": {
        "dataForSeoApi": {
          "id": "GxoHZ00Kb6B5qP12",
          "name": "DataForSEO account"
        }
      },
      "executeOnce": false,
      "typeVersion": 1
    },
    {
      "id": "73c65e58-ed21-4c88-8abf-7ec37b61c38b",
      "name": "Extract Query, Rank & Domain",
      "type": "n8n-nodes-base.code",
      "position": [
        280,
        260
      ],
      "parameters": {
        "jsCode": "// Get all DataForSEO results\nconst results = $input.all();\n\n// Prepare flattened array\nconst rows = [];\n\nfor (const res of results) {\n  const keyword = res.json.keyword;\n  const items = res.json.items || [];\n\n  for (const item of items) {\n    // Only take organic results\n    if (item.type === \"organic\") {\n      rows.push({\n        query: keyword,\n        rank: item.rank_group,\n        domain: item.domain\n      });\n    }\n  }\n}\n\n// Output flattened array for next node (Google Sheets)\nreturn rows.map(r => ({ json: r }));\n"
      },
      "typeVersion": 2
    },
    {
      "id": "3d158ce9-c0a5-47bf-92b8-b41c4bb71b0a",
      "name": "Add Timestamp & Prepare Output",
      "type": "n8n-nodes-base.set",
      "position": [
        500,
        260
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "563b4310-e127-4dea-905b-dcbc7c6236d2",
              "name": "query",
              "type": "string",
              "value": "={{ $json.query }}"
            },
            {
              "id": "ccbaa0ef-229a-447e-b524-5cf096a98cc9",
              "name": "rank",
              "type": "string",
              "value": "={{ $json.rank }}"
            },
            {
              "id": "1b6d2b30-9118-41b7-982d-67e2e89cd668",
              "name": "domain",
              "type": "string",
              "value": "={{ $json.domain }}"
            },
            {
              "id": "9718ada3-6127-4c73-8653-590fc99e88b2",
              "name": "date",
              "type": "string",
              "value": "={{ new Date().toISOString().split('T')[0] }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "d21074e7-2552-42c7-816b-79da4e9960fa",
      "name": "Append Results to Google Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        740,
        560
      ],
      "parameters": {
        "columns": {
          "value": {
            "date": "={{ $json.date }}",
            "rank": "={{ $json.rank }}",
            "query": "={{ $json.query }}",
            "domain": "={{ $json.domain }}"
          },
          "schema": [
            {
              "id": "query",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "query",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "rank",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "rank",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "domain",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "domain",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "date",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1022700330,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1ShdLc4td6MSQf49l4tDlVohRlFxNO0SdkG0bHQ5LJmE/edit#gid=1022700330",
          "cachedResultName": "rank"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1ShdLc4td6MSQf49l4tDlVohRlFxNO0SdkG0bHQ5LJmE",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1ShdLc4td6MSQf49l4tDlVohRlFxNO0SdkG0bHQ5LJmE/edit?usp=drivesdk",
          "cachedResultName": "Daily Monitor Position | n8n"
        },
        "authentication": "serviceAccount"
      },
      "credentials": {
        "googleApi": {
          "id": "A3ebjIB2M27srsju",
          "name": "Nima40"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "f14d433f-4a51-4bf7-95fa-a616a788e4d5",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -220,
        -400
      ],
      "parameters": {
        "color": 5,
        "width": 280,
        "height": 400,
        "content": "## 🕒 **Daily Schedule Trigger**\n\nStarts the workflow automatically `every day`.\n  \n⚠️ Adjust the time or interval in node settings.  "
      },
      "typeVersion": 1
    },
    {
      "id": "5aeafcc6-98e9-4cd8-8c66-184ebb882cc0",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        500,
        -560
      ],
      "parameters": {
        "color": 5,
        "width": 440,
        "height": 720,
        "content": "## 🔍 **Fetch SERP Data (DataForSEO API)**\n\nUses the DataForSEO API to retrieve Google Search results for each keyword.  \nPulls details like `rank`, `domain`, `title`, and `url` for the top results.\n\n⚙️ Configuration notes:\n- `request_type`: `serp` → Google Organic  \n- `location_code`: `2840` → United States (🌎 changeable)  \n- `language_code`: `en` → English (🗣️ changeable)  \n- `se_domain`: `google.com`\n\n💡 You can adjust **location** or **language** in this node to track regional rankings  \n(e.g., `location_code: 2826` for the UK, `language_code: fr` for French).\n\n📦 DataForSEO returns a rich JSON with multiple result types  \n(`organic`, `people_also_ask`, `video`, etc.) —  \nwe’ll filter only the `organic` items in the next step.\n\n⚠️ Each keyword consumes one API credit — monitor your usage regularly.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "61d504d9-512b-4aa9-acfc-6b257c6e5fbd",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        40,
        440
      ],
      "parameters": {
        "color": 5,
        "width": 900,
        "height": 300,
        "content": "## 📊 **Append Results to Google Sheet**\n\nWrites the processed data (`query`, `rank`, `domain`, `date`) into your Google Sheet.  \n👉 Output Sheet: [Daily Rank Tracker](https://docs.google.com/spreadsheets/d/1ShdLc4td6MSQf49l4tDlVohRlFxNO0SdkG0bHQ5LJmE/edit?usp=sharing)\n\n⚙️ What this node does:\n- Appends new rows every time the workflow runs.  \n- Columns must match exactly: `query`, `rank`, `domain`, `date`.  \n- Builds a growing history of keyword rankings for long-term tracking.\n\n💡 You can use this sheet for reports, trend charts, or connect it  \nto **Looker Studio (Data Studio)** for automated SEO dashboards."
      },
      "typeVersion": 1
    },
    {
      "id": "ecbc3949-16b9-4291-9e98-8c7e6c18d33d",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        980,
        280
      ],
      "parameters": {
        "color": 4,
        "width": 780,
        "height": 460,
        "content": "## 📋 **Sample Output (Google Sheet)**\n\nExample of how results are saved daily:\n\n| query | rank | domain | date |\n|--------|-------|------------------------|----------------|\n| cloud host | 1 | cloudhost.one | 2025-10-24 |\n| cloud host | 2 | cloud.google.com | 2025-10-24 |\n| cloud host | 3 | aws.amazon.com | 2025-10-24 |\n| cloud host | 4 | www.hostinger.com | 2025-10-24 |\n| cloud host | 5 | www.pcmag.com | 2025-10-24 |\n| cloud host | 6 | www.cloudways.com | 2025-10-24 |\n| cloud host | 7 | www.vultr.com | 2025-10-24 |\n| cloud host | 8 | azure.microsoft.com | 2025-10-24 |\n| cloud hosting | 1 | cloud.google.com | 2025-10-24 |\n| cloud hosting | 2 | aws.amazon.com | 2025-10-24 |\n| cloud hosting | 3 | www.hostinger.com | 2025-10-24 |\n| cloud hosting | 4 | www.cloudflare.com | 2025-10-24 |\n| cloud hosting | 5 | www.vultr.com | 2025-10-24 |\n| cloud hosting | 8 | www.pcmag.com | 2025-10-24 |\n| cloud hosting | 10 | cloud.google.com | 2025-10-24 |\n| cloud hosting | 11 | www.cloudways.com | 2025-10-24 |\n\n📄 Each workflow run appends new rows like this to your  \n[Google Sheet](https://docs.google.com/spreadsheets/d/1ShdLc4td6MSQf49l4tDlVohRlFxNO0SdkG0bHQ5LJmE/edit?usp=sharing)"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "5413dda5-1d8f-4d68-9322-f5e8fd06be69",
  "connections": {
    "Split Out": {
      "main": [
        [
          {
            "node": "Extract Query, Rank & Domain",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Schedule Trigger": {
      "main": [
        [
          {
            "node": "Fetch Keyword List (Google Sheets)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Extract Query, Rank & Domain": {
      "main": [
        [
          {
            "node": "Add Timestamp & Prepare Output",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Add Timestamp & Prepare Output": {
      "main": [
        [
          {
            "node": "Append Results to Google Sheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch SERP Data (DataForSEO API)": {
      "main": [
        [
          {
            "node": "Split Out",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch Keyword List (Google Sheets)": {
      "main": [
        [
          {
            "node": "Fetch SERP Data (DataForSEO API)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}