Landing Page data script problem

[Angus Lai]

I want to gather landing page data and add a field to separate them, but didn't work.

And I'm here asking for help. Script is below:

var SPREADSHEET_URL = "";
// All settings
// --- bucket on ROAS or CPA
var bucketRoas = true; // bucket based on tROAS
var bucketCpa = false; // bucket based on tCPA
// --- threshold settings
var targetRoas = 8; // your ROAS target, will be ignored if bucketRoas = false
var targetCpa = 40; // your CPA target, will be ignored if bucketCpa = false
var averageCvr = 3; // your average conversion rate to calculate the click threshold
var impressionThreshold = 50; // between 1 - 50 means a 'no-indexed' product, all products without a label have 0 impressions
var daysAgo = 90; // the date range you want to look at
// Advanced settings
// --- tROAS difference setting
var percentageDifferenceTarget = 20; // difference in % of your target it partly defines if a product is over-index or near-index
// --- over-index setting
var addToOverIndex = true; // add or do not add products to the over-index label, if not those will be added to your index bucket (set to true or false)
var clickMultiplier = 3; // your click multiplier to become an over-index product
// Start script, don't add anything below this line
function main() {
  if (bucketRoas === bucketCpa) {
    Logger.log("Error: Both bucketRoas and bucketCpa cannot be set to the same value. Please set one to true and the other to false."); // [2](https://support.google.com/google-ads/thread/191343654/target-cpa-40-but-cost-conv-110-should-i-set-new-target-cpa?hl=en)
    return;
  }

  var product = unexpanded_final_url(daysAgo)
    products.sort(function (a, b) {
    return a[0] > b[0];
  });
  products = products.slice(0, 999999);
  pushToSpreadsheet(products);
}
function unexpanded_final_url(daysAgo) {
  var today = new Date();
  var daysAgo = new Date(today.getFullYear(), today.getMonth(), today.getDate() - daysAgo);
  var dateFrom = Utilities.formatDate(daysAgo, AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
  var dateTo = Utilities.formatDate(today, AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
  var query =
  "SELECT landing_page_view.unexpanded_final_url, metrics.active_view_ctr, metrics.active_view_measurable_impressions, metrics.cost_per_conversion, metrics.conversions_value, metrics.clicks, metrics.conversions, metrics.cost_micros"
  +"FROM landing_page_view"+
  "DURING "+ dateFrom +","+ dateTo;
  var products = [];
  var count = 0;
  var productTypeCounts = {
    'over-index': 0,
    'index': 0,
    'near-index': 0,
    'no-index': 0,
    'under-index': 0
  };
  var report = AdWordsApp.report(query);
  var rows = report.rows();
  while (rows.hasNext()) {
    var row = rows.next();
    var FinalURL = row['landing_page_view.unexpanded_final_url'];
    var impressions = row['metrics.active_view_measurable_impressions'].toString();
    var clicks = row['metrics.clicks'].toString();
    var cost = row['Cost'].toString();
    var conversions = row['metrics.conversions'].toString();
    var conversionValue = row['metrics.conversions_value'].toString();
    var convPerCost = (cost.replace(",", "") / conversions.replace(",", "")).toString();
    if (isNaN(convPerCost) || !isFinite(convPerCost)){
      convPerCost = 0;
    }
    var convValuePerCost = (conversionValue.replace(",", "") / cost.replace(",", "")).toString();
    if (isNaN(convValuePerCost)){
      convValuePerCost = 0;
    }
    var isProductType = '';
    if (bucketRoas) {
      if (addToOverIndex && clicks > ((100 * clickMultiplier) / averageCvr)
          && convValuePerCost >= targetRoas + (percentageDifferenceTarget / 100 * targetRoas)) {
        isProductType = 'over-index';
      } else if (clicks >= (100 / averageCvr)
          && convValuePerCost >= targetRoas) {
        isProductType = 'index';
      } else if (convValuePerCost >= (targetRoas - (percentageDifferenceTarget / 100 * targetRoas))) {
        isProductType = 'near-index';
      } else if (impressions < impressionThreshold) {
        isProductType = 'no-index';
      } else {
        isProductType = 'under-index';
      }
    } else if (bucketCpa) {
      if (addToOverIndex && clicks > ((100 * clickMultiplier) / averageCvr)
          && convPerCost <= targetCpa - (percentageDifferenceTarget / 100 * targetCpa)
          && conversions >= 1) {
        isProductType = 'over-index';
      } else if (clicks >= (100 / averageCvr)
          && convPerCost <= targetCpa
          && conversions >= 1) {
        isProductType = 'index';
      } else if (convPerCost <= (targetCpa + (percentageDifferenceTarget / 100 * targetCpa))
          && conversions >= 1) {
        isProductType = 'near-index';
      } else if (impressions < impressionThreshold) {
        isProductType = 'no-index';
      } else {
        isProductType ='under-index';
      }
    }
    productTypeCounts[isProductType]++;
    products.push([
      FinalURL,
      impressions,
      clicks,
      cost,
      conversions,
      conversionValue,
      convValuePerCost,
      convPerCost,
      isProductType,
    ]);
    count += 1;
  }
  Logger.log('Total products: ' + count); // [6](https://www.reddit.com/r/PPC/comments/fquutx/the_system_started_to_ignore_my_target_cpa_what/)
  for (var productType in productTypeCounts) {
    Logger.log(productType + ': ' + productTypeCounts[productType]); // [3](https://support.google.com/google-ads/answer/2684489?hl=en)
  }
  return products;
}
function pushToSpreadsheet(data){
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = spreadsheet.getSheetByName('Custom_Label');
  var lastRow = sheet.getMaxRows();
  sheet.getRange('A2:I'+lastRow).clearContent();
  var start_row=2;
  var endRow=start_row+data.length-1;
  var range = sheet.getRange('A'+start_row+':'+'I'+endRow);
  if (data.length>0){range.setValues(data);}
  return;
}

------------------------------------------------------------------------

The information in this email and any attachments may contain Beyond Media Group proprietary and confidential information that is intended for the addressee(s) only. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, retention or use of the contents of this information is prohibited. When addressed to our clients or vendors, any information contained in this e-mail or any attachments is subject to the terms and conditions in any governing contract in all regions we operate in as well as local legal regulations. If you have received this email in error, please immediately contact the sender or le...@bmgww.com and delete the email.  Ethics and Integrity define Our Mission :   https://www.bmgww.com/about

[Google Ads Scripts Forum]

Hi,

Thank you for contacting the Google Ads Scripts team. 

I would like to inform you that your query is not clear, can you elaborate more about your concern? Also, provide us with the following information to further investigate this issue:

• Google Ads account ID, or CID.

• Name of the script.

• Error details or a screenshot of the issue (if any).

You can send the details via Reply privately to the author option, or direct private reply to this email.

Thanks,

Google Ads Scripts Team.

[Haseeb Mohamed]

Issues and Errors:

1. Configuration Error:

   • If both bucketRoas and bucketCpa are set to the same value (both true or both false), it logs an error and exits the script.

2. Syntax and Logical Errors:

   • The product variable in main() is incorrectly initialized. It should be var products = unexpanded_final_url(daysAgo);.
   • In the SQL-like query, there should be spaces before FROM and DURING.
   • Variable naming inconsistency: cost in the while loop should be fetched using row['metrics.cost_micros'], and conversion from micros to actual cost should be done by dividing by 1,000,000 (assuming the cost is in micros).

[Haseeb Mohamed]

Hi ankus can i have your contact to discuss this 

On Wednesday, May 29, 2024 at 11:11:48 PM UTC+5:30 Google Ads Scripts Forum wrote: