// creative-lens — DE 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 = "creative-lens";
const L = {"lang":"de","locale":"de-DE","text_lang":"de","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":"Dieser Zeitraum","kpi.prev":"Vorher","yes":"ja","no":"nein","mail.full_report":"Vollständiger Bericht","mail.open_report":"Bericht öffnen","note.no_rows":"Keine Zeilen.","note.truncated":"{shown} von {total} Zeilen angezeigt (max_rows_written).","note.no_campaigns":"Keine Kampagnen mit Impressionen entsprechen den aktuellen Filtern.","settings.key":"Schlüssel","settings.value":"Wert","settings.notes":"Hinweise","settings.script":"Script, dem dieses Sheet gehört (nicht ändern)","settings.account":"Google Ads-Konto, dem dieses Sheet gehört (nicht ändern)","note.truncated_queries":"⚠ {n} Abfrage(n) am Leselimit — Ergebnis unvollständig; Zeitraum verkleinern","note.account_neg_failed":"⚠ Negativs auf Kontoebene nicht lesbar:","note.skipped_time":"übersprungen — Zeitbudget","note.data_error":"Datenfehler:","note.failed":"FEHLGESCHLAGEN —","note.no_final_url":"(keine finale URL)","m.impressions":"Impr.","m.clicks":"Klicks","m.ctr":"CTR","m.cost":"Kosten","m.cpc":"Ø CPC","m.conversions":"Conv.","m.value":"Conv.-Wert","m.cr":"Conv.-Rate","m.cpa":"Kosten/Conv.","m.roas":"ROAS","m.aov":"Ø Bestellwert","m.vpc":"Wert / Klick","col.account":"Konto","col.period":"Zeitraum","col.currency":"Währung","col.date":"Datum","col.campaign":"Kampagne","col.ad_group":"Anzeigengruppe","col.n":"N","col.phrase":"Phrase","col.queries":"Suchanfragen","col.source":"Quelle","col.why":"Warum","title":"Creative Lens — wöchentlicher Creative-Report","tab.summary":"Zusammenfassung","tab.ads":"Anzeigen","tab.assets":"Assets","tab.headlines":"Überschriften","tab.urls":"Finale URLs","col.channel":"Kanal","col.ad_id":"Anzeigen-ID","col.ad_name":"Anzeigenname","col.type":"Typ","col.status":"Status","col.strength":"Anzeigeneffektivität","col.approval":"Freigabe","col.final_url":"Finale URL","col.prev_impressions":"Vorh. Impr.","col.prev_clicks":"Vorh. Klicks","col.prev_ctr":"Vorh. CTR","col.prev_cost":"Vorh. Kosten","col.prev_conversions":"Vorh. Conv.","col.wow_impressions":"Impr. WoW","col.wow_ctr":"CTR WoW","col.wow_cost":"Kosten WoW","col.wow_conversions":"Conv. WoW","col.field":"Feld","col.asset_text":"Asset-Text","col.label":"Leistungslabel","col.pinned":"Angepinnt","col.enabled":"Aktiv","col.ads_using":"Anzeigen damit","col.dominant_label":"Dominantes Label","col.times_pinned":"Anpinnungen","col.ads":"Anzeigen","col.assets":"Assets","col.shared_urls":"Geteilt (nicht gezählt)","col.where":"Wo","col.what":"Was","col.cost_in_period":"Kosten im Zeitraum","sum.ads":"Anzeigen mit Impressionen","sum.assets":"RSA-Assets mit Impressionen","sum.strength":"Anzeigeneffektivität","sum.labels":"Asset-Label","sum.health":"Gesundheit","sum.disapproved":"Abgelehnte Anzeigen","sum.limited":"Eingeschränkte Anzeigen","sum.poor":"Anzeigen POOR / AVERAGE","sum.low":"LOW-bewertete Assets","sum.pinned":"Angepinnte Assets","sum.top_headlines":"Top-Überschriften nach CTR (≥ {n} Impressionen)","fix.title":"Fix-Liste — diese Woche erledigen","fix.none":"Nichts zu fixen — gute Woche.","fix.disapproved":"Abgelehnte Anzeige","fix.disapproved_what":"Anzeige {id} ist abgelehnt","fix.no_conv":"Ausgaben, keine Conversions","fix.no_conv_what":"Anzeige {id}: {clicks} Klicks, {cost} ausgegeben, 0 Conv.","fix.poor":"Schwache Anzeigeneffektivität","fix.poor_what":"Anzeige {id} POOR bei {impr} Impressionen","fix.low_assets":"LOW-Assets ersetzen","fix.low_assets_what":"Anzeige {id}: {n} LOW-Assets, z. B. {ex}","fix.ctr_drop":"CTR-Einbruch WoW","fix.ctr_drop_what":"Anzeige {id}: CTR {now} vs. {prev} Vorperiode","fix.pinned_low":"Angepinnt, aber LOW","fix.pinned_low_what":"\"{text}\" angepinnt auf {pos}, aber LOW","mail.subject":"Creative Lens · {cid} · {period} · {n} Fixes","mail.sub":"{period} vs. vorherige {days} Tage · {currency}","mail.health":"{dis} abgelehnt · {poor} Anzeigen POOR/AVERAGE · {low} LOW-Assets","alias.summary":["Summary","Özet"],"alias.ads":["Ads","Reklamlar"],"alias.assets":["Öğeler","Metinler"],"alias.headlines":["Headlines","Başlıklar"],"alias.urls":["Final URLs","Son URL’ler","Son URL Listesi"],"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"]};
/* ===================== 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 Creative Lens
* @overview Weekly creative report: ad-level metrics for two periods (previous-period totals include paused ads and
* ads that stopped serving; REMOVED ads are excluded by the query), RSA asset performance labels, pinning, ad strength, approval, landing-page economics,
* a prioritised fix list, a persistent history row per period and an e-mail / Slack digest.
* Schedule: Weekly, Monday, 04:00 account time or later.
*/
const CONFIG = {
spreadsheet_url: 'YOUR_SPREADSHEET_URL',
email: '', slack_webhook: '', account_id: '',
days: 7,
campaign_filter: '', channel_types: [], // e.g. ['SEARCH']
min_impressions_for_ranking: 200,
min_clicks_no_conv: 30,
cost_no_conv_threshold: 0,
fix_list_size: 10,
max_rows_written: 20000
};
const STRENGTH = ['EXCELLENT', 'GOOD', 'AVERAGE', 'POOR', 'PENDING', 'NO_ADS', 'UNSPECIFIED'];
const LABELS = ['BEST', 'GOOD', 'LOW', 'LEARNING', 'PENDING', 'UNRATED'];
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);
const tabs = ['summary', 'ads', 'assets', 'headlines', 'urls'].map(k => ({ name: t_('tab.' + k), aliases: T['alias.' + k] }));
ensureTabs_(ss, tabs.concat([{ name: TECH.history, aliases: T['alias.history'] }]));
const cur = window_(CONFIG.days, 0, tz), prev = window_(CONFIG.days, CONFIG.days, tz), where = filter_();
const adsCur = fetchAds_(cur, where), adsPrev = fetchAds_(prev, where);
const prevByKey = {}; adsPrev.forEach(a => prevByKey[a.key] = a.m);
adsCur.forEach(a => a.p = prevByKey[a.key] || derive_(zeroM_()));
const assets = fetchAssets_(cur, where);
const s = summarize_(adsCur, adsPrev, assets);
const fixes = fixList_(adsCur, assets);
writeAds_(ss, adsCur); writeAssets_(ss, assets); writeHeadlines_(ss, assets); writeUrls_(ss, adsCur);
writeSummary_(ss, cid, currency, cur, prev, s, fixes);
upsertRow_(ss.getSheetByName(TECH.history), ['period_start', 'period_end', 'account', 'ads', 'assets', 'impressions', 'clicks', 'ctr', 'cost', 'conversions', 'value', 'cpa', 'roas',
'excellent', 'good', 'average', 'poor', 'best_assets', 'good_assets', 'low_assets', 'disapproved'],
[cur.start, cur.end, cid, s.ads, s.assets, s.tot.impressions, s.tot.clicks, nz_(s.tot.ctr), s.tot.cost, nz_(s.tot.conversions), nz_(s.tot.value), nz_(s.tot.cpa), nz_(s.tot.roas),
s.strength.EXCELLENT || 0, s.strength.GOOD || 0, s.strength.AVERAGE || 0, s.strength.POOR || 0, s.labels.BEST || 0, s.labels.GOOD || 0, s.labels.LOW || 0, s.disapproved], 3, ['@', '@', '@'], tz);
notify_(cid, currency, cur, s, fixes, ss.getUrl());
seal_(ss, t_('tab.summary'));
Logger.log(`${cur.label}: ${adsCur.length} ads, ${assets.length} assets, ${fixes.length} fixes.`);
}
function filter_() {
let w = '';
if (CONFIG.campaign_filter) w += ` AND campaign.name LIKE '%${escLike_(CONFIG.campaign_filter)}%'`;
if (CONFIG.channel_types.length) w += ` AND campaign.advertising_channel_type IN (${CONFIG.channel_types.map(t => `'${esc_(t)}'`).join(',')})`;
return w;
}
function fetchAds_(win, where) {
return query_(`SELECT campaign.id, campaign.name, campaign.advertising_channel_type, ad_group.id, ad_group.name,
ad_group_ad.ad.id, ad_group_ad.ad.name, ad_group_ad.ad.type, ad_group_ad.status, ad_group_ad.ad_strength,
ad_group_ad.policy_summary.approval_status, ad_group_ad.ad.final_urls,
metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions, metrics.conversions_value
FROM ad_group_ad WHERE segments.date BETWEEN '${win.start}' AND '${win.end}' AND metrics.impressions > 0 AND ad_group_ad.status != 'REMOVED'${where}`)
.map(r => ({
key: `${r.adGroup.id}:${r.adGroupAd.ad.id}`, campaign: r.campaign.name, channel: r.campaign.advertisingChannelType,
adGroup: r.adGroup.name, adId: String(r.adGroupAd.ad.id), adName: r.adGroupAd.ad.name || '', type: r.adGroupAd.ad.type, status: r.adGroupAd.status,
strength: r.adGroupAd.adStrength || 'UNSPECIFIED', approval: (r.adGroupAd.policySummary && r.adGroupAd.policySummary.approvalStatus) || '',
urls: (r.adGroupAd.ad.finalUrls || []).slice(), m: derive_(rawOf_(r))
}));
}
/* Asset-level rows. Conversions fall back only when the API says the field is not selectable — any other error propagates. */
function fetchAssets_(win, where) {
const base = `SELECT campaign.name, ad_group.name, ad_group_ad.ad.id, ad_group_ad_asset_view.field_type, ad_group_ad_asset_view.performance_label,
ad_group_ad_asset_view.pinned_field, ad_group_ad_asset_view.enabled, asset.id, asset.text_asset.text, metrics.impressions, metrics.clicks, metrics.cost_micros`;
const tail = ` FROM ad_group_ad_asset_view WHERE segments.date BETWEEN '${win.start}' AND '${win.end}'
AND ad_group_ad_asset_view.field_type IN ('HEADLINE','DESCRIPTION') AND metrics.impressions > 0${where}`;
let rows, hasConv = true;
try { rows = query_(base + ', metrics.conversions, metrics.conversions_value' + tail); }
catch (e) { if (!/not selectable|cannot be selected|cannot select|PROHIBITED|not supported|invalid.*metrics\.conversions/i.test(String(e))) throw e; hasConv = false; rows = query_(base + tail); Logger.log('Asset-level conversions not available: ' + e); }
return rows.map(r => {
const v = r.adGroupAdAssetView || {}, pin = String(v.pinnedField || '');
return { campaign: r.campaign.name, adGroup: r.adGroup.name, adId: String(r.adGroupAd.ad.id), assetId: String(r.asset.id),
text: (r.asset.textAsset && r.asset.textAsset.text) || '', field: v.fieldType, label: v.performanceLabel || 'UNRATED',
pinned: /^(HEADLINE|DESCRIPTION)_\d$/.test(pin) ? pin : '', enabled: v.enabled !== false, m: derive_(rawOf_(r, { noConv: !hasConv })) };
});
}
function summarize_(ads, adsPrev, assets) {
const tot = derive_(ads.reduce((s, a) => addM_(s, a.m), zeroM_())), totPrev = derive_(adsPrev.reduce((s, a) => addM_(s, a.m), zeroM_()));
const strength = {}, labels = {};
ads.forEach(a => strength[a.strength] = (strength[a.strength] || 0) + 1);
assets.forEach(a => labels[a.label] = (labels[a.label] || 0) + 1);
const rated = assets.filter(a => a.enabled && a.m.impressions >= CONFIG.min_impressions_for_ranking && isFinite(a.m.ctr)); // enabled=false = no longer in the ad's current version
return { ads: ads.length, assets: assets.length, tot, totPrev, strength, labels,
disapproved: ads.filter(a => /DISAPPROVED/.test(a.approval)).length, limited: ads.filter(a => /LIMITED/.test(a.approval)).length,
poor: (strength.POOR || 0) + (strength.AVERAGE || 0), lowAssets: assets.filter(a => a.label === 'LOW').length, pinnedAssets: assets.filter(a => a.pinned).length,
topHeadlines: rated.filter(a => a.field === 'HEADLINE').sort((x, y) => y.m.ctr - x.m.ctr).slice(0, 5) };
}
function fixList_(ads, assets) {
const out = [], where = a => `${a.campaign} › ${a.adGroup}`;
ads.filter(a => /DISAPPROVED/.test(a.approval)).forEach(a => out.push({ p: 1, type: t_('fix.disapproved'), where: where(a), what: t_('fix.disapproved_what', { id: a.adId }), cost: a.m.cost }));
ads.filter(a => a.m.cost > CONFIG.cost_no_conv_threshold && a.m.clicks >= CONFIG.min_clicks_no_conv && a.m.conversions === 0).forEach(a =>
out.push({ p: 2, type: t_('fix.no_conv'), where: where(a), what: t_('fix.no_conv_what', { id: a.adId, clicks: a.m.clicks, cost: fmtNum_(a.m.cost, 2) }), cost: a.m.cost }));
ads.filter(a => a.strength === 'POOR' && a.m.impressions >= CONFIG.min_impressions_for_ranking).forEach(a =>
out.push({ p: 3, type: t_('fix.poor'), where: where(a), what: t_('fix.poor_what', { id: a.adId, impr: fmtNum_(a.m.impressions, 0) }), cost: a.m.cost }));
const low = {};
assets.filter(a => a.enabled && a.label === 'LOW').forEach(a => { const l = low[a.adId] = low[a.adId] || { n: 0, a: a, ex: [] }; l.n++; if (l.ex.length < 2) l.ex.push(a.text); });
Object.keys(low).forEach(id => { const l = low[id]; if (l.n >= 2) out.push({ p: 4, type: t_('fix.low_assets'), where: where(l.a), what: t_('fix.low_assets_what', { id: id, n: l.n, ex: l.ex.join(' / ') }), cost: 0 }); });
ads.filter(a => a.p.impressions > 0 && a.m.impressions >= CONFIG.min_impressions_for_ranking && a.p.ctr > 0 && a.m.ctr < a.p.ctr * 0.7).forEach(a =>
out.push({ p: 5, type: t_('fix.ctr_drop'), where: where(a), what: t_('fix.ctr_drop_what', { id: a.adId, now: fmtPct_(a.m.ctr), prev: fmtPct_(a.p.ctr) }), cost: a.m.cost }));
assets.filter(a => a.enabled && a.pinned && a.label === 'LOW').forEach(a => out.push({ p: 6, type: t_('fix.pinned_low'), where: where(a), what: t_('fix.pinned_low_what', { text: a.text, pos: a.pinned }), cost: a.m.cost }));
return out.sort((x, y) => x.p - y.p || y.cost - x.cost).slice(0, CONFIG.fix_list_size);
}
/* ---- tabs ---- */
function writeAds_(ss, ads) {
const head = ['campaign', 'channel', 'ad_group', 'ad_id', 'ad_name', 'type', 'status', 'strength', 'approval', 'final_url'].map(k => t_('col.' + k))
.concat(mHead_()).concat(['prev_impressions', 'prev_clicks', 'prev_ctr', 'prev_cost', 'prev_conversions', 'wow_impressions', 'wow_ctr', 'wow_cost', 'wow_conversions'].map(k => t_('col.' + k)));
const rows = ads.slice().sort((x, y) => y.m.cost - x.m.cost).map(a => [a.campaign, a.channel, a.adGroup, a.adId, a.adName, a.type, a.status, a.strength, a.approval, a.urls[0] || '']
.concat(mRow_(a.m)).concat([a.p.impressions, a.p.clicks, nz_(a.p.ctr), a.p.cost, nz_(a.p.conversions), nz_(pct_(a.m.impressions, a.p.impressions)), nz_(pct_(a.m.ctr, a.p.ctr)), nz_(pct_(a.m.cost, a.p.cost)), nz_(pct_(a.m.conversions, a.p.conversions))]));
const fm = ['@', '@', '@', '@', '@', '@', '@', '@', '@', '@'].concat(mFmt_()).concat(['#,##0', '#,##0', '0.00%', '#,##0.00', '#,##0.0', '+0%;-0%;0%', '+0%;-0%;0%', '+0%;-0%;0%', '+0%;-0%;0%']);
table_(ss.getSheetByName(t_('tab.ads')), 1, head, rows, fm, CONFIG.max_rows_written);
}
function writeAssets_(ss, assets) {
const head = ['campaign', 'ad_group', 'ad_id', 'field', 'asset_text', 'label', 'pinned', 'enabled'].map(k => t_('col.' + k)).concat(mHead_());
const rows = assets.slice().sort((x, y) => y.m.impressions - x.m.impressions).map(a => [a.campaign, a.adGroup, a.adId, a.field, a.text, a.label, a.pinned, a.enabled ? t_('yes') : t_('no')].concat(mRow_(a.m)));
table_(ss.getSheetByName(t_('tab.assets')), 1, head, rows, ['@', '@', '@', '@', '@', '@', '@', '@'].concat(mFmt_()), CONFIG.max_rows_written);
}
function writeHeadlines_(ss, assets) {
const by = {};
assets.forEach(a => { const k = a.field + '|' + a.text.trim().toLowerCase(); const t = by[k] = by[k] || { field: a.field, text: a.text, ads: new Set(), labels: {}, pinned: 0, m: zeroM_() };
t.ads.add(a.adId); t.m = addM_(t.m, a.m); t.labels[a.label] = (t.labels[a.label] || 0) + 1; if (a.pinned) t.pinned++; });
const rows = Object.values(by).map(t => { derive_(t.m); return t; }).sort((x, y) => y.m.impressions - x.m.impressions)
.map(t => [t.field, t.text, t.ads.size, Object.keys(t.labels).sort((x, y) => t.labels[y] - t.labels[x])[0], t.pinned].concat(mRow_(t.m)));
table_(ss.getSheetByName(t_('tab.headlines')), 1, ['field', 'asset_text', 'ads_using', 'dominant_label', 'times_pinned'].map(k => t_('col.' + k)).concat(mHead_()), rows, ['@', '@', '#,##0', '@', '#,##0'].concat(mFmt_()), CONFIG.max_rows_written);
}
/* Cost is attributed to the first final URL; additional URLs are listed but marked shared to avoid double counting. */
function writeUrls_(ss, ads) {
const by = {};
ads.forEach(a => { const urls = a.urls.length ? a.urls : [t_('note.no_final_url')]; urls.forEach((u, i) => { const c = u.split('?')[0]; const e = by[c] = by[c] || { url: c, ads: 0, shared: 0, m: zeroM_() }; e.ads++; if (i === 0) e.m = addM_(e.m, a.m); else e.shared++; }); });
const rows = Object.values(by).map(e => { derive_(e.m); return e; }).sort((x, y) => y.m.cost - x.m.cost).map(e => [e.url, e.ads, e.shared].concat(mRow_(e.m)));
table_(ss.getSheetByName(t_('tab.urls')), 1, ['final_url', 'ads', 'shared_urls'].map(k => t_('col.' + k)).concat(mHead_()), rows, ['@', '#,##0', '#,##0'].concat(mFmt_()), CONFIG.max_rows_written);
}
function writeSummary_(ss, cid, currency, cur, prev, s, fixes) {
const sh = ss.getSheetByName(t_('tab.summary')), t = s.tot, p = s.totPrev;
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 K = (k, a, b, inv, fmt) => ({ r: [t_(k), a, b, nz_(pct_(a, b))], fmt: fmt, inv: inv });
const kpi = [K('m.impressions', t.impressions, p.impressions, false, '#,##0'), K('m.clicks', t.clicks, p.clicks, false, '#,##0'), K('m.ctr', nz_(t.ctr), nz_(p.ctr), false, '0.00%'),
K('m.cost', t.cost, p.cost, false, '#,##0.00'), K('m.conversions', nz_(t.conversions), nz_(p.conversions), false, '#,##0.0'), K('m.value', nz_(t.value), nz_(p.value), false, '#,##0.00'),
K('m.cpa', nz_(t.cpa), nz_(p.cpa), true, '#,##0.00'), K('m.roas', nz_(t.roas), nz_(p.roas), false, '0.00')];
const rows = [[t_('col.account'), cid, '', ''], [t_('col.period'), cur.label, t_('kpi.prev'), prev.label], [t_('col.currency'), currency, '', ''], [],
[t_('kpi'), t_('kpi.now'), t_('kpi.prev'), 'Δ'], [t_('sum.ads'), s.ads, '', ''], [t_('sum.assets'), s.assets, '', '']].concat(kpi.map(k => k.r))
.concat([[], [t_('sum.strength'), t_('col.ads'), '', '']]).concat(STRENGTH.map(k => [k, s.strength[k] || 0, '', '']))
.concat([[], [t_('sum.labels'), t_('col.assets'), '', '']]).concat(LABELS.map(k => [k, s.labels[k] || 0, '', '']))
.concat([[], [t_('sum.health'), '', '', ''], [t_('sum.disapproved'), s.disapproved, '', ''], [t_('sum.limited'), s.limited, '', ''], [t_('sum.poor'), s.poor, '', ''], [t_('sum.low'), s.lowAssets, '', ''], [t_('sum.pinned'), s.pinnedAssets, '', '']]);
sh.getRange(3, 1, rows.length, 4).setValues(rows.map(r => r.length ? r : ['', '', '', '']));
const kpiTop = 3 + 7;
kpi.forEach((k, i) => { sh.getRange(kpiTop + i, 2, 1, 2).setNumberFormat(k.fmt); sh.getRange(kpiTop + i, 4).setNumberFormat('+0.0%;-0.0%;0.0%'); });
rows.forEach((r, i) => { if (r.length && [t_('kpi'), t_('sum.strength'), t_('sum.labels'), t_('sum.health')].includes(r[0])) sh.getRange(3 + i, 1, 1, 4).setFontWeight('bold').setBackground('#F1F5F9'); });
let top = 3 + rows.length + 1;
sh.getRange(top, 1).setValue(t_('fix.title')).setFontWeight('bold').setFontColor('#0F766E');
styleHeader_(sh.getRange(top + 1, 1, 1, 5).setValues([['#', t_('col.type'), t_('col.where'), t_('col.what'), t_('col.cost_in_period')]]));
if (fixes.length) { sh.getRange(top + 2, 1, fixes.length, 5).setValues(fixes.map((f, i) => [i + 1, f.type, f.where, f.what, f.cost])); sh.getRange(top + 2, 5, fixes.length, 1).setNumberFormat('#,##0.00'); }
else sh.getRange(top + 2, 1).setValue(t_('fix.none'));
top += 3 + Math.max(fixes.length, 1);
sh.getRange(top, 1).setValue(t_('sum.top_headlines', { n: CONFIG.min_impressions_for_ranking })).setFontWeight('bold').setFontColor('#0F766E');
styleHeader_(sh.getRange(top + 1, 1, 1, 5).setValues([[t_('col.asset_text'), t_('col.label'), t_('m.impressions'), t_('m.ctr'), t_('m.conversions')]]));
if (s.topHeadlines.length) { sh.getRange(top + 2, 1, s.topHeadlines.length, 5).setValues(s.topHeadlines.map(a => [a.text, a.label, a.m.impressions, a.m.ctr, nz_(a.m.conversions)])); sh.getRange(top + 2, 4, s.topHeadlines.length, 1).setNumberFormat('0.00%'); }
sh.setColumnWidths(1, 1, 240); sh.setColumnWidths(3, 2, 260);
}
function notify_(cid, currency, cur, s, fixes, url) {
if (!CONFIG.email && !CONFIG.slack_webhook) return;
const t = s.tot, p = s.totPrev, f = v => fmtNum_(v);
const body = kpiTable_([[t_('m.impressions'), f(t.impressions), f(p.impressions), delta_(t.impressions, p.impressions)], [t_('m.clicks'), f(t.clicks), f(p.clicks), delta_(t.clicks, p.clicks)],
[t_('m.ctr'), fmtPct_(t.ctr), fmtPct_(p.ctr), delta_(t.ctr, p.ctr)], [t_('m.cost'), f(t.cost), f(p.cost), delta_(t.cost, p.cost)], [t_('m.conversions'), f(t.conversions), f(p.conversions), delta_(t.conversions, p.conversions)],
[t_('m.cpa'), f(t.cpa), f(p.cpa), delta_(t.cpa, p.cpa, true)], [t_('m.roas'), f(t.roas), f(p.roas), delta_(t.roas, p.roas)]]) +
'<div style="padding:12px 16px 4px;font-weight:bold;color:#0F766E">' + h_(t_('sum.health')) + '</div><div style="padding:0 16px">' +
h_(t_('mail.health', { dis: s.disapproved, poor: s.poor, low: s.lowAssets })) + '</div>' +
'<div style="padding:12px 16px 4px;font-weight:bold;color:#0F766E">' + h_(t_('fix.title')) + '</div><ol style="margin:0 16px 8px 32px;padding:0">' +
(fixes.map(x => '<li style="margin:3px 0"><b>' + h_(x.type) + '</b> — ' + h_(x.where) + '<br><span style="color:#64748B">' + h_(x.what) + '</span></li>').join('') || '<li>' + h_(t_('fix.none')) + '</li>') + '</ol>';
const subject = t_('mail.subject', { cid: cid, period: cur.label, n: fixes.length });
sendMail_(CONFIG.email, subject, htmlShell_(t_('title') + ' · ' + cid, t_('mail.sub', { period: cur.label, days: CONFIG.days, currency: currency }), body, url), subject + '\n' + url);
const S = slackText_;
sendSlack_(CONFIG.slack_webhook, `*${S(subject)}*\n${S(t_('m.ctr'))} ${fmtPct_(t.ctr)} · ${S(t_('m.cost'))} ${f(t.cost)} · ${S(t_('m.conversions'))} ${f(t.conversions)} · ${S(t_('m.cpa'))} ${f(t.cpa)}\n` +
fixes.slice(0, 5).map((x, i) => `${i + 1}. ${S(x.type)} — ${S(x.where)}: ${S(x.what)}`).join('\n') + `\n<${url}|${S(t_('mail.open_report'))}>`);
}