Shopping Zombie Products

1/**
2 * Shopping Zombie Products
3 *
4 * Analyseert producten in Shopping- en Performance Max-campagnes via
5 * shopping_performance_view. Vindt "zombies" (producten met nauwelijks impressies of
6 * klikken in de periode) en "geldverslinders" (producten met veel kosten maar zonder
7 * conversies). Schrijft beide lijsten naar een Google Sheet met een voorstel voor een
8 * custom_label-waarde die je in Merchant Center kunt gebruiken om de producten in een
9 * aparte campagne te zetten. Het script wijzigt niets in het account of in Merchant Center.
10 *
11 * Versie:     1.3.0
12 * Frequentie: Wekelijks
13 * Niveau:     Account
14 * Licentie:   MIT
15 */
16
17// ===== CONFIGURATIE =====
18
19// Google Sheet voor het rapport.
20var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/JOUW_SHEET_ID/edit';
21var SHEET_NAMES = { zombies: 'Zombies', wasters: 'Kosten zonder conversie', all: 'Alle producten' };
22
23// Periode.
24var DATE_RANGE = 'LAST_30_DAYS';
25
26// Alleen campagnes waarvan de naam deze tekst bevat. Leeg = alle Shopping/PMax-campagnes.
27var CAMPAIGN_NAME_CONTAINS = '';
28
29// Drempels.
30var THRESHOLDS = {
31  zombieMaxImpressions: 10,     // product is zombie bij hooguit dit aantal impressies ...
32  zombieMaxClicks: 0,           // ... en hooguit dit aantal klikken
33  wasterMinCost: 25,            // geldverslinder: kosten minimaal dit bedrag ...
34  wasterMaxConversions: 0,      // ... en hooguit dit aantal conversies
35  wasterMinClicks: 10,          // ... en minimaal dit aantal klikken
36  maxRowsAllProducts: 5000      // limiet voor het tabblad "Alle producten"
37};
38
39// Voorgestelde custom label-waarden (kolom "Voorstel custom_label").
40var LABEL_NAMES = {
41  zombie: 'zombie',
42  waster: 'no-conv-high-cost',
43  hero: 'hero'                  // producten met conversies en ROAS >= HERO_MIN_ROAS
44};
45var HERO_MIN_ROAS = 4;
46
47// Welk custom label-veld gebruik je hiervoor? (0 t/m 4) Wordt alleen als tekst in het rapport gebruikt.
48var CUSTOM_LABEL_INDEX = 4;
49
50// Optioneel e-mailoverzicht. Leeg = geen e-mail.
51var EMAIL_RECIPIENTS = '';
52
53// ===== EINDE CONFIGURATIE =====
54
55function main() {
56  try {
57    var account = AdsApp.currentAccount();
58    Logger.log('Zombie-analyse voor ' + account.getName() + ' | ' + DATE_RANGE);
59
60    var products = fetchProducts();
61    var ids = Object.keys(products);
62    Logger.log(ids.length + ' unieke producten met impressies gevonden.');
63
64    var zombies = [];
65    var wasters = [];
66    var all = [];
67    ids.forEach(function (id) {
68      var p = products[id];
69      p.cpa = p.conversions > 0 ? p.cost / p.conversions : null;
70      p.roas = p.cost > 0 ? p.convValue / p.cost : 0;
71      p.ctr = p.impressions > 0 ? p.clicks / p.impressions : 0;
72      p.suggestion = suggestLabel(p);
73      if (p.impressions <= THRESHOLDS.zombieMaxImpressions && p.clicks <= THRESHOLDS.zombieMaxClicks) { zombies.push(p); }
74      if (p.cost >= THRESHOLDS.wasterMinCost && p.conversions <= THRESHOLDS.wasterMaxConversions && p.clicks >= THRESHOLDS.wasterMinClicks) { wasters.push(p); }
75      all.push(p);
76    });
77    zombies.sort(function (a, b) { return a.impressions - b.impressions; });
78    wasters.sort(function (a, b) { return b.cost - a.cost; });
79    all.sort(function (a, b) { return b.cost - a.cost; });
80    Logger.log(zombies.length + ' zombies, ' + wasters.length + ' geldverslinders.');
81
82    var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
83    writeProductSheet(ss, SHEET_NAMES.zombies, zombies, 'Producten met <= ' + THRESHOLDS.zombieMaxImpressions + ' impressies en <= ' + THRESHOLDS.zombieMaxClicks + ' klikken in ' + DATE_RANGE);
84    writeProductSheet(ss, SHEET_NAMES.wasters, wasters, 'Producten met >= ' + THRESHOLDS.wasterMinCost + ' kosten, >= ' + THRESHOLDS.wasterMinClicks + ' klikken en <= ' + THRESHOLDS.wasterMaxConversions + ' conversies');
85    writeProductSheet(ss, SHEET_NAMES.all, all.slice(0, THRESHOLDS.maxRowsAllProducts), 'Alle producten, gesorteerd op kosten');
86    sendEmail(account, zombies, wasters, all.length);
87    Logger.log('Klaar: ' + SPREADSHEET_URL);
88  } catch (e) {
89    Logger.log('FOUT in shopping-zombie-products: ' + e + (e.stack ? '\n' + e.stack : ''));
90    throw e;
91  }
92}
93
94/**
95 * Haalt productprestaties op via shopping_performance_view, geaggregeerd per product-ID.
96 * Let op: producten die in de periode NUL impressies hadden komen niet in deze view voor.
97 * Die vind je door de Sheet te vergelijken met je Merchant Center-feed.
98 */
99function fetchProducts() {
100  var query =
101    'SELECT segments.product_item_id, segments.product_title, segments.product_brand, segments.product_type_l1, ' +
102    'segments.product_custom_attribute' + CUSTOM_LABEL_INDEX + ', campaign.name, campaign.advertising_channel_type, ' +
103    'metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions, metrics.conversions_value ' +
104    'FROM shopping_performance_view ' +
105    'WHERE segments.date DURING ' + DATE_RANGE + ' ' +
106    "AND campaign.status = 'ENABLED'";
107  if (CAMPAIGN_NAME_CONTAINS) {
108    query += " AND campaign.name LIKE '%" + CAMPAIGN_NAME_CONTAINS.replace(/'/g, "\\'") + "%'";
109  }
110
111  var products = {};
112  var rows = AdsApp.report(query).rows();
113  while (rows.hasNext()) {
114    var row = rows.next();
115    var id = String(row['segments.product_item_id'] || '').toLowerCase();
116    if (!id) { continue; }
117    if (!products[id]) {
118      products[id] = {
119        id: id,
120        title: row['segments.product_title'] || '',
121        brand: row['segments.product_brand'] || '',
122        type: row['segments.product_type_l1'] || '',
123        currentLabel: row['segments.product_custom_attribute' + CUSTOM_LABEL_INDEX] || '',
124        campaigns: {},
125        channels: {},
126        impressions: 0, clicks: 0, cost: 0, conversions: 0, convValue: 0
127      };
128    }
129    var p = products[id];
130    p.impressions += Number(row['metrics.impressions']);
131    p.clicks += Number(row['metrics.clicks']);
132    p.cost += Number(row['metrics.cost_micros']) / 1000000;
133    p.conversions += Number(row['metrics.conversions']);
134    p.convValue += Number(row['metrics.conversions_value']);
135    p.campaigns[row['campaign.name']] = true;
136    p.channels[row['campaign.advertising_channel_type']] = true;
137  }
138  return products;
139}
140
141function suggestLabel(p) {
142  if (p.impressions <= THRESHOLDS.zombieMaxImpressions && p.clicks <= THRESHOLDS.zombieMaxClicks) { return LABEL_NAMES.zombie; }
143  if (p.cost >= THRESHOLDS.wasterMinCost && p.conversions <= THRESHOLDS.wasterMaxConversions && p.clicks >= THRESHOLDS.wasterMinClicks) { return LABEL_NAMES.waster; }
144  if (p.conversions > 0 && p.roas >= HERO_MIN_ROAS) { return LABEL_NAMES.hero; }
145  return '';
146}
147
148function writeProductSheet(ss, name, list, description) {
149  var sheet = ss.getSheetByName(name) || ss.insertSheet(name);
150  sheet.clearContents();
151  var header = ['Product-ID', 'Titel', 'Merk', 'Producttype', 'Huidig custom_label' + CUSTOM_LABEL_INDEX, 'Campagnes', 'Kanaal',
152    'Impressies', 'Klikken', 'CTR', 'Kosten', 'Conversies', 'CPA', 'Conv. waarde', 'ROAS', 'Voorstel custom_label' + CUSTOM_LABEL_INDEX];
153  var values = [header].concat(list.map(function (p) {
154    return [p.id, p.title, p.brand, p.type, p.currentLabel, Object.keys(p.campaigns).join(', '), Object.keys(p.channels).join(', '),
155      p.impressions, p.clicks, p.ctr, round(p.cost), round(p.conversions), p.cpa === null ? '' : round(p.cpa), round(p.convValue), round(p.roas), p.suggestion];
156  }));
157  sheet.getRange(1, 1, values.length, header.length).setValues(values);
158  sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
159  sheet.setFrozenRows(1);
160  if (values.length > 1) {
161    sheet.getRange(2, 10, values.length - 1, 1).setNumberFormat('0.00%');
162    sheet.getRange(2, 11, values.length - 1, 1).setNumberFormat('#,##0.00');
163  }
164  sheet.getRange(1, header.length + 2).setValue(description + ' | bijgewerkt ' + new Date());
165}
166
167function sendEmail(account, zombies, wasters, total) {
168  if (!EMAIL_RECIPIENTS) { return; }
169  var wasterCost = wasters.reduce(function (s, p) { return s + p.cost; }, 0);
170  var html = '<div style="font-family:Arial,sans-serif;font-size:13px"><h3>Shopping productanalyse ' + escapeHtml(account.getName()) + '</h3>' +
171    '<p>' + total + ' producten met impressies in ' + DATE_RANGE + '. <b>' + zombies.length + '</b> zombies en <b>' + wasters.length +
172    '</b> producten met samen ' + wasterCost.toFixed(2) + ' kosten zonder conversies.</p>' +
173    '<h4>Top geldverslinders</h4><table cellpadding="5" style="border-collapse:collapse;border:1px solid #ddd">' +
174    '<tr style="background:#f5f5f5"><th align="left">Product</th><th>Klikken</th><th>Kosten</th></tr>';
175  wasters.slice(0, 20).forEach(function (p) {
176    html += '<tr><td>' + escapeHtml(p.title || p.id) + '</td><td align="right">' + p.clicks + '</td><td align="right">' + p.cost.toFixed(2) + '</td></tr>';
177  });
178  html += '</table><p><a href="' + SPREADSHEET_URL + '">Volledig overzicht in Sheet</a></p></div>';
179  try {
180    MailApp.sendEmail({ to: EMAIL_RECIPIENTS, subject: '[Google Ads] Shopping zombies en geldverslinders - ' + account.getName(), htmlBody: html });
181  } catch (e) {
182    Logger.log('E-mail versturen mislukt: ' + e);
183  }
184}
185
186function round(value) {
187  return Math.round(Number(value) * 100) / 100;
188}
189
190function escapeHtml(text) {
191  return String(text).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
192}
193