// anomaly-sentinel — EN build 2026-09-14
// Copyright 2026 Gökhan Güzel — gokhanguzel.com. All Rights Reserved. Generated file: edit src/ and i18n/, then run build.js.
// Do not share configured Slack webhook URLs.
'use strict';
const SCRIPT_ID = "anomaly-sentinel";
const L = {"lang":"en","locale":"en-US","text_lang":"en","dec":".","thou":","};
const ATTRIBUTION = String.fromCharCode(169,32,103,111,107,104,97,110,103,117,122,101,108,46,99,111,109);
const T = {"kpi":"KPI","kpi.now":"This period","kpi.prev":"Previous","yes":"yes","no":"no","mail.full_report":"Full report","mail.open_report":"Open report","note.no_rows":"No rows.","note.truncated":"Showing {shown} of {total} rows (max_rows_written).","note.no_campaigns":"No campaigns with impressions match the current filters.","settings.key":"Key","settings.value":"Value","settings.notes":"Notes","settings.script":"script that owns this Sheet (do not edit)","settings.account":"Google Ads account that owns this Sheet (do not edit)","note.truncated_queries":"⚠ {n} quer(y/ies) hit the read limit — results incomplete; narrow the window","note.account_neg_failed":"⚠ account-level negatives could not be read:","note.skipped_time":"skipped — execution time budget","note.data_error":"data error:","note.failed":"FAILED —","note.no_final_url":"(no final URL)","alias.settings":["Settings","Ayarlar","Einstellungen"],"alias.history":["History","Kronoloji","Verlauf","Runs","Calismalar","Laeufe"],"alias.snapshot":["Snapshot","Snapshots","Kesit","Kesitler","Momentaufnahme","Momentaufnahmen"],"alias.state":["State","DurumBellegi","Zustand"],"alias.baseline":["Baseline","TemelCizgi","Basislinie"],"m.impressions":"Impr.","m.clicks":"Clicks","m.ctr":"CTR","m.cost":"Cost","m.cpc":"Avg. CPC","m.conversions":"Conv.","m.value":"Conv. value","m.cr":"Conv. rate","m.cpa":"CPA","m.roas":"ROAS","m.aov":"AOV","m.vpc":"Value / click","col.account":"Account","col.period":"Period","col.currency":"Currency","col.date":"Date","col.campaign":"Campaign","col.ad_group":"Ad group","col.n":"N","col.phrase":"Phrase","col.queries":"Queries","col.source":"Source","col.why":"Why","title":"Anomaly Sentinel","tab.alerts":"Alerts","alias.alerts":["Uyarılar","Alarme","Alerts Log","Uyarı Günlüğü","Alarmprotokoll"],"settings.email":"comma-separated recipients; blank = no e-mail","settings.weeks":"same-weekday history depth (default 12)","settings.lag_hours":"reporting delay tolerance in hours (default 3)","settings.quiet_hours_end":"no rate-based alerts before this hour (default 6)","settings.min_impressions":"volume gate for rate metrics (default 1000)","settings.min_clicks":"volume gate for rate metrics (default 50)","settings.min_conversions_baseline":"skip CPA / conv. rate / ROAS alerts when baseline conversions are below this (default 3)","settings.z_warning":"default 2.0","settings.z_critical":"default 3.0","settings.min_pct_deviation":"ignore deviations below this share, e.g. 0.20 (default)","settings.exception_dates":"yyyy-MM-dd, comma-separated (holidays, launches)","settings.campaign_hints":"top-N campaigns explaining a deviation (default 3)","settings.conversion_metrics_max_severity":"WARNING (default) or CRITICAL — cap for conversion-based metrics until the snapshot baseline has enough history","col.timestamp":"Timestamp","col.hour":"Hour","col.metric":"Metric","col.status":"Status","col.severity":"Severity","col.observed":"Observed","col.expected":"Expected","col.deviation":"Deviation","col.samples":"Samples","col.hints":"Campaign hints","col.delivered":"Delivered via","sev.OK":"OK","sev.WARNING":"WARNING","sev.CRITICAL":"CRITICAL","status.NEW":"NEW","status.ESCALATED":"ESCALATED","status.RECOVERED":"RECOVERED","mail.subject":"Google Ads {cid} — {sev} anomaly ({date} {hour}:00)","mail.subject_recovered":"✅ Google Ads {cid} — anomalies recovered ({date} {hour}:00)","mail.sub":"{date} · evaluated through {hour}:00 · {currency}"};
/* ===================== common/core ===================== */
/* Pure helpers. No Sheets, no network. Everything here is unit-tested in test/run.js. */
/* AdsApp.search is report-backed and is not subject to selector entity limits. Keep a defensive count check only for
runtimes/iterators that expose totalNumEntities(); normally this stays at zero. */
let TRUNCATED_ = 0;
function query_(gaql) {
const out = [];
const it = AdsApp.search(gaql);
while (it.hasNext()) out.push(it.next());
try { if (typeof it.totalNumEntities === 'function') { const n = it.totalNumEntities(); if (n > out.length) { TRUNCATED_++; Logger.log(`Query truncated: ${out.length} of ${n} rows read — narrow the window or filters. ${gaql.slice(0, 120)}`); } } } catch (e) {}
return out;
}
/* Seconds left in the 30-minute execution window (Infinity when the runtime does not expose it, e.g. in the mock). */
function timeLeft_() { try { return AdsApp.getExecutionInfo().getRemainingTime(); } catch (e) { return Infinity; } }
function num_(v) { const x = parseFloat(v); return isFinite(x) ? x : 0; }
function nz_(v) { return isFinite(v) ? v : ''; }
function esc_(s) { return String(s).replace(/\\/g, '\\\\').replace(/'/g, "\\'"); }
function escLike_(s) { const m = { '[': '[[]', ']': '[]]', '%': '[%]', '_': '[_]' }; return esc_(s).replace(/[\[\]%_]/g, c => m[c]); }
function chunks_(arr, n) { const o = []; for (let i = 0; i < arr.length; i += n) o.push(arr.slice(i, i + n)); return o; }
function pct_(a, b) { return (typeof a === 'number' && typeof b === 'number' && isFinite(a) && isFinite(b) && b) ? a / b - 1 : NaN; }
/* Number formatting for e-mail / Slack. Deterministic: separators come from the build (L.dec / L.thou), not from
the runtime's ICU data. */
function fmtNum_(v, digits) {
if (typeof v !== 'number' || !isFinite(v)) return '–';
const d = digits === undefined ? (Math.abs(v) >= 1000 ? 0 : Math.abs(v) < 1 ? 3 : 2) : digits;
const parts = Math.abs(v).toFixed(d).split('.');
return (v < 0 ? '-' : '') + parts[0].replace(/\B(?=(\d{3})+(?!\d))/g, L.thou) + (parts[1] ? L.dec + parts[1] : '');
}
function fmtPct_(v, digits) { return isFinite(v) ? fmtNum_(v * 100, digits === undefined ? 2 : digits) + '%' : '–'; }
/* Metric bag: raw sums + derived rates. NaN means "undefined", never 0. */
function zeroM_() { return { impressions: 0, clicks: 0, cost: 0, conversions: 0, value: 0 }; }
function rawOf_(r, opts) {
const m = (r && r.metrics) || {}; opts = opts || {};
return {
impressions: num_(m.impressions), clicks: num_(m.clicks),
cost: opts.noCost ? 0 : num_(m.costMicros) / 1e6,
conversions: opts.noConv ? NaN : num_(m.conversions),
value: opts.noConv ? NaN : num_(m.conversionsValue)
};
}
function addM_(a, b) {
const sumKnown = (x, y) => isFinite(x) && isFinite(y) ? x + y : NaN;
return {
impressions: a.impressions + b.impressions, clicks: a.clicks + b.clicks, cost: a.cost + b.cost,
conversions: sumKnown(a.conversions, b.conversions),
value: sumKnown(a.value, b.value)
};
}
function derive_(o) {
o.ctr = o.impressions ? o.clicks / o.impressions : NaN;
o.cpc = o.clicks ? o.cost / o.clicks : NaN;
o.cr = o.clicks && isFinite(o.conversions) ? o.conversions / o.clicks : NaN;
o.cpa = o.conversions > 0 ? o.cost / o.conversions : NaN;
o.roas = o.cost > 0 && isFinite(o.value) ? o.value / o.cost : NaN;
o.aov = o.conversions > 0 ? o.value / o.conversions : NaN;
o.vpc = o.clicks ? o.value / o.clicks : NaN;
return o;
}
/* Text normalisation for search terms / negatives. language: 'tr' handles İ/ı. */
function normalize_(s, language) {
s = String(s || '');
s = language === 'tr' ? s.replace(/I/g, 'ı').replace(/İ/g, 'i').toLowerCase() : s.toLowerCase();
return s.replace(/[+\[\]"“”'’`^*]/g, ' ')
.replace(/[^\p{L}\p{N}\s\-\.]/gu, ' ')
.split(/\s+/).map(w => w.replace(/^[.\-]+|[.\-]+$/g, '')).filter(Boolean).join(' ');
}
function tokens_(s, language) { const n = normalize_(s, language); return n ? n.split(' ') : []; }
function hasSubseq_(hay, needle) {
outer: for (let i = 0; i + needle.length <= hay.length; i++) {
for (let j = 0; j < needle.length; j++) if (hay[i + j] !== needle[j]) continue outer;
return true;
}
return false;
}
/* exact = same tokens; phrase = contiguous; broad = every word present. */
function blocked_(q, negs) {
if (!negs) return false;
for (const n of negs) {
if (!n.words.length) continue;
if (n.mt === 'EXACT') { if (n.words.length === q.length && n.words.every((w, i) => w === q[i])) return true; }
else if (n.mt === 'PHRASE') { if (hasSubseq_(q, n.words)) return true; }
else if (n.words.every(w => q.includes(w))) return true;
}
return false;
}
/* Robust stats: median / MAD (σ-scaled), SD fallback. */
function median_(sorted) { const n = sorted.length; if (!n) return NaN; return n % 2 ? sorted[(n - 1) / 2] : (sorted[n / 2 - 1] + sorted[n / 2]) / 2; }
function robust_(arr) {
if (!arr.length) return { median: NaN, mad: NaN, scale: 0 };
const s = arr.slice().sort((a, b) => a - b), med = median_(s);
const mad = median_(s.map(v => Math.abs(v - med)).sort((a, b) => a - b)) * 1.4826;
let scale = mad;
if (!(scale > 0)) { const mean = arr.reduce((x, v) => x + v, 0) / arr.length; scale = Math.sqrt(arr.reduce((x, v) => x + (v - mean) * (v - mean), 0) / Math.max(1, arr.length - 1)); }
return { median: med, mad: mad, scale: scale };
}
/* Minimal HTML escape for anything user-generated that lands in e-mail. */
function h_(s) { return String(s === undefined || s === null ? '' : s).replace(/&/g, '&').replace(/</g, '<').replace(/>/g, '>').replace(/"/g, '"'); }
/* Text lookup. T is injected by the build; keys are stable, values are per language. */
function t_(key, vars) {
let s = T[key]; if (s === undefined) s = key;
return String(s).replace(/\{(\w+)\}/g, (m, k) => (vars && vars[k] !== undefined) ? vars[k] : m);
}
/* ===================== common/dates ===================== */
/* Calendar-based date arithmetic in the account time zone. No 864e5 shifts,
so DST transitions cannot skip or repeat a day. */
function tz_() { return AdsApp.currentAccount().getTimeZone(); }
function todayStr_(tz, base) { return Utilities.formatDate(base || new Date(), tz, 'yyyy-MM-dd'); }
function shiftDay_(dateStr, days) {
const p = dateStr.split('-').map(Number);
const d = new Date(Date.UTC(p[0], p[1] - 1, p[2] + days));
return d.getUTCFullYear() + '-' + String(d.getUTCMonth() + 1).padStart(2, '0') + '-' + String(d.getUTCDate()).padStart(2, '0');
}
/* window_(days, offset): `days` complete days, ending `offset+1` days before today. offset 0 → ends yesterday. */
function window_(days, offset, tz, base) {
const today = todayStr_(tz, base);
const end = shiftDay_(today, -(offset + 1)), start = shiftDay_(today, -(offset + days));
return { start: start, end: end, label: start + ' → ' + end };
}
const DOW_ = ['SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY'];
function dowOf_(dateStr) { const p = dateStr.split('-').map(Number); return DOW_[new Date(Date.UTC(p[0], p[1] - 1, p[2])).getUTCDay()]; }
/* Sheets may hand back a Date where we wrote a 'yyyy-MM-dd' string. Always normalise on read. */
function dateStrOf_(v, tz) {
if (v instanceof Date) return Utilities.formatDate(v, tz, 'yyyy-MM-dd');
return String(v === null || v === undefined ? '' : v).trim(); // strings are never truncated: keys like 'yyyy-MM-dd HH:mm' or '123-456-7890' stay intact
}
/* ===================== common/manager ===================== */
/* Runs as a single account, or selects one client account when started from a manager account. */
function isManager_() { try { if (typeof AdsManagerApp === 'undefined') return false; AdsManagerApp.accounts().withLimit(1).get(); return true; } catch (e) { return false; } }
function selectAccount_(accountId) {
if (!isManager_()) return;
if (!accountId) throw new Error('Running from a manager account: set CONFIG.account_id (e.g. 123-456-7890).');
const it = AdsManagerApp.accounts().withIds([String(accountId)]).get();
if (!it.hasNext()) throw new Error('Account ' + accountId + ' is not a client of this manager account.');
AdsManagerApp.select(it.next());
}
/* ===================== common/sheets ===================== */
/* Technical tabs are language-independent. User-facing tab names come from T. */
const TECH = { settings: '_settings', state: '_state', snapshot: '_snapshot', history: '_history', baseline: '_baseline' };
function openSpreadsheet_(url) {
if (!url || url === 'YOUR_SPREADSHEET_URL') throw new Error('Set CONFIG.spreadsheet_url to a Google Sheet you own.');
const ss = SpreadsheetApp.openByUrl(url);
claimSheet_(ss); // ownership first: nothing is written to a Sheet that belongs to another script or account
return seal_(ss); // every script opens its Sheet through the attribution layer
}
/* One Sheet per script. The first run records SCRIPT_ID in _settings; a different script pointed at the same Sheet stops
before it can overwrite tabs (ngram-lens and pmax-ngram-lens share tab names). */
function claimSheet_(ss) {
const sh = tab_(ss, TECH.settings, T['alias.settings']);
const cid = AdsApp.currentAccount().getCustomerId();
const kv = {}; if (sh.getLastRow() > 1) sh.getRange(2, 1, sh.getLastRow() - 1, 2).getValues().forEach(r => kv[String(r[0])] = String(r[1]));
if (kv.script && kv.script !== SCRIPT_ID) throw new Error(`This Sheet belongs to ${kv.script}. Use a separate Sheet for ${SCRIPT_ID}.`);
if (kv.account && kv.account !== cid) throw new Error(`This Sheet belongs to account ${kv.account}; running as ${cid}. Use a separate Sheet per account.`);
if (!kv.script || !kv.account) ensureSettings_(ss, []);
}
/* Title in A1, header in row `top` (2). Writes only what is missing, so the attribution cell in H1 never masks an empty
header row. Returns true when the header was (re)written. */
function ensureTitledHeader_(sheet, title, header) {
ensureSize_(sheet, 3, header.length);
if (String(sheet.getRange(1, 1).getValue()) === '') sheet.getRange(1, 1).setValue(title).setFontWeight('bold').setFontSize(13);
const a2 = sheet.getRange(2, 1).getValue();
if (String(a2) === header[0]) return false;
if (String(a2) !== '') sheet.insertRowsBefore(2, 1); // data written under a missing header (v2.2 layout): push it down, then add the header
styleHeader_(sheet.getRange(2, 1, 1, header.length).setValues([header])); sheet.setFrozenRows(2);
return true;
}
/* Find a tab by its current name or any legacy alias; rename legacy tabs once so state survives a language switch. */
function tab_(ss, name, aliases) {
let sh = ss.getSheetByName(name);
if (!sh && aliases) for (const a of aliases) { const old = ss.getSheetByName(a); if (old) { old.setName(name); sh = old; break; } }
if (!sh) sh = ss.insertSheet(name);
return sh;
}
/* specs: [{name, aliases:[...]}]. Removes the empty default tab whatever its locale name is. */
function ensureTabs_(ss, specs) {
const wanted = new Set(specs.map(s => s.name));
specs.forEach(s => tab_(ss, s.name, s.aliases));
const first = ss.getSheets()[0];
if (first && !wanted.has(first.getName()) && ss.getSheets().length > 1 && onlyAttribution_(first)) ss.deleteSheet(first);
}
/* A default tab that holds nothing but the attribution cell written by the early seal_ is still "empty". */
function onlyAttribution_(sheet) {
if (sheet.getLastRow() === 0) return true;
if (sheet.getLastRow() > 1) return false;
const row = sheet.getRange(1, 1, 1, Math.max(1, sheet.getLastColumn())).getValues()[0];
return row.every((v, i) => v === '' || (i === 7 && v === ATTRIBUTION));
}
/* Grow the grid BEFORE any getRange: Sheets throws on coordinates outside the current dimensions. */
function ensureSize_(sheet, rows, cols) {
if (cols > sheet.getMaxColumns()) sheet.insertColumnsAfter(sheet.getMaxColumns(), cols - sheet.getMaxColumns());
if (rows > sheet.getMaxRows()) sheet.insertRowsAfter(sheet.getMaxRows(), rows - sheet.getMaxRows());
}
function styleHeader_(rng) { return rng.setFontWeight('bold').setBackground('#1E293B').setFontColor('#ffffff'); }
/* Write a table. Rows above `top` are never touched (callers put titles there). formats[i] is a number format per column ('@' = text, applied BEFORE setValues so Sheets never auto-parses).
Rows beyond `cap` are dropped and a note is written. Returns the number of rows written. */
function table_(sheet, top, header, rows, formats, cap) {
let out = rows;
if (cap && rows.length > cap) out = rows.slice(0, cap);
ensureSize_(sheet, top + out.length + 1, header.length);
const maxR = sheet.getMaxRows(), maxC = sheet.getMaxColumns();
if (maxR >= top) sheet.getRange(top, 1, maxR - top + 1, maxC).clearContent(); // contents only: user notes/formats outside the table survive
styleHeader_(sheet.getRange(top, 1, 1, header.length).setValues([header]));
if (out.length) {
(formats || []).forEach((f, i) => { if (f) sheet.getRange(top + 1, i + 1, out.length, 1).setNumberFormat(f); });
sheet.getRange(top + 1, 1, out.length, header.length).setValues(out);
if (out.length < rows.length) sheet.getRange(top + 1 + out.length, 1).setValue(t_('note.truncated', { shown: out.length, total: rows.length }));
} else {
sheet.getRange(top + 1, 1).setValue(t_('note.no_rows'));
}
sheet.setFrozenRows(top);
return out.length;
}
/* Append rows with text formats applied first (appendRow cannot do that). */
function appendRows_(sheet, rows, formats) {
if (!rows.length) return;
const r0 = sheet.getLastRow() + 1, n = rows.length, w = rows[0].length;
ensureSize_(sheet, r0 + n - 1, w);
(formats || []).forEach((f, i) => { if (f) sheet.getRange(r0, i + 1, n, 1).setNumberFormat(f); });
sheet.getRange(r0, 1, n, w).setValues(rows);
}
/* A technical tab whose header row differs from what this version writes (older layout) is renamed to an archive tab and a
fresh one is created — rows of two schemas are never mixed. Returns the sheet to write to. */
function ensureSchema_(ss, name, header) {
const sh = ss.getSheetByName(name); if (!sh || sh.getLastRow() === 0) return sh;
const cur = sh.getRange(1, 1, 1, Math.max(1, sh.getLastColumn())).getValues()[0].map(String);
while (cur.length && cur[cur.length - 1] === '') cur.pop();
if (cur.join('|') === header.join('|')) return sh;
let n = 1, archive = name + '_archive'; while (ss.getSheetByName(archive)) archive = name + '_archive' + (++n);
sh.setName(archive); Logger.log(`${name}: old layout archived as ${archive}`);
return ss.insertSheet(name);
}
/* Idempotent history: one row per key (first `keyCols` columns, joined). Existing row with the same key is replaced. */
function upsertRow_(sheet, header, row, keyCols, formats, tz) {
ensureSize_(sheet, 2, Math.max(header.length, row.length));
const cur = sheet.getLastRow() === 0 ? [] : sheet.getRange(1, 1, 1, Math.max(1, sheet.getLastColumn())).getValues()[0].map(String).filter(Boolean);
if (cur.join('|') !== header.join('|')) {
const w = Math.max(header.length, sheet.getLastColumn()); sheet.getRange(1, 1, 1, w).clearContent();
styleHeader_(sheet.getRange(1, 1, 1, header.length).setValues([header])); sheet.setFrozenRows(1);
} // new/changed schema: clear stale cells, then rewrite header
const key = row.slice(0, keyCols).map(v => dateStrOf_(v, tz)).join('|');
const last = sheet.getLastRow();
if (last > 1) {
const keys = sheet.getRange(2, 1, last - 1, keyCols).getValues().map(r => r.map(v => dateStrOf_(v, tz)).join('|'));
const i = keys.indexOf(key);
if (i >= 0) {
const w = Math.max(row.length, sheet.getLastColumn()); sheet.getRange(i + 2, 1, 1, w).clearContent();
sheet.getRange(i + 2, 1, 1, row.length).setValues([row]); return;
}
}
appendRows_(sheet, [row], formats);
}
/* Replace a whole table safely: write the new rows first, then clear whatever is left below. */
function replaceRows_(sheet, header, rows, formats) {
const n = rows.length;
ensureSize_(sheet, n + 1, header.length);
if (sheet.getLastRow() === 0) styleHeader_(sheet.getRange(1, 1, 1, header.length).setValues([header]));
if (n) {
(formats || []).forEach((f, i) => { if (f) sheet.getRange(2, i + 1, n, 1).setNumberFormat(f); });
sheet.getRange(2, 1, n, header.length).setValues(rows);
}
const last = sheet.getLastRow();
if (last > n + 1) sheet.getRange(n + 2, 1, last - n - 1, Math.max(header.length, sheet.getLastColumn())).clearContent();
}
/* _settings: Key | Value | Notes. Keys are stable identifiers (never translated); notes are per language. */
function ensureSettings_(ss, defaults) {
const sh = tab_(ss, TECH.settings, T['alias.settings']);
ensureSize_(sh, 2 + defaults.length, 3);
if (sh.getLastRow() === 0) {
sh.getRange(1, 1, 1, 3).setNumberFormat('@').setValues([[t_('settings.key'), t_('settings.value'), t_('settings.notes')]]);
styleHeader_(sh.getRange(1, 1, 1, 3)); sh.setFrozenRows(1); sh.setColumnWidths(1, 1, 200); sh.setColumnWidths(3, 1, 420);
}
const all = [['script', SCRIPT_ID, t_('settings.script')], ['account', AdsApp.currentAccount().getCustomerId(), t_('settings.account')]].concat(defaults);
const last = sh.getLastRow();
const keys = last > 1 ? sh.getRange(2, 1, last - 1, 1).getValues().map(r => String(r[0])) : [];
const missing = all.filter(d => keys.indexOf(d[0]) < 0);
if (missing.length) { const r0 = sh.getLastRow() + 1; ensureSize_(sh, r0 + missing.length, 3); sh.getRange(r0, 1, missing.length, 3).setNumberFormat('@').setValues(missing.map(d => [d[0], d[1], d[2]])); }
if (keys.length) { // notes follow the current language; one batched write, only when something differs; values are never touched
const rng = sh.getRange(2, 3, keys.length, 1), cur = rng.getValues().map(r => String(r[0]));
const want = keys.map((k, i) => { const d = all.find(x => x[0] === k); return d && d[2] ? d[2] : cur[i]; });
if (want.join('\u0001') !== cur.join('\u0001')) rng.setValues(want.map(v => [v]));
}
return sh;
}
/* Reads _settings into cfg. Only keys present in `schema` are accepted; type comes from schema: 'num' | 'bool' | 'str' | 'list'. */
function readSettings_(ss, cfg, schema) {
const out = JSON.parse(JSON.stringify(cfg));
const sh = ss.getSheetByName(TECH.settings);
if (!sh || sh.getLastRow() < 2) return out;
sh.getRange(2, 1, sh.getLastRow() - 1, 2).getValues().forEach(([k, v]) => {
const type = schema[k]; if (!type || v === '' || v === null) return;
if (type === 'num') {
if (typeof v === 'number') { out[k] = v; return; }
const s = String(v).trim().replace(/\s/g, '');
if (!/^-?\d+([.,]\d+)?$/.test(s)) throw new Error(`_settings.${k}: "${v}" is not a number (use 0.5 or 0,5)`);
if (/^-?\d{1,3}[.,]\d{3}$/.test(s)) throw new Error(`_settings.${k}: "${v}" is ambiguous; do not use thousands separators (write 1000 or 0.5 / 0,5)`);
out[k] = parseFloat(s.replace(',', '.')); // decimal comma/dot accepted; ambiguous 1.000 / 1,000 is rejected
}
else if (type === 'bool') out[k] = /^(true|yes|1|on|evet|ja)$/i.test(String(v).trim());
else if (type === 'list') out[k] = String(v).split(',').map(s => s.trim()).filter(Boolean);
else out[k] = String(v).trim();
});
return out;
}
function writeSetting_(ss, key, value) {
const sh = ss.getSheetByName(TECH.settings); if (!sh) return;
const last = sh.getLastRow();
const keys = last > 1 ? sh.getRange(2, 1, last - 1, 1).getValues().map(r => String(r[0])) : [];
const i = keys.indexOf(key);
const cell = i >= 0 ? sh.getRange(i + 2, 2) : sh.getRange(last + 1, 2);
if (i < 0) sh.getRange(last + 1, 1).setNumberFormat('@').setValue(key);
cell.setNumberFormat('@').setValue(value);
}
/* ===================== common/seal ===================== */
/* Attribution cell. Warning-only protection (no editor management → no Session dependency, no lock-out of the
running user). Never throws: a report must not fail because a Sheet cell was edited.
Called twice: from openSpreadsheet_ (first tab, before anything else) and at the end of main() on the report tab.
Returns the spreadsheet so the open path depends on it. This is attribution, not DRM: anyone who edits the
source can remove it. */
function seal_(ss, tabName) {
try {
const sheet = (tabName && ss.getSheetByName(tabName)) || ss.getSheets().find(s => !s.getName().startsWith('_')) || ss.getSheets()[0];
const want = ATTRIBUTION;
const range = sheet.getRange('H1:K1');
let cur = ''; try { cur = String(range.getCell(1, 1).getValue()); } catch (e) {}
if (cur !== want) {
try { range.breakApart(); range.merge(); } catch (e) {}
range.getCell(1, 1).setValue(want).setFontWeight('bold').setFontColor('#0F766E').setHorizontalAlignment('center').setNote(want);
}
const has = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE).some(p => p.getDescription() === want);
if (!has) range.protect().setDescription(want).setWarningOnly(true);
} catch (e) { Logger.log('Attribution skipped: ' + e); }
return ss;
}
/* ===================== common/notify ===================== */
function htmlShell_(title, subtitle, body, url) {
return '<div style="font:13px Arial,sans-serif;color:#1E293B;max-width:760px">' +
'<div style="background:#1E293B;color:#fff;padding:12px 16px;font-size:15px;font-weight:bold">' + h_(title) + '</div>' +
'<div style="padding:10px 16px;color:#64748B">' + h_(subtitle) + '</div>' + body +
'<div style="padding:12px 16px;color:#94A3B8;font-size:11px">' + (url ? h_(t_('mail.full_report')) + ': <a href="' + h_(url) + '">' + h_(url) + '</a><br>' : '') + h_(ATTRIBUTION) + '</div></div>';
}
function kpiTable_(rows) {
/* rows: [[label, now, prev, delta_html]] */
const th = (s, r) => '<th style="padding:5px 10px;text-align:' + (r ? 'right' : 'left') + '">' + h_(s) + '</th>';
return '<table style="border-collapse:collapse;width:100%"><tr style="background:#F1F5F9;color:#64748B;font-size:11px">' +
th(t_('kpi'), false) + th(t_('kpi.now'), true) + th(t_('kpi.prev'), true) + th('Δ', true) + '</tr>' +
rows.map(r => '<tr><td style="padding:5px 10px">' + h_(r[0]) + '</td><td style="padding:5px 10px;text-align:right;font-weight:bold">' + h_(r[1]) +
'</td><td style="padding:5px 10px;text-align:right;color:#64748B">' + h_(r[2]) + '</td><td style="padding:5px 10px;text-align:right">' + r[3] + '</td></tr>').join('') + '</table>';
}
function delta_(a, b, invert) {
const v = pct_(a, b); if (!isFinite(v)) return '';
const good = invert ? v < 0 : v > 0;
return '<span style="color:' + (good ? '#15803D' : '#B91C1C') + '">' + (v >= 0 ? '▲' : '▼') + ' ' + fmtPct_(Math.abs(v), 0) + '</span>';
}
function sendMail_(to, subject, html, text) {
if (!to) return false;
try { MailApp.sendEmail({ to: to, subject: subject, htmlBody: html, body: text || subject }); return true; }
catch (e) { Logger.log('E-mail failed: ' + e); return false; }
}
function slackText_(s) { return String(s === undefined || s === null ? '' : s).replace(/&/g, '&').replace(/</g, '<').replace(/>/g, '>'); }
function sendSlack_(webhook, text) {
if (!webhook) return false;
try {
const res = UrlFetchApp.fetch(webhook, { method: 'post', contentType: 'application/json', muteHttpExceptions: true, payload: JSON.stringify({ text: text + '\n_' + slackText_(ATTRIBUTION) + '_' }) });
const ok = res.getResponseCode() === 200;
if (!ok) Logger.log('Slack returned ' + res.getResponseCode() + ': ' + res.getContentText().slice(0, 200));
return ok;
} catch (e) { Logger.log('Slack failed: ' + e); return false; }
}
/* ===================== common/negatives ===================== */
/* Negative keywords by ID: ad group, campaign, shared lists attached to campaigns, and account level. */
function fetchNegatives_(campaignIds, language, opts) {
opts = opts || {};
const neg = { adGroup: {}, campaign: {}, account: [] };
const item = (text, mt) => ({ words: tokens_(text, language), mt: String(mt || '').toUpperCase() });
const push = (bucket, key, text, mt) => { (bucket[key] = bucket[key] || []).push(item(text, mt)); };
chunks_(campaignIds, 200).forEach(chunk => {
if (opts.adGroupLevel !== false) {
query_(`SELECT ad_group.id, ad_group_criterion.keyword.text, ad_group_criterion.keyword.match_type
FROM ad_group_criterion WHERE ad_group_criterion.negative = TRUE AND ad_group_criterion.type = 'KEYWORD'
AND ad_group_criterion.status != 'REMOVED' AND campaign.id IN (${chunk.join(',')})`)
.forEach(r => push(neg.adGroup, String(r.adGroup.id), r.adGroupCriterion.keyword.text, r.adGroupCriterion.keyword.matchType));
}
query_(`SELECT campaign.id, campaign_criterion.keyword.text, campaign_criterion.keyword.match_type
FROM campaign_criterion WHERE campaign_criterion.negative = TRUE AND campaign_criterion.type = 'KEYWORD'
AND campaign_criterion.status != 'REMOVED' AND campaign.id IN (${chunk.join(',')})`)
.forEach(r => push(neg.campaign, String(r.campaign.id), r.campaignCriterion.keyword.text, r.campaignCriterion.keyword.matchType));
});
const setToCampaigns = {};
chunks_(campaignIds, 200).forEach(chunk => {
query_(`SELECT campaign.id, shared_set.id FROM campaign_shared_set
WHERE shared_set.type = 'NEGATIVE_KEYWORDS' AND campaign_shared_set.status = 'ENABLED' AND shared_set.status = 'ENABLED'
AND campaign.id IN (${chunk.join(',')})`)
.forEach(r => (setToCampaigns[String(r.sharedSet.id)] = setToCampaigns[String(r.sharedSet.id)] || []).push(String(r.campaign.id)));
});
const setIds = Object.keys(setToCampaigns);
chunks_(setIds, 200).forEach(chunk => {
query_(`SELECT shared_set.id, shared_criterion.keyword.text, shared_criterion.keyword.match_type
FROM shared_criterion WHERE shared_criterion.type = 'KEYWORD' AND shared_set.id IN (${chunk.join(',')})`)
.forEach(r => setToCampaigns[String(r.sharedSet.id)].forEach(cid => push(neg.campaign, cid, r.sharedCriterion.keyword.text, r.sharedCriterion.keyword.matchType)));
});
/* Account-level negatives are a shared set of type ACCOUNT_LEVEL_NEGATIVE_KEYWORDS (customer_negative_criterion has no
keyword field; it only points at that list). */
try {
query_(`SELECT shared_criterion.keyword.text, shared_criterion.keyword.match_type
FROM shared_criterion WHERE shared_set.type = 'ACCOUNT_LEVEL_NEGATIVE_KEYWORDS' AND shared_criterion.type = 'KEYWORD' AND shared_set.status = 'ENABLED'`)
.forEach(r => neg.account.push(item(r.sharedCriterion.keyword.text, r.sharedCriterion.keyword.matchType)));
} catch (e) { Logger.log('Account-level negatives not readable: ' + e); neg.accountError = String(e); }
return neg;
}
function isBlocked_(tok, neg, campaignId, adGroupId) {
return blocked_(tok, neg.account) || blocked_(tok, neg.campaign[campaignId]) || (adGroupId ? blocked_(tok, neg.adGroup[adGroupId]) : false);
}
/* ===================== common/metrics ===================== */
/* Standard metric block for tables: header / row / number formats, all driven by T and one metric bag. */
const M_KEYS = ['impressions', 'clicks', 'ctr', 'cost', 'cpc', 'conversions', 'value', 'cr', 'cpa', 'roas'];
const M_FMT = ['#,##0', '#,##0', '0.00%', '#,##0.00', '#,##0.00', '#,##0.0', '#,##0.00', '0.00%', '#,##0.00', '0.00'];
function mHead_(keys) { return (keys || M_KEYS).map(k => t_('m.' + k)); }
function mRow_(m, keys) { return (keys || M_KEYS).map(k => nz_(m[k])); }
function mFmt_(keys) { return (keys || M_KEYS).map(k => M_FMT[M_KEYS.indexOf(k)] || null); }
/* ===================== common/ngram ===================== */
/* Shared n-gram engine for N-Gram Lens and PMax N-Gram Lens. terms: [{campaignId, campaign, adGroupId, term, source, categories?, m}] */
function ngramAnalyse_(terms, neg, opts) {
const lang = opts.language, stop = new Set((opts.stopwords || []).map(w => normalize_(w, lang)));
const brand = (opts.brand_terms || []).map(b => tokens_(b, lang)).filter(t => t.length);
const acc = {}, camp = {}, cat = {}, wc = {}, totals = zeroM_(), split = { brand: zeroM_(), generic: zeroM_() };
const blockedKeys = new Set(); let excluded = 0, queries = 0;
const bump = (map, key, seed, m) => { const a = map[key] = map[key] || Object.assign(zeroM_(), { queries: 0 }, seed); Object.assign(a, addM_(a, m)); a.queries++; };
terms.forEach(t => {
const tok = tokens_(t.term, lang); if (!tok.length) return;
if (neg && isBlocked_(tok, neg, t.campaignId, t.adGroupId)) { excluded++; blockedKeys.add(t.campaignId + '|' + t.term.toLowerCase()); return; }
queries++; Object.assign(totals, addM_(totals, t.m));
const isBrand = brand.length && brand.some(b => hasSubseq_(tok, b));
const side = isBrand ? split.brand : split.generic; Object.assign(side, addM_(side, t.m)); side.queries = (side.queries || 0) + 1;
bump(wc, tok.length > 6 ? '7+' : String(tok.length), { words: tok.length > 6 ? '7+' : String(tok.length) }, t.m);
const seen = new Set();
for (let n = opts.min_ngram; n <= opts.max_ngram && n <= tok.length; n++) for (let i = 0; i + n <= tok.length; i++) {
const words = tok.slice(i, i + n); if (n === 1 && stop.has(words[0])) continue;
const g = words.join(' '), k = n + '|' + g; if (seen.has(k)) continue; seen.add(k);
bump(acc, k, { n: n, gram: g }, t.m);
bump(camp, t.campaignId + '|' + k, { n: n, gram: g, campaign: t.campaign, source: t.source }, t.m);
(t.categories || []).forEach(c => bump(cat, t.campaignId + '|' + c + '|' + k, { n: n, gram: g, campaign: t.campaign, category: c }, t.m));
}
});
derive_(totals); derive_(split.brand); derive_(split.generic);
const th = opts.thresholds, keep = a => a.queries >= th.query_count && a.impressions >= th.impressions && a.clicks >= th.clicks && (a.cost || 0) >= (th.cost || 0);
const rank = opts.rankBy === 'clicks' ? ((x, y) => y.clicks - x.clicks || y.impressions - x.impressions || x.gram.localeCompare(y.gram))
: ((x, y) => y.cost - x.cost || y.impressions - x.impressions || x.gram.localeCompare(y.gram));
return { queries, excluded, totals, split, blockedKeys,
account: Object.values(acc).map(derive_).filter(keep).sort(rank),
campaign: Object.values(camp).map(derive_).filter(keep).sort((x, y) => x.campaign.localeCompare(y.campaign) || rank(x, y)),
category: Object.values(cat).map(derive_).filter(keep).sort((x, y) => x.campaign.localeCompare(y.campaign) || x.category.localeCompare(y.category) || rank(x, y)),
wordCount: ['1', '2', '3', '4', '5', '6', '7+'].map(k => derive_(Object.assign({ words: k, queries: 0 }, zeroM_(), wc[k] || {}))) };
}
const NG_KEYS = ['queries'].concat(M_KEYS);
function ngHead_(keys) { return (keys || NG_KEYS).map(k => k === 'queries' ? t_('col.queries') : t_('m.' + k)); }
function ngFmt_(keys) { return (keys || NG_KEYS).map(k => k === 'queries' ? '#,##0' : M_FMT[M_KEYS.indexOf(k)] || null); }
function ngRow_(a, keys) { return (keys || NG_KEYS).map(k => nz_(a[k])); }
function writeNgrams_(ss, tabKey, rows, segKeys, keys, cap) {
const head = segKeys.map(k => t_('col.' + k)).concat([t_('col.n'), t_('col.phrase')]).concat(ngHead_(keys));
const fmt = segKeys.map(() => '@').concat(['0', '@']).concat(ngFmt_(keys));
table_(ss.getSheetByName(t_('tab.' + tabKey)), 1, head, rows.map(r => segKeys.map(k => r[k]).concat([r.n, r.gram]).concat(ngRow_(r, keys))), fmt, cap);
}
/**
* @name Anomaly Sentinel
* @overview Hourly account-health monitor. Compares today (through the last complete hour) with the same
* weekday over the past N weeks using median / MAD z-scores, absolute floors for zero baselines, volume
* gates, quiet hours, exception dates, campaign-level hints, de-duplicated alerts with recovery notices.
* Conversion metrics are capped at WARNING until the hour-of-day snapshot baseline has enough history
* (conversion lag makes "today so far" structurally lower than matured history).
* Schedule: Hourly.
*/
const CONFIG = {
spreadsheet_url: 'YOUR_SPREADSHEET_URL',
email: '',
slack_webhook: '', // keep here, not in the sheet
account_id: '',
weeks: 12,
lag_hours: 3,
quiet_hours_end: 6,
min_impressions: 1000,
min_clicks: 50,
min_conversions_baseline: 3,
z_warning: 2.0,
z_critical: 3.0,
min_pct_deviation: 0.20,
abs_floor: { impressions: 200, clicks: 20, cost: 20, conversions: 2, value: 50 }, // zero-baseline rule
conversion_metrics_max_severity: 'WARNING',
snapshot_baseline_min_samples: 12, // switch conversion metrics to the hour-of-day snapshot baseline after this many
snapshot_keep_weeks: 26, // _snapshot rows kept (hourly); older rows are pruned
exception_dates: [],
campaign_hints: 3
};
const SETTINGS_SCHEMA = { email: 'str', weeks: 'num', lag_hours: 'num', quiet_hours_end: 'num', min_impressions: 'num', min_clicks: 'num',
min_conversions_baseline: 'num', z_warning: 'num', z_critical: 'num', min_pct_deviation: 'num', exception_dates: 'list', campaign_hints: 'num',
conversion_metrics_max_severity: 'str' };
const METRICS = [
{ key: 'impressions', bad: 'low', kind: 'volume' }, { key: 'clicks', bad: 'both', kind: 'volume' },
{ key: 'cost', bad: 'both', kind: 'volume' }, { key: 'conversions', bad: 'low', kind: 'volume', conv: true },
{ key: 'value', bad: 'low', kind: 'volume', conv: true },
{ key: 'ctr', bad: 'both', kind: 'rate' }, { key: 'cpc', bad: 'high', kind: 'rate' },
{ key: 'cr', bad: 'low', kind: 'rate', conv: true }, { key: 'cpa', bad: 'high', kind: 'rate', conv: true }, { key: 'roas', bad: 'low', kind: 'rate', conv: true }
];
const RAW = 'metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions, metrics.conversions_value';
function main() {
selectAccount_(CONFIG.account_id);
const ss = openSpreadsheet_(CONFIG.spreadsheet_url);
const tz = tz_(), cid = AdsApp.currentAccount().getCustomerId(), currency = AdsApp.currentAccount().getCurrencyCode();
ss.setSpreadsheetTimeZone(tz);
ensureTabs_(ss, [{ name: t_('tab.alerts'), aliases: T['alias.alerts'] }, { name: TECH.settings, aliases: T['alias.settings'] },
{ name: TECH.snapshot, aliases: T['alias.snapshot'] }, { name: TECH.baseline, aliases: T['alias.baseline'] }, { name: TECH.state, aliases: T['alias.state'] }]);
ensureSettings_(ss, Object.keys(SETTINGS_SCHEMA).map(k => [k, '', t_('settings.' + k)]));
ensureTitledHeader_(ss.getSheetByName(t_('tab.alerts')), t_('title'), alertsHead_()); // header exists from the first run, events or not
const cfg = readSettings_(ss, CONFIG, SETTINGS_SCHEMA);
cfg.conversion_metrics_max_severity = String(cfg.conversion_metrics_max_severity || '').toUpperCase() === 'CRITICAL' ? 'CRITICAL' : 'WARNING';
['weeks', 'campaign_hints'].forEach(k => cfg[k] = Math.max(1, Math.round(cfg[k]) || 1)); cfg.lag_hours = Math.min(23, Math.max(0, cfg.lag_hours || 0));
const now = new Date(), upTo = new Date(now.getTime() - cfg.lag_hours * 3600 * 1000);
const hour = parseInt(Utilities.formatDate(upTo, tz, 'H'), 10);
const today = todayStr_(tz, upTo), dow = dowOf_(today);
const start = shiftDay_(today, -7 * cfg.weeks), yesterday = shiftDay_(today, -1);
const exceptions = new Set(cfg.exception_dates);
if (hour === 0) { Logger.log('Hour 0 — nothing complete yet.'); return; }
const observedRaw = sumUpTo_(query_(`SELECT segments.hour, ${RAW} FROM customer WHERE segments.date = '${today}'`), hour);
const observed = derive_(observedRaw);
const byDate = {};
query_(`SELECT segments.date, segments.hour, segments.day_of_week, ${RAW} FROM customer WHERE segments.day_of_week = '${dow}' AND segments.date BETWEEN '${start}' AND '${yesterday}'`)
.forEach(r => { const d = r.segments.date; if (exceptions.has(d) || parseInt(r.segments.hour, 10) >= hour) return; byDate[d] = addM_(byDate[d] || zeroM_(), rawOf_(r)); });
const samples = Object.keys(byDate).sort().map(d => derive_(byDate[d]));
const snap = readSnapshotBaseline_(ss, dow, hour, tz, exceptions); // hour-of-day observed history for conversion metrics
const convBaseline = robust_(samples.map(s => s.conversions)).median || 0;
const evals = METRICS.map(m => {
const useSnap = m.conv && snap[m.key] && snap[m.key].length >= cfg.snapshot_baseline_min_samples;
const hist = (useSnap ? snap[m.key] : samples.map(s => s[m.key])).filter(v => isFinite(v));
const st = robust_(hist);
const ev = { m: m, observed: observed[m.key], expected: st.median, mad: st.mad, z: NaN, pct: NaN, n: hist.length, severity: 'OK', basis: useSnap ? 'snapshot' : 'history' };
if (hist.length >= 4 && isFinite(ev.observed)) {
ev.z = st.scale > 0 ? (ev.observed - st.median) / st.scale : NaN; // NaN = constant history, judged by % only
ev.pct = st.median !== 0 ? ev.observed / st.median - 1 : NaN;
ev.severity = classify_(ev, cfg, observedRaw, convBaseline, hour, exceptions.has(today), useSnap);
}
return ev;
});
const alerting = evals.filter(e => e.severity !== 'OK');
let hints = '';
if (alerting.length) { const lead = alerting.filter(e => e.m.kind === 'volume').sort((a, b) => mag_(b) - mag_(a))[0] || alerting[0]; hints = campaignHints_(lead.m.key, today, dow, start, yesterday, hour, exceptions, cfg.campaign_hints); }
const state = readState_(ss, tz), events = [];
evals.forEach(ev => {
const prev = state[ev.m.key] || { date: '', severity: 'OK' }, sameDay = prev.date === today;
let status = null;
if (ev.severity !== 'OK') { if (!sameDay || prev.severity === 'OK') status = 'NEW'; else if (rank_(ev.severity) > rank_(prev.severity)) status = 'ESCALATED'; }
else if (sameDay && prev.severity !== 'OK') status = 'RECOVERED';
if (status) { events.push(Object.assign({ status: status, hints: ev.m.kind === 'volume' ? hints : '' }, ev)); state[ev.m.key] = { date: today, severity: ev.severity }; }
});
writeSnapshot_(ss, now, today, hour, dow, cid, evals);
writeBaseline_(ss, dow, hour, evals);
if (events.length) {
const delivered = notify_(cfg, cid, currency, today, hour, events, ss.getUrl());
if (delivered) appendAlerts_(ss, now, today, hour, cid, currency, events);
else events.forEach(e => { if (e.status !== 'RECOVERED') delete state[e.m.key]; }); // nothing delivered: no log row, state rolled back → same events next hour
}
writeState_(ss, state);
seal_(ss, t_('tab.alerts'));
Logger.log(`${today} ${hour}:00 — ${samples.length} samples, ${alerting.length} anomalies, ${events.length} events.`);
}
function classify_(ev, cfg, raw, convBaseline, hour, isException, snapBasis) {
const m = ev.m;
if (isException) return 'OK';
if (m.conv && !snapBasis && convBaseline < cfg.min_conversions_baseline) return 'OK'; // too few baseline conversions: neither rates nor conv/value volumes are judged
if (m.kind === 'rate') {
if (hour < cfg.quiet_hours_end) return 'OK';
if (raw.impressions < cfg.min_impressions || raw.clicks < cfg.min_clicks) return 'OK';
}
const dir = ev.observed > ev.expected ? 'high' : 'low';
if (m.bad !== 'both' && m.bad !== dir) return 'OK';
let sev = 'OK';
if (ev.expected === 0 || !isFinite(ev.pct)) { // zero baseline: absolute floor rule
const floor = cfg.abs_floor[m.key];
if (floor !== undefined && ev.observed >= floor && dir === 'high') sev = 'WARNING';
} else {
if (Math.abs(ev.pct) < cfg.min_pct_deviation) return 'OK';
if (!isFinite(ev.z)) sev = Math.abs(ev.pct) >= 0.5 ? 'WARNING' : 'OK'; // constant baseline (scale 0)
else { const az = Math.abs(ev.z); sev = az >= cfg.z_critical ? 'CRITICAL' : az >= cfg.z_warning ? 'WARNING' : 'OK'; }
}
if (m.conv && !snapBasis && rank_(sev) > rank_(cfg.conversion_metrics_max_severity)) sev = cfg.conversion_metrics_max_severity;
return sev;
}
function mag_(e) { return isFinite(e.z) ? Math.abs(e.z) : isFinite(e.pct) ? Math.abs(e.pct) : 0; }
function rank_(s) { return s === 'CRITICAL' ? 2 : s === 'WARNING' ? 1 : 0; }
function sumUpTo_(rows, hour) { return rows.filter(r => parseInt(r.segments.hour, 10) < hour).reduce((a, r) => addM_(a, rawOf_(r)), zeroM_()); }
function campaignHints_(key, today, dow, start, yesterday, hour, exceptions, topN) {
try {
const cur = {}, past = {}, names = {}, dates = new Set();
query_(`SELECT campaign.id, campaign.name, segments.date, segments.hour, segments.day_of_week, ${RAW} FROM campaign WHERE segments.date = '${today}'`).forEach(r => { if (parseInt(r.segments.hour, 10) >= hour) return; const id = r.campaign.id; names[id] = r.campaign.name; cur[id] = addM_(cur[id] || zeroM_(), rawOf_(r)); });
query_(`SELECT campaign.id, campaign.name, segments.date, segments.hour, segments.day_of_week, ${RAW} FROM campaign WHERE segments.day_of_week = '${dow}' AND segments.date BETWEEN '${start}' AND '${yesterday}'`).forEach(r => {
if (exceptions.has(r.segments.date) || parseInt(r.segments.hour, 10) >= hour) return; const id = r.campaign.id; names[id] = r.campaign.name; dates.add(r.segments.date); past[id] = addM_(past[id] || zeroM_(), rawOf_(r)); });
const weeks = Math.max(1, dates.size), ids = new Set([...Object.keys(cur), ...Object.keys(past)]), rows = [];
ids.forEach(id => { const c = derive_(cur[id] || zeroM_())[key], p = derive_(past[id] || zeroM_())[key] / weeks; if (isFinite(c) || isFinite(p)) rows.push({ name: names[id] || id, c: c || 0, p: p || 0 }); });
rows.sort((a, b) => Math.abs(b.c - b.p) - Math.abs(a.c - a.p));
return rows.slice(0, topN).map(r => `${r.name}: ${fmtNum_(r.c)} vs ~${fmtNum_(r.p)}`).join(' | ');
} catch (e) { Logger.log('Campaign hints skipped: ' + e); return ''; }
}
/* ---- sheets ---- */
function readState_(ss, tz) {
const sh = ss.getSheetByName(TECH.state), st = {};
if (sh.getLastRow() < 2) return st;
sh.getRange(2, 1, sh.getLastRow() - 1, 3).getValues().forEach(([m, d, s]) => { if (m) st[m] = { date: dateStrOf_(d, tz), severity: String(s) }; });
return st;
}
function writeState_(ss, state) {
replaceRows_(ss.getSheetByName(TECH.state), ['metric', 'date', 'severity'], Object.keys(state).map(k => [k, state[k].date, state[k].severity]), ['@', '@', '@']);
}
function snapshotHeader_() { return ['timestamp', 'date', 'hour', 'weekday', 'account'].concat(METRICS.reduce((a, m) => a.concat([m.key + '_obs', m.key + '_exp', m.key + '_z', m.key + '_sev']), [])); }
function writeSnapshot_(ss, now, today, hour, dow, cid, evals) {
const sh = ensureSchema_(ss, TECH.snapshot, snapshotHeader_()), head = snapshotHeader_();
ensureSize_(sh, 2, head.length);
if (sh.getLastRow() === 0) { styleHeader_(sh.getRange(1, 1, 1, head.length).setValues([head])); sh.setFrozenRows(1); }
const row = [now, today, hour, dow, cid]; evals.forEach(e => row.push(nz_(e.observed), nz_(e.expected), nz_(e.z), e.severity));
appendRows_(sh, [row], [null, '@', '0', '@', '@']);
const keep = Math.max(1, cfg_keep_()), extra = sh.getLastRow() - 1 - keep;
if (extra > 0) sh.deleteRows(2, extra); // oldest rows first; baseline needs only the last `weeks`
}
function cfg_keep_() { return (CONFIG.snapshot_keep_weeks || 26) * 7 * 24; }
/* Observed values at this weekday+hour on past dates → baseline that already includes conversion lag. */
function readSnapshotBaseline_(ss, dow, hour, tz, exceptions) {
const sh = ss.getSheetByName(TECH.snapshot), out = {};
if (sh.getLastRow() < 2) return out;
const head = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0].map(String);
const iD = head.indexOf('date'), iH = head.indexOf('hour'), iW = head.indexOf('weekday');
const seen = new Set();
sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues().forEach(r => {
const d = dateStrOf_(r[iD], tz);
if (String(r[iW]) !== dow || parseInt(r[iH], 10) !== hour || exceptions.has(d) || seen.has(d)) return;
seen.add(d);
METRICS.forEach(m => { const i = head.indexOf(m.key + '_obs'); if (i >= 0 && r[i] !== '' && isFinite(r[i])) (out[m.key] = out[m.key] || []).push(Number(r[i])); });
});
return out;
}
function writeBaseline_(ss, dow, hour, evals) {
replaceRows_(ss.getSheetByName(TECH.baseline), ['weekday', 'hour', 'metric', 'median', 'mad', 'samples', 'basis', 'updated'],
evals.map(e => [dow, hour, e.m.key, nz_(e.expected), nz_(e.mad), e.n, e.basis, new Date()]), ['@', '0', '@']);
}
function alertsHead_() {
return [t_('col.timestamp'), t_('col.date'), t_('col.hour'), t_('col.account'), t_('col.metric'), t_('col.status'), t_('col.severity'),
t_('col.observed'), t_('col.expected'), t_('col.deviation'), 'z', t_('col.samples'), t_('col.currency'), t_('col.hints')]; // 14 columns — dashboard contract, do not widen
}
function appendAlerts_(ss, now, today, hour, cid, currency, events) {
const sh = ss.getSheetByName(t_('tab.alerts'));
ensureTitledHeader_(sh, t_('title'), alertsHead_());
/* status / severity are written as API-style codes (NEW / CRITICAL …) in every language so dashboards can filter on them; labels are localised in e-mail and Slack only. */
appendRows_(sh, events.map(e => [now, today, hour, cid, t_('m.' + e.m.key), e.status, e.severity, nz_(e.observed), nz_(e.expected), nz_(e.pct), nz_(e.z), e.n, currency, e.hints || '']),
[null, '@', '0', '@', null, null, null, null, null, '0.0%', '0.0']);
const r0 = sh.getLastRow() - events.length + 1;
events.forEach((e, i) => { if (e.delivered) sh.getRange(r0 + i, 6).setNote(t_('col.delivered') + ': ' + e.delivered); }); // delivery info as a cell note: schema stays 14 columns
}
function notify_(cfg, cid, currency, today, hour, events, url) {
const worst = events.reduce((w, e) => rank_(e.severity) > rank_(w) ? e.severity : w, 'OK');
const allRecovered = events.every(e => e.status === 'RECOVERED');
const subject = allRecovered ? t_('mail.subject_recovered', { cid: cid, date: today, hour: hour })
: (worst === 'CRITICAL' ? '🔴 ' : '🟠 ') + t_('mail.subject', { cid: cid, sev: t_('sev.' + worst), date: today, hour: hour });
const line = e => `${t_('status.' + e.status)} · ${t_('m.' + e.m.key)}: ${fmtNum_(e.observed)} vs ${fmtNum_(e.expected)} ${isFinite(e.pct) ? (e.pct >= 0 ? '▲' : '▼') + ' ' + fmtPct_(Math.abs(e.pct), 0) : ''} (z ${isFinite(e.z) ? e.z.toFixed(1) : '–'}, ${t_('sev.' + e.severity)})` + (e.hints ? `\n ↳ ${e.hints}` : '');
const rows = events.map(e => '<tr><td style="padding:6px 10px;font-weight:bold;color:' + (e.status === 'RECOVERED' ? '#15803D' : e.severity === 'CRITICAL' ? '#B91C1C' : '#D97706') + '">' + h_(t_('status.' + e.status)) +
'</td><td style="padding:6px 10px">' + h_(t_('m.' + e.m.key)) + '</td><td style="padding:6px 10px;text-align:right">' + fmtNum_(e.observed) + '</td><td style="padding:6px 10px;text-align:right;color:#64748B">' + fmtNum_(e.expected) +
'</td><td style="padding:6px 10px;text-align:right">' + (isFinite(e.pct) ? fmtPct_(e.pct, 0) : '') + '</td></tr>' + (e.hints ? '<tr><td></td><td colspan="4" style="padding:0 10px 8px;color:#64748B;font-size:11px">↳ ' + h_(e.hints) + '</td></tr>' : '')).join('');
const body = '<table style="border-collapse:collapse;width:100%">' + rows + '</table>';
const html = htmlShell_(t_('title') + ' · ' + cid, t_('mail.sub', { date: today, hour: hour, currency: currency }), body, url);
const a = sendMail_(cfg.email, subject, html, events.map(line).join('\n'));
const b = sendSlack_(cfg.slack_webhook, `*${slackText_(subject)}*\n` + events.map(e => '• ' + slackText_(line(e))).join('\n'));
const wanted = [cfg.email ? 'mail' : null, cfg.slack_webhook ? 'slack' : null].filter(Boolean), got = [a ? 'mail' : null, b ? 'slack' : null].filter(Boolean);
if (got.length < wanted.length) Logger.log('Partial delivery: ' + got.join('+') + ' of ' + wanted.join('+'));
events.forEach(e => e.delivered = wanted.length ? got.join('+') + (got.length < wanted.length ? ' (' + wanted.filter(w => got.indexOf(w) < 0).join('+') + ' failed)' : '') : 'none configured');
return a || b || !wanted.length;
}