Optmyzr
Tools
One-Click Optimizations™
Data Insights
Report Designer
Shopping Campaign Tools
Enhanced Scripts
Bid Management
Pricing
Blog
More
Knowledge Base
About Optmyzr
Community
Contact
Sign Up
Login
Script Download: Lin-Rodnitzky Ratio
New to scripts ?
See
step by step instructions
for running AdWords Scripts in general.
Need Assistance? We are here to help!
Contact Us
Script Code
Copy script code into your Clipboard.
// Copyright 2015 - Optmyzr Inc. // All rights reserved // For an Enhanced Script version of this script which works at the MCC level and is easier to modify, visit www.optmyzr.com function main() { var SETTINGS = new Object(); // --- CHANGE THESE SETTINGS SETTINGS.dateRange = "LAST_MONTH"; // --- DONT CHANGE ANYTHING AFTER THIS var currentSetting = new Object(); currentSetting.reportType = "SEARCH_QUERY_PERFORMANCE_REPORT"; currentSetting.metricsColumns = ['Impressions', 'Clicks', 'ConvertedClicks', 'Cost', 'KeywordTextMatchingQuery' ]; currentSetting.whereConditions = ['WHERE', 'ConvertedClicks', '>=', '0']; var columnsUsedArray = currentSetting.metricsColumns; var whereClause = currentSetting.whereConditions.join(" "); var columnsStr = currentSetting.metricsColumns.join(','); var reportDate = new Date(); var dateForFilename = reportDate.yyyymmdd(); var spreadsheetName = currentSetting.reportType + " (" + SETTINGS.dateRange + ") " + " created " + dateForFilename; var overwrite = 1; currentSetting.spreadsheetUrl = "NEW"; currentSetting.spreadsheetTab = ""; //var sheet = prepareSpreadsheet(spreadsheetName, currentSetting.spreadsheetUrl, currentSetting.spreadsheetTab, currentSetting.accountManagers, overwrite); // Pull the report with the options defined for all non-deleted campaigns var query = 'SELECT ' + columnsStr + ' ' + 'FROM ' + currentSetting.reportType + ' ' + whereClause + ' ' + 'DURING ' + SETTINGS.dateRange; //Logger.log("query: " + query); var report = AdWordsApp.report(query); var rows = report.rows(); var numRowsToWritePerCycle = 100; //writeReportToGoogleSheet(report, sheet, columnsUsedArray, overwrite, numRowsToWritePerCycle); SETTINGS.statsForConvertingQueries = new Object(); SETTINGS.statsForAll = new Object(); var numConvertingQueries = 0; SETTINGS.statsForAll.clicks = 0; SETTINGS.statsForAll.impressions = 0; SETTINGS.statsForAll.cost = 0; SETTINGS.statsForAll.convertedClicks = 0; SETTINGS.statsForConvertingQueries.clicks = 0; SETTINGS.statsForConvertingQueries.impressions = 0; SETTINGS.statsForConvertingQueries.cost = 0; SETTINGS.statsForConvertingQueries.convertedClicks = 0; while(rows.hasNext()) { var row = rows.next(); var clicks = parseInt(row['Clicks']); //Logger.log("clicks: " + clicks); var convertedClicks = parseInt(row['ConvertedClicks']); var impressions = parseInt(row['Impressions']); var cost = parseFloat(row['Cost']); var keyword = row['KeywordTextMatchingQuery']; //Logger.log(keyword); if(!keyword.indexOf("\=") != -1) { if(convertedClicks > 0) { SETTINGS.statsForConvertingQueries.clicks += clicks; SETTINGS.statsForConvertingQueries.impressions += impressions; SETTINGS.statsForConvertingQueries.cost += cost; SETTINGS.statsForConvertingQueries.convertedClicks += convertedClicks; numConvertingQueries++; } SETTINGS.statsForAll.clicks += clicks; SETTINGS.statsForAll.impressions += impressions; SETTINGS.statsForAll.cost += cost; SETTINGS.statsForAll.convertedClicks += convertedClicks; } } //Logger.log("numConvertingQueries: " + numConvertingQueries); //Logger.log("SETTINGS.statsForAll.clicks: " + SETTINGS.statsForAll.clicks); //Logger.log("SETTINGS.statsForConvertingQueries.clicks: " + SETTINGS.statsForConvertingQueries.clicks); var costPerConvertedClickForAll = SETTINGS.statsForAll.cost / SETTINGS.statsForAll.convertedClicks; var costPerConvertedClickForConvertingQueries = SETTINGS.statsForConvertingQueries.cost / SETTINGS.statsForConvertingQueries.convertedClicks; var lrRatio = costPerConvertedClickForAll / costPerConvertedClickForConvertingQueries; Logger.log("LR Ratio for Account '" + AdWordsApp.currentAccount().getName() + "': " + lrRatio.toFixed(2)); //Logger.log("costPerConvertedClickForAll: " + costPerConvertedClickForAll); //Logger.log("costPerConvertedClickForConvertingQueries: " + costPerConvertedClickForConvertingQueries); } // FUNCTION: writeReportToGoogleSheet function writeReportToGoogleSheet(report, sheet, columnsUsedArray, overwrite, numRowsToWritePerCycle) { var rowCounter = 0; var dataToWrite = new Array(); var dataAdded = 0; // Write Header if(overwrite) { var toWrite = new Array(); toWrite.push(columnsUsedArray); var range = sheet.getRange(1, 1, toWrite.length, toWrite[0].length); range.setValues(toWrite); } // Write Data var reportIterator = report.rows(); while(reportIterator.hasNext()){ var row = reportIterator.next(); var rowArray = new Array(); //Logger.log("row.length: " + row.length); for(var i = 0; i < columnsUsedArray.length; i++){ var key = columnsUsedArray[i]; var data = row[key]; //Logger.log("data: " + data); rowArray.push(data); } dataToWrite.push(rowArray); rowCounter++; if(rowCounter % numRowsToWritePerCycle == 0 || !reportIterator.hasNext()) { dataAdded = 1; Logger.log("write to sheet"); if(!overwrite || dataAdded) { var startRow = sheet.getLastRow(); var startColumn = 0; //sheet.getLastColumn(); } else { var startRow = 1; // accounts for 1 row of headers var startColumn = 0; } var maxRows = sheet.getMaxRows(); var maxColumns = sheet.getMaxColumns(); Logger.log("startRow: " + startRow + " startColumn: " + startColumn + " dataToWrite.length: " + dataToWrite.length + " dataToWrite[0].length: " + dataToWrite[0].length); var range = sheet.getRange(startRow+1, startColumn+1, dataToWrite.length, dataToWrite[0].length); range.setValues(dataToWrite); dataToWrite = new Array(); } } } // FUNCTION: prepareSpreadsheet function prepareSpreadsheet(spreadsheetName, spreadsheetUrl, spreadsheetTabName, accountManagers, overwrite) { // Error Checks if(spreadsheetUrl.toLowerCase().indexOf("new") == -1 && !spreadsheetTabName) { Logger.log("When using an existing spreadsheet for a report, you must also specify the name of the tab that will get the new data. We're starting a new spreadsheet for this report."); spreadsheetUrl = "new"; } // Create the new spreadsheet Logger.log("spreadsheetTabName: " + spreadsheetTabName); Logger.log("currentSetting.spreadsheetUrl: " + spreadsheetUrl); var newSpreadsheetCreated = 0; if(spreadsheetUrl.toLowerCase().indexOf("new") != -1) { var spreadsheet = SpreadsheetApp.create(spreadsheetName); var spreadsheetUrl = spreadsheet.getUrl(); //get all sheets except first and delete them and insert new sheets every time to avoid name error var allSheets = spreadsheet.getSheets(); for(var i=1,len=allSheets.length;i
Questions? Feel Free to
Contact Us
.