Summarize Google Reviews Into Monthly Owner Digests with Claude and Supabase

The workflow JSON

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

{
  "name": "Review Intelligence (Google Reviews) [TEMPLATE]",
  "nodes": [
    {
      "name": "Find Locations",
      "type": "n8n-nodes-base.manualTrigger",
      "maxTries": 2,
      "position": [
        -864,
        736
      ],
      "parameters": {},
      "retryOnFail": true,
      "typeVersion": 1,
      "waitBetweenTries": 3000
    },
    {
      "name": "List Locations With Names",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        -640,
        736
      ],
      "parameters": {
        "url": "https://mybusinessbusinessinformation.googleapis.com/v1/accounts/YOUR_GBP_ACCOUNT_ID/locations",
        "options": {},
        "sendQuery": true,
        "authentication": "predefinedCredentialType",
        "queryParameters": {
          "parameters": [
            {
              "name": "readMask",
              "value": "name,title,storefrontAddress"
            },
            {
              "name": "pageSize",
              "value": "100"
            }
          ]
        },
        "nodeCredentialType": "googleBusinessProfileOAuth2Api"
      },
      "typeVersion": 4.2,
      "alwaysOutputData": true
    },
    {
      "name": "Run on Live Reviews",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -864,
        416
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "name": "Get Reviews",
      "type": "n8n-nodes-base.googleBusinessProfile",
      "maxTries": 5,
      "position": [
        -640,
        336
      ],
      "parameters": {
        "account": {
          "__rl": true,
          "mode": "list",
          "value": "accounts/YOUR_GBP_ACCOUNT_ID",
          "cachedResultName": "Your Name"
        },
        "location": {
          "__rl": true,
          "mode": "list",
          "value": "locations/YOUR_GBP_LOCATION_ID",
          "cachedResultName": "locations/YOUR_GBP_LOCATION_ID"
        },
        "resource": "review",
        "operation": "getAll",
        "returnAll": true,
        "requestOptions": {}
      },
      "retryOnFail": true,
      "typeVersion": 1,
      "alwaysOutputData": true,
      "waitBetweenTries": 8000
    },
    {
      "name": "Run Test Cases",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -864,
        576
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "name": "Load Test Set",
      "type": "n8n-nodes-base.code",
      "position": [
        -640,
        544
      ],
      "parameters": {
        "jsCode": "\n// Constructed sets exist to prove the FAILURE MODES. Real reviews cannot do that,\n// because you cannot prove a model does not invent contradictions using data that has some.\nconst SETS = {\n  // A: genuine two-sided split on ONE theme -> must report a split with counts\n  A_genuine_split: [\n    {id:\"a1\", rating:5, text:\"Ordered a capacitor Tuesday, it was on my doorstep Wednesday. Incredibly fast.\"},\n    {id:\"a2\", rating:5, text:\"Shipping was quicker than expected, had the part next day.\"},\n    {id:\"a3\", rating:5, text:\"Fast delivery, no complaints at all.\"},\n    {id:\"a4\", rating:2, text:\"Took eleven days to get my order. Way too slow.\"},\n    {id:\"a5\", rating:2, text:\"Shipping is painfully slow, I could have driven there faster.\"},\n    {id:\"a6\", rating:1, text:\"Still waiting on a part I ordered two weeks ago. Terrible shipping.\"}\n  ],\n  // B: LOPSIDED - 9 happy, 1 substantive complaint. The 1 must SURVIVE, not get averaged away.\n  B_lopsided: [\n    {id:\"b1\", rating:5, text:\"Great counter staff, knew exactly what I needed.\"},\n    {id:\"b2\", rating:5, text:\"Always have the parts in stock. Lifesaver.\"},\n    {id:\"b3\", rating:5, text:\"Good prices and friendly people.\"},\n    {id:\"b4\", rating:5, text:\"These guys know HVAC. Very helpful.\"},\n    {id:\"b5\", rating:5, text:\"In and out in five minutes with the right part.\"},\n    {id:\"b6\", rating:5, text:\"Best parts house in the area.\"},\n    {id:\"b7\", rating:4, text:\"Solid selection, helpful staff.\"},\n    {id:\"b8\", rating:5, text:\"They had the motor nobody else had.\"},\n    {id:\"b9\", rating:5, text:\"Quick, easy, knowledgeable.\"},\n    {id:\"b10\", rating:1, text:\"They sold me a used compressor as new. When I brought it back they refused the return and argued with me for twenty minutes.\"}\n  ],\n  // C: ALL AGREE - there is NOTHING to contradict. If it reports a split, it invented one.\n  C_all_agree: [\n    {id:\"c1\", rating:5, text:\"Fantastic service, super helpful staff.\"},\n    {id:\"c2\", rating:5, text:\"The team here is great, really knowledgeable.\"},\n    {id:\"c3\", rating:5, text:\"Staff went out of their way to help me find the part.\"},\n    {id:\"c4\", rating:5, text:\"Very friendly and helpful people at the counter.\"},\n    {id:\"c5\", rating:5, text:\"Excellent help from the guys working there.\"}\n  ],\n  // D: DIFFERENT dimensions. Price complaints + quality praise is NOT a contradiction.\n  D_different_dimensions: [\n    {id:\"d1\", rating:3, text:\"Parts are good quality but honestly overpriced compared to online.\"},\n    {id:\"d2\", rating:3, text:\"Quality is solid, prices are steep.\"},\n    {id:\"d3\", rating:2, text:\"Way too expensive for what it is.\"},\n    {id:\"d4\", rating:5, text:\"Very well made parts, they last.\"},\n    {id:\"d5\", rating:5, text:\"Quality is excellent, never had one fail.\"}\n  ],\n  // E: MIXED WITHIN a single review. One reviewer, two opposite signals.\n  E_mixed_within_one: [\n    {id:\"e1\", rating:3, text:\"The staff were genuinely great and helped me troubleshoot, but I waited almost an hour at the counter before anyone got to me.\"},\n    {id:\"e2\", rating:3, text:\"Love the people here, hate the wait. Always a line.\"},\n    {id:\"e3\", rating:4, text:\"Knowledgeable crew but they are understaffed, took forever.\"}\n  ],\n  // Paste real reviews here to smoke-test realism (the live path uses the GBP node instead)\n  REAL_paste: []\n};\n\n// >>> pick which case to run <<<\nconst WHICH = \"E_mixed_within_one\";\n\nconst rows = SETS[WHICH] || [];\nif (!rows.length) throw new Error(\"Test set is empty: \" + WHICH);\nreturn rows.map(r => ({ json: {\n  review_id: r.id,\n  rating: r.rating,\n  text: r.text,\n  author: \"test\",\n  created: null,\n  source: \"TEST:\" + WHICH\n}}));\n"
      },
      "typeVersion": 2
    },
    {
      "name": "Normalize Reviews",
      "type": "n8n-nodes-base.code",
      "position": [
        -368,
        384
      ],
      "parameters": {
        "jsCode": "\n// Accepts BOTH shapes: the GBP node output and the test-set output.\n// GBP starRating is an ENUM STRING (\"FIVE\"), not a number. That trips people up.\nconst WORDS = { ONE:1, TWO:2, THREE:3, FOUR:4, FIVE:5 };\nconst out = [];\nfor (const item of $input.all()) {\n  const j = item.json || {};\n  if (j.text !== undefined && j.review_id !== undefined) { out.push({ json: j }); continue; } // already normalized\n  const text = (j.comment || \"\").trim();\n  if (!text) continue;                                   // star-only ratings carry no feedback content\n  out.push({ json: {\n    review_id: j.reviewId || j.name || String(out.length),\n    rating: WORDS[j.starRating] || null,\n    text,\n    author: (j.reviewer && j.reviewer.displayName) || \"anonymous\",\n    created: j.createTime || null,\n    replied: !!(j.reviewReply && j.reviewReply.comment),\n    source: \"GBP\"\n  }});\n}\nif (!out.length) throw new Error(\"No reviews with text found.\");\nreturn out;\n"
      },
      "typeVersion": 2
    },
    {
      "name": "Build Classify Prompt",
      "type": "n8n-nodes-base.code",
      "position": [
        784,
        368
      ],
      "parameters": {
        "jsCode": "\n// FIXED taxonomy. If themes are free text, \"shipping\" / \"delivery\" / \"shipping speed\"\n// become three buckets and every count downstream is garbage.\nconst THEMES = [\"price_value\",\"product_quality\",\"stock_availability\",\"staff_service\",\"wait_time\",\n                \"shipping_delivery\",\"returns_warranty\",\"expertise\",\"facility\",\"communication\"];\n\nreturn $input.all().map(item => {\n  const r = item.json;\n  const prompt =\n`Classify ONE customer review for an HVAC parts retailer.\n\nREVIEW (rating: ${r.rating === null ? \"not given\" : r.rating + \"/5\"}):\n\"\"\"\n${r.text}\n\"\"\"\n\nAllowed themes (use ONLY these exact strings): ${THEMES.join(\", \")}\n\nRules:\n- Return every theme the review actually speaks to. Usually 1-3. Never invent a theme it does not mention.\n- A single review CAN carry opposite sentiments on different themes. Report each separately, honestly.\n- sentiment must be exactly \"positive\", \"negative\", or \"mixed\".\n- \"mixed\" only when the reviewer is genuinely torn about that SAME theme.\n- quote must be a short verbatim span copied from the review, not a paraphrase.\n- If the review says nothing substantive, return an empty findings array.\n\nOutput ONLY raw JSON, no markdown fence, exactly:\n{\"findings\":[{\"theme\":\"<one allowed theme>\",\"sentiment\":\"positive|negative|mixed\",\"quote\":\"<verbatim span>\"}]}`;\n  return { json: { ...r, prompt } };\n});\n"
      },
      "typeVersion": 2
    },
    {
      "name": "Classify Review",
      "type": "@n8n/n8n-nodes-langchain.anthropic",
      "maxTries": 3,
      "position": [
        1024,
        368
      ],
      "parameters": {
        "modelId": {
          "__rl": true,
          "mode": "list",
          "value": "claude-sonnet-4-6",
          "cachedResultName": "claude-sonnet-4-6"
        },
        "options": {
          "temperature": 0
        },
        "messages": {
          "values": [
            {
              "content": "={{ $json.prompt }}"
            }
          ]
        }
      },
      "retryOnFail": true,
      "typeVersion": 1,
      "waitBetweenTries": 5000
    },
    {
      "name": "Parse Classification",
      "type": "n8n-nodes-base.code",
      "position": [
        1392,
        368
      ],
      "parameters": {
        "jsCode": "\n// Pair each model response back to its review by index, and fail LOUDLY rather than\n// silently dropping a review (a silently dropped negative is how you lose the signal).\nconst src = $('Build Classify Prompt').all();\nconst ALLOWED = new Set([\"price_value\",\"product_quality\",\"stock_availability\",\"staff_service\",\"wait_time\",\n                         \"shipping_delivery\",\"returns_warranty\",\"expertise\",\"facility\",\"communication\"]);\nconst grab = (j) => {\n  if (typeof j === \"string\") return j;\n  if (Array.isArray(j?.content)) return j.content.map(c => c?.text || \"\").join(\"\");\n  return j?.content?.[0]?.text || j?.text || j?.message?.content || j?.output || \"\";\n};\nconst out = [];\n$input.all().forEach((item, i) => {\n  const raw = String(grab(item.json) || \"\").replace(/```json|```/g, \"\").trim();\n  const review = (src[i] && src[i].json) || {};\n  let findings = [], parse_error = null;\n  try {\n    const m = raw.match(/\\{[\\s\\S]*\\}/);\n    const obj = JSON.parse(m ? m[0] : raw);\n    findings = Array.isArray(obj.findings) ? obj.findings : [];\n  } catch (e) { parse_error = String(e.message || e); }\n  // drop anything outside the taxonomy so a hallucinated theme cannot pollute the counts\n  let clean = findings.filter(f => f && ALLOWED.has(f.theme) &&\n                                   [\"positive\",\"negative\",\"mixed\"].includes(f.sentiment));\n\n  // QUOTE VERIFICATION (added 2026-07-22, after a real fabrication).\n  // The classifier stitched two fragments of one review together in REVERSE order and\n  // stored the result as a verbatim quote. The writer downstream copied it faithfully,\n  // so every rule about exact quoting was obeyed and the report still carried words no\n  // customer wrote. Asking the model to be careful is not a control. Checking is.\n  // A failed quote does NOT discard the finding: the theme and sentiment are still\n  // usable signal. Only the untrustworthy words are dropped.\n  const canon = (s) => String(s == null ? \"\" : s)\n    .replace(/[\\u2018\\u2019]/g, \"'\")\n    .replace(/[\\u201C\\u201D]/g, '\"')\n    .replace(/\\s+/g, \" \")\n    .trim();\n  const haystack = canon(review.text);\n  let quotes_rejected = 0;\n  clean = clean.map(f => {\n    if (!f.quote) return f;\n    const q = canon(f.quote).replace(/^[\"']|[\"'.,;:!?]+$/g, \"\");\n    if (q.length >= 4 && haystack.includes(q)) return f;\n    quotes_rejected++;\n    return { ...f, quote: null, quote_rejected: canon(f.quote) };\n  });\n  out.push({ json: {\n    review_id: review.review_id, rating: review.rating, text: review.text,\n    author: review.author, source: review.source,\n    created: review.created ?? null,\n    findings: clean,\n    dropped: findings.length - clean.length,\n    quotes_rejected,\n    parse_error\n  }});\n});\nreturn out;\n"
      },
      "typeVersion": 2
    },
    {
      "name": "Aggregate + Detect Splits",
      "type": "n8n-nodes-base.code",
      "position": [
        -416,
        1184
      ],
      "parameters": {
        "jsCode": "\n// ============ THE DETERMINISTIC CORE ============\n// Contradictions are DETECTED HERE, in arithmetic, not asked of a model.\n// If every review agrees, there is mathematically nothing to report and the\n// writer downstream is told so explicitly. That is what stops invented conflict.\nconst MIN_SIDE = 2;      // need >= this many reviews on BOTH sides to call it a real split\nconst MIN_SHARE = 0.30;  // ...AND the minority side must be >= this share of the theme\nconst rows = $input.all().map(i => i.json);\n\nconst themes = {};\nlet parse_errors = 0;\nfor (const r of rows) {\n  if (r.parse_error) parse_errors++;\n  for (const f of (r.findings || [])) {\n    const t = themes[f.theme] || (themes[f.theme] = { theme: f.theme, positive: [], negative: [], mixed: [] });\n    t[f.sentiment].push({ review_id: r.review_id, rating: r.rating,\n                          quote: f.quote, created: r.created || null });\n  }\n}\n\nconst summary = Object.values(themes).map(t => {\n  const pos = t.positive.length, neg = t.negative.length, mix = t.mixed.length;\n  const total = pos + neg + mix;\n  // FIXED 2026-07-20 (found on 58 real reviews): absolute counts alone do not scale.\n  // staff_service came in 32 pos / 4 neg and got published as a \"disagreement\".\n  // 32 vs 4 is consensus with outliers, not a divide. A split now also needs the\n  // minority side to hold at least MIN_SHARE of the theme. Those 4 negatives are\n  // not lost, they fall through to minority_flags below.\n  const share = total ? Math.min(pos, neg) / (pos + neg || 1) : 0;\n  const is_split = pos >= MIN_SIDE && neg >= MIN_SIDE && share >= MIN_SHARE;\n  // FIXED 2026-07-20: the old rule required pos>=3, so a WHOLLY NEGATIVE low-volume theme\n  // (0 positive, 1 furious customer) fell through silently. That is the exact signal we\n  // most need to surface. Now: ANY negative not already captured as a split must surface.\n  const minority_negative = neg > 0 && !is_split;\n\n  // SEVERITY, so the owner reads the worst thing first instead of an 8-item list.\n  // Four signals, all arithmetic: how many complained, how concentrated it is,\n  // whether anything positive offsets it, and how angry the complainers were.\n  const negStars = t.negative.map(n => n.rating).filter(r => typeof r === \"number\");\n  const avgNegStar = negStars.length ? negStars.reduce((s, r) => s + r, 0) / negStars.length : null;\n  // REBALANCED: the first version used neg*10, so raw count swamped proportion and\n  // staff_service (4 bad of 35) outranked wait_time (2 bad of 3). For a small business,\n  // \"what share of people who mentioned this were unhappy\" is the sharper signal, so\n  // concentration carries the most weight and volume is capped.\n  let severity = Math.round(40 * (neg / (total || 1)));        // concentration, 0-40\n  severity += Math.round(20 * Math.min(1, neg / 5));           // volume, capped at 20\n  if (pos === 0 && neg > 0) severity += 25;          // nothing good said about it at all\n  if (avgNegStar !== null && avgNegStar <= 2) severity += 15;  // furious, not mildly annoyed\n  const severity_label = severity >= 50 ? \"high\" : (severity >= 30 ? \"medium\" : \"low\");\n\n  // STALENESS. This engine reads every review a business has ever received, so a 2019\n  // complaint arrives looking exactly as urgent as one from last month. Telling an owner\n  // to fix something they fixed five years ago is how a report loses its credibility.\n  // Computed here from real dates, not guessed by the writer.\n  const STALE_MONTHS = 12;  // a full year with no recurrence is fair grounds to say \"you may have fixed this\"\n  const negTimes = t.negative.map(n => n.created).filter(Boolean)\n                             .map(d => new Date(d).getTime()).filter(n => !isNaN(n));\n  const newestNeg = negTimes.length ? Math.max(...negTimes) : null;\n  const monthsOld = newestNeg === null ? null\n    : Math.floor((Date.now() - newestNeg) / (1000 * 60 * 60 * 24 * 30.44));\n  const evidence_stale = monthsOld !== null && monthsOld >= STALE_MONTHS;\n\n  return { theme: t.theme, total, positive: pos, negative: neg, mixed: mix,\n           severity, severity_label,\n           evidence_stale,\n           newest_negative_at: newestNeg === null ? null : new Date(newestNeg).toISOString().slice(0,10),\n           months_since_last_complaint: monthsOld,\n           no_positive_offset: pos === 0 && neg > 0,\n           avg_negative_stars: avgNegStar === null ? null : Math.round(avgNegStar * 10) / 10,\n           minority_share: Math.round(share*100)/100,\n           is_split, minority_negative,\n           sample_positive: t.positive.slice(0,2), sample_negative: t.negative.slice(0,2),\n           sample_mixed: t.mixed.slice(0,2) };\n}).sort((a,b) => b.total - a.total);\n\n// RECENT WINDOW. A lifetime average hides everything that matters about direction.\n// A store can sit at 4.34 lifetime while that number is carried by years-old reviews; the recent\n// years average 3.00. Without this split, the report congratulates a business that is sliding.\n// This also disciplines the staleness rule: you cannot conclude a problem was fixed just\n// because complaints stopped, if REVIEWS stopped. Silence is not evidence of repair.\nconst RECENT_MONTHS = 24;\nconst cutoff = Date.now() - RECENT_MONTHS * 30.44 * 24 * 60 * 60 * 1000;\nconst when = (r) => { const t = r.created ? new Date(r.created).getTime() : NaN; return isNaN(t) ? null : t; };\nconst dated  = rows.filter(r => when(r) !== null);\nconst recent = dated.filter(r => when(r) >= cutoff);\nconst prior  = dated.filter(r => when(r) <  cutoff);\nconst avgOf  = (arr) => {\n  const s = arr.filter(r => typeof r.rating === \"number\");\n  return s.length ? Math.round((s.reduce((a,r)=>a+r.rating,0)/s.length)*100)/100 : null;\n};\nconst negCount = (arr) => arr.reduce((n,r) => n + (r.findings||[]).filter(f=>f.sentiment===\"negative\").length, 0);\n\nconst recent_avg = avgOf(recent), prior_avg = avgOf(prior);\nconst recent_window = {\n  months: RECENT_MONTHS,\n  reviews: recent.length,\n  average_rating: recent_avg,\n  low_star_reviews: recent.filter(r => typeof r.rating === \"number\" && r.rating <= 2).length,\n  negative_findings: negCount(recent),\n  prior_reviews: prior.length,\n  prior_average_rating: prior_avg,\n  rating_direction: (recent_avg !== null && prior_avg !== null)\n    ? Math.round((recent_avg - prior_avg) * 100) / 100 : null,\n  // Fewer than 10 reviews in two years, under one every two months, means we cannot tell a\n  // fixed problem from a quiet one: a theme may simply never have come up again by chance.\n  too_few_to_judge: recent.length < 10,\n  newest_review_at: dated.length\n    ? new Date(Math.max(...dated.map(when))).toISOString().slice(0,10) : null\n};\n\nconst splits    = summary.filter(s => s.is_split);\nconst minority  = summary.filter(s => s.minority_negative)\n                         .sort((a,b) => b.severity - a.severity);\nconst rated     = rows.filter(r => typeof r.rating === \"number\");\nconst avg       = rated.length ? (rated.reduce((s,r)=>s+r.rating,0)/rated.length) : null;\n\nreturn [{ json: {\n  reviews_analyzed: rows.length,\n  parse_errors,\n  average_rating: avg === null ? null : Math.round(avg*100)/100,\n  themes: summary,\n  splits,                       // genuine two-sided disagreements\n  minority_flags: minority,     // lone complaints that must survive the summary\n  has_contradictions: splits.length > 0,\n  recent_window,\n  source: rows[0] ? rows[0].source : \"unknown\"\n}}];\n"
      },
      "typeVersion": 2
    },
    {
      "name": "Build Digest Prompt",
      "type": "n8n-nodes-base.code",
      "position": [
        32,
        1184
      ],
      "parameters": {
        "jsCode": "\nconst d = $json;\nconst PLAIN = { price_value:\"pricing\", product_quality:\"product quality\",\n  stock_availability:\"stock availability\", staff_service:\"staff service\", wait_time:\"wait times\",\n  shipping_delivery:\"shipping\", returns_warranty:\"returns and warranty\",\n  expertise:\"technical expertise\", facility:\"the store itself\", communication:\"communication\" };\nconst word = k => PLAIN[k] || k;\n\n// ---------- QUIET MONTH: written in code, no model call. ----------\n// Most months are quiet. A fixed four-line template cannot hallucinate, costs nothing,\n// and its shape tells the owner at a glance that there is nothing new to read.\nif (d.report_type === 'quiet') {\n  // A quiet month still has to tell the truth about direction. The earlier version printed\n  // only the lifetime average, which is the exact number the full report proves is\n  // misleading, so the tool would have spent eleven months undoing its own headline.\n  const rw = d.recent_window || {};\n  const top = (d.minority_flags || []).slice(0, 2)\n    .map(f => `${word(f.theme)} (${f.negative} negative${f.positive ? `, ${f.positive} positive` : \", none positive\"})`);\n\n  const lines = [\n    \"NO NEW REVIEWS THIS MONTH\",\n    \"\",\n    `All time: ${d.reviews_analyzed} reviews, ${d.average_rating} average.`\n  ];\n\n  if (rw.reviews !== undefined && rw.average_rating !== null && rw.average_rating !== undefined) {\n    const dir = rw.rating_direction;\n    const move = (dir === null || dir === undefined || dir === 0)\n      ? \"level with\"\n      : (dir < 0 ? `down ${Math.abs(dir)} from` : `up ${dir} from`);\n    lines.push(`Last ${rw.months} months: ${rw.reviews} review${rw.reviews === 1 ? '' : 's'}, `\n             + `${rw.average_rating} average, ${move} ${rw.prior_average_rating} before that.`);\n  }\n  if (rw.newest_review_at) {\n    const months = Math.floor((Date.now() - new Date(rw.newest_review_at).getTime()) / (1000*60*60*24*30.44));\n    lines.push(`Your most recent review is from ${rw.newest_review_at}, about ${months} months ago.`);\n  }\n  lines.push(\"\");\n\n  if (rw.too_few_to_judge) {\n    lines.push(\"WORTH KNOWING\", \"\",\n      \"Your all-time average is carried by older reviews. With so few recent ones, your store looks \"\n      + \"quiet to anyone searching today, and there is not enough current feedback to tell whether \"\n      + \"older problems have been resolved. Asking a few recent happy customers for a review is the \"\n      + \"fastest way to fix both.\", \"\");\n  }\n\n  if (top.length) {\n    // blank line after the heading, or the email renderer treats heading+bullets\n    // as one block and prints them as a run-on paragraph.\n    lines.push(\"STILL OPEN\", \"\", ...top.map(t => `- ${t}`), \"\");\n  }\n  lines.push(\"We keep reading every month. You only hear from us when something moves.\");\n  return [{ json: { ...d, prompt: null, quiet_digest: lines.join(\"\\n\") } }];\n}\n\nconst facts = JSON.stringify({\n  reviews_analyzed: d.reviews_analyzed,\n  average_rating: d.average_rating,\n  themes: d.themes,\n  splits: d.splits,\n  minority_flags: d.minority_flags,\n  recent_window: d.recent_window\n}, null, 1);\n\nconst c = d.comparison;\nconst isDelta = d.report_type === 'delta' && c;\nconst changeFacts = isDelta ? JSON.stringify({\n  new_reviews_this_period: d.new_reviews,\n  previous_total: c.previous_total,\n  current_total: d.reviews_analyzed,\n  previous_average: c.previous_average,\n  current_average: d.average_rating,\n  average_change: c.average_change,\n  changed_themes: c.changed_themes,\n  first_ever_positive_mentions: c.first_positive_mentions\n}, null, 1) : null;\n\nconst prompt =\n`You are writing a short feedback digest that is emailed DIRECTLY TO the owner and store manager\nof an HVAC parts retailer. They are the reader. Address them as \"you\" and \"your store\". Never\nwrite about them in the third person (\"the owner should...\"), because they are the one reading it.\nWrite as their IT partner reporting what customers said, not as an analyst describing a business.\nYou are given ALREADY-COMPUTED analysis. Do not recount, re-judge, or infer beyond it.\n\nCOMPUTED ANALYSIS:\n${facts}\n${isDelta ? `\nWHAT CHANGED SINCE THE LAST REPORT:\n${changeFacts}\n` : \"\"}\nHARD RULES:\n${isDelta ? `- THIS IS A FOLLOW-UP REPORT. The owner already read the previous one. Lead with WHAT CHANGED. Do not re-explain findings that were unchanged; a repeated report is a report nobody opens.\n- Spend most of the digest on the ${d.new_reviews} new review(s) and on themes in \"changed_themes\". Mention unchanged standing findings in one line at most.\n- If \"first_ever_positive_mentions\" is not empty, say so plainly. A theme earning its first positive mention is the clearest sign something got fixed, and it is the most encouraging thing you can tell an owner.\n- Only describe a change if it appears in the change data. Never infer improvement or decline from tone.` : `- THIS IS THE FIRST REPORT for this business. Cover the whole picture.`}\n- ${d.has_contradictions\n    ? \"There ARE genuine disagreements (see \\\"splits\\\"). Give them their own section with the counts on each side. Do not average them into one lukewarm sentence.\"\n    : \"There are NO genuine disagreements in this batch. Say so plainly in one line. DO NOT invent, imply, or hint at a split. Inventing disagreement here would be a factual error.\"}\n- \"minority_flags\" holds EVERY negative signal that is not part of a declared split, ALREADY SORTED WORST FIRST by a computed \"severity\" score. Lead with the highest severity. Every flag with severity_label \"high\" or \"medium\" MUST appear. You may group the \"low\" ones into a single closing line. A theme with \"no_positive_offset\": true means nothing good was ever said about it, which is worse than a higher raw count that is mostly positive.\n- TREND FIRST: \"recent_window\" compares the last 24 months against everything before it. If\n\"rating_direction\" is negative, the lifetime average is flattering them and you must say so\nearly and plainly, with both numbers. A business sliding from 4.8 to 3.0 needs to hear that\nbefore it hears anything else. If the direction is positive, say that too, and mean it.\n- REVIEW VOLUME: if \"too_few_to_judge\" is true, the store has had almost no reviews in two\nyears. Say this explicitly as a finding in its own right. A business with no recent reviews is\ninvisible to people searching for it, and it also means you CANNOT tell a fixed problem from a\nquiet one. Recommend asking recent satisfied customers for reviews.\n- RECENCY: every flag carries \"newest_negative_at\" (the date of its most recent complaint),\n\"months_since_last_complaint\", and \"evidence_stale\". When a flag is stale, say so in plain words and\ngive the date of the most recent complaint. If \"too_few_to_judge\" is FALSE (they have had real\nreview volume since, with no recurrence), you may say it looks resolved and they can disregard.\nIf \"too_few_to_judge\" is TRUE, you must NOT say it looks resolved. Say instead that there has\nnot been enough recent feedback to tell, and that it may already be handled. Never present an\nabsence of complaints as proof of repair when there is an absence of reviews. Do not soften the finding, just date it honestly. An owner who fixed something\nin 2019 should not be told to go fix it again in 2026. Where complaints are recent, say that too,\nbecause \"this happened last month\" and \"this happened six years ago\" call for different urgency.\n- Use only the counts given. Never state a number that is not in the analysis.\n- CREDIBILITY: never assert a customer claim as established fact. One reviewer's account is a\nreport, not a finding. Write it as \"one reviewer reports X\" and name the record YOU CAN ASK THEM\nto check it against (close-out times, the work ticket, the invoice). Where a claim cannot be\nchecked at all (\"the owner does not care\"), describe it as how that customer felt, never as\nwhat happened. The owner knows their own business. One sentence they know to be false costs\nyou the credibility of the entire report.\n- PARAPHRASE IS HELD TO THE SAME STANDARD AS A QUOTE. Never describe an OUTCOME the review\ndoes not state. A customer predicting, threatening, or wishing for a consequence is not a\nreport that the consequence happened: \"they will get caught up in court\" is anger, NOT a\nlegal dispute. Do not upgrade a threat into an event, a worry into a fact, or an opinion\ninto a finding. If you cannot describe it without adding something the customer did not\nsay, quote them instead.\nQUOTES MUST BE EXACT. If you put text in quotation marks it must be copied character for character from a \"quote\" field in the analysis. Do not reorder, trim, or rephrase inside quotation marks. If you cannot quote exactly, describe it without quotation marks.\n- Plain English to a business owner. No buzzwords, no em dashes, use commas.\n- PLAIN TEXT ONLY. No markdown. No asterisks for bold, no ## headings, no --- rules. Section headings in CAPS on their own line, list items starting with \"- \". This gets emailed as plain text.\n- NEVER print raw theme keys. They are internal identifiers. Write them as normal words: price_value -> pricing, product_quality -> product quality, stock_availability -> stock availability, staff_service -> staff service, wait_time -> wait times, shipping_delivery -> shipping, returns_warranty -> returns and warranty, expertise -> technical expertise, facility -> the store itself, communication -> communication.\n- End with 2-3 concrete action items, each tied to a specific theme.\n\nFormat:\n${isDelta ? `WHAT CHANGED\n<2-4 sentences on the new reviews and any moved themes>\n\n` : \"\"}WHAT CUSTOMERS SAID\n<3-5 sentences>\n\n${d.has_contradictions ? \"WHERE THEY DISAGREE\\n<one bullet per split, with counts on each side>\" : \"DISAGREEMENTS\\n<one line stating there were none>\"}\n\nWORTH A CLOSER LOOK\n<minority complaints worst first, or \"none\" if the list is empty>\n\nDO THIS NEXT\n<2-3 bullets>`;\nreturn [{ json: { ...d, prompt, quiet_digest: null } }];\n"
      },
      "typeVersion": 2
    },
    {
      "name": "Write Digest",
      "type": "@n8n/n8n-nodes-langchain.anthropic",
      "maxTries": 3,
      "position": [
        464,
        1168
      ],
      "parameters": {
        "modelId": {
          "__rl": true,
          "mode": "list",
          "value": "claude-sonnet-4-6",
          "cachedResultName": "claude-sonnet-4-6"
        },
        "options": {
          "temperature": 0.2
        },
        "messages": {
          "values": [
            {
              "content": "={{ $json.prompt }}"
            }
          ]
        }
      },
      "retryOnFail": true,
      "typeVersion": 1,
      "waitBetweenTries": 5000
    },
    {
      "name": "Format Digest",
      "type": "n8n-nodes-base.code",
      "position": [
        784,
        1168
      ],
      "parameters": {
        "jsCode": "\nconst a = $('Aggregate + Detect Splits').first().json;\nconst j = $input.first().json;\nconst grab = (x) => {\n  if (typeof x === \"string\") return x;\n  if (Array.isArray(x?.content)) return x.content.map(c => c?.text || \"\").join(\"\");\n  return x?.content?.[0]?.text || x?.text || x?.output || \"\";\n};\nconst bp = $('Build Digest Prompt').first().json;\n// A quiet month never goes near the model; its digest was built in code upstream.\nconst digest = bp.quiet_digest ? bp.quiet_digest : String(grab(j) || \"\").trim();\n\n// Self-check: did the writer obey the no-invented-conflict rule?\nconst claimsDisagreement = /disagree|conflict|split|some customers.*(while|but|others)/i.test(digest);\nconst violation = !a.has_contradictions && /WHERE THEY DISAGREE/i.test(digest);\n\n// QUOTE DRIFT CHECK: every quoted string in the finished digest must appear verbatim\n// in some source review. Trailing punctuation placed inside the closing quote mark is\n// allowed (normal typography); anything else is the writer paraphrasing inside quotes.\nconst canon = (s) => String(s == null ? \"\" : s)\n  .replace(/[\\u2018\\u2019]/g, \"'\")\n  .replace(/[\\u201C\\u201D]/g, '\"')\n  .replace(/\\s+/g, \" \")\n  .trim();\nconst sources = canon($('Union Stored + New').all().map(i => i.json.text || \"\").join(\" ||| \"));\nconst quoted = (digest.match(/\"[^\"]{4,160}\"/g) || []).map(q => q.slice(1, -1));\nconst drifted = quoted.filter(q => {\n  const c = canon(q).replace(/[.,;:!?]+$/, \"\");\n  return !sources.includes(c);\n});\n\n// Carry the comparison fields through; the email and the saved report row both need them.\nconst cmp = $('Compare To Last Report').first().json;\nreturn [{ json: {\n  digest,\n  recent_window: a.recent_window,\n  report_type: cmp.report_type,\n  new_reviews: cmp.new_reviews,\n  comparison: cmp.comparison,\n  theme_counts: cmp.theme_counts,\n  business_name: cmp.business_name,\n  location_id: cmp.location_id,\n  period_label: cmp.report_type === 'quiet' ? 'No new reviews'\n    : (cmp.report_type === 'delta' ? `${cmp.new_reviews} new review${cmp.new_reviews === 1 ? '' : 's'}`\n    : `All ${cmp.reviews_analyzed} reviews`),\n  flag_count: (a.minority_flags || []).length,\n  reviews_analyzed: a.reviews_analyzed,\n  average_rating: a.average_rating,\n  had_real_contradictions: a.has_contradictions,\n  split_count: (a.splits || []).length,\n  minority_flag_count: (a.minority_flags || []).length,\n  parse_errors: a.parse_errors,\n  source: a.source,\n  QA_invented_conflict: violation,\n  QA_quotes_checked: quoted.length,\n  QA_quote_drift: drifted.length > 0,\n  QA_drifted_quotes: drifted,\n  QA_note: violation\n    ? \"FAIL: no real split existed but the digest published a disagreement section.\"\n    : \"ok\"\n}}];\n"
      },
      "typeVersion": 2
    },
    {
      "name": "Build Email HTML",
      "type": "n8n-nodes-base.code",
      "position": [
        1008,
        1168
      ],
      "parameters": {
        "jsCode": "\n// Renders the plain-text digest into a branded HTML email.\n// The LLM writes prose only. All markup is built here, so nothing it emits can break layout.\nconst d = $json;\nconst esc = s => String(s == null ? '' : s)\n  .replace(/&/g,'&amp;').replace(/</g,'&lt;').replace(/>/g,'&gt;');\n\nconst NAVY='#0B2545', ACCENT='#1D7A8C', INK='#1f2933', MUTED='#6b7785', LINE='#e3e8ee';\nconst blocks = String(d.digest || '').split(/\\n\\s*\\n/).map(b => b.trim()).filter(Boolean);\nlet body = '';\nfor (const b of blocks) {\n  // A heading is a short ALL-CAPS line with no leading bullet.\n  if (/^[A-Z][A-Z0-9 ,'&/-]{2,60}$/.test(b) && !b.startsWith('-')) {\n    body += `<h2 style=\"margin:26px 0 10px;font:600 13px/1.4 Arial,sans-serif;letter-spacing:.09em;text-transform:uppercase;color:${ACCENT};border-bottom:1px solid ${LINE};padding-bottom:6px;\">${esc(b)}</h2>`;\n  } else if (b.startsWith('-')) {\n    const items = b.split('\\n').filter(l => l.trim().startsWith('-'))\n      .map(l => `<li style=\"margin:0 0 11px;\">${esc(l.replace(/^\\s*-\\s*/,''))}</li>`).join('');\n    body += `<ul style=\"margin:0 0 14px;padding-left:20px;font:400 15px/1.62 Arial,sans-serif;color:${INK};\">${items}</ul>`;\n  } else {\n    body += `<p style=\"margin:0 0 14px;font:400 15px/1.62 Arial,sans-serif;color:${INK};\">${esc(b)}</p>`;\n  }\n}\n\n\n// Month-over-month strip. Only rendered when there is a real prior report to compare to.\nconst c = d.comparison;\nconst arrow = (n) => n > 0 ? '\u25b2' : (n < 0 ? '\u25bc' : '\u2013');\nconst tone  = (n, goodUp) => n === 0 ? MUTED : ((n > 0) === goodUp ? '#1a7f5a' : '#b4232a');\nlet cmpStrip = '';\nif (c && d.report_type !== 'quiet') {   // the quiet header already says it\n  const bits = [];\n  bits.push(`<span style=\"color:${tone(c.total_change, true)};\">${arrow(c.total_change)} ${Math.abs(c.total_change)} new review${Math.abs(c.total_change) === 1 ? '' : 's'}</span>`);\n  if (c.average_change !== null) {\n    bits.push(`<span style=\"color:${tone(c.average_change, true)};\">${arrow(c.average_change)} ${c.average_change === 0 ? 'rating unchanged' : Math.abs(c.average_change).toFixed(2) + ' rating'}</span>`);\n  }\n  if ((c.first_positive_mentions || []).length) {\n    bits.push(`<span style=\"color:#1a7f5a;font-weight:600;\">first positive mention: ${esc(c.first_positive_mentions.join(', ').replace(/_/g,' '))}</span>`);\n  }\n  cmpStrip = `<tr><td style=\"padding:10px 28px;background:#f2f7f9;border-bottom:1px solid ${LINE};font:400 13px/1.5 Arial,sans-serif;color:${INK};\">\n    <strong style=\"color:${NAVY};\">Since last report:</strong> ${bits.join(' &nbsp;&middot;&nbsp; ')}\n  </td></tr>`;\n}\n\nconst stat = (n,l) => `<td align=\"center\" style=\"padding:12px 8px;\"><div style=\"font:700 22px/1 Arial,sans-serif;color:${NAVY};\">${esc(n)}</div><div style=\"font:400 11px/1.4 Arial,sans-serif;color:${MUTED};text-transform:uppercase;letter-spacing:.06em;margin-top:4px;\">${esc(l)}</div></td>`;\n\nconst html = `<!doctype html><html><body style=\"margin:0;padding:0;background:#f4f6f8;\">\n<table role=\"presentation\" width=\"100%\" cellpadding=\"0\" cellspacing=\"0\" style=\"background:#f4f6f8;padding:24px 12px;\"><tr><td align=\"center\">\n<table role=\"presentation\" width=\"640\" cellpadding=\"0\" cellspacing=\"0\" style=\"max-width:640px;width:100%;background:#ffffff;border-radius:10px;overflow:hidden;border:1px solid ${LINE};\">\n  <tr><td style=\"background:${NAVY};padding:22px 28px;\">\n    <div style=\"font:700 19px/1.3 Arial,sans-serif;color:#ffffff;\">Review Intelligence</div>\n    <div style=\"font:400 13px/1.4 Arial,sans-serif;color:#9fb3c8;margin-top:3px;\">${esc(d.business_name || 'Your business')} &nbsp;&middot;&nbsp; ${esc(d.period_label || 'Latest reviews')}</div>\n  </td></tr>\n  ${cmpStrip}\n  <tr><td style=\"padding:4px 20px 0;\"><table role=\"presentation\" width=\"100%\" cellpadding=\"0\" cellspacing=\"0\" style=\"border-bottom:1px solid ${LINE};\"><tr>\n    ${stat(d.reviews_analyzed, 'Reviews read')}\n    ${stat(d.average_rating, 'Avg all time')}\n    ${(d.recent_window && d.recent_window.average_rating !== null && d.recent_window.average_rating !== undefined)\n        ? stat(d.recent_window.average_rating, 'Avg last 24mo') : ''}\n    ${stat(d.split_count, 'Real splits')}\n    ${stat(d.flag_count, 'Needs a look')}\n  </tr></table></td></tr>\n  <tr><td style=\"padding:6px 28px 26px;\">${body}</td></tr>\n  <tr><td style=\"background:#f8fafb;border-top:1px solid ${LINE};padding:16px 28px;font:400 12px/1.6 Arial,sans-serif;color:${MUTED};\">\n    Generated from ${esc(d.reviews_analyzed)} Google reviews. Every quote is verbatim. Themes are counted in code, not estimated.<br>\n    <span style=\"color:${NAVY};font-weight:600;\">Your Company</span> &nbsp;&middot;&nbsp; Review Intelligence\n  </td></tr>\n</table></td></tr></table></body></html>`;\n\nreturn [{ json: { ...d, email_html: html, email_subject: `Review Intelligence: ${d.business_name || 'your business'} (${d.reviews_analyzed} reviews, ${d.average_rating}\u2605)` } }];\n"
      },
      "typeVersion": 2
    },
    {
      "name": "Send Digest Email",
      "type": "n8n-nodes-base.microsoftOutlook",
      "maxTries": 3,
      "position": [
        1232,
        1168
      ],
      "parameters": {
        "subject": "={{ $json.email_subject }}",
        "bodyContent": "={{ $json.email_html }}",
        "toRecipients": "client@example.com",
        "additionalFields": {
          "ccRecipients": "manager@example.com",
          "bccRecipients": "you@example.com",
          "bodyContentType": "html"
        }
      },
      "retryOnFail": true,
      "typeVersion": 2,
      "waitBetweenTries": 5000
    },
    {
      "name": "Fetch Stored",
      "type": "n8n-nodes-base.httpRequest",
      "maxTries": 3,
      "position": [
        80,
        384
      ],
      "parameters": {
        "url": "={{ 'https://YOUR_PROJECT_REF.supabase.co/rest/v1/review_classifications?select=review_id,themes,star_rating,review_text,reviewer_name,review_created_at&limit=5000&location_id=eq.' + encodeURIComponent($json.location_id) }}",
        "options": {},
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "supabaseApi"
      },
      "executeOnce": true,
      "retryOnFail": true,
      "typeVersion": 4.2,
      "alwaysOutputData": true,
      "waitBetweenTries": 3000
    },
    {
      "name": "Select Unclassified",
      "type": "n8n-nodes-base.code",
      "position": [
        304,
        384
      ],
      "parameters": {
        "jsCode": "\nconst CFG = $('Run Config').first().json;\nconst LOCATION_ID = CFG.location_id;\nconst BUSINESS_NAME = CFG.business_name;\n// Classify-once gate. Anything already in review_classifications is never sent to the model\n// again, which is what makes the numbers stable: LLM output drifts run to run even at\n// temperature 0, so the only reliable fix is to stop asking twice.\nconst stored = new Set(\n  $input.all().map(i => i.json && i.json.review_id).filter(Boolean)\n);\nconst fresh = $('Normalize Reviews').all()\n  .map(i => i.json)\n  .filter(r => r.review_id && !stored.has(r.review_id))\n  .map(r => ({ json: { ...r, location_id: LOCATION_ID, business_name: BUSINESS_NAME } }));\nconsole.log(`already stored: ${stored.size} | to classify: ${fresh.length}`);\nreturn fresh;   // may be empty; the IF node downstream routes that case\n"
      },
      "typeVersion": 2,
      "alwaysOutputData": true
    },
    {
      "name": "Has New Reviews?",
      "type": "n8n-nodes-base.if",
      "position": [
        528,
        384
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "hasnew",
              "operator": {
                "type": "string",
                "operation": "notEmpty",
                "singleValue": true
              },
              "leftValue": "={{ $json.review_id }}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "name": "Build Rows To Save",
      "type": "n8n-nodes-base.code",
      "position": [
        1632,
        368
      ],
      "parameters": {
        "jsCode": "\nconst CFG = $('Run Config').first().json;\nconst LOCATION_ID = CFG.location_id;\nconst BUSINESS_NAME = CFG.business_name;\n// One request, all rows. PostgREST takes an array body.\nconst src = $('Select Unclassified').all().map(i => i.json);\nconst rows = $input.all().map((item, i) => {\n  const j = item.json, s = src[i] || {};\n  return {\n    location_id: LOCATION_ID,\n    business_name: BUSINESS_NAME,\n    review_id: j.review_id,\n    star_rating: j.rating ?? null,\n    reviewer_name: j.author ?? null,\n    review_text: j.text ?? null,\n    review_created_at: s.created ?? null,\n    themes: j.findings || [],\n    model: \"claude-sonnet-4-6\"\n  };\n}).filter(r => r.review_id);\nreturn [{ json: { rows, count: rows.length } }];\n"
      },
      "typeVersion": 2
    },
    {
      "name": "Save Classifications",
      "type": "n8n-nodes-base.httpRequest",
      "maxTries": 3,
      "position": [
        1856,
        368
      ],
      "parameters": {
        "url": "=https://YOUR_PROJECT_REF.supabase.co/rest/v1/review_classifications",
        "method": "POST",
        "options": {},
        "jsonBody": "={{ JSON.stringify($json.rows) }}",
        "sendBody": true,
        "sendHeaders": true,
        "specifyBody": "json",
        "authentication": "predefinedCredentialType",
        "headerParameters": {
          "parameters": [
            {
              "name": "Prefer",
              "value": "resolution=merge-duplicates,return=minimal"
            }
          ]
        },
        "nodeCredentialType": "supabaseApi"
      },
      "retryOnFail": true,
      "typeVersion": 4.2,
      "alwaysOutputData": true,
      "waitBetweenTries": 3000
    },
    {
      "name": "Fetch Last Report",
      "type": "n8n-nodes-base.httpRequest",
      "maxTries": 3,
      "position": [
        -864,
        1184
      ],
      "parameters": {
        "url": "={{ 'https://YOUR_PROJECT_REF.supabase.co/rest/v1/review_reports?select=*&order=created_at.desc&limit=1&location_id=eq.' + encodeURIComponent($('Run Config').first().json.location_id) }}",
        "options": {},
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "supabaseApi"
      },
      "executeOnce": true,
      "retryOnFail": true,
      "typeVersion": 4.2,
      "alwaysOutputData": true,
      "waitBetweenTries": 3000
    },
    {
      "name": "Union Stored + New",
      "type": "n8n-nodes-base.code",
      "position": [
        -640,
        1184
      ],
      "parameters": {
        "jsCode": "\n// Rebuild the full classification set: everything previously stored, plus anything\n// classified on this run. Reports count THESE rows. Nothing is re-derived from a model.\nconst toParseShape = (row) => ({\n  review_id: row.review_id,\n  rating: row.star_rating ?? null,\n  text: row.review_text ?? \"\",\n  author: row.reviewer_name ?? \"anonymous\",\n  source: \"storage\",\n  created: row.review_created_at ?? null,\n  findings: Array.isArray(row.themes) ? row.themes : [],\n  parse_error: null\n});\n\nconst byId = new Map();\nfor (const it of $('Fetch Stored').all()) {\n  const r = it.json;\n  if (r && r.review_id) byId.set(r.review_id, toParseShape(r));\n}\nlet added = 0;\ntry {\n  for (const it of $('Parse Classification').all()) {\n    const r = it.json;\n    if (r && r.review_id) { byId.set(r.review_id, r); added++; }\n  }\n} catch (e) { /* quiet month: the classify branch never ran */ }\n\nconst all = [...byId.values()];\nconsole.log(`classifications in play: ${all.length} (newly classified this run: ${added})`);\nif (!all.length) throw new Error(\"No classifications available to report on.\");\nreturn all.map(json => ({ json }));\n"
      },
      "typeVersion": 2
    },
    {
      "name": "Compare To Last Report",
      "type": "n8n-nodes-base.code",
      "position": [
        -208,
        1184
      ],
      "parameters": {
        "jsCode": "\n// Month-over-month. The previous report's theme_counts snapshot is the baseline;\n// everything here is arithmetic against it, so the comparison cannot be invented.\nconst agg = $json;\nconst prevRows = $('Fetch Last Report').all().map(i => i.json).filter(r => r && r.id);\nconst prev = prevRows.length ? prevRows[0] : null;\n\nlet new_reviews = 0;\ntry { new_reviews = $('Parse Classification').all().length; } catch (e) {}\n\nconst theme_counts = {};\nfor (const s of (agg.themes || [])) {\n  theme_counts[s.theme] = { positive: s.positive, negative: s.negative, mixed: s.mixed };\n}\n\nconst report_type = !prev ? 'full' : (new_reviews > 0 ? 'delta' : 'quiet');\n\nlet comparison = null;\nif (prev) {\n  const pc = prev.theme_counts || {};\n  const changed = [];\n  for (const [theme, now] of Object.entries(theme_counts)) {\n    const was = pc[theme] || { positive: 0, negative: 0 };\n    const dp = now.positive - (was.positive || 0);\n    const dn = now.negative - (was.negative || 0);\n    if (dp || dn) changed.push({ theme, positive_change: dp, negative_change: dn,\n                                 now_positive: now.positive, now_negative: now.negative });\n  }\n  // A theme going from zero positive to its first positive is the single most\n  // meaningful movement a business owner can be told. Surface it explicitly.\n  const first_positive = changed.filter(c => c.positive_change > 0 &&\n                                             (pc[c.theme]?.positive || 0) === 0).map(c => c.theme);\n  comparison = {\n    previous_report_at: prev.created_at,\n    previous_total: prev.reviews_total,\n    previous_average: prev.average_rating === null ? null : Number(prev.average_rating),\n    total_change: agg.reviews_analyzed - (prev.reviews_total || 0),\n    average_change: (prev.average_rating != null && agg.average_rating != null)\n      ? Math.round((agg.average_rating - Number(prev.average_rating)) * 100) / 100 : null,\n    changed_themes: changed,\n    first_positive_mentions: first_positive\n  };\n}\n\nreturn [{ json: { ...agg, report_type, new_reviews, theme_counts, comparison,\n                  location_id: $('Run Config').first().json.location_id, business_name: $('Run Config').first().json.business_name } }];\n"
      },
      "typeVersion": 2
    },
    {
      "name": "Save Report Row",
      "type": "n8n-nodes-base.httpRequest",
      "maxTries": 3,
      "position": [
        1440,
        1168
      ],
      "parameters": {
        "url": "=https://YOUR_PROJECT_REF.supabase.co/rest/v1/review_reports",
        "method": "POST",
        "options": {},
        "jsonBody": "={{ JSON.stringify({ location_id: $('Format Digest').first().json.location_id, business_name: $('Format Digest').first().json.business_name, report_type: $('Format Digest').first().json.report_type, reviews_total: $('Format Digest').first().json.reviews_analyzed, new_reviews: $('Format Digest').first().json.new_reviews, average_rating: $('Format Digest').first().json.average_rating, split_count: $('Format Digest').first().json.split_count, flag_count: $('Format Digest').first().json.flag_count, theme_counts: $('Format Digest').first().json.theme_counts, digest: $('Format Digest').first().json.digest, sent_to: $('Send Digest Email').first().json ? 'sent' : null }) }}",
        "sendBody": true,
        "sendHeaders": true,
        "specifyBody": "json",
        "authentication": "predefinedCredentialType",
        "headerParameters": {
          "parameters": [
            {
              "name": "Prefer",
              "value": "return=minimal"
            }
          ]
        },
        "nodeCredentialType": "supabaseApi"
      },
      "retryOnFail": true,
      "typeVersion": 4.2,
      "alwaysOutputData": true,
      "waitBetweenTries": 3000
    },
    {
      "name": "Needs Writing?",
      "type": "n8n-nodes-base.if",
      "position": [
        256,
        1184
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "loose"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "needswrite",
              "operator": {
                "type": "string",
                "operation": "notEmpty",
                "singleValue": true
              },
              "leftValue": "={{ $json.prompt }}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "name": "Monthly Run",
      "type": "n8n-nodes-base.scheduleTrigger",
      "disabled": true,
      "position": [
        -864,
        256
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "months",
              "triggerAtHour": 8
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "name": "Run Config",
      "type": "n8n-nodes-base.code",
      "position": [
        -144,
        384
      ],
      "parameters": {
        "jsCode": "\n// SINGLE SOURCE OF TRUTH for which business this run belongs to.\n// Two reasons this exists:\n//  1) Test runs must NEVER write fake reviews into a real client's stored history.\n//     Every test set gets its own location_id, so prod data cannot be polluted.\n//  2) Everything downstream reads location_id from here, so pointing this workflow at\n//     a different client is a one-line change instead of hunting four hardcoded copies.\nconst LIVE = {\n  location_id:   \"locations/YOUR_GBP_LOCATION_ID\",\n  business_name: \"Your Business Name\"\n};\n\nconst first = $input.first().json || {};\nconst src = String(first.source || \"\");\nconst isTest = src.startsWith(\"TEST\");\n\nconst cfg = isTest\n  ? { location_id: \"TEST/\" + src.replace(/^TEST:?/, \"\"), business_name: \"TEST \" + src.replace(/^TEST:?/, \"\") }\n  : LIVE;\n\nconsole.log(`run config -> ${cfg.location_id} (${isTest ? \"TEST, isolated\" : \"LIVE CLIENT DATA\"})`);\nreturn $input.all().map(i => ({ json: { ...i.json, ...cfg, is_test: isTest } }));\n"
      },
      "typeVersion": 2
    },
    {
      "name": "Sticky - Overview",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -928,
        -784
      ],
      "parameters": {
        "color": 7,
        "width": 1488,
        "height": 804,
        "content": "# \ud83d\udd0d Review Intelligence for Google Reviews\n\n## Turns a business's Google reviews into a plain-English monthly email the owner actually reads.\n\nThe AI classifies one review at a time. Code does all the counting and every decision. The AI only turns finished numbers into sentences, which is what stops it inventing findings.\n\n## Setup\n1. Create the two Supabase tables (SQL note nearby).\n2. Attach credentials: Google Business Profile, Anthropic, Supabase, Outlook.\n3. Replace placeholders: YOUR_PROJECT_REF, YOUR_GBP_ACCOUNT_ID, YOUR_GBP_LOCATION_ID, emails.\n4. Run **Run Test Cases**, then **Run on Live Reviews**. Enable **Monthly Run** when ready.\n\nBuilt by Your Company."
      },
      "typeVersion": 1
    },
    {
      "name": "Sticky - 1 Triggers",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -928,
        48
      ],
      "parameters": {
        "color": 4,
        "width": 480,
        "height": 880,
        "content": "# 1 \u00b7 Triggers & fetch reviews\n\n## Run monthly or on demand. Run Test Cases loads fixtures A to E; the live triggers pull Google reviews."
      },
      "typeVersion": 1
    },
    {
      "name": "Sticky - 2 Prepare",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -416,
        48
      ],
      "parameters": {
        "color": 5,
        "width": 1080,
        "height": 880,
        "content": "# 2 \u00b7 Prepare & de-duplicate\n\n## Keep only reviews never classified before. Read once, never re-ask the model, so the numbers stay reproducible.\n\n### Attach your Supabase credential here."
      },
      "typeVersion": 1
    },
    {
      "name": "Sticky - 3 Classify",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        704,
        48
      ],
      "parameters": {
        "color": 6,
        "width": 1336,
        "height": 880,
        "content": "# 3 \u00b7 Classify once & verify\n\n## The AI labels one review at a time. Every quote is checked against the source in code before it is stored.\n\n### Attach your Anthropic credential here."
      },
      "typeVersion": 1
    },
    {
      "name": "Sticky - 4 Count",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -928,
        960
      ],
      "parameters": {
        "color": 3,
        "width": 900,
        "height": 520,
        "content": "# 4 \u00b7 Count in code\n\n## All the math is here, never in the AI: themes, splits, severity, the 24-month trend, and staleness."
      },
      "typeVersion": 1
    },
    {
      "name": "Sticky - 5 Write",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        0,
        960
      ],
      "parameters": {
        "color": 4,
        "width": 920,
        "height": 520,
        "content": "# 5 \u00b7 Write the digest\n\n## Computed facts become prose. Quiet months skip the AI entirely and are built in code."
      },
      "typeVersion": 1
    },
    {
      "name": "Sticky - 6 Deliver",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        960,
        960
      ],
      "parameters": {
        "color": 5,
        "width": 720,
        "height": 520,
        "content": "# 6 \u00b7 Deliver & remember\n\n## Send the email, then save this month's snapshot so next month can report only what changed."
      },
      "typeVersion": 1
    },
    {
      "name": "Sticky - SQL",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -2464,
        -768
      ],
      "parameters": {
        "color": 6,
        "width": 1500,
        "height": 1420,
        "content": "# \ud83d\uddc4\ufe0f Supabase schema \u2014 run ONCE before first use\n\n```sql\ncreate table if not exists public.review_classifications (\n  id uuid primary key default gen_random_uuid(),\n  location_id text not null, business_name text, review_id text not null,\n  star_rating int, reviewer_name text, review_text text,\n  review_created_at timestamptz, themes jsonb not null default '[]'::jsonb,\n  model text, classified_at timestamptz not null default now(),\n  unique (location_id, review_id)\n);\ncreate table if not exists public.review_reports (\n  id uuid primary key default gen_random_uuid(),\n  location_id text not null, business_name text, report_type text not null default 'full',\n  reviews_total int, new_reviews int not null default 0, average_rating numeric(3,2),\n  split_count int, flag_count int, theme_counts jsonb not null default '{}'::jsonb,\n  digest text, sent_to text, created_at timestamptz not null default now()\n);\n```\n## The unique (location_id, review_id) is what makes classify-once work: re-runs upsert instead of re-classifying."
      },
      "typeVersion": 1
    }
  ],
  "settings": {
    "executionOrder": "v1"
  },
  "connections": {
    "Run Config": {
      "main": [
        [
          {
            "node": "Fetch Stored",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Reviews": {
      "main": [
        [
          {
            "node": "Normalize Reviews",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Monthly Run": {
      "main": [
        [
          {
            "node": "Get Reviews",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch Stored": {
      "main": [
        [
          {
            "node": "Select Unclassified",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Write Digest": {
      "main": [
        [
          {
            "node": "Format Digest",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Format Digest": {
      "main": [
        [
          {
            "node": "Build Email HTML",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Load Test Set": {
      "main": [
        [
          {
            "node": "Normalize Reviews",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Find Locations": {
      "main": [
        [
          {
            "node": "List Locations With Names",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Needs

About this workflow

Pulls Google Business Profile reviews, classifies each one with Claude, stores them in Supabase, and emails the owner a monthly plain-English digest via Outlook. Every count, disagreement and trend is computed in code, never by the model, so the report cannot invent findings.…

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

More AI & RAG workflows → · Browse all categories →

Related workflows

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