Weekly GSC Metric Send (Incl. Brand vs NonBrand) - Generic

{
  "id": "91yA1R0n3iR9BtYC",
  "meta": {
    "instanceId": "bd3424651820da96219b3bf8bf1cdfabcd2b0cc2dbde58159a106ccfa63cca09",
    "templateCredsSetupCompleted": true
  },
  "name": "Weekly GSC Metric Send (Incl. Brand vs NonBrand) - Generic",
  "tags": [
    {
      "id": "10",
      "name": "SEO",
      "createdAt": "2022-11-25T12:57:02.999Z",
      "updatedAt": "2022-11-25T12:57:02.999Z"
    },
    {
      "id": "dO9obbaAMg8UNnbo",
      "name": "Scheduled",
      "createdAt": "2025-07-10T15:12:41.643Z",
      "updatedAt": "2025-07-10T15:12:41.643Z"
    }
  ],
  "nodes": [
    {
      "id": "8056e0f1-b8d5-4c77-bf68-4c2972c9d562",
      "name": "Schedule Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        1160,
        -3160
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "weeks",
              "triggerAtDay": [
                1
              ],
              "triggerAtHour": 15
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "cf42380c-42a6-4cb7-9fb3-52d58f305e12",
      "name": "Define Weeks",
      "type": "n8n-nodes-base.code",
      "position": [
        1320,
        -3160
      ],
      "parameters": {
        "jsCode": "function formatDate(date) {\n  return date.toISOString().split('T')[0];\n}\n\nconst today = new Date();\n\n// Prior week (15 to 9 days ago)\nconst priorStart = new Date(today);\npriorStart.setDate(today.getDate() - 15);\n\nconst priorEnd = new Date(today);\npriorEnd.setDate(today.getDate() - 9);\n\n// Last week (8 to 2 days ago)\nconst lastStart = new Date(today);\nlastStart.setDate(today.getDate() - 8);\n\nconst lastEnd = new Date(today);\nlastEnd.setDate(today.getDate() - 2);\n\nreturn [\n  {\n    json: {\n      label: \"2 Weeks Ago\",\n      startDate: formatDate(priorStart),\n      endDate: formatDate(priorEnd)\n    }\n  },\n  {\n    json: {\n      label: \"Last Week\",\n      startDate: formatDate(lastStart),\n      endDate: formatDate(lastEnd)\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "fe2cab2e-0d51-48cf-b073-a9d6aa9ddb99",
      "name": "Brand Filter",
      "type": "n8n-nodes-base.code",
      "position": [
        1800,
        -3060
      ],
      "parameters": {
        "jsCode": "// Add brand terms here\nconst brandTerms = ['BRAND TERMS'];\nlet brandClicks = 0;\nlet nonBrandClicks = 0;\n\nfor (const row of $json.rows || []) {\n  const query = row.keys?.[0]?.toLowerCase() || '';\n  const clicks = row.clicks || 0;\n\n  if (brandTerms.some(term => query.includes(term))) {\n    brandClicks += clicks;\n  } else {\n    nonBrandClicks += clicks;\n  }\n}\n\nreturn [\n  {\n    json: {\n      startDate: $input.first().json.startDate,\n      endDate: $input.first().json.endDate,\n      label: \"Last Week GSC\",\n      brandClicks,\n      nonBrandClicks\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "1340e164-7f1c-45fe-9534-7f60a2cfdc42",
      "name": "Brand Filter1",
      "type": "n8n-nodes-base.code",
      "position": [
        1800,
        -3400
      ],
      "parameters": {
        "jsCode": "// Add brand terms here\nconst brandTerms = ['BRAND TERMS'];\nlet brandClicks = 0;\nlet nonBrandClicks = 0;\n\nfor (const row of $json.rows || []) {\n  const query = row.keys?.[0]?.toLowerCase() || '';\n  const clicks = row.clicks || 0;\n\n  if (brandTerms.some(term => query.includes(term))) {\n    brandClicks += clicks;\n  } else {\n    nonBrandClicks += clicks;\n  }\n}\n\nreturn [\n  {\n    json: {\n      startDate: $input.first().json.startDate,\n      endDate: $input.first().json.endDate,\n      label: \"2 Weeks Ago GSC\",\n      brandClicks,\n      nonBrandClicks\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "75aabedf-8065-4b3f-b85e-9e0113fac9fd",
      "name": "Flatten8",
      "type": "n8n-nodes-base.code",
      "position": [
        1800,
        -2900
      ],
      "parameters": {
        "jsCode": "const { startDate, endDate } = $input.all()[0].json;\nconst row = $json.rows?.[0] || {};\n\nreturn [{\n  json: {\n    startDate,\n    endDate,\n    ...row\n  }\n}];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "99402142-24b2-4b24-b275-0427ee05f759",
      "name": "Flatten9",
      "type": "n8n-nodes-base.code",
      "position": [
        1800,
        -3240
      ],
      "parameters": {
        "jsCode": "const { startDate, endDate } = $input.all()[0].json;\nconst row = $json.rows?.[0] || {};\n\nreturn [{\n  json: {\n    startDate,\n    endDate,\n    ...row\n  }\n}];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "80e99961-68ed-435b-8e0b-fdc6ef7528cb",
      "name": "If4",
      "type": "n8n-nodes-base.if",
      "position": [
        1480,
        -3160
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "8ab18755-9c4f-40d8-a0c0-f16ab3b7d940",
              "operator": {
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.label }}",
              "rightValue": "2 Weeks Ago"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "ba691c97-8c1c-42bf-8eee-33f64455d343",
      "name": "Merge 2 Weeks Ago",
      "type": "n8n-nodes-base.merge",
      "position": [
        2000,
        -3320
      ],
      "parameters": {
        "mode": "combine",
        "options": {},
        "combineBy": "combineByPosition"
      },
      "typeVersion": 3
    },
    {
      "id": "c48b74df-2d9f-4a2a-bb69-60401436b151",
      "name": "Merge Last Week",
      "type": "n8n-nodes-base.merge",
      "position": [
        2000,
        -2980
      ],
      "parameters": {
        "mode": "combine",
        "options": {},
        "combineBy": "combineByPosition"
      },
      "typeVersion": 3
    },
    {
      "id": "a7035456-2779-4ee0-912f-fa2d17a712f9",
      "name": "Brand/NB Pull1",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        1660,
        -3060
      ],
      "parameters": {
        "method": "POST",
        "options": {},
        "jsonBody": "={\n  \"startDate\": \"{{$json.startDate}}\",\n  \"endDate\": \"{{$json.endDate}}\",\n  \"dimensions\": [\"query\"],\n  \"rowLimit\": 5000\n}\n",
        "sendBody": true,
        "specifyBody": "=json",
        "authentication": "predefinedCredentialType",
        "bodyParameters": {
          "parameters": [
            {}
          ]
        },
        "nodeCredentialType": "googleOAuth2Api"
      },
      "credentials": {
        "googleOAuth2Api": {
          "id": "vGLK8wkbx5dOMive",
          "name": "My GSC Account"
        }
      },
      "executeOnce": false,
      "typeVersion": 4.2,
      "alwaysOutputData": false
    },
    {
      "id": "0e12a39c-2919-4849-88e6-95f988ebb1d7",
      "name": "Brand/NB Pull",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        1660,
        -3400
      ],
      "parameters": {
        "method": "POST",
        "options": {},
        "jsonBody": "={\n  \"startDate\": \"{{$json.startDate}}\",\n  \"endDate\": \"{{$json.endDate}}\",\n  \"dimensions\": [\"query\"],\n  \"rowLimit\": 5000\n}\n",
        "sendBody": true,
        "specifyBody": "=json",
        "authentication": "predefinedCredentialType",
        "bodyParameters": {
          "parameters": [
            {}
          ]
        },
        "nodeCredentialType": "googleOAuth2Api"
      },
      "credentials": {
        "googleOAuth2Api": {
          "id": "vGLK8wkbx5dOMive",
          "name": "My GSC Account"
        }
      },
      "executeOnce": false,
      "typeVersion": 4.2,
      "alwaysOutputData": false
    },
    {
      "id": "f80fa85c-0cf7-420f-9a1b-18df52f08bd4",
      "name": "Total Metrics Pull1",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        1660,
        -2900
      ],
      "parameters": {
        "method": "POST",
        "options": {},
        "jsonBody": "={\n  \"startDate\": \"{{$json.startDate}}\",\n  \"endDate\": \"{{$json.endDate}}\",\n  \"rowLimit\": 5000\n}\n",
        "sendBody": true,
        "specifyBody": "=json",
        "authentication": "predefinedCredentialType",
        "bodyParameters": {
          "parameters": [
            {}
          ]
        },
        "nodeCredentialType": "googleOAuth2Api"
      },
      "credentials": {
        "googleOAuth2Api": {
          "id": "vGLK8wkbx5dOMive",
          "name": "My GSC Account"
        }
      },
      "executeOnce": false,
      "typeVersion": 4.2,
      "alwaysOutputData": false
    },
    {
      "id": "a3bf4392-665f-40b6-8cf2-5d78ad8b0639",
      "name": "Total Metrics Pull",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        1660,
        -3240
      ],
      "parameters": {
        "method": "POST",
        "options": {},
        "jsonBody": "={\n  \"startDate\": \"{{$json.startDate}}\",\n  \"endDate\": \"{{$json.endDate}}\",\n  \"rowLimit\": 5000\n}\n",
        "sendBody": true,
        "specifyBody": "=json",
        "authentication": "predefinedCredentialType",
        "bodyParameters": {
          "parameters": [
            {}
          ]
        },
        "nodeCredentialType": "googleOAuth2Api"
      },
      "credentials": {
        "googleOAuth2Api": {
          "id": "vGLK8wkbx5dOMive",
          "name": "My GSC Account"
        }
      },
      "executeOnce": false,
      "typeVersion": 4.2,
      "alwaysOutputData": false
    },
    {
      "id": "9edc39e5-a46a-49e5-b5fe-97aca12562cc",
      "name": "Add Brand Name",
      "type": "n8n-nodes-base.code",
      "position": [
        2380,
        -3160
      ],
      "parameters": {
        "jsCode": "return [\n  {\n    json: {\n      // Add brand name here\n      brand: \"BRAND NAME\",\n      data: items.map(item => item.json)\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "a45a4cb9-baab-4cae-a569-77f96d9a09c8",
      "name": "Merge Time Periods",
      "type": "n8n-nodes-base.merge",
      "position": [
        2220,
        -3160
      ],
      "parameters": {},
      "typeVersion": 3
    },
    {
      "id": "01b59e92-11ac-42a9-8184-23bbcf95c9aa",
      "name": "Generate Brand Performance Table",
      "type": "n8n-nodes-base.code",
      "position": [
        2540,
        -3160
      ],
      "parameters": {
        "jsCode": "const brands = $input.all();\n\nlet html = '';\n\nfunction formatNumber(value) {\n  return typeof value === 'number' ? value.toLocaleString() : value;\n}\n\nfunction formatPercentChange(current, previous, precision = 1, invert = false) {\n  if (previous === 0 || previous === undefined || current === undefined) return '';\n  const rawChange = ((current - previous) / previous) * 100;\n  const change = invert ? -rawChange : rawChange;\n  const rounded = change.toFixed(precision);\n  const color = change > 0 ? 'green' : change < 0 ? 'red' : '#333';\n  return `<span style=\"color: ${color}; font-weight: 500;\">${change > 0 ? '+' : ''}${rounded}%</span>`;\n}\n\nfunction formatCTR(value) {\n  return typeof value === 'number' ? `${(value * 100).toFixed(2)}%` : value;\n}\n\nfor (const item of brands) {\n  const brand = item.json.brand;\n  const rows = item.json.data;\n\n  if (!rows || rows.length < 2) continue;\n\n  // Map rows by label\n  const rowMap = {};\n  rows.forEach(r => {\n    if (r.label) rowMap[r.label] = r;\n  });\n\n  const current = rowMap[\"Last Week GSC\"];\n  const previous = rowMap[\"2 Weeks Ago GSC\"];\n\n  if (!current || !previous) continue;\n\n  const baseFields = [\"brandClicks\", \"nonBrandClicks\"];\n  const deltaFields = [\"clicks\", \"impressions\", \"ctr\", \"position\"];\n\n  const headers = [\"Label\"];\n  headers.push(...baseFields);\n  deltaFields.forEach(f => {\n    headers.push(`% Δ ${f}`);\n    headers.push(f);\n  });\n\n  html += `\n    <div style=\"margin-bottom: 40px; border-top: 3px solid #333; padding-top: 20px;\">\n      <table style=\"border-collapse: collapse; width: 100%; font-family: sans-serif; font-size: 14px; border: 1px solid #999;\">\n        <caption style=\"caption-side: top; font-weight: bold; font-size: 18px; margin-bottom: 12px; text-align: left;\">${brand}</caption>\n        <thead>\n          <tr>\n            ${headers.map(h => `<th style=\"border: 1px solid #999; padding: 8px 10px; background-color: #f2f2f2; text-align: left;\">${h}</th>`).join('')}\n          </tr>\n        </thead>\n        <tbody>\n  `;\n\n  [\"2 Weeks Ago GSC\", \"Last Week GSC\"].forEach(label => {\n    const row = rowMap[label];\n    html += `<tr><td style=\"border: 1px solid #999; padding: 8px 10px;\">${label}</td>`;\n\n    // Add base fields (no delta)\n    baseFields.forEach(field => {\n      html += `<td style=\"border: 1px solid #999; padding: 8px 10px;\">${formatNumber(row[field]) ?? ''}</td>`;\n    });\n\n    // Add delta fields\n    deltaFields.forEach(field => {\n      let displayVal = row[field];\n      if (field === \"ctr\") {\n        displayVal = formatCTR(displayVal);\n      } else {\n        displayVal = formatNumber(displayVal);\n      }\n\n      if (label === \"2 Weeks Ago GSC\") {\n        html += `<td style=\"border: 1px solid #999; padding: 8px 10px;\"></td>`;\n        html += `<td style=\"border: 1px solid #999; padding: 8px 10px;\">${displayVal ?? ''}</td>`;\n      } else {\n        const delta = formatPercentChange(current[field], previous[field], 1, field === 'position');\n        html += `<td style=\"border: 1px solid #999; padding: 8px 10px;\">${delta}</td>`;\n        html += `<td style=\"border: 1px solid #999; padding: 8px 10px;\">${displayVal ?? ''}</td>`;\n      }\n    });\n\n    html += `</tr>`;\n  });\n\n  html += `</tbody></table></div>`;\n}\n\nreturn [{ json: { htmlTable: html } }];"
      },
      "typeVersion": 2
    },
    {
      "id": "308b3eaa-ddf8-4cdf-bb84-01f5be7567bf",
      "name": "Weekly GSC Metric Email",
      "type": "n8n-nodes-base.gmail",
      "position": [
        2700,
        -3160
      ],
      "webhookId": "56443199-fa21-46ae-ae67-352b6cb85e30",
      "parameters": {
        "message": "={{ $json[\"htmlTable\"] }}",
        "options": {},
        "subject": "Weekly GSC Metric Send"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "020fc4ae-9c23-4719-8484-ebde4daead63",
          "name": "Gmail Account 01 (matthew.fritschle)"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "53064791-2cfa-46c6-a5df-83209e9b436a",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2640,
        -3340
      ],
      "parameters": {
        "height": 140,
        "content": "## Connect to Gmail account or update to something else"
      },
      "typeVersion": 1
    },
    {
      "id": "73debc42-b892-4b85-b844-76978d21b2e0",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1580,
        -3540
      ],
      "parameters": {
        "width": 180,
        "height": 100,
        "content": "## Connect to GSC account"
      },
      "typeVersion": 1
    },
    {
      "id": "f9b96cd1-b4f2-4c24-8a09-45e499ec7f6b",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1800,
        -3560
      ],
      "parameters": {
        "width": 200,
        "height": 120,
        "content": "## Update with your own brand term(s)"
      },
      "typeVersion": 1
    },
    {
      "id": "732610cd-82ed-4027-8150-f0fdd9bc9248",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        360,
        -3400
      ],
      "parameters": {
        "width": 720,
        "height": 680,
        "content": "## This workflow generates a weekly performance summary from Google Search Console, focused on brand-level SEO metrics and week-over-week trends.\n\nIt provides a structured view of how each brand segment is performing, with clean formatting for quick insights.\n\nKey Features\n- Sends a weekly email with a table showing clicks, impressions, CTR, and position — along with % change vs. the previous week.\n- Highlights both brand and non-brand clicks separately.\n- Color-coded % changes make it easy to spot wins (green) and losses (red) at a glance.\n\nIt’s designed to give SEO teams a consistent overview of performance by brand, helping to track directional shifts and support deeper analysis when needed.\n\n### How it works\n1. Runs weekly (e.g. every Monday) to compare “Last Week” vs. “2 Weeks Ago” from GSC data.\n2. Includes both brand + non-brand click breakdown.\n3. Calculates raw values and week-over-week % change for clicks, impressions, CTR, and position.\n4. Outputs a clean, formatted table with labeled rows and color-coded changes.\n5. Sends the table as part of a scheduled email (can also be adapted for Slack or other channels).\n\n### Setup steps\n- Requires connected Google Search Console data (per brand segment).\n- Email delivery is included by default (customizable to other platforms).\n- Update brand segmentation logic to match your tracking needs (e.g. domain, label, or custom filters).\n- Typical setup time: ~5-10 minutes with structured input data.\n"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "487f2671-b2bb-4124-83da-4534e344328b",
  "connections": {
    "If4": {
      "main": [
        [
          {
            "node": "Brand/NB Pull",
            "type": "main",
            "index": 0
          },
          {
            "node": "Total Metrics Pull",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Brand/NB Pull1",
            "type": "main",
            "index": 0
          },
          {
            "node": "Total Metrics Pull1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Flatten8": {
      "main": [
        [
          {
            "node": "Merge Last Week",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Flatten9": {
      "main": [
        [
          {
            "node": "Merge 2 Weeks Ago",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Brand Filter": {
      "main": [
        [
          {
            "node": "Merge Last Week",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Define Weeks": {
      "main": [
        [
          {
            "node": "If4",
            "type": "main",
            "index": 0
          },
          {
            "node": "Merge 2 Weeks Ago2",
            "type": "main",
            "index": 0
          },
          {
            "node": "Merge 2 Weeks Ago3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Brand Filter1": {
      "main": [
        [
          {
            "node": "Merge 2 Weeks Ago",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Brand/NB Pull": {
      "main": [
        [
          {
            "node": "Brand Filter1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Add Brand Name": {
      "main": [
        [
          {
            "node": "Generate Brand Performance Table",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Brand/NB Pull1": {
      "main": [
        [
          {
            "node": "Brand Filter",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge Last Week": {
      "main": [
        [
          {
            "node": "Merge Time Periods",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Schedule Trigger": {
      "main": [
        [
          {
            "node": "Define Weeks",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge 2 Weeks Ago": {
      "main": [
        [
          {
            "node": "Merge Time Periods",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge Time Periods": {
      "main": [
        [
          {
            "node": "Add Brand Name",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Total Metrics Pull": {
      "main": [
        [
          {
            "node": "Flatten9",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Total Metrics Pull1": {
      "main": [
        [
          {
            "node": "Flatten8",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Generate Brand Performance Table": {
      "main": [
        [
          {
            "node": "Weekly GSC Metric Email",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}