/**
*
* Quality Score Components Checker 
*
* The script checks all the serving keywords in your search campaigns for their QS component scores.
* If the keywords has had a minimum number of impressions and cost AND a low score for the 'Ad Relevance' or 'Landinge Page Experience' component, it is reported in a Google Sheet. 
* If there are keywords reported in the sheet, the script will send a link to the report via email.
*
* Version 1.0
*
* @author: Nils Rooijmans
*
* For the MCC version that runs on Google Ads manager accounts and checks all search campaigns in the client accounts contact nils@nilsrooijmans.com 
*/
  
// CHANGE SETTINGS HERE

var SPREADSHEET_URL = "";  //insert a new blank spreadsheet url

var PERIOD = "LAST_30_DAYS"; // date range 
var IMPRESSION_THRESHOLD = 100; // ignore keywords with less impressions
var COST_THRESHOLD = 100; // ignore keywords with less cost
var IGNORE_KEYWORD_LABEL = "Ignore QS Alert"; // label to use on keywords you want to exclude from the warnings

var CHECK_EXPECTED_CTR = true; // set to true if you want to check for below avg Expected CTR
var CHECK_AD_RELEVANCE = true; // set to true if you want to check for below avg Ad Relevance
var CHECK_LANDING_PAGE_EXPERIENCE = true; // set to true if you want to check for below avg Landing Page Experience

//alert email adresses, insert your email adresses between the double quotes. You can add multiple email addresses between the double quotes, just separate them via a comma, ie: "john@doe.com, jane@doe.com"
var EMAIL_ADDRESSES = ""; 

var EMAIL_SUBJECT = "[Google Ads] - WARNING - Keywords with QS Issues";


// DO NOT CHANGE ANYHTING BELOW THIS LINE


function main() {

 
  const accountName = AdsApp.currentAccount().getName();
  const accountId = AdsApp.currentAccount().getCustomerId();
  console.log(`Processing account '${accountName}'`);

  var emailBody = `Account '${accountName}' with id '${accountId}' has enabled keywords that had significant spend, and have Quality Score improvement opportunities.\n\nHere's the list:\n ${SPREADSHEET_URL}\n\n`;
  
  //prepare the Spreadsheet
  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getActiveSheet();
  sheet.deleteRow(1); // remove first line since it often contains filters that mess things up
  sheet.clear(); //remove earlies alerts
  sheet.appendRow([
    "Account Name",
    "Campaign Name",
    "AdGroup Name",
    "Keyword",
    "Impressions",
    "Clicks",
    "Cost",
    "Conversions",
    "Quality Score",
    "Ad Relvance",
    "Landing Page Experience",
    "Expected CTR"
  ]);
    
  var totalNrOfKWsWithQSIssue = 0; // number of KWs with a QS issue

  var query =     'SELECT AccountDescriptiveName, CampaignName, AdGroupName, Criteria, Impressions, Clicks, Cost, Conversions, QualityScore, SearchPredictedCtr, CreativeQualityScore, PostClickQualityScore, Labels ' +
    'FROM   KEYWORDS_PERFORMANCE_REPORT ' +
    'WHERE  Impressions > '+IMPRESSION_THRESHOLD+' AND Cost > '+toMoney(COST_THRESHOLD)+' AND CampaignStatus = "ENABLED" AND AdGroupStatus = "ENABLED" AND Status = "ENABLED" ' +
    'DURING '+PERIOD;
  
  console.log(`query: ${query}`);
  
  var labelId = getLabelId(IGNORE_KEYWORD_LABEL);
      
  var report = AdsApp.report(query);

  var rows = report.rows();

  while (rows.hasNext()) {

    var row = rows.next();
    
    var warning = false;

    var expectedCTR = row['SearchPredictedCtr'];
    var adRelevance = row['CreativeQualityScore'];
    var landingPageExperience = row['PostClickQualityScore'];
    var labels = row['Labels'];

    var campaignName = row['CampaignName'];

    if (labels.indexOf(labelId) == -1) {
      if (CHECK_EXPECTED_CTR && (expectedCTR == 'Below average')) {warning = true};
      if (CHECK_AD_RELEVANCE && (adRelevance == 'Below average')) {warning = true};
      if (CHECK_LANDING_PAGE_EXPERIENCE && (landingPageExperience == 'Below average')) {warning = true};   
    }

    if (warning) {
      console.log(`labels: ${labels}`);
      totalNrOfKWsWithQSIssue++; 
      sheet.appendRow([
        row['AccountDescriptiveName'],
        row['CampaignName'],
        row['AdGroupName'],
        " "+row['Criteria'],
        row['Impressions'],
        row['Clicks'],
        row['Cost'],
        row['Conversions'],
        row['QualityScore'],
        row['CreativeQualityScore'],
        row['PostClickQualityScore'],
        row['SearchPredictedCtr']          
      ]);
    }       
  }

  if (totalNrOfKWsWithQSIssue > 0) { // if there is a Keyword with QS issues, send email
	  sheet.sort(7, false);
    var range = sheet.getRange(1, 1, 1, sheet.getLastColumn());
    range.setFontWeight("bold");
    emailBody += `\n\n-----------------------------\nThis email is generated by the Google Ads Script "Quality Score Components Checker", (C) Nils Rooijmans.`;
    emailBody += `\n\nFor the MCC version that runs on Google Ads manager accounts and checks all search campaigns in the client accounts contact nils@nilsrooijmans.com `;
    MailApp.sendEmail(EMAIL_ADDRESSES, EMAIL_SUBJECT+` in account '${accountName}'`, emailBody);
    console.log(`Sending alert mail`);
	
    MailApp.sendEmail(EMAIL_ADDRESSES, EMAIL_SUBJECT, emailBody);
  }

}


// convert cost value to money data type
function toMoney(m) {
  return m * 1000000;
}


function getLabelId(labelName) {
  try {
    var labelIterator = AdsApp.labels()
      .withCondition("Name = '" + labelName + "'")
      .get();

    if (labelIterator.hasNext()) {
      var label = labelIterator.next();
      Logger.log("Label '" + labelName + "' has id: "+label.getId());
      return label.getId();
    } else {
      Logger.log("Label '" + labelName + "' not found.");
      return null; // Or throw an error, depending on your needs.
    }
  } catch (e) {
    Logger.log("Error getting label ID: " + e.message);
    return null; // Or throw an error.
  }
}