Inzicht in CPC pieken

// SEAlyzer CPC Peak Analyzer
//
// Author: Stefan Neefischer (stefan@sealyzer.com)
// Minor changes: Tibbe van Asten
// 
// Created: 13-05-2020
// Last update: 15-06-2021
//
// ABOUT THE SCRIPT
// This script gives you insights on the impact of hidden search queries
// in your account. See what percentage of impressions, clicks, cost
// and conversion is generated by unknows search queries.
//
////////////////////////////////////////////////////////////////////

var config = {
  
  DATE_RANGE : last_n_days(30),
  
  // Type in your upper CPC bound - some good dynamic bounds are: 2 * AvgCPC, 3 * AvgCPC
  MAX_CPC : 5.00,
  
}

////////////////////////////////////////////////////////////////////

function main(){
  
  report("KeywordId, AdNetworkType2, Query, Date, Impressions, Clicks, AverageCpc, Conversions, ConversionValue, Cost, Device","SEARCH_QUERY_PERFORMANCE_REPORT");
  report("Id, Criteria, AdNetworkType2, Date, ClickType, Device, Impressions, Clicks, AverageCpc, Conversions, ConversionValue, Cost","KEYWORDS_PERFORMANCE_REPORT");
  
} // function main()

////////////////////////////////////////////////////////////////////

function report(fields, type) {
  
  // Microamount is used in awql - we have to convert it
  var upperbound_micro = 1000000 * config.MAX_CPC;
  
  var top_cpc = 0;
  var cost_sum = 0;
  var conversion_sum = 0;
  var conversionValue_sum = 0;
  
  outliercount = 0;
 
  var report = AdsApp.report(
    "SELECT " + fields +
    " FROM " + type +
    " WHERE AverageCpc > "+ upperbound_micro +
    " DURING " + config.DATE_RANGE);
  
  var rows = report.rows();  
  while (rows.hasNext()) {
    var row = rows.next();
    
    outliercount += 1;
    
    cost_sum += parseFloat(row['Cost']);
    conversion_sum += parseFloat(row['Conversions']);
    conversionValue_sum+=parseFloat(row['ConversionValue']);
    
    if (top_cpc < row['AverageCpc']) {
      top_cpc = row['AverageCpc'];
    }

  } // rowIterator
  
  if (conversion_sum > 0) {
    cpo = cost_sum/conversion_sum;
  } else {
    cpo = cost_sum;
  }
  
  if (cost_sum > 0) {
    roi = conversionValue_sum / cost_sum;
  } else {
    roi = 0;
  }

  Logger.log("--------------------------------");
  Logger.log("Segment performance of Clicks with CPCs > " + config.MAX_CPC);
  Logger.log("FROM " + type);
  Logger.log("--------------------------------");
  Logger.log("Your CPC Peak was " + top_cpc + " for the time period " + config.DATE_RANGE);
  Logger.log("There where " + outliercount + " cases of clicks higher than your CPC bound");
  Logger.log("In total you paid " + cost_sum.toFixed(2) + " for those clicks");
  Logger.log("This segment of clicks generated " + conversion_sum.toFixed(2) + " conversions resulting in a CPO of " + cpo.toFixed(2));
  Logger.log("This segment of clicks generated " + conversionValue_sum.toFixed(2) + " in conversionValue resulting in a ROI of " + roi.toFixed(2));
  Logger.log(" ");
  
} // funtion report()

////////////////////////////////////////////////////////////////////

function last_n_days(n) {
  
	var	from = new Date(), to = new Date();
	to.setUTCDate(from.getUTCDate() - n);
  
	return googleDateRange(from, to);
  
} // function last_n_days()

////////////////////////////////////////////////////////////////////

function googleDateRange(from, to) {
  
	function googleFormat(date) {
    
		var date_array = [date.getUTCFullYear(), date.getUTCMonth() + 1, date.getUTCDate()];
    
		if (date_array[1] < 10) date_array[1] = '0' + date_array[1];
		if (date_array[2] < 10) date_array[2] = '0' + date_array[2];
    
		return date_array.join('');
	}
  
	var inverse = (from > to);
	from = googleFormat(from);
	to = googleFormat(to);
  
	var result = [from, to];
	
  if (inverse) {
		result = [to, from];
	}
  
	return result;
  
} // function googleDateRange()