AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

Ads
5.2K members online now
5.2K members online now
Dive into advanced features like Remarketing, Flexible Bid Strategies, AdWords Editor, and AdWords Scripts
Guide Me
star_border
Reply

quality score script

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hello there,

 

I'm using the quality score script, wich is very usefull. Although because i'm running it everyday I think it's a pity that every line in my output file is in a row below. If you have a campaign with lots of keywords then it's very though to see if there is any progress at keyword level.

 

My wish is to see the same output but then in columns in stead of lines. So the excel output will be something like this :

 

                  

"keyword 1"    7.43    7.45    7.48

"keyword 2"    8.33    8.24    8.26

 

instead of how it is presented right now:

 

"keyword 1"    7.43

"keyword 2"    8.33

"keyword 1"    7.45

"keyword 2"    8.24

"keyword 1"    7.48

"keyword 2"    8.26

 

I'm using the following script:

/************************************
* Store Account, Campaign, and AdGroup Level Quality Score
* Version 2.1
* ChangeLog v2.1
*  - Ignore negatives
* ChangeLog v2.0
*  - Rewrote for speed using the reporting api
*  - Added ability to store data in .csv file
*  - Added the ability for custom date ranges
*  - Added the ability for Spreadsheet Names
* ChangeLog v1.3
*  - Updated writeToSpreadsheet function
*  - Added keyword level reporting
* ChangeLog v1.2
*  - Changed status to ENABLED
* ChangeLog v1.1
*  - Added APPEND option
*  - Added ability to create spreadsheet sheets
*  - Updated logic for faster spreadsheet insertion
* Created By: Russ Savage
* FreeAdWordsScripts.com
**************************************/
var DECIMALS = 4; //this will give you 4 decimal places of accuracy
//You can set this to anything in this list: TODAY, YESTERDAY, LAST_7_DAYS,
// THIS_WEEK_SUN_TODAY, THIS_WEEK_MON_TODAY, LAST_WEEK, LAST_14_DAYS,
// LAST_30_DAYS, LAST_BUSINESS_WEEK, LAST_WEEK_SUN_SAT, THIS_MONTH
var DATE_RANGE = 'LAST_30_DAYS';
// Or you can set this to any number of days you like. it overrides the DATE_RANGE set above
var LAST_N_DAYS = 0;
 
var CSV_FILE_PREFIX = ""; //Set this if you want to write to a set of CSV files, one for each account level.
var SPREADSHEET_URL = ""; //Set this if you have the url of a spreadsheet you want to update
var SPREADSHEET_NAME = ""; //Set this if you want to write to the name of a spreadsheet instead
   
function main() {
  var isCSV = (CSV_FILE_PREFIX !== "");
  var allData = getKeywordsReport();
  var tabs = ['Account','Campaign','AdGroup','Keyword'];
  for(var i in tabs) {
    var tab = tabs[i];
    var dataToWrite = [];
    var cols = getCols(tab);
    var rowKeys = getRowKeys(tab,Object.keys(allData));
    for(var x in rowKeys) {
      var rowArray = [];
      var key = rowKeys[x];
      var row = allData[key];
      for(var y in cols) {
        rowArray.push(row[cols[y]]);
      }
      dataToWrite.push(rowArray);
    }
    if(isCSV) {
      writeDataToCSV(tab,dataToWrite);
    } else {
      writeDataToSpreadsheet(tab,dataToWrite);
    }
  }
}
 
function getRowKeys(tab,allKeys) {
  return allKeys.filter(function(e) { return (e.indexOf(tab) >= 0); });
}
 
function getCols(tab) {
  return {
    'Account' : ['Date','Account','ImpsWeightedQS'],
    'Campaign': ['Date','Account','Campaign','ImpsWeightedQS'],
    'AdGroup' : ['Date','Account','Campaign','AdGroup','ImpsWeightedQS'],
    'Keyword' : ['Date','Account','Campaign','AdGroup','Keyword','QS','ImpsWeightedQS']
  }[tab];
}
 
// Super fast spreadsheet insertion
function writeDataToSpreadsheet(tab,toWrite) {
  //This is where i am going to store all my data
  var spreadsheet;
  if("QS AdWords  (Account / Campagnes / Advertentiegroepen / Zoekwoorden)") {
    var fileIter = DriveApp.getFilesByName("QS AdWords  (Account / Campagnes / Advertentiegroepen / Zoekwoorden)");
    if(fileIter.hasNext()) {
      var file = fileIter.next();
      spreadsheet = SpreadsheetApp.openById(file.getId());
    } else {
      spreadsheet = SpreadsheetApp.create("QS AdWords  (Account / Campagnes / Advertentiegroepen / Zoekwoorden)");
    }
  } else if(SPREADSHEET_URL) {
    spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  } else {
    throw 'You need to set at least one of the SPREADSHEET_URL or SPREADSHEET_NAME variables.';
  }
  var sheet = spreadsheet.getSheetByName(tab);
  if(!sheet) {
    sheet = spreadsheet.insertSheet(tab);
    sheet.appendRow(getCols(tab));
  }
   
  var lastRow = sheet.getLastRow();
  var numRows = sheet.getMaxRows();
  if((numRows-lastRow) < toWrite.length) {
    sheet.insertRowsAfter((lastRow == 0) ? 1 : lastRow,toWrite.length-numRows+lastRow);
  }
  var range = sheet.getRange(lastRow+1,1,toWrite.length,toWrite[0].length);
  range.setValues(toWrite);
}
 
function writeDataToCSV(tab,toWrite) {
  if(!toWrite) { return; }
  var fileName = CSV_FILE_PREFIX + '_' + tab + '.csv';
  var file;
  var fileIter = DriveApp.getFilesByName(fileName);
  if(fileIter.hasNext()) {
    file = fileIter.next();
  } else {
    file = DriveApp.createFile(fileName, formatCsvRow(getCols(tab)));
  }
  var fileData = file.getBlob().getDataAsString();
  for(var i in toWrite) {
    fileData +=  formatCsvRow(toWrite[i]);
  }
  file.setContent(fileData);
  return file.getUrl();
}
 
function formatCsvRow(row) {
  for(var i in row) {
    if(row[i].toString().indexOf('"') == 0) {
      row[i] = '""'+row[i]+'""';
    }
    if(row[i].toString().indexOf('+') == 0) {
      row[i] = "'"+row[i];
    }
    if(row[i].toString().indexOf(',') >= 0 &&
       row[i].toString().indexOf('"""') != 0)
    {
      row[i] = ('"'+row[i]+'"');
    }
  }
  return row.join(',')+'\n';
}
 
function getKeywordsReport() {
  var theDate = DATE_RANGE;
  if(LAST_N_DAYS != 0) {
    theDate = getDateDaysAgo(LAST_N_DAYS)+','+getDateDaysAgo(1);
  }
  Logger.log('Using date range: '+theDate);
  var OPTIONS = { includeZeroImpressions : true };
  var cols = ['ExternalCustomerId',
              'CampaignId','CampaignName',
              'AdGroupId','AdGroupName',
              'Id','KeywordText','KeywordMatchType',
              'Impressions', 'QualityScore'];
  var report = 'KEYWORDS_PERFORMANCE_REPORT';
  var query = ['select',cols.join(','),'from',report,
               'where AdNetworkType1 = SEARCH',
               'and CampaignStatus = ENABLED',
               'and AdGroupStatus = ENABLED',
               'and Status = ENABLED',
           'and IsNegative = false',
               'during',theDate].join(' ');
  var results = {};
  var reportIter = AdWordsApp.report(query, OPTIONS).rows();
  while(reportIter.hasNext()) {
    var row = reportIter.next();
    loadHashEntry('Account:'+row.ExternalCustomerId,row,results);
    loadHashEntry('Campaign:'+row.CampaignId,row,results);
    loadHashEntry('AdGroup:'+[row.CampaignId,row.AdGroupId].join('-'),row,results);
    loadHashEntry('Keyword:'+[row.CampaignId,row.AdGroupId,row.Id].join('-'),row,results);
  }
  var dateStr = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');
  for(var i in results) {
    results[i]['Date'] = dateStr;
    results[i]['ImpsWeightedQS'] = (results[i]['totalImps'] === 0) ? 0 : round(results[i]['ImpsWeightedQS']/results[i]['totalImps']);
  }
  return results;
}
 
function loadHashEntry(key,row,results) {
  if(!results[key]) {
    results[key] = {
      QS : 0,
      ImpsWeightedQS : 0,
      totalImps : 0,
      Account : null,
      Campaign : null,
      AdGroup : null,
      Keyword : null
    };
  }
  results[key].QS = parseFloat(row.QualityScore);
  results[key].ImpsWeightedQS += (parseFloat(row.QualityScore)*parseFloat(row.Impressions));
  results[key].totalImps += parseFloat(row.Impressions);
  results[key].Account = row.ExternalCustomerId;
  results[key].Campaign = row.CampaignName;
  results[key].AdGroup = row.AdGroupName;
  results[key].Keyword = (row.KeywordMatchType === 'Exact') ? '['+row.KeywordText+']' :
                         (row.KeywordMatchType === 'Phrase') ? '"'+row.KeywordText+'"' : row.KeywordText;
}
 
//A helper function to return the number of days ago.
function getDateDaysAgo(days) {
  var thePast = new Date();
  thePast.setDate(thePast.getDate() - days);
  return Utilities.formatDate(thePast, AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
}
 
function round(val) {
  var divisor = Math.pow(10,DECIMALS);
  return Math.round(val*divisor)/divisor;
}

 

 

Please advise. Thanks

1 Expert replyverified_user

Re: quality score script

Top Contributor
# 2
Top Contributor

Hi @Sven A B although it seems like this should be a simple edit, in fact it's quite complex.  Working with sheets has a simple "append" option that writes data to the first empty row in a sheet so this is very easy to use for producing daily stats (as you have now).  Each day the script just writes to the next row and the code is straightforward.

 

Unfortunately, there's no equivalent command to append horizontally (by columns).  The code to do so isn't hard (there's a lookup to find the last column with data in it) but that's not really the biggest problem.

 

When you write in rows as you are now, you don't have to worry about matching the Keyword to the data because the Keyword is written with each row in column A.  If you want to write the data by columns, with each column being a different day, the script has to know which row a certain Keyword is on.  You can't rely upon the Keyword data being returned always in the same order since this can be affected by how the Keywords are ordered (there's no order defined in this script, so I'm not sure what their current ordering is) and of course will be affected if you add or delete Keywords.  So a script writing data by columns has to include some form of lookup that compares the Keyword the script has found a QS for with the existing Keywords listed in the sheet, then write to the correct row.  It's not that complex, but it will require quite a re-write of the script you have here.

 

I would also suggest that if you're writing this out each day, by the end of the year you're going to have 365 columns which won't be easy to scroll through so ideally you'll want to write the new results to the first column, not the last, which makes it slightly more complex.

 

All in all, you'd need a complete re-write here to do what you want...

 

Jon

AdWords Top Contributor Google+ Profile | Partner Profile | AdWords Audits

quality score script

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

You could combine both the previous script + pivot table to get the job done here as well.  You could run a pivot table with the KWs as rows and add the date as columns with the QS being the value.  It would plot it out the way you are looking to do without having to change the script. If you select the rows as the data set it would update with any new data added.