Geo Performance Report

1/**
2 * Geo Performance Report
3 *
4 * Rapporteert prestaties per locatie: per provincie (regio) en per stad, op basis van de
5 * fysieke locatie van de gebruiker (user_location_view) en optioneel de getargete locatie
6 * (geographic_view). Per locatie worden kosten, klikken, conversies, CPA, conversieratio en
7 * ROAS berekend en vergeleken met het accountgemiddelde, zodat je locaties met een
8 * afwijkende CPA snel herkent voor bid adjustments of uitsluitingen. Het script wijzigt
9 * niets in het account.
10 *
11 * Versie:     1.2.0
12 * Frequentie: Wekelijks of maandelijks
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 = { regions: 'Per provincie', cities: 'Per stad', campaignsRegions: 'Campagne x provincie' };
22
23// Periode.
24var DATE_RANGE = 'LAST_30_DAYS';
25
26// Alleen campagnes waarvan de naam deze tekst bevat. Leeg = alle campagnes.
27var CAMPAIGN_NAME_CONTAINS = '';
28
29// Alleen locaties met minimaal dit aantal klikken opnemen (tegen ruis).
30var MIN_CLICKS = 20;
31
32// Markeer locaties waarvan de CPA meer dan deze fractie afwijkt van het gemiddelde (0.3 = 30%).
33var THRESHOLDS = {
34  cpaDeviation: 0.30,
35  minConversionsForCpa: 3
36};
37
38// Welke bron: 'USER_LOCATION' (fysieke locatie gebruiker) of 'GEOGRAPHIC' (locatie van belang/targeting).
39var LOCATION_SOURCE = 'USER_LOCATION';
40
41// Optioneel e-mailoverzicht met de opvallendste provincies. Leeg = geen e-mail.
42var EMAIL_RECIPIENTS = '';
43
44// ===== EINDE CONFIGURATIE =====
45
46function main() {
47  try {
48    var account = AdsApp.currentAccount();
49    Logger.log('Geo-rapport voor ' + account.getName() + ' | ' + DATE_RANGE + ' | bron ' + LOCATION_SOURCE);
50
51    var rows = fetchGeoRows();
52    Logger.log(rows.length + ' locatieregels opgehaald.');
53    if (!rows.length) { return; }
54
55    var geoNames = resolveGeoNames(rows);
56    var totals = aggregate(rows, function () { return 'TOTAAL'; })['TOTAAL'];
57    var byRegion = toList(aggregate(rows, function (r) { return r.regionId || 'onbekend'; }), geoNames, totals);
58    var byCity = toList(aggregate(rows, function (r) { return r.cityId || 'onbekend'; }), geoNames, totals);
59    var byCampaignRegion = toList(aggregate(rows, function (r) { return r.campaign + ' || ' + (r.regionId || 'onbekend'); }), geoNames, totals, true);
60
61    var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
62    writeSheet(ss, SHEET_NAMES.regions, byRegion, 'Provincie / regio', totals);
63    writeSheet(ss, SHEET_NAMES.cities, byCity, 'Stad', totals);
64    writeSheet(ss, SHEET_NAMES.campaignsRegions, byCampaignRegion, 'Campagne || provincie', totals);
65    sendEmail(account, byRegion, totals);
66    Logger.log('Klaar: ' + SPREADSHEET_URL);
67  } catch (e) {
68    Logger.log('FOUT in geo-performance-report: ' + e + (e.stack ? '\n' + e.stack : ''));
69    throw e;
70  }
71}
72
73/**
74 * Haalt prestaties per campagne x regio x stad op.
75 */
76function fetchGeoRows() {
77  var resource = LOCATION_SOURCE === 'GEOGRAPHIC' ? 'geographic_view' : 'user_location_view';
78  var query =
79    'SELECT campaign.id, campaign.name, segments.geo_target_region, segments.geo_target_city, ' +
80    (resource === 'geographic_view' ? 'geographic_view.country_criterion_id, ' : 'user_location_view.country_criterion_id, ') +
81    'metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions, metrics.conversions_value ' +
82    'FROM ' + resource + ' ' +
83    'WHERE segments.date DURING ' + DATE_RANGE + ' AND metrics.impressions > 0';
84  if (CAMPAIGN_NAME_CONTAINS) {
85    query += " AND campaign.name LIKE '%" + CAMPAIGN_NAME_CONTAINS.replace(/'/g, "\\'") + "%'";
86  }
87  var list = [];
88  var report = AdsApp.report(query).rows();
89  while (report.hasNext()) {
90    var row = report.next();
91    list.push({
92      campaign: row['campaign.name'],
93      regionId: extractCriterionId(row['segments.geo_target_region']),
94      cityId: extractCriterionId(row['segments.geo_target_city']),
95      countryId: String(row[resource + '.country_criterion_id'] || ''),
96      impressions: Number(row['metrics.impressions']),
97      clicks: Number(row['metrics.clicks']),
98      cost: Number(row['metrics.cost_micros']) / 1000000,
99      conversions: Number(row['metrics.conversions']),
100      convValue: Number(row['metrics.conversions_value'])
101    });
102  }
103  return list;
104}
105
106/**
107 * geo_target_region komt als resource name 'geoTargetConstants/2528' terug; we willen alleen het ID.
108 */
109function extractCriterionId(value) {
110  if (!value) { return ''; }
111  var s = String(value);
112  var idx = s.lastIndexOf('/');
113  return idx >= 0 ? s.substring(idx + 1) : s;
114}
115
116/**
117 * Zet geo target-ID's om naar namen via geo_target_constant.
118 */
119function resolveGeoNames(rows) {
120  var ids = {};
121  rows.forEach(function (r) {
122    if (r.regionId) { ids[r.regionId] = true; }
123    if (r.cityId) { ids[r.cityId] = true; }
124  });
125  var names = {};
126  var idList = Object.keys(ids).filter(function (id) { return /^\d+$/.test(id); });
127  for (var i = 0; i < idList.length; i += 500) {
128    var batch = idList.slice(i, i + 500);
129    var query = 'SELECT geo_target_constant.id, geo_target_constant.name, geo_target_constant.canonical_name, geo_target_constant.target_type ' +
130      'FROM geo_target_constant WHERE geo_target_constant.id IN (' + batch.join(',') + ')';
131    try {
132      var report = AdsApp.report(query).rows();
133      while (report.hasNext()) {
134        var row = report.next();
135        names[String(row['geo_target_constant.id'])] = { name: row['geo_target_constant.name'], canonical: row['geo_target_constant.canonical_name'], type: row['geo_target_constant.target_type'] };
136      }
137    } catch (e) {
138      Logger.log('geo_target_constant-lookup mislukt: ' + e);
139    }
140  }
141  return names;
142}
143
144function aggregate(rows, keyFn) {
145  var map = {};
146  rows.forEach(function (r) {
147    var key = keyFn(r);
148    if (!map[key]) { map[key] = { key: key, impressions: 0, clicks: 0, cost: 0, conversions: 0, convValue: 0 }; }
149    var m = map[key];
150    m.impressions += r.impressions;
151    m.clicks += r.clicks;
152    m.cost += r.cost;
153    m.conversions += r.conversions;
154    m.convValue += r.convValue;
155  });
156  Object.keys(map).forEach(function (k) { derive(map[k]); });
157  return map;
158}
159
160function derive(m) {
161  m.ctr = m.impressions > 0 ? m.clicks / m.impressions : 0;
162  m.cpc = m.clicks > 0 ? m.cost / m.clicks : 0;
163  m.cpa = m.conversions > 0 ? m.cost / m.conversions : null;
164  m.convRate = m.clicks > 0 ? m.conversions / m.clicks : 0;
165  m.roas = m.cost > 0 ? m.convValue / m.cost : 0;
166  return m;
167}
168
169function toList(map, geoNames, totals, compositeKey) {
170  var list = [];
171  Object.keys(map).forEach(function (key) {
172    var m = map[key];
173    if (m.clicks < MIN_CLICKS) { return; }
174    var geoId = compositeKey ? key.split(' || ')[1] : key;
175    var geo = geoNames[geoId];
176    m.label = compositeKey ? key.split(' || ')[0] + ' || ' + (geo ? geo.name : geoId) : (geo ? geo.name : geoId);
177    m.canonical = geo ? geo.canonical : '';
178    m.costShare = totals.cost > 0 ? m.cost / totals.cost : 0;
179    m.cpaIndex = m.cpa !== null && totals.cpa ? m.cpa / totals.cpa : null;
180    m.signal = '';
181    if (m.conversions >= THRESHOLDS.minConversionsForCpa && m.cpaIndex !== null) {
182      if (m.cpaIndex <= 1 - THRESHOLDS.cpaDeviation) { m.signal = 'Sterk: CPA ' + ((1 - m.cpaIndex) * 100).toFixed(0) + '% onder gemiddelde (bid verhogen?)'; }
183      if (m.cpaIndex >= 1 + THRESHOLDS.cpaDeviation) { m.signal = 'Zwak: CPA ' + ((m.cpaIndex - 1) * 100).toFixed(0) + '% boven gemiddelde (bid verlagen/uitsluiten?)'; }
184    } else if (m.conversions === 0 && m.cost >= (totals.cpa || 50)) {
185      m.signal = 'Geen conversies bij ' + m.cost.toFixed(0) + ' kosten';
186    }
187    list.push(m);
188  });
189  list.sort(function (a, b) { return b.cost - a.cost; });
190  return list;
191}
192
193function writeSheet(ss, name, list, labelHeader, totals) {
194  var sheet = ss.getSheetByName(name) || ss.insertSheet(name);
195  sheet.clearContents();
196  sheet.clearFormats();
197  var header = [labelHeader, 'Volledige naam', 'Impressies', 'Klikken', 'CTR', 'Gem. CPC', 'Kosten', 'Kostenaandeel', 'Conversies', 'Conv. ratio', 'CPA', 'CPA-index (1 = gem.)', 'Conv. waarde', 'ROAS', 'Signaal'];
198  var values = [header, ['ACCOUNT TOTAAL', '', totals.impressions, totals.clicks, totals.ctr, round(totals.cpc), round(totals.cost), 1, round(totals.conversions), totals.convRate,
199    totals.cpa === null ? '' : round(totals.cpa), 1, round(totals.convValue), round(totals.roas), '']];
200  list.forEach(function (m) {
201    values.push([m.label, m.canonical, m.impressions, m.clicks, m.ctr, round(m.cpc), round(m.cost), m.costShare, round(m.conversions), m.convRate,
202      m.cpa === null ? '' : round(m.cpa), m.cpaIndex === null ? '' : round(m.cpaIndex), round(m.convValue), round(m.roas), m.signal]);
203  });
204  sheet.getRange(1, 1, values.length, header.length).setValues(values);
205  sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
206  sheet.getRange(2, 1, 1, header.length).setFontWeight('bold').setBackground('#f5f5f5');
207  sheet.setFrozenRows(2);
208  if (values.length > 2) {
209    sheet.getRange(3, 5, values.length - 2, 1).setNumberFormat('0.00%');
210    sheet.getRange(3, 8, values.length - 2, 1).setNumberFormat('0.0%');
211    sheet.getRange(3, 10, values.length - 2, 1).setNumberFormat('0.00%');
212    for (var i = 0; i < list.length; i++) {
213      if (list[i].signal.indexOf('Sterk') === 0) { sheet.getRange(i + 3, 15).setBackground('#e6f4ea'); }
214      else if (list[i].signal) { sheet.getRange(i + 3, 15).setBackground('#fdecea'); }
215    }
216  }
217  sheet.getRange(1, header.length + 2).setValue('Periode ' + DATE_RANGE + ' | bron ' + LOCATION_SOURCE + ' | min. ' + MIN_CLICKS + ' klikken | ' + new Date());
218}
219
220function sendEmail(account, byRegion, totals) {
221  if (!EMAIL_RECIPIENTS) { return; }
222  var flagged = byRegion.filter(function (m) { return m.signal; });
223  var html = '<div style="font-family:Arial,sans-serif;font-size:13px"><h3>Geo-prestaties ' + escapeHtml(account.getName()) + '</h3>' +
224    '<p>Account-CPA: ' + (totals.cpa === null ? '-' : totals.cpa.toFixed(2)) + ' | ' + flagged.length + ' provincies met signaal.</p>' +
225    '<table cellpadding="5" style="border-collapse:collapse;border:1px solid #ddd"><tr style="background:#f5f5f5"><th align="left">Provincie</th><th>Kosten</th><th>Conv.</th><th>CPA</th><th>Index</th><th align="left">Signaal</th></tr>';
226  byRegion.slice(0, 25).forEach(function (m) {
227    html += '<tr' + (m.signal ? ' style="background:' + (m.signal.indexOf('Sterk') === 0 ? '#e6f4ea' : '#fdecea') + '"' : '') + '><td>' + escapeHtml(m.label) + '</td><td align="right">' + m.cost.toFixed(2) +
228      '</td><td align="right">' + m.conversions.toFixed(1) + '</td><td align="right">' + (m.cpa === null ? '-' : m.cpa.toFixed(2)) + '</td><td align="right">' + (m.cpaIndex === null ? '-' : m.cpaIndex.toFixed(2)) + '</td><td>' + escapeHtml(m.signal) + '</td></tr>';
229  });
230  html += '</table><p><a href="' + SPREADSHEET_URL + '">Open de Sheet</a></p></div>';
231  try {
232    MailApp.sendEmail({ to: EMAIL_RECIPIENTS, subject: '[Google Ads] Geo-rapport - ' + account.getName(), htmlBody: html });
233  } catch (e) {
234    Logger.log('E-mail versturen mislukt: ' + e);
235  }
236}
237
238function round(value) {
239  return Math.round(Number(value) * 100) / 100;
240}
241
242function escapeHtml(text) {
243  return String(text).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
244}
245