Gestire delle Campagne AdWords può richiedere davvero tantissimo tempo. Creare nuove campagne, ottimizzare quelle esistenti e tante altri operazioni si susseguono ciclicamente, ed è proprio per questo motivo che Google ha introdotto gli AdWords Script: programmi scritti in Javascript permettono agli inserzionisti di eseguire delle azione automaticamente in determinati momenti del giorno, settimana,… .
Argomenti del post
Controllare lo stato delle landing page
Può capitare di avere delle landing page che portano ad errori 404 oppure avere delle campagne attive per prodotti che non sono più disponibili all’interno di un sito di e-commerce.
Grazie a questo script è possibile ricevere un’email con una lista di tutti gli url di campagne AdWords attive e che presentano dei problemi che appunto potrebbero compromettere i risultati.
/**
*
* Script AdWords per controllare lo stato delle landing page.
* Versione: 1.0
* Script di brainlabsdigital.com
*
**/
function main() {
var messagesToCheckFor = ["hey", "jude"];
// What out of stock messages appear on the source code of your landing pages?
// Enter like ["Out of stock", "<em>0 available</em>"]
var trimAtQuestionMark = true;
// Do you want to remove all parameters which occur after the '?' character?
// Enter true or false
var type = "keywords";
// Choose "keywords" or "ads"
// Are your final URLs at the keyword or ad level?
var recipients = ["a@b.com"];
// If set, these addresses will be emailed with a list of any bad URLs.
// Enter like ["a@b.com"] or ["a@b.com","c@d.com","e@g.co.uk"]
// Leave as [] to skip.
// Optional filtering options
// Enter like ["hey", "jude"]
// Leave as [] to skip
var containsArray = [];
// If set, only campaigns whose names contain these phrases will be checked
var excludesArray = [];
// If set, campaigns whose names contain any of these phrases will be ignored.
var labelArray = [];
// If set, only keywords / ads with these labels will be checked
// Case sensitive.
// Status options
// Choose from ["ENABLED"] if you only want enabled entities
// ["PAUSED"] if you only want paused entities
// ["ENABLED","PAUSED"] if you want enabled and paused entities
var campaignStatus = ["ENABLED"];
// The status of the campaigns
var adGroupStatus = ["ENABLED"];
// The status of the ad groups
var status = ["ENABLED"];
// The status of the keywords / ads
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
var urls = [];
var bad_urls = [];
var urlFetchOptions = {muteHttpExceptions: true};
var countEntities = 0;
var conditions = [];
if (containsArray.length > 0) {
conditions.push(" where the campaign name contains " + containsArray.join(", "));
}
if (excludesArray.length > 0) {
conditions.push(" where the campaign name excludes " + excludesArray.join(", "));
}
if (labelArray.length > 0) {
conditions.push(" where the " + type + " are labelled " + labelArray.join(", "));
}
if (containsArray.length === 0) {
containsArray.push("");
}
for(var i = 0; i < containsArray.length; i++){
var string = iteratorConstructor(type, containsArray[i], excludesArray, labelArray, status, campaignStatus, adGroupStatus);
eval(string);
countEntities += iterator.totalNumEntities();
excludesArray.push(containsArray[i]);
while(iterator.hasNext()){
var object = iterator.next();
var url = object.urls().getFinalUrl();
if(url == null || url == undefined){
url = object.getDestinationUrl();
}
if(url !== null && url !== undefined){
if(trimAtQuestionMark){
url = url.split('?')[0];
}
if(urls.indexOf(url) === -1) {
urls.push(url);
}
}
}
}
if (countEntities == 0) {
throw "No " + type + " found" + conditions.join("; and");
}
Logger.log(countEntities + " " + type + " found" + conditions.join("; and"));
Logger.log(urls.length + " unique URLs to check.");
for(var x in urls){
var response = UrlFetchApp.fetch(urls[x],urlFetchOptions);
var code = response.getContentText();
for(var y = 0; y < messagesToCheckFor.length; y++){ var message = messagesToCheckFor[y]; if(code.indexOf(message) !== -1){ bad_urls.push(urls[x]); break; } } } if (bad_urls.length === 0) { Logger.log("No bad URLs found."); } else { Logger.log(bad_urls.length + " found:"); Logger.log(bad_urls.join("\n")); } if(recipients.length > 0 && bad_urls.length > 0){
var name = AdWordsApp.currentAccount().getName();
var subject = name + " URL checking";
var body = 'The following URLs were found to have one of the following phrases in their web page source code. \n\nPhrases:\n"' + messagesToCheckFor.join('",\n"') + '"\n\nURLs:\n';
body += bad_urls.join("\n");
MailApp.sendEmail(recipients.join(","),subject,body);
Logger.log("Email sent to " + recipients.join(", "));
}
function iteratorConstructor(type, containsString, excludesArray, labelArray, status, campaignStatus, adGroupStatus){
var string = "var iterator = AdWordsApp."+type+"()";
if (containsString != "") {
string = string + ".withCondition('CampaignName CONTAINS_IGNORE_CASE " + '"' + containsString + '"' + "')";
}
for(var i = 0; i < excludesArray.length; i++){ string = string + ".withCondition('CampaignName DOES_NOT_CONTAIN_IGNORE_CASE " + '"' + excludesArray[i] + '"' + "')"; } if(labelArray.length > 0){
string = string + ".withCondition('LabelNames CONTAINS_ANY " + '["' + labelArray.join('","') + '"]' + "')";
}
string = string + ".withCondition('Status IN [" + status.join(",") + "]')";
string = string + ".withCondition('CampaignStatus IN [" + campaignStatus.join(",") + "]')";
string = string + ".withCondition('AdGroupStatus IN [" + adGroupStatus.join(",") + "]')";
string = string + ".orderBy('Cost DESC').forDateRange('LAST_30_DAYS')";
string = string + ".withLimit(50000)";
string = string + ".get();"
return string;
}
}
Monitorare il Punteggio di Qualità con Google Spreadsheet
Probabilmente è uno degli AdWords Script più famosi. Sviluppato da Martin Roettgerding, questo script permette di salvarsi in un foglio spreadsheet tutti i punteggi di qualità delle proprie campagne AdWords.
È così possibile analizzare l’andamento di questo importante fattore col passare del tempo, cosa che non è possibile fare all’interno dell’interfaccia.
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Quality Score Tracker v3.0.2
* Written by Martin Roettgerding.
* © 2016 Martin Roettgerding, Bloofusion Germany GmbH.
* www.ppc-epiphany.com/qstracker/latest
*/
function main(){
/*
* The following preferences can be changed to customize this script.
* Most of options can be set by using 1 for yes or 0 for no.
* You don't have to change anything here. The script will do fine with the defaults.
*/
var config = {
/*
* Which of the following charts should be displayed on the dashboard?
* The "per QS" charts are column charts. They show the current state compared to a previous one (see next option).
* "Average" and "weighted" charts are line charts, showing changes over time.
*/
"chartsToDisplay" : {
"Keywords per QS" : 0,
"Average QS" : 0,
"Keywords with Impressions per QS" : 1,
"Average QS for Keywords with Impressions" : 0,
"Impressions per QS" : 0,
"Impression weighted QS" : 1,
"Clicks per QS" : 0,
"Click weighted QS" : 0,
"Conversions per QS" : 0,
"Conversion weighted QS" : 0,
"Conversion value per QS" : 0,
"Conversion value weighted QS" : 0,
},
/*
* Column charts can show a former date for comparison. Set the number of steps you want to go back for this.
* Note that the date you're comparing this to will depend on how often you've run the script in the past.
* Example: If the setting is 30 and you ran the script daily, your comparison will be with the values from 30 days before. If you ran it hourly, it will be with values from 30 hours before.
* If you haven't run the script often enough, the comparison will go as far back as possible.
* Put 0 to disable the comparison.
*/
"chartsCompareStepsBack" : 30,
/*
* When stats are taken into account (like impressions per QS, or impression weighted QS), this timeframe is used.
* Note that this affects the values to be tracked and stored. Past values that are already stored won't be affected.
* Use one of the following: 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
*/
"statsTimeframe" : "LAST_30_DAYS",
/*
* Whether to only look at stats from Google (e.g. for impression weighted QS).
* Recommended. Quality Score itself only reflects data from Google, so weighting should only take Google into account and leave out search partners.
* Note that this affects the values to be tracked and stored. Past values that are already stored won't be affected.
*/
"googleOnly" : 1,
/*
* Whether to only track active keywords. This means that the keyword, the adgroup, and the campaign have to be enabled.
* Recommended. Otherwise inactive keywords with meaningless Quality Scores might skew your data.
*/
"activeKeywordsOnly" : 1,
/*
* Set to 1 if you want your dates (in charts, table headers, and file names) to contain hours and minutes as well.
* Do this if you want to run the script hourly.
*/
"useHours" : 0,
/*
* Use this option to not keep track of individual keywords' Quality Scores and only save data to the dashboard file.
* This makes sense if you have more than 400,000 keywords. Note that you don't have to change this: The script will notice on its own and log a message otherwise.
*/
"skipIndividualKeywords" : 0,
/*
* The name of the file where dashboard and summarized data are stored.
*/
"summaryFileName" : "Dashboard + Summary",
/*
* The base folder for all Quality Score Tracker files.
*/
"baseFolder" : "Quality Score Tracker/",
/*
* Whether to add a client folder in the base folder (resulting in a folder like "Quality Score Tracker/CLIENT_NAME (123-456-7890)/")
* The folder's name is not important, as long as the Adwords client id remains in it.
* This is useful if you want to track multiple accounts with this script.
*/
"useClientFolder" : 1,
}
trackQS(config);
}
function trackQS(config){
var version = "3.0";
if(config['useHours']) var dateString = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd HH:mm");
else var dateString = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd");
var folder = getOrCreateFolder(config['baseFolder']);
if(config['useClientFolder']) folder = getOrCreateClientFolder(folder);
// Find the latest report file in the folder.
var maxFileNumber = 0;
var reportFile;
var summaryFile;
var fileIterator = folder.getFiles();
while(fileIterator.hasNext()){
var file = fileIterator.next();
var matches = new RegExp(' #([0-9]+) ').exec(file.getName());
if(matches && parseInt(matches[1]) > maxFileNumber){
maxFileNumber = parseInt(matches[1]);
reportFile = file;
}else if(file.getName() == config['summaryFileName']) summaryFile = file;
}
// No report file found? Add a new one.
if(maxFileNumber == 0){
reportFile = addReportFile(folder, "QS Report #1 (" + dateString + ")");
maxFileNumber = 1;
}
// No summary file found? Add a new one.
if(!summaryFile) summaryFile = addSummaryFile(folder, config['summaryFileName']);
Logger.log("All files are stored at");
Logger.log(folder.getUrl());
Logger.log("The dashboard is here:");
Logger.log(summaryFile.getUrl());
var spreadsheet = SpreadsheetApp.open(reportFile);
var sheet = spreadsheet.getActiveSheet();
var idColumnValues = sheet.getRange(1, 4, sheet.getLastRow(), 1).getValues();
var summarySpreadsheet = SpreadsheetApp.open(summaryFile);
updateInfo(summarySpreadsheet, version);
var sheetCharts = summarySpreadsheet.getSheetByName("Dashboard");
summarySpreadsheet.setActiveSheet(sheetCharts);
summarySpreadsheet.moveActiveSheet(1);
// Track an event in Google Analytics.
trackInAnalytics(version);
// Remember the line number for every keyword.
var lineNumbers = {};
var lastRowNumber = sheet.getLastRow();
for(var i = 1; i < lastRowNumber; i++){
lineNumbers[idColumnValues[i][0]] = i;
}
// qsValues represents the new column that will go right next to the others.
var qsValues = new Array(sheet.getLastRow());
qsValues[0] = [ dateString ];
// Initialize everything with an empty string.
var qsValuesLength = qsValues.length;
for(var i = 1; i < qsValuesLength; i++) qsValues[i] = [""];
// In case new keywords are found, they'll be added as new rows below the rest (campaign, adgroup, keyword, id string).
var newRows = [];
// All aggregated data goes in this variable.
var qsStats = {
"Keywords" : {},
"Keywords with impressions" : {},
"Impressions" : {},
"Clicks" : {},
"Conversions" : {},
"Conversion value" : {}
};
// Initialize the arrays so that everything can be added up later. Index 0 is for totals, 1-10 for Quality Scores.
for(var key in qsStats){
for(var i = 0; i <= 10; i++){ qsStats[key][i] = 0; } } // Get the data from AdWords. var awql = "SELECT Id, Criteria, KeywordMatchType, CampaignId, CampaignName, AdGroupId, AdGroupName, QualityScore, Impressions, Clicks, Conversions, ConversionValue FROM KEYWORDS_PERFORMANCE_REPORT WHERE Id NOT_IN [3000000, 3000006]"; if(config['googleOnly']) awql += " AND AdNetworkType2 = 'SEARCH'"; if(config['activeKeywordsOnly']) awql += " AND CampaignStatus = 'ENABLED' AND AdGroupStatus = 'ENABLED' AND Status = 'ENABLED'"; awql += " DURING " + config['statsTimeframe']; var report = AdWordsApp.report(awql); var reportRows = report.rows(); // Go through the report and count Quality Scores. while(reportRows.hasNext()){ var row = reportRows.next(); // Save the aggregated data. qsStats['Keywords'][row['QualityScore']]++; if(row['Impressions'] > 0) qsStats['Keywords with impressions'][row['QualityScore']]++;
qsStats['Impressions'][row['QualityScore']] += parseInt(row['Impressions']);
qsStats['Clicks'][row['QualityScore']] += parseInt(row['Clicks']);
qsStats['Conversions'][row['QualityScore']] += parseInt(row['Conversions']);
qsStats['Conversion value'][row['QualityScore']] += parseInt(row['ConversionValue']);
// Save the individual keyword's Quality Score.
if(!config['skipIndividualKeywords']){
var id = row['CampaignId']+"_"+row['AdGroupId']+"_"+row['Id'];
// Check if there is already a line for this keyword
if(lineNumbers[id]) var line_number = lineNumbers[id];
else{
// There is no line for this keyword yet. Create a new one and add the line headers.
line_number = qsValues.length;
if(row['KeywordMatchType'] == "Exact") var keyword = '[' + row['Criteria'] + ']';
else if(row['KeywordMatchType'] == "Phrase") var keyword = '"' + row['Criteria'] + '"';
else var keyword = row['Criteria'];
newRows.push([row['CampaignName'], row['AdGroupName'], keyword, id]);
}
qsValues[line_number] = [row['QualityScore']];
}
}
// Check if everything fits.
if(!config['skipIndividualKeywords']){
// A spreadsheet can hold up to 2 million cells. Calculate if the new data will fit in with the rest.
// With four rows needed for every keyword, plus one for every tracking run, this won't fit if there are more than 400,000 rows (header + 399,999 keywords).
if(qsValues.length >= 400000){
Logger.log("There are too many keywords to be tracked (" + qsValues.length + "). This tool can only track up to 399,999 keywords.");
Logger.log("A summary will be logged, but individual keyword quality scores cannot be stored.");
skipIndividualKeywords = true;
}else if(qsValues.length * (sheet.getLastColumn() + 1) > 2000000){
// This spreadsheet is full, a new one is needed.
// Add new file.
maxFileNumber++;
reportFile = addReportFile(folder, "QS Report #" + maxFileNumber + " (" + dateString + ")");
var newSpreadsheet = SpreadsheetApp.open(reportFile);
var newSheet = newSpreadsheet.getActiveSheet();
// Copy the first columns from the old sheet to the new one.
newSheet.getRange(1, 1, sheet.getLastRow(), 4).setValues(sheet.getRange(1, 1, sheet.getLastRow(), 4).getValues());
// From now on, work with the new sheet and spreadsheet.
spreadsheet = newSpreadsheet;
sheet = newSheet;
}
}
// Store the keyword data in the spreadsheet.
if(!config['skipIndividualKeywords']){
// If there are new rows, add their line headers beneath the others.
if(newRows.length > 0){
var sheetLastRow = sheet.getLastRow();
sheet.insertRowsAfter(sheetLastRow, newRows.length).getRange(sheetLastRow + 1, 1, newRows.length, 4).setValues(newRows);
sheet.autoResizeColumn(1).autoResizeColumn(2).autoResizeColumn(3);
}
// Add a new column with the tracked data.
var sheetLastColumn = sheet.getLastColumn();
sheet.insertColumnAfter(sheetLastColumn);
sheet.getRange(1, sheetLastColumn + 1, qsValues.length, 1).setValues(qsValues);
sheet.autoResizeColumn(sheetLastColumn + 1);
// Change file name to reflect the new date.
// Find out which dates are currently noted in the file's name.
var matches = /\((.*?)( - (.*))?\)/.exec(reportFile.getName());
if(matches && matches[1]){
if(matches[2]){
// There's a start date and an end date.
var startDate = matches[1];
var endDate = matches[3];
if(endDate != dateString){
var newFileName = reportFile.getName().replace(endDate, dateString);
reportFile.setName(newFileName);
}
}else{
// There's just a start date.
var startDate = matches[1];
if(startDate != dateString){
var newFileName = reportFile.getName().replace(startDate, startDate + " - " + dateString);
reportFile.setName(newFileName);
}
}
}else{
Logger.log("Could not recognize dates in file name " + reportFile.getName() +". File name remains unchanged.");
}
}
// Now take care of the summary file.
// Get the total numbers.
for(var key in qsStats){
for(var i = 1; i <= 10; i++){
qsStats[key][0] += qsStats[key][i];
}
}
// Prepare a new column for the Percentages data sheet.
var newValues = [];
var newValuesNumberFormats = [];
for(var key in qsStats){
newValues.push([dateString]);
newValuesNumberFormats.push(["@STRING@"]);
for(var i = 1; i <= 10; i++){ if(qsStats[key][0]) newValues.push([qsStats[key][i] / qsStats[key][0]]); else newValues.push([0]); newValuesNumberFormats.push(["0.00%"]); } newValues.push([qsStats[key][0]]); newValuesNumberFormats.push(["0.##"]); } var sheetPercentages = summarySpreadsheet.getSheetByName("Percentages"); var sheetAverages = summarySpreadsheet.getSheetByName("Averages"); var lastCol = sheetPercentages.getLastColumn() + 1; var lastRow = sheetAverages.getLastRow() + 1; // Add the data to the Percentages sheet. sheetPercentages.insertColumnAfter(lastCol - 1); sheetPercentages.getRange(1, lastCol, 72, 1).setNumberFormats(newValuesNumberFormats).setValues(newValues); sheetPercentages.autoResizeColumn(lastCol); // Add a new row with formulas to the Averages sheet. sheetAverages.appendRow([""]); sheetAverages.getRange(lastRow, 1, 1, 1).setValue(dateString); sheetAverages.getRange(lastRow, 2, 1, 6).setFormulasR1C1([[ "=SUMPRODUCT(Percentages!R2C1:R11C1; Percentages!R2C" + lastCol + ":R11C" + lastCol + ")", "=SUMPRODUCT(Percentages!R14C1:R23C1; Percentages!R14C" + lastCol + ":R23C" + lastCol + ")", "=SUMPRODUCT(Percentages!R26C1:R35C1; Percentages!R26C" + lastCol + ":R35C" + lastCol + ")", "=SUMPRODUCT(Percentages!R38C1:R47C1; Percentages!R38C" + lastCol + ":R47C" + lastCol + ")", "=SUMPRODUCT(Percentages!R50C1:R59C1; Percentages!R50C" + lastCol + ":R59C" + lastCol + ")", "=SUMPRODUCT(Percentages!R62C1:R71C1; Percentages!R62C" + lastCol + ":R71C" + lastCol + ")" ]]); // The properties for the charts. This is not meant to be reconfigured. var chartsProperties = { "Keywords per QS" : { "type" : "column", "vCol" : 2, }, "Average QS" : { "type" : "line", "vCol" : 2, }, "Keywords with Impressions per QS" : { "type" : "column", "vCol" : 3, }, "Average QS for Keywords with Impressions" : { "type" : "line", "vCol" : 3, }, "Impressions per QS" : { "type" : "column", "vCol" : 4, }, "Impression weighted QS" : { "type" : "line", "vCol" : 4, }, "Clicks per QS" : { "type" : "column", "vCol" : 5, }, "Click weighted QS" : { "type" : "line", "vCol" : 5, }, "Conversions per QS" : { "type" : "column", "vCol" : 6, }, "Conversion weighted QS" : { "type" : "line", "vCol" : 6, }, "Conversion value per QS" : { "type" : "column", "vCol" : 7, }, "Conversion value weighted QS" : { "type" : "line", "vCol" : 7, }, }; var row = 1; var col = 1; var summarySheets = { "dataH": sheetPercentages, "dataV": sheetAverages, "charts": sheetCharts, } // Add charts to the dashboard. for(var chartName in config['chartsToDisplay']){ // Skip all charts that are not set to be displayed. if(!config['chartsToDisplay'][chartName]) continue; addChartToDashboard(chartName, chartsProperties[chartName]['type'], summarySheets, row, col, lastRow, lastCol, chartsProperties[chartName]['vCol'], config['chartsCompareStepsBack']); // Add the "Average QS" cells. sheetCharts.setRowHeight(row, 60).setRowHeight(row + 1, 20).setRowHeight(row + 2, 270); sheetCharts.getRange(row, 2).setValue("Average QS").setFontWeight("bold").setFontSize(24).setBorder(true, true, false, true, null, null); sheetCharts.getRange(row + 2, 2).setFontWeight("bold").setFontSize(24).setNumberFormat("0.00").setBorder(false, true, false, true, null, null); sheetCharts.getRange(row + 1, 2, 2, 1).setFormulasR1C1( [ ["=LOWER(Averages!R1C" + chartsProperties[chartName]['vCol'] + ")"], ["=Averages!R" + lastRow + "C" + chartsProperties[chartName]['vCol']] ]).setBorder(false, true, true, true, null, null); sheetCharts.autoResizeColumn(2); row += 3; } } /* * Checks if there is a folder with the given name in the Google Drive root folder. If not, the folder is created. * The folderName can be in the form of a complete path with subfolders, like "QS Reports/123/whatever". * Returns the folder. */ function getOrCreateFolder(folderName){ return getOrCreateFolderFromArray(folderName.toString().split("/"), DriveApp.getRootFolder()); } /* * Does the actual work for getOrCreateFolder. Recursive function, based on an array of folder names (to handle paths with subfolders). */ function getOrCreateFolderFromArray(folderNameArray, currentFolder){ var folderName = ""; // Skip empty folders (multiple slashes or a slash at the end). do folderName = folderNameArray.shift(); while(folderName == "" && folderNameArray.length > 0);
if(folderName == "") return currentFolder;
// See if the folder is already there.
var folderIterator = currentFolder.getFoldersByName(folderName);
if(folderIterator.hasNext()){
var folder = folderIterator.next();
}else{
// Create folder.
Logger.log("Creating folder '" + folderName + "'");
var folder = currentFolder.createFolder(folderName);
}
if(folderNameArray.length > 0) return getOrCreateFolderFromArray(folderNameArray, folder);
return folder;
}
/*
* Checks if there is a folder for the current client account in the base folder. If not, the folder is created.
* Existing client folders are recognized by the client id in parentheses. This way, folders can be found again, even if an account has been renamed.
*/
function getOrCreateClientFolder(baseFolder){
var folderIterator = baseFolder.getFolders();
var regExp = new RegExp(AdWordsApp.currentAccount().getCustomerId());
while(folderIterator.hasNext()){
var folder = folderIterator.next();
if(folder.getName().match(regExp)) return folder;
}
// Since no folder has been found: Create one.
var newFolderName = AdWordsApp.currentAccount().getName() + " (" + AdWordsApp.currentAccount().getCustomerId() + ")";
Logger.log("Creating folder '" + newFolderName + "'");
return baseFolder.createFolder(newFolderName);
}
/*
* Creates a spreadsheet for QS tracking.
* Adds headers to the spreadsheet.
* Returns the file.
*/
function addReportFile(folder, name){
var spreadsheet = SpreadsheetApp.create(name, 1, 4);
var sheet = spreadsheet.getActiveSheet();
sheet.setName("QS history");
// Put in the table headings
sheet.getRange(1, 1, 1, 4).setValues([["Campaign", "AdGroup", "Keyword", "ID string"]]);
//sheet.getRange(1, 1, 1, 4).setFontWeight("bold");
sheet.setColumnWidth(4, 1);
var file = DriveApp.getFileById(spreadsheet.getId());
folder.addFile(file);
var parentFolder = file.getParents().next();
parentFolder.removeFile(file);
return folder.getFilesByName(name).next();
}
/*
* Creates a spreadsheet for the summary and stores it in the folder.
* Creates sheets for the Percentages and Averages.
* Populates header rows and columns.
*/
function addSummaryFile(folder, name){
var spreadsheet = SpreadsheetApp.create(name);
var sheetH = spreadsheet.getActiveSheet();
sheetH.setName("Percentages");
// Add the first column for the horizontal data table.
sheetH.getRange(1, 1, 72, 1).setValues(
[["All keywords"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total'],
["Keywords with impressions"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total'],
["Impression weighted"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total'],
["Click weighted"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total'],
["Conversion weighted"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total'],
["Conversion value weighted"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total']
]
);
sheetH.getRange("A:A").setNumberFormat('@STRING@');
sheetH.autoResizeColumn(1);
var sheetV = spreadsheet.insertSheet("Averages");
// Add the first rows for the vertical data table.
sheetV.getRange(1, 1, 4, 7).setValues([
["Date", "Average", "Average for keywords with impressions", "Impression weighted", "Click weighted", "Conversion weighted", "Value weighted"],
["Highest", "", "", "", "", "", ""],
["Lowest", "", "", "", "", "", ""],
["Average", "", "", "", "", "", ""]
]);
// Add some formulas for maximums, minimums, and averages.
sheetV.getRange(2, 2, 3, 6).setFormulas([
["=MAX(B$5:B)", "=MAX(C$5:C)", "=MAX(D$5:D)", "=MAX(E$5:E)", "=MAX(F$5:F)", "=MAX(G$5:G)"],
["=MIN(B$5:B)", "=MIN(C$5:C)", "=MIN(D$5:D)", "=MIN(E$5:E)", "=MIN(F$5:F)", "=MIN(G$5:G)"],
["=AVERAGE(B$5:B)", "=AVERAGE(C$5:C)", "=AVERAGE(D$5:D)", "=AVERAGE(E$5:E)", "=AVERAGE(F$5:F)", "=AVERAGE(G$5:G)"]
]);
sheetV.getRange(1, 1, 1, 7).setFontWeight("bold").setNumberFormat('@STRING@');
sheetV.autoResizeColumn(1);
sheetV.autoResizeColumn(2);
sheetV.autoResizeColumn(3);
sheetV.autoResizeColumn(4);
sheetV.autoResizeColumn(5);
sheetV.autoResizeColumn(6);
sheetV.autoResizeColumn(7);
// Store the spreadsheet.
var file = DriveApp.getFileById(spreadsheet.getId());
folder.addFile(file);
var parentFolder = file.getParents().next();
parentFolder.removeFile(file);
return folder.getFilesByName(name).next();
}
/*
* Replaces the About sheet in the summary spreadsheet with a fresh one from the master sheet. This way, the sheet (including the FAQ) stays up to date.
* Also replaces the Dashboard with a fresh copy (resulting in an empty sheet with the correct conditional formatting).
* If there's a new version, a sheet "New Version Available!" is added.
*/
function updateInfo(summarySpreadsheet, version){
var templateSpreadsheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1qnTYdpBCgHP_5u5eQcXmc5gP0NrOrBK51JnTCTlc0_g/");
var oldSheet = summarySpreadsheet.getSheetByName("Dashboard");
if(oldSheet) summarySpreadsheet.deleteSheet(oldSheet);
templateSpreadsheet.getSheetByName("Dashboard v" + version).copyTo(summarySpreadsheet).setName("Dashboard");
var oldSheet = summarySpreadsheet.getSheetByName("About + FAQ");
if(oldSheet) summarySpreadsheet.deleteSheet(oldSheet);
templateSpreadsheet.getSheetByName("About v" + version).copyTo(summarySpreadsheet).setName("About + FAQ");
var oldSheet = summarySpreadsheet.getSheetByName("New Version Available!");
if(oldSheet) summarySpreadsheet.deleteSheet(oldSheet);
// Check if there is a newer version.
var versionHistory = templateSpreadsheet.getSheetByName("Version History").getDataRange().getValues();
if(versionHistory[0][0] != version){
// There's a new version available (at least one).
// Look for the row which has the info about the current (old) version.
var oldVersionRow = 1;
while(oldVersionRow < versionHistory.length && versionHistory[oldVersionRow][0] != version){ oldVersionRow++; } // Copy the entire version history. var newVersionSheet = templateSpreadsheet.getSheetByName("Version History").copyTo(summarySpreadsheet).setName("New Version Available!"); // Remove everything about the old version. newVersionSheet.deleteRows(oldVersionRow + 1, versionHistory.length - oldVersionRow); // Add new Rows at the beginning. newVersionSheet.insertRows(1, 6); newVersionSheet.getRange(1, 1, 6, 2).setValues([["Latest version:", versionHistory[0][0]], ["Your version:", version], ["", ""], ["Get the latest version at", "https://www.ppc-epiphany.com/qstracker/latest"], ["", ""], ["Newer Versions", ""]]); newVersionSheet.getRange(1, 1, 1, 2).setFontWeight("bold"); newVersionSheet.getRange(6, 1, 1, 1).setFontWeight("bold"); newVersionSheet.autoResizeColumn(1); newVersionSheet.autoResizeColumn(2); } } /* * Inserts a line or column chart into the dashboard sheet. * The chart is based on data from the Percentages or Averages sheet. */ function addChartToDashboard(name, type, sheets, row, col, lastRow, lastCol, vCol, compareStepsBack){ var chartBuilder = sheets['charts'].newChart(); chartBuilder .setOption('title', name) .setOption('width', 800) .setOption('height', 349) .setOption('colors', ['#fa9d1c','#00507d']) .setPosition(row, col, 0, 0); switch(type){ case "column": var statsRow = (vCol - 2) * 12 + 1; // First range for a column chart is always the same column with QS from 1 to 10. var dataRanges = [sheets['dataH'].getRange(1, 1, 11, 1)]; if(compareStepsBack && lastCol > 2){
// The column for comparison is either the specified number of columns behind lastCol, or 2 (the first column with data).
dataRanges.push(sheets['dataH'].getRange(statsRow, Math.max(2, lastCol - compareStepsBack), 11, 1));
}
dataRanges.push(sheets['dataH'].getRange(statsRow, lastCol, 11, 1));
chartBuilder = chartBuilder.asColumnChart();
break;
case "line":
var dataRanges = [sheets['dataV'].getRange(5, 1, lastRow - 2, 1), sheets['dataV'].getRange(5, vCol, lastRow - 2, 1)];
chartBuilder = chartBuilder.asLineChart();
chartBuilder.setOption("vAxis.maxValue", 10);
chartBuilder.setOption("vAxis.ticks", [0,2,4,6,8,10]);
chartBuilder.setLegendPosition(Charts.Position.NONE);
break;
}
for(var i in dataRanges) chartBuilder.addRange(dataRanges[i]);
sheets['charts'].insertChart(chartBuilder.build());
}
/*
* Tracks the execution of the script as an event in Google Analytics.
* Sends the version number and a random UUID (basically just a random number, required by Analytics).
* Basically tells that somewhere someone ran the script with a certain version.
* Credit for the idea goes to Russel Savage, who posted his version at https://www.freeadwordsscripts.com/2013/11/track-adwords-script-runs-with-google.html.
*/
function trackInAnalytics(version){
// Create the random UUID from 30 random hex numbers gets them into the format xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx (with y being 8, 9, a, or b).
var uuid = "";
for(var i = 0; i < 30; i++){
uuid += parseInt(Math.random()*16).toString(16);
}
uuid = uuid.substr(0, 8) + "-" + uuid.substr(8, 4) + "-4" + uuid.substr(12, 3) + "-" + parseInt(Math.random() * 4 + 8).toString(16) + uuid.substr(15, 3) + "-" + uuid.substr(18, 12);
var url = "https://www.google-analytics.com/collect?v=1&t=event&tid=UA-74705456-1&cid=" + uuid + "&ds=adwordsscript&an=qstracker&av="
+ version
+ "&ec=AdWords%20Scripts&ea=Script%20Execution&el=QS%20Tracker%20v" + version;
UrlFetchApp.fetch(url);
}
Controllare i propri concorrenti
Nell’interfaccia di Google AdWords è già possibile leggere il report informativo sulle aste, che permette di capire quali sono i principali concorrenti per quanto riguarda la ricerca a pagamento.
Con questo script è possibile salvarsi in uno spreadsheet di Google tutte queste informazioni per poi poter vedere l’andamento nel tempo della concorrenza.
/**
* Brainlabs Auctions Insights Report Tool
*
* This script will take data from an Auctions Insights report and use
* it to create a sheet for each column heading, with the data for the
* your domain and the top 5 competitors over time.
*
* Version: 2.0
* Google Apps Script maintained on brainlabsdigital.com
**/
var dateFormat = 'yyyy-MM-dd';
// The date format to be used in the tables and charts
// Can be replaced with 'dd/MM/yyyy' or 'MM/dd/yyyy' if preferred
var currencySymbol = "£";
// The symbol used for formatting cells as currency
// Can be replaced with "$", "€", etc
var costColumnName = "Cost";
var clicksColumnName = "Clicks";
// The name of the columns for cost and clicks (used if CPC data is provided)
// Can be replaced if your AdWords report is in another language
// Note this is case sensitive!
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Information about the different columns of the Auctions Insights report
// (Will only be included if column names are given in English)
var subtitle = {};
subtitle["Impr. share"] = "How often a participant received an impression, as a proportion of the auctions in which you were also competing.";
subtitle["Avg. position"] = "The average position on the search results page for the participant’s ads when they received an impression.";
subtitle["Overlap rate"] = "How often another participant's ad received an impression when your ad also received an impression.";
subtitle["Position above rate"] = "When you and another participant received an impression in the same auctions, % when participant’s ad was shown in a higher position.";
subtitle["Top of page rate"] = "When a participant’s ads received impressions, how often it appeared at the top of the page above the search results.";
subtitle["Outranking share"] = "How often your ad ranked higher in the auction than another participant's ad, or your ad showed when theirs did not.";
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// The function to create new sheets and charts
function onEdit() {
// Finds Sheet1 - it is assumed this is the first sheet with a name ending in '1'
// If no sheet names end with '1' then the first sheet is used
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
for (var i=0; i<sheets.length; i++) {
if (sheets[i].getSheetName().substr(sheets[i].getSheetName().length-1) == "1") {
var sheet1 = sheets[i];
break;
}
}
var sheet1Name = sheet1.getSheetName();
// Finds Sheet2 - it is assumed this is the first sheet with a name ending in '2'
for (var i=0; i<sheets.length; i++) {
if (sheets[i].getSheetName().substr(sheets[i].getSheetName().length-1) == "2") {
var sheet2 = sheets[i];
var sheet2Name = sheet2.getSheetName();
break;
}
}
var columnHeaders = sheet1.getRange(2, 3, 1, 10).getValues()[0];
// Loop through all the columns
for (var g=0; g<columnHeaders.length; g++) { // If the header is blank, we have reached the end of the headers, so the loop ends if (columnHeaders[g] == "") { break; } // We try to go to the sheet for the current column var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(columnHeaders[g]); if (sheet == null) { // If the sheet doesn't exist, create it sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(columnHeaders[g]); } // Because there may not be a Sheet2 yet, or it might not have the CPC information yet, we check // whether we should include CPCs or not, and only use a REARRANGE function with Sheet2 if it's there. // REARRANGE has three inputs: the column name, the Auctions Insights report and the CPC performance // If there is no CPC performance then the third input is set as a single cell // The REARRANGE function (see below) will check for this if (typeof sheet2 == 'undefined' || sheet2.getRange("A1").getValue().length == 0) { sheet.getRange(1,1).setValue('=REARRANGE("' + columnHeaders[g] + '";' + sheet1Name +'!A:H;' + sheet1Name +'!A1)'); } else { if ((sheet.getRange(1,1).getFormula() == '=REARRANGE("' + columnHeaders[g] + '";' + sheet1Name +'!A:H;' + sheet1Name +'!A1)' || sheet.getRange(1,1).getFormula() == '=REARRANGE("' + columnHeaders[g] + '",' + sheet1Name +'!A:H,' + sheet1Name +'!A1)') && sheet.getCharts().length > 0) {
// If there is an old chart that didn't include the CPC, remove it so a new one (with the CPC) will be created
sheet.removeChart(sheet.getCharts()[0]);
}
sheet.getRange(1,1).setValue('=REARRANGE("' + columnHeaders[g] + '";' + sheet1Name +'!A:H;' + sheet2Name +'!A:Z)');
}
var numRows = 0;
var numColumns = 0;
// These are set to 2 and 1 because the data from REARRANGE starts in cell A2
var startRow = 2;
var startColumn = 1;
// Look at cells to the right of the starting position until a blank one is found,
// to find the number of columns that contain data
for (var i=0; i<2000; i++) {
if (sheet.getRange(startRow+i,startColumn).isBlank()) {
numRows = i;
break;
}
}
// Find the number of rows that contain data
for (var i=0; i<30; i++) { if (sheet.getRange(startRow,startColumn+i).isBlank()) { numColumns = i; break; } } if (sheet.getCharts().length > 0) {
var oldChartLastRow = sheet.getCharts()[0].getRanges()[0].getLastRow();
if (oldChartLastRow != startRow+numRows-1) {
// If the last row of the chart's range isn't startRow+numRows-1, then there is new data
// The old chart is removed, so a new one (that covers all the data) will be created
sheet.removeChart(sheet.getCharts()[0]);
}
}
// If there aren't any charts then we need to add formatting and charts
if (sheet.getCharts().length < 1) { // Format the first column (the dates) as dates sheet.getRange(startRow+1, startColumn, numRows, 1).setNumberFormat(dateFormat); // Format the second column (the CPCs) as currency sheet.getRange(startRow+1, startColumn+1, numRows-1, 1).setNumberFormat(currencySymbol + "0.00"); if (sheet.getRange(startRow+1, startColumn+2, 1,1).getValue() > 1) {
// If the data is over 1, it can't be a percentage, so it must be the average postion.
// So it is formatted as a number
sheet.getRange(startRow+1, startColumn+2, numRows-1, numColumns-1).setNumberFormat("0.0");
} else {
// Otherwise format it as a percentage
sheet.getRange(startRow+1, startColumn+2, numRows-1, numColumns-1).setNumberFormat("0.00%");
}
// Get the width in pixels for the chart, based on the chart being a few columns wider than the data
var width = 0;
for (var i= startColumn; i< startColumn+numColumns+2; i++) {
width += sheet.getColumnWidth(i);
}
// Creates the chart
var chartTitle = columnHeaders[g];
if (typeof subtitle[columnHeaders[g]] != 'undefined') {
chartTitle = chartTitle + " - " + subtitle[columnHeaders[g]];
}
var chartBuilder = sheet.newChart()
.setChartType(Charts.ChartType.LINE)
.addRange(sheet.getRange(startRow, startColumn, numRows, 1))
.addRange(sheet.getRange(startRow, startColumn+1, numRows, 1))
.addRange(sheet.getRange(startRow, startColumn+2, numRows, 1))
.addRange(sheet.getRange(startRow, startColumn+3, numRows, numColumns-2))
.setOption('chartArea', {left:'10%',top:'15%',width:'80%',height:'70%'})
.setPosition(startRow + numRows + 1, startColumn, 0, 0)
.setOption('width', width)
.setOption('height', 500)
.setOption('title', chartTitle)
.setOption('legend', {position: 'top'})
.setOption('vAxes', {
// Adds titles to each axis.
0: {title: 'Percentage'} ,
1: {title: 'CPC'}
});
// Find out if there are any CPCs
var cpcValues = sheet.getRange(startRow+1, startColumn+1, numRows-1, 1).getValues();
var thereAreCPCs = false;
for (var i=0; i<numRows-1; i++) { if (cpcValues[i][0] > 0) {
thereAreCPCs = true;
break;
}
}
// Find out if there is data for 'You'
var thereIsYou = sheet.getRange(startRow, startColumn+2).getValue() == "You";
// If the CPCs are all 0, then remove it from the chart (as it isn't useful). Otherwise it
// is shown as a grey dashed line.
// If one of the domains is 'You', that will be the second series of data (after the CPC) -
// this is formatted differently to the competitors to stick out.
if (thereIsYou && thereAreCPCs) {
chartBuilder.setOption('series', {
// The CPC
0: {targetAxisIndex: 1,
lineDashStyle: [10,5],
color: '#999999'},
// 'You'
1: {targetAxisIndex: 0,
color: '#000000',
lineWidth: 4},
// Competitors
2: {targetAxisIndex: 0}
});
} else if (thereIsYou && !thereAreCPCs) {
chartBuilder.removeRange(sheet.getRange(startRow, startColumn+1, numRows, 1));
chartBuilder.setOption('series', {
// 'You'
0: {targetAxisIndex: 0,
color: '#000000',
lineWidth: 4},
// Competitors
1: {targetAxisIndex: 0},
2: {targetAxisIndex: 0}
});
} else if (!thereIsYou && thereAreCPCs) {
chartBuilder.setOption('series', {
// The CPC
0: {targetAxisIndex: 1,
lineDashStyle: [10,5],
color: '#999999'},
// Competitors
1: {targetAxisIndex: 0},
2: {targetAxisIndex: 0}
});
} else if (!thereIsYou && !thereAreCPCs) {
chartBuilder.removeRange(sheet.getRange(startRow, startColumn+1, numRows, 1));
chartBuilder.setOption('series', {
// Competitors
0: {targetAxisIndex: 0},
1: {targetAxisIndex: 0},
2: {targetAxisIndex: 0}
});
}
// Creates the specified chart and inserts it into the sheet
var chart = chartBuilder.build();
sheet.insertChart(chart);
}
}
} //end function onEdit
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// The function to create new sheets and charts
function REARRANGE(columnHeader,auditInsights,performance) {
// Dates are stored as bigendian date strings, then converted back to dates at the end
var bigendianDate = 'yyyy-MM-dd';
// The timezone is used to convert them back
var timezone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
var domains = {};
var dates = [];
var domainNames = [];
var g = auditInsights[1].indexOf(columnHeader);
// The index of the required stat
// First we record the stats for each domain, by month
// and record each domain's highest impression share
for (var i = 2; i<auditInsights.length; i++) {
// auditInsights is a multi-dimensional array containing the values of the auditInsights cells.
// So auditInsights[i] is a row on the Auction Insights report
// The loop starts at 2 as auditInsights[0] is the title and auditInsights[1] is the headers.
var date = auditInsights[i][0];
if (!date) {
// If the date field is blank, we have reached the end of the data
// so we end the for loop
break;
}
if (typeof date != "string") {
// The date is converted into a string
date = Utilities.formatDate(date, timezone, bigendianDate);
}
if (Utilities.formatDate(stringToDate(date), "UTC", bigendianDate) == "1970-01-01") {
//This means it isn't a proper date, so the row is skipped
continue;
}
if (dates.indexOf(date) < 0) {
// If the current row's date isn't in the dates array, it's added
dates.push(date);
}
var imprShare = auditInsights[i][2]; //the impression share
if (imprShare == "< 10%") {
// If the impression share is "< 10%" (a string) it is changed to 5% (a float)
// so it can be displayed in the graph.
imprShare = 0.05;
}
var domainName = auditInsights[i][1];
if (domainNames.indexOf(domainName) < 0) { // If the current row's domain name isn't in the domainNames array, it is added, // and an entry for it is entered into the domains object. domainNames.push(domainName); domains[domainName] = []; domains[domainName]["Max Impr Share"] = imprShare; } // If g is 2 then the stat is impression share, so that is recorded if (g == 2) { domains[domainName][date] = imprShare; } else { // Otherwise the gth value of the row is recorded domains[domainName][date] = auditInsights[i][g]; } if (imprShare > domains[domainName]["Max Impr Share"]) {
// If the current imprShare is bigger than the last recorded max impr share,
// the current one is recorded as being the max
domains[domainName]["Max Impr Share"] = imprShare;
}
} // end of for loop
// Next we get the costs and clicks from Sheet2 (if it exists), to get the CPC
// If Sheet2 exists, performance will be a multidimensional array. If it doesn't it will only contain 1 cell.
var hasCPC = (performance.length > 1);
var costTotals = [];
var clicksTotals = [];
if (hasCPC) {
// Dates should be in the first column, but the position of the cost and clicks columns varies depending on AdWords settings
// So we set variables to record the required column numbers
var costIndex = performance[1].indexOf(costColumnName);
var clicksIndex = performance[1].indexOf(clicksColumnName);
for (var i = 2; i<performance.length; i++) {
var date = performance[i][0];
if (!date) {
// If there's no date we've reached the end of the data
break;
}
if (typeof date != "string") {
// If the date isn't a string, convert it into one
date = Utilities.formatDate(date, timezone, bigendianDate);
}
if (costTotals[date] == undefined) {
costTotals[date] = performance[i][costIndex];
clicksTotals[date] = performance[i][clicksIndex];
} else {
costTotals[date] += performance[i][costIndex];
clicksTotals[date] += performance[i][clicksIndex];
}
} // end of for loop
}
dates.sort();
// Sorts the dates alphabetically - as they're in bigendian format, this means they are sorted oldest to newest
domainNames.sort(compareDomainNames);
// Sorts the domain names by their highest impression share, using the function below
function compareDomainNames(a,b) {
if (domains[a]["Max Impr Share"] != domains[b]["Max Impr Share"]) {
// If the max impression shares are different, the domain with the highest is put first
return domains[b]["Max Impr Share"] - domains[a]["Max Impr Share"];
} else {
// If both domains have the same max impression share, the one with data for the most dates is put first
return Object.keys(domains[b]).length - Object.keys(domains[a]).length;
}
}
var includeYou = false;
for (var i=0; i<dates.length; i++) {
if (domains["You"][dates[i]] != "--") {
includeYou = true;
break;
}
}
domainNames.splice(domainNames.indexOf("You"),1);
// Removes "You" from the array
if (includeYou) {
// If this graph is supposed to include 'You', then it's added to the start of the array
domainNames.unshift("You");
}
if (g < 0) {
// Error checking - if the columnHeader wasn't a recognised title, we output an error message
return [[columnHeader + " not recognised."]];
}
// 'output' is a multi-dimensional array that will become cells in the spreadsheet
output = [];
// The first row of the output is the column name
output[0] = [columnHeader];
// The second row of the output is the headings
output[1] = ["Date","Avg. CPC"];
for (var d = 0; d<domainNames.length && d<6; d++) {
output[1].push(domainNames[d]);
}
// We loop though the dates to make their lines of output
// (the date, the CPC, then each domain's metric)
for (var i = 0; i<dates.length; i++) {
output[i+2] = [stringToDate(dates[i])];
// Calculate the average CPC
if (costTotals[dates[i]] == undefined || clicksTotals[dates[i]] == undefined || clicksTotals[dates[i]] == 0) {
output[i+2].push(0);
} else {
output[i+2].push(costTotals[dates[i]]/clicksTotals[dates[i]]);
}
for (var d = 0; d<domainNames.length && d<6; d++) {
if (domains[domainNames[d]][dates[i]] === undefined) {
output[i+2].push(0);
} else {
output[i+2].push(domains[domainNames[d]][dates[i]]);
}
}
}
return output;
}// end function REARRANGE
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// This function is used by REARRANGE to convert date-strings back into dates
function stringToDate(string) {
var dateBits = string.split("-");
var date = new Date();
date.setFullYear(dateBits[0]);
date.setMonth(parseInt(dateBits[1],10)-1);
date.setDate(parseInt(dateBits[2],10));
return date;
}
