Disapproved Ads Monitor

1/**
2 * Disapproved Ads Monitor
3 *
4 * Zoekt alle actieve advertenties en assets (sitelinks, callouts, afbeeldingen etc.) met
5 * de goedkeuringsstatus DISAPPROVED of APPROVED_LIMITED, inclusief de policy-topics.
6 * Schrijft het overzicht naar een Google Sheet en stuurt een e-mail (alleen als er
7 * problemen zijn, of als er nieuwe problemen zijn t.o.v. de vorige run). Het script
8 * wijzigt niets in het account.
9 *
10 * Versie:     1.3.0
11 * Frequentie: Dagelijks
12 * Niveau:     Account
13 * Licentie:   MIT
14 */
15
16// ===== CONFIGURATIE =====
17
18// Google Sheet voor het overzicht.
19var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/JOUW_SHEET_ID/edit';
20var SHEET_NAME = 'Afgekeurde ads en assets';
21
22// Ontvangers van de alert (komma-gescheiden). Leeg = geen e-mail.
23var EMAIL_RECIPIENTS = 'ads@voorbeeld.nl';
24
25// Slack webhook (optioneel).
26var SLACK_WEBHOOK_URL = '';
27
28// Alleen mailen als er NIEUWE problemen zijn t.o.v. de vorige run (voorkomt dagelijkse herhaling).
29var ONLY_EMAIL_NEW_ISSUES = true;
30
31// Ook advertenties met APPROVED_LIMITED (beperkt goedgekeurd) meenemen.
32var INCLUDE_LIMITED = true;
33
34// Alleen campagnes waarvan de naam deze tekst bevat. Leeg = alle campagnes.
35var CAMPAIGN_NAME_CONTAINS = '';
36
37// ===== EINDE CONFIGURATIE =====
38
39function main() {
40  try {
41    var account = AdsApp.currentAccount();
42    Logger.log('Disapproved monitor voor ' + account.getName() + ' (' + account.getCustomerId() + ')');
43
44    var issues = fetchAdIssues().concat(fetchAssetIssues());
45    Logger.log(issues.length + ' advertenties/assets met beperkingen gevonden.');
46
47    var previousKeys = readPreviousKeys();
48    var newIssues = issues.filter(function (i) { return !previousKeys[i.key]; });
49    Logger.log(newIssues.length + ' daarvan zijn nieuw t.o.v. de vorige run.');
50
51    writeSheet(issues, previousKeys);
52
53    var toReport = ONLY_EMAIL_NEW_ISSUES ? newIssues : issues;
54    if (toReport.length) {
55      sendEmail(account, toReport, issues.length);
56      sendSlack(account, toReport);
57    } else {
58      Logger.log('Geen (nieuwe) problemen om te melden.');
59    }
60  } catch (e) {
61    Logger.log('FOUT in disapproved-ads-monitor: ' + e + (e.stack ? '\n' + e.stack : ''));
62    throw e;
63  }
64}
65
66/**
67 * Advertenties met policy-problemen via ad_group_ad.policy_summary.
68 */
69function fetchAdIssues() {
70  var statuses = INCLUDE_LIMITED ? "('DISAPPROVED', 'APPROVED_LIMITED')" : "('DISAPPROVED')";
71  var query =
72    'SELECT campaign.name, ad_group.name, ad_group_ad.ad.id, ad_group_ad.ad.type, ad_group_ad.ad.name, ' +
73    'ad_group_ad.policy_summary.approval_status, ad_group_ad.policy_summary.review_status, ' +
74    'ad_group_ad.policy_summary.policy_topic_entries, ad_group_ad.ad.final_urls ' +
75    'FROM ad_group_ad ' +
76    "WHERE ad_group_ad.status = 'ENABLED' AND ad_group.status = 'ENABLED' AND campaign.status = 'ENABLED' " +
77    'AND ad_group_ad.policy_summary.approval_status IN ' + statuses;
78  if (CAMPAIGN_NAME_CONTAINS) {
79    query += " AND campaign.name LIKE '%" + CAMPAIGN_NAME_CONTAINS.replace(/'/g, "\\'") + "%'";
80  }
81
82  var issues = [];
83  var rows = AdsApp.search(query);
84  while (rows.hasNext()) {
85    var r = rows.next();
86    var summary = r.adGroupAd.policySummary || {};
87    var topics = (summary.policyTopicEntries || []).map(function (t) { return t.topic + ' (' + t.type + ')'; });
88    issues.push({
89      key: 'AD-' + r.adGroupAd.ad.id,
90      kind: 'Advertentie',
91      type: r.adGroupAd.ad.type,
92      id: String(r.adGroupAd.ad.id),
93      campaign: r.campaign.name,
94      adGroup: r.adGroup.name,
95      name: r.adGroupAd.ad.name || (r.adGroupAd.ad.finalUrls || []).join(', '),
96      approval: summary.approvalStatus,
97      review: summary.reviewStatus,
98      topics: topics.join('; ')
99    });
100  }
101  return issues;
102}
103
104/**
105 * Assets met policy-problemen (sitelinks, callouts, afbeeldingen, etc.) via de asset-resource.
106 * Alleen assets die aan een actieve campagne, adgroep of het account gekoppeld zijn.
107 */
108function fetchAssetIssues() {
109  var statuses = INCLUDE_LIMITED ? "('DISAPPROVED', 'APPROVED_LIMITED')" : "('DISAPPROVED')";
110  var issues = [];
111  var seen = {};
112
113  var queries = [
114    { level: 'Campagne', query: 'SELECT campaign.name, asset.id, asset.type, asset.name, asset.policy_summary.approval_status, asset.policy_summary.review_status, asset.policy_summary.policy_topic_entries, asset.sitelink_asset.link_text, asset.callout_asset.callout_text, asset.text_asset.text FROM campaign_asset WHERE campaign_asset.status = \'ENABLED\' AND campaign.status = \'ENABLED\' AND asset.policy_summary.approval_status IN ' + statuses },
115    { level: 'Advertentiegroep', query: 'SELECT campaign.name, ad_group.name, asset.id, asset.type, asset.name, asset.policy_summary.approval_status, asset.policy_summary.review_status, asset.policy_summary.policy_topic_entries, asset.sitelink_asset.link_text, asset.callout_asset.callout_text, asset.text_asset.text FROM ad_group_asset WHERE ad_group_asset.status = \'ENABLED\' AND ad_group.status = \'ENABLED\' AND campaign.status = \'ENABLED\' AND asset.policy_summary.approval_status IN ' + statuses },
116    { level: 'Account', query: 'SELECT asset.id, asset.type, asset.name, asset.policy_summary.approval_status, asset.policy_summary.review_status, asset.policy_summary.policy_topic_entries, asset.sitelink_asset.link_text, asset.callout_asset.callout_text, asset.text_asset.text FROM customer_asset WHERE customer_asset.status = \'ENABLED\' AND asset.policy_summary.approval_status IN ' + statuses },
117    { level: 'Asset group (PMax)', query: 'SELECT campaign.name, asset_group.name, asset.id, asset.type, asset.name, asset.policy_summary.approval_status, asset.policy_summary.review_status, asset.policy_summary.policy_topic_entries, asset.text_asset.text FROM asset_group_asset WHERE asset_group_asset.status = \'ENABLED\' AND campaign.status = \'ENABLED\' AND asset.policy_summary.approval_status IN ' + statuses }
118  ];
119
120  queries.forEach(function (q) {
121    try {
122      var rows = AdsApp.search(q.query);
123      while (rows.hasNext()) {
124        var r = rows.next();
125        var asset = r.asset;
126        var campaignName = r.campaign ? r.campaign.name : '';
127        if (CAMPAIGN_NAME_CONTAINS && campaignName && campaignName.toLowerCase().indexOf(CAMPAIGN_NAME_CONTAINS.toLowerCase()) === -1) { continue; }
128        var key = 'ASSET-' + asset.id + '-' + q.level + '-' + campaignName;
129        if (seen[key]) { continue; }
130        seen[key] = true;
131        var summary = asset.policySummary || {};
132        var text = asset.name ||
133          (asset.sitelinkAsset && asset.sitelinkAsset.linkText) ||
134          (asset.calloutAsset && asset.calloutAsset.calloutText) ||
135          (asset.textAsset && asset.textAsset.text) || '';
136        issues.push({
137          key: key,
138          kind: 'Asset (' + q.level + ')',
139          type: asset.type,
140          id: String(asset.id),
141          campaign: campaignName,
142          adGroup: r.adGroup ? r.adGroup.name : (r.assetGroup ? r.assetGroup.name : ''),
143          name: text,
144          approval: summary.approvalStatus,
145          review: summary.reviewStatus,
146          topics: (summary.policyTopicEntries || []).map(function (t) { return t.topic + ' (' + t.type + ')'; }).join('; ')
147        });
148      }
149    } catch (e) {
150      Logger.log('Asset-query (' + q.level + ') overgeslagen: ' + e);
151    }
152  });
153  return issues;
154}
155
156/**
157 * Leest de sleutels van de vorige run uit de Sheet om nieuwe problemen te herkennen.
158 */
159function readPreviousKeys() {
160  var keys = {};
161  try {
162    var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
163    var sheet = ss.getSheetByName(SHEET_NAME);
164    if (!sheet || sheet.getLastRow() < 2) { return keys; }
165    var values = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues();
166    values.forEach(function (row) { if (row[0]) { keys[String(row[0])] = true; } });
167  } catch (e) {
168    Logger.log('Kon vorige run niet lezen: ' + e);
169  }
170  return keys;
171}
172
173function writeSheet(issues, previousKeys) {
174  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
175  var sheet = ss.getSheetByName(SHEET_NAME) || ss.insertSheet(SHEET_NAME);
176  sheet.clearContents();
177  var header = ['Sleutel', 'Gecontroleerd op', 'Soort', 'Type', 'ID', 'Campagne', 'Advertentiegroep / asset group', 'Naam / tekst',
178    'Goedkeuring', 'Reviewstatus', 'Policy-topics', 'Nieuw?'];
179  var now = new Date();
180  var values = [header].concat(issues.map(function (i) {
181    return [i.key, now, i.kind, i.type, i.id, i.campaign, i.adGroup, i.name, i.approval, i.review, i.topics, previousKeys[i.key] ? '' : 'NIEUW'];
182  }));
183  sheet.getRange(1, 1, values.length, header.length).setValues(values);
184  sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
185  sheet.setFrozenRows(1);
186  for (var i = 1; i < values.length; i++) {
187    var color = values[i][8] === 'DISAPPROVED' ? '#fdecea' : '#fff8e1';
188    sheet.getRange(i + 1, 1, 1, header.length).setBackground(color);
189  }
190  Logger.log('Sheet bijgewerkt met ' + issues.length + ' regels.');
191}
192
193function sendEmail(account, issues, totalCount) {
194  if (!EMAIL_RECIPIENTS) { return; }
195  var subject = '[Google Ads] ' + issues.length + (ONLY_EMAIL_NEW_ISSUES ? ' nieuwe' : '') + ' afgekeurde/beperkte item(s) - ' + account.getName();
196  var html = '<div style="font-family:Arial,sans-serif;font-size:13px"><p>Totaal ' + totalCount + ' items met beperkingen; hieronder ' +
197    issues.length + '.</p><table cellpadding="5" style="border-collapse:collapse;border:1px solid #ddd">' +
198    '<tr style="background:#f5f5f5"><th align="left">Soort</th><th align="left">Campagne</th><th align="left">Groep</th><th align="left">Naam</th><th>Status</th><th align="left">Policy-topics</th></tr>';
199  issues.forEach(function (i) {
200    var color = i.approval === 'DISAPPROVED' ? '#fdecea' : '#fff8e1';
201    html += '<tr style="background:' + color + '"><td>' + escapeHtml(i.kind + ' / ' + i.type) + '</td><td>' + escapeHtml(i.campaign) + '</td><td>' +
202      escapeHtml(i.adGroup) + '</td><td>' + escapeHtml(i.name) + '</td><td>' + escapeHtml(i.approval) + '</td><td>' + escapeHtml(i.topics) + '</td></tr>';
203  });
204  html += '</table><p><a href="' + SPREADSHEET_URL + '">Volledig overzicht in Sheet</a></p></div>';
205  try {
206    MailApp.sendEmail({ to: EMAIL_RECIPIENTS, subject: subject, htmlBody: html });
207    Logger.log('E-mail verstuurd naar ' + EMAIL_RECIPIENTS);
208  } catch (e) {
209    Logger.log('E-mail versturen mislukt: ' + e);
210  }
211}
212
213function sendSlack(account, issues) {
214  if (!SLACK_WEBHOOK_URL) { return; }
215  var text = ':no_entry: *' + issues.length + ' afgekeurde/beperkte item(s) in ' + account.getName() + '*\n' +
216    issues.slice(0, 10).map(function (i) { return '- ' + i.kind + ' | ' + i.campaign + ' | ' + i.name + ' -> ' + i.approval + (i.topics ? ' (' + i.topics + ')' : ''); }).join('\n');
217  try {
218    UrlFetchApp.fetch(SLACK_WEBHOOK_URL, { method: 'post', contentType: 'application/json', payload: JSON.stringify({ text: text }), muteHttpExceptions: true });
219  } catch (e) {
220    Logger.log('Slack versturen mislukt: ' + e);
221  }
222}
223
224function escapeHtml(text) {
225  return String(text === undefined || text === null ? '' : text).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
226}
227