Отчет эффективности объявлений

1. Создать пустую таблицу Google Sheet

2. Скопировать код, приведенный ниже

3. Google Sheets URL - укажите ссылку на созданную таблицу (строка 16)

4. Date range - установите диапазон дат, который вы хотите проанализировать в формате год-месяц-день. (строки 18 и 19)

5. Define campaigns to include or exclude - определите, какие кампании нужно включить в отчет, либо исключить. По умолчанию включены все кампании. (строки 25 и 28)

6. Select your currency - установите валюту, используя символ, например "₴" или "$". (строка 30)

/**********************
 *
 * 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;
}