Shoora
6/12/2014 - 7:54 PM

Track AdWords Quality Score with Google Analytics

Track AdWords Quality Score with Google Analytics

***********************************************
*v. 1.6 updated 02062014
* Credits:
* Alexey Chesnok google.com/+AlexeyChesnok
* Based on:
* Quality score calculator by Frederick Vallaeys at Optmyzr 
* http://www.optmyzr.com/scripts Buy his Scripts, they are awesome!
* License http://creativecommons.org/licenses/by-nc/4.0/deed.en_US
* and google analytics beacon by Russell Savage 
* http://www.freeadwordsscripts.com/2013/11/track-adwords-script-runs-with-google.html
************************************************/

/*
* Setup
* Step-by-Step Set up instructions http://goo.gl/hZkJN3
*Update UA_ID to your Universal analytics ID and 
*yourdomain.com on the last page. 
*I strongly recommend creating separate UA profile! 
*End of setup
*/

function main() {
 
  var time = "YESTERDAY"; 
  var date_str = Utilities.formatDate(new Date(),AdWordsApp.currentAccount().getTimeZone(),'yyyy-MM-dd'); 
  var accountId = AdWordsApp.currentAccount().getCustomerId();
  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 keywordReport = AdWordsApp.report(
    'SELECT Impressions, Cost, Clicks, KeywordText, QualityScore, AdGroupName, CampaignName ' +
    'FROM   KEYWORDS_PERFORMANCE_REPORT ' +
    'WHERE  AdNetworkType2 = SEARCH ' +
    'AND    Impressions > 0 ' +
    'AND    Status != DELETED ' +
    'AND    Device != HIGH_END_MOBILE ' +
    'DURING ' + time );
  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'];
     
     
    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 ' +
    'AND    Device != HIGH_END_MOBILE ' +
    '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));
 _uacall('AccountQS',totalQS.toFixed(2)*100);
  
   
  // CAMPAIGN QS
 Logger.log("Campaigns");
  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);
    }
   
      
  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));
   _uacall(campaignName,campaignQS.toFixed(2)*100);
   Logger.log(campaignName)
  }
 
       
 
  // Ad GROUP QS
  Logger.log("");
  Logger.log("Ad Groups");
  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(uniqueAdGroupName, adGroupQS.toFixed(2)*100)
    _uacall(uniqueAdGroupName, adGroupQS.toFixed(2)*100);
   }
     
       
  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;
    var uniqueAdGroupName = "" + campaignName + " - " + adGroupName;
    }
}

// Universal analytics calls
// Events
function _uacall(qlabel,qvalue) {
  var UA_ID = 'UA-XXX-X';
  var EVENT_CATEGORY = 'PC Tablets';
  var EVENT_ACTION = 'script';
  var EVENT_VALUE = parseInt(qvalue);
  var EVENT_LABEL = qlabel;
  var CUSTOM_METRIC = parseInt(qvalue);
  var CAMPAIGN_SOURCE = 'PC Tablets';
  var CAMPAIGN_MEDIUM = 'script';
  var CAMPAIGN_NAME = qlabel;
  var HOSTNAME = 'yourdomain.com';
  var PAGE = '/ScriptTest';
  var DOMAIN_LINK = 'http://'+HOSTNAME+PAGE;
    
 
 var uuid = 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, 
    function(c) {var r = Math.random()*16|0,v=c=='x'?r:r&0x3|0x8;return v.toString(16);});
   
  var url = 'http://www.google-analytics.com/collect?';
  var payload = {
 'v':1,'tid':UA_ID,'cid':uuid,    
 't':'event','ec':EVENT_CATEGORY,'ea':EVENT_ACTION,'el':EVENT_LABEL,'ev':EVENT_VALUE,'cm1':CUSTOM_METRIC,'cs':CAMPAIGN_SOURCE,'cm':CAMPAIGN_MEDIUM,'cn':CAMPAIGN_NAME,
    'dl':DOMAIN_LINK

    
  };
  
  var qs = '';
  for(var key in payload) {
    qs += key + '=' + encodeURIComponent(payload[key]) + '&';
  }
  url += qs.substring(0,qs.length-1);
  UrlFetchApp.fetch(url);
  Utilities.sleep(1000)
  Logger.log(url)
 }