Track AdWords Account, Campaign, and Ad Group Quality Score

gistfile1.txt

// Account, Campaign, and Ad Group Level Quality Score
// -------------------------------------------------
// Script created by Frederick Vallaeys
// Optmyzr - http://www.optmyzr.com   http://www.optmyzr.com/enhanced-scripts-for-adwords
// Copyright 2012-2016 - Optmyzr Inc.
//


function main() {

  // USER SETTINGS - CHANGE THESE
  // ---------------------------------------
  var emailNotificationRecipients = "";
  var time = "LAST_30_DAYS"; 
  
  //-----------------
  // END OF SETTINGS
  
  
  // EMAIL
  var subject = "QS Analysis: ";
  var emailBody = "";
  var accountId = AdWordsApp.currentAccount().getCustomerId();
  subject = subject + " " + accountId;
  
  
  Logger.log("Quality Score Report provided by Optmyzr");
  emailBody = emailBody + "Quality Score Report provided by Optmyzr\n";
  Logger.log("----------------------------------------------");
  emailBody = emailBody + "----------------------------------------------\n";
  Logger.log("");
  Logger.log("Settings:");
  emailBody = emailBody + "Settings:\n";
  Logger.log("  Date range: " + time);
  emailBody = emailBody + "  Date range: " + time + "\n";
  Logger.log("");
  Logger.log("Script by Frederick Vallaeys and Optmyzr - http://www.optmyzr.com/enhanced-scripts-for-adwords");
  emailBody = emailBody + "Script by Frederick Vallaeys and Optmyzr - http://www.optmyzr.com/enhanced-scripts-for-adwords\n";
  Logger.log("follow us on Twitter: @SiliconVallaeys and @Optmyzr ");
  emailBody = emailBody + "follow us on Twitter: @SiliconVallaeys and @Optmyzr\n\n";
  Logger.log("");
  Logger.log("");
  
  
  
  var impressions = 0;
  var totalImpressions = 0;
  var totalQS = 0;
  var adGroupQS = 0;
  var adGroupCost = 0;
  var adGroupClicks = 0;
  var adGroupList = new Array();
  var campaignList = new Array();
  var domainList = new Array();
  var emailBody = emailBody + "This is an automatically generated email with an update about your AdWords account. Thank you for using an Optmyzr script to simplify your online marketing. Visit our site at http://www.optmyzr.com\n\n";
  
  
  
  
  var keywordReport = AdWordsApp.report(
    'SELECT Impressions, Cost, Clicks, Criteria, QualityScore, AdGroupName, CampaignName ' +
    'FROM   KEYWORDS_PERFORMANCE_REPORT ' +
    'WHERE  AdNetworkType2 = SEARCH ' +
    'AND    Impressions > 0 ' +
    'AND    Status != REMOVED ' +
    'AND    QualityScore >= 1 ' +
    'DURING ' + time );
  var keywordRows = keywordReport.rows();
  
  while(keywordRows.hasNext()) {
    var keywordRow = keywordRows.next();
    var campaignName = keywordRow['CampaignName'];
    var adGroupName = keywordRow['AdGroupName'];
    var keywordText = keywordRow['Criteria'];
    var impressions = keywordRow['Impressions'];
    var cost = keywordRow['Cost'];
    var qs = keywordRow['QualityScore'];
    
    
    totalImpressions = parseFloat(totalImpressions) + parseFloat(impressions);
    
    
    if(typeof(campaignList[campaignName]) == 'undefined') {
      campaignList[campaignName] = new Object();
      campaignList[campaignName].campaignName = campaignName;
      campaignList[campaignName].impressions = parseFloat(impressions);
      campaignList[campaignName].qs = parseFloat(0);
      campaignList[campaignName].cost = parseFloat(cost);
    } else {
      campaignList[campaignName].impressions = parseFloat(campaignList[campaignName].impressions) + parseFloat(impressions);
      campaignList[campaignName].cost = parseFloat(campaignList[campaignName].cost) + parseFloat(cost);
    }
    
    
    var uniqueAdGroupName = "" + campaignName + " - " + adGroupName;
    
    if(typeof(adGroupList[uniqueAdGroupName]) == 'undefined') {
      adGroupList[uniqueAdGroupName] = new Object();
      adGroupList[uniqueAdGroupName].adGroupName = adGroupName;
      adGroupList[uniqueAdGroupName].campaignName = campaignName;
      adGroupList[uniqueAdGroupName].impressions = parseFloat(impressions);
      adGroupList[uniqueAdGroupName].qs = parseFloat(0);
      adGroupList[uniqueAdGroupName].cost = parseFloat(cost);
    } else {
      adGroupList[uniqueAdGroupName].impressions = parseFloat(adGroupList[uniqueAdGroupName].impressions) + parseFloat(impressions);
      adGroupList[uniqueAdGroupName].cost = parseFloat(adGroupList[uniqueAdGroupName].cost) + parseFloat(cost);
    }
   
      
  }
  
  
  var adReport = AdWordsApp.report(
    'SELECT Impressions, Cost, DisplayUrl, AdGroupName, CampaignName ' +
    'FROM   AD_PERFORMANCE_REPORT ' +
    'WHERE  AdNetworkType2 = SEARCH ' +
    'AND    Impressions > 0 ' +
    'DURING ' + time );
  var adRows = adReport.rows();
  
  while(adRows.hasNext()) {
    var adRow = adRows.next();
    var displayUrl = adRow['DisplayUrl'];
    var adGroupName = adRow['AdGroupName'];
    var campaignName = adRow['CampaignName'];
    var impressions = adRow['Impressions'];
    var cost = adRow['Cost'];
    
    var uniqueAdGroupName = "" + campaignName + " - " + adGroupName;
    
    var displayUrlParts = displayUrl.split("/");
    var displayUrl = displayUrlParts[0].toLowerCase();
    
    
    if(typeof(domainList[displayUrl]) == 'undefined') {
      domainList[displayUrl] = new Object();
      domainList[displayUrl].domain = displayUrl;
      domainList[displayUrl].impressions = parseFloat(impressions);
      domainList[displayUrl].impressionCounter = 0;
      domainList[displayUrl].cost = parseFloat(cost);
      domainList[displayUrl].qs = 0;
      
    } else {
      domainList[displayUrl].impressions = parseFloat(domainList[displayUrl].impressions) + parseFloat(impressions);
      domainList[displayUrl].cost = parseFloat(domainList[displayUrl].cost) + parseFloat(cost);
      
    }
    
    if(typeof(adGroupList[uniqueAdGroupName]) == 'undefined') {
      adGroupList[uniqueAdGroupName] = new Object();
      adGroupList[uniqueAdGroupName].domain = displayUrl;
      adGroupList[uniqueAdGroupName].adGroupName = adGroupName;
      adGroupList[uniqueAdGroupName].campaignName = campaignName;
      adGroupList[uniqueAdGroupName].impressions = parseFloat(0);
      adGroupList[uniqueAdGroupName].qs = parseFloat(0);
      adGroupList[uniqueAdGroupName].cost = parseFloat(0);
    } else {
      adGroupList[uniqueAdGroupName].domain = displayUrl;
    }
  }
  
  
  var keywordRows = keywordReport.rows();
  while(keywordRows.hasNext()) {
    var keywordRow = keywordRows.next();
    var campaignName = keywordRow['CampaignName'];
    var adGroupName = keywordRow['AdGroupName'];
    var keywordText = keywordRow['KeywordText'];
    var impressions = keywordRow['Impressions'];
    var cost = keywordRow['Cost'];
    var qs = keywordRow['QualityScore'];
    
    var uniqueAdGroupName = "" + campaignName + " - " + adGroupName;
    
    var agImpressions = adGroupList[uniqueAdGroupName].impressions;
    var campaignImpressions = campaignList[campaignName].impressions;
    var displayUrl = adGroupList[uniqueAdGroupName].domain;
    
    var agQsContribution = qs * impressions / agImpressions;
    var campaignQsContribution = qs * impressions / campaignImpressions;
    var accountQsContribution = qs * impressions / totalImpressions;
    
    
    adGroupList[uniqueAdGroupName].qs = parseFloat(adGroupList[uniqueAdGroupName].qs) + parseFloat(agQsContribution);
    campaignList[campaignName].qs = parseFloat(campaignList[campaignName].qs) + parseFloat(campaignQsContribution);
    totalQS = parseFloat(totalQS) + parseFloat(accountQsContribution);
    
  }
  
  // ACCOUNT QS
  Logger.log("Account QS: " + totalQS.toFixed(2));
  emailBody = emailBody + "\nResults\n";
  emailBody = emailBody + "\nAccount QS: " + totalQS.toFixed(2) + "\n";
  Logger.log("");
  
  // CAMPAIGN QS
  Logger.log("Campaigns");
  Logger.log("---------");
  var keys = new Array();
  var values = new Array();
  for(var campaignName in campaignList) {
    var campaign = campaignList[campaignName];
    var campaignName = campaign.campaignName;
    var campaignQS = campaign.qs;
    var campaignImpressions = campaign.impressions;
    var campaignCost = campaign.cost;
    keys.push(campaignName);
    values.push(campaignCost);
    //Logger.log(campaignName + ": " + campaignImpressions + " QS: " + campaignQS);
  }
  
  // do some sorting
  var list = [];
  for (var j=0; j<keys.length; j++) {
    list.push({'name': keys[j], 'value': values[j]});
  }
  
  list.sort(function(a, b) {
    return b.value - a.value;
  });
  
  for (var k=0; k<list.length; k++) {
    keys[k] = list[k].name;
    values[k] = list[k].value;
    //Logger.log("Key: " + keys[k] + " Value: " + kpiValues[k]);
  }
    
      
  for(var campaignCounter = 0; campaignCounter < keys.length; campaignCounter++) {
    var key = keys[campaignCounter];
    var campaign = campaignList[key];
    var campaignName = campaign.campaignName;
    var campaignQS = campaign.qs;
    var campaignImpressions = campaign.impressions;
    var campaignCost = campaign.cost;
    Logger.log(campaignName + " cost: $" + campaignCost.toFixed(2) + " imp: " + campaignImpressions + " QS: " + campaignQS.toFixed(2));
  }
  Logger.log("");    

  // Ad GROUP QS
  Logger.log("Ad Groups");
  Logger.log("---------");
  var keys = new Array();
  var values = new Array();
  for(var name in adGroupList) {
    var adGroup = adGroupList[name];
    var campaignName = adGroup.campaignName;
    var adGroupName = adGroup.adGroupName;
    var adGroupQS = adGroup.qs;
    var adGroupImpressions = adGroup.impressions;
    var adGroupCost = adGroup.cost;
    var uniqueAdGroupName = "" + campaignName + " - " + adGroupName;
    keys.push(uniqueAdGroupName);
    values.push(adGroupCost);
    //Logger.log(adGroupName + ": " + adGroupImpressions + " QS: " + adGroupQS);
  }
  
  // do some sorting
  var list = [];
  for (var j=0; j<keys.length; j++) {
    list.push({'name': keys[j], 'value': values[j]});
  }
  
  list.sort(function(a, b) {
    return b.value - a.value;
  });
  
  for (var k=0; k<list.length; k++) {
    keys[k] = list[k].name;
    values[k] = list[k].value;
    //Logger.log("Key: " + keys[k] + " Value: " + values[k]);
  }
    
      
  for(var adGroupCounter = 0; adGroupCounter < keys.length; adGroupCounter++) {
    var key = keys[adGroupCounter];
    var adGroup = adGroupList[key];
    var adGroupName = adGroup.adGroupName;
    var adGroupQS = adGroup.qs;
    var adGroupImpressions = adGroup.impressions;
    var adGroupCost = adGroup.cost;
    Logger.log(adGroupName + " cost: $" + adGroupCost.toFixed(2) + " imp: " + adGroupImpressions + " QS: " + adGroupQS.toFixed(2));
  }
  
  
  if(emailNotificationRecipients != "") {
   MailApp.sendEmail(emailNotificationRecipients, subject, emailBody); 
  } 
}