Quality Score Tracker

1/**
2 * Quality Score Tracker
3 *
4 * Maakt dagelijks een snapshot van de Quality Score en de drie componenten
5 * (verwachte CTR, advertentierelevantie, ervaring op landingspagina) per zoekwoord en
6 * schrijft die weg naar een Google Sheet. Er wordt een historietabblad opgebouwd en per
7 * campagne een impressie-gewogen QS berekend, zodat trends zichtbaar worden. Het script
8 * wijzigt niets in het account.
9 *
10 * Versie:     1.2.0
11 * Frequentie: Dagelijks
12 * Niveau:     Account
13 * Licentie:   MIT
14 */
15
16// ===== CONFIGURATIE =====
17
18// Google Sheet waarin de snapshots komen.
19var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/JOUW_SHEET_ID/edit';
20
21// Namen van de tabbladen.
22var SHEET_NAMES = {
23  keywords: 'QS Keywords (vandaag)',   // snapshot van vandaag per zoekwoord
24  history: 'QS Historie',              // dagelijkse regel per zoekwoord (groeit)
25  campaigns: 'QS per campagne'         // gewogen QS per campagne per dag
26};
27
28// Alleen zoekwoorden uit campagnes waarvan de naam deze tekst bevat. Leeg = alle.
29var CAMPAIGN_NAME_CONTAINS = '';
30
31// Alleen zoekwoorden met minimaal dit aantal impressies in de afgelopen 30 dagen.
32var MIN_IMPRESSIONS_30D = 10;
33
34// Aantal dagen historie dat bewaard blijft in het historietabblad (oudere regels worden verwijderd).
35var HISTORY_RETENTION_DAYS = 120;
36
37// Optioneel: e-mail bij daling van de gewogen account-QS met meer dan dit aantal punten t.o.v. gisteren. 0 = uit.
38var ALERT_DROP_POINTS = 0.5;
39var EMAIL_RECIPIENTS = '';
40
41// ===== EINDE CONFIGURATIE =====
42
43function main() {
44  try {
45    var account = AdsApp.currentAccount();
46    var timezone = account.getTimeZone();
47    var today = Utilities.formatDate(new Date(), timezone, 'yyyy-MM-dd');
48    Logger.log('Quality Score snapshot ' + today + ' voor ' + account.getName());
49
50    var keywords = fetchKeywords();
51    Logger.log(keywords.length + ' zoekwoorden met QS-gegevens.');
52
53    var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
54    writeKeywordSnapshot(ss, keywords, today);
55    appendHistory(ss, keywords, today, timezone);
56    var campaignStats = computeCampaignWeightedQs(keywords);
57    var previousAccountQs = appendCampaignHistory(ss, campaignStats, today);
58
59    var accountQs = weightedAverage(keywords);
60    Logger.log('Gewogen account-QS: ' + accountQs.toFixed(2) + (previousAccountQs !== null ? ' (gisteren ' + previousAccountQs.toFixed(2) + ')' : ''));
61
62    if (ALERT_DROP_POINTS > 0 && EMAIL_RECIPIENTS && previousAccountQs !== null && previousAccountQs - accountQs >= ALERT_DROP_POINTS) {
63      sendAlert(account, accountQs, previousAccountQs, campaignStats);
64    }
65    Logger.log('Klaar: ' + SPREADSHEET_URL);
66  } catch (e) {
67    Logger.log('FOUT in quality-score-tracker: ' + e + (e.stack ? '\n' + e.stack : ''));
68    throw e;
69  }
70}
71
72/**
73 * Haalt zoekwoorden met QS-componenten en 30-daagse impressies op.
74 */
75function fetchKeywords() {
76  var query =
77    'SELECT campaign.id, campaign.name, ad_group.name, ad_group_criterion.criterion_id, ' +
78    'ad_group_criterion.keyword.text, ad_group_criterion.keyword.match_type, ' +
79    'ad_group_criterion.quality_info.quality_score, ' +
80    'ad_group_criterion.quality_info.search_predicted_ctr, ' +
81    'ad_group_criterion.quality_info.creative_quality_score, ' +
82    'ad_group_criterion.quality_info.post_click_quality_score, ' +
83    'metrics.impressions, metrics.clicks, metrics.cost_micros ' +
84    'FROM keyword_view ' +
85    'WHERE segments.date DURING LAST_30_DAYS ' +
86    "AND campaign.status = 'ENABLED' AND ad_group.status = 'ENABLED' AND ad_group_criterion.status = 'ENABLED' " +
87    'AND metrics.impressions >= ' + MIN_IMPRESSIONS_30D;
88  if (CAMPAIGN_NAME_CONTAINS) {
89    query += " AND campaign.name LIKE '%" + CAMPAIGN_NAME_CONTAINS.replace(/'/g, "\\'") + "%'";
90  }
91
92  var rows = AdsApp.report(query).rows();
93  var result = [];
94  while (rows.hasNext()) {
95    var row = rows.next();
96    var qs = Number(row['ad_group_criterion.quality_info.quality_score']);
97    if (!qs || isNaN(qs)) { continue; } // geen QS beschikbaar (te weinig data)
98    result.push({
99      campaignId: row['campaign.id'],
100      campaign: row['campaign.name'],
101      adGroup: row['ad_group.name'],
102      criterionId: row['ad_group_criterion.criterion_id'],
103      keyword: row['ad_group_criterion.keyword.text'],
104      matchType: row['ad_group_criterion.keyword.match_type'],
105      qs: qs,
106      expectedCtr: translateComponent(row['ad_group_criterion.quality_info.search_predicted_ctr']),
107      adRelevance: translateComponent(row['ad_group_criterion.quality_info.creative_quality_score']),
108      landingPage: translateComponent(row['ad_group_criterion.quality_info.post_click_quality_score']),
109      impressions: Number(row['metrics.impressions']),
110      clicks: Number(row['metrics.clicks']),
111      cost: Number(row['metrics.cost_micros']) / 1000000
112    });
113  }
114  return result;
115}
116
117function translateComponent(value) {
118  var map = { BELOW_AVERAGE: 'Onder gemiddeld', AVERAGE: 'Gemiddeld', ABOVE_AVERAGE: 'Boven gemiddeld' };
119  return map[value] || '-';
120}
121
122function writeKeywordSnapshot(ss, keywords, today) {
123  var sheet = getSheet(ss, SHEET_NAMES.keywords);
124  sheet.clearContents();
125  var header = ['Datum', 'Campagne', 'Advertentiegroep', 'Zoekwoord', 'Matchtype', 'QS', 'Verwachte CTR',
126    'Advertentierelevantie', 'Landingspagina', 'Impressies 30d', 'Klikken 30d', 'Kosten 30d'];
127  var values = [header];
128  keywords.sort(function (a, b) { return b.impressions - a.impressions; });
129  keywords.forEach(function (k) {
130    values.push([today, k.campaign, k.adGroup, k.keyword, k.matchType, k.qs, k.expectedCtr, k.adRelevance,
131      k.landingPage, k.impressions, k.clicks, round(k.cost)]);
132  });
133  sheet.getRange(1, 1, values.length, header.length).setValues(values);
134  sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
135  sheet.setFrozenRows(1);
136  // Kleur QS-kolom: laag rood, hoog groen.
137  for (var i = 1; i < values.length; i++) {
138    var qs = values[i][5];
139    var color = qs <= 4 ? '#fdecea' : (qs >= 8 ? '#e6f4ea' : '#ffffff');
140    sheet.getRange(i + 1, 6).setBackground(color);
141  }
142}
143
144/**
145 * Voegt de snapshot van vandaag toe aan de historie en verwijdert regels ouder dan de retentie.
146 */
147function appendHistory(ss, keywords, today, timezone) {
148  var sheet = getSheet(ss, SHEET_NAMES.history);
149  var header = ['Datum', 'Campagne', 'Advertentiegroep', 'Criterion ID', 'Zoekwoord', 'Matchtype', 'QS',
150    'Verwachte CTR', 'Advertentierelevantie', 'Landingspagina', 'Impressies 30d'];
151  if (sheet.getLastRow() === 0) {
152    sheet.appendRow(header);
153    sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
154    sheet.setFrozenRows(1);
155  }
156
157  // Verwijder eerdere regels van vandaag (bij herhaalde run) en oude regels.
158  var cutoff = new Date();
159  cutoff.setDate(cutoff.getDate() - HISTORY_RETENTION_DAYS);
160  var cutoffStr = Utilities.formatDate(cutoff, timezone, 'yyyy-MM-dd');
161  var lastRow = sheet.getLastRow();
162  if (lastRow > 1) {
163    var dates = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
164    var keep = [];
165    var data = sheet.getRange(2, 1, lastRow - 1, header.length).getValues();
166    for (var i = 0; i < data.length; i++) {
167      var d = normalizeDate(dates[i][0], timezone);
168      if (d !== today && d >= cutoffStr) { keep.push(data[i]); }
169    }
170    sheet.getRange(2, 1, lastRow - 1, header.length).clearContent();
171    if (keep.length) { sheet.getRange(2, 1, keep.length, header.length).setValues(keep); }
172  }
173
174  var values = keywords.map(function (k) {
175    return [today, k.campaign, k.adGroup, k.criterionId, k.keyword, k.matchType, k.qs, k.expectedCtr, k.adRelevance,
176      k.landingPage, k.impressions];
177  });
178  if (values.length) {
179    var start = sheet.getLastRow() + 1;
180    sheet.getRange(start, 1, values.length, header.length).setValues(values);
181  }
182  Logger.log(values.length + ' historieregels toegevoegd.');
183}
184
185function computeCampaignWeightedQs(keywords) {
186  var byCampaign = {};
187  keywords.forEach(function (k) {
188    if (!byCampaign[k.campaign]) {
189      byCampaign[k.campaign] = { campaign: k.campaign, weighted: 0, impressions: 0, keywords: 0, low: 0 };
190    }
191    var c = byCampaign[k.campaign];
192    c.weighted += k.qs * k.impressions;
193    c.impressions += k.impressions;
194    c.keywords += 1;
195    if (k.qs <= 4) { c.low += 1; }
196  });
197  var list = Object.keys(byCampaign).map(function (name) {
198    var c = byCampaign[name];
199    c.qs = c.impressions > 0 ? c.weighted / c.impressions : 0;
200    return c;
201  });
202  list.sort(function (a, b) { return b.impressions - a.impressions; });
203  return list;
204}
205
206/**
207 * Schrijft per-campagne gewogen QS als nieuwe kolom-regel in de campagnehistorie.
208 * Geeft de gewogen account-QS van de vorige run terug (of null).
209 */
210function appendCampaignHistory(ss, campaignStats, today) {
211  var sheet = getSheet(ss, SHEET_NAMES.campaigns);
212  var header = ['Datum', 'Campagne', 'Gewogen QS', 'Zoekwoorden', 'Zoekwoorden QS <= 4', 'Impressies 30d'];
213  if (sheet.getLastRow() === 0) {
214    sheet.appendRow(header);
215    sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
216    sheet.setFrozenRows(1);
217  }
218
219  var previousAccountQs = null;
220  var lastRow = sheet.getLastRow();
221  if (lastRow > 1) {
222    var data = sheet.getRange(2, 1, lastRow - 1, header.length).getValues();
223    for (var i = data.length - 1; i >= 0; i--) {
224      if (data[i][1] === 'ACCOUNT TOTAAL' && normalizeDate(data[i][0], AdsApp.currentAccount().getTimeZone()) !== today) {
225        previousAccountQs = Number(data[i][2]);
226        break;
227      }
228    }
229  }
230
231  var totalWeighted = 0;
232  var totalImpr = 0;
233  var totalKw = 0;
234  var totalLow = 0;
235  var values = campaignStats.map(function (c) {
236    totalWeighted += c.weighted;
237    totalImpr += c.impressions;
238    totalKw += c.keywords;
239    totalLow += c.low;
240    return [today, c.campaign, round(c.qs), c.keywords, c.low, c.impressions];
241  });
242  values.push([today, 'ACCOUNT TOTAAL', totalImpr > 0 ? round(totalWeighted / totalImpr) : 0, totalKw, totalLow, totalImpr]);
243  sheet.getRange(sheet.getLastRow() + 1, 1, values.length, header.length).setValues(values);
244  return previousAccountQs;
245}
246
247function weightedAverage(keywords) {
248  var w = 0;
249  var n = 0;
250  keywords.forEach(function (k) { w += k.qs * k.impressions; n += k.impressions; });
251  return n > 0 ? w / n : 0;
252}
253
254function sendAlert(account, accountQs, previousQs, campaignStats) {
255  var subject = '[Google Ads] Quality Score gedaald: ' + previousQs.toFixed(2) + ' -> ' + accountQs.toFixed(2) + ' (' + account.getName() + ')';
256  var html = '<p>De impressie-gewogen Quality Score van het account is gedaald van <b>' + previousQs.toFixed(2) +
257    '</b> naar <b>' + accountQs.toFixed(2) + '</b>.</p><table cellpadding="5" style="border-collapse:collapse;border:1px solid #ddd">' +
258    '<tr style="background:#f5f5f5"><th align="left">Campagne</th><th>Gewogen QS</th><th>Zoekwoorden QS &lt;= 4</th></tr>';
259  campaignStats.slice(0, 25).forEach(function (c) {
260    html += '<tr><td>' + escapeHtml(c.campaign) + '</td><td align="right">' + c.qs.toFixed(2) + '</td><td align="right">' + c.low + '</td></tr>';
261  });
262  html += '</table><p><a href="' + SPREADSHEET_URL + '">Open de Sheet</a></p>';
263  try {
264    MailApp.sendEmail({ to: EMAIL_RECIPIENTS, subject: subject, htmlBody: html });
265    Logger.log('Alert-e-mail verstuurd.');
266  } catch (e) {
267    Logger.log('E-mail versturen mislukt: ' + e);
268  }
269}
270
271function getSheet(ss, name) {
272  return ss.getSheetByName(name) || ss.insertSheet(name);
273}
274
275function normalizeDate(value, timezone) {
276  if (value instanceof Date) { return Utilities.formatDate(value, timezone, 'yyyy-MM-dd'); }
277  return String(value).substring(0, 10);
278}
279
280function round(value) {
281  return Math.round(Number(value) * 100) / 100;
282}
283
284function escapeHtml(text) {
285  return String(text).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
286}
287