[Adwords Script] Ad Template Report Script

It’s no secret that ad testing is one of the key pillars to success within Adwords. The advertisers that are winning in 2018 are not the ones that are the most creative, it is the ones that micro split test every aspect of their advert and continually improve.

To help advertisers create better ads we have created this script that analyses adverts and aggregates performance for headlines, descriptions, and paths, showing you which parts of your ads perform best. It then creates a report like the one below which shows the best performing headline 1 in our account for the last 7 days.

It also creates several tabs at the bottom of the sheet, each one containing the report for different elements of the ad copy. So you can analyse headline 1, headline 2, the description, path 1 and path 2 separately.

Within the script, there are several variables that you can set that allow you to program the script to do what you want to do, they are as follows:

  • Google Sheets URL. To use this script you need to create a new blank Google Sheet and then enter the URL of that sheet into the Script on line 20.
  • Date range. You need to set the date range that you want to analyze in the format year-month-day. This can be done on lines 23 aand 24.
  • Do you want to include paused ads? You can select whether or not to include data from paused ads within the script. You can either set this to True if you want to or False if not on line 27.
  • Include Ads with no conversions? On large accounts with tens of thousands of ads it is suggested that you change this to False otherwise leave this as True on line 30.
  • Define campaigns to include or exclude. In lines 32 to 37 you have the option to define which campaigns you want to include or exclude. By default, all campaigns are included. To include only certain campaigns define these within the brackets on line 33. To exclude certain campaigns define these within the brackets on line 37.
  • Select your currency. Select the currency on line 40 using the symbol, E.g. “£” or “$”.

The script can be found below, you can add it to your account within the bulk operations section of your account.

/**********************
 *
 * AdWords script to pull ad data into a Google Sheet.
 *
 * Parameters include:
 * - Sheet URL
 * - Start and end dates
 * - Include/exclude paused ads
 * - Include/exclude ads with no conversions
 * - Lists of campaigns to include/exclude (or shows all)
 * - V1. Clicteq Limited
 */


function main() {

  ///////////////////////// BEGIN USER EDIT AREA /////////////////////////////

  //Enter the url of the sheet where you want the data to be added
  var sheetURL = "Enter Google Sheets URL here";

  //Enter start and end dates in this format:  2018-04-10
  var startDate = "2018-04-01";
  var endDate = "2018-04-14"

  //Include paused ads?
  var includePaused = false;

  //Include ads with no conversions?
  var includeNoConversions = true;

  //List of campaign names to include (leave blank for all)
  var includeCampaigns = [];// e.g. ["My campaign 1", "My campaign 2"];

  //List of campaign names to exclude (leave blank for all,
  //and do not exclude campaigns you have explicitly included)
  var excludeCampaigns = [];// e.g. ["My campaign 1", "My campaign 2"];

  //Enter here the currency used for the cost
  var currency = "£";

  //List of outputs to display by
  var pivots = ["HeadlinePart1","HeadlinePart2","Description","Path1","Path2"];

  ///////////////////////// END USER EDIT AREA ///////////////////////////////


  generateReports(sheetURL,pivots,startDate,endDate,includePaused,includeCampaigns,excludeCampaigns,includeNoConversions,currency)


}

//Put it all together and generate reports
function generateReports(sheetURL,pivots,startDate,endDate,includePaused,includeCampaigns,excludeCampaigns,includeNoConversions,currency){
  //Open the sheet provided by the user
  var ss = SpreadsheetApp.openByUrl(sheetURL);

  var start = convertPrettyDateToAQL(startDate);
  var end = convertPrettyDateToAQL(endDate);

  //Continue only if valid dates
  if (start != undefined && end != undefined){
    //Pivot by each of the five categories
    for (var i=0;i<pivots.length;i++){
      var pivot = pivots[i];
      Logger.log("Retrieving Adwords data for " + pivot + "...");
      addReportToSheetWithName(ss,pivot,start,end,includePaused,includeCampaigns,excludeCampaigns,includeNoConversions,currency);
    }
  }
  else{
    Logger.log("Dates entered not in correct format! Please make sure you enter them in the format specified: 2018-04-10 (YYYY-MM-DD)");
  }
}

//Retrieve AdWords reporting data
function generateAdWordsReport(pivot,start,end,paused,include,exclude,includeNoConversions){
  var includes = "";

  //If paused ads need to be shown, add it to the query, otherwise keep it as it is
  var includePausedText = (paused ? "" : " Status = ENABLED ");
  includes = addToQuery(includes,includePausedText);

  //If include/exclude campaigns contain names, add them to the query, otherwise keep it as it is
  var includeCampaignsText = include.length>0 ? (" CampaignName IN " + formatTextForQuerySet(include)) : "";
  includes = addToQuery(includes,includeCampaignsText);
  var excludeCampaignsText = exclude.length>0 ? (" CampaignName NOT_IN " + formatTextForQuerySet(exclude)) : "";
  includes = addToQuery(includes,excludeCampaignsText);

  if (!includeNoConversions){
  	includes = addToQuery(includes," Conversions > 0 ");
  }

  var query =   "SELECT " + pivot + ", Clicks, Impressions, Cost, Conversions, Ctr, ConversionRate, CostPerConversion " +
    			"FROM AD_PERFORMANCE_REPORT " +
    			includes +
    			"DURING " + start + "," + end;

  var report = AdWordsApp.report(query);

  return report;
}

//Generate report and fill sheet
function addReportToSheetWithName(ss,pivot,start,end,paused,include,exclude,conversions,currency){
  //Get or create the sheet with the pivot specified
  var sheet = ss.getSheetByName(pivot);
  if (sheet == undefined){
      sheet = ss.insertSheet(pivot);
  }

  //Create the report
  var report = generateAdWordsReport(pivot,start,end,paused,include,exclude,conversions);

  //Add the data to the sheet. If the data exceeds the max sheet size,
  //log it so the user knows
  try{
    Logger.log("Pivotting the data for " + pivot);
    var data = getRowsAndAggregateToUnique(report,pivot);
    var asArray = getObjectAsArray(data,pivot);
    Logger.log("Writing data to the sheet\n");
    if (asArray.length>0){
      var range = sheet.getRange(1,1,asArray.length,asArray[0].length);
      sheet.clear();
      range.setValues(asArray);
      sheet.setFrozenRows(1);

      // Pct format
      var column = sheet.getRange("F2:G");
      column.setNumberFormat("#.##%")
      // Currency format
      column = sheet.getRange("H2:H");
      column.setNumberFormat(currency + "#,##0.00");
    }
  }
  catch(e){
    Logger.log(e);
  }
}

//Format array as AQL set
function formatTextForQuerySet(array){
  var awq = "[";
  Logger.log(array);
  for (var i=0;i<array.length;i++){
    var item = "'" + array[i] + "'";
    var isLast = (i==array.length-1);
    awq += item + (isLast?"":",");
    Logger.log(awq);
  }
  awq += "] ";
  return awq;
}

//Add to query filter
function addToQuery(queryBuilder,newQueryItem){
  //no new queries to add
  if (newQueryItem == ""){
    return queryBuilder;
  }

  //add 'WHERE' or 'AND' depending on blank statement or existing statement
  if (queryBuilder == ""){
    queryBuilder +=  " WHERE " + newQueryItem;
  }
  else{
    queryBuilder += " AND " + newQueryItem;
  }
  return queryBuilder;
}

//Retrieve rows for further processing and aggregation of data
function getRowsAndAggregateToUnique(report,pivot){
  var rows = report.rows();
  var data = {};
  while (rows.hasNext()){
    var row = rows.next();
    var dataRow = [];
    var keys = Object.keys(row);

    if (data[row[pivot]] == undefined){
      data[row[pivot]] = {};
    }
    for (var i=0;i<keys.length;i++){
      if (keys[i]!="Ctr" && keys[i]!="ConversionRate" && keys[i]!="CostPerConversion" && keys[i]!=pivot){
        if (data[row[pivot]][keys[i]] == undefined){
          data[row[pivot]][keys[i]] = 0;
        }
        var n = readNumber(row[keys[i]]);
        var value = isNaN(n) ? 0 : n;

        data[row[pivot]][keys[i]] += value*1;
      }
    }


  }

  //Now based on clicks, implessions, conversions and cost, calculare Ctr, ConversionRate and CostPerConversion
  var pivotKeys = Object.keys(data);
  for (var i=0;i<pivotKeys.length;i++){
    var pivotKey = pivotKeys[i];
    var value = ( isNaN(data[pivotKey]["Clicks"])
                 || (data[pivotKey]["Impressions"] == 0)
                 || isNaN(data[pivotKey]["Impressions"])
                 || isNaN(data[pivotKey]["Clicks"] / data[pivotKey]["Impressions"])
                )? 0 : (data[pivotKey]["Clicks"] / data[pivotKey]["Impressions"]);
    data[pivotKey]["Ctr"] = value;
    value =     ( isNaN(data[pivotKey]["Conversions"])
                 || (data[pivotKey]["Clicks"] == 0)
                 || isNaN(data[pivotKey]["Clicks"])
                 || isNaN(data[pivotKey]["Conversions"] / data[pivotKey]["Clicks"])
                )? 0 : (data[pivotKey]["Conversions"] / data[pivotKey]["Clicks"]);
    data[pivotKey]["ConversionRate"] =  value;
    value =     ( isNaN(data[pivotKey]["Cost"])
                 || (data[pivotKey]["Conversions"] == 0)
                 || isNaN(data[pivotKey]["Conversions"])
                 || isNaN(data[pivotKey]["Cost"] / data[pivotKey]["Conversions"])
                )? 0 : (data[pivotKey]["Cost"]/ data[pivotKey]["Conversions"]);
    data[pivotKey]["CostPerConversion"] = value;
  }

  return data;
}

//Convert the object to an array so it can be written to sheets
function getObjectAsArray(obj,pivot){
  var keys = Object.keys(obj);

  //first add the headers
  var rows = [[pivot,"Clicks","Impressions","Cost","Conversions","Ctr","ConversionRate","CostPerConversion"]];

  //now add the data
  for (var i=0;i<keys.length;i++){
    var row = [keys[i] , obj[keys[i]]["Clicks"] , obj[keys[i]]["Impressions"] , obj[keys[i]]["Cost"]
               , obj[keys[i]]["Conversions"] , obj[keys[i]]["Ctr"] , obj[keys[i]]["ConversionRate"]
               ,obj[keys[i]]["CostPerConversion"]];
    rows.push(row);

  }

  return rows;
}

//Fix the raw format returned by the report
function readNumber(textNumber){
  if (typeof textNumber == 'string'){
    textNumber = textNumber.replace(',', '');
    return parseFloat(textNumber);
  }
  return textNumber;
}

//Convert user input date into AQL format
function convertPrettyDateToAQL(date){
  var split = date.toString().split("-");
  if (split.length == 3){
    var year = split[0];
    var month = split[1];
    var day = split[2];
    var checks = (year.length == 4) && (month <12) && (day <32);
    if (checks){
      var monthString = (month.toString().length == 1)?("0" + month.toString()):month.toString();
      var dayString = (day.toString().length == 1)?("0" + day.toString()):day.toString();
      var dateOut = year.toString() + monthString + dayString;
      return dateOut;
    }
  }
  //fallback
  return undefined;
}

 

wesley parker
About wesley parker

Wesley is Founder and CEO at Clicteq. He currently manages a £6 Mil Adwords portfolio across a range of different sectors. He regulally features in leading search publications such as Search Engine Journal, Econsultancy and Certified Knowledge. You can follow him on Twitter or connect with him on Linkedin

Leave a Reply

Your email address will not be published. Required fields are marked *