Store Account, Campaign, AdGroup, and Keyword Level Quality Score

UPDATE: 2014-02-15 - I updated the script to v2.0 which speeds the script up dramatically, includes the ability to store data into a CSV and use whatever date range you like for the stats.

I've had a lot of good feedback on my previous script: Store Account Level Quality Score. To that end, I've been asked a few times about storing Campaign and/or AdGroup level quality scores as well so I figured it would be a good time for an update.

Below is a script that will attempt to store Account, Campaign, and AdGroup level quality scores for the top 50000 keywords in your account. It will store the results in a google spreadsheet. In order for this to work, you will need to set up a new google spreadsheet. with three sheets named Account, Campaign, and AdGroup. You can simply make a copy of my spreadsheet found here (File > Make a copy...) : Account, Campaign, AdGroup Quality Score Spreadsheet (No longer needed as of v1.1)

Thanks,
Russ

	/************************************
	* Store Account, Campaign, and AdGroup Level Quality Score
	* Version 2.3
	* ChangeLog v2.3
	* - Solved #NUM! issue by filtering out -- values
	* ChangeLog v2.2
	* - Updated KeywordText to Criteria
	* 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(SPREADSHEET_NAME) {
	var fileIter = DriveApp.getFilesByName(SPREADSHEET_NAME);
	if(fileIter.hasNext()) {
	var file = fileIter.next();
	spreadsheet = SpreadsheetApp.openById(file.getId());
	} else {
	spreadsheet = SpreadsheetApp.create(SPREADSHEET_NAME);
	}
	} 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','Criteria','KeywordMatchType',
	'IsNegative','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',
	'during',theDate].join(' ');
	var results = {};
	var reportIter = AdWordsApp.report(query, OPTIONS).rows();
	while(reportIter.hasNext()) {
	var row = reportIter.next();
	if(row.QualityScore == "--") { continue; }
	if(row.IsNegative == true || row.IsNegative === 'true') { continue; }
	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.Criteria+']' :
	(row.KeywordMatchType === 'Phrase') ? '"'+row.Criteria+'"' : row.Criteria;
	}
	//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;
	}

view raw account_level_qs.js hosted with ❤ by GitHub