SHARE
TWEET

Untitled

a guest Oct 18th, 2019 83 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /**
  2. * Updated in V1.1 on 15-06-2018 to allow the use of multiple versions of this script within one acccount.
  3. * This is especially for running on campaigns that need different Min and Max Bid settings.
  4. *
  5. * Pseudo Average Position Bidding Tool
  6. * Version 2.0.0 - Updated to use model for Pseudo Average position with Google Ads sunset of Avg Position.
  7. *
  8. * This script changes keyword bids so that they target specified positions,
  9. * based on recent performance.
  10. *
  11. * With thanks to brainlabs for the original script.
  12. **/
  13.  
  14. //Options set in each account
  15.  
  16. function remoteScript() {
  17.  
  18. this.main = function() {
  19.    
  20.  var fieldJoin = ",";
  21.  var lineJoin = "$";
  22.  var idJoin = "#";
  23.    
  24.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  25.    
  26.  var files = DriveApp.getFilesByName(dataFile);
  27.  if (!files.hasNext()) {
  28.   var file = DriveApp.createFile(dataFile,"\n");
  29.   Logger.log("File '" + dataFile + "' has been created.");
  30.  } else {
  31.   var file = files.next();
  32.   if (files.hasNext()) {
  33.    Logger.log("Error - more than one file named '" + dataFile + "'");
  34.    return;
  35.   }
  36.   Logger.log("File '" + dataFile + "' has been read.");
  37.  }
  38.    
  39.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  40.    
  41.  var labelIds = [];
  42.    
  43.  var labelIterator = AdWordsApp.labels()
  44.  .withCondition("KeywordsCount > 0")
  45.  .withCondition("LabelName CONTAINS_IGNORE_CASE '" + positionLabelToUse + "'")
  46.  .get();
  47.    
  48.  while (labelIterator.hasNext()) {
  49.   var label = labelIterator.next();
  50.   if (label.getName().substr(0, positionLabelToUse.length).toLowerCase() === lowerCasePositionLabel) {
  51.    labelIds.push(label.getId());
  52.   }
  53.  }
  54.    
  55.  if (labelIds.length == 0) {
  56.   Logger.log("No position labels found.");
  57.   return;
  58.  }
  59.  Logger.log(labelIds.length + " position labels have been found.");
  60.  
  61.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  62.    
  63.  var keywordData = {
  64.   //UniqueId1: {LastHour: {Impressions: , AveragePosition: }, ThisHour: {Impressions: , AveragePosition: },
  65.   //CpcBid: , FirstPageCpc: , MaxBid, MinBid, FirstPageMaxBid, PositionTarget: , CurrentAveragePosition:,
  66.   //Criteria: }
  67.  }
  68.    
  69.  var ids = [];
  70.  var uniqueIds = [];
  71.    
  72.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  73.    
  74. var report = AdWordsApp.report(
  75.   'SELECT Id, Criteria, AdGroupId, AdGroupName, CampaignName, Impressions, AveragePosition, CpcBid, FirstPageCpc, Labels, BiddingStrategyType, AbsoluteTopImpressionPercentage, TopImpressionPercentage ' +
  76.   'FROM KEYWORDS_PERFORMANCE_REPORT ' +
  77.   'WHERE Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignStatus = ENABLED ' +
  78.   'AND LabelIds CONTAINS_ANY [' + labelIds.join(",") + '] ' +
  79.   'AND AdNetworkType2 = SEARCH ' +
  80.    'AND Device NOT_IN ["HIGH_END_MOBILE"] ' +
  81.     'DURING TODAY'
  82.    );
  83.    
  84.  var rows = report.rows();
  85.    
  86.  while(rows.hasNext()){
  87.   var row = rows.next();
  88.   var absTop = row['AbsoluteTopImpressionPercentage']
  89.   var topImp = row['TopImpressionPercentage']
  90.   var impressionsNow = row ['Impressions']
  91.   var oldAvgPos = row['AveragePosition']
  92.  
  93.   var pseudoAveragePositionModel = 3.00126+(topImp*-0.1298)+(absTop*-2.123);
  94.  
  95.    Logger.log("Pseudo Avg Position Model: " + pseudoAveragePositionModel);
  96.  
  97.   if (impressionsNow == 0) {
  98.     var pseudoAveragePosition = 0.0
  99.   } else if (pseudoAveragePositionModel < 1.0 ) {
  100.     var pseudoAveragePosition = 1.0
  101.   } else{
  102.     var pseudoAveragePosition = Math.round(pseudoAveragePositionModel * 10)/10
  103.   }
  104. Logger.log("Final PsAVGPos: " + pseudoAveragePosition)
  105.  
  106.    
  107.   if (row["BiddingStrategyType"] != "cpc") {
  108.    if (row["BiddingStrategyType"] == "Enhanced CPC"
  109.      || row["BiddingStrategyType"] == "Target search page location"
  110.      || row["BiddingStrategyType"] == "Target Outranking Share"
  111.      || row["BiddingStrategyType"] == "None"
  112.      || row["BiddingStrategyType"] == "unknown") {
  113.     Logger.log("Warning: keyword " + row["Criteria"] + "' in campaign '" + row["CampaignName"] +
  114.           "' uses '" + row["BiddingStrategyType"] + "' rather than manual CPC. This may overrule keyword bids and interfere with the script working.");
  115.    } else {
  116.     Logger.log("Warning: keyword " + row["Criteria"] + "' in campaign '" + row["CampaignName"] +
  117.           "' uses the bidding strategy '" + row["BiddingStrategyType"] + "' rather than manual CPC. This keyword will be skipped.");
  118.     continue;
  119.    }
  120.   }
  121.    
  122.   var positionTarget = "";
  123.    
  124.   if (row["Labels"].trim() == "--") {
  125.    continue;
  126.   }
  127.   var labels = JSON.parse(row["Labels"].toLowerCase()); // Labels are returned as a JSON formatted string
  128.    
  129.   for (var i=0; i<labels.length; i++) {
  130.    if (labels[i].substr(0,lowerCasePositionLabel.length) == lowerCasePositionLabel) {
  131.     var positionTarget = parseFloat(labels[i].substr(lowerCasePositionLabel.length-1).replace(/,/g,"."),10);
  132.     break;
  133.    }
  134.   }
  135.   if (positionTarget == "") {
  136.    continue;
  137.   }
  138.   if (integrityCheck(positionTarget) == -1) {
  139.    Logger.log("Invalid position target '" + positionTarget + "' for keyword '" + row["Criteria"] + "' in campaign '" + row["CampaignName"] + "'");
  140.    continue;
  141.   }
  142.    
  143.   ids.push(parseFloat(row['Id'],10));
  144.   var uniqueId = row['AdGroupId'] + idJoin + row['Id'];
  145.   uniqueIds.push(uniqueId);
  146.    
  147.   keywordData[uniqueId] = {};
  148.   keywordData[uniqueId]['Criteria'] = row['Criteria'];
  149.   keywordData[uniqueId]['ThisHour'] = {};
  150.    
  151.   keywordData[uniqueId]['ThisHour']['Impressions'] = parseFloat(row['Impressions'].replace(/,/g,""),10);
  152.   keywordData[uniqueId]['ThisHour']['AveragePosition'] = pseudoAveragePosition;
  153.  
  154.   var keywordDataTestElement = keywordData[uniqueId]['ThisHour']['AveragePosition']
  155.   Logger.log(keywordDataTestElement)
  156.    
  157.   keywordData[uniqueId]['CpcBid'] = parseFloat(row['CpcBid'].replace(/,/g,""),10);
  158.   keywordData[uniqueId]['FirstPageCpc'] = parseFloat(row['FirstPageCpc'].replace(/,/g,""),10);
  159.    
  160.   setPositionTargets(uniqueId, positionTarget);
  161.  }
  162.  
  163.  Logger.log(uniqueIds.length + " labelled keywords found");
  164.    
  165.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  166.    
  167.  setBidChange();
  168.  setMinMaxBids();
  169.    
  170.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  171.    
  172.  var currentHour = parseInt(Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), "HH"), 10);
  173.    
  174.  if (currentHour != 0) {
  175.   var data = file.getBlob().getDataAsString();
  176.   var data = data.split(lineJoin);
  177.   for(var i = 0; i < data.length; i++){
  178.    data[i] = data[i].split(fieldJoin);
  179.    var uniqueId = data[i][0];
  180.    if(keywordData.hasOwnProperty(uniqueId)){
  181.     keywordData[uniqueId]['LastHour'] = {};
  182.     keywordData[uniqueId]['LastHour']['Impressions'] = parseFloat(data[i][1],10);
  183.     keywordData[uniqueId]['LastHour']['AveragePosition'] = parseFloat(data[i][2],10);
  184.    }
  185.   }
  186.  }
  187.    
  188.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  189.    
  190.  findCurrentAveragePosition();
  191.    
  192.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  193.    
  194.  //Batch the keyword IDs, as the iterator can't take them all at once
  195.  var idBatches = [];
  196.  var batchSize = 5000;
  197.  for (var i=0; i<uniqueIds.length; i += batchSize) {
  198.   idBatches.push(uniqueIds.slice(i,i+batchSize));
  199.  }
  200.  
  201.  Logger.log("Updating keywords");
  202.    
  203.  // Update each batch
  204.  for (var i=0; i<idBatches.length; i++) {
  205.   try {
  206.    updateKeywords(idBatches[i]);
  207.   } catch (e) {
  208.    Logger.log("Error updating keywords: " + e);
  209.    Logger.log("Retrying after one minute.");
  210.    Utilities.sleep(60000);
  211.    updateKeywords(idBatches[i]);
  212.   }
  213.  }
  214.    
  215.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  216.    
  217.  Logger.log("Writing file.");
  218.  var content = resultsString();
  219.  file.setContent(content);
  220.  
  221.  Logger.log("Finished.");
  222.    
  223.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  224.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  225.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  226.    
  227.  // Functions
  228.    
  229.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  230.    
  231.  function integrityCheck(target){
  232.   var n = parseFloat(target, 10);
  233.   if(!isNaN(n) && n >= 1){
  234.    return n;
  235.   }
  236.   else{
  237.    return -1;
  238.   }
  239.    
  240.  }
  241.    
  242.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  243.    
  244.  function setPositionTargets(uniqueId, target){
  245.   if(target !== -1){
  246.    keywordData[uniqueId]['HigherPositionTarget'] = Math.max(target-targetPositionTolerance, 1);
  247.    keywordData[uniqueId]['LowerPositionTarget'] = target+targetPositionTolerance;
  248.   }
  249.   else{
  250.    keywordData[uniqueId]['HigherPositionTarget'] = -1;
  251.    keywordData[uniqueId]['LowerPositionTarget'] = -1;
  252.   }
  253.  }
  254.    
  255.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  256.    
  257.  function bidChange(uniqueId){
  258.    
  259.   var newBid = -1;
  260.   if(keywordData[uniqueId]['HigherPositionTarget'] === -1){
  261.    return newBid;
  262.   }
  263.    
  264.   var cpcBid = keywordData[uniqueId]['CpcBid'];
  265.   var minBid = keywordData[uniqueId]['MinBid'];
  266.   var maxBid = keywordData[uniqueId]['MaxBid'];
  267.    
  268.   if (isNaN(keywordData[uniqueId]['FirstPageCpc'])) {
  269.    Logger.log("Warning: first page CPC estimate is not a number for keyword '" + keywordData[uniqueId]['Criteria'] + "'. This keyword will be skipped");
  270.    return -1;
  271.   }
  272.    
  273.   var firstPageBid = Math.min(keywordData[uniqueId]['FirstPageCpc'], keywordData[uniqueId]['FirstPageMaxBid'], maxBid);
  274.    
  275.   var currentPosition = keywordData[uniqueId]['CurrentAveragePosition'];
  276.   var higherPositionTarget = keywordData[uniqueId]['HigherPositionTarget'];
  277.   var lowerPositionTarget = keywordData[uniqueId]['LowerPositionTarget'];
  278.    
  279.   var bidIncrease = keywordData[uniqueId]['BidIncrease'];
  280.   var bidDecrease = keywordData[uniqueId]['BidDecrease'];
  281.    
  282.   if((currentPosition > lowerPositionTarget) && (currentPosition !== 0)){
  283.    var linearBidModel = Math.min(2*bidIncrease,(2*bidIncrease/lowerPositionTarget)*(currentPosition-lowerPositionTarget));
  284.    var newBid = Math.min((cpcBid + linearBidModel), maxBid);
  285.   }
  286.   if((currentPosition < higherPositionTarget) && (currentPosition !== 0)) {
  287.    var linearBidModel = Math.min(2*bidDecrease,((-4)*bidDecrease/higherPositionTarget)*(currentPosition-higherPositionTarget));
  288.    var newBid = Math.max((cpcBid-linearBidModel),minBid);
  289.    if (cpcBid > firstPageBid) {
  290.     var newBid = Math.max(firstPageBid,newBid);
  291.    }
  292.   }
  293.   if((currentPosition === 0) && useFirstPageBidsOnKeywordsWithNoImpressions && (cpcBid < firstPageBid)){
  294.    var newBid = firstPageBid;
  295.   }
  296.    
  297.   if (isNaN(newBid)) {
  298.    Logger.log("Warning: new bid is not a number for keyword '" + keywordData[uniqueId]['Criteria'] + "'. This keyword will be skipped");
  299.    return -1;
  300.   }
  301.    
  302.   return newBid;
  303.    
  304.  }
  305.    
  306.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  307.    
  308.  function findCurrentAveragePosition(){
  309.   for(var x in keywordData){
  310.    if(keywordData[x].hasOwnProperty('LastHour')){
  311.     keywordData[x]['CurrentAveragePosition'] = calculateAveragePosition(keywordData[x]);
  312.    } else {
  313.     keywordData[x]['CurrentAveragePosition'] = keywordData[x]['ThisHour']['AveragePosition'];
  314.    }
  315.   }
  316.  }
  317.    
  318.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  319.    
  320.  function calculateAveragePosition(keywordDataElement){
  321.   var lastHourImpressions = keywordDataElement['LastHour']['Impressions'];
  322.   var lastHourAveragePosition = keywordDataElement['LastHour']['AveragePosition'];
  323.    
  324.   var thisHourImpressions = keywordDataElement['ThisHour']['Impressions'];
  325.   var thisHourAveragePosition = keywordDataElement['ThisHour']['AveragePosition'];
  326.    
  327.   if(thisHourImpressions == lastHourImpressions){
  328.    return 0;
  329.   }
  330.   else{
  331.    var currentPosition = (thisHourImpressions*thisHourAveragePosition-lastHourImpressions*lastHourAveragePosition)/(thisHourImpressions-lastHourImpressions);
  332.    if (currentPosition < 1) {
  333.     return 0;
  334.    } else {
  335.     return currentPosition;
  336.    }
  337.   }
  338.  }
  339.    
  340.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  341.    
  342.  function keywordUniqueId(keyword){
  343.   var id = keyword.getId();
  344.   var idsIndex = ids.indexOf(id);
  345.   if(idsIndex === ids.lastIndexOf(id)){
  346.    return uniqueIds[idsIndex];
  347.   }
  348.   else{
  349.    var adGroupId = keyword.getAdGroup().getId();
  350.    return adGroupId + idJoin + id;
  351.   }
  352.  }
  353.    
  354.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  355.    
  356.  function setMinMaxBids(){
  357.   for(var x in keywordData){
  358.    keywordData[x]['MinBid'] = minBid;
  359.    keywordData[x]['MaxBid'] = maxBid;
  360.    keywordData[x]['FirstPageMaxBid'] = firstPageMaxBid;
  361.   }
  362.  }
  363.    
  364.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  365.    
  366.  function setBidChange(){
  367.   for(var x in keywordData){
  368.    keywordData[x]['BidIncrease'] = keywordData[x]['CpcBid'] * bidIncreaseProportion/2;
  369.    keywordData[x]['BidDecrease'] = keywordData[x]['CpcBid'] * bidDecreaseProportion/2;
  370.   }
  371.  }
  372.    
  373.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  374.    
  375.  function updateKeywords(idBatch) {
  376.   var keywordIterator = AdWordsApp.keywords()
  377.   .withIds(idBatch.map(function(str){return str.split(idJoin);}))
  378.   .get();
  379.   while(keywordIterator.hasNext()){
  380.    var keyword = keywordIterator.next();
  381.      
  382.    var uniqueId = keywordUniqueId(keyword);
  383.      
  384.    var newBid = bidChange(uniqueId);
  385.      
  386.    if(newBid !== -1){
  387.     keyword.setMaxCpc(newBid);
  388.    }
  389.      
  390.   }
  391.  }
  392.    
  393.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  394.    
  395.  function resultsString(){
  396.    
  397.   var results = [];
  398.   for(var uniqueId in keywordData){
  399.    var resultsRow = [uniqueId, keywordData[uniqueId]['ThisHour']['Impressions'], keywordData[uniqueId]['ThisHour']['AveragePosition']];
  400.    results.push(resultsRow.join(fieldJoin));
  401.   }
  402.    
  403.   return results.join(lineJoin);
  404.  }
  405.    
  406.  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  407.    
  408. }
  409. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top