RSA Asset Performance

1/**
2 * RSA Asset Performance
3 *
4 * Leest per responsieve zoekadvertentie (RSA) alle koppen en beschrijvingen met hun
5 * prestatielabel (BEST / GOOD / LOW / LEARNING / PENDING) en de Ad Strength. Schrijft een
6 * overzicht per asset en per advertentie naar een Google Sheet, inclusief een
7 * vervangingsadvies: LOW-assets die al lang meelopen vervangen, te weinig assets aanvullen,
8 * vastgepinde assets heroverwegen. Het script wijzigt niets in het account.
9 *
10 * Versie:     1.2.0
11 * Frequentie: Wekelijks of maandelijks
12 * Niveau:     Account
13 * Licentie:   MIT
14 */
15
16// ===== CONFIGURATIE =====
17
18// Google Sheet voor het rapport.
19var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/JOUW_SHEET_ID/edit';
20var SHEET_NAMES = { assets: 'RSA assets', ads: 'RSA advertenties', advice: 'Vervangingsadvies' };
21
22// Periode voor de advertentiestatistieken.
23var DATE_RANGE = 'LAST_30_DAYS';
24
25// Alleen campagnes waarvan de naam deze tekst bevat. Leeg = alle Search-campagnes.
26var CAMPAIGN_NAME_CONTAINS = '';
27
28// Alleen advertenties met minimaal dit aantal impressies in de periode (anders is het label niet betrouwbaar).
29var MIN_IMPRESSIONS = 500;
30
31// Drempels voor het advies.
32var THRESHOLDS = {
33  minHeadlines: 8,          // adviseer aanvullen bij minder koppen
34  minDescriptions: 3,       // adviseer aanvullen bij minder beschrijvingen
35  maxLowShare: 0.25         // adviseer herschrijven als > 25% van de assets LOW is
36};
37
38// Optioneel e-mailoverzicht met het vervangingsadvies. Leeg = geen e-mail.
39var EMAIL_RECIPIENTS = '';
40
41// ===== EINDE CONFIGURATIE =====
42
43var LABEL_TRANSLATION = { BEST: 'BEST', GOOD: 'GOOD', LOW: 'LOW', LEARNING: 'LEARNING', PENDING: 'PENDING', UNSPECIFIED: 'ONBEKEND', UNKNOWN: 'ONBEKEND' };
44
45function main() {
46  try {
47    var account = AdsApp.currentAccount();
48    Logger.log('RSA asset performance voor ' + account.getName() + ' | ' + DATE_RANGE);
49
50    var ads = fetchRsaAds();
51    Logger.log(ads.length + ' RSA\'s met >= ' + MIN_IMPRESSIONS + ' impressies.');
52    if (!ads.length) { return; }
53
54    var assetRows = [];
55    var adRows = [];
56    var advice = [];
57    ads.forEach(function (ad) {
58      var analysis = analyzeAd(ad);
59      adRows.push(analysis.adRow);
60      assetRows = assetRows.concat(analysis.assetRows);
61      if (analysis.advice.length) {
62        advice.push({ ad: ad, items: analysis.advice });
63      }
64    });
65
66    var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
67    writeAssets(ss, assetRows);
68    writeAds(ss, adRows);
69    writeAdvice(ss, advice);
70    sendEmail(account, advice);
71    Logger.log(advice.length + ' advertenties met vervangingsadvies. Klaar: ' + SPREADSHEET_URL);
72  } catch (e) {
73    Logger.log('FOUT in rsa-asset-performance: ' + e + (e.stack ? '\n' + e.stack : ''));
74    throw e;
75  }
76}
77
78/**
79 * Haalt RSA's op met headlines/descriptions (inclusief asset_performance_label) via AdsApp.search,
80 * omdat herhaalde velden daar als arrays beschikbaar zijn.
81 */
82function fetchRsaAds() {
83  var query =
84    'SELECT campaign.name, ad_group.name, ad_group_ad.ad.id, ad_group_ad.ad_strength, ad_group_ad.status, ' +
85    'ad_group_ad.ad.responsive_search_ad.headlines, ad_group_ad.ad.responsive_search_ad.descriptions, ' +
86    'ad_group_ad.ad.responsive_search_ad.path1, ad_group_ad.ad.responsive_search_ad.path2, ' +
87    'metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions ' +
88    'FROM ad_group_ad ' +
89    "WHERE ad_group_ad.ad.type = 'RESPONSIVE_SEARCH_AD' AND ad_group_ad.status = 'ENABLED' AND ad_group.status = 'ENABLED' AND campaign.status = 'ENABLED' " +
90    'AND segments.date DURING ' + DATE_RANGE + ' AND metrics.impressions >= ' + MIN_IMPRESSIONS;
91  if (CAMPAIGN_NAME_CONTAINS) {
92    query += " AND campaign.name LIKE '%" + CAMPAIGN_NAME_CONTAINS.replace(/'/g, "\\'") + "%'";
93  }
94  query += ' ORDER BY metrics.impressions DESC';
95
96  var ads = [];
97  var rows = AdsApp.search(query);
98  while (rows.hasNext()) {
99    var r = rows.next();
100    var rsa = r.adGroupAd.ad.responsiveSearchAd || {};
101    ads.push({
102      campaign: r.campaign.name,
103      adGroup: r.adGroup.name,
104      adId: String(r.adGroupAd.ad.id),
105      adStrength: r.adGroupAd.adStrength || '',
106      headlines: (rsa.headlines || []).map(normalizeAsset),
107      descriptions: (rsa.descriptions || []).map(normalizeAsset),
108      impressions: Number(r.metrics.impressions || 0),
109      clicks: Number(r.metrics.clicks || 0),
110      cost: Number(r.metrics.costMicros || 0) / 1000000,
111      conversions: Number(r.metrics.conversions || 0)
112    });
113  }
114  return ads;
115}
116
117function normalizeAsset(asset) {
118  return {
119    text: asset.text || '',
120    label: LABEL_TRANSLATION[asset.assetPerformanceLabel] || 'ONBEKEND',
121    pinned: asset.pinnedField && asset.pinnedField !== 'UNSPECIFIED' ? asset.pinnedField : ''
122  };
123}
124
125/**
126 * Analyseert één advertentie: telt labels, bouwt asset-regels en advies.
127 */
128function analyzeAd(ad) {
129  var counts = { BEST: 0, GOOD: 0, LOW: 0, LEARNING: 0, PENDING: 0, ONBEKEND: 0 };
130  var pinned = 0;
131  var assetRows = [];
132
133  function handle(list, kind) {
134    list.forEach(function (a) {
135      counts[a.label] = (counts[a.label] || 0) + 1;
136      if (a.pinned) { pinned++; }
137      var action = a.label === 'LOW' ? 'Vervangen' : (a.label === 'BEST' ? 'Behouden / hergebruiken' : (a.label === 'GOOD' ? 'Behouden' : 'Afwachten'));
138      assetRows.push([ad.campaign, ad.adGroup, ad.adId, kind, a.text, a.text.length, a.label, a.pinned, action]);
139    });
140  }
141  handle(ad.headlines, 'Kop');
142  handle(ad.descriptions, 'Beschrijving');
143
144  var totalAssets = ad.headlines.length + ad.descriptions.length;
145  var lowShare = totalAssets > 0 ? counts.LOW / totalAssets : 0;
146  var advice = [];
147  if (counts.LOW > 0) {
148    var lowTexts = ad.headlines.concat(ad.descriptions).filter(function (a) { return a.label === 'LOW'; }).map(function (a) { return '"' + a.text + '"'; });
149    advice.push('Vervang ' + counts.LOW + ' LOW-asset(s): ' + lowTexts.join(', ') + '. Gebruik varianten van BEST-assets als inspiratie.');
150  }
151  if (ad.headlines.length < THRESHOLDS.minHeadlines) {
152    advice.push('Slechts ' + ad.headlines.length + ' koppen; vul aan tot minimaal ' + THRESHOLDS.minHeadlines + ' (max 15) voor meer combinaties.');
153  }
154  if (ad.descriptions.length < THRESHOLDS.minDescriptions) {
155    advice.push('Slechts ' + ad.descriptions.length + ' beschrijvingen; vul aan tot minimaal ' + THRESHOLDS.minDescriptions + ' (max 4).');
156  }
157  if (lowShare > THRESHOLDS.maxLowShare) {
158    advice.push((lowShare * 100).toFixed(0) + '% van de assets is LOW; overweeg de advertentie grondig te herschrijven.');
159  }
160  if (pinned >= 3 && ad.adStrength && ['POOR', 'AVERAGE'].indexOf(ad.adStrength) !== -1) {
161    advice.push(pinned + ' vastgepinde assets beperken de combinaties; heroverweeg pinnen (Ad Strength ' + ad.adStrength + ').');
162  }
163  if (ad.adStrength === 'POOR') {
164    advice.push('Ad Strength is POOR: voeg unieke koppen toe met zoekwoorden en USP\'s.');
165  }
166
167  var adRow = [ad.campaign, ad.adGroup, ad.adId, ad.adStrength, ad.headlines.length, ad.descriptions.length, counts.BEST, counts.GOOD, counts.LOW, counts.LEARNING + counts.PENDING,
168    pinned, ad.impressions, ad.clicks, ad.impressions > 0 ? ad.clicks / ad.impressions : 0, round(ad.cost), round(ad.conversions), advice.length ? 'JA' : ''];
169  return { adRow: adRow, assetRows: assetRows, advice: advice };
170}
171
172function writeAssets(ss, rows) {
173  var sheet = ss.getSheetByName(SHEET_NAMES.assets) || ss.insertSheet(SHEET_NAMES.assets);
174  sheet.clearContents();
175  sheet.clearFormats();
176  var header = ['Campagne', 'Advertentiegroep', 'Ad ID', 'Type', 'Tekst', 'Lengte', 'Prestatielabel', 'Vastgepind', 'Actie'];
177  var values = [header].concat(rows);
178  sheet.getRange(1, 1, values.length, header.length).setValues(values);
179  sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
180  sheet.setFrozenRows(1);
181  var colors = { BEST: '#e6f4ea', GOOD: '#f1f8e9', LOW: '#fdecea', LEARNING: '#fff8e1', PENDING: '#f5f5f5', ONBEKEND: '#ffffff' };
182  var backgrounds = rows.map(function (r) { return [colors[r[6]] || '#ffffff']; });
183  if (backgrounds.length) { sheet.getRange(2, 7, backgrounds.length, 1).setBackgrounds(backgrounds); }
184  sheet.getRange(1, header.length + 2).setValue('Periode ' + DATE_RANGE + ' | ' + new Date());
185}
186
187function writeAds(ss, rows) {
188  var sheet = ss.getSheetByName(SHEET_NAMES.ads) || ss.insertSheet(SHEET_NAMES.ads);
189  sheet.clearContents();
190  var header = ['Campagne', 'Advertentiegroep', 'Ad ID', 'Ad Strength', 'Koppen', 'Beschrijvingen', 'BEST', 'GOOD', 'LOW', 'LEARNING/PENDING', 'Vastgepind',
191    'Impressies', 'Klikken', 'CTR', 'Kosten', 'Conversies', 'Advies?'];
192  var values = [header].concat(rows);
193  sheet.getRange(1, 1, values.length, header.length).setValues(values);
194  sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
195  sheet.setFrozenRows(1);
196  if (values.length > 1) { sheet.getRange(2, 14, values.length - 1, 1).setNumberFormat('0.00%'); }
197}
198
199function writeAdvice(ss, advice) {
200  var sheet = ss.getSheetByName(SHEET_NAMES.advice) || ss.insertSheet(SHEET_NAMES.advice);
201  sheet.clearContents();
202  var header = ['Campagne', 'Advertentiegroep', 'Ad ID', 'Ad Strength', 'Impressies', 'Advies'];
203  var values = [header];
204  advice.forEach(function (a) {
205    a.items.forEach(function (text) { values.push([a.ad.campaign, a.ad.adGroup, a.ad.adId, a.ad.adStrength, a.ad.impressions, text]); });
206  });
207  sheet.getRange(1, 1, values.length, header.length).setValues(values);
208  sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
209  sheet.setFrozenRows(1);
210}
211
212function sendEmail(account, advice) {
213  if (!EMAIL_RECIPIENTS || !advice.length) { return; }
214  var html = '<div style="font-family:Arial,sans-serif;font-size:13px"><h3>RSA vervangingsadvies ' + escapeHtml(account.getName()) + '</h3><ul>';
215  advice.slice(0, 30).forEach(function (a) {
216    html += '<li><b>' + escapeHtml(a.ad.campaign + ' > ' + a.ad.adGroup) + '</b> (Ad ' + a.ad.adId + ', ' + a.ad.adStrength + ')<ul>' +
217      a.items.map(function (t) { return '<li>' + escapeHtml(t) + '</li>'; }).join('') + '</ul></li>';
218  });
219  html += '</ul><p><a href="' + SPREADSHEET_URL + '">Open de Sheet</a></p></div>';
220  try {
221    MailApp.sendEmail({ to: EMAIL_RECIPIENTS, subject: '[Google Ads] RSA asset advies - ' + account.getName(), htmlBody: html });
222  } catch (e) {
223    Logger.log('E-mail versturen mislukt: ' + e);
224  }
225}
226
227function round(value) {
228  return Math.round(Number(value) * 100) / 100;
229}
230
231function escapeHtml(text) {
232  return String(text).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
233}
234