Low Volume SKUs - where are ramped up products?

[J. P.]

Hi,

we've implemented this solution:

https://marketingplatformacademy.withgoogle.com/events/tech-breakfast-training-series?talk=low-volume-sku

The script flags low volume products, displays them in a pmax campaign, after getting a click it is removed from low volume label - up to this point it works as intended. 

However, no products are ever flagged as ramped up. When a given product receives a click, it moves back to original campaign instead. Custom label doesn't ever change to ramped up.

Any ideas why it happens?

Posting the script below (w/o sheets url).

Cheers.

/* This Script pulls all products associated with the account CID.
 * You can optionally add a MerchantId filter, as stated below.
 * Please modify the variable for spreadsheet link as instructed below
 * Some other variables may be modified by demand, following guideline in
 comments.
 * Currently this script outputs 2 columns, the product ID and the custom label.
 * IMPORTANT: An EMPTY label is required for this solution.
 * NOTE: A product should not be running in multiple campaigns.

 * COPY THIS SCRIPT INTO YOUR GOOGLE ADS SCRIPT.
 */


// Define which custom label nr [0-4] will be used.
// IMPORTANT: Ensure this label is free and only used for this solution.
// This number should match the custom_label nr in the second column of the
// spreadsheet above.
var CUSTOM_LABEL_NR = '4';

// Create a new Google spreadsheet.
// Add these values to A1 and B1 respectively:
// A1 = 'id', B1 = 'custom_label4' - the nr of the custom label should match the
// above. Name this working sheet 'LowVolume'. Copy the link of the new sheet
// and paste it below.
var SPREADSHEET_URL =
    'sheet';

// Set the value for the label for newly flagged low volume products.
var LABEL_LOW = 'low_clicks_last_30D';

// Set the value for the label for low volume products that have ramped up.
var LABEL_RAMPED_UP = 'product_ramped_up';

// Set the nr. of clicks with which should be considered ramped_up.
// It needs to be a string to be added as part of the query statement.
var THRESHOLD = '1';
var IMPRESSIONS_THRESHOLD = '101';
// The following filter will detect low volume products, using the threshold
// above. You can add other metrics to filter on, for ex. adding AND
// metrics.impressions < 100. Optionally you can filter on a merchant, e.g.
// adding AND MerchantId = 1234.
var FILTER_NO_CLICKS = 'metrics.clicks < ' + THRESHOLD + ' AND metrics.impressions < ' + IMPRESSIONS_THRESHOLD;

// The following filter will identify products that have already ramped up.
// As a condition, it must have the previously added label and for ex. clicks
// >50. To add further conditions use the AND clause, e.g. AND Conversions > 10.
var FILTER_RAMPED_UP = 'metrics.clicks > ' + THRESHOLD +
    ' AND segments.product_custom_attribute' + CUSTOM_LABEL_NR + ' = "' +
    LABEL_LOW + '" ';

// To filter campaign names, add for ex. AND campaign.name LIKE “%FR_FR%”.
// Set the filter to true to include it.
var USE_CAMPAIGN_FILTER = false;
var FILTER_CAMPAIGN_NAME = ' AND campaign.name LIKE "%FR_FR_%" ';

// Enter time duration below. Possibilities:
// TODAY | YESTERDAY | LAST_7_DAYS | LAST_WEEK | LAST_BUSINESS_WEEK |
// THIS_MONTH | LAST_MONTH | LAST_14_DAYS | LAST_30_DAYS |
// THIS_WEEK_SUN_TODAY | THIS_WEEK_MON_TODAY | LAST_WEEK_SUN_SAT Currently
// default time duration is set to: LAST_30_DAYS
var TIME_DURATION = 'LAST_30_DAYS';

// This variable helps control data overflow in the target sheet.
// Increasing this value may cause timeouts and sheet errors.
// For ex. 10K products may take ~30 secs to run, 100K ~ 5 mins, while 500K
// could take 20+ mins.
var COUNT_LIMIT = '10000';


function main() {
  var productsNoClicks =
      getFilteredShoppingProducts(FILTER_NO_CLICKS, checkLabel = false);
  var productsRampedUp =
      getFilteredShoppingProducts(FILTER_RAMPED_UP, checkLabel = true);
  var products = productsNoClicks.concat(productsRampedUp);
  pushToSpreadsheet(products);
}

function getFilteredShoppingProducts(filters, checkLabel) {
  var campaignField = ''
  if (USE_CAMPAIGN_FILTER) {
    campaignField = 'campaign.name, ';
    filters = filters + FILTER_CAMPAIGN_NAME
  }
  var labelField = ''
  if (checkLabel) {
    label = 'segments.product_custom_attribute' + CUSTOM_LABEL_NR
    labelField = label + ', '
  };

  var query = 'SELECT segments.product_item_id, ' + campaignField + labelField +
      'metrics.clicks, metrics.impressions ' +
      'FROM shopping_performance_view WHERE ' + filters +
      ' AND segments.product_item_id != "undefined"' +
      ' AND segments.date DURING ' + TIME_DURATION +
      ' ORDER BY segments.product_item_id LIMIT ' + COUNT_LIMIT;

  var products = [];
  var count = 0;
  var report = AdsApp.report(query);
  var rows = report.rows();
  while (rows.hasNext()) {
    var row = rows.next();
    var clicks = row['metrics.clicks'];
    var productId = row['segments.product_item_id']

    // Label product as low volume, if below threshold defined above.
    if (clicks < THRESHOLD) {
      products.push([productId, LABEL_LOW]);
      count += 1;

      // Label product as ramped up, if it surpasses expected threshold.
    } else if (
        row[label] == LABEL_LOW && clicks > parseInt(THRESHOLD)) {
      products.push([product_id, LABEL_RAMPED_UP]);
      count += 1;
    }
  }
  Logger.log(count);
  return products;
}


function pushToSpreadsheet(data) {
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = spreadsheet.getSheetByName('LowVolume');

  var lastRow = sheet.getMaxRows();
  sheet.getRange('A2:B' + lastRow).clearContent();

  var start_row = 2;
  var endRow = start_row + data.length - 1;
  var range = sheet.getRange(
      'A' + start_row + ':' +
      'B' + endRow);
  if (data.length > 0) {
    range.setValues(data);
  }

  return;
}

[Google Ads Scripts Forum Advisor]

Hi,

 

Thank you for reaching out to us. This is Yasmin from the Google Ads Scripts team.

 

Can you please provide the following so we're able to further investigate this and better address your concern?:

• Google Ads account ID / CID
• Script name
• Screenshot of the issue
• Shareable link of your spreadsheet

 

Kindly send the requested items via `Reply privately to author` option. If the said option is not available on your end, you may send those through this email alias <googleadsscr...@google.com> instead.

 

Regards,

 

Yasmin Gabrielle Google Ads Scripts Team

ref:_00D1U1174p._5004Q2blfmU:ref

[Carmine D'Alessio]

i have the same problem

[Google Ads Scripts Forum]

Reposting the last inquiry (https://groups.google.com/g/adwords-scripts/c/jcuMGs_fKa8) from the forum as it wasn't routed to our support queue.

Regards,
Yasmin
Google Ads Scripts Team

[Google Ads Scripts Forum Advisor]

Hello,

 

This is Yasmin from the Google Ads scripts team. Please excuse us as forum messages fail to be routed to our support queue.

 

Thank you for confirming this. Can you please confirm if you are still having an issue with regard to your script? If yes, kindly provide the following so we're able to further investigate this and better address your concern:

• Google Ads account ID / CID
• Script name
• Screenshot of the issue

• Shareable link of your spreadsheet (You may follow this guide under Share a file publicly)