PMax Channel Split

1/**
2 * Performance Max Channel Split
3 *
4 * Benadert per Performance Max-campagne de verdeling van kosten, impressies, klikken en
5 * conversies over de kanalen Shopping, Video, Display, Search en Discover/Overig.
6 * Methode:
7 *  1. Exact, indien het account het ondersteunt: campagne gesegmenteerd op
8 *     segments.ad_network_type (nieuwere PMax-rapportage).
9 *  2. Anders: benadering via campagne-totalen minus shopping_performance_view
10 *     (= Shopping), video views (= Video-indicatie) en asset-group totalen.
11 * Resultaten gaan naar een Google Sheet met per campagne een regel en een
12 * historietabblad. Het script wijzigt niets in het account.
13 *
14 * Versie:     1.2.0
15 * Frequentie: Wekelijks
16 * Niveau:     Account
17 * Licentie:   MIT
18 */
19
20// ===== CONFIGURATIE =====
21
22// Google Sheet voor het rapport.
23var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/JOUW_SHEET_ID/edit';
24var SHEET_NAMES = { current: 'PMax kanalen', history: 'PMax kanalen historie', assetGroups: 'PMax asset groups' };
25
26// Periode.
27var DATE_RANGE = 'LAST_30_DAYS';
28
29// Alleen PMax-campagnes waarvan de naam deze tekst bevat. Leeg = alle PMax-campagnes.
30var CAMPAIGN_NAME_CONTAINS = '';
31
32// Probeer eerst de exacte methode via segments.ad_network_type (valt automatisch terug op benadering).
33var TRY_EXACT_NETWORK_SEGMENTATION = true;
34
35// Optioneel e-mailoverzicht. Leeg = geen e-mail.
36var EMAIL_RECIPIENTS = '';
37
38// ===== EINDE CONFIGURATIE =====
39
40function main() {
41  try {
42    var account = AdsApp.currentAccount();
43    Logger.log('PMax channel split voor ' + account.getName() + ' | ' + DATE_RANGE);
44
45    var campaigns = fetchPmaxCampaignTotals();
46    var ids = Object.keys(campaigns);
47    if (!ids.length) {
48      Logger.log('Geen Performance Max-campagnes met data gevonden.');
49      return;
50    }
51    Logger.log(ids.length + ' PMax-campagnes gevonden.');
52
53    var method = 'benadering';
54    var exact = TRY_EXACT_NETWORK_SEGMENTATION ? fetchNetworkSegmentation(ids) : null;
55    if (exact) {
56      method = 'exact (ad_network_type)';
57      ids.forEach(function (id) { campaigns[id].channels = exact[id] || emptyChannels(); });
58    } else {
59      var shopping = fetchShoppingTotals(ids);
60      ids.forEach(function (id) { campaigns[id].channels = approximateChannels(campaigns[id], shopping[id]); });
61    }
62    Logger.log('Methode: ' + method);
63
64    var assetGroups = fetchAssetGroups(ids);
65    var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
66    writeCurrentSheet(ss, campaigns, method);
67    appendHistory(ss, campaigns, method, account.getTimeZone());
68    writeAssetGroups(ss, assetGroups);
69    sendEmail(account, campaigns, method);
70    Logger.log('Klaar: ' + SPREADSHEET_URL);
71  } catch (e) {
72    Logger.log('FOUT in pmax-channel-split: ' + e + (e.stack ? '\n' + e.stack : ''));
73    throw e;
74  }
75}
76
77function campaignFilterClause() {
78  return CAMPAIGN_NAME_CONTAINS ? " AND campaign.name LIKE '%" + CAMPAIGN_NAME_CONTAINS.replace(/'/g, "\\'") + "%'" : '';
79}
80
81/**
82 * Totalen per PMax-campagne.
83 */
84function fetchPmaxCampaignTotals() {
85  var query =
86    'SELECT campaign.id, campaign.name, metrics.impressions, metrics.clicks, metrics.cost_micros, ' +
87    'metrics.conversions, metrics.conversions_value, metrics.video_views ' +
88    'FROM campaign ' +
89    "WHERE campaign.advertising_channel_type = 'PERFORMANCE_MAX' AND segments.date DURING " + DATE_RANGE + ' ' +
90    'AND metrics.impressions > 0' + campaignFilterClause();
91  var result = {};
92  var rows = AdsApp.report(query).rows();
93  while (rows.hasNext()) {
94    var row = rows.next();
95    result[row['campaign.id']] = {
96      id: row['campaign.id'],
97      name: row['campaign.name'],
98      total: metricsFromRow(row),
99      videoViews: Number(row['metrics.video_views'] || 0),
100      channels: null
101    };
102  }
103  return result;
104}
105
106/**
107 * Exacte kanaalsplit via segments.ad_network_type (werkt alleen als de API dit voor PMax teruggeeft).
108 * Retourneert null als de query niet ondersteund wordt of geen kanaalsegmenten oplevert.
109 */
110function fetchNetworkSegmentation(ids) {
111  var query =
112    'SELECT campaign.id, segments.ad_network_type, metrics.impressions, metrics.clicks, metrics.cost_micros, ' +
113    'metrics.conversions, metrics.conversions_value ' +
114    'FROM campaign ' +
115    "WHERE campaign.advertising_channel_type = 'PERFORMANCE_MAX' AND segments.date DURING " + DATE_RANGE + ' ' +
116    'AND campaign.id IN (' + ids.join(',') + ')';
117  try {
118    var rows = AdsApp.report(query).rows();
119    var result = {};
120    var distinctNetworks = {};
121    while (rows.hasNext()) {
122      var row = rows.next();
123      var network = row['segments.ad_network_type'];
124      distinctNetworks[network] = true;
125      var id = row['campaign.id'];
126      if (!result[id]) { result[id] = emptyChannels(); }
127      var channel = mapNetworkToChannel(network);
128      addMetrics(result[id][channel], metricsFromRow(row));
129    }
130    var networks = Object.keys(distinctNetworks);
131    // Alleen bruikbaar als er echt per netwerk gesegmenteerd is (niet alleen MIXED/UNKNOWN).
132    var informative = networks.filter(function (n) { return ['SEARCH', 'SEARCH_PARTNERS', 'CONTENT', 'YOUTUBE', 'YOUTUBE_SEARCH', 'YOUTUBE_WATCH', 'GOOGLE_TV'].indexOf(n) !== -1; });
133    if (!informative.length) {
134      Logger.log('ad_network_type geeft geen kanaalsegmentatie voor PMax (' + networks.join(', ') + '); benadering wordt gebruikt.');
135      return null;
136    }
137    return result;
138  } catch (e) {
139    Logger.log('Exacte segmentatie niet beschikbaar (' + e + '); benadering wordt gebruikt.');
140    return null;
141  }
142}
143
144function mapNetworkToChannel(network) {
145  switch (network) {
146    case 'SEARCH':
147    case 'SEARCH_PARTNERS':
148      return 'search';
149    case 'YOUTUBE':
150    case 'YOUTUBE_SEARCH':
151    case 'YOUTUBE_WATCH':
152    case 'GOOGLE_TV':
153      return 'video';
154    case 'CONTENT':
155      return 'display';
156    case 'MIXED':
157      return 'shopping'; // In PMax-rapportage staat Shopping-verkeer doorgaans onder MIXED.
158    default:
159      return 'other';
160  }
161}
162
163/**
164 * Shopping-totalen per PMax-campagne via shopping_performance_view.
165 */
166function fetchShoppingTotals(ids) {
167  var query =
168    'SELECT campaign.id, metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions, metrics.conversions_value ' +
169    'FROM shopping_performance_view ' +
170    "WHERE campaign.advertising_channel_type = 'PERFORMANCE_MAX' AND segments.date DURING " + DATE_RANGE + ' ' +
171    'AND campaign.id IN (' + ids.join(',') + ')';
172  var result = {};
173  try {
174    var rows = AdsApp.report(query).rows();
175    while (rows.hasNext()) {
176      var row = rows.next();
177      var id = row['campaign.id'];
178      if (!result[id]) { result[id] = emptyMetrics(); }
179      addMetrics(result[id], metricsFromRow(row));
180    }
181  } catch (e) {
182    Logger.log('Shopping-query mislukt (geen Merchant Center-koppeling?): ' + e);
183  }
184  return result;
185}
186
187/**
188 * Benadering: Shopping = shopping_performance_view; Video = op basis van video views
189 * (impressies met views, kosten evenredig aan het aandeel views in de niet-shopping impressies);
190 * rest = Search/Display/Discover samengevoegd als "other" met een indicatieve verdeling.
191 */
192function approximateChannels(campaign, shopping) {
193  var channels = emptyChannels();
194  var shop = shopping || emptyMetrics();
195  ['impressions', 'clicks', 'cost', 'conversions', 'convValue'].forEach(function (k) {
196    channels.shopping[k] = Math.min(shop[k], campaign.total[k]);
197  });
198  var rest = emptyMetrics();
199  ['impressions', 'clicks', 'cost', 'conversions', 'convValue'].forEach(function (k) {
200    rest[k] = Math.max(campaign.total[k] - channels.shopping[k], 0);
201  });
202  // Video-indicatie: aandeel views in de resterende impressies bepaalt het video-aandeel.
203  var videoShare = rest.impressions > 0 ? Math.min(campaign.videoViews / rest.impressions, 1) : 0;
204  ['impressions', 'clicks', 'cost', 'conversions', 'convValue'].forEach(function (k) {
205    channels.video[k] = rest[k] * videoShare;
206    channels.other[k] = rest[k] * (1 - videoShare);
207  });
208  channels.approximated = true;
209  return channels;
210}
211
212/**
213 * Asset group-prestaties (voor context in een apart tabblad).
214 */
215function fetchAssetGroups(ids) {
216  var query =
217    'SELECT campaign.name, asset_group.name, asset_group.status, asset_group.ad_strength, ' +
218    'metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions, metrics.conversions_value ' +
219    'FROM asset_group ' +
220    'WHERE segments.date DURING ' + DATE_RANGE + ' AND campaign.id IN (' + ids.join(',') + ')';
221  var list = [];
222  try {
223    var rows = AdsApp.report(query).rows();
224    while (rows.hasNext()) {
225      var row = rows.next();
226      var m = metricsFromRow(row);
227      list.push({ campaign: row['campaign.name'], assetGroup: row['asset_group.name'], status: row['asset_group.status'], adStrength: row['asset_group.ad_strength'], m: m });
228    }
229  } catch (e) {
230    Logger.log('Asset group-query mislukt: ' + e);
231  }
232  list.sort(function (a, b) { return b.m.cost - a.m.cost; });
233  return list;
234}
235
236function metricsFromRow(row) {
237  return {
238    impressions: Number(row['metrics.impressions'] || 0),
239    clicks: Number(row['metrics.clicks'] || 0),
240    cost: Number(row['metrics.cost_micros'] || 0) / 1000000,
241    conversions: Number(row['metrics.conversions'] || 0),
242    convValue: Number(row['metrics.conversions_value'] || 0)
243  };
244}
245
246function emptyMetrics() {
247  return { impressions: 0, clicks: 0, cost: 0, conversions: 0, convValue: 0 };
248}
249
250function emptyChannels() {
251  return { shopping: emptyMetrics(), video: emptyMetrics(), display: emptyMetrics(), search: emptyMetrics(), other: emptyMetrics(), approximated: false };
252}
253
254function addMetrics(target, source) {
255  ['impressions', 'clicks', 'cost', 'conversions', 'convValue'].forEach(function (k) { target[k] += source[k]; });
256}
257
258var CHANNEL_ORDER = [
259  { key: 'shopping', label: 'Shopping' },
260  { key: 'video', label: 'Video' },
261  { key: 'display', label: 'Display' },
262  { key: 'search', label: 'Search' },
263  { key: 'other', label: 'Overig / Discover' }
264];
265
266function writeCurrentSheet(ss, campaigns, method) {
267  var sheet = ss.getSheetByName(SHEET_NAMES.current) || ss.insertSheet(SHEET_NAMES.current);
268  sheet.clearContents();
269  var header = ['Campagne', 'Totale kosten', 'Totale conversies', 'ROAS'];
270  CHANNEL_ORDER.forEach(function (c) {
271    header.push(c.label + ' kosten', c.label + ' kosten %', c.label + ' conv.', c.label + ' ROAS');
272  });
273  var values = [header];
274  Object.keys(campaigns).forEach(function (id) {
275    var c = campaigns[id];
276    var row = [c.name, round(c.total.cost), round(c.total.conversions), c.total.cost > 0 ? round(c.total.convValue / c.total.cost) : 0];
277    CHANNEL_ORDER.forEach(function (ch) {
278      var m = c.channels[ch.key];
279      row.push(round(m.cost), c.total.cost > 0 ? m.cost / c.total.cost : 0, round(m.conversions), m.cost > 0 ? round(m.convValue / m.cost) : 0);
280    });
281    values.push(row);
282  });
283  sheet.getRange(1, 1, values.length, header.length).setValues(values);
284  sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
285  sheet.setFrozenRows(1);
286  sheet.setFrozenColumns(1);
287  for (var i = 0; i < CHANNEL_ORDER.length; i++) {
288    if (values.length > 1) { sheet.getRange(2, 6 + i * 4, values.length - 1, 1).setNumberFormat('0.0%'); }
289  }
290  sheet.getRange(1, header.length + 2).setValue('Periode: ' + DATE_RANGE + ' | methode: ' + method +
291    (method === 'benadering' ? ' (Display/Search/Discover samengevoegd onder Overig; Video op basis van views)' : '') + ' | ' + new Date());
292}
293
294function appendHistory(ss, campaigns, method, timezone) {
295  var sheet = ss.getSheetByName(SHEET_NAMES.history) || ss.insertSheet(SHEET_NAMES.history);
296  var header = ['Datum', 'Periode', 'Methode', 'Campagne', 'Kanaal', 'Impressies', 'Klikken', 'Kosten', 'Conversies', 'Conv. waarde'];
297  if (sheet.getLastRow() === 0) {
298    sheet.appendRow(header);
299    sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
300    sheet.setFrozenRows(1);
301  }
302  var today = Utilities.formatDate(new Date(), timezone, 'yyyy-MM-dd');
303  var values = [];
304  Object.keys(campaigns).forEach(function (id) {
305    var c = campaigns[id];
306    CHANNEL_ORDER.forEach(function (ch) {
307      var m = c.channels[ch.key];
308      if (m.impressions === 0 && m.cost === 0) { return; }
309      values.push([today, DATE_RANGE, method, c.name, ch.label, Math.round(m.impressions), Math.round(m.clicks), round(m.cost), round(m.conversions), round(m.convValue)]);
310    });
311  });
312  if (values.length) { sheet.getRange(sheet.getLastRow() + 1, 1, values.length, header.length).setValues(values); }
313}
314
315function writeAssetGroups(ss, assetGroups) {
316  var sheet = ss.getSheetByName(SHEET_NAMES.assetGroups) || ss.insertSheet(SHEET_NAMES.assetGroups);
317  sheet.clearContents();
318  var header = ['Campagne', 'Asset group', 'Status', 'Ad strength', 'Impressies', 'Klikken', 'Kosten', 'Conversies', 'Conv. waarde', 'ROAS'];
319  var values = [header].concat(assetGroups.map(function (a) {
320    return [a.campaign, a.assetGroup, a.status, a.adStrength, a.m.impressions, a.m.clicks, round(a.m.cost), round(a.m.conversions), round(a.m.convValue), a.m.cost > 0 ? round(a.m.convValue / a.m.cost) : 0];
321  }));
322  sheet.getRange(1, 1, values.length, header.length).setValues(values);
323  sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
324  sheet.setFrozenRows(1);
325}
326
327function sendEmail(account, campaigns, method) {
328  if (!EMAIL_RECIPIENTS) { return; }
329  var html = '<div style="font-family:Arial,sans-serif;font-size:13px"><h3>PMax kanaalverdeling ' + escapeHtml(account.getName()) + '</h3>' +
330    '<p>Periode ' + DATE_RANGE + ', methode: ' + method + '</p><table cellpadding="5" style="border-collapse:collapse;border:1px solid #ddd">' +
331    '<tr style="background:#f5f5f5"><th align="left">Campagne</th><th>Kosten</th>';
332  CHANNEL_ORDER.forEach(function (c) { html += '<th>' + c.label + '</th>'; });
333  html += '</tr>';
334  Object.keys(campaigns).forEach(function (id) {
335    var c = campaigns[id];
336    html += '<tr><td>' + escapeHtml(c.name) + '</td><td align="right">' + c.total.cost.toFixed(2) + '</td>';
337    CHANNEL_ORDER.forEach(function (ch) {
338      var share = c.total.cost > 0 ? c.channels[ch.key].cost / c.total.cost : 0;
339      html += '<td align="right">' + (share * 100).toFixed(0) + '%</td>';
340    });
341    html += '</tr>';
342  });
343  html += '</table><p><a href="' + SPREADSHEET_URL + '">Open de Sheet</a></p></div>';
344  try {
345    MailApp.sendEmail({ to: EMAIL_RECIPIENTS, subject: '[Google Ads] PMax kanaalverdeling - ' + account.getName(), htmlBody: html });
346  } catch (e) {
347    Logger.log('E-mail versturen mislukt: ' + e);
348  }
349}
350
351function round(value) {
352  return Math.round(Number(value) * 100) / 100;
353}
354
355function escapeHtml(text) {
356  return String(text).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
357}
358