The Automation Wing
Search Console weekly movers: the queries that gained and lost, emailed every Monday
A weekly n8n workflow that pulls the last 28 days of Search Console clicks against the 28 before, writes the biggest query winners and losers to a Google Sheet, and emails the top movers.
Search Console will happily show you last month's top queries, but it is coy about change. The question that actually moves work forward is not "what ranks?" but "what moved?": which queries picked up clicks, which quietly fell away, and by how much. Answering it by hand means two exports and a spreadsheet every week, which is precisely the sort of chore that stops getting done by February.
This workflow does the two exports and the arithmetic for you. Once a week it asks the Search Console API for query clicks over the last 28 days and over the 28 days before that, works out the difference per query, appends the winners and losers to a Google Sheet so you keep a history, and emails you the top movers. It only reads Search Console and appends to a sheet; it changes nothing about your site or your search presence.
Before you start
- Know your property's exact URL as Search Console stores it, including the trailing slash for a URL-prefix property (for example
https://example.com/) or thesc-domain:example.comform for a Domain property. The API is fussy about this. - The two HTTP nodes use a Google OAuth2 credential with the
webmasters.readonlyscope. You attach it after import; nothing sensitive travels inside the file. - Have a Google Sheet ready with a header row, and a Google Sheets credential for the append step.
The workflow
The two API calls hit the Search Console searchanalytics/query endpoint:
POST https://www.googleapis.com/webmasters/v3/sites/{SITE_URL}/searchAnalytics/query
with a JSON body of startDate, endDate, dimensions: ["query"] and a rowLimit. Import the JSON below, then set your dates, site URL, sheet and credentials.
{
"name": "Search Console weekly movers",
"nodes": [
{
"parameters": {
"rule": {
"interval": [
{
"field": "weeks",
"triggerAtDay": [1],
"triggerAtHour": 8
}
]
}
},
"name": "Every Monday",
"type": "n8n-nodes-base.scheduleTrigger",
"typeVersion": 1.2,
"position": [220, 300]
},
{
"parameters": {
"method": "POST",
"url": "https://www.googleapis.com/webmasters/v3/sites/https%3A%2F%2Fexample.com%2F/searchAnalytics/query",
"authentication": "predefinedCredentialType",
"nodeCredentialType": "googleOAuth2Api",
"sendBody": true,
"specifyBody": "json",
"jsonBody": "={\n \"startDate\": \"{{ $now.minus({ days: 28 }).toFormat('yyyy-MM-dd') }}\",\n \"endDate\": \"{{ $now.minus({ days: 1 }).toFormat('yyyy-MM-dd') }}\",\n \"dimensions\": [\"query\"],\n \"rowLimit\": 1000\n}",
"options": {}
},
"name": "Last 28 days",
"type": "n8n-nodes-base.httpRequest",
"typeVersion": 4.2,
"position": [460, 300]
},
{
"parameters": {
"method": "POST",
"url": "https://www.googleapis.com/webmasters/v3/sites/https%3A%2F%2Fexample.com%2F/searchAnalytics/query",
"authentication": "predefinedCredentialType",
"nodeCredentialType": "googleOAuth2Api",
"sendBody": true,
"specifyBody": "json",
"jsonBody": "={\n \"startDate\": \"{{ $now.minus({ days: 56 }).toFormat('yyyy-MM-dd') }}\",\n \"endDate\": \"{{ $now.minus({ days: 29 }).toFormat('yyyy-MM-dd') }}\",\n \"dimensions\": [\"query\"],\n \"rowLimit\": 1000\n}",
"options": {}
},
"name": "Previous 28 days",
"type": "n8n-nodes-base.httpRequest",
"typeVersion": 4.2,
"position": [700, 300]
},
{
"parameters": {
"jsCode": "// Both API responses carry a `rows` array of { keys: [query], clicks, ... }.\nconst current = $('Last 28 days').first().json.rows || [];\nconst previous = $('Previous 28 days').first().json.rows || [];\n\nconst prevByQuery = {};\nfor (const r of previous) prevByQuery[r.keys[0]] = r.clicks;\n\nconst rows = [];\nfor (const r of current) {\n const query = r.keys[0];\n const now = r.clicks;\n const before = prevByQuery[query] || 0;\n rows.push({ query, clicksNow: now, clicksBefore: before, delta: now - before });\n}\nfor (const [query, before] of Object.entries(prevByQuery)) {\n if (!current.find(r => r.keys[0] === query)) {\n rows.push({ query, clicksNow: 0, clicksBefore: before, delta: -before });\n }\n}\n\nrows.sort((a, b) => b.delta - a.delta);\nconst movers = [...rows.slice(0, 10), ...rows.slice(-10)];\nconst runDate = $now.toFormat('yyyy-MM-dd');\n\nreturn movers.map(m => ({ json: { runDate, ...m } }));"
},
"name": "Compare clicks by query",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [940, 300]
},
{
"parameters": {
"operation": "append",
"documentId": {
"__rl": true,
"value": "REPLACE-ME-SHEET-ID",
"mode": "id"
},
"sheetName": {
"__rl": true,
"value": "Movers",
"mode": "name"
},
"columns": {
"mappingMode": "autoMapInputData",
"value": {}
},
"options": {}
},
"name": "Append to Google Sheets",
"type": "n8n-nodes-base.googleSheets",
"typeVersion": 4.5,
"position": [1180, 300]
},
{
"parameters": {
"fromEmail": "reports@example.com",
"toEmail": "you@example.com",
"subject": "Search Console: top query movers this week",
"emailFormat": "text",
"text": "={{ $items().map(i => `${i.json.delta >= 0 ? '+' : ''}${i.json.delta} ${i.json.query}`).join('\\n') }}",
"options": {}
},
"name": "Email the top movers",
"type": "n8n-nodes-base.emailSend",
"typeVersion": 2.1,
"executeOnce": true,
"position": [1420, 300]
}
],
"connections": {
"Every Monday": {
"main": [[{ "node": "Last 28 days", "type": "main", "index": 0 }]]
},
"Last 28 days": {
"main": [[{ "node": "Previous 28 days", "type": "main", "index": 0 }]]
},
"Previous 28 days": {
"main": [[{ "node": "Compare clicks by query", "type": "main", "index": 0 }]]
},
"Compare clicks by query": {
"main": [[{ "node": "Append to Google Sheets", "type": "main", "index": 0 }]]
},
"Append to Google Sheets": {
"main": [[{ "node": "Email the top movers", "type": "main", "index": 0 }]]
}
}
}
Set it up
- Import the JSON and open the workflow.
- In both HTTP nodes, replace the URL-encoded site in the path with your own property. A URL-prefix property is URL-encoded (
https%3A%2F%2Fexample.com%2F); a Domain property issc-domain%3Aexample.com. - Attach your Google OAuth2 credential (Search Console scope) to both HTTP nodes.
- In "Append to Google Sheets", set the document ID and tab name, and attach your Sheets credential. A header row of runDate, query, clicksNow, clicksBefore, delta matches the appended fields.
- In "Email the top movers", attach your SMTP credential and set the addresses. Run once by hand to confirm the sheet fills and the email arrives.
How to read the movers, and its honest edges
The email lists the ten biggest risers and the ten biggest fallers by clicks, signed so a plus is a gain. The sheet keeps every week's list, so over a couple of months you can see whether a fall was a blip or a trend. Treat clicks, not position, as the headline here: position averages hide as much as they show, whereas a query that lost clicks lost real visits.
Two limitations worth stating plainly. Search Console data settles over two to three days, so the workflow reads yesterday back, not today, and a very recent week is still firming up. And the API anonymises low-volume queries, so the long tail is under-counted on both sides of the comparison; the movers you see are the ones with enough volume to be reported, which is usually the ones worth acting on anyway.