Googleレビュー - テンプレート2
上級
これは自動化ワークフローで、62個のノードを含みます。主にCode, Gmail, Merge, Slack, HttpRequestなどのノードを使用。 GPTによる洞察に基づくGoogle BusinessレポートをSlackとメールに自動送信
前提条件
- •Googleアカウント + Gmail API認証情報
- •Slack Bot Token または Webhook URL
- •ターゲットAPIの認証情報が必要な場合あり
- •Google Sheets API認証情報
- •OpenAI API Key
使用ノード (62)
カテゴリー
-
ワークフロープレビュー
ノード接続関係を可視化、ズームとパンをサポート
ワークフローをエクスポート
以下のJSON設定をn8nにインポートして、このワークフローを使用できます
{
"id": "ysIu0eYMKlOhtV8D",
"meta": {
"instanceId": "40f67202a0c7d6108d2560d7802e849a11ba1d2f1edc7c24c5fa903cab90a8f2",
"templateCredsSetupCompleted": true
},
"name": "Google Review - Template 2",
"tags": [],
"nodes": [
{
"id": "c6e82665-fa63-4cbf-9e83-dcdc9d99bba3",
"name": "OpenAI Chat Model4",
"type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
"position": [
-832,
2848
],
"parameters": {
"model": {
"__rl": true,
"mode": "list",
"value": "gpt-5",
"cachedResultName": "gpt-5"
},
"options": {}
},
"credentials": {
"openAiApi": {
"id": "0wxxfUy5p26AEoAX",
"name": "OpenAi account"
}
},
"typeVersion": 1.2
},
{
"id": "a83547f7-dfac-4064-b0ab-a98c4d5668c4",
"name": "企業情報を読み込む",
"type": "n8n-nodes-base.googleSheets",
"position": [
-4000,
2160
],
"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": {
"id": "ssM8fSaJL5EtFqLs",
"name": "Google Sheets account"
}
},
"typeVersion": 4
},
{
"id": "8d733b11-c5e6-4f3b-a065-3b5c7b44869a",
"name": "週次ウィンドウを構築2",
"type": "n8n-nodes-base.code",
"position": [
-3472,
2160
],
"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": "インプレッションデータを取得",
"type": "n8n-nodes-base.httpRequest",
"position": [
-3280,
2160
],
"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"
},
"credentials": {
"googleBusinessProfileOAuth2Api": {
"id": "JBlnixnNTbXzI9WJ",
"name": "Google Business Profile account 2"
}
},
"typeVersion": 4
},
{
"id": "2c9d403b-d03d-43fd-900e-60089927fc03",
"name": "フラット化",
"type": "n8n-nodes-base.code",
"position": [
-3104,
2160
],
"parameters": {
"jsCode": "// === Upstream node names (update if you renamed) ===\nconst META_NODE = 'Build Week Window2';\nconst HTTP_NODE = 'Get Impression data';\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 = 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 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: totals.BUSINESS_DIRECTION_REQUESTS || 0,\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": "シートにインプレッションを追加",
"type": "n8n-nodes-base.googleSheets",
"position": [
-2336,
2160
],
"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 "
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "ssM8fSaJL5EtFqLs",
"name": "Google Sheets account"
}
},
"typeVersion": 4
},
{
"id": "ef1c5e27-f336-4bbb-a668-a10abe6087c2",
"name": "企業を設定（レビュー）",
"type": "n8n-nodes-base.code",
"position": [
-3296,
2992
],
"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": "複数レビューを取得",
"type": "n8n-nodes-base.googleBusinessProfile",
"position": [
-3104,
2992
],
"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": {}
},
"credentials": {
"googleBusinessProfileOAuth2Api": {
"id": "snpnWRsJVydiU7oU",
"name": "Google Business Profile account"
}
},
"typeVersion": 1
},
{
"id": "c0315534-5395-4ba9-b4d8-d2554455b156",
"name": "週次ウィンドウを構築（レビュー）",
"type": "n8n-nodes-base.code",
"position": [
-3504,
2992
],
"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": "シートにレビューを追加",
"type": "n8n-nodes-base.googleSheets",
"position": [
-2720,
2992
],
"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 "
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "ssM8fSaJL5EtFqLs",
"name": "Google Sheets account"
}
},
"typeVersion": 4.7
},
{
"id": "30b1aa4a-255b-4d30-8ade-af80dabaab78",
"name": "週次レビューを要約",
"type": "n8n-nodes-base.code",
"position": [
-2528,
2992
],
"parameters": {
"jsCode": "// Weekly Reviews Summary — 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": "週次要約を追加",
"type": "n8n-nodes-base.googleSheets",
"position": [
-2336,
2992
],
"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 "
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "ssM8fSaJL5EtFqLs",
"name": "Google Sheets account"
}
},
"typeVersion": 4.7
},
{
"id": "50e504fd-5112-4f11-98c9-5f1b922d4a6a",
"name": "企業を設定（総合レビュー）",
"type": "n8n-nodes-base.code",
"position": [
-3488,
3616
],
"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": "12週間ウィンドウを構築",
"type": "n8n-nodes-base.code",
"position": [
-3488,
2512
],
"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週間インプレッション",
"type": "n8n-nodes-base.httpRequest",
"position": [
-3296,
2512
],
"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"
},
"credentials": {
"googleBusinessProfileOAuth2Api": {
"id": "JBlnixnNTbXzI9WJ",
"name": "Google Business Profile account 2"
}
},
"typeVersion": 4
},
{
"id": "b954b33c-c0b8-426e-bdc7-127499c93f36",
"name": "12週間データをフラット化",
"type": "n8n-nodes-base.code",
"position": [
-3120,
2512
],
"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 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 // collect totals per metric name\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 // 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": "シートに12週間インプレッションを追加",
"type": "n8n-nodes-base.googleSheets",
"position": [
-2336,
2512
],
"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 "
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "ssM8fSaJL5EtFqLs",
"name": "Google Sheets account"
}
},
"typeVersion": 4
},
{
"id": "daa03d24-bc82-4a11-961e-e47777a5e8cd",
"name": "全期間要約を追加",
"type": "n8n-nodes-base.googleSheets",
"position": [
-2336,
3616
],
"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 "
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "ssM8fSaJL5EtFqLs",
"name": "Google Sheets account"
}
},
"typeVersion": 4.7
},
{
"id": "fae71ed4-93b7-4d38-b996-871dad806fc2",
"name": "全レビューを取得",
"type": "n8n-nodes-base.googleBusinessProfile",
"position": [
-3296,
3616
],
"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": {}
},
"credentials": {
"googleBusinessProfileOAuth2Api": {
"id": "snpnWRsJVydiU7oU",
"name": "Google Business Profile account"
}
},
"typeVersion": 1
},
{
"id": "5ba35759-85e4-4fae-a4a0-b0b9946ec0b3",
"name": "マージ2",
"type": "n8n-nodes-base.merge",
"position": [
-1664,
2560
],
"parameters": {
"numberInputs": 7
},
"typeVersion": 3.2
},
{
"id": "1a372bcf-358e-42c3-b225-82690917e5e0",
"name": "インプレッションを取得（先週）",
"type": "n8n-nodes-base.httpRequest",
"position": [
-3280,
2336
],
"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"
},
"credentials": {
"googleBusinessProfileOAuth2Api": {
"id": "JBlnixnNTbXzI9WJ",
"name": "Google Business Profile account 2"
}
},
"typeVersion": 4
},
{
"id": "f9ce0523-f032-4e67-9246-37bfd77310f6",
"name": "フラット化（先週）",
"type": "n8n-nodes-base.code",
"position": [
-3104,
2336
],
"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": "すべてを結合",
"type": "n8n-nodes-base.code",
"position": [
-1424,
2640
],
"parameters": {
"jsCode": "/***** Join All — 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 (…_last + last_week_start/last_week_end)\n * - 12-week impressions rollup\n * - Weekly reviews (this week) -> { total_reviews, positive, neutral, negative, …, week_start/week_end }\n * - Weekly reviews (last week) -> { … , isLastWeek:true } OR object with week_start == last_week_start\n * - 12-week reviews summary -> { total_reviews_12w, positive_share_pct_12w, …, weeks }\n * - All-time reviews -> { total_reviews_all_time, positive_share_pct_all_time, … }\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) — 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: block12_imp.week_end || null,\n search, maps, total,\n website_clicks: clicks,\n call_clicks: calls,\n direction_requests: dirs,\n search_desktop: n(block12_imp.impressions_search_desktop),\n search_mobile: n(block12_imp.impressions_search_mobile),\n maps_desktop: n(block12_imp.impressions_maps_desktop),\n maps_mobile: n(block12_imp.impressions_maps_mobile),\n avg_week_total,\n avg_week_actions_total,\n avg_week_ctr_pct\n };\n\n if (impressions){\n const diff = +(impressions.total - avg_week_total).toFixed(1);\n const pctVs = avg_week_total ? +((impressions.total/avg_week_total)*100).toFixed(1) : 0;\n vs12wAvg_impressions = {\n weeks,\n avg_week_total,\n pct_vs_avg: pctVs,\n diff_from_avg: diff,\n arrow: diff > 0 ? '↑' : diff < 0 ? '↓' : '↔'\n };\n }\n }\n\n // vs last week (impressions) — include CTR delta\n let vsLast_impressions = null, impressions_delta = null;\n if (impressions && impressions_lastWeek){\n const tThis = impressions.total, tLast = impressions_lastWeek.total;\n const diffAbs = tThis - tLast;\n impressions_delta = { arrow: diffAbs>0?'↑':diffAbs<0?'↓':'↔', diffAbs };\n vsLast_impressions = {\n last_total: tLast,\n diff_total: diffAbs,\n pct_change_total: diffPct(tThis, tLast),\n ctr_this: impressions.ctr_actions_pct,\n ctr_last: impressions_lastWeek.ctr_actions_pct,\n ctr_diff: pp(impressions.ctr_actions_pct, impressions_lastWeek.ctr_actions_pct)\n };\n }\n\n // ---- reviews (this & last) ----\n const reviews = thisW_rev ? {\n total: n(thisW_rev.total_reviews),\n pos: n(thisW_rev.positive),\n neu: n(thisW_rev.neutral),\n neg: n(thisW_rev.negative),\n positiveShare: nn(thisW_rev.positive_share_pct) ?? 0,\n averageRating: nn(thisW_rev.average_rating),\n replyRatePct: nn(thisW_rev.reply_rate_pct) ?? 0,\n medianReplyLatencyHours: nn(thisW_rev.median_reply_latency_hours)\n } : { total:0, pos:0, neu:0, neg:0, positiveShare:0, averageRating:null, replyRatePct:0, medianReplyLatencyHours:null };\n\n const reviews_lastWeek = lastW_rev ? {\n total: n(lastW_rev.total_reviews),\n pos: n(lastW_rev.positive),\n neu: n(lastW_rev.neutral),\n neg: n(lastW_rev.negative),\n positiveShare: nn(lastW_rev.positive_share_pct) ?? 0,\n averageRating: nn(lastW_rev.average_rating),\n replyRatePct: nn(lastW_rev.reply_rate_pct) ?? 0,\n medianReplyLatencyHours: nn(lastW_rev.median_reply_latency_hours),\n week_start: lastW_rev.week_start || null,\n week_end: lastW_rev.week_end || null\n } : null;\n\n // ---- 12-week reviews rollup ----\n let reviews_12w = null, vsLast_reviews = null, vs12wAvg_reviews = null;\n if (block12_rev){\n const weeks = n(block12_rev.weeks) || 12;\n const total12 = n(block12_rev.total_reviews_12w);\n const pos12 = n(block12_rev.positive_12w);\n const neu12 = n(block12_rev.neutral_12w);\n const neg12 = n(block12_rev.negative_12w);\n\n const avg_total = weeks ? +(total12 / weeks).toFixed(1) : 0;\n const avg_pos = weeks ? +(pos12 / weeks).toFixed(1) : 0;\n const avg_neu = weeks ? +(neu12 / weeks).toFixed(1) : 0;\n const avg_neg = weeks ? +(neg12 / weeks).toFixed(1) : 0;\n\n const avg_positiveShare = nn(block12_rev.positive_share_pct_12w) ?? 0;\n const avg_rating = nn(block12_rev.average_rating_12w);\n const avg_replyRatePct = nn(block12_rev.reply_rate_pct_12w) ?? 0;\n const avg_medianReplyLatencyHours = nn(block12_rev.median_reply_latency_hours_12w);\n\n reviews_12w = {\n weeks,\n total: total12,\n pos: pos12, neu: neu12, neg: neg12,\n avg_total, avg_pos, avg_neu, avg_neg,\n avg_positiveShare, avg_rating, avg_replyRatePct, avg_medianReplyLatencyHours\n };\n\n if (reviews){\n const diff = +(reviews.total - avg_total).toFixed(1);\n const pctVs = avg_total ? +((reviews.total/avg_total)*100).toFixed(1) : 0;\n vs12wAvg_reviews = {\n pct_vs_avg: pctVs,\n diff_from_avg: diff,\n arrow: diff > 0 ? '↑' : diff < 0 ? '↓' : '↔'\n };\n }\n }\n\n if (reviews && reviews_lastWeek){\n const diff = reviews.total - reviews_lastWeek.total;\n vsLast_reviews = {\n last_total: reviews_lastWeek.total,\n diff_total: diff,\n pct_change_total: diffPct(reviews.total, reviews_lastWeek.total)\n };\n }\n\n // ---- emit ----\n out.push({\n json: {\n companyName,\n locationId,\n week_start: impressions?.week_start || thisW_rev?.week_start || null,\n week_end: impressions?.week_end || thisW_rev?.week_end || null,\n\n impressions: impressions || null,\n impressions_lastWeek: impressions_lastWeek || null,\n impressions_12w: impressions_12w || null,\n vs12wAvg_impressions,\n vsLast_impressions,\n impressions_delta: vsLast_impressions ? { arrow: vsLast_impressions.diff_total>0?'↑':vsLast_impressions.diff_total<0?'↓':'↔', diffAbs: vsLast_impressions.diff_total } : null,\n\n reviews,\n reviews_lastWeek,\n reviews_12w,\n vsLast_reviews,\n vs12wAvg_reviews,\n\n // new: all-time (if present upstream)\n allTimeReviews: allTime || {}\n }\n });\n}\n\nreturn out;"
},
"typeVersion": 2
},
{
"id": "8d45eeac-97ce-4dbb-a6ec-7df9e3b5d05f",
"name": "OpenAI Chat Model2",
"type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
"position": [
-816,
3264
],
"parameters": {
"model": {
"__rl": true,
"mode": "list",
"value": "gpt-5",
"cachedResultName": "gpt-5"
},
"options": {}
},
"credentials": {
"openAiApi": {
"id": "0wxxfUy5p26AEoAX",
"name": "OpenAi account"
}
},
"typeVersion": 1.2
},
{
"id": "fee14269-084f-45d7-92e0-1a99da5cc2f6",
"name": "レビューパーサー",
"type": "@n8n/n8n-nodes-langchain.outputParserStructured",
"position": [
-688,
2832
],
"parameters": {
"schemaType": "manual",
"inputSchema": "{\n \"type\": \"object\",\n \"additionalProperties\": false,\n \"required\": [\"companyKey\", \"oneLinerReviews\"],\n \"properties\": {\n \"companyKey\": { \"type\": \"string\", \"minLength\": 1 },\n \"oneLinerReviews\": { \"type\": \"string\", \"minLength\": 6, \"maxLength\": 200 }\n }\n}"
},
"typeVersion": 1.3
},
{
"id": "54e4abfc-d9c1-4822-a3a1-87662c0b0c7d",
"name": "インプレッションパーサー",
"type": "@n8n/n8n-nodes-langchain.outputParserStructured",
"position": [
-672,
3264
],
"parameters": {
"schemaType": "manual",
"inputSchema": "{\n \"type\": \"object\",\n \"additionalProperties\": false,\n \"required\": [\"companyKey\", \"oneLinerImpressions\"],\n \"properties\": {\n \"companyKey\": { \"type\": \"string\", \"minLength\": 1 },\n \"oneLinerImpressions\": { \"type\": \"string\", \"minLength\": 6, \"maxLength\": 200 }\n }\n}"
},
"typeVersion": 1.3
},
{
"id": "aa759b90-5e5b-494f-9271-cc93e2e61523",
"name": "全期間レビュー → KPI（LLMなし）",
"type": "n8n-nodes-base.code",
"position": [
-3104,
3616
],
"parameters": {
"jsCode": "// All-time Reviews → KPIs (no LLM)\n// Inputs:\n// - Get Many Reviews (all reviews for each location)\n// - Set Company (total reviews) (company ↔ locationId mapping)\n// Output: 1 item per company+location with lifetime KPIs\n\n// --- helpers ---\nfunction normIso(s){ return s ? String(s).replace(/\\.(\\d{3})\\d*Z$/, '.$1Z') : null; }\nfunction msSafe(s){ const t = Date.parse(normIso(s)); return Number.isFinite(t) ? t : null; }\nfunction toNum(star){ const m={ONE:1,TWO:2,THREE:3,FOUR:4,FIVE:5}; return typeof star==='number'?star:(m[String(star||'').toUpperCase()] ?? 0); }\nfunction pickLocationId(j){\n if (j.locationId) return String(j.locationId);\n if (j.locationPath) return String(j.locationPath).split('/').pop();\n if (j.location) return String(j.location).split('/').pop();\n if (j.name) {\n const p = String(j.name).split('/'); const i=p.indexOf('locations');\n if (i>-1 && p[i+1]) return p[i+1];\n }\n return null;\n}\nfunction inferSentiment(j){\n // rating-only heuristic (fast, consistent)\n const n = Number(j.ratingNum || toNum(j.starRating));\n if (!Number.isFinite(n) || n <= 0) return null;\n if (n === 3) return 'Neutral';\n if (n >= 4) return 'Positive';\n if (n <= 2) return 'Negative';\n return null;\n}\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\n// build company index from Set Company (total reviews)\nconst seed = $items('Set Company (total reviews)') || [];\nconst companyIndex = new Map();\nfor (const { json:s } of seed) {\n const locId = (s.locationId && String(s.locationId))\n || (s.locationPath && String(s.locationPath).split('/').pop())\n || null;\n if (locId) companyIndex.set(locId, s.companyName || s.company || '(unknown)');\n}\n\n// normalize all incoming reviews\nconst rows = [];\nfor (const { json } of $input.all()){\n const locationId = pickLocationId(json);\n const companyName = json.companyName || companyIndex.get(locationId) || '(unknown)';\n if (!locationId) continue; // skip ghosts\n\n const ratingNum = Number(json.ratingNum || toNum(json.starRating) || 0);\n const createISO = json.createTime || json.created || null;\n\n // reply latency\n let latency = (typeof json.replyLatencyHours === 'number') ? json.replyLatencyHours : null;\n if (latency === null && json.reviewReply?.updateTime && createISO) {\n const msC = msSafe(createISO), msR = msSafe(json.reviewReply.updateTime);\n if (msC != null && msR != null) latency = Math.round(((msR - msC)/36e5)*100)/100;\n }\n\n rows.push({\n companyName,\n locationId,\n ratingNum,\n sentiment: inferSentiment(json),\n hasReply: !!(json.hasReply || json.reviewReply?.comment),\n replyLatencyHours: (typeof latency === 'number') ? latency : null,\n createdMs: msSafe(createISO),\n });\n}\n\n// aggregate per company+location\nconst byKey = new Map();\nfor (const r of rows) {\n if (!r.sentiment) continue; // skip unrated/unknown\n const key = `${r.companyName}__${r.locationId}`;\n if (!byKey.has(key)) byKey.set(key, []);\n byKey.get(key).push(r);\n}\n\nconst out = [];\nfor (const [, list] of byKey.entries()){\n const companyName = list[0].companyName;\n const locationId = list[0].locationId;\n\n const total = list.length;\n const pos = list.filter(x => x.sentiment === 'Positive').length;\n const neu = list.filter(x => x.sentiment === 'Neutral').length;\n const neg = list.filter(x => x.sentiment === 'Negative').length;\n\n const ratings = list.map(x => Number(x.ratingNum||0)).filter(Number.isFinite);\n const average_rating = avg(ratings);\n const positive_share_pct = total ? +((pos/total)*100).toFixed(1) : 0;\n\n const replied = list.filter(x => x.hasReply === true).length;\n const reply_rate_pct = total ? +((replied/total)*100).toFixed(1) : 0;\n\n const latencies = list.map(x => (typeof x.replyLatencyHours === 'number') ? x.replyLatencyHours : null).filter(v=>v!==null);\n const median_reply_latency_hours = median(latencies);\n\n const lastReviewAt = list.map(x => x.createdMs).filter(Number.isFinite).sort((a,b)=>a-b).pop() || null;\n\n out.push({\n json: {\n companyName,\n locationId,\n total_reviews_all_time: total,\n positive_all_time: pos,\n neutral_all_time: neu,\n negative_all_time: neg,\n positive_share_pct_all_time: positive_share_pct,\n average_rating_all_time: average_rating,\n reply_rate_pct_all_time: reply_rate_pct,\n median_reply_latency_hours_all_time: median_reply_latency_hours,\n last_review_at: lastReviewAt ? new Date(lastReviewAt).toISOString() : null\n }\n });\n}\n\nreturn out;"
},
"typeVersion": 2
},
{
"id": "7dfc29b9-989e-4ded-8b41-bd21f99015c7",
"name": "週次レビューをマッピング",
"type": "n8n-nodes-base.code",
"position": [
-2896,
2992
],
"parameters": {
"jsCode": "// Map + categorize Weekly Google Reviews in ONE node\n// Depends on:\n// - Set Company (reviews): creates company index (companyName, locationPath)\n// - Build Week Window (reviews): provides startISO/endISO (or it will fallback to Build Week Window2)\n\nconst companies = $items('Set Company (reviews)') || [];\nif (!companies.length) throw new Error('Set Company (reviews) not found or empty');\n\n// Get week window\nconst winItems = $items('Build Week Window (reviews)') || $items('Build Week Window2') || [];\nconst win = winItems[0]?.json || {};\nconst week_start = win.startISO?.slice(0,10) || win.week_start || null;\nconst week_end = win.endISO?.slice(0,10) || win.week_end || null;\n\n// Build locationId -> companyName index\nconst companyIndex = new Map();\nfor (const {json:c} of companies) {\n const locId = (c.locationPath || '').split('/').pop();\n if (locId) companyIndex.set(String(locId), c.companyName || c.company || '(unknown)');\n}\n\n// Helpers\nfunction toNum(star){\n const m = { ONE:1, TWO:2, THREE:3, FOUR:4, FIVE:5 };\n return typeof star === 'number' ? star : (m[String(star||'').toUpperCase()] ?? 0);\n}\nfunction normIso(s){ return s ? String(s).replace(/\\.(\\d{3})\\d*Z$/, '.$1Z') : null; }\nfunction msSafe(s){ const t = s ? Date.parse(normIso(s)) : NaN; return Number.isFinite(t) ? t : null; }\nfunction withinWeek(ms){\n if (!ms) return false;\n const startMs = msSafe(week_start+'T00:00:00Z');\n const endMs = msSafe(week_end +'T23:59:59Z'); // inclusive\n return Number.isFinite(startMs) && Number.isFinite(endMs) && ms >= startMs && ms <= endMs;\n}\nfunction classify(ratingNum){\n const n = Number(ratingNum || 0);\n if (!Number.isFinite(n) || n <= 0) return null;\n if (n === 3) return 'Neutral';\n if (n >= 4) return 'Positive';\n if (n <= 2) return 'Negative';\n return null;\n}\n\nconst out = [];\nfor (const {json:r} of $input.all()){\n // IDs\n let locationId = r.locationId ? String(r.locationId) : null;\n let reviewId = r.reviewId ? String(r.reviewId) : null;\n\n if ((!locationId || !reviewId) && r.name) {\n const p = String(r.name).split('/');\n const li = p.indexOf('locations'); if (li > -1 && p[li+1]) locationId ||= p[li+1];\n const ri = p.indexOf('reviews'); if (ri > -1 && p[ri+1]) reviewId ||= p[ri+1];\n }\n\n const createdMs = msSafe(r.createTime || r.created);\n if (!withinWeek(createdMs)) continue; // keep only this week's reviews\n\n const replyMs = msSafe(r.reviewReply?.updateTime);\n const ratingNum = Number(r.ratingNum || toNum(r.starRating) || 0);\n const sentiment_label = classify(ratingNum);\n\n const companyName = r.companyName || companyIndex.get(locationId) || '(unknown)';\n\n out.push({\n json: {\n companyName,\n locationId,\n reviewId,\n createTime: createdMs ? new Date(createdMs).toISOString() : null,\n\n ratingNum,\n ratingText: r.ratingText || r.starRating || null,\n comment: (r.comment || '').toString(),\n\n hasReply: !!r.reviewReply?.comment,\n replyLatencyHours: (replyMs && createdMs) ? Math.round(((replyMs - createdMs)/36e5)*100)/100 : null,\n\n // weekly fields for grouping\n week_start,\n week_end,\n\n // sentiment\n sentiment_label,\n sentimentCategory: sentiment_label,\n\n // stable key if you need it\n key: [companyName, locationId || 'noLocation', week_start || 'noStart'].join('__'),\n }\n });\n}\n\nreturn out;"
},
"typeVersion": 2
},
{
"id": "b69c6393-95ac-4234-9129-9d12653b4919",
"name": "スケジュールトリガー",
"type": "n8n-nodes-base.scheduleTrigger",
"position": [
-4448,
2160
],
"parameters": {
"rule": {
"interval": [
{
"field": "weeks",
"triggerAtHour": 18
}
]
}
},
"typeVersion": 1.2
},
{
"id": "42790f95-855c-439e-8ea2-b9a938a0ba55",
"name": "メールを作成",
"type": "n8n-nodes-base.code",
"position": [
1232,
2912
],
"parameters": {
"mode": "runOnceForEachItem",
"jsCode": "// n8n Code node — Build final HTML email from:\n// - Base metrics from node \"format\" (single item)\n// - Three one-liners from current input (Append Merge produced 3 items)\n// Output: one item with { subject, html }\n\nfunction num(n) { return Number(n) || 0; }\nfunction fmtInt(n) { return num(n).toLocaleString('en-US'); }\nfunction fmtPct(n, dp = 0) { return `${(Number(n) || 0).toFixed(dp)}%`; }\nfunction fmtHours(h) { const v = Number(h) || 0; return `${Math.round(v*10)/10}h`; }\nfunction nowStamp() { return new Date().toLocaleString('en-US', { hour: 'numeric', minute: '2-digit' }); }\nfunction fmtDateISOtoMdy(iso) { if (!iso) return ''; const d = new Date(iso); return isNaN(d) ? iso : d.toLocaleDateString('en-US'); }\n\n// ---------- 1) Load the base formatted item (match by locationId)\nconst me = $json; // current grouped company item\nconst myLoc = String(me.locationId || \"\").trim();\n\n// find the matching \"format\" item for this locationId\nconst baseList = $items('format', 0, 0).map(i => i.json || {});\nconst base = baseList.find(x => String(x.locationId || \"\").trim() === myLoc) || {};\nconst companyName = (base.companyName || me.company || '').trim();\nconst locationId = myLoc;\nconst periodStart = base.week_start;\nconst periodEnd = base.week_end;\n\n// Convenience: display numbers\nconst disp = base.display || {};\nconst dispImpr = disp.impressions || {};\nconst dispRev = disp.reviews || {};\n\nconst thisImpr = dispImpr.this || {};\nconst lastImpr = dispImpr.last || {};\nconst avgImpr = dispImpr.avg || {};\n\nconst thisRev = dispRev.this || {};\nconst lastRev = dispRev.last || {};\nconst avgRev = dispRev.avg || {};\n\n// KPI deltas (vs 12w) from base.kpi\nconst kpi = base.kpi || {};\nconst kpiImprDelta = Number(kpi.impressions_vs12w_pct || 0);\nconst kpiActDelta = Number(kpi.actions_vs12w_pct || 0);\nconst kpiCtrPp = Number(kpi.ctr_vs12w_pp || 0);\nconst kpiRevCnt = Number(kpi.reviews_vs12w_count || 0);\n\n// ---------- 2) Read three one-liners from current item (already grouped)\nconst liners = {\n overall: String(($json.lines && $json.lines.overall) || '').trim(),\n impressions: String(($json.lines && $json.lines.impressions) || '').trim(),\n reviews: String(($json.lines && $json.lines.reviews) || '').trim(),\n};\n\n// ---------- 3) KPI badge helpers (vs 12w)\nfunction kpiBadgePct(val) {\n const v = Number(val) || 0;\n if (Math.abs(v) <= 1) return { text: '= vs 12w', cls:'pill pill-gray' };\n if (v < 0) return { text: `▼ ${Math.abs(Math.round(v))}% vs 12w`, cls:'pill pill-red' };\n return { text: `▲ ${Math.round(v)}% vs 12w`, cls:'pill pill-green' };\n}\nfunction kpiBadgePp(pp) {\n const v = Number(pp) || 0;\n if (Math.abs(v) < 0.5) return { text: '= vs 12w', cls:'pill pill-gray' };\n if (v > 0) return { text: `▲ ${v.toFixed(1)} pp vs 12w`, cls:'pill pill-green' };\n return { text: `▼ ${Math.abs(v).toFixed(1)} pp vs 12w`, cls:'pill pill-red' };\n}\nfunction kpiBadgeCount(delta) {\n const v = Number(delta) || 0;\n if (Math.abs(v) < 0.2) return { text: '= vs 12w', cls:'pill pill-gray' };\n if (v < 0) return { text: `▼ ${Math.abs(v).toFixed(1)} vs 12w`, cls:'pill pill-red' };\n return { text: `▲ ${v.toFixed(1)} vs 12w`, cls:'pill pill-green' };\n}\n\nconst badgeImpr = kpiBadgePct(kpiImprDelta);\nconst badgeAct = kpiBadgePct(kpiActDelta);\nconst badgeCtr = kpiBadgePp(kpiCtrPp);\nconst badgeRev = kpiBadgeCount(kpiRevCnt);\n\n// ---------- 4) HTML (KPI pills won’t clip thanks to .pill class)\nconst reportingStr = `Reporting period: ${fmtDateISOtoMdy(periodStart)}–${fmtDateISOtoMdy(periodEnd)} • Generated: ${fmtDateISOtoMdy(new Date().toISOString())}, ${nowStamp()}`;\n\nconst html = `<!DOCTYPE html>\n<html lang=\"en\" xmlns=\"http://www.w3.org/1999/xhtml\">\n<head>\n <meta charset=\"utf-8\">\n <meta name=\"viewport\" content=\"width=device-width\">\n <meta http-equiv=\"X-UA-Compatible\" content=\"IE=edge\">\n <title>Weekly Google Reviews & Impressions</title>\n <style>\n @media screen and (max-width:640px){\n .container{width:100% !important}\n .stack, .stack td{display:block !important; width:100% !important}\n .col{display:block !important; width:100% !important}\n .p-24{padding:16px !important}\n .kpi{width:100% !important; display:block !important; margin-bottom:8px !important}\n }\n /* Anti-clipping chips */\n .pill{display:inline-block; padding:3px 6px; border-radius:9999px; line-height:1; mso-line-height-rule:exactly;}\n .pill-red{background:#fee2e2; color:#991b1b;}\n .pill-gray{background:#e2e8f0; color:#334155;}\n .pill-green{background:#dcfce7; color:#166534;}\n .kpi td{overflow:visible !important;}\n </style>\n</head>\n<body style=\"margin:0; padding:0; background:#f5f7fb;\">\n <table role=\"presentation\" cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" style=\"background:#f5f7fb;\">\n <tr>\n <td align=\"center\" style=\"padding:24px 12px;\">\n\n <!-- Container -->\n <table role=\"presentation\" cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"680\" class=\"container\" style=\"width:680px; max-width:680px; background:#ffffff; border-radius:14px; overflow:hidden; font-family:Arial, Helvetica, sans-serif; color:#0f172a;\">\n\n <!-- Header -->\n <tr>\n <td style=\"padding:22px 24px; background:#0f172a; color:#ffffff;\">\n <div style=\"font-size:18px; font-weight:700;\">Weekly Google Reviews & Impressions</div>\n <div style=\"font-size:12px; opacity:0.9; margin-top:6px;\">${reportingStr}</div>\n <div style=\"font-size:12px; opacity:0.9;\">Company: ${companyName}</div>\n </td>\n </tr>\n\n <!-- KPI Row -->\n <tr>\n <td class=\"p-24\" style=\"padding:20px 24px 8px 24px;\">\n <table role=\"presentation\" cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\">\n <tr>\n\n <!-- Impressions -->\n <td class=\"kpi\" width=\"25%\" valign=\"top\" style=\"width:25%; padding:8px;\">\n <table role=\"presentation\" cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" style=\"background:#fafafa; border:1px solid #e5e7eb; border-radius:10px;\">\n <tr><td style=\"padding:10px 12px; font-size:11px; color:#475569;\">Impressions</td></tr>\n <tr><td style=\"padding:0 12px 10px 12px; font-size:20px; font-weight:700;\">${fmtInt(thisImpr.impr)}</td></tr>\n <tr><td style=\"padding:0 12px 12px 12px; font-size:11px;\"><span class=\"${badgeImpr.cls}\">${badgeImpr.text}</span></td></tr>\n </table>\n </td>\n\n <!-- Actions -->\n <td class=\"kpi\" width=\"25%\" valign=\"top\" style=\"width:25%; padding:8px;\">\n <table role=\"presentation\" cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" style=\"background:#fafafa; border:1px solid #e5e7eb; border-radius:10px;\">\n <tr><td style=\"padding:10px 12px; font-size:11px; color:#475569;\">Actions</td></tr>\n <tr><td style=\"padding:0 12px 10px 12px; font-size:20px; font-weight:700;\">${fmtInt(thisImpr.act)}</td></tr>\n <tr><td style=\"padding:0 12px 12px 12px; font-size:11px;\"><span class=\"${badgeAct.cls}\">${badgeAct.text}</span></td></tr>\n </table>\n </td>\n\n <!-- CTR -->\n <td class=\"kpi\" width=\"25%\" valign=\"top\" style=\"width:25%; padding:8px;\">\n <table role=\"presentation\" cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" style=\"background:#fafafa; border:1px solid #e5e7eb; border-radius:10px;\">\n <tr><td style=\"padding:10px 12px; font-size:11px; color:#475569;\">CTR</td></tr>\n <tr><td style=\"padding:0 12px 10px 12px; font-size:20px; font-weight:700;\">${fmtPct(thisImpr.ctr)}</td></tr>\n <tr><td style=\"padding:0 12px 12px 12px; font-size:11px;\"><span class=\"${badgeCtr.cls}\">${badgeCtr.text}</span></td></tr>\n </table>\n </td>\n\n <!-- Reviews -->\n <td class=\"kpi\" width=\"25%\" valign=\"top\" style=\"width:25%; padding:8px;\">\n <table role=\"presentation\" cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" style=\"background:#fafafa; border:1px solid #e5e7eb; border-radius:10px;\">\n <tr><td style=\"padding:10px 12px; font-size:11px; color:#475569;\">Reviews</td></tr>\n <tr><td style=\"padding:0 12px 10px 12px; font-size:20px; font-weight:700;\">${fmtInt(thisRev.total)}</td></tr>\n <tr><td style=\"padding:0 12px 12px 12px; font-size:11px;\"><span class=\"${badgeRev.cls}\">${badgeRev.text}</span></td></tr>\n </table>\n </td>\n\n </tr>\n </table>\n </td>\n </tr>\n\n <!-- GLOBAL ONE-LINER SUMMARY -->\n <tr>\n <td style=\"padding:0 24px 12px 24px;\">\n <table role=\"presentation\" cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" style=\"background:#f8fafc; border:1px solid #e5e7eb; border-radius:10px;\">\n <tr>\n <td style=\"padding:12px 14px; font-size:13px; color:#334155;\">\n <strong>Overall:</strong> ${liners.overall || '—'}\n </td>\n </tr>\n </table>\n </td>\n </tr>\n\n <!-- Two Columns -->\n <tr>\n <td class=\"p-24\" style=\"padding:8px 24px 20px;\">\n <table role=\"presentation\" cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" class=\"stack\">\n <tr>\n\n <!-- Impressions & Actions -->\n <td class=\"col\" width=\"50%\" valign=\"top\" style=\"width:50%; padding-right:10px;\">\n <table role=\"presentation\" cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" style=\"border:1px solid #e5e7eb; border-radius:12px;\">\n <tr><td style=\"background:#f1f5f9; padding:10px 14px; font-size:13px; font-weight:700;\">Impressions & Actions</td></tr>\n <tr>\n <td style=\"padding:12px 14px; font-size:13px; line-height:1.6;\">\n <div style=\"margin-bottom:10px; padding:10px 12px; background:#f8fafc; border:1px solid #e5e7eb; border-radius:10px; font-size:12px; color:#334155;\"><strong>Summary:</strong> ${liners.impressions || '—'}</div>\n\n <div style=\"margin-bottom:8px;\"><strong style=\"color:#0f172a;\">This week:</strong> ${fmtInt(thisImpr.impr)} impr • ${fmtInt(thisImpr.act)} actions • ${fmtPct(thisImpr.ctr)}</div>\n\n <div style=\"margin-bottom:8px;\">\n <strong style=\"color:#0f172a;\">Last week:</strong> ${fmtInt(lastImpr.impr)} impr • ${fmtInt(lastImpr.act)} actions • ${fmtPct(lastImpr.ctr)}\n <span class=\"pill pill-red\" style=\"margin-left:6px;\">▼ ${Math.abs(Number(base.deltas?.impr_wow_pct || 0))}% impr</span>\n <span class=\"pill pill-red\" style=\"margin-left:4px;\">▼ ${Math.abs(Number(base.deltas?.act_wow_pct || 0))}% actions</span>\n <span class=\"${(Number(base.deltas?.ctr_wow_pp)||0)>=0? 'pill pill-green':'pill pill-red'}\" style=\"margin-left:4px;\">${(Number(base.deltas?.ctr_wow_pp)||0)>=0? '▲ ':'▼ '}${Math.abs(Number(base.deltas?.ctr_wow_pp||0)).toFixed(1)} pp CTR</span>\n </div>\n\n <div>\n <strong style=\"color:#0f172a;\">12-wk avg:</strong> ${fmtInt(avgImpr.impr)} impr • ${fmtInt(avgImpr.act)} actions • ${fmtPct(avgImpr.ctr)}\n <span class=\"pill pill-red\" style=\"margin-left:6px;\">▼ ${Math.abs(Number(base.deltas?.impr_vsavg_pct || 0))}% impr</span>\n <span class=\"pill pill-red\" style=\"margin-left:4px;\">▼ ${Math.abs(Number(base.deltas?.act_vsavg_pct || 0))}% actions</span>\n <span class=\"${Math.abs(Number(base.deltas?.ctr_vsavg_pp||0))<0.5 ? 'pill pill-gray' : ((Number(base.deltas?.ctr_vsavg_pp)||0)>0? 'pill pill-green':'pill pill-red')}\" style=\"margin-left:4px;\">\n ${Math.abs(Number(base.deltas?.ctr_vsavg_pp||0))<0.5 ? '= CTR' : ((Number(base.deltas?.ctr_vsavg_pp)||0)>0 ? '▲ '+Number(base.deltas?.ctr_vsavg_pp).toFixed(1)+' pp CTR' : '▼ '+Math.abs(Number(base.deltas?.ctr_vsavg_pp)).toFixed(1)+' pp CTR')}\n </span>\n </div>\n\n </td>\n </tr>\n </table>\n </td>\n\n <!-- Reviews -->\n <td class=\"col\" width=\"50%\" valign=\"top\" style=\"width:50%; padding-left:10px;\">\n <table role=\"presentation\" cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" style=\"border:1px solid #e5e7eb; border-radius:12px;\">\n <tr><td style=\"background:#f1f5f9; padding:10px 14px; font-size:13px; font-weight:700;\">Reviews</td></tr>\n <tr>\n <td style=\"padding:12px 14px; font-size:13px; line-height:1.6;\">\n\n <div style=\"margin-bottom:10px; padding:10px 12px; background:#f8fafc; border:1px solid #e5e7eb; border-radius:10px; font-size:12px; color:#334155;\"><strong>Summary:</strong> ${liners.reviews || '—'}</div>\n\n <div style=\"margin-bottom:8px;\">\n <strong style=\"color:#0f172a;\">This week:</strong>\n ${fmtInt(thisRev.total)} reviews • ${fmtInt(thisRev.pos)}/0/0 (Pos/Neu/Neg) • 5.0★ • ${fmtPct(thisRev.pos)} positive • Replies ${fmtPct(thisRev.replies)} • Median reply ${fmtHours(thisRev.hours)}\n </div>\n\n <div style=\"margin-bottom:8px;\">\n <strong style=\"color:#0f172a;\">Last week:</strong>\n ${fmtInt(lastRev.total)} reviews • ${fmtInt(lastRev.pos)}/0/0 • 5.0★ • ${fmtPct(lastRev.pos)} positive • Replies ${fmtPct(lastRev.replies)} • Median reply ${fmtHours(lastRev.hours)}\n <span class=\"pill pill-red\" style=\"margin-left:6px;\">▼ ${Math.abs(Number(base.vsLast_reviews?.diff_total || 0))} review${Math.abs(Number(base.vsLast_reviews?.diff_total || 0))===1?'':'s'}</span>\n <span class=\"pill pill-gray\" style=\"margin-left:4px;\">= sentiment</span>\n <span class=\"pill pill-gray\" style=\"margin-left:4px;\">= pos%</span>\n <span class=\"pill pill-gray\" style=\"margin-left:4px;\">= replies</span>\n <span class=\"pill pill-gray\" style=\"margin-left:4px;\">= hours</span>\n </div>\n\n <div>\n <strong style=\"color:#0f172a;\">12-wk avg:</strong>\n ${Number(base.reviews_12w?.avg_total || 0).toFixed(1)} reviews • ${Number(base.reviews_12w?.avg_pos || 0).toFixed(1)}/${Number(base.reviews_12w?.avg_neu || 0).toFixed(1)}/${Number(base.reviews_12w?.avg_neg || 0).toFixed(1)} • ${Number(base.reviews_12w?.avg_rating || 0).toFixed(1)}★ • ${fmtPct(base.reviews_12w?.avg_positiveShare || 0)} positive • Replies ${fmtPct(base.reviews_12w?.avg_replyRatePct || 0)} • Median reply ${fmtHours(base.reviews_12w?.avg_medianReplyLatencyHours || 0)}\n <span class=\"pill pill-red\" style=\"margin-left:6px;\">▼ ${Math.abs(Number(base.vs12wAvg_reviews?.diff_from_avg || 0)).toFixed(1)} reviews</span>\n <span class=\"${(Number(base.deltas?.pos_vsavg_pp)||0)>=0 ? 'pill pill-green':'pill pill-red'}\" style=\"margin-left:4px;\">${(Number(base.deltas?.pos_vsavg_pp)||0)>=0?'▲ ':'▼ '}${Math.abs(Number(base.deltas?.pos_vsavg_pp||0)).toFixed(1)} pp pos%</span>\n <span class=\"${(Number(base.deltas?.replies_vsavg_pp)||0)>=0 ? 'pill pill-green':'pill pill-red'}\" style=\"margin-left:4px;\">${(Number(base.deltas?.replies_vsavg_pp)||0)>=0?'▲ ':'▼ '}${Math.abs(Number(base.deltas?.replies_vsavg_pp||0)).toFixed(1)} pp replies</span>\n <span class=\"pill pill-green\" style=\"margin-left:4px;\">▼ ${Math.abs(Number(base.deltas?.replyHours_vsavg || 0)).toFixed(1)}h reply time</span>\n </div>\n\n </td>\n </tr>\n </table>\n </td>\n\n </tr>\n </table>\n </td>\n </tr>\n\n <!-- All-Time (conditional) -->\n ${base.allTimeReviews && (base.allTimeReviews.total || 0) > 0 ? `\n <tr>\n <td style=\"padding:0 24px 20px 24px;\">\n <table role=\"presentation\" cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" style=\"border:1px solid #e5e7eb; border-radius:12px;\">\n <tr><td style=\"background:#f1f5f9; padding:10px 14px; font-size:13px; font-weight:700;\">All-Time Reviews</td></tr>\n <tr>\n <td style=\"padding:12px 14px; font-size:13px; line-height:1.6;\">\n Total: ${fmtInt(base.allTimeReviews.total)} • Positive share: ${fmtPct(base.allTimeReviews.positiveShare)} • Avg rating: ${Number(base.allTimeReviews.averageRating || 0).toFixed(1)}★<br>\n Reply rate: ${fmtPct(base.allTimeReviews.replyRatePct)} • Median reply: ${fmtHours(base.allTimeReviews.medianReplyLatencyHours)}<br>\n Last review: ${base.allTimeReviews.lastReviewAt || ''}\n </td>\n </tr>\n </table>\n </td>\n </tr>\n ` : ''}\n\n <!-- Footer -->\n <tr>\n <td style=\"padding:16px 24px 24px 24px; font-size:11px; color:#64748b; text-align:center;\">\n Sent automatically. Questions or corrections? Reply to this email.\n </td>\n </tr>\n\n </table>\n </td>\n </tr>\n </table>\n</body>\n</html>`;\n\n// Subject line\nconst subject = `Weekly Google Reviews & Impressions — ${companyName} (${fmtDateISOtoMdy(periodStart)}–${fmtDateISOtoMdy(periodEnd)})`;\n\n// per-item mode must return ONE object\nreturn { json: { ...$json, subject, html } };"
},
"typeVersion": 2
},
{
"id": "0f96a41e-c575-4d21-81df-83274680903d",
"name": "先週ウィンドウを構築",
"type": "n8n-nodes-base.code",
"position": [
-3488,
3200
],
"parameters": {
"jsCode": "// Build last-week-only window (Mon–Sun style: last 7 full days ending yesterday, UTC)\n\nfunction atUtcMidnight(d = new Date()) {\n return new Date(Date.UTC(d.getUTCFullYear(), d.getUTCMonth(), d.getUTCDate()));\n}\n\nconst todayMid = atUtcMidnight(); // today 00:00Z\nconst endThis = new Date(todayMid); // yesterday 00:00Z will be end-of-this-week (exclusive)\nendThis.setUTCDate(endThis.getUTCDate() - 1);\n\nconst startThis = new Date(endThis); // not used downstream; here for clarity only\nstartThis.setUTCDate(startThis.getUTCDate() - 6);\n\nconst endLast = new Date(startThis); // last week ends the day before this-week starts\nendLast.setUTCDate(endLast.getUTCDate() - 1);\n\nconst startLast = new Date(endLast);\nstartLast.setUTCDate(startLast.getUTCDate() - 6);\n\nconst isoDate = d => d.toISOString().slice(0, 10);\n\nreturn [{\n json: {\n // last-week window only\n startY: startLast.getUTCFullYear(),\n startM: startLast.getUTCMonth() + 1,\n startD: startLast.getUTCDate(),\n endY: endLast.getUTCFullYear(),\n endM: endLast.getUTCMonth() + 1,\n endD: endLast.getUTCDate(),\n\n startISO: startLast.toISOString(), // IMPORTANT: set to last week\n endISO: endLast.toISOString(), // IMPORTANT: set to last week\n week_start: isoDate(startLast),\n week_end: isoDate(endLast),\n\n // convenience numeric timestamps (inclusive end)\n ts: {\n start: startLast.getTime(),\n end: endLast.getTime() + 24*60*60*1000 - 1\n }\n }\n}];"
},
"typeVersion": 2
},
{
"id": "aa300513-a3fe-49e0-b89f-dac7c83fcf53",
"name": "企業を設定（先週レビュー）",
"type": "n8n-nodes-base.code",
"position": [
-3296,
3200
],
"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": "89555b75-6474-40c6-a7e3-b31c01efec64",
"name": "先週レビューを取得",
"type": "n8n-nodes-base.googleBusinessProfile",
"position": [
-3104,
3200
],
"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": {}
},
"credentials": {
"googleBusinessProfileOAuth2Api": {
"id": "snpnWRsJVydiU7oU",
"name": "Google Business Profile account"
}
},
"typeVersion": 1
},
{
"id": "5a36f4ff-70f0-4cb3-b74e-4995050a159c",
"name": "企業を設定（過去12週間レビュー）",
"type": "n8n-nodes-base.code",
"position": [
-3296,
3376
],
"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": "ec75d4f5-e9b4-48aa-a971-e0f5829dba91",
"name": "過去12週間レビューを取得",
"type": "n8n-nodes-base.googleBusinessProfile",
"position": [
-3104,
3376
],
"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": {}
},
"credentials": {
"googleBusinessProfileOAuth2Api": {
"id": "snpnWRsJVydiU7oU",
"name": "Google Business Profile account"
}
},
"typeVersion": 1
},
{
"id": "725efc5d-a776-4a68-af5c-d04444ce6fae",
"name": "過去12週間レビューをマッピングおよび要約",
"type": "n8n-nodes-base.code",
"position": [
-2896,
3376
],
"parameters": {
"jsCode": "// Map + categorize Google Reviews (12-WEEK window) — SAFE\n// Accepts upstream reviews as $input (preferred) OR pulls from a reviews node if present.\n// Tries multiple node names for company index + date window. No throws; proceeds best-effort.\n\n// ---------- helpers ----------\nfunction safeItems(name){\n try {\n const arr = $items(name, 0, 0);\n return Array.isArray(arr) ? arr.map(i => i.json) : [];\n } catch { return []; }\n}\nfunction normIso(s){ return s ? String(s).replace(/\\.(\\d{3})\\d*Z$/, '.$1Z') : null; }\nfunction msSafe(s){ const t = s ? Date.parse(normIso(s)) : NaN; return Number.isFinite(t) ? t : null; }\nfunction toNum(star){\n const m = { ONE:1, TWO:2, THREE:3, FOUR:4, FIVE:5 };\n return typeof star === 'number' ? star : (m[String(star||'').toUpperCase()] ?? 0);\n}\nfunction classify(n){\n const x = Number(n || 0);\n if (!Number.isFinite(x) || x <= 0) return null;\n if (x === 3) return 'Neutral';\n if (x >= 4) return 'Positive';\n if (x <= 2) return 'Negative';\n return null;\n}\n\n// ---------- get company index (locationId -> companyName) ----------\nconst companySeed =\n safeItems('Set Company last 12 week reviews').length ? safeItems('Set Company last 12 week reviews') :\n safeItems('Set Company (reviews)').length ? safeItems('Set Company (reviews)') :\n [];\n\nconst companyIndex = new Map();\nfor (const c of companySeed) {\n const locId = (c.locationPath || '').split('/').pop() || String(c.locationId || '');\n if (locId) companyIndex.set(String(locId), c.companyName || c.company || '(unknown)');\n}\n\n// ---------- get 12-week window (multiple fallbacks; default = last 12 weeks ending yesterday) ----------\nfunction default12w(){\n const now = new Date();\n const end = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate()));\n end.setUTCDate(end.getUTCDate() - 1); // yesterday\n const start = new Date(end);\n start.setUTCDate(start.getUTCDate() - (7*12 - 1)); // inclusive start\n return { startISO: start.toISOString(), endISO: end.toISOString(), weeks: 12 };\n}\n\nconst winNode =\n safeItems('Build 12-week window (reviews)')[0]?.json ||\n safeItems('Build 12-Week Window (reviews)')[0]?.json ||\n safeItems('Build 12-Week Window')[0]?.json ||\n default12w();\n\nconst startISO = winNode.startISO || winNode.week_start || default12w().startISO;\nconst endISO = winNode.endISO || winNode.week_end || default12w().endISO;\nconst weeks = Number(winNode.weeks || 12) || 12;\nconst startMs = msSafe(startISO);\nconst endMsInc = (msSafe(endISO) ?? 0) + 24*60*60*1000 - 1;\n\n// ---------- gather reviews input ----------\nlet reviews = $input.all().map(i => i.json);\nif (!reviews.length) {\n // fallback if this node isn’t wired after the reviews fetch\n const fromGbp = safeItems('Get all reviews');\n if (fromGbp.length) reviews = fromGbp;\n}\n\n// ---------- map ----------\nconst out = [];\nfor (const r of reviews) {\n // IDs\n let locationId = r.locationId ? String(r.locationId) : null;\n let reviewId = r.reviewId ? String(r.reviewId) : null;\n\n if ((!locationId || !reviewId) && r.name){\n const p = String(r.name).split('/');\n const li = p.indexOf('locations'); if (li > -1 && p[li+1]) locationId ||= p[li+1];\n const ri = p.indexOf('reviews'); if (ri > -1 && p[ri+1]) reviewId ||= p[ri+1];\n }\n\n const createdMs = msSafe(r.createTime || r.created);\n if (!(Number.isFinite(createdMs) && Number.isFinite(startMs) && Number.isFinite(endMsInc))) continue;\n if (createdMs < startMs || createdMs > endMsInc) continue; // keep only 12-week window\n\n const replyMs = msSafe(r.reviewReply?.updateTime);\n const ratingNum = Number(r.ratingNum || toNum(r.starRating) || 0);\n const sentiment_label = classify(ratingNum);\n\n const companyName = r.companyName || companyIndex.get(locationId) || '(unknown)';\n\n out.push({\n json: {\n companyName,\n locationId,\n reviewId,\n createTime: createdMs ? new Date(createdMs).toISOString() : null,\n\n ratingNum,\n ratingText: r.ratingText || r.starRating || null,\n comment: (r.comment || '').toString(),\n\n hasReply: !!r.reviewReply?.comment,\n replyLatencyHours: (replyMs && createdMs) ? Math.round(((replyMs - createdMs)/36e5)*100)/100 : null,\n\n // window\n startISO, endISO, weeks, is12w: true,\n\n // sentiment\n sentiment_label,\n sentimentCategory: sentiment_label,\n }\n });\n}\n\nreturn out;"
},
"typeVersion": 2
},
{
"id": "3c85818b-251f-4a00-9c8e-e15feb663765",
"name": "レビューをマッピング＋分類（先週、単一ノード）",
"type": "n8n-nodes-base.code",
"position": [
-2896,
3200
],
"parameters": {
"jsCode": "// Map + categorize Google Reviews (LAST WEEK)\n// Depends on:\n// - \"Set Company last week reviews\" (company index)\n// - \"Build last week window\" (startISO/endISO are last week)\n\nconst companies = $items('Set Company last week reviews') || [];\nif (!companies.length) throw new Error('Set Company last week reviews not found or empty');\n\nconst win = ($items('Build last week window')?.[0]?.json) || {};\nconst week_start = win.startISO?.slice(0,10) || win.week_start || null;\nconst week_end = win.endISO?.slice(0,10) || win.week_end || null;\n\n// Build locationId -> companyName index\nconst companyIndex = new Map();\nfor (const {json:c} of companies) {\n const locId = (c.locationPath || '').split('/').pop();\n if (locId) companyIndex.set(String(locId), c.companyName || c.company || '(unknown)');\n}\n\n// Helpers\nfunction toNum(star){\n const m = { ONE:1, TWO:2, THREE:3, FOUR:4, FIVE:5 };\n return typeof star === 'number' ? star : (m[String(star||'').toUpperCase()] ?? 0);\n}\nfunction normIso(s){ return s ? String(s).replace(/\\.(\\d{3})\\d*Z$/, '.$1Z') : null; }\nfunction msSafe(s){ const t = s ? Date.parse(normIso(s)) : NaN; return Number.isFinite(t) ? t : null; }\nfunction within(ms){\n const startMs = msSafe(week_start+'T00:00:00Z');\n const endMs = msSafe(week_end +'T23:59:59Z'); // inclusive\n return Number.isFinite(ms) && Number.isFinite(startMs) && Number.isFinite(endMs) && ms >= startMs && ms <= endMs;\n}\nfunction classify(n){\n const x = Number(n || 0);\n if (!Number.isFinite(x) || x <= 0) return null;\n if (x === 3) return 'Neutral';\n if (x >= 4) return 'Positive';\n if (x <= 2) return 'Negative';\n return null;\n}\n\nconst out = [];\nfor (const {json:r} of $input.all()){\n // IDs\n let locationId = r.locationId ? String(r.locationId) : null;\n let reviewId = r.reviewId ? String(r.reviewId) : null;\n\n if ((!locationId || !reviewId) && r.name) {\n const p = String(r.name).split('/');\n const li = p.indexOf('locations'); if (li > -1 && p[li+1]) locationId ||= p[li+1];\n const ri = p.indexOf('reviews'); if (ri > -1 && p[ri+1]) reviewId ||= p[ri+1];\n }\n\n const createdMs = msSafe(r.createTime || r.created);\n if (!within(createdMs)) continue; // keep only last-week reviews\n\n const replyMs = msSafe(r.reviewReply?.updateTime);\n const ratingNum = Number(r.ratingNum || toNum(r.starRating) || 0);\n const sentiment_label = classify(ratingNum);\n\n const companyName = r.companyName || companyIndex.get(locationId) || '(unknown)';\n\n out.push({\n json: {\n companyName,\n locationId,\n reviewId,\n createTime: createdMs ? new Date(createdMs).toISOString() : null,\n\n ratingNum,\n ratingText: r.ratingText || r.starRating || null,\n comment: (r.comment || '').toString(),\n\n hasReply: !!r.reviewReply?.comment,\n replyLatencyHours: (replyMs && createdMs) ? Math.round(((replyMs - createdMs)/36e5)*100)/100 : null,\n\n // weekly fields for grouping\n week_start,\n week_end,\n isLastWeek: true, // <— tag for Join All\n\n // sentiment\n sentiment_label,\n sentimentCategory: sentiment_label,\n\n // stable key if you need it\n key: [companyName, locationId || 'noLocation', week_start || 'noStart'].join('__'),\n }\n });\n}\n\nreturn out;"
},
"typeVersion": 2
},
{
"id": "76cd4683-e038-4ede-a715-bec7321c09bb",
"name": "週次レビューを要約（先週）",
"type": "n8n-nodes-base.code",
"position": [
-2336,
3200
],
"parameters": {
"jsCode": "// Weekly Reviews Summary (LAST WEEK) — input: mapped reviews from the node 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 (include location if needed)\nconst groups = new Map();\nfor (const r of rows){\n const key = `${r.companyName||'(unknown)'}__${r.locationId||'null'}__${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(Number.isFinite);\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 isLastWeek: true // <— keep tag so Join can detect “last-week reviews”\n }\n });\n}\n\nreturn out;"
},
"typeVersion": 2
},
{
"id": "8d710516-4d4c-4b13-87c2-1534861bb63a",
"name": "12週間レビューを要約（合計＋週平均）",
"type": "n8n-nodes-base.code",
"position": [
-2336,
3376
],
"parameters": {
"jsCode": "// Summarize 12-WEEK Google Reviews — SAFE\n// Input: rows from the 12-week mapper above (or any rows with companyName/locationId/ratingNum/sentiment/hasReply)\n\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); const 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).filter(Boolean);\n\n// group by company+location (12w window is already applied upstream)\nconst groups = new Map();\nfor (const r of rows) {\n const key = `${r.companyName||'(unknown)'}__${r.locationId||'null'}`;\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\n const total = list.length;\n\n const ratings = list.map(r => Number(r.ratingNum||0)).filter(Number.isFinite);\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).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 // also pass through the 12w window (start/end/weeks) if present\n const startISO = list[0].startISO || null;\n const endISO = list[0].endISO || null;\n const weeks = Number(list[0].weeks || 12) || 12;\n\n out.push({\n json: {\n companyName, locationId,\n startISO, endISO, weeks,\n\n // 12w totals/KPIs\n total_reviews_12w: total,\n positive_12w: pos, neutral_12w: neu, negative_12w: neg,\n positive_share_pct_12w: positiveShare,\n average_rating_12w: avgRating,\n reply_rate_pct_12w: replyRatePct,\n median_reply_latency_hours_12w: medianReplyLatencyHours\n }\n });\n}\n\nreturn out;"
},
"typeVersion": 2
},
{
"id": "511fbcd7-1d48-47d6-be2c-63d1f99d80fe",
"name": "12週間ウィンドウを構築（レビュー）",
"type": "n8n-nodes-base.code",
"position": [
-3488,
3376
],
"parameters": {
"jsCode": "// Build 12 full weeks ending yesterday (UTC), aligned to whole days.\n// Output is a single item with {startISO,endISO,week_start,week_end,weeks:12}\n\nconst WEEKS = 12;\n\nfunction atUtcMidnight(d = new Date()){\n return new Date(Date.UTC(d.getUTCFullYear(), d.getUTCMonth(), d.getUTCDate()));\n}\n\nconst todayMid = atUtcMidnight(); // today 00:00Z\nconst endISOd = new Date(todayMid); // yesterday 00:00Z\nendISOd.setUTCDate(endISOd.getUTCDate() - 1);\n\n// inclusive range: start = end - (7*WEEKS - 1) days\nconst startISOd = new Date(endISOd);\nstartISOd.setUTCDate(startISOd.getUTCDate() - (7*WEEKS - 1));\n\nconst isoDate = d => d.toISOString().slice(0,10);\n\nreturn [{\n json: {\n weeks: WEEKS,\n startISO: startISOd.toISOString(),\n endISO: endISOd.toISOString(),\n week_start: isoDate(startISOd),\n week_end: isoDate(endISOd),\n ts: {\n start: startISOd.getTime(),\n end: endISOd.getTime() + 24*60*60*1000 - 1,\n }\n }\n}];"
},
"typeVersion": 2
},
{
"id": "b9891c51-db0e-4e30-bdc9-639de247fc8a",
"name": "フォーマット",
"type": "n8n-nodes-base.code",
"position": [
-1200,
2640
],
"parameters": {
"jsCode": "// n8n Code node (JavaScript)\n// Input: items[] from Join (one per company) with keys:\n// impressions, impressions_lastWeek, impressions_12w\n// reviews, reviews_lastWeek, reviews_12w\n// Optional: allTimeReviews\n// Output: same items, enriched with deltas + LLM one-liner prompts\n\nfunction toNum(x, def = 0) {\n const n = Number(x);\n return Number.isFinite(n) ? n : def;\n}\nfunction round(n, dp = 0) {\n const f = Math.pow(10, dp);\n return Math.round(toNum(n) * f) / f;\n}\nfunction safePct(numerator, denominator) {\n numerator = toNum(numerator);\n denominator = toNum(denominator);\n if (denominator === 0) return 0;\n return (numerator / denominator) * 100;\n}\nfunction pctDelta(thisVal, prevVal) {\n // percent change vs previous: (this - prev) / prev * 100\n const a = toNum(thisVal), b = toNum(prevVal);\n if (b === 0) return 0;\n return ((a - b) / b) * 100;\n}\nfunction ppDelta(thisPct, prevPct) {\n return toNum(thisPct) - toNum(prevPct);\n}\nfunction hoursDelta(thisH, prevH) {\n return toNum(thisH) - toNum(prevH);\n}\nfunction fmtPct(x) { return `${round(x, 0)}%`; }\nfunction fmtPp(x) { const s = round(x, 1); return (s >= 0 ? `+` : ``) + `${s} pp`; }\nfunction fmtHrs(x) { const s = round(x, 1); return `${s}h`; }\n\n// Treat small changes as flat for readability\nconst FLAT_PCT = 2; // ±2% considered \"flat\" for volumes\nconst FLAT_PP = 0.5; // ±0.5 pp considered \"flat\" for rates\nconst FLAT_HRS = 0.3; // ±0.3h considered \"flat\" for med reply hours\n\nfunction flatLabel(value, kind = 'pct') {\n const v = toNum(value);\n const th = kind === 'pp' ? FLAT_PP : kind === 'hrs' ? FLAT_HRS : FLAT_PCT;\n if (Math.abs(v) <= th) return 'flat';\n return v > 0 ? 'up' : 'down';\n}\n\nfunction sanitizeBlocks(i) {\n // Provide safe defaults if any blocks are missing\n i.impressions = i.impressions || {};\n i.impressions_lastWeek = i.impressions_lastWeek || {};\n i.impressions_12w = i.impressions_12w || {};\n i.reviews = i.reviews || {};\n i.reviews_lastWeek = i.reviews_lastWeek || {};\n i.reviews_12w = i.reviews_12w || {};\n i.allTimeReviews = i.allTimeReviews || {};\n return i;\n}\n\nfunction buildOutputs(it) {\n const company = it.companyName || it.company || 'Company';\n\n // ---- Impressions (this, last, 12w) ----\n const imprThis = toNum(it.impressions.total);\n const actThis = toNum(it.impressions.actions_total);\n const ctrThis = toNum(it.impressions.ctr_actions_pct);\n\n const imprLW = toNum(it.impressions_lastWeek.total);\n const actLW = toNum(it.impressions_lastWeek.actions_total);\n const ctrLW = toNum(it.impressions_lastWeek.ctr_actions_pct);\n\n const imprAvg = toNum(it.impressions_12w.avg_week_total);\n const actAvg = toNum(it.impressions_12w.avg_week_actions_total);\n const ctrAvg = toNum(it.impressions_12w.avg_week_ctr_pct);\n\n // deltas vs last week\n const imprWoW = pctDelta(imprThis, imprLW);\n const actWoW = pctDelta(actThis, actLW);\n const ctrWoW = ppDelta(ctrThis, ctrLW);\n\n // deltas vs 12w average\n const imprVsAvg = pctDelta(imprThis, imprAvg);\n const actVsAvg = pctDelta(actThis, actAvg);\n const ctrVsAvg = ppDelta(ctrThis, ctrAvg);\n\n // ---- Reviews (this, last, 12w) ----\n const revThis = toNum(it.reviews.total);\n const posThis = toNum(it.reviews.positiveShare);\n const repThis = toNum(it.reviews.replyRatePct);\n const hrsThis = toNum(it.reviews.medianReplyLatencyHours);\n\n const revLW = toNum(it.reviews_lastWeek.total);\n const posLW = toNum(it.reviews_lastWeek.positiveShare);\n const repLW = toNum(it.reviews_lastWeek.replyRatePct);\n const hrsLW = toNum(it.reviews_lastWeek.medianReplyLatencyHours);\n\n const revAvg = toNum(it.reviews_12w.avg_total);\n const posAvg = toNum(it.reviews_12w.avg_positiveShare);\n const repAvg = toNum(it.reviews_12w.avg_replyRatePct);\n const hrsAvg = toNum(it.reviews_12w.avg_medianReplyLatencyHours);\n\n // Review deltas\n const revWoW = pctDelta(revThis, revLW);\n const revVsAvg = pctDelta(revThis, revAvg);\n const posWoWpp = ppDelta(posThis, posLW);\n const posVsAvgpp = ppDelta(posThis, posAvg);\n const repWoWpp = ppDelta(repThis, repLW);\n const repVsAvgpp = ppDelta(repThis, repAvg);\n const hrsWoW = hoursDelta(hrsThis, hrsLW);\n const hrsVsAvg = hoursDelta(hrsThis, hrsAvg);\n\n // ---- KPI chip text (optional) ----\n it.kpi = {\n impressions_vs12w_pct: round(imprVsAvg, 0), // negative is below avg\n actions_vs12w_pct: round(actVsAvg, 0),\n ctr_vs12w_pp: round(ctrVsAvg, 1),\n reviews_vs12w_count: round(revThis - revAvg, 1) // absolute difference in count vs avg\n };\n\n // ---- LLM one-liner prompts ----\n it.oneLinerOverall_prompt =\n`Write ONE sentence (≤26 words) on ${company}'s weekly Google performance vs last week and vs 12-week average.\nNumbers (whole %; CTR deltas in pp):\nThis: ${imprThis} impr, ${actThis} actions, CTR ${round(ctrThis,0)}%.\nLast: ${imprLW} impr, ${actLW} actions, CTR ${round(ctrLW,0)}%.\n12w: ${round(imprAvg,0)} impr, ${round(actAvg,0)} actions, CTR ${round(ctrAvg,0)}%.\nReviews: this ${revThis} (${round(posThis,0)}% positive), last ${revLW}, 12w avg ${round(revAvg,1)}.\nOutput only the sentence.`;\n\n it.oneLinerImpressions_prompt =\n`One sentence (≤22 words) comparing impressions, actions, and CTR this week vs last week and the 12-week average; say up/down/flat in whole % or \"pp\".\nThis: ${imprThis} impr, ${actThis} actions, CTR ${round(ctrThis,0)}%.\nLast: ${imprLW} impr, ${actLW} actions, CTR ${round(ctrLW,0)}%.\n12w: ${round(imprAvg,0)} impr, ${round(actAvg,0)} actions, CTR ${round(ctrAvg,0)}%.`;\n\n it.oneLinerReviews_prompt =\n`One sentence (≤22 words) comparing review volume, sentiment (positive share), and reply behavior this week vs last week and 12-week average.\nThis: ${revThis} total, ${round(posThis,0)}% positive, replies ${round(repThis,0)}%, median ${round(hrsThis,1)}h.\nLast: ${revLW} total, ${round(posLW,0)}% positive, replies ${round(repLW,0)}%, median ${round(hrsLW,1)}h.\n12w: ${round(revAvg,1)} avg/wk, ${round(posAvg,0)}% positive, replies ${round(repAvg,0)}%, median ${round(hrsAvg,1)}h.`;\n\n // ---- Add friendly deltas for chips/labels (already rounded) ----\n it.deltas = {\n // vs last week\n impr_wow_pct: round(imprWoW, 0),\n act_wow_pct: round(actWoW, 0),\n ctr_wow_pp: round(ctrWoW, 1),\n // vs 12w avg\n impr_vsavg_pct: round(imprVsAvg, 0),\n act_vsavg_pct: round(actVsAvg, 0),\n ctr_vsavg_pp: round(ctrVsAvg, 1),\n // reviews\n reviews_wow_pct: round(revWoW, 0),\n reviews_vsavg_pct: round(revVsAvg, 0),\n pos_wow_pp: round(posWoWpp, 1),\n pos_vsavg_pp: round(posVsAvgpp, 1),\n replies_wow_pp: round(repWoWpp, 1),\n replies_vsavg_pp: round(repVsAvgpp, 1),\n replyHours_wow: round(hrsWoW, 1),\n replyHours_vsavg: round(hrsVsAvg, 1)\n };\n\n // ---- Flat flags (optional, for “=” badges) ----\n it.flags = {\n impr_wow_flat: flatLabel(imprWoW, 'pct') === 'flat',\n act_wow_flat: flatLabel(actWoW, 'pct') === 'flat',\n ctr_wow_flat: flatLabel(ctrWoW, 'pp') === 'flat',\n impr_vsavg_flat: flatLabel(imprVsAvg, 'pct') === 'flat',\n act_vsavg_flat: flatLabel(actVsAvg, 'pct') === 'flat',\n ctr_vsavg_flat: flatLabel(ctrVsAvg, 'pp') === 'flat',\n reviews_wow_flat: flatLabel(revWoW, 'pct') === 'flat',\n reviews_vsavg_flat: flatLabel(revVsAvg, 'pct') === 'flat',\n pos_wow_flat: flatLabel(posWoWpp, 'pp') === 'flat',\n pos_vsavg_flat: flatLabel(posVsAvgpp, 'pp') === 'flat',\n replies_wow_flat: flatLabel(repWoWpp, 'pp') === 'flat',\n replies_vsavg_flat: flatLabel(repVsAvgpp, 'pp') === 'flat',\n replyHours_wow_flat: flatLabel(hrsWoW, 'hrs') === 'flat',\n replyHours_vsavg_flat: flatLabel(hrsVsAvg, 'hrs') === 'flat'\n };\n\n // Convenience fields for HTML bindings (this/last/avg, rounded for display)\n it.display = {\n impressions: {\n this: { impr: imprThis, act: actThis, ctr: round(ctrThis,0) },\n last: { impr: imprLW, act: actLW, ctr: round(ctrLW,0) },\n avg: { impr: round(imprAvg,0), act: round(actAvg,0), ctr: round(ctrAvg,0) }\n },\n reviews: {\n this: { total: revThis, pos: round(posThis,0), replies: round(repThis,0), hours: round(hrsThis,1) },\n last: { total: revLW, pos: round(posLW,0), replies: round(repLW,0), hours: round(hrsLW,1) },\n avg: { total: round(revAvg,1), pos: round(posAvg,0), replies: round(repAvg,0), hours: round(hrsAvg,1) }\n }\n };\n\n return it;\n}\n\nconst out = [];\nfor (const item of items) {\n const i = sanitizeBlocks({ ...item.json });\n out.push({ json: buildOutputs(i) });\n}\nreturn out;"
},
"typeVersion": 2
},
{
"id": "223f8523-f064-494a-adfc-bf35457d288c",
"name": "OpenAI Chat Model",
"type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
"position": [
-832,
2496
],
"parameters": {
"model": {
"__rl": true,
"mode": "list",
"value": "gpt-5",
"cachedResultName": "gpt-5"
},
"options": {}
},
"credentials": {
"openAiApi": {
"id": "0wxxfUy5p26AEoAX",
"name": "OpenAi account"
}
},
"typeVersion": 1.2
},
{
"id": "6068f4e7-d0b1-4969-9630-a146b59edb8d",
"name": "レビュー一行要約",
"type": "@n8n/n8n-nodes-langchain.agent",
"position": [
-832,
2640
],
"parameters": {
"text": "=companyKey = \"{{$json.companyName}}__{{$json.locationId}}\"\n\n{{$json.oneLinerReviews_prompt}}",
"options": {
"systemMessage": "You are a marketing analyst. Return STRICT JSON per schema.\nOne sentence, plain English: compare review volume vs last week and 12-week average, note sentiment (positive share) and reply behavior (rate + speed).\nAvoid numeric dumps; use words like up/down/flat; hours as “slow/fast” unless a simple figure helps (“~3h”). No extra fields.",
"enableStreaming": false
},
"promptType": "define",
"hasOutputParser": true
},
"typeVersion": 2.2
},
{
"id": "76d5fcbb-d9da-4c63-8f9d-cc4b9b9e43b5",
"name": "インプレッション一行要約",
"type": "@n8n/n8n-nodes-langchain.agent",
"position": [
-816,
3024
],
"parameters": {
"text": "=companyKey = \"{{$json.companyName}}__{{$json.locationId}}\"\n\n{{$json.oneLinerImpressions_prompt}}",
"options": {
"systemMessage": "You are a marketing analyst. Return STRICT JSON per schema.\nOne sentence, plain English: compare impressions, actions, and CTR vs last week and vs the 12-week average.\nPrefer words (up/down/flat); avoid numeric dumps; only use “pp” for CTR if needed. No extra fields.",
"enableStreaming": false
},
"promptType": "define",
"hasOutputParser": true
},
"typeVersion": 2.2
},
{
"id": "27cd3fc0-266a-4657-9895-d43f2eb6b181",
"name": "全体一行要約",
"type": "@n8n/n8n-nodes-langchain.agent",
"position": [
-832,
2304
],
"parameters": {
"text": "=companyKey = \"{{$json.companyName}}__{{$json.locationId}}\"\n\n{{$json.oneLinerOverall_prompt}}",
"options": {
"systemMessage": "You are a marketing analyst. Return STRICT JSON per schema.\nWrite ONE concise sentence for executives, plain English, minimal numbers.\nSay whether traffic and actions are up/down/flat vs last week AND vs the 12-week average; mention CTR status; mention review volume + sentiment.\nAvoid numeric values except short cues like “12-week average” or “+2 pp” if essential. No emojis. No extra fields.",
"enableStreaming": false
},
"promptType": "define",
"hasOutputParser": true
},
"typeVersion": 2.2
},
{
"id": "82ad46ba-481e-4e73-8136-fea2a915c344",
"name": "全体パーサー",
"type": "@n8n/n8n-nodes-langchain.outputParserStructured",
"position": [
-688,
2496
],
"parameters": {
"schemaType": "manual",
"inputSchema": "{\n \"type\": \"object\",\n \"additionalProperties\": false,\n \"required\": [\"companyKey\", \"oneLinerOverall\"],\n \"properties\": {\n \"companyKey\": { \"type\": \"string\", \"minLength\": 1 },\n \"oneLinerOverall\": { \"type\": \"string\", \"minLength\": 6, \"maxLength\": 220 }\n }\n}"
},
"typeVersion": 1.3
},
{
"id": "dbcb0445-bc6a-4609-9595-5ca76e07f3e5",
"name": "受信者を読み込む",
"type": "n8n-nodes-base.googleSheets",
"position": [
320,
2912
],
"parameters": {
"options": {},
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1425813895,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1nLUdbky6ngWyEFXS-I-1dkmUDt5IeVbY75QwvKCQ-o0/edit#gid=1425813895",
"cachedResultName": "Recipients"
},
"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": {
"id": "ssM8fSaJL5EtFqLs",
"name": "Google Sheets account"
}
},
"executeOnce": true,
"typeVersion": 4.7
},
{
"id": "4be9d84d-8407-492e-8106-a123cc1c4fbb",
"name": "キーを正規化",
"type": "n8n-nodes-base.code",
"position": [
512,
2640
],
"parameters": {
"jsCode": "// Input item shape from Merge One Liners:\n// { output: { companyKey: \"Company__locationId\", oneLinerOverall?, oneLinerImpressions?, oneLinerReviews? } }\n\nconst items = $input.all();\n\nfunction nz(v){ return v == null ? \"\" : String(v).trim(); }\n\nfunction pickLine(o){\n // prefer Overall, fallback to Impressions, then Reviews\n return nz(o.oneLinerOverall || o.oneLinerImpressions || o.oneLinerReviews || \"\");\n}\n\nreturn items.map(({json}) => {\n const out = json.output || {};\n const key = nz(out.companyKey);\n const parts = key.split(\"__\"); // [\"Company\", \"locationId\"]\n const company = nz(parts[0] || \"\");\n const locationId = nz(parts[1] || \"\");\n const one_liner = pickLine(out);\n\n return {\n json: {\n company,\n locationId,\n // if you also carry accountId elsewhere, you can add it here\n join_key: key, // use the exact companyKey to avoid name mismatches\n one_liner\n }\n };\n});"
},
"typeVersion": 2
},
{
"id": "196c8c75-fc62-429d-9c0c-052cbd932fb5",
"name": "受信者を正規化",
"type": "n8n-nodes-base.code",
"position": [
512,
2912
],
"parameters": {
"jsCode": "// Build join_key = company + \"__\" + locationId (strings), and rename headers.\n\nconst rows = $items('Read Recipients').map(i => i.json || {});\nfunction nz(v){ return v == null ? \"\" : String(v).trim(); }\n\nreturn rows.map(r => {\n const company = nz(r.company);\n const accountId = nz(r.accountId);\n const locationId = nz(r.locationId);\n const email_to = nz(r[\"Email to\"]);\n const email_cc = nz(r[\"Email CC\"]);\n const slack_channel = nz(r.slack_channel);\n const slack_user = nz(r.slack_user);\n\n // match upstream join_key exactly (Company__locationId)\n const join_key = company + \"__\" + locationId;\n\n return {\n json: {\n company, accountId, locationId, join_key,\n email_to, email_cc, slack_channel, slack_user\n }\n };\n});"
},
"typeVersion": 2
},
{
"id": "52a7d6c0-5347-4f17-a6aa-24eb618e41dc",
"name": "受信者を添付",
"type": "n8n-nodes-base.merge",
"position": [
832,
2656
],
"parameters": {
"mode": "combine",
"options": {},
"fieldsToMatchString": "join_key"
},
"typeVersion": 3.2
},
{
"id": "c5c888b3-4a75-4983-ae8e-6dc94ed15bfc",
"name": "全企業 Slack",
"type": "n8n-nodes-base.slack",
"position": [
1424,
2400
],
"webhookId": "617e6f70-8d00-4353-9e9b-b2788da3420e",
"parameters": {
"text": "={{$json.company + \"\\n\" + $json.period + \"\\n\" + $json.one_liner}}",
"user": {
"__rl": true,
"mode": "list",
"value": "U034CQH4PHR",
"cachedResultName": "peyton"
},
"select": "user",
"otherOptions": {
"includeLinkToWorkflow": false
},
"authentication": "oAuth2"
},
"credentials": {
"slackOAuth2Api": {
"id": "jUKlfOXBmqFckWUK",
"name": "TS Slack"
}
},
"typeVersion": 2.3
},
{
"id": "eea60dd1-d891-41c6-bb82-2d98156dfcac",
"name": "企業別 Slack",
"type": "n8n-nodes-base.slack",
"position": [
1424,
2656
],
"webhookId": "132c2789-b731-44d0-ae69-f66bff3ca330",
"parameters": {
"text": "={{$json.company + \"\\n\" + $json.period + \"\\n\" + $json.one_liner}}",
"select": "channel",
"channelId": {
"__rl": true,
"mode": "name",
"value": "={{$json.slack_channel}}"
},
"otherOptions": {},
"authentication": "oAuth2"
},
"credentials": {
"slackOAuth2Api": {
"id": "qUR4mW2YUh1F1uDg",
"name": "Slack account"
}
},
"typeVersion": 2.3
},
{
"id": "3eb7c2e1-555a-4ffe-a890-0c24eaa363fe",
"name": "企業別メール",
"type": "n8n-nodes-base.gmail",
"position": [
1424,
2912
],
"webhookId": "b31af8ae-f6be-4fec-a54d-a34de38875de",
"parameters": {
"sendTo": "={{$json.email_to}}",
"message": "={{$json.html}}",
"options": {
"ccList": "={{$json.email_cc}}"
},
"subject": "={{$json.subject}}"
},
"credentials": {
"gmailOAuth2": {
"id": "MvPzgYZlHt189mx3",
"name": "Gmail account"
}
},
"typeVersion": 2.1
},
{
"id": "acdb5642-075c-4f9d-b4c7-838bde6d01a9",
"name": "一行要約をマージ",
"type": "n8n-nodes-base.merge",
"position": [
96,
2624
],
"parameters": {
"numberInputs": 3
},
"typeVersion": 3.2
},
{
"id": "e7e1cc2c-0e3e-43c9-b2ee-55c9079eec75",
"name": "企業ごとに3行をグループ化",
"type": "n8n-nodes-base.code",
"position": [
1024,
2656
],
"parameters": {
"jsCode": "// Input: mixed items per join_key (may be structured as output.oneLinerOverall/Impressions/Reviews,\n// or flat \"one_liner\") + recipient fields.\n// Also reads from node \"format\" to attach week_start/week_end by locationId.\n// Output: exactly 1 item per join_key with lines.{overall,impressions,reviews} (no nulls),\n// one_liner (joined), week_start, week_end, period, and recipient fields.\n\n// ---------- helpers ----------\nconst S = v => String(v ?? '').trim();\n\nfunction fallbackClassify(text = '') {\n // only used if we don't have explicit labels\n const t = S(text).toLowerCase();\n if (t.includes('impressions') || t.includes('ctr') || t.includes('actions')) return 'impressions';\n if (t.includes('review') || t.includes('sentiment') || t.includes('reply') || t.includes('rating')) return 'reviews';\n return 'overall';\n}\n\n// Build a reliable join key from the item\nfunction getJoinKey(j) {\n // Prefer explicit join_key\n if (S(j.join_key)) return S(j.join_key);\n\n // If structured output exists, it usually has companyKey like \"<name>__<locationId>\"\n if (j.output && S(j.output.companyKey)) return S(j.output.companyKey);\n\n // As a last resort, compose\n const c = S(j.company);\n const loc = S(j.locationId);\n return c && loc ? `${c}__${loc}` : '';\n}\n\n// ---------- build lookup from \"format\" by locationId ----------\nconst fmtArray = ($items('format', 0, 0) || []).map(i => i.json || {});\nconst fmtByLoc = new Map(fmtArray.map(f => [S(f.locationId), f]));\n\n// ---------- group ----------\nconst groups = new Map();\n\nfor (const { json } of $input.all()) {\n // Determine grouping key\n const key = getJoinKey(json);\n if (!key) continue;\n\n // Start/restore group\n const g = groups.get(key) || {\n join_key: key,\n company: S(json.company) || S((json.output || {}).companyName) || S(key.split('__')[0]),\n locationId: S(json.locationId) || S((json.output || {}).locationId) || S(key.split('__')[1]),\n lines: { overall: null, impressions: null, reviews: null }\n };\n\n // --- Bring in recipients/identity if present\n g.accountId = g.accountId ?? json.accountId;\n g.email_to = g.email_to ?? json.email_to;\n g.email_cc = g.email_cc ?? json.email_cc;\n g.slack_channel = g.slack_channel ?? json.slack_channel;\n g.slack_user = g.slack_user ?? json.slack_user;\n\n // --- Prefer structured one-liners when available\n if (json.output) {\n const o = json.output;\n if (S(o.oneLinerOverall)) g.lines.overall = S(o.oneLinerOverall);\n if (S(o.oneLinerImpressions)) g.lines.impressions = S(o.oneLinerImpressions);\n if (S(o.oneLinerReviews)) g.lines.reviews = S(o.oneLinerReviews);\n }\n\n // --- If we only have a flat one_liner, classify and place\n if (!json.output && S(json.one_liner)) {\n const line = S(json.one_liner);\n const cls = fallbackClassify(line);\n if (!g.lines[cls]) g.lines[cls] = line;\n else if (!g.lines.overall) g.lines.overall = line; // spillover\n else if (!g.lines.overall.includes(line)) g.lines.overall += ' ' + line;\n }\n\n // --- Attach week period from \"format\" by locationId\n const f = fmtByLoc.get(S(g.locationId)) || {};\n g.week_start = g.week_start || f.week_start || '';\n g.week_end = g.week_end || f.week_end || '';\n g.period = (S(g.week_start) && S(g.week_end)) ? `week period = ${g.week_start}–${g.week_end}` : '';\n\n groups.set(key, g);\n}\n\n// ---------- finalize: ensure no nulls and stable order ----------\nconst out = [];\nfor (const g of groups.values()) {\n // Fill any missing slots with sensible fallbacks\n // If overall missing, derive from impressions + reviews; else mark em-dash.\n if (!g.lines.overall) {\n if (g.lines.impressions && g.lines.reviews) {\n g.lines.overall = `${g.lines.impressions} ${g.lines.reviews}`;\n } else if (g.lines.impressions) {\n g.lines.overall = g.lines.impressions;\n } else if (g.lines.reviews) {\n g.lines.overall = g.lines.reviews;\n } else {\n g.lines.overall = '—';\n }\n }\n if (!g.lines.impressions) g.lines.impressions = '—';\n if (!g.lines.reviews) g.lines.reviews = '—';\n\n // Join in this order: overall → impressions → reviews\n const oneJoined = [g.lines.overall, g.lines.impressions, g.lines.reviews]\n .filter(s => S(s).length > 0)\n .join('\\n');\n\n out.push({ json: { ...g, one_liner: oneJoined } });\n}\n\nreturn out;"
},
"typeVersion": 2
},
{
"id": "95279c49-402c-4d05-97d9-52c2ea0a5c07",
"name": "シートに行を追加",
"type": "n8n-nodes-base.googleSheets",
"position": [
-2336,
2336
],
"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": 2132032743,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1nLUdbky6ngWyEFXS-I-1dkmUDt5IeVbY75QwvKCQ-o0/edit#gid=2132032743",
"cachedResultName": "last week 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 "
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "ssM8fSaJL5EtFqLs",
"name": "Google Sheets account"
}
},
"typeVersion": 4.7
},
{
"id": "8f3324e0-86a2-4c29-a874-016fb8709cf8",
"name": "付箋",
"type": "n8n-nodes-base.stickyNote",
"position": [
-5840,
1936
],
"parameters": {
"width": 1280,
"height": 1280,
"content": "# 🧩 Setup Guide: Google Business → Slack + Weekly Pulse Email Workflow\n\nThis workflow automates your **Google Business performance reporting** by:\n\n- Pulling data from Google Business Profiles (impressions, actions, CTR, reviews)\n- Comparing results vs **last week** and the **12-week average**\n- Generating **LLM-powered summaries**\n- Sending formatted **Slack updates** and a **weekly pulse email**\n\n---\n\n## ✅ Prerequisites\n\nBefore activating the workflow, make sure you have the following ready:\n\n### 1. Google Sheet for Companies \n👉 [**Copy this Template**](https://docs.google.com/spreadsheets/d/1u-87I9CUywZwiy9oDTUkMrVSADzeojk2UTTJRs3YOGY/edit?usp=sharing)\n\n**Steps:**\n- Go to **File → Make a copy**\n- Add one row per company\n- Include the following columns:\n - `Company Name`\n - `Google Business ID`\n - `Slack Channel` (e.g. `#brads-service-center`)\n - `Slack Recipients` (comma-separated Slack IDs)\n - `Email Recipients` (for the weekly pulse)\n\n---\n\n### 2. Google Business API Credential\n- Create a **Google Cloud Project** with the **Business Profile API** enabled \n- Connect in n8n via a **Google Business OAuth2** credential\n\n---\n\n### 3. Google Sheets Credential\n- Add a **Google Sheets OAuth2** credential in n8n \n- Used to read the company data from your Google Sheet\n\n---\n\n### 4. Slack Credential\n- Add a **Slack OAuth2** credential in n8n \n- Required permissions:\n - `chat:write`\n - `users:read`\n - `channels:read`\n\n---\n\n### 5. Email Credential\n(Optional but recommended) \n- Add a **Gmail** or **SMTP** credential in n8n for sending the weekly pulse email\n\n\n"
},
"typeVersion": 1
},
{
"id": "72c2fca8-ba8a-409a-b5f3-42162f3d6168",
"name": "付箋1",
"type": "n8n-nodes-base.stickyNote",
"position": [
-3600,
1952
],
"parameters": {
"width": 1408,
"height": 736,
"content": "### 🧭 Impressions Data Collection\n\nThis section gathers **visibility metrics** from Google Business Profiles:\n\n- Collects data for **this week**, **last week**, and the **12-week average**\n- Tracks **impressions, actions, and CTR** — showing how often your business is seen and how effectively customers engage\n- Results are **flattened and written** back to the Google Sheet for use in weekly summaries, Slack messages, and pulse emails\n"
},
"typeVersion": 1
},
{
"id": "628a5f2c-6ebb-48b0-b059-f34699ec919b",
"name": "付箋2",
"type": "n8n-nodes-base.stickyNote",
"position": [
-4160,
1952
],
"parameters": {
"width": 528,
"height": 736,
"content": "### Gather company data from google sheets \n\nThis section gathers your company info from google sheets to later fill out the HTTP requests to the google business api\n"
},
"typeVersion": 1
},
{
"id": "31974318-19cf-47c7-bfa9-51691787fa48",
"name": "付箋3",
"type": "n8n-nodes-base.stickyNote",
"position": [
-3600,
2704
],
"parameters": {
"width": 1408,
"height": 1152,
"content": "### 💬 Reviews Data Collection & Summaries\n\nThis section gathers **review data** from Google Business Profiles across multiple timeframes:\n\n- **Current week:** Captures new reviews, sentiment (positive/neutral/negative), and reply rate \n- **Last week:** Provides a direct week-over-week comparison \n- **12-week average:** Establishes a performance baseline to show longer-term trends \n- **All-time summary:** Aggregates total reviews, overall rating, and reply metrics for historical context \n\nEach batch is **mapped, categorized, and summarized** using n8n code nodes, then written to the Google Sheet. \nThe data feeds into the **LLM summary and weekly pulse email**, helping identify shifts in reputation and engagement over time.\n"
},
"typeVersion": 1
},
{
"id": "a834ccd0-dd90-4e21-a201-a7de917ec9c3",
"name": "付箋4",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1712,
1952
],
"parameters": {
"width": 1712,
"height": 1888,
"content": "### 🧠 LLM Summaries & One-Liners\n\nThis section combines and formats all key metrics before generating **AI-powered summaries**.\n\n- **Merge2 + Join All:** Collects results from the impressions, actions, CTR, and reviews pipelines \n- **Format Node:** Structures the combined data into a clean JSON payload for the LLMs \n\nThen, three **OpenAI Chat Model** nodes create short, natural-language insights:\n- **Overall one-liner:** A concise summary tying together visibility, engagement, and reputation \n- **Reviews one-liner:** Focused on review volume, sentiment, and response quality \n- **Impressions one-liner:** Highlights visibility and click-through performance week-over-week \n\nThese summaries feed directly into the **Slack messages** and **weekly pulse email**, making complex performance data easy to read and actionable.\n"
},
"typeVersion": 1
},
{
"id": "3ae35762-9f4b-418d-8528-a77ca9b6e693",
"name": "付箋5",
"type": "n8n-nodes-base.stickyNote",
"position": [
32,
1952
],
"parameters": {
"width": 1712,
"height": 1888,
"content": "### 📬 Distribution: Slack & Weekly Pulse Emails\n\nThis section prepares and sends the **final summaries** to each company via Slack and email.\n\n- **Merge One Liners:** Combines all AI-generated one-liners (overall, impressions, and reviews) \n- **Normalize Keys:** Cleans and aligns data field names across merged results \n- **Read + Normalize Recipients:** Pulls recipient details (Slack channels and email addresses) from the Google Sheet \n- **Attach Recipients:** Joins each company’s metrics with its communication targets \n- **Group Three Lines Per Company:** Combines all summaries into a single formatted message per business \n\nFrom here:\n- **All-companies Slack:** Sends a combined summary for all companies \n- **Per Company Slack:** Sends an individual message to each company channel using Slack Blocks \n- **Create Email → Per Company Email:** Builds and delivers the weekly **Google Business Pulse** email to each company’s contact list \n\nThis is the final step where insights become **actionable communications**, automatically shared with each business every week.\n\n"
},
"typeVersion": 1
}
],
"active": false,
"pinData": {},
"settings": {
"callerPolicy": "workflowsFromSameOwner",
"executionOrder": "v1",
"timeSavedPerExecution": 40
},
"versionId": "478fd55e-2f2f-48f4-946d-e82217615f46",
"connections": {
"5ba35759-85e4-4fae-a4a0-b0b9946ec0b3": {
"main": [
[
{
"node": "ad7d3fb3-00ec-4f77-811d-5f34c6e5e14d",
"type": "main",
"index": 0
}
]
]
},
"b9891c51-db0e-4e30-bdc9-639de247fc8a": {
"main": [
[
{
"node": "27cd3fc0-266a-4657-9895-d43f2eb6b181",
"type": "main",
"index": 0
},
{
"node": "6068f4e7-d0b1-4969-9630-a146b59edb8d",
"type": "main",
"index": 0
},
{
"node": "76d5fcbb-d9da-4c63-8f9d-cc4b9b9e43b5",
"type": "main",
"index": 0
}
]
]
},
"2c9d403b-d03d-43fd-900e-60089927fc03": {
"main": [
[
{
"node": "0ac8d44a-7393-4bf8-9d59-364c5a4c7a2f",
"type": "main",
"index": 0
}
]
]
},
"ad7d3fb3-00ec-4f77-811d-5f34c6e5e14d": {
"main": [
[
{
"node": "b9891c51-db0e-4e30-bdc9-639de247fc8a",
"type": "main",
"index": 0
}
]
]
},
"42790f95-855c-439e-8ea2-b9a938a0ba55": {
"main": [
[
{
"node": "3eb7c2e1-555a-4ffe-a890-0c24eaa363fe",
"type": "main",
"index": 0
}
]
]
},
"fee14269-084f-45d7-92e0-1a99da5cc2f6": {
"ai_outputParser": [
[
{
"node": "6068f4e7-d0b1-4969-9630-a146b59edb8d",
"type": "ai_outputParser",
"index": 0
}
]
]
},
"4be9d84d-8407-492e-8106-a123cc1c4fbb": {
"main": [
[
{
"node": "52a7d6c0-5347-4f17-a6aa-24eb618e41dc",
"type": "main",
"index": 0
}
]
]
},
"a83547f7-dfac-4064-b0ab-a98c4d5668c4": {
"main": [
[
{
"node": "8d733b11-c5e6-4f3b-a065-3b5c7b44869a",
"type": "main",
"index": 0
},
{
"node": "c0315534-5395-4ba9-b4d8-d2554455b156",
"type": "main",
"index": 0
},
{
"node": "13e402fa-fbaa-4b98-93f3-22fc9c7dbf43",
"type": "main",
"index": 0
},
{
"node": "50e504fd-5112-4f11-98c9-5f1b922d4a6a",
"type": "main",
"index": 0
},
{
"node": "0f96a41e-c575-4d21-81df-83274680903d",
"type": "main",
"index": 0
},
{
"node": "511fbcd7-1d48-47d6-be2c-63d1f99d80fe",
"type": "main",
"index": 0
}
]
]
},
"82ad46ba-481e-4e73-8136-fea2a915c344": {
"ai_outputParser": [
[
{
"node": "27cd3fc0-266a-4657-9895-d43f2eb6b181",
"type": "ai_outputParser",
"index": 0
}
]
]
},
"b954b33c-c0b8-426e-bdc7-127499c93f36": {
"main": [
[
{
"node": "ce979f8e-8b6b-4d3e-a07d-1646e59d2e52",
"type": "main",
"index": 0
}
]
]
},
"fae71ed4-93b7-4d38-b996-871dad806fc2": {
"main": [
[
{
"node": "aa759b90-5e5b-494f-9271-cc93e2e61523",
"type": "main",
"index": 0
}
]
]
},
"dbcb0445-bc6a-4609-9595-5ca76e07f3e5": {
"main": [
[
{
"node": "196c8c75-fc62-429d-9c0c-052cbd932fb5",
"type": "main",
"index": 0
}
]
]
},
"887dd0d9-898e-4c32-adf3-bdbfeb580049": {
"main": [
[
{
"node": "7dfc29b9-989e-4ded-8b41-bd21f99015c7",
"type": "main",
"index": 0
}
]
]
},
"acdb5642-075c-4f9d-b4c7-838bde6d01a9": {
"main": [
[
{
"node": "4be9d84d-8407-492e-8106-a123cc1c4fbb",
"type": "main",
"index": 0
},
{
"node": "dbcb0445-bc6a-4609-9595-5ca76e07f3e5",
"type": "main",
"index": 0
}
]
]
},
"b69c6393-95ac-4234-9129-9d12653b4919": {
"main": [
[
{
"node": "a83547f7-dfac-4064-b0ab-a98c4d5668c4",
"type": "main",
"index": 0
}
]
]
},
"52a7d6c0-5347-4f17-a6aa-24eb618e41dc": {
"main": [
[
{
"node": "e7e1cc2c-0e3e-43c9-b2ee-55c9079eec75",
"type": "main",
"index": 0
}
]
]
},
"54e4abfc-d9c1-4822-a3a1-87662c0b0c7d": {
"ai_outputParser": [
[
{
"node": "76d5fcbb-d9da-4c63-8f9d-cc4b9b9e43b5",
"type": "ai_outputParser",
"index": 0
}
]
]
},
"223f8523-f064-494a-adfc-bf35457d288c": {
"ai_languageModel": [
[
{
"node": "27cd3fc0-266a-4657-9895-d43f2eb6b181",
"type": "ai_languageModel",
"index": 0
}
]
]
},
"27cd3fc0-266a-4657-9895-d43f2eb6b181": {
"main": [
[
{
"node": "acdb5642-075c-4f9d-b4c7-838bde6d01a9",
"type": "main",
"index": 0
}
]
]
},
"6068f4e7-d0b1-4969-9630-a146b59edb8d": {
"main": [
[
{
"node": "acdb5642-075c-4f9d-b4c7-838bde6d01a9",
"type": "main",
"index": 1
}
]
]
},
"f897b099-2e89-4537-84bd-fd74b381d453": {
"main": [
[
{
"node": "5ba35759-85e4-4fae-a4a0-b0b9946ec0b3",
"type": "main",
"index": 3
}
]
]
},
"8d733b11-c5e6-4f3b-a065-3b5c7b44869a": {
"main": [
[
{
"node": "e982ec74-55f0-418b-9790-4d17cb929103",
"type": "main",
"index": 0
},
{
"node": "1a372bcf-358e-42c3-b225-82690917e5e0",
"type": "main",
"index": 0
}
]
]
},
"7dfc29b9-989e-4ded-8b41-bd21f99015c7": {
"main": [
[
{
"node": "8dfac27e-5b86-42da-8c83-d9170075e194",
"type": "main",
"index": 0
}
]
]
},
"8d45eeac-97ce-4dbb-a6ec-7df9e3b5d05f": {
"ai_languageModel": [
[
{
"node": "76d5fcbb-d9da-4c63-8f9d-cc4b9b9e43b5",
"type": "ai_languageModel",
"index": 0
}
]
]
},
"c6e82665-fa63-4cbf-9e83-dcdc9d99bba3": {
"ai_languageModel": [
[
{
"node": "6068f4e7-d0b1-4969-9630-a146b59edb8d",
"type": "ai_languageModel",
"index": 0
}
]
]
},
"19b21559-b1e9-4803-8493-a8a55522162d": {
"main": [
[
{
"node": "b954b33c-c0b8-426e-bdc7-127499c93f36",
"type": "main",
"index": 0
}
]
]
},
"95279c49-402c-4d05-97d9-52c2ea0a5c07": {
"main": [
[
{
"node": "5ba35759-85e4-4fae-a4a0-b0b9946ec0b3",
"type": "main",
"index": 1
}
]
]
},
"f9ce0523-f032-4e67-9246-37bfd77310f6": {
"main": [
[
{
"node": "95279c49-402c-4d05-97d9-52c2ea0a5c07",
"type": "main",
"index": 0
}
]
]
},
"e982ec74-55f0-418b-9790-4d17cb929103": {
"main": [
[
{
"node": "2c9d403b-d03d-43fd-900e-60089927fc03",
"type": "main",
"index": 0
}
]
]
},
"8dfac27e-5b86-42da-8c83-d9170075e194": {
"main": [
[
{
"node": "30b1aa4a-255b-4d30-8ade-af80dabaab78",
"type": "main",
"index": 0
}
]
]
},
"daa03d24-bc82-4a11-961e-e47777a5e8cd": {
"main": [
[
{
"node": "5ba35759-85e4-4fae-a4a0-b0b9946ec0b3",
"type": "main",
"index": 6
}
]
]
},
"13e402fa-fbaa-4b98-93f3-22fc9c7dbf43": {
"main": [
[
{
"node": "19b21559-b1e9-4803-8493-a8a55522162d",
"type": "main",
"index": 0
}
]
]
},
"196c8c75-fc62-429d-9c0c-052cbd932fb5": {
"main": [
[
{
"node": "52a7d6c0-5347-4f17-a6aa-24eb618e41dc",
"type": "main",
"index": 1
}
]
]
},
"89555b75-6474-40c6-a7e3-b31c01efec64": {
"main": [
[
{
"node": "3c85818b-251f-4a00-9c8e-e15feb663765",
"type": "main",
"index": 0
}
]
]
},
"76d5fcbb-d9da-4c63-8f9d-cc4b9b9e43b5": {
"main": [
[
{
"node": "acdb5642-075c-4f9d-b4c7-838bde6d01a9",
"type": "main",
"index": 2
}
]
]
},
"ef1c5e27-f336-4bbb-a668-a10abe6087c2": {
"main": [
[
{
"node": "887dd0d9-898e-4c32-adf3-bdbfeb580049",
"type": "main",
"index": 0
}
]
]
},
"0f96a41e-c575-4d21-81df-83274680903d": {
"main": [
[
{
"node": "aa300513-a3fe-49e0-b89f-dac7c83fcf53",
"type": "main",
"index": 0
}
]
]
},
"0ac8d44a-7393-4bf8-9d59-364c5a4c7a2f": {
"main": [
[
{
"node": "5ba35759-85e4-4fae-a4a0-b0b9946ec0b3",
"type": "main",
"index": 0
}
]
]
},
"ec75d4f5-e9b4-48aa-a971-e0f5829dba91": {
"main": [
[
{
"node": "725efc5d-a776-4a68-af5c-d04444ce6fae",
"type": "main",
"index": 0
}
]
]
},
"30b1aa4a-255b-4d30-8ade-af80dabaab78": {
"main": [
[
{
"node": "f897b099-2e89-4537-84bd-fd74b381d453",
"type": "main",
"index": 0
}
]
]
},
"c0315534-5395-4ba9-b4d8-d2554455b156": {
"main": [
[
{
"node": "ef1c5e27-f336-4bbb-a668-a10abe6087c2",
"type": "main",
"index": 0
}
]
]
},
"1a372bcf-358e-42c3-b225-82690917e5e0": {
"main": [
[
{
"node": "f9ce0523-f032-4e67-9246-37bfd77310f6",
"type": "main",
"index": 0
}
]
]
},
"50e504fd-5112-4f11-98c9-5f1b922d4a6a": {
"main": [
[
{
"node": "fae71ed4-93b7-4d38-b996-871dad806fc2",
"type": "main",
"index": 0
}
]
]
},
"e7e1cc2c-0e3e-43c9-b2ee-55c9079eec75": {
"main": [
[
{
"node": "c5c888b3-4a75-4983-ae8e-6dc94ed15bfc",
"type": "main",
"index": 0
},
{
"node": "eea60dd1-d891-41c6-bb82-2d98156dfcac",
"type": "main",
"index": 0
},
{
"node": "42790f95-855c-439e-8ea2-b9a938a0ba55",
"type": "main",
"index": 0
}
]
]
},
"aa300513-a3fe-49e0-b89f-dac7c83fcf53": {
"main": [
[
{
"node": "89555b75-6474-40c6-a7e3-b31c01efec64",
"type": "main",
"index": 0
}
]
]
},
"511fbcd7-1d48-47d6-be2c-63d1f99d80fe": {
"main": [
[
{
"node": "5a36f4ff-70f0-4cb3-b74e-4995050a159c",
"type": "main",
"index": 0
}
]
]
},
"ce979f8e-8b6b-4d3e-a07d-1646e59d2e52": {
"main": [
[
{
"node": "5ba35759-85e4-4fae-a4a0-b0b9946ec0b3",
"type": "main",
"index": 2
}
]
]
},
"5a36f4ff-70f0-4cb3-b74e-4995050a159c": {
"main": [
[
{
"node": "ec75d4f5-e9b4-48aa-a971-e0f5829dba91",
"type": "main",
"index": 0
}
]
]
},
"aa759b90-5e5b-494f-9271-cc93e2e61523": {
"main": [
[
{
"node": "daa03d24-bc82-4a11-961e-e47777a5e8cd",
"type": "main",
"index": 0
}
]
]
},
"76cd4683-e038-4ede-a715-bec7321c09bb": {
"main": [
[
{
"node": "5ba35759-85e4-4fae-a4a0-b0b9946ec0b3",
"type": "main",
"index": 4
}
]
]
},
"725efc5d-a776-4a68-af5c-d04444ce6fae": {
"main": [
[
{
"node": "8d710516-4d4c-4b13-87c2-1534861bb63a",
"type": "main",
"index": 0
}
]
]
},
"3c85818b-251f-4a00-9c8e-e15feb663765": {
"main": [
[
{
"node": "76cd4683-e038-4ede-a715-bec7321c09bb",
"type": "main",
"index": 0
}
]
]
},
"8d710516-4d4c-4b13-87c2-1534861bb63a": {
"main": [
[
{
"node": "5ba35759-85e4-4fae-a4a0-b0b9946ec0b3",
"type": "main",
"index": 5
}
]
]
}
}
}よくある質問
このワークフローの使い方は？
上記のJSON設定コードをコピーし、n8nインスタンスで新しいワークフローを作成して「JSONからインポート」を選択、設定を貼り付けて認証情報を必要に応じて変更してください。
このワークフローはどんな場面に適していますか？
上級
有料ですか？
このワークフローは完全無料です。ただし、ワークフローで使用するサードパーティサービス（OpenAI APIなど）は別途料金が発生する場合があります。
関連ワークフロー
自動ニュース要約AIエージェントv13
AIニュース制作チーム：24/7リポート自動化＋Perplexity引用
Set
Code
Gmail
+
Set
Code
Gmail
37 ノードDerek Cheung
プロダクト
ブログ投稿自動化とSNSプロモーションエージェント
GPT-4、Perplexity、WordPressを使用したSEOブログ作成の自動化＋ソーシャルメディア
Set
Code
Gmail
+
Set
Code
Gmail
79 ノードLukaszB
デザイン
n8nノードの探索（可視化リファレンスライブラリ内）
n8nノードを可視化リファレンスライブラリで探索
If
Ftp
Set
+
If
Ftp
Set
113 ノードI versus AI
その他
AI、ElevenLabs、PIAPI Shotstack / Creatomate を使用して動画を作成し、YouTube に投稿
AI、ElevenLabs、PIAPI Shotstack/Creatomate を使用して動画を生成し、YouTube に投稿
If
Set
Code
+
If
Set
Code
79 ノードImmanuel
人工知能
🗞️ AIドライブの持続可能性マーケティングブリーフィング（Gmail、GPT-4o使用）
🗞️ AI駆動型持続可能性マーケティングブリーフィング（Gmail、GPT-4oを使用）
If
Set
Code
+
If
Set
Code
21 ノードSamir Saci
人工知能
AI生成LinkedIn投稿（人間による承認付き）
GPT-4、GoToHuman、Blotatoを使用したAI生成LinkedIn投稿（人間による承認付き）
Code
Merge
Filter
+
Code
Merge
Filter
19 ノードRobert Breen
ソーシャルメディア
