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

Python strftime reference pandas.Period.strftime python - Formatting Quarter time in pandas columns - Stack Overflow python - Pandas: Change day - Stack Overflow python - Check if multiple columns exist in a df - Stack Overflow Pandas DataFrame apply() - sending arguments examples python - How to filter a dataframe of dates by a particular month/day? - Stack Overflow python - replace a value in the entire pandas data frame - Stack Overflow python - Replacing blank values (white space) with NaN in pandas - Stack Overflow python - get list from pandas dataframe column - Stack Overflow python - How to drop rows of Pandas DataFrame whose value in a certain column is NaN - Stack Overflow python - How to drop rows of Pandas DataFrame whose value in a certain column is NaN - Stack Overflow python - How to lowercase a pandas dataframe string column if it has missing values? - Stack Overflow How to Convert Integers to Strings in Pandas DataFrame - Data to Fish How to Convert Integers to Strings in Pandas DataFrame - Data to Fish create a dictionary of two pandas Dataframe columns? - Stack Overflow python - ValueError: No axis named node2 for object type <class 'pandas.core.frame.DataFrame'> - Stack Overflow Python Pandas iterate over rows and access column names - Stack Overflow python - Creating dataframe from a dictionary where entries have different lengths - Stack Overflow python - Deleting DataFrame row in Pandas based on column value - Stack Overflow python - How to check if a column exists in Pandas - Stack Overflow python - Import pandas dataframe column as string not int - Stack Overflow python - What is the most efficient way to create a dictionary of two pandas Dataframe columns? - Stack Overflow Python Loop through Excel sheets, place into one df - Stack Overflow python - How do I get the row count of a Pandas DataFrame? - Stack Overflow python - How to save a new sheet in an existing excel file, using Pandas? - Stack Overflow Python Loop through Excel sheets, place into one df - Stack Overflow How do I select a subset of a DataFrame? — pandas 1.2.4 documentation python - Delete column from pandas DataFrame - Stack Overflow python - Convert list of dictionaries to a pandas DataFrame - Stack Overflow How to Add or Insert Row to Pandas DataFrame? - Python Examples python - Check if a value exists in pandas dataframe index - Stack Overflow python - Set value for particular cell in pandas DataFrame using index - Stack Overflow python - Pandas Dataframe How to cut off float decimal points without rounding? - Stack Overflow python - Pandas: Change day - Stack Overflow python - Clean way to convert quarterly periods to datetime in pandas - Stack Overflow Pandas - Number of Months Between Two Dates - Stack Overflow python - MonthEnd object result in <11 * MonthEnds> instead of number - Stack Overflow python - Extracting the first day of month of a datetime type column in pandas - Stack Overflow
MySQL MULTIPLES INNER JOIN How to Use EXISTS, UNIQUE, DISTINCT, and OVERLAPS in SQL Statements - dummies postgresql - SQL OVERLAPS PostgreSQL Joins: Inner, Outer, Left, Right, Natural with Examples PostgreSQL Joins: A Visual Explanation of PostgreSQL Joins PL/pgSQL Variables ( Format Dates ) The Ultimate Guide to PostgreSQL Date By Examples Data Type Formatting Functions PostgreSQL - How to calculate difference between two timestamps? | TablePlus Date/Time Functions and Operators PostgreSQL - DATEDIFF - Datetime Difference in Seconds, Days, Months, Weeks etc - SQLines CASE Statements in PostgreSQL - DataCamp SQL Optimizations in PostgreSQL: IN vs EXISTS vs ANY/ALL vs JOIN PostgreSQL DESCRIBE TABLE Quick and best way to Compare Two Tables in SQL - DWgeek.com sql - Best way to select random rows PostgreSQL - Stack Overflow PostgreSQL: Documentation: 13: 70.1. Row Estimation Examples Faster PostgreSQL Counting How to Add a Default Value to a Column in PostgreSQL - PopSQL How to Add a Default Value to a Column in PostgreSQL - PopSQL SQL Subquery - Dofactory SQL IN - SQL NOT IN - JournalDev DROP FUNCTION (Transact-SQL) - SQL Server | Microsoft Docs SQL : Multiple Row and Column Subqueries - w3resource PostgreSQL: Documentation: 9.5: CREATE FUNCTION PostgreSQL CREATE FUNCTION By Practical Examples datetime - PHP Sort a multidimensional array by element containing date - Stack Overflow database - Oracle order NULL LAST by default - Stack Overflow PostgreSQL: Documentation: 9.5: Modifying Tables
PostgreSQL POSITION() function PostgresQL ANY / SOME Operator ( IN vs ANY ) PostgreSQL Substring - Extracting a substring from a String How to add an auto-incrementing primary key to an existing table, in PostgreSQL PostgreSQL STRING_TO_ARRAY()function mysql FIND_IN_SET equivalent to postgresql PL/pgSQL Variables ( Format Dates ) The Ultimate Guide to PostgreSQL Date By Examples Data Type Formatting Functions PostgreSQL - How to calculate difference between two timestamps? | TablePlus Date/Time Functions and Operators PostgreSQL - DATEDIFF - Datetime Difference in Seconds, Days, Months, Weeks etc - SQLines CASE Statements in PostgreSQL - DataCamp SQL Optimizations in PostgreSQL: IN vs EXISTS vs ANY/ALL vs JOIN PL/pgSQL Variables PostgreSQL: Documentation: 11: CREATE PROCEDURE Reading a Postgres EXPLAIN ANALYZE Query Plan Faster PostgreSQL Counting sql - Fast way to discover the row count of a table in PostgreSQL - Stack Overflow PostgreSQL: Documentation: 9.1: tablefunc PostgreSQL DESCRIBE TABLE Quick and best way to Compare Two Tables in SQL - DWgeek.com sql - Best way to select random rows PostgreSQL - Stack Overflow How to Add a Default Value to a Column in PostgreSQL - PopSQL How to Add a Default Value to a Column in PostgreSQL - PopSQL PL/pgSQL IF Statement PostgreSQL: Documentation: 9.1: Declarations SQL Subquery - Dofactory SQL IN - SQL NOT IN - JournalDev PostgreSQL - IF Statement - GeeksforGeeks How to work with control structures in PostgreSQL stored procedures: Using IF, CASE, and LOOP statements | EDB PL/pgSQL IF Statement How to combine multiple selects in one query - Databases - ( loop reference ) DROP FUNCTION (Transact-SQL) - SQL Server | Microsoft Docs SQL : Multiple Row and Column Subqueries - w3resource PostgreSQL: Documentation: 9.5: CREATE FUNCTION PostgreSQL CREATE FUNCTION By Practical Examples datetime - PHP Sort a multidimensional array by element containing date - Stack Overflow database - Oracle order NULL LAST by default - Stack Overflow PostgreSQL: Documentation: 9.5: Modifying Tables
כמה עוד נשאר למשלוח חינם גם לעגלה ולצקאאוט הוספת צ'קבוקס לאישור דיוור בצ'קאאוט הסתרת אפשרויות משלוח אחרות כאשר משלוח חינם זמין דילוג על מילוי כתובת במקרה שנבחרה אפשרות איסוף עצמי הוספת צ'קבוקס לאישור דיוור בצ'קאאוט שינוי האפשרויות בתפריט ה-סידור לפי בווקומרס שינוי הטקסט "אזל מהמלאי" הערה אישית לסוף עמוד העגלה הגבלת רכישה לכל המוצרים למקסימום 1 מכל מוצר קבלת שם המוצר לפי ה-ID בעזרת שורטקוד הוספת כפתור וואטסאפ לקנייה בלופ ארכיון מוצרים הפיכה של מיקוד בצ'קאאוט ללא חובה מעבר ישיר לצ'קאאוט בלחיתה על הוספה לסל (דילוג עגלה) התראה לקבלת משלוח חינם בדף עגלת הקניות גרסה 1 התראה לקבלת משלוח חינם בדף עגלת הקניות גרסה 2 קביעה של מחיר הזמנה מינימלי (מוצג בעגלה ובצ'קאאוט) העברת קוד הקופון ל-ORDER REVIEW העברת קוד הקופון ל-ORDER REVIEW Kadence WooCommerce Email Designer קביעת פונט אסיסנט לכל המייל בתוסף מוצרים שאזלו מהמלאי - יופיעו מסומנים באתר, אבל בתחתית הארכיון הוספת כפתור "קנה עכשיו" למוצרים הסתרת אפשרויות משלוח אחרות כאשר משלוח חינם זמין שיטה 2 שינוי סימן מטבע ש"ח ל-ILS להפוך סטטוס הזמנה מ"השהייה" ל"הושלם" באופן אוטומטי תצוגת הנחה באחוזים שינוי טקסט "בחר אפשרויות" במוצרים עם וריאציות חיפוש מוצר לפי מק"ט שינוי תמונת מוצר לפי וריאציה אחרי בחירה של וריאציה אחת במקרה של וריאציות מרובות הנחה קבועה לפי תפקיד בתעריף קבוע הנחה קבועה לפי תפקיד באחוזים הסרה של שדות משלוח לקבצים וירטואליים הסתרת טאבים מעמוד מוצר הצגת תגית "אזל מהמלאי" בלופ המוצרים להפוך שדות ל-לא חובה בצ'קאאוט שינוי טקסט "אזל מהמלאי" לוריאציות שינוי צבע ההודעות המובנות של ווקומרס הצגת ה-ID של קטגוריות המוצרים בעמוד הקטגוריות אזל מהמלאי- שינוי ההודעה, תגית בלופ, הודעה בדף המוצר והוספת אזל מהמלאי על וריאציה הוספת שדה מחיר ספק לדף העריכה שינוי טקסט אזל מהמלאי תמונות מוצר במאונך לצד תמונת המוצר הראשית באלמנטור הוספת כפתור קנה עכשיו לעמוד המוצר בקניה הזו חסכת XX ש''ח לאפשר למנהל חנות לנקות קאש ברוקט לאפשר רק מוצר אחד בעגלת קניות
הודעת שגיאה מותאמת אישית בטפסים להפוך כל סקשן/עמודה לקליקבילית (לחיצה) - שיטה 1 להפוך כל סקשן/עמודה לקליקבילית (לחיצה) - שיטה 2 שינוי הגבלת הזיכרון בשרת הוספת לינק להורדת מסמך מהאתר במייל הנשלח ללקוח להפוך כל סקשן/עמודה לקליקבילית (לחיצה) - שיטה 3 יצירת כפתור שיתוף למובייל פתיחת דף תודה בטאב חדש בזמן שליחת טופס אלמנטור - טופס בודד בדף פתיחת דף תודה בטאב חדש בזמן שליחת טופס אלמנטור - טפסים מרובים בדף ביי ביי לאריק ג'ונס (חסימת ספאם בטפסים) זיהוי אלו אלמנטים גורמים לגלילה אופקית לייבלים מרחפים בטפסי אלמנטור יצירת אנימציה של "חדשות רצות" בג'ט (marquee) שינוי פונט באופן דינאמי בג'ט פונקציה ששולפת שדות מטא מתוך JET ומאפשרת לשים הכל בתוך שדה SELECT בטופס אלמנטור הוספת קו בין רכיבי התפריט בדסקטופ ולדציה למספרי טלפון בטפסי אלמנטור חיבור שני שדות בטופס לשדה אחד שאיבת נתון מתוך כתובת ה-URL לתוך שדה בטופס וקידוד לעברית מדיה קוורי למובייל לייבלים מרחפים בטפסי אלמנטור תמונות מוצר במאונך לצד תמונת המוצר הראשית באלמנטור הצגת תאריך עברי פורמט תאריך מותאם אישית