Workflow: 0657_Splitout_Schedule_Update_Webhook
Integrations: googleSheets, httpRequest, scheduleTrigger, set, splitOut, stickyNote

{"nodes": [{"id": "8e3f167d-cbeb-4f7f-a867-c356d2dca9d0", "name": "Split Out", "type": "n8n-nodes-base.splitOut", "position": [1580, 240], "parameters": {"options": {}, "fieldToSplitOut": "rows"}, "typeVersion": 1}, {"id": "19370d12-f6de-44a1-91a6-da097abdf7de", "name": "Edit Fields", "type": "n8n-nodes-base.set", "position": [1780, 240], "parameters": {"options": {}, "assignments": {"assignments": [{"id": "7343c80f-37f3-4bb5-84d8-9f21f8a350cd", "name": "Keyword", "type": "string", "value": "={{ $json.keys[0] }}"}, {"id": "436e7c8b-2df2-40a9-97af-597dc00cf143", "name": "clicks", "type": "number", "value": "={{ $json.clicks }}"}, {"id": "5b4aaffe-391a-4c9d-8249-f447397a3f5d", "name": "impressions", "type": "number", "value": "={{ $json.impressions }}"}, {"id": "33677237-57fe-48f4-aff8-72ae81b5f5a2", "name": "ctr", "type": "number", "value": "={{ $json.ctr }}"}, {"id": "f961deee-d222-4df7-a7ff-b7286405e4a7", "name": "position", "type": "number", "value": "={{ $json.position }}"}]}}, "typeVersion": 3.4}, {"id": "9eae4908-5266-439c-a66b-5679036234de", "name": "Split Out1", "type": "n8n-nodes-base.splitOut", "position": [1580, 440], "parameters": {"options": {}, "fieldToSplitOut": "rows"}, "typeVersion": 1}, {"id": "b05926b1-507f-4531-a05c-a15e835ee82e", "name": "Edit Fields1", "type": "n8n-nodes-base.set", "position": [1780, 440], "parameters": {"options": {}, "assignments": {"assignments": [{"id": "7343c80f-37f3-4bb5-84d8-9f21f8a350cd", "name": "page", "type": "string", "value": "={{ $json.keys[0] }}"}, {"id": "436e7c8b-2df2-40a9-97af-597dc00cf143", "name": "clicks", "type": "number", "value": "={{ $json.clicks }}"}, {"id": "5b4aaffe-391a-4c9d-8249-f447397a3f5d", "name": "impressions", "type": "number", "value": "={{ $json.impressions }}"}, {"id": "33677237-57fe-48f4-aff8-72ae81b5f5a2", "name": "ctr", "type": "number", "value": "={{ $json.ctr }}"}, {"id": "f961deee-d222-4df7-a7ff-b7286405e4a7", "name": "position", "type": "number", "value": "={{ $json.position }}"}]}}, "typeVersion": 3.4}, {"id": "42321587-2565-4a0a-9d9d-25cbfdeb9f49", "name": "Split Out2", "type": "n8n-nodes-base.splitOut", "position": [1580, 620], "parameters": {"options": {}, "fieldToSplitOut": "rows"}, "typeVersion": 1}, {"id": "9e25eef9-daa4-47dd-b2cf-03cfebadb5c6", "name": "Edit Fields2", "type": "n8n-nodes-base.set", "position": [1780, 620], "parameters": {"options": {}, "assignments": {"assignments": [{"id": "7343c80f-37f3-4bb5-84d8-9f21f8a350cd", "name": "date", "type": "string", "value": "={{ $json.keys[0] }}"}, {"id": "436e7c8b-2df2-40a9-97af-597dc00cf143", "name": "clicks", "type": "number", "value": "={{ $json.clicks }}"}, {"id": "5b4aaffe-391a-4c9d-8249-f447397a3f5d", "name": "impressions", "type": "number", "value": "={{ $json.impressions }}"}, {"id": "33677237-57fe-48f4-aff8-72ae81b5f5a2", "name": "ctr", "type": "number", "value": "={{ $json.ctr }}"}, {"id": "f961deee-d222-4df7-a7ff-b7286405e4a7", "name": "position", "type": "number", "value": "={{ $json.position }}"}]}}, "typeVersion": 3.4}, {"id": "e8f1ab65-9594-45e7-ba9e-7873bd53a107", "name": "date", "type": "n8n-nodes-base.httpRequest", "position": [1360, 620], "parameters": {"url": "=https://www.googleapis.com/webmasters/v3/sites/sc-domain:{{$json.domain}}/searchAnalytics/query", "method": "POST", "options": {}, "jsonBody": "={\n  \"startDate\": \"{{ $now.format('yyyy-MM-dd') }}\",\n  \"endDate\": \"{{ $now.minus($json.days, 'days').format('yyyy-MM-dd') }}\",\n  \"dimensions\": [\"date\"]\n}", "sendBody": true, "specifyBody": "json", "authentication": "predefinedCredentialType", "nodeCredentialType": "googleOAuth2Api"}, "credentials": {"googleApi": {"id": "9vSHyulYjxYMr8MK", "name": "Service Account\u2705"}, "httpHeaderAuth": {"id": "Ng5SZdTqwe74l2KO", "name": "Header Auth account \u26a0\ufe0f"}, "googleOAuth2Api": {"id": "wuKNLprxCMuetOYN", "name": "Google account\u27053"}}, "typeVersion": 4.2}, {"id": "d3bbf719-9524-4269-8c26-0eb7599add55", "name": "Schedule Trigger", "type": "n8n-nodes-base.scheduleTrigger", "position": [700, 460], "parameters": {"rule": {"interval": [{}]}}, "typeVersion": 1.2}, {"id": "69cf781d-7ff5-4e2d-ad7d-505a5143710a", "name": "Sticky Note", "type": "n8n-nodes-base.stickyNote", "position": [1220, 160], "parameters": {"color": 4, "width": 1033, "height": 660, "content": ""}, "typeVersion": 1}, {"id": "b701bc62-07e7-4494-a674-560846783a29", "name": "Sticky Note1", "type": "n8n-nodes-base.stickyNote", "position": [0, 100], "parameters": {"color": 4, "width": 645, "height": 828, "content": "\n## Usage\n\n1. Make a copy of this google sheet https://docs.google.com/spreadsheets/d/10hSuGOOf14YvVY2Bw8WXUIpsyXO614l7qNEjkyVY_Qg/edit?usp=sharing\n\n2. Set your google service credentials and add these scopes `https://www.googleapis.com/auth/webmasters, https://www.googleapis.com/auth/webmasters.readonly, https://www.googleapis.com/auth/adwords`\n\n3. Replace the domains with your desired domains\n\n\n1. **Understanding the Workflow:**\n- **Nodes Overview:**\nThis workflow contains several nodes:\n- **Set your domain:** Sets the domain to be used in the queries.\n- **Schedule Trigger:** Starts the workflow based on a defined schedule.\n- **HttpRequest (query, page, date):** Fetches data from Google's Search Console API using specified dimensions and dates.\n- **Split Out (x3):** Splits the incoming JSON array into individual items for further processing.\n- **Edit Fields (x3):** Maps the outgoing data to specified fields, preparing it for insertion into Google Sheets.\n- **Google Sheets (x3):** Adds or updates entries in specified Google Sheets documents with the fetched data.\n\n- **Inputs and Outputs:**\n- Input: API response from Google Search Console regarding keywords, page data, and date data.\n- Output: Entries written to Google Sheets containing keyword data, clicks, impressions, CTR, and positions.\n\n2. **Setup Instructions:**\n- **Prerequisites:**\n- An n8n instance set up and running.\n- Active Google Account with access to Google Search Console and Google Sheets.\n- Google OAuth 2.0 credentials for API access.\n\n- **Step-by-Step Setup:**\n1. Open n8n and create a new workflow.\n2. Add the nodes as described in the JSON.\n3. Configure the **Google OAuth2** credentials in n8n to enable API access.\n4. Set your domain in the **Set your domain** node.\n5. Customize the Google Sheets document URLs to your personal sheets.\n6. Adjust the schedule in the **Schedule Trigger** node as per your requirements.\n7. Save the workflow.\n\n- **Configuration Options:**\n- You can customize the date ranges in the body of the **HttpRequest** nodes.\n- Adjust any fields in the **Edit Fields** nodes based on different data requirements.\n\n3. **Use Case Examples:**\n- Useful in tracking website performance over time using Search Console metrics.\n- Ideal for digital marketers, SEO specialists, and web analytics professionals.\n- Offers value in compiling performance reports for stakeholders or team reviews.\n\n4. **Running and Troubleshooting:**\n- **Running the Workflow:**\n- Trigger the workflow manually or wait for the schedule to run it automatically.\n\n- **Monitoring Execution:**\n- Check the execution logs in n8n's dashboard to ensure all nodes complete successfully.\n\n- **Common Issues:**\n- Invalid OAuth credentials \u2013 ensure credentials are set up correctly.\n- Incorrect Google Sheets URLs \u2013 double-check document links and permissions.\n- Scheduling conflicts \u2013 make sure the schedule set does not overlap with other workflows.\n"}, "typeVersion": 1}, {"id": "07432897-f068-4371-9f88-d70340e2082a", "name": "Sticky Note17", "type": "n8n-nodes-base.stickyNote", "position": [1440, 100], "parameters": {"color": 4, "width": 503.60808870324274, "height": 80, "content": "# Search console REPORTS"}, "typeVersion": 1}, {"id": "092645b2-9e75-4ff0-8d33-4a3acadac789", "name": "Set your domain", "type": "n8n-nodes-base.set", "position": [980, 460], "parameters": {"options": {}, "assignments": {"assignments": [{"id": "6f74dee0-3789-433e-b60e-ed2a05202675", "name": "domain", "type": "string", "value": "funautomations.io"}, {"id": "8c73135e-9d39-4f66-821d-7decb3c64085", "name": "days", "type": "number", "value": 30}]}}, "typeVersion": 3.4}, {"id": "0b04b552-e484-417b-9a7e-a90d477dd45a", "name": "Get query Report", "type": "n8n-nodes-base.httpRequest", "position": [1360, 240], "parameters": {"url": "=https://www.googleapis.com/webmasters/v3/sites/sc-domain:{{$json.domain}}/searchAnalytics/query", "method": "POST", "options": {}, "jsonBody": "={\n  \"startDate\": \"{{ $now.format('yyyy-MM-dd') }}\",\n  \"endDate\": \"{{ $now.minus($json.days, 'days').format('yyyy-MM-dd') }}\",\n  \"dimensions\": [\"query\"]\n}", "sendBody": true, "specifyBody": "json", "authentication": "predefinedCredentialType", "nodeCredentialType": "googleOAuth2Api"}, "credentials": {"httpHeaderAuth": {"id": "Ng5SZdTqwe74l2KO", "name": "Header Auth account \u26a0\ufe0f"}, "googleOAuth2Api": {"id": "SlPOQ6j86r5XbnxV", "name": "Oath account \u27055"}}, "typeVersion": 4.2}, {"id": "9f9f2be7-1301-4c91-8da1-86eab5725683", "name": "Get Page Report", "type": "n8n-nodes-base.httpRequest", "position": [1360, 440], "parameters": {"url": "=https://www.googleapis.com/webmasters/v3/sites/sc-domain:{{$json.domain}}/searchAnalytics/query", "method": "POST", "options": {}, "jsonBody": "={\n  \"startDate\": \"{{ $now.format('yyyy-MM-dd') }}\",\n  \"endDate\": \"{{ $now.minus($json.days, 'days').format('yyyy-MM-dd') }}\",\n  \"dimensions\": [\"page\"]\n}", "sendBody": true, "specifyBody": "json", "authentication": "predefinedCredentialType", "nodeCredentialType": "googleOAuth2Api"}, "credentials": {"httpHeaderAuth": {"id": "Ng5SZdTqwe74l2KO", "name": "Header Auth account \u26a0\ufe0f"}, "googleOAuth2Api": {"id": "wuKNLprxCMuetOYN", "name": "Google account\u27053"}}, "typeVersion": 4.2}, {"id": "737f802f-4629-41f2-9b21-4a98e92d6433", "name": "Sticky Note2", "type": "n8n-nodes-base.stickyNote", "position": [880, 380], "parameters": {"color": 4, "width": 300, "height": 300, "content": "## Set Domain and the days frequency"}, "typeVersion": 1}, {"id": "f8f62dde-1529-4d3a-a030-aa952496652d", "name": "Update queries to Sheets", "type": "n8n-nodes-base.googleSheets", "position": [1980, 240], "parameters": {"columns": {"value": {}, "schema": [{"id": "Keyword", "type": "string", "display": true, "removed": false, "required": false, "displayName": "Keyword", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "clicks", "type": "string", "display": true, "removed": false, "required": false, "displayName": "clicks", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "impressions", "type": "string", "display": true, "removed": false, "required": false, "displayName": "impressions", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "ctr", "type": "string", "display": true, "removed": false, "required": false, "displayName": "ctr", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "position", "type": "string", "display": true, "removed": false, "required": false, "displayName": "position", "defaultMatch": false, "canBeUsedToMatch": true}], "mappingMode": "autoMapInputData", "matchingColumns": ["Keyword"]}, "options": {}, "operation": "appendOrUpdate", "sheetName": {"__rl": true, "mode": "list", "value": 996986484, "cachedResultUrl": "https://docs.google.com/spreadsheets/d/10hSuGOOf14YvVY2Bw8WXUIpsyXO614l7qNEjkyVY_Qg/edit#gid=996986484", "cachedResultName": "Query"}, "documentId": {"__rl": true, "mode": "url", "value": "https://docs.google.com/spreadsheets/d/10hSuGOOf14YvVY2Bw8WXUIpsyXO614l7qNEjkyVY_Qg/edit?usp=sharing"}}, "credentials": {"googleSheetsOAuth2Api": {"id": "ZAI2a6Qt80kX5a9s", "name": "Google Sheets account\u2705 "}}, "typeVersion": 4.5}, {"id": "299c4fa9-fb7e-4c85-a8a5-3cea53ba7136", "name": "Update Pages to Sheets ", "type": "n8n-nodes-base.googleSheets", "position": [2000, 440], "parameters": {"columns": {"value": {}, "schema": [{"id": "page", "type": "string", "display": true, "removed": false, "required": false, "displayName": "page", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "clicks", "type": "string", "display": true, "removed": false, "required": false, "displayName": "clicks", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "impressions", "type": "string", "display": true, "removed": false, "required": false, "displayName": "impressions", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "ctr", "type": "string", "display": true, "removed": false, "required": false, "displayName": "ctr", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "position", "type": "string", "display": true, "removed": false, "required": false, "displayName": "position", "defaultMatch": false, "canBeUsedToMatch": true}], "mappingMode": "autoMapInputData", "matchingColumns": ["page"]}, "options": {}, "operation": "appendOrUpdate", "sheetName": {"__rl": true, "mode": "list", "value": "gid=0", "cachedResultUrl": "https://docs.google.com/spreadsheets/d/10hSuGOOf14YvVY2Bw8WXUIpsyXO614l7qNEjkyVY_Qg/edit#gid=0", "cachedResultName": "PAGES"}, "documentId": {"__rl": true, "mode": "url", "value": "https://docs.google.com/spreadsheets/d/10hSuGOOf14YvVY2Bw8WXUIpsyXO614l7qNEjkyVY_Qg/edit?usp=sharing"}}, "credentials": {"googleSheetsOAuth2Api": {"id": "ZAI2a6Qt80kX5a9s", "name": "Google Sheets account\u2705 "}}, "typeVersion": 4.5}, {"id": "4cc4197a-7ee5-4cd8-ade7-80bca911a3cf", "name": "Update date report to sheets", "type": "n8n-nodes-base.googleSheets", "position": [2000, 620], "parameters": {"columns": {"value": {}, "schema": [{"id": "date", "type": "string", "display": true, "removed": false, "required": false, "displayName": "date", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "clicks", "type": "string", "display": true, "removed": false, "required": false, "displayName": "clicks", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "impressions", "type": "string", "display": true, "removed": false, "required": false, "displayName": "impressions", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "ctr", "type": "string", "display": true, "removed": false, "required": false, "displayName": "ctr", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "position", "type": "string", "display": true, "removed": false, "required": false, "displayName": "position", "defaultMatch": false, "canBeUsedToMatch": true}], "mappingMode": "autoMapInputData", "matchingColumns": ["date"]}, "options": {}, "operation": "appendOrUpdate", "sheetName": {"__rl": true, "mode": "list", "value": 1823079319, "cachedResultUrl": "https://docs.google.com/spreadsheets/d/10hSuGOOf14YvVY2Bw8WXUIpsyXO614l7qNEjkyVY_Qg/edit#gid=1823079319", "cachedResultName": "Dates"}, "documentId": {"__rl": true, "mode": "url", "value": "https://docs.google.com/spreadsheets/d/10hSuGOOf14YvVY2Bw8WXUIpsyXO614l7qNEjkyVY_Qg/edit?usp=sharing"}}, "credentials": {"googleSheetsOAuth2Api": {"id": "ZAI2a6Qt80kX5a9s", "name": "Google Sheets account\u2705 "}}, "retryOnFail": true, "typeVersion": 4.5}], "pinData": {}, "connections": {"date": {"main": [[{"node": "Split Out2", "type": "main", "index": 0}]]}, "Split Out": {"main": [[{"node": "Edit Fields", "type": "main", "index": 0}]]}, "Split Out1": {"main": [[{"node": "Edit Fields1", "type": "main", "index": 0}]]}, "Split Out2": {"main": [[{"node": "Edit Fields2", "type": "main", "index": 0}]]}, "Edit Fields": {"main": [[{"node": "Update queries to Sheets", "type": "main", "index": 0}]]}, "Edit Fields1": {"main": [[{"node": "Update Pages to Sheets ", "type": "main", "index": 0}]]}, "Edit Fields2": {"main": [[{"node": "Update date report to sheets", "type": "main", "index": 0}]]}, "Get Page Report": {"main": [[{"node": "Split Out1", "type": "main", "index": 0}]]}, "Set your domain": {"main": [[{"node": "Get query Report", "type": "main", "index": 0}, {"node": "Get Page Report", "type": "main", "index": 0}, {"node": "date", "type": "main", "index": 0}]]}, "Get query Report": {"main": [[{"node": "Split Out", "type": "main", "index": 0}]]}, "Schedule Trigger": {"main": [[{"node": "Set your domain", "type": "main", "index": 0}]]}}}