Google Ads Scripts

/**
*
* Report Disapproved Ads
*
* Creates a report indicating which ads are disapproved, including the reason why
* Sends an alert via email whenever ads are disapproved
*
* @author: Nils Rooijmans
*
* contact nils@nilsrooijmans.com for questions and the MCC version of the script 
*/
  
 
// CHANGE SETTINGS HERE
 
var SPREADSHEET_URL = "";  //insert a new blank spreadsheet url between the quotes
var EMAIL = ""; //insert your email adresses between the quotes
 
 
// NO CHANGES NEEDED BELOW THIS LINE
 
var SHEET_REPORT_HEADER = [
    "Campaign Name", 
    "AdGroup Name", 
    "AdID",
    "Ad Type",
    "Ad Status",
    "Disapproval Reason"
  ];
 
 
function main() {
 
  // first we clear the report sheet and add header
  prepareSpreadsheet();  
   
  // now let's check for disapprovals
  var disapprovedAds = getDisApprovedAds();    
   
  // finally, let's report on any issues
  if (disapprovedAds.length > 0) { // there is at least one issue
    Logger.log("Total NR of Ads Disapproved: "+disapprovedAds.length);
    reportResults(disapprovedAds);
  }  
}
 
 
function prepareSpreadsheet() {
 
  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getActiveSheet();
  sheet.clear(); //remove earlies alerts
  sheet.clearConditionalFormatRules();
  sheet.appendRow(SHEET_REPORT_HEADER);
}
 
 
function getDisApprovedAds() {
   
  var issues = [];
   
  var whereClause = "WHERE CampaignStatus = 'ENABLED' AND AdGroupStatus = 'ENABLED' AND Status = 'ENABLED' AND CombinedApprovalStatus = 'DISAPPROVED' ";
   
  var awqlQuery = "SELECT CampaignName, AdGroupName, Id, AdType, CombinedApprovalStatus, PolicySummary FROM AD_PERFORMANCE_REPORT "+whereClause;
   
  //debug("awqlQuery: "+awqlQuery);
   
  var rows=AdsApp.report(awqlQuery).rows() ; 
   
  while (rows.hasNext()) {
     
    var row=rows.next() ;
     
    var campaignName = row["CampaignName"];
    var adGroupName = row["AdGroupName"];
    var adId = row["Id"];
    var adType = row["AdType"];
    var approvalStatus = row["CombinedApprovalStatus"];
    var policySummary = row["PolicySummary"];
 
    issues.push([
        campaignName,
        adGroupName,
        adId,  
        adType,
        approvalStatus, 
        policySummary
    ]);  
  } 
  return issues;
}
 
 
function reportResults(results) {
 
    var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
    var sheet = ss.getActiveSheet();
    var lastRow = sheet.getLastRow();
 
    // write issues to sheet
    var range = sheet.getRange(lastRow+1, 1, results.length, SHEET_REPORT_HEADER.length);
    range.setValues(results);
 
    // send the email
    var emailBody = 
      "Number of Ads Disapproved: " + results.length + "\n" + 
      "See details: "+ SPREADSHEET_URL + "\n\n" +
      "For more FREE Google Ads Scripts to improve your results and make your working day feel like a breeze, visit https://nilsrooijmans.com \n" + 
      "---\n" + 
      "This email is generated by a copy of the free Google Ads Script - Disapproved Ads Checker, (C) Nils Rooijmans \n" +
      "---\n";
 
    MailApp.sendEmail(EMAIL, "[GOOGLE ADS ALERT] - Disapproved Ads", emailBody);
}
/**
*
* Report Trending Search Terms
*
* Creates a Google Sheets report containing two sheets:
*
* 1. Sheet with trending search terms for Last Week versus Week Before last Week
* 2. Sheet with trending search terms for Last Week versus Same Week Last Year
*
* @author: Nils Rooijmans
*
* contact nils@nilsrooijmans.com for questions and a High Performance MCC version of the script	
*/


/*** ADD YOUR CONFIGURATION HERE ***/

var SPREADSHEET_URL = "";  //insert a new blank spreadsheet url between quotes
var EMAIL_ADDRESSES = ""; //insert your email between quotes
var EMAIL_SUBJECT = "Search Terms Trends for Account - XYZ"; // replace XYZ with the account/client name


/*** NO NEED TO CHANGE ANYTHING BELOW THIS LINE ***/

var IMPRESSIONS_MIN = 10; //igore search terms with less impresssions last week 
var IMPRESSIONS_MIN_ABS_DIFFERENCE = 50; // ignore search terms with absolute difference that is smaller then the value you set here
var IMPRESSIONS_MIN_REL_DIFFERENCE = 0.2; // ignore search terms with relative difference that is smaller then the value you set here

var DEBUG = true;
var SEND_EMAIL = true; // set to false if you do not want to send alert email
var EMAIL_BODY = "";


/*** DO NOT CHANGE ANYTHING BELOW THIS LINE ***/

function main() {
    
  var searchtermsLastWeek = {};
  var searchtermsWeekBeforeLastWeek = {};
  var searchtermsLastWeekLastYear = {};
  
  var trendingSearchterms_LastWeek_vs_WeekBefore = {};
  var trendingSearchterms_LastWeek_vs_LastYear = {};
  
  searchtermsLastWeek = getSearchterms("lastWeek"); 
  searchtermsWeekBeforeLastWeek = getSearchterms("weekBeforeLastWeek"); 
  searchtermsLastWeekLastYear = getSearchterms("lastWeekLastYear"); 
    
  
  debug("Nr of searchterms last week: "+Object.keys(searchtermsLastWeek).length);
  debug("Nr of searchterms week before last week: "+Object.keys(searchtermsWeekBeforeLastWeek).length);
  debug("Nr of searchterms week last week last year: "+Object.keys(searchtermsLastWeekLastYear).length);
  
  trendingSearchterms_LastWeek_vs_WeekBefore = getTrendingSearchterms(searchtermsLastWeek, searchtermsWeekBeforeLastWeek);
  trendingSearchterms_LastWeek_vs_LastYear = getTrendingSearchterms(searchtermsLastWeek, searchtermsLastWeekLastYear);
  
  debug("Nr of trending searchterms LastWeek_vs_WeekBefore: "+Object.keys(trendingSearchterms_LastWeek_vs_WeekBefore).length);
  debug("Nr of trending searchterms LastWeek_vs_LastYear: "+Object.keys(trendingSearchterms_LastWeek_vs_LastYear).length);
  
  generateReport(trendingSearchterms_LastWeek_vs_WeekBefore, trendingSearchterms_LastWeek_vs_LastYear);

  // send email
  if (SEND_EMAIL) {
   
    var emailBody = 
        "\nNumber of trending search terms: " + ( Object.keys(trendingSearchterms_LastWeek_vs_WeekBefore).length + Object.keys(trendingSearchterms_LastWeek_vs_LastYear).length ) + "\n" + 
        "See details: "+ SPREADSHEET_URL+ "\n---\n\n" + EMAIL_BODY + "\n"+
    		"For more FREE Google Ads Scripts to improve your results and make your working day feel like a breeze, visit https://nilsrooijmans.com \n" + 
      	"---\n" + 
      	"This email is generated by a copy of the free Google Ads Script - Trending Search Terms, (C) Nils Rooijmans \n" +
      	"---\n";

    MailApp.sendEmail(EMAIL_ADDRESSES, EMAIL_SUBJECT, emailBody);
    debug("Report email sent");
  }
}


function generateReport(trendingSearchterms_LastWeek_vs_WeekBefore, trendingSearchterms_LastWeek_vs_LastYear) {
    
  var rowsThisYear  = getReportRows(trendingSearchterms_LastWeek_vs_WeekBefore);
  var rowsLastYear  = getReportRows(trendingSearchterms_LastWeek_vs_LastYear);
    
  
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL) ;
  if (!spreadsheet) {
    Logger.log("Cannot open new reporting spreadsheet") ;
    return ;
  }

  // First, generate the sheet for this year's data
  var sheet=spreadsheet.getSheetByName('LastWeek_vs_WeekBefore');
    
  if (!sheet) {
    Logger.log("Cannot open exisiting reporting sheet 'LastWeek_vs_WeekBefore', creating new one") ;
    spreadsheet.insertSheet('LastWeek_vs_WeekBefore');
    var sheet=spreadsheet.getSheetByName('LastWeek_vs_WeekBefore');
  }
  
  generateReportSheet(sheet, rowsThisYear); 
  debug("-> Generated report sheet: "+sheet.getName());
 
  
  // Second, generate the sheet for this year's data
  var sheet=spreadsheet.getSheetByName('LastWeek_vs_LastYear');
    
  if (!sheet) {
    Logger.log("Cannot open exisiting reporting sheet 'LastWeek_vs_LastYear' , creating new one") ;
    spreadsheet.insertSheet('LastWeek_vs_LastYear');
    var sheet=spreadsheet.getSheetByName('LastWeek_vs_LastYear');
  }
  
  generateReportSheet(sheet, rowsLastYear);
  debug("-> Generated report sheet: "+sheet.getName());
}


function generateReportSheet(sheet, rows) {
  
  debug("--> Generating report sheet: "+sheet.getName());

  if (sheet.getName()=='LastWeek_vs_WeekBefore') {
    var header = [
      "Searchterm",
      "Impressions Last Week",
      "Impressions week Before Last Week",
      "Diff",
      "Relative Diff (%)"
    ];
  } else if (sheet.getName()=='LastWeek_vs_LastYear') {
    var header = [  
      "Searchterm",
      "Impressions Last Week",
      "Impressions Last Week Last Year",
      "Diff",
      "Relative Diff (%)"
    ];  
  } else {
    Logger.log("### ERROR: name of sheet not recognized");
  }
  
  
  sheet.clear();
  sheet.clearConditionalFormatRules();
  sheet.appendRow(header);
  var range = sheet.getRange(1,1,1,header.length);
  range.setFontWeight("bold");
  
  var rules = sheet.getConditionalFormatRules();
  
  if ( !(rows.length < 1) ) { range = sheet.getRange(2,1,rows.length,5); var diffRange = [sheet.getRange("E2:E"+ (2+rows.length))]; var rule = SpreadsheetApp.newConditionalFormatRule().whenNumberGreaterThan(100).setFontColor("#32CD32").setRanges(diffRange).build(); rules.push(rule); var rule = SpreadsheetApp.newConditionalFormatRule().whenNumberLessThan(-50).setFontColor("#FF8C00").setRanges(diffRange).build(); rules.push(rule); sheet.setConditionalFormatRules(rules); range.setValues(rows); range.sort([{column: 4, ascending: false}]); } else { debug("ZERO trending search terms"); } } function getReportRows(trendingSearchTermObject) { var rows = []; for (var searchterm in trendingSearchTermObject) { var impressions_period1 = trendingSearchTermObject[searchterm].impressions_1; var impressions_period2 = trendingSearchTermObject[searchterm].impressions_2; var absoluteDiff = impressions_period1-impressions_period2; var relativeDiff = (((impressions_period1-impressions_period2)/impressions_period2)*100).toFixed(0); rows.push([searchterm, impressions_period1, impressions_period2, absoluteDiff, relativeDiff]); } return rows; } function getTrendingSearchterms(searchtermsPeriod_1,searchtermsPeriod_2) { var trendingSearchTerms = {}; for (var searchterm in searchtermsPeriod_1) { if (searchtermsPeriod_1.hasOwnProperty(searchterm)) { var impressions1 = searchtermsPeriod_1[searchterm]; if (searchtermsPeriod_2.hasOwnProperty(searchterm)) { var impressions2 = searchtermsPeriod_2[searchterm]; } else { var impressions2 = 0; } if (Math.abs(impressions1-impressions2) > IMPRESSIONS_MIN_ABS_DIFFERENCE && 
          Math.abs((impressions1-impressions2)/impressions2) > IMPRESSIONS_MIN_REL_DIFFERENCE ) {
        
         var searchTermObject = {};
        
         searchTermObject.impressions_1 = impressions1;     
         searchTermObject.impressions_2 = impressions2;
        
         trendingSearchTerms[searchterm] = searchTermObject;
      }
    }
  }
  
 
  // now add searchterms that were not present in data from period_1
  for (var searchterm in searchtermsPeriod_2) {
    
    if (searchtermsPeriod_2.hasOwnProperty(searchterm)) {
      
      var impressions2 = searchtermsPeriod_2[searchterm];
      
      if (!searchtermsPeriod_1.hasOwnProperty(searchterm)) { // searchterm not present in data from period_1
        var impressions1 = 0;
 
        if (Math.abs(impressions1-impressions2) > IMPRESSIONS_MIN_ABS_DIFFERENCE && 
            Math.abs((impressions1-impressions2)/impressions2) > IMPRESSIONS_MIN_REL_DIFFERENCE ) {

           var searchTermObject = {};

           searchTermObject.impressions_1 = impressions1;     
           searchTermObject.impressions_2 = impressions2;

           trendingSearchTerms[searchterm] = searchTermObject;
        }
      }
    }
  }   
  
  return trendingSearchTerms;
}



function getSearchterms(period) {
  
  var searchtermObject = {};
  var periodString;
  
  switch(period) {    
    case "today" :
      periodString = "TODAY"; 
      break;
    case "yesterday" :
      periodString = "YESTERDAY";
      break;
    case "dayBeforeYesterday" :
      periodString = dates(2) + "," + dates(2);
      break;
    case "lastWeek" :
      periodString = dates(7) + "," + dates(1);
      break;
    case "weekBeforeLastWeek" :
      periodString = dates(14) + "," + dates(8);
      break; 
    case "lastWeekLastYear" :
      periodString = dates(372) + "," + dates(366);
      break;      
    default :
      Logger.log("### ERROR: Could not recognize the period for which to add search terms");
  }
  
  var awqlQuery="SELECT Query, Impressions FROM SEARCH_QUERY_PERFORMANCE_REPORT WHERE Impressions > "+IMPRESSIONS_MIN+" DURING "+periodString;   
  debug("awqlQuery: "+awqlQuery);
  
  var rows=AdWordsApp.report(awqlQuery).rows(); 
  
  while (rows.hasNext()) {
    var row = rows.next();
    var searchterm = row['Query'];
    var impressions = numericalize(row['Impressions']);
    
    if (searchtermObject.hasOwnProperty(searchterm)) {
      searchtermObject[searchterm] += impressions;
    } else {
      searchtermObject[searchterm] = impressions;      
    }    
  }
  
  return searchtermObject;
}


function numericalize(string){
  return parseFloat(string.toString().replace(/\,/g, ''));
}


function debug(string) {
  if (DEBUG == true) {
    Logger.log(string);
  }
}


// return date x days before today
function dates(x){ 
 var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
 var now = new Date();
 var date = new Date(now.getTime() - x * MILLIS_PER_DAY);
 var timeZone = AdWordsApp.currentAccount().getTimeZone();
 var output = Utilities.formatDate(date, timeZone, 'yyyyMMdd')
 return output;
}
/******************************************
* ETA vs RSA Test
* @version: 1.0
* @author: Naman Jindal (Optmyzr)
* March 2, 2020
*******************************************/

var LOOKBACK_DAYS = 30; // Number of Days to look back for stats
var EMAILS = ['example@example.com']; // Array of Emails separated by comma
var CAMPAIGN_NAME_CONTAINS = ''; // Case Insensitive
var CAMPAIGN_NAME_DOES_NOT_CONTAIN = ''; // Case Insensitive


// Do not edit anything below this line

function main() {
  
  var DATE_RANGE = getAdWordsFormattedDate(LOOKBACK_DAYS, 'yyyyMMdd') + ',' + getAdWordsFormattedDate(1, 'yyyyMMdd');
  
  var adMap = {}, agMap = {};
  var query = [
    'SELECT Id, AdGroupId, AdType, Status FROM AD_PERFORMANCE_REPORT',
    'WHERE CampaignStatus = ENABLED and AdGroupStatus = ENABLED',
    'and AdType IN [RESPONSIVE_SEARCH_AD, EXPANDED_TEXT_AD]',
    CAMPAIGN_NAME_CONTAINS ? 'and CampaignName CONTAINS_IGNORE_CASE "' + CAMPAIGN_NAME_CONTAINS + '"' : '',
    CAMPAIGN_NAME_DOES_NOT_CONTAIN ? 'and CampaignName DOES_NOT_CONTAIN_IGNORE_CASE "' + CAMPAIGN_NAME_DOES_NOT_CONTAIN + '"' : '',
    'DURING', DATE_RANGE
  ].join(' ');
  
  var rows = AdsApp.report(query, {'includeZeroImpressions': false }).rows();
  while(rows.hasNext()) {
   var row = rows.next();
    var key = [row.AdGroupId, row.Id].join('-');
    adMap[key] = row.AdType;
    
     if(!agMap[row.AdGroupId] ) { 
       agMap[row.AdGroupId] = {
         'Responsive search ad': 0, 'Expanded text ad': 0
       }; 
     }
    
    if(row.Status == 'enabled') {
     agMap[row.AdGroupId][row.AdType]++;
    }
  }
  
  
  var agIds = Object.keys(agMap)
  var initMap = {
    'ETA': 0, 'RSA': 0,
    'Responsive search ad': { 'Impressions': 0, 'Clicks': 0, 'Conversions': 0, 'Cost': 0, 'ConversionValue': 0 } ,
    'Expanded text ad': { 'Impressions': 0, 'Clicks': 0, 'Conversions': 0, 'Cost': 0, 'ConversionValue': 0 } 
  };
    
  var statsMap = {};
  var query = [
    'SELECT',
    'Query, CampaignName, AdGroupName, AdGroupId, CreativeId, Impressions, Clicks, Conversions, Cost, ConversionValue',
    'FROM SEARCH_QUERY_PERFORMANCE_REPORT',
    'WHERE CampaignStatus = ENABLED and AdGroupStatus = ENABLED',
    CAMPAIGN_NAME_CONTAINS ? 'and CampaignName CONTAINS_IGNORE_CASE "' + CAMPAIGN_NAME_CONTAINS + '"' : '',
    CAMPAIGN_NAME_DOES_NOT_CONTAIN ? 'and CampaignName DOES_NOT_CONTAIN_IGNORE_CASE "' + CAMPAIGN_NAME_DOES_NOT_CONTAIN + '"' : '',
    agIds.length < 10000 ? 'and AdGroupId IN [' + agIds.join(',') + ']' : '',
    'DURING', DATE_RANGE
  ].join(' ');
  
  var rows = AdsApp.report(query, {'includeZeroImpressions': false }).rows();
  while(rows.hasNext()) {
   var row = rows.next();
    var adKey = [row.AdGroupId, row.CreativeId].join('-');
    var adType = adMap[adKey];
    if(!initMap[adType]) {
      continue; 
    }
    
    var agRow = agMap[row.AdGroupId];
    if(!agRow) {
      agRow = { 'Responsive search ad': 0, 'Expanded text ad': 0 }
    }
    
    var key = [row.Query, row.AdGroupName, row.CampaignName].join('!~!');
    
    if(!statsMap[key]) {
      statsMap[key] = JSON.parse(JSON.stringify(initMap));
    }
    
    statsMap[key]['ETA'] = agRow['Expanded text ad'];
    statsMap[key]['RSA'] = agRow['Responsive search ad'];
    
    row.Impressions = parseFloat(row.Impressions);
    row.Clicks = parseFloat(row.Clicks);
    row.Cost = parseFloat(row.Cost.toString().replace(/,/g, ''));
    row.Conversions = parseFloat(row.Conversions.toString().replace(/,/g, ''));
    row.ConversionValue = parseFloat(row.ConversionValue.toString().replace(/,/g, ''));
    
    statsMap[key][adType].Impressions += row.Impressions;
    statsMap[key][adType].Clicks += row.Clicks;
    statsMap[key][adType].Cost += row.Cost;
    statsMap[key][adType].Conversions += row.Conversions;
    statsMap[key][adType].ConversionValue += row.ConversionValue;
  }
  
  var output = [];
  var results = {
    'rsa':  { 'incrementalClicks': 0, 'incrementalConversions': 0 },
    'all':  { 'incrementalClicks': 0, 'incrementalConversions': 0 }
  }
  
  for(var key in statsMap) {
    var row = statsMap[key];
    var out =  key.split('!~!');
    out.push(row['ETA'], row['RSA']);
    
    var rsaRow = row['Responsive search ad']; 
    rsaRow.Ctr = rsaRow.Impressions > 0 ? rsaRow.Clicks / rsaRow.Impressions : 0;
    rsaRow.CPI = rsaRow.Impressions > 0 ? rsaRow.Conversions / rsaRow.Impressions : 0;
    rsaRow.CPA = rsaRow.Conversions > 0 ? rsaRow.Cost / rsaRow.Conversions : 0;
    rsaRow.Cpc = rsaRow.Clicks > 0 ? rsaRow.Cost / rsaRow.Clicks : 0;
    rsaRow.ROAS = rsaRow.Cost > 0 ? rsaRow.ConversionValue / rsaRow.Cost : 0;
    
    out.push(rsaRow.Impressions, rsaRow.Clicks, rsaRow.Conversions, rsaRow.Ctr, rsaRow.CPI, rsaRow.Cost, rsaRow.CPA, rsaRow.ROAS);
    
    var etaRow = row['Expanded text ad'];
    etaRow.Ctr = etaRow.Impressions > 0 ? etaRow.Clicks / etaRow.Impressions : 0;
    etaRow.CPI = etaRow.Impressions > 0 ? etaRow.Conversions / etaRow.Impressions : 0;
    etaRow.CPA = etaRow.Conversions > 0 ? etaRow.Cost / etaRow.Conversions : 0;
    etaRow.Cpc = etaRow.Clicks > 0 ? etaRow.Cost / etaRow.Clicks : 0;
    etaRow.ROAS = etaRow.Cost > 0 ? etaRow.ConversionValue / etaRow.Cost : 0;
    
    out.push(etaRow.Impressions, etaRow.Clicks, etaRow.Conversions, etaRow.Ctr, etaRow.CPI, etaRow.Cost, etaRow.CPA, etaRow.ROAS);
    
    var incrementalClicks = rsaRow.Impressions * (rsaRow.Ctr - etaRow.Ctr);
    var incrementalConversions = rsaRow.Impressions * (rsaRow.CPI - etaRow.CPI);
    var incrementalCost = rsaRow.Clicks * (rsaRow.Cpc - etaRow.Cpc);
    
    var recommendation = '';
    if(row['ETA'] == 0) {
      recommendation = 'Test ETAs';
    } else if(row['RSA'] == 0) {
      recommendation = 'Test RSAs';
    } else if(incrementalConversions > 0) {
      recommendation = 'SKAG query with existing RSA';
    } else if(incrementalConversions < 0) {
      recommendation = 'SKAG query with existing ETA';
    } else if(etaRow.Impressions == 0) {
      recommendation = 'Test ETAs';
    } else if(rsaRow.Impressions == 0) {
      recommendation = 'Test RSAs';
    } 
    
    if(row['ETA'] == 0 && etaRow.Impressions == 0) {
      results['rsa'].incrementalClicks += incrementalClicks;
      results['rsa'].incrementalConversions += incrementalConversions;
    }
    
    if(etaRow.Impressions > 0 && rsaRow.Impressions > 0) {
      results['all'].incrementalClicks += incrementalClicks;
      results['all'].incrementalConversions += incrementalConversions;
    }
    
    out.push(incrementalClicks, incrementalConversions, incrementalCost, recommendation);
    
    output.push(out);
  }
  
  if(!output.length) {
    Logger.log('No Data found');
    return;   
  }
  
  var TEMPLATE_URL = 'https://docs.google.com/spreadsheets/d/1AAk_Vsdq0LezvezOarzhOzVXsQq5BN-EKMRtGX-eDVQ/edit';
  var template = SpreadsheetApp.openByUrl(TEMPLATE_URL);
  
  var now = getAdWordsFormattedDate(0, 'MMM d, yyyy')
  var name = AdsApp.currentAccount().getName() + ' - ETA vs RSA (' + now + ')';
  var ss = template.copy(name);
  var reportUrl = ss.getUrl();
  Logger.log('Report URL: ' + reportUrl);
  ss.addEditors(EMAILS);
  
  ss.getSheets()[0].getRange(3,1,output.length,output[0].length).setValues(output);
  
  sendEmail(results, reportUrl);
}

function sendEmail(results, reportUrl) {
  var sub = AdsApp.currentAccount().getName() + ' - ETA vs RSA Test Report';
  var msg = 'Hi,\n\nPlease find below summary of latest test results:\n';
  
  var rsaResults = results['rsa'];
  if(rsaResults.incrementalClicks > 0) {
    msg += 'You got ' + Math.round(rsaResults.incrementalClicks) + 'more clicks and ' + Math.round(rsaResults.incrementalConversions)  + ' more convversions from queries that only triggered with RSA ads.\n';
  }
  
  var change = ''
  if(results['all'].incrementalConversions > 0) {
    change = 'gained';
  } else if(results['all'].incrementalConversions < 0) {
    change = 'lost';
  }
  
  if(change) {
    msg += 'For queries that showed both RSA and ETA ads, you ' + change + ' ' + Math.round(results['all'].incrementalConversions) + ' conversions.\n';
  }
  
  msg += '\n\n' + reportUrl;
  msg +- '\n\nThanks';
  
  MailApp.sendEmail(EMAILS.join(','), sub, msg);
}

function getAdWordsFormattedDate(d, format){
  var date = new Date();
  date.setDate(date.getDate() - d);
  return Utilities.formatDate(date,AdWordsApp.currentAccount().getTimeZone(),format);
}
 Save
/*
Disapproved Extensions Report Script.
This scripts generates an email if there are some non-removed disapproved extensions.
Disapproval reasons are taken from PLACEHOLDER_FEED_ITEM_REPORT.
Check the nameMapping veriable below for the list of supported extensions.
Email message could also include a long list of dissaproved remarketing feed items, so adjust settings to skip this if necessary.

Developed by Dmytro Bulakh, 2020, bulakh@ppchead.com
*/


// SCRIPT SETIINGS:

// EMAIL:  add recipients emails, like ['john@doe.com', 'doe@john.com']
// If there are no emails, like [] , the script would only log the report
var EMAIL = ['john@doe.com']

// SKIP DYNAMIC REMARKETING: set to true to skip remarketing feed items report or false otherwise

var SKIP_DYNAMIC_REMARKETING = true

function main() {

  // PlaceholderType mapping as of API version v201809 (https://developers.google.com/adwords/api/docs/appendix/placeholders)
  var nameMapping = {
    "1": "Sitelink",
    "2": "Call",
    "3": "App",
    "7": "Location",
    "30": "Affiliate location",
    "17": "Callout",
    "24": "Structured snippet",
    "31": "Message",
    "35": "Price",
    "38": "Promotion",
    "10": "Ad customizers",
    "12": "Dynamic remarketing item: Education",
    "13": "Dynamic remarketing item: Flights",
    "14": "Dynamic remarketing item: Custom Feed",
    "15": "Dynamic remarketing item: Hotels",
    "16": "Dynamic remarketing item: Real estate",
    "17": "Dynamic remarketing item: Travel",
    "19": "Dynamic remarketing item: Local",
    "20": "Dynamic remarketing item: Jobs"
  }

  var query = "SELECT PlaceholderType, DisapprovalShortNames, FeedId, FeedItemId, AttributeValues, Status " +
    "FROM PLACEHOLDER_FEED_ITEM_REPORT " +
    "WHERE DisapprovalShortNames != '' " +
    "AND Status != REMOVED " +
    "DURING TODAY"

  var report = AdsApp.report(query)
  var rows = report.rows(), values = [], iterator = {}, reasons = {}, total = 0
  var unknownTypes = {}
  while (rows.hasNext()) {
    var row = rows.next()
    if (SKIP_DYNAMIC_REMARKETING && nameMapping[row.PlaceholderType] && nameMapping[row.PlaceholderType].match('Dynamic')) continue
    row.ExtensionType = nameMapping[row.PlaceholderType]
    total++
    if (!row.ExtensionType) {
      if (!unknownTypes[row.PlaceholderType]) unknownTypes[row.PlaceholderType] = 0.0
      unknownTypes[row.PlaceholderType]++
      continue
    }
    iterator[row.ExtensionType] = iterator[row.ExtensionType] || []
    iterator[row.ExtensionType].push({
      'id': row.FeedItemId,
      'attr': row.AttributeValues,
      'status': row.Status
    })
    reasons[row.FeedItemId] = row.DisapprovalShortNames
    if (values.indexOf(row.DisapprovalShortNames) == -1) {
      values.push(row.DisapprovalShortNames)
    }
  }
  if (total < 1) {
    Logger.log('found no dissaproved extensions')
    return 'no dissaproved'
  }

  var message = ''
  for (var type in iterator) {
    message += '\n>>' + type + ': ' + (iterator[type].length) + ' disapproved extensions:\n'
    Logger.log('there are %s disapproved %s extensions:', iterator[type].length, type)
    var typeMessage = iterator[type].map(function (item) {
      var reason = JSON.parse(reasons[item.id])[0].split('\t')[0]
      return 'reason: ' + reason + ' (id:' + item.id + ')\n' + flat(JSON.parse(item.attr))
    })
    message += typeMessage.join('\n') + '\n'
  }
  Logger.log(message)

  if (EMAIL && EMAIL.length && EMAIL.length > 0) { 
    MailApp.sendEmail(EMAIL, 'Disapproved Extensions Report for ' + AdsApp.currentAccount().getName(), message)
  }
}

// Helper function for displaying extensions
function flat(record) {
  var s = []
  for (var key in record) {
    if (key > 3) {
      s.push('...')
      break
    }
    var val = record[key].join ? record[key].join(',') : record[key]
    s.push(val)
  }
  return s.join('\n')
}

Similiar Collections