Bid Adjustment by Hour

1/**
2 * Bid Adjustment by Hour (dagdeel-analyse)
3 *
4 * Analyseert prestaties per uur van de dag en dag van de week (hour_of_day x day_of_week)
5 * over de afgelopen periode en schrijft een heatmap van conversieratio, CPA en kosten naar
6 * een Google Sheet. Op basis daarvan worden bid adjustments per dagdeel (blokken van
7 * 4 uur, max. 6 per dag i.v.m. de advertentieplanning-limiet) voorgesteld. Aanpassingen
8 * worden ALLEEN toegepast op campagnes met Manual CPC / Enhanced CPC en alleen als
9 * APPLY_ADJUSTMENTS true is en DRY_RUN false. Voor Smart Bidding wordt uitsluitend
10 * gerapporteerd.
11 *
12 * Versie:     1.3.0
13 * Frequentie: Wekelijks of maandelijks
14 * Niveau:     Account
15 * Licentie:   MIT
16 */
17
18// ===== CONFIGURATIE =====
19
20// Zolang DRY_RUN true is, worden voorgestelde bid adjustments alleen gelogd en naar de Sheet geschreven.
21var DRY_RUN = true;
22
23// Bid adjustments daadwerkelijk toepassen (alleen bij Manual/Enhanced CPC en DRY_RUN=false).
24var APPLY_ADJUSTMENTS = false;
25
26// Google Sheet voor heatmap en voorstellen.
27var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/JOUW_SHEET_ID/edit';
28
29// Periode (minimaal 30 dagen aanbevolen voor voldoende data per uur).
30var DATE_RANGE = 'LAST_90_DAYS';
31
32// Alleen campagnes waarvan de naam deze tekst bevat. Leeg = alle Search-campagnes.
33var CAMPAIGN_NAME_CONTAINS = '';
34
35// Dagdelen (uren inclusief start, exclusief einde). Maximaal 6 blokken per dag.
36var DAYPARTS = [
37  { label: 'Nacht 00-06', start: 0, end: 6 },
38  { label: 'Ochtend 06-10', start: 6, end: 10 },
39  { label: 'Middag 10-14', start: 10, end: 14 },
40  { label: 'Namiddag 14-18', start: 14, end: 18 },
41  { label: 'Avond 18-22', start: 18, end: 22 },
42  { label: 'Laat 22-24', start: 22, end: 24 }
43];
44
45// Regels voor de voorstellen.
46var THRESHOLDS = {
47  minClicksPerCell: 30,      // minimaal aantal klikken in een dagdeel voordat er een voorstel komt
48  maxAdjustment: 0.30,       // maximaal +/- 30%
49  dampening: 0.5,            // demping: voorgestelde aanpassing = 50% van de relatieve afwijking
50  minConversionsAccount: 30  // onder dit totaal aantal conversies alleen rapporteren (te weinig data)
51};
52
53// ===== EINDE CONFIGURATIE =====
54
55var DAYS = ['MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY'];
56var DAY_LABELS = { MONDAY: 'Ma', TUESDAY: 'Di', WEDNESDAY: 'Wo', THURSDAY: 'Do', FRIDAY: 'Vr', SATURDAY: 'Za', SUNDAY: 'Zo' };
57
58function main() {
59  try {
60    var account = AdsApp.currentAccount();
61    var preview = AdsApp.getExecutionInfo().isPreview();
62    var effectiveDryRun = DRY_RUN || preview || !APPLY_ADJUSTMENTS;
63    Logger.log('Dagdeel-analyse voor ' + account.getName() + ' | ' + DATE_RANGE + ' | DRY_RUN=' + effectiveDryRun);
64
65    var data = fetchHourlyData();
66    var campaignIds = Object.keys(data.campaigns);
67    Logger.log(campaignIds.length + ' campagnes met data.');
68    if (!campaignIds.length) { return; }
69
70    var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
71    writeHeatmap(ss, 'Heatmap account', data.account);
72
73    var proposals = [];
74    campaignIds.forEach(function (id) {
75      var c = data.campaigns[id];
76      var campaignProposals = buildProposals(c);
77      proposals = proposals.concat(campaignProposals);
78    });
79    writeProposals(ss, proposals, effectiveDryRun);
80    Logger.log(proposals.length + ' dagdeel-voorstellen gegenereerd.');
81
82    if (!effectiveDryRun) {
83      applyProposals(proposals, data.campaigns);
84    } else {
85      Logger.log('Geen wijzigingen toegepast (DRY_RUN, preview of APPLY_ADJUSTMENTS=false).');
86    }
87    Logger.log('Klaar: ' + SPREADSHEET_URL);
88  } catch (e) {
89    Logger.log('FOUT in bid-adjustment-by-hour: ' + e + (e.stack ? '\n' + e.stack : ''));
90    throw e;
91  }
92}
93
94/**
95 * Haalt metrics per campagne x dag x uur op.
96 */
97function fetchHourlyData() {
98  var query =
99    'SELECT campaign.id, campaign.name, campaign.bidding_strategy_type, campaign.manual_cpc.enhanced_cpc_enabled, ' +
100    'segments.day_of_week, segments.hour, ' +
101    'metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions, metrics.conversions_value ' +
102    'FROM campaign ' +
103    "WHERE campaign.advertising_channel_type = 'SEARCH' AND campaign.status = 'ENABLED' " +
104    'AND segments.date DURING ' + DATE_RANGE;
105  if (CAMPAIGN_NAME_CONTAINS) {
106    query += " AND campaign.name LIKE '%" + CAMPAIGN_NAME_CONTAINS.replace(/'/g, "\\'") + "%'";
107  }
108
109  var campaigns = {};
110  var account = newGrid();
111  var rows = AdsApp.report(query).rows();
112  while (rows.hasNext()) {
113    var row = rows.next();
114    var id = row['campaign.id'];
115    if (!campaigns[id]) {
116      campaigns[id] = {
117        id: id,
118        name: row['campaign.name'],
119        biddingStrategy: row['campaign.bidding_strategy_type'],
120        enhancedCpc: String(row['campaign.manual_cpc.enhanced_cpc_enabled']) === 'true',
121        grid: newGrid(),
122        totals: emptyCell()
123      };
124    }
125    var day = row['segments.day_of_week'];
126    var hour = Number(row['segments.hour']);
127    if (DAYS.indexOf(day) === -1 || isNaN(hour)) { continue; }
128    var m = {
129      impressions: Number(row['metrics.impressions']),
130      clicks: Number(row['metrics.clicks']),
131      cost: Number(row['metrics.cost_micros']) / 1000000,
132      conversions: Number(row['metrics.conversions']),
133      convValue: Number(row['metrics.conversions_value'])
134    };
135    addTo(campaigns[id].grid[day][hour], m);
136    addTo(campaigns[id].totals, m);
137    addTo(account.grid[day][hour], m);
138    addTo(account.totals, m);
139  }
140  return { campaigns: campaigns, account: account };
141}
142
143function newGrid() {
144  var grid = {};
145  DAYS.forEach(function (d) {
146    grid[d] = [];
147    for (var h = 0; h < 24; h++) { grid[d].push(emptyCell()); }
148  });
149  return { grid: grid, totals: emptyCell() };
150}
151
152function emptyCell() {
153  return { impressions: 0, clicks: 0, cost: 0, conversions: 0, convValue: 0 };
154}
155
156function addTo(target, m) {
157  target.impressions += m.impressions;
158  target.clicks += m.clicks;
159  target.cost += m.cost;
160  target.conversions += m.conversions;
161  target.convValue += m.convValue;
162}
163
164/**
165 * Schrijft drie heatmaps (conversieratio, CPA, kosten) onder elkaar in één tabblad.
166 */
167function writeHeatmap(ss, sheetName, gridData) {
168  var sheet = ss.getSheetByName(sheetName) || ss.insertSheet(sheetName);
169  sheet.clearContents();
170  sheet.clearFormats();
171  var grid = gridData.grid || gridData;
172  var hours = [];
173  for (var h = 0; h < 24; h++) { hours.push(h + ':00'); }
174
175  var blocks = [
176    { title: 'Conversieratio (conversies / klikken)', fn: function (c) { return c.clicks > 0 ? c.conversions / c.clicks : 0; }, format: '0.0%', higherIsBetter: true },
177    { title: 'CPA (kosten / conversies)', fn: function (c) { return c.conversions > 0 ? c.cost / c.conversions : 0; }, format: '#,##0.00', higherIsBetter: false },
178    { title: 'Kosten', fn: function (c) { return c.cost; }, format: '#,##0.00', higherIsBetter: null },
179    { title: 'Klikken', fn: function (c) { return c.clicks; }, format: '0', higherIsBetter: null }
180  ];
181
182  var rowPointer = 1;
183  blocks.forEach(function (block) {
184    sheet.getRange(rowPointer, 1).setValue(block.title).setFontWeight('bold');
185    rowPointer++;
186    var values = [['Dag / uur'].concat(hours)];
187    var numeric = [];
188    DAYS.forEach(function (d) {
189      var rowVals = [DAY_LABELS[d]];
190      for (var h = 0; h < 24; h++) {
191        var v = block.fn(grid[d][h]);
192        rowVals.push(v);
193        numeric.push(v);
194      }
195      values.push(rowVals);
196    });
197    sheet.getRange(rowPointer, 1, values.length, 25).setValues(values);
198    sheet.getRange(rowPointer, 1, 1, 25).setFontWeight('bold').setBackground('#e8eaf6');
199    sheet.getRange(rowPointer + 1, 2, 7, 24).setNumberFormat(block.format);
200    colorHeatmap(sheet, rowPointer + 1, 2, values.slice(1), numeric, block.higherIsBetter);
201    rowPointer += values.length + 2;
202  });
203  sheet.getRange(1, 27).setValue('Periode: ' + DATE_RANGE + ' | ' + new Date());
204}
205
206function colorHeatmap(sheet, startRow, startCol, rows, numeric, higherIsBetter) {
207  var positives = numeric.filter(function (v) { return v > 0; });
208  if (!positives.length) { return; }
209  var max = Math.max.apply(null, positives);
210  var min = Math.min.apply(null, positives);
211  var backgrounds = rows.map(function (r) {
212    return r.slice(1).map(function (v) {
213      if (!(v > 0)) { return '#f5f5f5'; }
214      var t = max > min ? (v - min) / (max - min) : 0.5;
215      if (higherIsBetter === false) { t = 1 - t; }
216      if (higherIsBetter === null) { return blend('#ffffff', '#90caf9', t); }
217      return blend('#f8bbd0', '#a5d6a7', t);
218    });
219  });
220  sheet.getRange(startRow, startCol, rows.length, 24).setBackgrounds(backgrounds);
221}
222
223function blend(hexA, hexB, t) {
224  var a = hexToRgb(hexA);
225  var b = hexToRgb(hexB);
226  var c = a.map(function (v, i) { return Math.round(v + (b[i] - v) * t); });
227  return '#' + c.map(function (v) { var s = v.toString(16); return s.length === 1 ? '0' + s : s; }).join('');
228}
229
230function hexToRgb(hex) {
231  return [parseInt(hex.substr(1, 2), 16), parseInt(hex.substr(3, 2), 16), parseInt(hex.substr(5, 2), 16)];
232}
233
234/**
235 * Bouwt per campagne en dagdeel een voorstel voor een bid adjustment op basis van de
236 * conversieratio t.o.v. het campagnegemiddelde (gedempt en begrensd).
237 */
238function buildProposals(campaign) {
239  var proposals = [];
240  var avgConvRate = campaign.totals.clicks > 0 ? campaign.totals.conversions / campaign.totals.clicks : 0;
241  var eligible = isManualBidding(campaign);
242  var enoughData = campaign.totals.conversions >= THRESHOLDS.minConversionsAccount;
243
244  DAYS.forEach(function (day) {
245    DAYPARTS.forEach(function (part) {
246      var cell = emptyCell();
247      for (var h = part.start; h < part.end; h++) { addTo(cell, campaign.grid[day][h]); }
248      var convRate = cell.clicks > 0 ? cell.conversions / cell.clicks : 0;
249      var suggested = 0;
250      var note = '';
251      if (!enoughData) {
252        note = 'Te weinig conversies in campagne (' + Math.round(campaign.totals.conversions) + ')';
253      } else if (cell.clicks < THRESHOLDS.minClicksPerCell) {
254        note = 'Te weinig klikken (' + cell.clicks + ')';
255      } else if (avgConvRate > 0) {
256        var relative = (convRate - avgConvRate) / avgConvRate;
257        suggested = Math.max(-THRESHOLDS.maxAdjustment, Math.min(THRESHOLDS.maxAdjustment, relative * THRESHOLDS.dampening));
258        suggested = Math.round(suggested * 20) / 20; // afronden op 5%
259        note = eligible ? 'Toepasbaar (Manual/eCPC)' : 'Alleen rapport: ' + campaign.biddingStrategy;
260      }
261      proposals.push({
262        campaignId: campaign.id,
263        campaignName: campaign.name,
264        biddingStrategy: campaign.biddingStrategy + (campaign.enhancedCpc ? ' (eCPC)' : ''),
265        day: day,
266        part: part,
267        clicks: cell.clicks,
268        cost: cell.cost,
269        conversions: cell.conversions,
270        convRate: convRate,
271        avgConvRate: avgConvRate,
272        cpa: cell.conversions > 0 ? cell.cost / cell.conversions : null,
273        suggested: suggested,
274        eligible: eligible && enoughData && cell.clicks >= THRESHOLDS.minClicksPerCell,
275        note: note
276      });
277    });
278  });
279  return proposals;
280}
281
282function isManualBidding(campaign) {
283  return campaign.biddingStrategy === 'MANUAL_CPC' || campaign.biddingStrategy === 'ENHANCED_CPC';
284}
285
286function writeProposals(ss, proposals, dryRun) {
287  var sheet = ss.getSheetByName('Voorstellen bid adjustments') || ss.insertSheet('Voorstellen bid adjustments');
288  sheet.clearContents();
289  var header = ['Campagne', 'Biedstrategie', 'Dag', 'Dagdeel', 'Klikken', 'Kosten', 'Conversies', 'Conv. ratio', 'Gem. conv. ratio campagne', 'CPA',
290    'Voorgestelde aanpassing', 'Toepasbaar', 'Opmerking', 'Status'];
291  var values = [header].concat(proposals.map(function (p) {
292    return [p.campaignName, p.biddingStrategy, DAY_LABELS[p.day], p.part.label, p.clicks, round(p.cost), round(p.conversions), p.convRate, p.avgConvRate,
293      p.cpa === null ? '' : round(p.cpa), p.suggested, p.eligible ? 'JA' : 'NEE', p.note, dryRun ? 'Voorstel' : (p.eligible && p.suggested !== 0 ? 'Toegepast' : 'Geen actie')];
294  }));
295  sheet.getRange(1, 1, values.length, header.length).setValues(values);
296  sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
297  sheet.setFrozenRows(1);
298  if (values.length > 1) {
299    sheet.getRange(2, 8, values.length - 1, 2).setNumberFormat('0.00%');
300    sheet.getRange(2, 11, values.length - 1, 1).setNumberFormat('+0%;-0%;0%');
301  }
302}
303
304/**
305 * Past de voorstellen toe: bestaande advertentieplanning wordt verwijderd en vervangen door
306 * de dagdelen met bid modifier. Alleen voor Manual/eCPC-campagnes.
307 */
308function applyProposals(proposals, campaigns) {
309  var byCampaign = {};
310  proposals.forEach(function (p) {
311    if (!p.eligible) { return; }
312    if (!byCampaign[p.campaignId]) { byCampaign[p.campaignId] = []; }
313    byCampaign[p.campaignId].push(p);
314  });
315  var ids = Object.keys(byCampaign);
316  if (!ids.length) { Logger.log('Geen campagnes met toepasbare voorstellen.'); return; }
317
318  var iterator = AdsApp.campaigns().withIds(ids.map(function (id) { return Number(id); })).get();
319  while (iterator.hasNext()) {
320    var campaign = iterator.next();
321    var list = byCampaign[String(campaign.getId())] || [];
322    if (!list.length) { continue; }
323
324    // Verwijder bestaande planning zodat we niet dubbel targeten.
325    var existing = campaign.targeting().adSchedules().get();
326    var removed = 0;
327    while (existing.hasNext()) { existing.next().remove(); removed++; }
328
329    var added = 0;
330    list.forEach(function (p) {
331      try {
332        campaign.addAdSchedule({
333          dayOfWeek: p.day,
334          startHour: p.part.start,
335          startMinute: 0,
336          endHour: p.part.end,
337          endMinute: 0,
338          bidModifier: 1 + p.suggested
339        });
340        added++;
341      } catch (e) {
342        Logger.log('Planning toevoegen mislukt voor ' + campaign.getName() + ' ' + p.day + ' ' + p.part.label + ': ' + e);
343      }
344    });
345    Logger.log(campaign.getName() + ': ' + removed + ' bestaande blokken verwijderd, ' + added + ' dagdelen met bid adjustment toegevoegd.');
346  }
347}
348
349function round(value) {
350  return Math.round(Number(value) * 100) / 100;
351}
352