Automated Google Business Reports with Gpt Insights to Slack & Email

The workflow JSON

Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →

{
  "id": "MgcLGZgNkm1mQheX",
  "meta": {
    "templateId": "9290"
  },
  "name": "Automated Google Business Reports with GPT Insights to Slack & Email",
  "tags": [],
  "nodes": [
    {
      "id": "c6e82665-fa63-4cbf-9e83-dcdc9d99bba3",
      "name": "OpenAI Chat Model4",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        5376,
        1152
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-5",
          "cachedResultName": "gpt-5"
        },
        "options": {}
      },
      "typeVersion": 1.2
    },
    {
      "id": "a83547f7-dfac-4064-b0ab-a98c4d5668c4",
      "name": "Read Companies",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        2208,
        464
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1nLUdbky6ngWyEFXS-I-1dkmUDt5IeVbY75QwvKCQ-o0/edit#gid=0",
          "cachedResultName": "Companies"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1nLUdbky6ngWyEFXS-I-1dkmUDt5IeVbY75QwvKCQ-o0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1nLUdbky6ngWyEFXS-I-1dkmUDt5IeVbY75QwvKCQ-o0/edit?usp=drivesdk",
          "cachedResultName": "GoogleData - Peyton "
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4
    },
    {
      "id": "8d733b11-c5e6-4f3b-a065-3b5c7b44869a",
      "name": "Build Week Window2",
      "type": "n8n-nodes-base.code",
      "position": [
        2736,
        464
      ],
      "parameters": {
        "jsCode": "// Builds \"this week\" (last 7 full days ending yesterday, UTC)\n// AND \"last week\" (the 7 days before that), per-company.\n//\n// Input items: rows from \"Read Companies\" with { company, accountId, locationId }\n// Output items: same company fields + week parts for API + ts for comparisons\n\nfunction atUtcMidnight(d = new Date()) {\n  return new Date(Date.UTC(d.getUTCFullYear(), d.getUTCMonth(), d.getUTCDate()));\n}\n\n// This week: last 7 days ending yesterday (inclusive)\nconst todayMid = atUtcMidnight();\nconst endThis = new Date(todayMid); endThis.setUTCDate(endThis.getUTCDate() - 1);\nconst startThis = new Date(endThis); startThis.setUTCDate(startThis.getUTCDate() - 6);\n\n// Last week: the 7 days before \"this week\"\nconst endLast = new Date(startThis); endLast.setUTCDate(endLast.getUTCDate() - 1);\nconst startLast = new Date(endLast); startLast.setUTCDate(startLast.getUTCDate() - 6);\n\nconst parts = d => ({\n  Y: d.getUTCFullYear(),\n  M: d.getUTCMonth() + 1,\n  D: d.getUTCDate(),\n});\nconst isoDate = d => d.toISOString().slice(0,10);\n\nreturn items.map(({ json }) => {\n  const sT = parts(startThis), eT = parts(endThis);\n  const sL = parts(startLast), eL = parts(endLast);\n\n  return {\n    json: {\n      // carry company identifiers\n      company: json.company,\n      accountId: json.accountId,\n      locationId: json.locationId,\n\n      // THIS WEEK (for API)\n      startY: sT.Y, startM: sT.M, startD: sT.D,\n      endY:   eT.Y, endM: eT.M, endD: eT.D,\n      startISO: startThis.toISOString(),\n      endISO:   endThis.toISOString(),\n      week_start: isoDate(startThis),\n      week_end:   isoDate(endThis),\n\n      // LAST WEEK (extra fields so downstream nodes can compare)\n      last_startY: sL.Y, last_startM: sL.M, last_startD: sL.D,\n      last_endY:   eL.Y, last_endM: eL.M, last_endD: eL.D,\n      last_startISO: startLast.toISOString(),\n      last_endISO:   endLast.toISOString(),\n      last_week_start: isoDate(startLast),\n      last_week_end:   isoDate(endLast),\n\n      // Millisecond timestamps for code nodes that prefer numeric ranges\n      ts: {\n        startThis: startThis.getTime(),\n        endThis:   endThis.getTime() + 24*60*60*1000 - 1,  // inclusive guard\n        startLast: startLast.getTime(),\n        endLast:   endLast.getTime() + 24*60*60*1000 - 1,\n      },\n    },\n  };\n});"
      },
      "typeVersion": 2
    },
    {
      "id": "e982ec74-55f0-418b-9790-4d17cb929103",
      "name": "Get Impression data",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        2928,
        464
      ],
      "parameters": {
        "url": "={{\n(() => {\n  const base = `https://businessprofileperformance.googleapis.com/v1/locations/${$json.locationId}:fetchMultiDailyMetricsTimeSeries`;\n  const metrics = [\n    'WEBSITE_CLICKS',\n    'CALL_CLICKS',\n    'BUSINESS_DIRECTION_REQUESTS',\n    'BUSINESS_IMPRESSIONS_DESKTOP_SEARCH',\n    'BUSINESS_IMPRESSIONS_MOBILE_SEARCH',\n    'BUSINESS_IMPRESSIONS_DESKTOP_MAPS',\n    'BUSINESS_IMPRESSIONS_MOBILE_MAPS'\n  ];\n  const q = [\n    ...metrics.map(m => `dailyMetrics=${encodeURIComponent(m)}`),\n    `dailyRange.start_date.year=${$json.startY}`,\n    `dailyRange.start_date.month=${$json.startM}`,\n    `dailyRange.start_date.day=${$json.startD}`,\n    `dailyRange.end_date.year=${$json.endY}`,\n    `dailyRange.end_date.month=${$json.endM}`,\n    `dailyRange.end_date.day=${$json.endD}`\n  ].join('&');\n  return `${base}?${q}`;\n})()\n}}\n",
        "options": {
          "response": {
            "response": {
              "fullResponse": true,
              "responseFormat": "json"
            }
          }
        },
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "googleBusinessProfileOAuth2Api"
      },
      "typeVersion": 4
    },
    {
      "id": "2c9d403b-d03d-43fd-900e-60089927fc03",
      "name": "Flatten",
      "type": "n8n-nodes-base.code",
      "position": [
        3104,
        464
      ],
      "parameters": {
        "jsCode": "// === Upstream node names (update if you renamed) ===\nconst META_NODE = 'Build Week Window2';\nconst HTTP_NODE = 'Get Impression data';\n\n// ---------- Helpers ----------\nconst pad = n => String(n).padStart(2, '0');\n\nfunction coerceBody(x) {\n  const b = x?.body ?? x;\n  if (b == null) return {};\n  if (typeof b === 'string') { try { return JSON.parse(b); } catch { return {}; } }\n  return b;\n}\n\nfunction toNumber(v) {\n  if (v == null) return 0;\n  if (typeof v === 'number') return Number.isFinite(v) ? v : 0;\n  if (typeof v === 'string') {\n    const n = Number(v.replace?.(/,/g, '') ?? v);\n    return Number.isFinite(n) ? n : 0;\n  }\n  if (typeof v === 'object') {\n    if ('value' in v) return toNumber(v.value);\n    if ('intValue' in v) return toNumber(v.intValue);\n    if ('doubleValue' in v) return toNumber(v.doubleValue);\n    if ('count' in v) return toNumber(v.count);\n  }\n  return 0;\n}\n\nfunction getPoints(ts) {\n  if (!ts) return [];\n  if (Array.isArray(ts)) return ts;\n  return ts.datedValues || ts.dailyValues || ts.values || [];\n}\n\nfunction sumPoints(arr) {\n  if (!Array.isArray(arr)) return 0;\n  let sum = 0;\n  for (const d of arr) sum += toNumber(d?.value ?? d?.count ?? d);\n  return sum;\n}\n\n// ---------- Main ----------\nconst metas = $items(META_NODE, 0, 0).map(i => i.json);\nconst httpItems = $items(HTTP_NODE, 0, 0).map(i => i.json || i);\nconst out = [];\n\nfor (let i = 0; i < metas.length; i++) {\n  const meta = metas[i] || {};\n  const http = httpItems[i] || {};\n  const body = coerceBody(http);\n\n  // \u2705 FIX: Use Map to ensure each metric is only counted once\n  const metricMap = new Map();\n  \n  const allEntries = body?.multiDailyMetricTimeSeries ?? [];\n  \n  for (const entry of allEntries) {\n    const series = entry?.dailyMetricTimeSeries ?? [];\n    for (const s of series) {\n      const metricKey = s?.dailyMetric || s?.metric;\n      if (!metricKey) continue;\n      \n      // Only process if we haven't seen this metric yet\n      if (!metricMap.has(metricKey)) {\n        const points = getPoints(s?.timeSeries);\n        metricMap.set(metricKey, sumPoints(points));\n      }\n    }\n  }\n\n  // Convert Map to object for easy access\n  const totals = Object.fromEntries(metricMap);\n\n  // Extract known metric values\n  const DESKTOP_SEARCH = totals.BUSINESS_IMPRESSIONS_DESKTOP_SEARCH || 0;\n  const MOBILE_SEARCH  = totals.BUSINESS_IMPRESSIONS_MOBILE_SEARCH  || 0;\n  const DESKTOP_MAPS   = totals.BUSINESS_IMPRESSIONS_DESKTOP_MAPS   || 0;\n  const MOBILE_MAPS    = totals.BUSINESS_IMPRESSIONS_MOBILE_MAPS    || 0;\n\n  const impressions_search = DESKTOP_SEARCH + MOBILE_SEARCH;\n  const impressions_maps   = DESKTOP_MAPS  + MOBILE_MAPS;\n\n  // \u2705 Only this key is used for directions\n  const direction_requests = totals.BUSINESS_DIRECTION_REQUESTS || 0;\n\n  out.push({\n    json: {\n      company: meta.company || '(unknown)',\n      locationId: meta.locationId ?? null,\n\n      week_start: `${meta.startY}-${pad(meta.startM)}-${pad(meta.startD)}`,\n      week_end:   `${meta.endY}-${pad(meta.endM)}-${pad(meta.endD)}`,\n\n      website_clicks: totals.WEBSITE_CLICKS || 0,\n      call_clicks: totals.CALL_CLICKS || 0,\n      direction_requests,\n\n      impressions_search,\n      impressions_maps,\n\n      impressions_search_desktop: DESKTOP_SEARCH,\n      impressions_search_mobile:  MOBILE_SEARCH,\n      impressions_maps_desktop:   DESKTOP_MAPS,\n      impressions_maps_mobile:    MOBILE_MAPS,\n    }\n  });\n}\n\nreturn out;"
      },
      "typeVersion": 2
    },
    {
      "id": "0ac8d44a-7393-4bf8-9d59-364c5a4c7a2f",
      "name": "Add Impressions to Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        3872,
        464
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [
            {
              "id": "company",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "company",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "locationId",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "locationId",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "week_start",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "week_start",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "week_end",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "week_end",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "website_clicks",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "website_clicks",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "call_clicks",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "call_clicks",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "direction_requests",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "direction_requests",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "impressions_search",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "impressions_search",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "impressions_maps",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "impressions_maps",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "impressions_search_desktop",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "impressions_search_desktop",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "impressions_search_mobile",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "impressions_search_mobile",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "impressions_maps_desktop",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "impressions_maps_desktop",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "impressions_maps_mobile",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "impressions_maps_mobile",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 467460706,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1nLUdbky6ngWyEFXS-I-1dkmUDt5IeVbY75QwvKCQ-o0/edit#gid=467460706",
          "cachedResultName": "Impression Data"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1nLUdbky6ngWyEFXS-I-1dkmUDt5IeVbY75QwvKCQ-o0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1nLUdbky6ngWyEFXS-I-1dkmUDt5IeVbY75QwvKCQ-o0/edit?usp=drivesdk",
          "cachedResultName": "GoogleData - Peyton "
        }
      },
      "typeVersion": 4
    },
    {
      "id": "ef1c5e27-f336-4bbb-a668-a10abe6087c2",
      "name": "Set Company (reviews)",
      "type": "n8n-nodes-base.code",
      "position": [
        2912,
        1296
      ],
      "parameters": {
        "jsCode": "// Build items from the \"Read Companies\" node, not from the incoming payload\nconst companies = $items('Read Companies').map(i => i.json);\n\nreturn companies.map(c => ({\n  json: {\n    companyName: c.company,\n    // keep both the full path and the raw IDs for convenience\n    accountId: String(c.accountId),\n    locationId: String(c.locationId),\n    accountPath: `accounts/${c.accountId}`,\n    locationPath: `locations/${c.locationId}`,\n  }\n}));"
      },
      "typeVersion": 2
    },
    {
      "id": "887dd0d9-898e-4c32-adf3-bdbfeb580049",
      "name": "Get Many Reviews",
      "type": "n8n-nodes-base.googleBusinessProfile",
      "position": [
        3104,
        1296
      ],
      "parameters": {
        "account": {
          "__rl": true,
          "mode": "list",
          "value": "accounts/102160578675379169778",
          "cachedResultName": "Peyton Leveillee"
        },
        "location": {
          "__rl": true,
          "mode": "name",
          "value": "={{ $json.locationPath }}"
        },
        "resource": "review",
        "operation": "getAll",
        "returnAll": true,
        "requestOptions": {}
      },
      "typeVersion": 1
    },
    {
      "id": "c0315534-5395-4ba9-b4d8-d2554455b156",
      "name": "Build Week Window (reviews)",
      "type": "n8n-nodes-base.code",
      "position": [
        2704,
        1296
      ],
      "parameters": {
        "jsCode": "// Builds \"this week\" (last 7 full days ending yesterday, UTC)\n// AND \"last week\" (the 7 days before that), as a single shared window item.\n\nfunction atUtcMidnight(d = new Date()) {\n  return new Date(Date.UTC(d.getUTCFullYear(), d.getUTCMonth(), d.getUTCDate()));\n}\n\nconst todayMid = atUtcMidnight();\nconst endThis = new Date(todayMid); endThis.setUTCDate(endThis.getUTCDate() - 1);\nconst startThis = new Date(endThis); startThis.setUTCDate(startThis.getUTCDate() - 6);\n\nconst endLast = new Date(startThis); endLast.setUTCDate(endLast.getUTCDate() - 1);\nconst startLast = new Date(endLast); startLast.setUTCDate(startLast.getUTCDate() - 6);\n\nconst parts = d => ({\n  Y: d.getUTCFullYear(),\n  M: d.getUTCMonth() + 1,\n  D: d.getUTCDate(),\n});\nconst isoDate = d => d.toISOString().slice(0,10);\n\nconst sT = parts(startThis), eT = parts(endThis);\nconst sL = parts(startLast), eL = parts(endLast);\n\nreturn [{\n  json: {\n    // THIS WEEK\n    startY: sT.Y, startM: sT.M, startD: sT.D,\n    endY:   eT.Y, endM: eT.M, endD: eT.D,\n    startISO: startThis.toISOString(),\n    endISO:   endThis.toISOString(),\n    week_start: isoDate(startThis),\n    week_end:   isoDate(endThis),\n\n    // LAST WEEK\n    last_startY: sL.Y, last_startM: sL.M, last_startD: sL.D,\n    last_endY:   eL.Y, last_endM: eL.M, last_endD: eL.D,\n    last_startISO: startLast.toISOString(),\n    last_endISO:   endLast.toISOString(),\n    last_week_start: isoDate(startLast),\n    last_week_end:   isoDate(endLast),\n\n    // Numeric timestamps for easy filtering/comparison in later code nodes\n    ts: {\n      startThis: startThis.getTime(),\n      endThis:   endThis.getTime() + 24*60*60*1000 - 1,\n      startLast: startLast.getTime(),\n      endLast:   endLast.getTime() + 24*60*60*1000 - 1,\n    },\n  }\n}];"
      },
      "typeVersion": 2
    },
    {
      "id": "8dfac27e-5b86-42da-8c83-d9170075e194",
      "name": "Add Reviews to Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        3488,
        1296
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [
            {
              "id": "companyName",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "companyName",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "locationId",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "locationId",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "reviewId",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "reviewId",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "ratingNum",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "ratingNum",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "createTime",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "createTime",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "comment",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "comment",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "sentiment_label",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "sentiment_label",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "week_start",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "week_start",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "week_end",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "week_end",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "key",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "key",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "sentimentCategory",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "sentimentCategory",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "hasReply",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "hasReply",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "replyLatencyHours",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "replyLatencyHours",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "ratingText",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "ratingText",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 307941098,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1nLUdbky6ngWyEFXS-I-1dkmUDt5IeVbY75QwvKCQ-o0/edit#gid=307941098",
          "cachedResultName": "Review Data"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1nLUdbky6ngWyEFXS-I-1dkmUDt5IeVbY75QwvKCQ-o0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1nLUdbky6ngWyEFXS-I-1dkmUDt5IeVbY75QwvKCQ-o0/edit?usp=drivesdk",
          "cachedResultName": "GoogleData - Peyton "
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "30b1aa4a-255b-4d30-8ade-af80dabaab78",
      "name": "Summarize Weekly Reviews",
      "type": "n8n-nodes-base.code",
      "position": [
        3680,
        1296
      ],
      "parameters": {
        "jsCode": "// Weekly Reviews Summary \u2014 Input: items from the weekly mapper above\nfunction avg(a){ return a.length ? +(a.reduce((x,y)=>x+y,0)/a.length).toFixed(2) : 0; }\nfunction median(a){\n  if (!a.length) return 0;\n  const s=[...a].sort((x,y)=>x-y), m=Math.floor(s.length/2);\n  return s.length%2 ? s[m] : +(((s[m-1]+s[m])/2).toFixed(2));\n}\n\nconst rows = $input.all().map(i => i.json);\n\n// group by company + week_start (use location too if you have multi-location per company)\nconst groups = new Map();\nfor (const r of rows) {\n  const key = `${r.companyName||'(unknown)'}__${r.week_start||''}`;\n  if (!groups.has(key)) groups.set(key, []);\n  groups.get(key).push(r);\n}\n\nconst out = [];\nfor (const [, list] of groups.entries()) {\n  const companyName = list[0].companyName || '(unknown)';\n  const locationId  = list[0].locationId || null;\n  const week_start  = list[0].week_start || null;\n  const week_end    = list[0].week_end || null;\n\n  const total = list.length;\n  const ratings = list.map(r => Number(r.ratingNum||0)).filter(n => Number.isFinite(n));\n  const avgRating = avg(ratings);\n\n  const pos = list.filter(r => r.sentiment_label === 'Positive').length;\n  const neu = list.filter(r => r.sentiment_label === 'Neutral').length;\n  const neg = list.filter(r => r.sentiment_label === 'Negative').length;\n  const positiveShare = total ? +((pos/total)*100).toFixed(1) : 0;\n\n  const replied = list.filter(r => r.hasReply === true).length;\n  const replyRatePct = total ? +((replied/total)*100).toFixed(1) : 0;\n\n  const latencies = list.map(r => (typeof r.replyLatencyHours === 'number') ? r.replyLatencyHours : null).filter(v => v !== null);\n  const medianReplyLatencyHours = median(latencies);\n\n  out.push({\n    json: {\n      companyName, locationId, week_start, week_end,\n      total_reviews: total,\n      positive: pos, neutral: neu, negative: neg,\n      positive_share_pct: positiveShare,\n      average_rating: avgRating,\n      reply_rate_pct: replyRatePct,\n      median_reply_latency_hours: medianReplyLatencyHours\n    }\n  });\n}\n\nreturn out;"
      },
      "typeVersion": 2
    },
    {
      "id": "f897b099-2e89-4537-84bd-fd74b381d453",
      "name": "Add Weekly Summary",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        3872,
        1296
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [
            {
              "id": "companyName",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "companyName",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "locationId",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "locationId",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "week_start",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "week_start",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "week_end",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "week_end",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "total_reviews",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "total_reviews",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "positive",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "positive",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "neutral",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "neutral",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "negative",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "negative",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "positive_share_pct",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "positive_share_pct",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "average_rating",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "average_rating",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "reply_rate_pct",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "reply_rate_pct",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "median_reply_latency_hours",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "median_reply_latency_hours",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 238935942,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1nLUdbky6ngWyEFXS-I-1dkmUDt5IeVbY75QwvKCQ-o0/edit#gid=238935942",
          "cachedResultName": "Reviews Weekly Summary"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1nLUdbky6ngWyEFXS-I-1dkmUDt5IeVbY75QwvKCQ-o0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1nLUdbky6ngWyEFXS-I-1dkmUDt5IeVbY75QwvKCQ-o0/edit?usp=drivesdk",
          "cachedResultName": "GoogleData - Peyton "
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "50e504fd-5112-4f11-98c9-5f1b922d4a6a",
      "name": "Set Company (total reviews)",
      "type": "n8n-nodes-base.code",
      "position": [
        2720,
        1920
      ],
      "parameters": {
        "jsCode": "// Build items from the \"Read Companies\" node, not from the incoming payload\nconst companies = $items('Read Companies').map(i => i.json);\n\nreturn companies.map(c => ({\n  json: {\n    companyName: c.company,\n    // keep both the full path and the raw IDs for convenience\n    accountId: String(c.accountId),\n    locationId: String(c.locationId),\n    accountPath: `accounts/${c.accountId}`,\n    locationPath: `locations/${c.locationId}`,\n  }\n}));"
      },
      "typeVersion": 2
    },
    {
      "id": "13e402fa-fbaa-4b98-93f3-22fc9c7dbf43",
      "name": "Build 12-Week Window",
      "type": "n8n-nodes-base.code",
      "position": [
        2720,
        816
      ],
      "parameters": {
        "jsCode": "// Builds a date window covering the last 12 full ISO weeks ending yesterday\nconst now = new Date();\nconst end = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate()));\nend.setUTCDate(end.getUTCDate() - 1); // yesterday\n\n// go back ~84 days (12 weeks)\nconst start = new Date(end);\nstart.setUTCDate(start.getUTCDate() - (7 * 12 - 1)); // inclusive start\n\nreturn $input.all().map(({ json }) => {\n  return {\n    json: {\n      ...json,\n      startY: start.getUTCFullYear(),\n      startM: start.getUTCMonth() + 1,\n      startD: start.getUTCDate(),\n      endY:   end.getUTCFullYear(),\n      endM:   end.getUTCMonth() + 1,\n      endD:   end.getUTCDate()\n    }\n  };\n});"
      },
      "typeVersion": 2
    },
    {
      "id": "19b21559-b1e9-4803-8493-a8a55522162d",
      "name": "12 week impressions",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        2912,
        816
      ],
      "parameters": {
        "url": "={{\n(() => {\n  const base = `https://businessprofileperformance.googleapis.com/v1/locations/${$json.locationId}:fetchMultiDailyMetricsTimeSeries`;\n  const metrics = [\n    'WEBSITE_CLICKS',\n    'CALL_CLICKS',\n    'BUSINESS_DIRECTION_REQUESTS',\n    'BUSINESS_IMPRESSIONS_DESKTOP_SEARCH',\n    'BUSINESS_IMPRESSIONS_MOBILE_SEARCH',\n    'BUSINESS_IMPRESSIONS_DESKTOP_MAPS',\n    'BUSINESS_IMPRESSIONS_MOBILE_MAPS'\n  ];\n  const q = [\n    ...metrics.map(m => `dailyMetrics=${encodeURIComponent(m)}`),\n    `dailyRange.start_date.year=${$json.startY}`,\n    `dailyRange.start_date.month=${$json.startM}`,\n    `dailyRange.start_date.day=${$json.startD}`,\n    `dailyRange.end_date.year=${$json.endY}`,\n    `dailyRange.end_date.month=${$json.endM}`,\n    `dailyRange.end_date.day=${$json.endD}`\n  ].join('&');\n  return `${base}?${q}`;\n})()\n}}\n",
        "options": {
          "response": {
            "response": {
              "fullResponse": true,
              "responseFormat": "json"
            }
          }
        },
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "googleBusinessProfileOAuth2Api"
      },
      "typeVersion": 4
    },
    {
      "id": "b954b33c-c0b8-426e-bdc7-127499c93f36",
      "name": "Flatten 12-week",
      "type": "n8n-nodes-base.code",
      "position": [
        3088,
        816
      ],
      "parameters": {
        "jsCode": "// === Upstream node names (change if you renamed) ===\nconst META_NODE = 'Build 12-Week Window';\nconst HTTP_NODE = '12 week impressions';\n\n// Pull aligned arrays from the two upstream nodes\nconst metas = $items(META_NODE, 0, 0).map(i => i.json);\nconst httpItems = $items(HTTP_NODE, 0, 0).map(i => i.json || i);\n\n// helpers\nconst pad = n => String(n).padStart(2, '0');\n\nfunction coerceBody(x) {\n  const b = x?.body ?? x;\n  if (b == null) return {};\n  if (typeof b === 'string') {\n    try { return JSON.parse(b); } catch { return {}; }\n  }\n  return b;\n}\n\nfunction toUTCDate(y, m, d) {\n  if (y == null || m == null || d == null) return null;\n  return new Date(Date.UTC(Number(y), Number(m) - 1, Number(d)));\n}\n\nfunction getWindow(meta) {\n  const start = toUTCDate(meta.startY, meta.startM, meta.startD);\n  const end   = toUTCDate(meta.endY,   meta.endM,   meta.endD);\n  return { start, end };\n}\n\nfunction parsePointDate(p) {\n  // Google often returns { date: { year, month, day } }\n  const dd = p?.date || p?.startDate || p?.endDate || p?.time?.startDate || p?.time?.endDate;\n  if (!dd) return null;\n\n  if (typeof dd === 'string') {\n    const d = new Date(dd); // ISO string case\n    return isNaN(d) ? null : d;\n  }\n\n  // object form { year, month, day }\n  const y = dd.year ?? dd?.year;\n  const m = dd.month ?? dd?.month;\n  const d = dd.day ?? dd?.day;\n  const dt = toUTCDate(y, m, d);\n  return dt ?? null;\n}\n\nfunction inRange(date, start, end) {\n  if (!date || !start || !end) return true; // if anything missing, don't filter out\n  return date >= start && date <= end;\n}\n\nfunction sumPointsInRange(arr, start, end) {\n  if (!Array.isArray(arr)) return 0;\n  let sum = 0;\n  for (const p of arr) {\n    const v = Number(p?.value ?? p?.count ?? 0);\n    if (!Number.isFinite(v)) continue;\n    const pd = parsePointDate(p);\n    if (inRange(pd, start, end)) sum += v;\n  }\n  return sum;\n}\n\nconst out = [];\n\nfor (let i = 0; i < metas.length; i++) {\n  const meta = metas[i] || {};\n  const http = httpItems[i] || {};\n  const body = coerceBody(http);\n  const { start, end } = getWindow(meta);\n\n  // \u2705 FIX: Use Map to deduplicate metrics\n  const metricMap = new Map();\n  \n  const allEntries = body?.multiDailyMetricTimeSeries ?? [];\n  \n  for (const entry of allEntries) {\n    const series = entry?.dailyMetricTimeSeries ?? [];\n    for (const s of series) {\n      const metric = s?.dailyMetric;\n      if (!metric) continue;\n      \n      // Only process if we haven't seen this metric yet\n      if (!metricMap.has(metric)) {\n        const points = s?.timeSeries?.datedValues || s?.timeSeries?.dailyValues || [];\n        metricMap.set(metric, sumPointsInRange(points, start, end));\n      }\n    }\n  }\n\n  // Convert Map to object\n  const totals = Object.fromEntries(metricMap);\n\n  // raw splits\n  const DESKTOP_SEARCH = totals.BUSINESS_IMPRESSIONS_DESKTOP_SEARCH || 0;\n  const MOBILE_SEARCH  = totals.BUSINESS_IMPRESSIONS_MOBILE_SEARCH  || 0;\n  const DESKTOP_MAPS   = totals.BUSINESS_IMPRESSIONS_DESKTOP_MAPS   || 0;\n  const MOBILE_MAPS    = totals.BUSINESS_IMPRESSIONS_MOBILE_MAPS    || 0;\n\n  // combined\n  const impressions_search = DESKTOP_SEARCH + MOBILE_SEARCH;\n  const impressions_maps   = DESKTOP_MAPS  + MOBILE_MAPS;\n\n  out.push({\n    json: {\n      company: meta.company || '(unknown)',\n      locationId: meta.locationId || null,\n\n      week_start: `${meta.startY}-${pad(meta.startM)}-${pad(meta.startD)}`,\n      week_end:   `${meta.endY}-${pad(meta.endM)}-${pad(meta.endD)}`,\n\n      // clicks & requests\n      website_clicks: totals.WEBSITE_CLICKS || 0,\n      call_clicks: totals.CALL_CLICKS || 0,\n      direction_requests: totals.BUSINESS_DIRECTION_REQUESTS || 0,\n\n      // combined\n      impressions_search,\n      impressions_maps,\n\n      // granular splits\n      impressions_search_desktop: DESKTOP_SEARCH,\n      impressions_search_mobile:  MOBILE_SEARCH,\n      impressions_maps_desktop:   DESKTOP_MAPS,\n      impressions_maps_mobile:    MOBILE_MAPS,\n    }\n  });\n}\n\nreturn out;"
      },
      "typeVersion": 2
    },
    {
      "id": "ce979f8e-8b6b-4d3e-a07d-1646e59d2e52",
      "name": "Add 12 week Impressions to Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        3872,
        816
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [
            {
              "id": "company",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "company",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "locationId",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "locationId",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "week_start",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "week_start",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "week_end",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "week_end",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "website_clicks",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "website_clicks",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "call_clicks",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "call_clicks",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "direction_requests",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "direction_requests",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "impressions_search",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "impressions_search",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "impressions_maps",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "impressions_maps",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "impressions_search_desktop",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "impressions_search_desktop",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "impressions_search_mobile",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "impressions_search_mobile",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "impressions_maps_desktop",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "impressions_maps_desktop",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "impressions_maps_mobile",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "impressions_maps_mobile",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1992525174,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1nLUdbky6ngWyEFXS-I-1dkmUDt5IeVbY75QwvKCQ-o0/edit#gid=1992525174",
          "cachedResultName": "Impression 12 week window"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1nLUdbky6ngWyEFXS-I-1dkmUDt5IeVbY75QwvKCQ-o0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1nLUdbky6ngWyEFXS-I-1dkmUDt5IeVbY75QwvKCQ-o0/edit?usp=drivesdk",
          "cachedResultName": "GoogleData - Peyton "
        }
      },
      "typeVersion": 4
    },
    {
      "id": "daa03d24-bc82-4a11-961e-e47777a5e8cd",
      "name": "Add all time Summary",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        3872,
        1920
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [
            {
              "id": "companyName",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "companyName",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "locationId",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "locationId",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "total_reviews_all_time",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "total_reviews_all_time",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "positive_all_time",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "positive_all_time",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "neutral_all_time",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "neutral_all_time",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "negative_all_time",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "negative_all_time",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "positive_share_pct_all_time",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "positive_share_pct_all_time",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "average_rating_all_time",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "average_rating_all_time",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "reply_rate_pct_all_time",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "reply_rate_pct_all_time",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "median_reply_latency_hours_all_time",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "median_reply_latency_hours_all_time",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "last_review_at",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "last_review_at",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 44559940,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1nLUdbky6ngWyEFXS-I-1dkmUDt5IeVbY75QwvKCQ-o0/edit#gid=44559940",
          "cachedResultName": "Total Review Summary"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1nLUdbky6ngWyEFXS-I-1dkmUDt5IeVbY75QwvKCQ-o0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1nLUdbky6ngWyEFXS-I-1dkmUDt5IeVbY75QwvKCQ-o0/edit?usp=drivesdk",
          "cachedResultName": "GoogleData - Peyton "
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "fae71ed4-93b7-4d38-b996-871dad806fc2",
      "name": "Get all reviews",
      "type": "n8n-nodes-base.googleBusinessProfile",
      "position": [
        2912,
        1920
      ],
      "parameters": {
        "account": {
          "__rl": true,
          "mode": "list",
          "value": "accounts/102160578675379169778",
          "cachedResultName": "Peyton Leveillee"
        },
        "location": {
          "__rl": true,
          "mode": "name",
          "value": "={{ $json.locationPath }}"
        },
        "resource": "review",
        "operation": "getAll",
        "returnAll": true,
        "requestOptions": {}
      },
      "typeVersion": 1
    },
    {
      "id": "5ba35759-85e4-4fae-a4a0-b0b9946ec0b3",
      "name": "Merge2",
      "type": "n8n-nodes-base.merge",
      "position": [
        4544,
        864
      ],
      "parameters": {
        "numberInputs": 7
      },
      "typeVersion": 3.2
    },
    {
      "id": "1a372bcf-358e-42c3-b225-82690917e5e0",
      "name": "Get Impressions (last week)",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        2928,
        640
      ],
      "parameters": {
        "url": "={{\n(() => {\n  const base = `https://businessprofileperformance.googleapis.com/v1/locations/${$json.locationId}:fetchMultiDailyMetricsTimeSeries`;\n  const metrics = [\n    'WEBSITE_CLICKS',\n    'CALL_CLICKS',\n    'BUSINESS_DIRECTION_REQUESTS',\n    'BUSINESS_IMPRESSIONS_DESKTOP_SEARCH',\n    'BUSINESS_IMPRESSIONS_MOBILE_SEARCH',\n    'BUSINESS_IMPRESSIONS_DESKTOP_MAPS',\n    'BUSINESS_IMPRESSIONS_MOBILE_MAPS'\n  ];\n  const q = [\n    ...metrics.map(m => `dailyMetrics=${encodeURIComponent(m)}`),\n    `dailyRange.start_date.year=${$json.last_startY}`,\n    `dailyRange.start_date.month=${$json.last_startM}`,\n    `dailyRange.start_date.day=${$json.last_startD}`,\n    `dailyRange.end_date.year=${$json.last_endY}`,\n    `dailyRange.end_date.month=${$json.last_endM}`,\n    `dailyRange.end_date.day=${$json.last_endD}`\n  ].join('&');\n  return `${base}?${q}`;\n})()\n}}",
        "options": {
          "response": {
            "response": {
              "fullResponse": true,
              "responseFormat": "json"
            }
          }
        },
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "googleBusinessProfileOAuth2Api"
      },
      "typeVersion": 4
    },
    {
      "id": "f9ce0523-f032-4e67-9246-37bfd77310f6",
      "name": "Flatten (last week)",
      "type": "n8n-nodes-base.code",
      "position": [
        3104,
        640
      ],
      "parameters": {
        "jsCode": "// === Upstream node names (change if yours differ) ===\nconst META_NODE = 'Build Week Window2';\nconst HTTP_NODE = 'Get Impressions (last week)'; // <- set to your last-week HTTP node name\n\n// Pull aligned arrays\nconst metas = $items(META_NODE, 0, 0).map(i => i.json);\nconst httpItems = $items(HTTP_NODE, 0, 0).map(i => i.json || i);\n\nconst pad = n => String(n).padStart(2, '0');\n\nfunction sumPoints(arr) {\n  if (!Array.isArray(arr)) return 0;\n  return arr.reduce((acc, d) => acc + Number(d?.value ?? d?.count ?? 0), 0);\n}\n\nconst out = [];\n\nfor (let i = 0; i < metas.length; i++) {\n  const meta = metas[i] || {};\n  const http = httpItems[i] || {};\n  const body = http.body ?? http;\n\n  const series = body?.multiDailyMetricTimeSeries?.[0]?.dailyMetricTimeSeries || [];\n\n  const totals = {};\n  for (const s of series) {\n    const points = s?.timeSeries?.datedValues || s?.timeSeries?.dailyValues || [];\n    totals[s.dailyMetric] = sumPoints(points);\n  }\n\n  // splits\n  const DESKTOP_SEARCH = totals.BUSINESS_IMPRESSIONS_DESKTOP_SEARCH || 0;\n  const MOBILE_SEARCH  = totals.BUSINESS_IMPRESSIONS_MOBILE_SEARCH  || 0;\n  const DESKTOP_MAPS   = totals.BUSINESS_IMPRESSIONS_DESKTOP_MAPS   || 0;\n  const MOBILE_MAPS    = totals.BUSINESS_IMPRESSIONS_MOBILE_MAPS    || 0;\n\n  // combined\n  const impressions_search_last = DESKTOP_SEARCH + MOBILE_SEARCH;\n  const impressions_maps_last   = DESKTOP_MAPS  + MOBILE_MAPS;\n\n  out.push({\n    json: {\n      company: meta.company || '(unknown)',\n      locationId: meta.locationId || null,\n\n      // last-week window (from meta.last_*)\n      last_week_start: `${meta.last_startY}-${pad(meta.last_startM)}-${pad(meta.last_startD)}`,\n      last_week_end:   `${meta.last_endY}-${pad(meta.last_endM)}-${pad(meta.last_endD)}`,\n\n      // actions\n      website_clicks_last: totals.WEBSITE_CLICKS || 0,\n      call_clicks_last: totals.CALL_CLICKS || 0,\n      direction_requests_last: totals.BUSINESS_DIRECTION_REQUESTS || 0,\n\n      // impressions (combined)\n      impressions_search_last,\n      impressions_maps_last,\n\n      // impressions (splits)\n      impressions_search_desktop_last: DESKTOP_SEARCH,\n      impressions_search_mobile_last:  MOBILE_SEARCH,\n      impressions_maps_desktop_last:   DESKTOP_MAPS,\n      impressions_maps_mobile_last:    MOBILE_MAPS,\n    }\n  });\n}\n\nreturn out;"
      },
      "typeVersion": 2
    },
    {
      "id": "ad7d3fb3-00ec-4f77-811d-5f34c6e5e14d",
      "name": "Join All",
      "type": "n8n-nodes-base.code",
      "position": [
        4784,
        944
      ],
      "parameters": {
        "jsCode": "/***** Join All \u2014 robust comparisons (impressions + reviews + all-time)\n * Accepts mixed records per company/location:\n *  - This-week impressions (impressions_* + week_start/week_end)\n *  - Last-week impressions  (\u2026_last + last_week_start/last_week_end)\n *  - 12-week impressions rollup\n *  - Weekly reviews (this week)  -> { total_reviews, positive, neutral, negative, \u2026, week_start/week_end }\n *  - Weekly reviews (last week)  -> { \u2026 , isLastWeek:true } OR object with week_start == last_week_start\n *  - 12-week reviews summary     -> { total_reviews_12w, positive_share_pct_12w, \u2026, weeks }\n *  - All-time reviews            -> { total_reviews_all_time, positive_share_pct_all_time, \u2026 }\n *\n * Emits one row per company/location with normalized blocks:\n *  impressions, impressions_lastWeek, impressions_12w (+avg fields), vs12wAvg_impressions, vsLast_impressions\n *  reviews, reviews_lastWeek, reviews_12w (+avg fields), vsLast_reviews, vs12wAvg_reviews\n *  allTimeReviews (mapped if provided upstream)\n *****/\n\nfunction n(v){ const x = Number(v); return Number.isFinite(x) ? x : 0; }\nfunction nn(v){ return v == null ? null : Number(v); }\nconst round1 = v => (v == null ? null : +(+v).toFixed(1));\nconst pct = (num, den) => (den ? +(((+num)/(+den))*100).toFixed(1) : 0);\nconst pp = (a, b) => (a == null || b == null) ? null : +(+a - +b).toFixed(1);\nconst diffPct = (cur, prev) => (prev ? +(((+cur - +prev) / +prev) * 100).toFixed(1) : 0);\n\nfunction daysBetween(aISO, bISO){\n  const a = Date.parse(aISO), b = Date.parse(bISO);\n  return (Number.isFinite(a) && Number.isFinite(b)) ? Math.abs((b-a)/86400000) : null;\n}\nfunction weekSpanCount(startISO, endISO){\n  const d = daysBetween(startISO, endISO);\n  if (d == null) return null;\n  return Math.max(1, Math.round((d + 1) / 7));\n}\n\nfunction packImpressions(src, isLast=false){\n  if (!src) return null;\n\n  const s  = isLast ? n(src.impressions_search_last) : n(src.impressions_search);\n  const m  = isLast ? n(src.impressions_maps_last)   : n(src.impressions_maps);\n  const sd = isLast ? n(src.impressions_search_desktop_last) : n(src.impressions_search_desktop);\n  const sm = isLast ? n(src.impressions_search_mobile_last)  : n(src.impressions_search_mobile);\n  const md = isLast ? n(src.impressions_maps_desktop_last)   : n(src.impressions_maps_desktop);\n  const mm = isLast ? n(src.impressions_maps_mobile_last)    : n(src.impressions_maps_mobile);\n  const w  = isLast ? n(src.website_clicks_last) : n(src.website_clicks);\n  const c  = isLast ? n(src.call_clicks_last)    : n(src.call_clicks);\n  const d  = isLast ? n(src.direction_requests_last) : n(src.direction_requests);\n\n  const total = s + m;\n  const actions_total = w + c + d;\n  const ctr_actions_pct = total ? +((actions_total / total) * 100).toFixed(1) : 0;\n\n  return {\n    week_start: isLast ? (src.last_week_start || src.week_start || null) : (src.week_start || null),\n    week_end:   isLast ? (src.last_week_end   || src.week_end   || null) : (src.week_end   || null),\n\n    search: s, maps: m, total,\n    search_desktop: sd, search_mobile: sm,\n    maps_desktop: md, maps_mobile: mm,\n\n    website_clicks: w, call_clicks: c, direction_requests: d,\n    actions_total, ctr_actions_pct\n  };\n}\n\n// -------- collect + group ----------\nconst rows = $input.all().map(i => i.json);\nconst groups = new Map();\nfor (const r of rows){\n  const company = r.companyName || r.company || '(unknown)';\n  const loc = r.locationId ?? null;\n  const key = `${company}__${loc ?? 'null'}`;\n  if (!groups.has(key)) groups.set(key, []);\n  groups.get(key).push(r);\n}\n\nconst out = [];\n\nfor (const [key, list] of groups.entries()){\n  const companyName = list[0].companyName || list[0].company || '(unknown)';\n  const locationId  = list[0].locationId ?? null;\n\n  let thisW_imp = null, lastW_imp = null, block12_imp = null;\n  let thisW_rev = null, lastW_rev = null, block12_rev = null;\n  let allTime = null;\n\n  let anchor_last_start = null, anchor_last_end = null;\n\n  for (const r of list){\n    // impressions shapes\n    const hasImp = (r.impressions_search !== undefined || r.impressions_maps !== undefined);\n    const hasImpLast = (r.impressions_search_last !== undefined || r.website_clicks_last !== undefined);\n\n    if (hasImp && r.week_start && r.week_end) {\n      const spanDays = daysBetween(r.week_start, r.week_end);\n      if (spanDays != null && spanDays >= 60) block12_imp = r;   // ~12w rollup\n      else thisW_imp = r;                                        // 1 week\n    }\n    if (hasImpLast && (r.last_week_start || r.last_week_end)) lastW_imp = r;\n\n    // weekly reviews (this week) \u2014 take most recent\n    if (r.total_reviews !== undefined && r.week_start) {\n      thisW_rev = (thisW_rev == null || r.week_start >= thisW_rev.week_start) ? r : thisW_rev;\n    }\n    // explicit last-week reviews\n    if (r.total_reviews !== undefined && r.isLastWeek === true) lastW_rev = r;\n\n    // 12-week reviews summary (any of these keys)\n    if (r.total_reviews_12w !== undefined || r.positive_share_pct_12w !== undefined || r.weeks !== undefined) {\n      block12_rev = r;\n    }\n\n    // all-time reviews (map common variants)\n    if (!allTime && (r.total_reviews_all_time !== undefined || r.average_rating_all_time !== undefined ||\n                     r.all_time_total !== undefined || r.avg_rating_all_time !== undefined)) {\n      allTime = {\n        total: n(r.total_reviews_all_time ?? r.all_time_total),\n        positiveShare: nn(r.positive_share_pct_all_time ?? r.all_time_positive_share_pct) ?? 0,\n        averageRating: nn(r.average_rating_all_time ?? r.avg_rating_all_time),\n        replyRatePct:  nn(r.reply_rate_pct_all_time ?? r.all_time_reply_rate_pct) ?? 0,\n        medianReplyLatencyHours: nn(r.median_reply_latency_hours_all_time ?? r.all_time_median_reply_latency_hours),\n        lastReviewAt: r.last_review_at ?? r.all_time_last_review_at ?? null\n      };\n    }\n\n    // anchors\n    if (!anchor_last_start && (r.last_week_start || r.last_startISO)) {\n      anchor_last_start = r.last_week_start || (r.last_startISO && r.last_startISO.slice(0,10)) || null;\n    }\n    if (!anchor_last_end && (r.last_week_end || r.last_endISO)) {\n      anchor_last_end = r.last_week_end || (r.last_endISO && r.last_endISO.slice(0,10)) || null;\n    }\n  }\n\n  // try find last-week reviews by date if not explicitly flagged\n  if (!lastW_rev && anchor_last_start) {\n    lastW_rev = list.find(x => x.total_reviews !== undefined && x.week_start === anchor_last_start) || null;\n  }\n\n  // ---- impressions packing ----\n  const impressions          = packImpressions(thisW_imp, false);\n  const impressions_lastWeek = packImpressions(lastW_imp,  true);\n\n  // 12w impressions totals & avgs (and CTR avg)\n  let impressions_12w = null, vs12wAvg_impressions = null;\n  if (block12_imp){\n    const search = n(block12_imp.impressions_search);\n    const maps   = n(block12_imp.impressions_maps);\n    const total  = search + maps;\n\n    const clicks = n(block12_imp.website_clicks);\n    const calls  = n(block12_imp.call_clicks);\n    const dirs   = n(block12_imp.direction_requests);\n    const actions_total_12w = clicks + calls + dirs;\n\n    const weeks = weekSpanCount(block12_imp.week_start, block12_imp.week_end) || 12;\n    const avg_week_total = weeks ? +(total / weeks).toFixed(1) : 0;\n    const avg_week_actions_total = weeks ? +((actions_total_12w / weeks).toFixed(1)) : 0;\n    const avg_week_ctr_pct = avg_week_total ? +( (avg_week_actions_total / avg_week_total) * 100 ).toFixed(1) : 0;\n\n    impressions_12w = {\n      start: block12_imp.week_start || null,\n      end:   bl

Credentials you'll need

Each integration node will prompt for credentials when you import. We strip credential IDs before publishing — you'll add your own.

• googleSheetsOAuth2Api

About this workflow

Created by: Peyton Leveillee Last updated: October 2025

Source: https://n8n.io/workflows/9290/ — original creator credit. Request a take-down →

More AI & RAG workflows → · Browse all categories →

Related workflows

Workflows that share integrations, category, or trigger type with this one. All free to copy and import.