Skip to content

Instantly share code, notes, and snippets.

@stevenbank

stevenbank/policy_issues_by_email.js

Forked from nakitadog/policy_issues_by_email.js
Created June 17, 2022 02:56
Show Gist options
  • Save stevenbank/5715b9104a86cc76a27ca0eca684ddf1 to your computer and use it in GitHub Desktop.
Save stevenbank/5715b9104a86cc76a27ca0eca684ddf1 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @nakitadog nakitadog created this gist Jul 1, 2021.
    385 changes: 385 additions & 0 deletions policy_issues_by_email.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,385 @@
    //Enter your email address where you want the email to be sent.
    var RECIPIENT_EMAIL = "[email protected]";

    //Enter the subject of the email.
    var EMAIL_SUBJECT = 'Google Ads - Checked for policy issues.';

    //Enter the label for all the accounts you wish to analyze.
    var ACCOUNT_LABEL_TO_CHECK = "Monitor";

    //Make sure that you update the getPolicyManagerURL function with your hardcoded OCID values


    function main() {
    var accountSelector = AdsManagerApp
    .accounts()
    .withLimit(20)
    .withCondition('LabelNames CONTAINS "' + ACCOUNT_LABEL_TO_CHECK +'"');

    var results = [];
    var accountIterator = accountSelector.get();
    while (accountIterator.hasNext()) {
    var account = accountIterator.next();
    AdsManagerApp.select(account);

    var accountName = account.getName();
    var accountId = account.getCustomerId();
    Logger.log(accountName + ' (' + accountId + ') - ' + 'Starting to process the account.');

    var Ad_Policy_Issues = Check_For_Ad_Policy_Issues(account);
    var Extension_Policy_Issues = Check_For_Ad_Extension_Policy_Issues(account);

    results.push({
    "accountName":accountName,
    "accountId":accountId,
    "Ad_Policy_Issues":Ad_Policy_Issues,
    "Extension_Policy_Issues":Extension_Policy_Issues
    });

    Logger.log(accountName + ' (' + accountId + ') - ' + 'Finished processing account.');
    }

    results.sort(compareValues("accountName","asc"));

    //Now send the email report.
    SendEmailReport(BuildEmail(results));

    Logger.log('Finished processing all accounts.');
    }

    function BuildEmail(results) {
    //This function will receive the results and build the HTML formatted email.
    var strHTMLBody = "<p>Checked each account for ad and ad extension policy issues.</p>" +
    "<p>Filters used were: Campaign status: All enabled; Ad group status: All enabled; Ad status: All enabled.</p>" +
    "<p>This report ran on: " + Utilities.formatDate(new Date(), "America/Chicago", "EEE, MMM d, yyyy") +"</p>\n\n";

    var HeaderStyle = "font-family:Arial,sans-serif;font-size:14px;font-weight:normal;padding:5px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#000000;color:#ffffff;background-color:#343434;text-align:left;vertical-align:top";
    var ClientHeaderStyle = "font-family:Arial,sans-serif;font-size:14px;font-weight:normal;padding:5px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#000000;color:#ffffff;background-color:#b9b9b9;text-align:left;vertical-align:top";
    var RawDataRowStyleNormal = "font-family:Arial,sans-serif;font-weight:normal;padding:10px 10px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#000000;color:#333;background-color:#fff;text-align:left;vertical-align:top";
    var RawDataRowStyleError = "font-family:Arial,sans-serif;font-weight:normal;padding:10px 10px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#000000;color:#333;background-color:#f59a9a;text-align:left;vertical-align:top";

    strHTMLBody += '<table style="border-collapse:collapse;border-spacing:0;border-color:#ccc">\n';

    //Write out the header row
    strHTMLBody += '<tr>\n'+
    '<th style="' + HeaderStyle + '">Client</th>\n' +
    '<th style="' + HeaderStyle + '">Ad Policy Issues</th>\n' +
    '<th style="' + HeaderStyle + '">Ad Extension Policy Issues</th>\n' +
    '<th style="' + HeaderStyle + '">Policy Manager Link</th>\n' +
    '</tr>\n';

    for (i in results) {
    var accountName = results[i].accountName;
    var accountId = results[i].accountId;
    var Ad_Policy_Issues = results[i].Ad_Policy_Issues;
    var Extension_Policy_Issues = results[i].Extension_Policy_Issues;

    Logger.log(accountName + ' (' + accountId + ') - ' + JSON.stringify(Ad_Policy_Issues) + ' - ' + JSON.stringify(Extension_Policy_Issues));

    var Ad_Policy_Issues_Text = "";
    var Extension_Policy_Issues_Text = "";

    if (Ad_Policy_Issues.length > 0){
    for (i in Ad_Policy_Issues) {
    Ad_Policy_Issues_Text += Ad_Policy_Issues[i].policyIssue + ": (" +
    ((Ad_Policy_Issues[i].approvalStatus != "Approved Limited") ? "<strong><font color=\"red\">" + Ad_Policy_Issues[i].approvalStatus + "</font></strong>" : Ad_Policy_Issues[i].approvalStatus) + "): " +
    "<strong>" + Ad_Policy_Issues[i].count + "</strong><br>\n";
    }
    } else {
    Ad_Policy_Issues_Text = "<strong><font color=\"green\">No ad policy issues.</font></strong>";
    }

    if (Extension_Policy_Issues.length > 0){
    for (i in Extension_Policy_Issues) {
    Extension_Policy_Issues_Text += Extension_Policy_Issues[i].policyIssue + ": (" +
    ((Extension_Policy_Issues[i].approvalStatus != "Approved Limited") ? "<strong><font color=\"red\">" + Extension_Policy_Issues[i].approvalStatus + "</font></strong>" : Extension_Policy_Issues[i].approvalStatus) + "): " +
    "<strong>" + Extension_Policy_Issues[i].count + "</strong><br>\n";
    }
    } else {
    Extension_Policy_Issues_Text = "<strong><font color=\"green\">No ad extension policy issues.</font></strong>";
    }


    var policyManagerURL = getPolicyManagerURL(accountId);

    strHTMLBody += '<tr>\n'+
    '<td style="' + RawDataRowStyleNormal + '">' + accountName + ' (' + accountId + ')' + '</td>\n' +
    '<td style="' + ((Ad_Policy_Issues.length == 0) ? RawDataRowStyleNormal : RawDataRowStyleError) + '">' + Ad_Policy_Issues_Text + '</td>\n' +
    '<td style="' + ((Extension_Policy_Issues.length == 0) ? RawDataRowStyleNormal : RawDataRowStyleError) + '">' + Extension_Policy_Issues_Text + '</td>\n' +
    '<td style="' + RawDataRowStyleNormal + '"><a href="' + policyManagerURL + '">Policy issues</a> ' + ((policyManagerURL.indexOf('00000000')>-1)?'<small style="color:red;">missing</small>':'') + ' <br /><a href="' + policyManagerURL.replace("policymanager/issues", "policymanager/resubmit") + '">Appeal history</a> ' + ((policyManagerURL.indexOf('00000000')>-1)?'<small style="color:red;">missing</small>':'') + '</td>\n' +
    '</tr>\n';

    }
    strHTMLBody += '</table><br /><br />\n\n';

    return strHTMLBody;
    }

    function SendEmailReport(strHTMLBody){

    // Process your client account here.
    if (RECIPIENT_EMAIL != '') {
    //now send.
    //Logger.log('Sending email to %s this is the body: %s',RECIPIENT_EMAIL, strHTMLBody);
    MailApp.sendEmail({
    to: RECIPIENT_EMAIL,
    subject: EMAIL_SUBJECT,
    htmlBody: strHTMLBody
    });
    }
    Logger.log('Finished sending the report!');
    }


    function Check_For_Ad_Policy_Issues(account){
    //This function will look for ads with any policy issues.

    var Ad_Policy_Issues = [];
    var Ad_ids = [];
    var accountName = account.getName();
    var accountId = account.getCustomerId();

    Logger.log(accountName + ' (' + accountId + ') - ' + 'Starting to check for not fully approved ads.');

    var query = 'SELECT ad_group_ad.ad.name, ad_group_ad.ad.final_urls, ad_group_ad.ad.type, ' +
    'ad_group_ad.policy_summary.approval_status,ad_group_ad.policy_summary.policy_topic_entries, ' +
    'ad_group_ad.ad.id, ad_group_ad.status, ad_group_ad.ad.type,ad_group_ad.policy_summary.review_status ' +
    'FROM ad_group_ad ' +
    'WHERE campaign.status = "ENABLED" AND ad_group.status = "ENABLED" AND ad_group_ad.status = "ENABLED" ' +
    'ORDER BY ad_group_ad.ad.type, ad_group_ad.ad.id ';

    try {

    var result = AdsApp.search(query);

    while (result.hasNext()) {
    var row = result.next();
    var policySummary = row.adGroupAd.policySummary;

    if (policySummary.approvalStatus != "APPROVED"){
    //Logger.log(JSON.stringify(row));

    var ad_type = row.adGroupAd.ad.type;
    var ad_id = row.adGroupAd.ad.id;
    Ad_ids.push(ad_id);

    var finalURLs = row.adGroupAd.ad.finalUrls;

    //Logger.log("policySummary:" + policySummary + " - policySummary.policyTopicEntries:" + policySummary.policyTopicEntries );

    if (policySummary.policyTopicEntries){
    for (var i = 0;i < policySummary.policyTopicEntries.length; i++) {
    var policyIssue = normalize_text(policySummary.policyTopicEntries[i].topic);
    var approvalStatus = normalize_text(policySummary.approvalStatus);

    var pos = Ad_Policy_Issues.map(function(e) { return e.policyIssue; }).indexOf(policyIssue);
    if (pos === -1){

    Ad_Policy_Issues.push({
    "policyIssue":policyIssue,
    "approvalStatus":approvalStatus,
    "count":1
    });
    } else{
    //Logger.log(Ad_Policy_Issues[pos].policyIssue + " - " + parseInt(Ad_Policy_Issues[pos].count))
    Ad_Policy_Issues[pos].count += 1;
    }
    }
    } else {
    var policyIssue = policySummary.reviewStatus + " ADID:[" + ad_id + "]";
    var approvalStatus = normalize_text(policySummary.approvalStatus);

    var pos = Ad_Policy_Issues.map(function(e) { return e.policyIssue; }).indexOf(policyIssue);
    if (pos === -1){

    Ad_Policy_Issues.push({
    "policyIssue":policyIssue,
    "approvalStatus":approvalStatus,
    "count":1
    });
    }
    }
    }
    }

    //Here we need to try the backup approach to grabbing not approved ads
    var Report_Query = "SELECT Id, CombinedApprovalStatus, PolicySummary, AdType " +
    "FROM AD_PERFORMANCE_REPORT " +
    "WHERE CombinedApprovalStatus NOT_IN ['ELIGIBLE','APPROVED'] AND " +
    "AdGroupStatus = ENABLED and CampaignStatus = ENABLED AND " +
    "Status = ENABLED";

    //We just need to exclude any adIds that we already looked at if we have any.
    if (Ad_ids.length > 0){
    Report_Query += " AND Id NOT_IN [" + Ad_ids + "]";
    }

    var report = AdWordsApp.report(Report_Query);
    var rows = report.rows();

    while (rows.hasNext()) {
    //Loop through all the report of the NOT approved ads.
    var row = rows.next();

    var adID = row["Id"];
    var CombinedApprovalStatus = normalize_text(row["CombinedApprovalStatus"]);
    var PolicySummary = normalize_text(row["PolicySummary"]);
    var AdType = row["AdType"];

    var pos = Ad_Policy_Issues.map(function(e) { return e.policyIssue; }).indexOf(PolicySummary);
    if (pos == -1){
    Ad_Policy_Issues.push({
    "policyIssue":PolicySummary,
    "approvalStatus":CombinedApprovalStatus,
    "count":1
    });
    } else{
    Ad_Policy_Issues[pos].count += 1;
    }
    }

    Ad_Policy_Issues.sort(compareValues("count","desc"));
    Logger.log("Ad_Policy_Issues: " + JSON.stringify(Ad_Policy_Issues));
    } catch(err) {
    Logger.log(accountName + " (" + accountId + ") - " + "ERROR: Ad_Policy_Issues: " + err);
    Ad_Policy_Issues.push({
    "policyIssue":"ERROR GETTING policyIssue",
    "approvalStatus":"ERROR: " + err,
    "count":1
    });
    } finally {
    return Ad_Policy_Issues;
    }
    }

    function Check_For_Ad_Extension_Policy_Issues(account){
    //This function will look for ad extensions with policy issues

    var Extension_Policy_Issues = [];
    var accountName = account.getName();
    var accountId = account.getCustomerId();

    Logger.log(accountName + ' (' + accountId + ') - ' + 'Starting to check for not fully approved extensions.');

    var query = 'SELECT feed_item.attribute_values, feed_item.policy_infos, feed_item.id, feed_item.resource_name, segments.placeholder_type, feed_item.status ' +
    'FROM feed_item ' +
    'WHERE feed_item.status != "REMOVED" ' +
    'ORDER BY segments.placeholder_type,feed_item.id ';

    try {

    //# The second argument is optional.
    var result = AdsApp.search(query);

    while (result.hasNext()) {
    var row = result.next();

    var policyInfos = row.feedItem.policyInfos;
    var attributeValues = row.feedItem.attributeValues;
    var placeholderType = row.segments.placeholderType;

    if (policyInfos[0].approvalStatus != "APPROVED" && placeholderType != "UNKNOWN" ){
    //Logger.log(JSON.stringify(row));

    for (i in policyInfos) {
    for (j in policyInfos[i].policyTopicEntries){
    var policyIssue = normalize_text(policyInfos[i].policyTopicEntries[j].topic);
    var approvalStatus = normalize_text(policyInfos[i].approvalStatus);
    var pos = Extension_Policy_Issues.map(function(e) { return e.policyIssue; }).indexOf(policyIssue);

    if (pos == -1){
    Extension_Policy_Issues.push({
    "policyIssue":policyIssue,
    "approvalStatus":approvalStatus,
    "extensionType":normalize_text(placeholderType),
    "count":1
    });
    } else{
    Extension_Policy_Issues[pos].count += 1;
    }
    }
    }
    }
    }

    Extension_Policy_Issues.sort(compareValues("count","desc"));
    Logger.log("Extension_Policy_Issues: " + JSON.stringify(Extension_Policy_Issues));
    } catch(err) {
    Logger.log(accountName + " (" + accountId + ") - " + "ERROR: Extension_Policy_Issues: " + err);

    Extension_Policy_Issues.push({
    "policyIssue": "ERROR GETTING policyIssue",
    "approvalStatus":"ERROR: " + err,
    "extensionType":"",
    "count":1
    });

    } finally {
    return Extension_Policy_Issues;
    }
    }

    function compareValues(key, order) {
    //This function is used to sort arrays of objects based on a specific key
    //order = asc or desc

    return function innerSort(a, b) {
    if (!a.hasOwnProperty(key) || !b.hasOwnProperty(key)) {
    // property doesn't exist on either object
    return 0;
    }

    const varA = (typeof a[key] === 'string')
    ? a[key].toUpperCase() : a[key];
    const varB = (typeof b[key] === 'string')
    ? b[key].toUpperCase() : b[key];

    var comparison = 0;
    if (varA > varB) {
    comparison = 1;
    } else if (varA < varB) {
    comparison = -1;
    }
    return (
    (order === 'desc') ? (comparison * -1) : comparison
    );
    };
    }

    function normalize_text(string){
    //Fix the text so that we don't have any underscores or all uppercase words.
    //Also, clean up some other text issues.

    var new_string = string.replace(/_/g, " ").replace(/\w\S*/g, function(txt){return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();});

    new_string = new_string.replace(/\(Limited\)/g, "Limited");
    new_string = new_string.replace(/\[\"/g, "").replace(/\"\]/g, "");

    return new_string;
    }


    function getPolicyManagerURL(accountID){

    // This function is for creating the hotlinks
    // to the policy manager for specific client
    // accounts. Since Google Ads does not allow you
    // to programmatically grab the OCID, you need to
    // hard code that into this function.


    var PolicyManagerURL = "https://ads.google.com/aw/policymanager/issues?ocid=00000000&authuser=0";

    switch (accountID) {
    case "111-111-1111": //client 111-111-1111
    PolicyManagerURL = PolicyManagerURL.replace("00000000", "1111111");
    break;
    case "222-222-2222": //client 222-222-2222
    PolicyManagerURL = PolicyManagerURL.replace("00000000", "2222222");
    break;
    case "333-333-3333": //client 333-333-3333
    PolicyManagerURL = PolicyManagerURL.replace("00000000", "3333333");
    break;
    }
    return PolicyManagerURL;
    }