Impressions-weighed Quality Score report

weighed_qs_report.js

/*
This script produces Impressions-weighed Quality Score report in G-sheet for Top X Ad Groups,
paired with key performance metrics
Report is based on Historical Quality Score records
Script uses three custom user defined slots for Quality Score: Low, Mid and High
*/


// Options section:
// Default quality score (if unknown)
var DEFAULT_QS = 6

// QS thresholds:
// under LOW QS is LOW (excluding)
var LOW_QS = 6
// over HI_SQ is HIGH (excluding)
var HI_QS = 8

// Top X spending groups to report
var TOP_X = 12

//Lookback period, days
var LOOKBACK_DAYS = 7 * 8

// Sheet URL. Set null to create new report each time,
// Set to sheet URL if you want the report to be updated
var SHEET_URL = null

// Exclude campaigns, like ['campaign name 1', 'campaign name 2'], set null to ignore
var EXCLUDE_CAMP_NAMES = null

// Include only campaigns, like ['campaign name 1', 'campaign name 2'], set null to ignore
var ONLY_CAMP_NAMES = null


// Include only enabled AdGroups in the reporting
var ENABLED_ONLY=false


var DEBUG = false


function main() {
  
  var plainParams = "CampaignId, CampaignName, AdGroupId, AdGroupName, Criteria, Id".split(/\s*\,\s*/)
  var plainMetrics = "Impressions, Clicks, Conversions".split(/\s*\,\s*/)

  var idKey = function (row) {
    return row['AdGroupId'] + '::' + row['Id']
  }
  
  var getSlot = function (obj) {
    if (obj['WeighedQS'] < LOW_QS ) return 'Low QS '
    if (obj['WeighedQS'] > HI_QS ) return 'High QS '
    return 'Mid QS '
  }
  
  var SLOTS = ['Low QS ', 'Mid QS ', 'High QS ']
  var SLOT_LABELS = {
    'Low QS ': "wQS < " + LOW_QS,
    'Mid QS ': LOW_QS + ">= wQS < " + HI_QS,
    'High QS ' : HI_QS + ">= wQS"
  }
  
  var calculatedMetrics = [
    {
      'name': 'HistoricalQualityScore',
      'caller': function (row) {
        return (row['HistoricalQualityScore'] != '--') ? row['HistoricalQualityScore'] : DEFAULT_QS
      }
    },
    {
      'name': 'ScoredImpressions',
      'caller': function (row) {
        var hqs = row['HistoricalQualityScore'] != '--' ? row['HistoricalQualityScore'] : DEFAULT_QS
        return hqs * row['Impressions']
      }
    },
    {
      'name': 'Cost',
      'caller': function (row) {
        return row['Cost'] / 1000000
      }
    }
  ]
  
  var cumulativeMetrics = [
    {
      'name': 'WeighedQS',
      'caller': function (prefix, item) {
        if (!prefix) prefix = ''
        return (item[prefix + 'Impressions'] > 0 ) ? item[prefix + 'ScoredImpressions'] / item[ prefix + 'Impressions'] : 0
      }
    },
    {
      'name': 'CostPerConversion',
      'caller': function (prefix, item) {
        if (!prefix) prefix = ''
        return (item[prefix + 'Conversions'] > 0 ) ? item[prefix + 'Cost'] / item[prefix + 'Conversions'] : 0
      }
    },
    {
      'name': 'CTR',
      'caller': function (prefix, item) {
        if (!prefix) prefix = ''
        return (item[prefix + 'Impressions'] > 0 ) ? item[prefix + 'Clicks'] / item[prefix + 'Impressions'] : 0
      }
    },
    {
      'name': 'CPC',
      'caller': function (prefix, item) {
        if (!prefix) prefix = ''
        return (item[prefix + 'Clicks'] > 0 ) ? item[prefix + 'Cost'] / item[prefix + 'Clicks'] : 0
      }
    }
  ]

  var selectMetrics = ',HistoricalQualityScore,Cost'
  
  // Basic Report Query
  var query = 'SELECT Date,' +  plainParams.join(',') + ',' + plainMetrics.join(',') + selectMetrics + ' ' +
      'FROM KEYWORDS_PERFORMANCE_REPORT ' +
      'WHERE CampaignId IN [%list] '.replace("%list", getSearchCampaigns()) +
      ( ENABLED_ONLY ? 'AND CampaignStatus = ENABLED AND AdGroupStatus = ENABLED ' : '' ) +
      'DURING ' + backNDays(LOOKBACK_DAYS) + ',' + backNDays(1)
  debug(query)
    
  var keywordReport = AdWordsApp.report(query, {returnMoneyInMicros: true});
  var rows = keywordReport.rows()
  var reportedKeywords = {}
  while (rows.hasNext()) {
    var row = rows.next()
    var id = idKey(row)
    if (!reportedKeywords[id])  {
      reportedKeywords[id] = plainParams.reduce(function (item, param) { item[param] = row[param]; return item }, {}) 
      plainMetrics.map(function (metric) {
        reportedKeywords[id][metric] = 0.0
      })
      calculatedMetrics.map(function (calculation) {
        reportedKeywords[id][calculation.name] = 0.0
      })
    }
    plainMetrics.map(function (metric) {
      reportedKeywords[id][metric] += +row[metric]
    })
    calculatedMetrics.map(function (calculation) {
      reportedKeywords[id][calculation.name] += +calculation.caller(row)
    })
    cumulativeMetrics.map(function (accumulation) {
      reportedKeywords[id][accumulation.name] = accumulation.caller(false, reportedKeywords[id])
    })
  }
  
  Logger.log('Total %s keywords records preocessed\nStarted AdGroups Processing', Object.keys(reportedKeywords).length)
  debug('Sample keywords %s', Object.keys(reportedKeywords).slice(0,5).map(function (id) {return _j(reportedKeywords[id])}).join('\n'))
  
  var groupParams = "CampaignId, CampaignName, AdGroupId, AdGroupName".split(/\s*\,\s*/)
  // Helper AdGroup Query
  var query = 'SELECT Cost, AdGroupId ' +
      'FROM ADGROUP_PERFORMANCE_REPORT ' +
      'WHERE CampaignId IN [%list] '.replace("%list", getSearchCampaigns()) +
      ( ENABLED_ONLY ? 'AND CampaignStatus = ENABLED AND AdGroupStatus = ENABLED ' : '' ) +
      'DURING ' + backNDays(LOOKBACK_DAYS) + ',' + backNDays(1)
  var groupReport = AdsApp.report(query, {returnMoneyInMicros: true})
  var rows = groupReport.rows(), costs = []
  while (rows.hasNext()) {
    var row = rows.next()
    costs.push(row)
  }
  costs.sort(function (groupA, groupB) {return groupB['Cost'] - groupA['Cost']} )
  var topGroupsIDs = costs.slice(0, TOP_X).map(function (row) {return row['AdGroupId']})

  var reportedAdGroups = {}
  var heads = ['Total', 'Others']
  heads.map(function (groupId) {
    reportedAdGroups[groupId] = groupParams.reduce(function (item, param) { item[param] = '--'; return item }, {})
    reportedAdGroups[groupId]['AdGroupName'] = groupId
    plainMetrics.map(function (metric) {
      reportedAdGroups[groupId][metric] = 0.0
      SLOTS.map(function (slot) {
        reportedAdGroups[groupId][slot + metric] = 0.0
      })
    })
    reportedAdGroups[groupId]['ScoredImpressions'] = 0.0
    reportedAdGroups[groupId]['Cost'] = 0.0
    SLOTS.map(function (slot) {
      reportedAdGroups[groupId][slot + 'Cost'] = 0.0
      reportedAdGroups[groupId][slot + 'ScoredImpressions' ] = 0.0
    })
  })
  for (var ind in reportedKeywords) {
    var keyword = reportedKeywords[ind]
    var groupId = keyword['AdGroupId']
    
    // Seeding Group Object
    if (!reportedAdGroups[groupId]) {
      reportedAdGroups[groupId] = groupParams.reduce(function (item, param) { item[param] = reportedKeywords[ind][param]; return item }, {})
      plainMetrics.map(function (metric) {
        reportedAdGroups[groupId][metric] = 0.0
        SLOTS.map(function (slot) {
          reportedAdGroups[groupId][slot + metric] = 0.0
        })
      })
      reportedAdGroups[groupId]['ScoredImpressions'] = 0.0
      reportedAdGroups[groupId]['Cost'] = 0.0
      SLOTS.map(function (slot) {
        reportedAdGroups[groupId][slot + 'Cost'] = 0.0
        reportedAdGroups[groupId][slot + 'ScoredImpressions' ] = 0.0
      })
    }
    
    // Updating Group Data
    reportedAdGroups[groupId]['ScoredImpressions'] += keyword['ScoredImpressions']
    reportedAdGroups[groupId]['Cost'] += keyword['Cost']
    var keywordSlot = getSlot(keyword)
    plainMetrics.map(function (metric) {
      reportedAdGroups[groupId][metric] += keyword[metric]
      reportedAdGroups[groupId][keywordSlot + metric] += keyword[metric]
    })
    reportedAdGroups[groupId][keywordSlot + 'ScoredImpressions' ] += keyword['ScoredImpressions']
    reportedAdGroups[groupId][keywordSlot + 'Cost'] += keyword['Cost']

    //Calculate Others
    if (topGroupsIDs.indexOf(groupId) == -1) {
      reportedAdGroups['Others']['ScoredImpressions'] += keyword['ScoredImpressions']
      reportedAdGroups['Others']['Cost'] += keyword['Cost']
      plainMetrics.map(function (metric) {
        reportedAdGroups['Others'][metric] += keyword[metric]
        reportedAdGroups['Others'][keywordSlot + metric] += keyword[metric]
      })
      reportedAdGroups['Others'][keywordSlot + 'ScoredImpressions' ] += keyword['ScoredImpressions']
      reportedAdGroups['Others'][keywordSlot + 'Cost'] += keyword['Cost']

    }
    
    //Calculate Total
    reportedAdGroups['Total']['ScoredImpressions'] += keyword['ScoredImpressions']
    reportedAdGroups['Total']['Cost'] += keyword['Cost']
    plainMetrics.map(function (metric) {
      reportedAdGroups['Total'][metric] += keyword[metric]
      reportedAdGroups['Total'][keywordSlot + metric] += keyword[metric]

    })
    reportedAdGroups['Total'][keywordSlot + 'ScoredImpressions' ] += keyword['ScoredImpressions']
    reportedAdGroups['Total'][keywordSlot + 'Cost'] += keyword['Cost']
  }
  
  Logger.log('Total %s AdGroups records processed', Object.keys(reportedAdGroups).length)
  
  // Making cumulative calculations:
  for (var groupId in reportedAdGroups) {
    cumulativeMetrics.map(function (accumulation) {
      reportedAdGroups[groupId][accumulation.name] = accumulation.caller(false, reportedAdGroups[groupId])
      SLOTS.map( function (keywordSlot) {
        reportedAdGroups[groupId][keywordSlot + accumulation.name] = accumulation.caller(keywordSlot, reportedAdGroups[groupId])
      })
    })
  }
  
  debug('Sample AdGroups %s', Object.keys(reportedAdGroups).slice(0,9).map(function (id) {return _j(reportedAdGroups[id])}).join('\n'))
  
  var allGroups = []
  for (var ind in reportedAdGroups) {
    if (ind != 'Total' && ind != 'Others') allGroups.push(reportedAdGroups[ind])
  }
  
  allGroups.sort(function (groupA, groupB) {return groupB['Cost'] - groupA['Cost']} )
  
  var slicedGroups = allGroups.slice(0, TOP_X)

  var sheetHeader = [
    "CampaignName",
    "AdGroupName",
    "WeighedQS",
    //   "High QS Clicks",
    //   "High QS Conversions",
    //   "Low QS Clicks",
    //   "Low QS Conversions",
    "Low QS Cost",
    "Low QS CostPerConversion",
    "Low QS CPC",
    //   "Low QS CTR",
    //   "Low QS Impressions",
    //   "Low QS ScoredImpressions",
    //   "Low QS WeighedQS",

    "Mid QS Cost",
    "Mid QS CostPerConversion",
    "Mid QS CPC",
    //   "Mid QS CTR",
    //   "Mid QS Impressions",
    //   "Mid QS ScoredImpressions",
    //   "Mid QS WeighedQS",
    "High QS Cost",
    "High QS CostPerConversion",
    "High QS CPC",
    //   "High QS CTR",
    //   "High QS Impressions",
    //   "High QS ScoredImpressions",
    //   "High QS WeighedQS",
    //   "Mid QS Clicks",
    //   "Mid QS Conversions",

    //  "Impressions",
    "Clicks",
    //  "Conversions",
    "Cost",
    "CPC",
    "CTR",
    "CostPerConversion",
  ]
  
  // Sheet Data Processing
  slicedGroups.push(reportedAdGroups['Others'], reportedAdGroups['Total'])
  var sheetRows = slicedGroups.map(function (group) {
    return sheetHeader.map(function (colName) {
      if (colName.match('CostPerConversion') && group[colName] == 0.0) return '--'
      return group[colName]
    })
  })
  sheetRows.unshift(sheetHeader)

  if (!SHEET_URL) Logger.log('... new sheet will be created')
  var ss = (SHEET_URL) ? SpreadsheetApp.openByUrl(SHEET_URL) : SpreadsheetApp.create(AdsApp.currentAccount().getName() + ' QS Report '+ backNDays(0))
  var sheet =  ss.getSheets()[0]
 sheet.clear().getRange(1, 1, sheetRows.length, sheetRows[0].length).setValues(sheetRows)
  
  
  
  // Column Formatting
  var conditionalRules = []
  sheetHeader.map(function (colName, index) {
    var targetRange = sheet.getRange(2, index + 1, sheetRows.length-1, 1)
    sheet.setColumnWidth(index+1, colName.length * 8 + 15);
    var targetRangeWide = sheet.getRange(1, index + 1, sheetRows.length, 1)

    if (colName.match(/Cost|QS|CPC/i)) {
      
      targetRange.setNumberFormat("#,##0.00")
    }
    if (colName.match(/CTR/i)) {
      targetRange.setNumberFormat("0.00%") 
    }
    if (colName.match(/Clicks|Impressions/i)) {
      targetRange.setNumberFormat("#,##0") 
    }
    if (colName.match(/WeighedQS/i)) { 
      var rule = SpreadsheetApp.newConditionalFormatRule()
      .setGradientMaxpointWithValue('#6AA84F',SpreadsheetApp.InterpolationType.NUMBER, 10 )
      .setGradientMidpointWithValue('#FFFFFF', SpreadsheetApp.InterpolationType.NUMBER, 5.5)
      .setGradientMinpointWithValue('#DD7E6B', SpreadsheetApp.InterpolationType.NUMBER, 1 )
      .setRanges([targetRange])
      .build()
      conditionalRules.push(rule)
    }
    if (colName.match(/CostPerConversion/i)) {
      var lowRule  = SpreadsheetApp.newConditionalFormatRule()
      .whenNumberBetween(0, reportedAdGroups['Total']['CostPerConversion'] * 0.5 )
      .setBackground("#6AA84F")
      .setRanges([targetRange])
      .build();
      var hiRule = SpreadsheetApp.newConditionalFormatRule()
      .whenNumberGreaterThan(reportedAdGroups['Total']['CostPerConversion'] * 2 )
      .setBackground("#e57c94")
      .setRanges([targetRange])
      .build();
      conditionalRules.push(lowRule, hiRule)
    }
    if (colName.match(/QS.+Cost$/i)) { 
      var rule = SpreadsheetApp.newConditionalFormatRule()
      .setGradientMaxpointWithValue('#DD7E6B',SpreadsheetApp.InterpolationType.NUMBER, reportedAdGroups['Total']['Cost'] * 0.1  )
      .setGradientMinpointWithValue('#FFF', SpreadsheetApp.InterpolationType.NUMBER, 0)
      .setRanges([targetRange])
      .build()
      conditionalRules.push(rule)
    }
    if (colName.match(/High QS/i)) {
      targetRangeWide.setBackground('#d0e0e3')
    }
    if (colName.match(/Mid QS/i)) {
      targetRangeWide.setBackground('#fff2cc')
    }
    if (colName.match(/Low QS/i)) {
      targetRangeWide.setBackground('#ead1dc')
    }

  })
  
  var topOffSet = Math.min(TOP_X + 2, sheetRows.length) + 1
  
  sheet.getRange(1, 1, 1, sheetHeader.length).setFontWeight('bold')
  sheet.getRange(topOffSet, 1, 1, sheetHeader.length).setFontWeight('bold')
  sheet.setFrozenColumns(3)
  sheet.clearConditionalFormatRules()
  sheet.setConditionalFormatRules(conditionalRules)
  
  sheet.getRange(topOffSet + 2, 5).setBackground("#e57c94")
  sheet.getRange(topOffSet + 2, 6).setValue('Conversion Cost is more than x2 of account avg CPA')
  sheet.getRange(topOffSet + 3, 5).setBackground("#6AA84F")
  sheet.getRange(topOffSet + 3, 6).setValue('Conversion Cost is less than 50% of account avg CPA')
  
  var offset = topOffSet  + 5
  var graphheader = ['Cost', 'CostPerConversion']
  
  
  
  var graphRows = SLOTS.map(function (slot) {
    var row = [SLOT_LABELS[slot]]
    Array.prototype.push.apply(row, graphheader.map( function (colName) {
      return reportedAdGroups['Total'][slot + colName]
    }))
    return row
  })
  graphheader.unshift('Slot')
  graphRows.unshift(graphheader)
  
  debug(_j(graphRows))
  sheet.getRange(offset, 1, graphRows.length, graphheader.length  ).setValues(graphRows).setNumberFormat('#,##0.00')
  sheet.getRange(offset, 1, 1, graphheader.length ).setFontWeight('bold')
  
  var series =      {
    0: {
      labelInLegend: 'Cost',
      dataLabel: "value"
    },
    1: {
      labelInLegend: 'CostPerConversion',
      dataLabel: "value"
    }
  }
    
  var chartBuilder = sheet.newChart();
  chartBuilder
  .setChartType(Charts.ChartType.BAR)
  .addRange(sheet.getRange(offset, 1, graphRows.length, graphheader.length))
  .setOption('title', 'Cost / CPA by Quality Score')
  .setOption('subtitle', 'Total Spend and CPA By Quality Score Slots')
  .setOption('useFirstColumnAsDomain','true')
  .setOption('series', series)
  .build()
    sheet.insertChart(chartBuilder.setPosition(offset, 5, 0, 0 ).build());
  

  Logger.log('QS Report is ready, visit: %s' , ss.getUrl())
  
  
  return 'done'
}

function getSearchCampaigns(format) {
  var query = "Select CampaignId " +
      "FROM CAMPAIGN_PERFORMANCE_REPORT " +
      "WHERE AdvertisingChannelType = SEARCH " +
      ( ENABLED_ONLY ? 'AND CampaignStatus = ENABLED ' : '' ) +
      ((EXCLUDE_CAMP_NAMES && EXCLUDE_CAMP_NAMES.length)? "AND CampaignName NOT_IN [%n] ".replace('%n', EXCLUDE_CAMP_NAMES.map(function (pattern) { return "'"+pattern+"'"  }).join(',')): "" )+
      ((ONLY_CAMP_NAMES && ONLY_CAMP_NAMES.length)? "AND CampaignName IN [%n] ".replace('%n', ONLY_CAMP_NAMES.map(function (pattern) { return "'"+pattern+"'"  }).join(',')): "" )+
      "AND ServingStatus != ENDED "
  
  debug(query)
  var report = AdsApp.report(query)
  var rows = report.rows(),
      campaigns = []
  while (rows.hasNext()) {
    var row = rows.next()
    campaigns.push(row['CampaignId'])
  }
  if (format) return campaigns.map(function (id) { return "'id'".replace('id', id) })
  return campaigns
}

function backNDays (n) {
  var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
  var now = new Date();
  var backDate = new Date(now.getTime() - MILLIS_PER_DAY*n);
  var tz = AdsApp.currentAccount().getTimeZone()
  return Utilities.formatDate(backDate, tz, 'YYYMMdd')
}

function _j(obj) {
  return JSON.stringify(obj, null, 2)
}
function _n(val, placeholder) {
  if (typeof val =="string") return val
  var p = placeholder || ''
  if (isNaN(val)) return 0.0
  if (!isFinite(val)) return p
  return val
}

function debug(format, values) {
  var newAruments = arguments
  newAruments[0] = AdsApp.currentAccount().getName() + ' :: \t' + newAruments[0]
  if (DEBUG) Logger.log.apply(Logger, arguments)
}