// quality-lens — 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 = "quality-lens";
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":"Quality Lens — keyword quality score","tab.summary":"Summary","tab.keywords":"Keywords","tab.changes":"Changes","alias.summary":["Özet","Zusammenfassung"],"alias.keywords":["Anahtar kelimeler","AnahtarKelimeler"],"alias.changes":["Değişimler","Änderungen","Degisimler","Aenderungen"],"col.keyword":"Keyword","col.keywords":"Keywords","col.match_type":"Match type","col.qs":"QS","col.prev_qs":"Previous QS","col.delta":"Δ","col.exp_ctr":"Expected CTR","col.ad_relevance":"Ad relevance","col.lp_exp":"Landing page exp.","comp.ctr":"Expected CTR","comp.rel":"Ad relevance","comp.lpe":"Landing page experience","sum.window":"Metrics window","sum.scored":"Keywords with a QS","sum.weighted_qs":"Impression-weighted QS","sum.simple_qs":"Simple average QS","sum.low_cost":"Cost on QS ≤ 4","sum.low_share":"Share of cost on QS ≤ 4","sum.changes":"QS changes ≥ {n} since last run","sum.by_qs":"By QS","sum.no_qs":"no QS","sum.worst":"Most expensive low-quality keywords (QS ≤ 4)","sum.weakest":"Weakest component","mail.subject":"Quality Lens · {cid} · QS {qs} · {n} moves","mail.drops":"Drops","mail.gains":"Gains"};
/* ===================== 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 Quality Lens
* @overview Daily keyword Quality Score tracker with components, a change log against the previous snapshot,
* an idempotent daily history row, and a summary of where low quality costs the most.
* Schedule: Daily, 04:00 account time or later.
*/
const CONFIG = {
spreadsheet_url: 'YOUR_SPREADSHEET_URL', email: '', account_id: '',
days: 30, enabled_only: true, min_impressions: 1, campaign_contains: '',
change_threshold: 2, keep_history_days: 400, max_rows_written: 5000
};
const RATINGS = ['ABOVE_AVERAGE', 'AVERAGE', 'BELOW_AVERAGE', 'UNKNOWN'];
const COMPONENTS = [{ key: 'ctr', field: 'searchPredictedCtr' }, { key: 'rel', field: 'creativeQualityScore' }, { key: 'lpe', field: 'postClickQualityScore' }];
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.summary'), aliases: T['alias.summary'] }, { name: t_('tab.keywords'), aliases: T['alias.keywords'] }, { name: t_('tab.changes'), aliases: T['alias.changes'] },
{ name: TECH.history, aliases: T['alias.history'] }, { name: TECH.snapshot, aliases: T['alias.snapshot'] }]);
const today = todayStr_(tz), win = window_(CONFIG.days, 0, tz);
const rows = fetchKeywords_(win);
const prev = readSnapshot_(ss);
const agg = aggregate_(rows), changes = detectChanges_(rows, prev, CONFIG.change_threshold);
const worst = rows.filter(r => r.qs !== null && r.qs <= 4).sort((a, b) => b.m.cost - a.m.cost).slice(0, 25);
writeKeywords_(ss, rows);
writeSnapshot_(ss, rows, today);
writeHistory_(ss, today, cid, rows.length, agg, tz);
table_(ss.getSheetByName(t_('tab.changes')), 1, ['date', 'campaign', 'ad_group', 'keyword', 'match_type', 'prev_qs', 'qs', 'delta', 'exp_ctr', 'ad_relevance', 'lp_exp'].map(k => t_('col.' + k)).concat([t_('m.impressions'), t_('m.cost')]),
changes.map(c => [today, c.campaign, c.adGroup, c.text, c.match, c.prevQs, c.qs, c.delta, c.ctr, c.rel, c.lpe, c.m.impressions, c.m.cost]), ['@', '@', '@', '@', '@', '0', '0', '+0;-0;0', '@', '@', '@', '#,##0', '#,##0.00']);
writeSummary_(ss, cid, currency, today, win, rows, agg, changes, worst);
notify_(cid, currency, today, rows.length, agg, changes, worst, ss.getUrl());
seal_(ss, t_('tab.summary'));
Logger.log(`Done: weighted QS ${agg.weightedQs.toFixed(2)}, ${changes.length} changes ≥ ${CONFIG.change_threshold}.`);
}
function fetchKeywords_(win) {
const st = CONFIG.enabled_only ? "= 'ENABLED'" : "IN ('ENABLED','PAUSED')";
let w = `campaign.status ${st} AND ad_group.status ${st} AND ad_group_criterion.status ${st}`;
if (CONFIG.campaign_contains) w += ` AND campaign.name LIKE '%${escLike_(CONFIG.campaign_contains)}%'`;
return query_(`SELECT campaign.name, ad_group.id, ad_group.name, ad_group_criterion.criterion_id, ad_group_criterion.keyword.text, ad_group_criterion.keyword.match_type,
ad_group_criterion.quality_info.quality_score, ad_group_criterion.quality_info.search_predicted_ctr, ad_group_criterion.quality_info.creative_quality_score, ad_group_criterion.quality_info.post_click_quality_score,
metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions, metrics.conversions_value
FROM keyword_view WHERE segments.date BETWEEN '${win.start}' AND '${win.end}' AND ${w} AND metrics.impressions >= ${CONFIG.min_impressions}`)
.map(r => { const c = r.adGroupCriterion || {}, q = c.qualityInfo || {}, qs = parseInt(q.qualityScore, 10);
const rating = v => RATINGS.indexOf(String(v)) >= 0 ? String(v) : 'UNKNOWN';
return { key: `ag${r.adGroup.id}_cr${c.criterionId}`, campaign: r.campaign.name, adGroup: r.adGroup.name, text: (c.keyword && c.keyword.text) || '', match: (c.keyword && c.keyword.matchType) || '',
qs: isFinite(qs) ? qs : null, ctr: rating(q.searchPredictedCtr), rel: rating(q.creativeQualityScore), lpe: rating(q.postClickQualityScore), m: derive_(rawOf_(r)) }; });
}
function aggregate_(rows) {
const z = () => Object.assign({ keywords: 0 }, zeroM_()), add = (a, r) => { a.keywords++; Object.assign(a, addM_(a, r.m)); return a; };
const byQs = {}; for (let i = 1; i <= 10; i++) byQs[i] = z(); byQs['n/a'] = z();
const byComp = {}; COMPONENTS.forEach(c => { byComp[c.key] = {}; RATINGS.forEach(r => byComp[c.key][r] = z()); });
let wSum = 0, wImpr = 0, scored = 0, qsSum = 0;
rows.forEach(r => { const b = byQs[r.qs === null ? 'n/a' : r.qs]; add(b, r); COMPONENTS.forEach(c => add(byComp[c.key][r[c.key]], r));
if (r.qs !== null) { wSum += r.qs * r.m.impressions; wImpr += r.m.impressions; scored++; qsSum += r.qs; } });
const total = rows.reduce((a, r) => add(a, r), z());
return { byQs, byComp, total, scored, weightedQs: wImpr ? wSum / wImpr : 0, simpleQs: scored ? qsSum / scored : 0, lowCost: rows.filter(r => r.qs !== null && r.qs <= 4).reduce((s, r) => s + r.m.cost, 0) };
}
function detectChanges_(rows, prev, thr) {
const out = [];
rows.forEach(r => { if (r.qs === null) return; const p = prev[r.key]; if (p === undefined || p === null) return; const d = r.qs - p; if (Math.abs(d) >= thr) out.push(Object.assign({ prevQs: p, delta: d }, r)); });
return out.sort((a, b) => a.delta - b.delta || b.m.cost - a.m.cost);
}
function writeKeywords_(ss, rows) {
const head = ['campaign', 'ad_group', 'keyword', 'match_type', 'qs', 'exp_ctr', 'ad_relevance', 'lp_exp'].map(k => t_('col.' + k)).concat(mHead_());
const data = rows.slice().sort((a, b) => b.m.cost - a.m.cost).map(r => [r.campaign, r.adGroup, r.text, r.match, r.qs === null ? '' : r.qs, r.ctr, r.rel, r.lpe].concat(mRow_(r.m)));
const sh = ss.getSheetByName(t_('tab.keywords'));
const n = table_(sh, 1, head, data, ['@', '@', '@', '@', '0', '@', '@', '@'].concat(mFmt_()), CONFIG.max_rows_written);
sh.clearConditionalFormatRules();
if (n) { const rng = sh.getRange(2, 6, n, 3), qsr = sh.getRange(2, 5, n, 1);
sh.setConditionalFormatRules([
SpreadsheetApp.newConditionalFormatRule().whenTextEqualTo('BELOW_AVERAGE').setBackground('#FDE2E1').setRanges([rng]).build(),
SpreadsheetApp.newConditionalFormatRule().whenTextEqualTo('ABOVE_AVERAGE').setBackground('#DCF5E6').setRanges([rng]).build(),
SpreadsheetApp.newConditionalFormatRule().whenNumberLessThanOrEqualTo(4).setBackground('#FDE2E1').setRanges([qsr]).build(),
SpreadsheetApp.newConditionalFormatRule().whenNumberGreaterThanOrEqualTo(8).setBackground('#DCF5E6').setRanges([qsr]).build()]); }
}
function readSnapshot_(ss) {
const sh = ss.getSheetByName(TECH.snapshot), out = {};
if (sh.getLastRow() < 2) return out;
sh.getRange(2, 1, sh.getLastRow() - 1, 2).getValues().forEach(([k, q]) => { if (!k) return; const key = String(k).replace(/^(\d+):(\d+)$/, 'ag$1_cr$2'); out[key] = q === '' ? null : parseInt(q, 10); }); // v1 keys 'ag:cr' still compare
return out;
}
/* New rows first, then the leftover is cleared — a failed write never destroys the previous snapshot. */
function writeSnapshot_(ss, rows, today) {
const sh = ss.getSheetByName(TECH.snapshot);
replaceRows_(sh, ['key', 'qs', 'date'], rows.map(r => [r.key, r.qs === null ? '' : r.qs, today]), ['@', '0', '@']);
sh.hideSheet();
}
function writeHistory_(ss, today, cid, n, agg, tz) {
const sh = ss.getSheetByName(TECH.history);
const head = ['date', 'account', 'keywords', 'scored', 'weighted_qs', 'simple_qs', 'cost_qs_le4', 'total_cost'].concat([1, 2, 3, 4, 5, 6, 7, 8, 9, 10].map(i => 'impr_qs_' + i))
.concat(COMPONENTS.reduce((a, c) => a.concat(['ABOVE_AVERAGE', 'AVERAGE', 'BELOW_AVERAGE'].map(r => c.key + '_' + r.toLowerCase())), []));
const row = [today, cid, n, agg.scored, agg.weightedQs, agg.simpleQs, agg.lowCost, agg.total.cost].concat([1, 2, 3, 4, 5, 6, 7, 8, 9, 10].map(i => agg.byQs[i].impressions))
.concat(COMPONENTS.reduce((a, c) => a.concat(['ABOVE_AVERAGE', 'AVERAGE', 'BELOW_AVERAGE'].map(r => agg.byComp[c.key][r].impressions)), []));
upsertRow_(sh, head, row, 2, ['@', '@'], tz);
const extra = sh.getLastRow() - 1 - CONFIG.keep_history_days;
if (extra > 0) sh.deleteRows(2, extra);
}
function writeSummary_(ss, cid, currency, today, win, rows, agg, changes, worst) {
const sh = ss.getSheetByName(t_('tab.summary')), t = agg.total;
sh.getRange(2, 1, Math.max(1, sh.getMaxRows() - 1), Math.max(1, sh.getMaxColumns())).clear();
sh.getRange('A1').setValue(t_('title')).setFontWeight('bold').setFontSize(13);
const out = [[t_('col.account'), cid], [t_('col.date'), today], [t_('sum.window'), win.label], [t_('col.currency'), currency], [t_('col.keywords'), rows.length], [t_('sum.scored'), agg.scored],
[t_('sum.weighted_qs'), agg.weightedQs], [t_('sum.simple_qs'), agg.simpleQs], [t_('sum.low_cost'), agg.lowCost], [t_('sum.low_share'), t.cost ? agg.lowCost / t.cost : 0], [t_('sum.changes', { n: CONFIG.change_threshold }), changes.length],
[], [t_('sum.by_qs'), t_('m.impressions'), t_('m.clicks'), t_('m.cost'), t_('m.conversions')]];
for (let i = 1; i <= 10; i++) { const b = agg.byQs[i]; out.push([String(i), b.impressions, b.clicks, b.cost, b.conversions]); }
const na = agg.byQs['n/a']; out.push([t_('sum.no_qs'), na.impressions, na.clicks, na.cost, na.conversions]);
COMPONENTS.forEach(c => { out.push([], [t_('comp.' + c.key), t_('col.keywords'), t_('m.impressions'), t_('m.cost'), t_('m.conversions')]); RATINGS.forEach(r => { const b = agg.byComp[c.key][r]; out.push([r, b.keywords, b.impressions, b.cost, b.conversions]); }); });
out.push([], [t_('sum.worst'), 'QS', t_('m.cost'), t_('m.conversions'), t_('sum.weakest')]);
worst.forEach(w => out.push([`${w.text} (${w.campaign})`, w.qs, w.m.cost, w.m.conversions, COMPONENTS.filter(c => w[c.key] === 'BELOW_AVERAGE').map(c => t_('comp.' + c.key)).join(', ') || '—']));
sh.getRange(3, 1, out.length, 1).setNumberFormat('@'); sh.getRange(4, 2).setNumberFormat('@'); sh.getRange(5, 2).setNumberFormat('@');
sh.getRange(3, 1, out.length, 5).setValues(out.map(r => { while (r.length < 5) r.push(''); return r; }));
sh.getRange(9, 2, 2, 1).setNumberFormat('0.00'); sh.getRange(11, 2).setNumberFormat('#,##0.00'); sh.getRange(12, 2).setNumberFormat('0.0%');
out.forEach((r, i) => { if (r.length && [t_('sum.by_qs'), t_('sum.worst')].includes(r[0]) || COMPONENTS.some(c => r[0] === t_('comp.' + c.key))) sh.getRange(3 + i, 1, 1, 5).setFontWeight('bold').setBackground('#F1F5F9'); });
sh.setColumnWidths(1, 1, 320); sh.setColumnWidths(2, 4, 110);
}
function notify_(cid, currency, today, n, agg, changes, worst, url) {
if (!CONFIG.email) return;
const drops = changes.filter(c => c.delta < 0).slice(0, 10), gains = changes.filter(c => c.delta > 0).slice(-10).reverse();
const li = c => '<li>' + h_(c.text) + ' <span style="color:#64748B">(' + h_(c.campaign) + ')</span>: ' + c.prevQs + ' → <b>' + c.qs + '</b></li>';
const body = kpiTable_([[t_('sum.weighted_qs'), agg.weightedQs.toFixed(2), '', ''], [t_('sum.low_cost'), fmtNum_(agg.lowCost) + ' (' + fmtPct_(agg.total.cost ? agg.lowCost / agg.total.cost : 0, 0) + ')', '', ''], [t_('sum.changes', { n: CONFIG.change_threshold }), String(changes.length), '', '']]) +
'<div style="padding:12px 16px 4px;font-weight:bold;color:#B91C1C">' + h_(t_('mail.drops')) + '</div><ul style="margin:0 16px 8px 32px;padding:0">' + (drops.map(li).join('') || '<li>—</li>') + '</ul>' +
'<div style="padding:12px 16px 4px;font-weight:bold;color:#15803D">' + h_(t_('mail.gains')) + '</div><ul style="margin:0 16px 8px 32px;padding:0">' + (gains.map(li).join('') || '<li>—</li>') + '</ul>' +
'<div style="padding:12px 16px 4px;font-weight:bold;color:#0F766E">' + h_(t_('sum.worst')) + '</div><ul style="margin:0 16px 8px 32px;padding:0">' + (worst.slice(0, 5).map(w => '<li>' + h_(w.text) + ' — QS ' + w.qs + ', ' + fmtNum_(w.m.cost) + '</li>').join('') || '<li>—</li>') + '</ul>';
const subject = t_('mail.subject', { cid: cid, qs: agg.weightedQs.toFixed(2), n: changes.length });
sendMail_(CONFIG.email, subject, htmlShell_(t_('title') + ' · ' + cid, `${today} · ${n} ${t_('col.keywords')} · ${currency}`, body, url), subject + '\n' + url);
}