Flowboost Labelizer Google Ads script

Well, as many of our Library members, I used the solution of Product Hero before (shoutout to their idea). Only I found it not the best segmentation and it was very prone to errors. Those errors are mainly because of the coming of pMax and high fluctuations due to display and YouTube traffic.

floris

August 12, 2023

Since august 2022 I am using my first version of the Labelizer. It’s a Google Ads scripts that gets all the performance data from Google Shopping and segments based on good and bad performance.

The good and bad performance can be set in the config part of the script using nothing more than the target ROAS and the actual conversion rate. This conversion rate must be checked every week and updated accordingly (free version).

After a while I created a stronger version which has become quite complex. One in every 3 users need a lot over support, that’s why it’s a premium one now. BUT you don’t need the premium one when you know what you’re doing, have ROAS targets between 4-6 and do not have capitalized item IDs.

So why I created the Flowboost Labelizer?

Let’s dive in the settings and differences between the Product Hero- and the Flowboost Labelizer.

Well first of all it’s how we name the products.

Product Hero has: Heroes, Sidekicks, Villains and Zombies. Where my segmentation has: over-index, index, near-index, under-index and no-index.

Later in this blogpost I will explain when a product will be matched as one of them.

The PH solution only uses clicks and tROAS where the Flowboost Labelizer uses:

Actual and live conversion rates
The possibility to add a ‘Superhero’ bucket
A more realistic way of grouping the products

Moreover, in the premium version there is an advanced dashboard where you can see the change in products over time.

Let’s dive deeper into the differences. And please, feel free to contact me using the form (or e-mail me) if you have any questions about this script.

The actual conversion rates

Well sometimes you’ll get a ton of clicks due to display traffic. In the Product Hero solution most of your products turn into Heroes or Villains instead of possible Sidekicks when the conversion rates drop.

This happened to me quite some time and shook up the entire algorithm. I was bidding top dollar on products that suddenly got pushed on display with only one conversion. For over a week (until the regrouping took place), I spend too much on a product who had one lucky conversion.

In my opinion only one conversion does not say anything. That’s why the no-index group can contain an occasional conversion when there are not many item clicks and the ROAS in under the index.

Also, in bid management or anything else than product analysis I never take one conversion into account. I believe it can be a lucky shot.

The Superhero bucket

When I use standard shopping and I have a ton of products I don’t want to go to pMax with all of them. Because it will likely not to boost the profit.

No, I only want to go with my best performers.

Sometimes those are the index products. But when that group is too big, I use the over-index products to advertise in my pMax layer.

The Superhero bucket can be set inside the config to ‘true’, if it’s set to ‘false’ the products will be added to the index bucket.

A realistic grouping

Now it’s time to explain how the segmentation works. Here it gets a bit technical because I am using parts of the IF statement from version 4 to explain it better.

Over-index condition

This condition checks whether a product is performing significantly above the set target and got enough clicks according to the click multiplier you can set yourself.

Basically ‘3x’ means on average 3 conversions in the timespan.

config.addToOverIndex: This is a setting which, if true, allows products to be classified as ‘over-index’.
productData.clicks > ((100 * config.clickMultiplier) / averageConversionRate): This part checks if the product has enough clicks. It uses a special calculation that takes into account the average conversion rate and a multiplier.
convValuePerCost >= config.targetRoas + (config.percentageDifferenceTarget / 100 * config.targetRoas): This checks if the return on ad spend (ROAS) is above the target ROAS by a certain percentage. If these conditions are all met, the product is performing much better than the target and is labeled as ‘over-index’.

Index condition

This condition checks whether a product is performing at or slightly above the target and has reached enough clicks for at least one conversion.

productData.clicks >= (100 / averageConversionRate): This part checks if the product has enough clicks, this time using the average conversion rate.
convValuePerCost >= config.targetRoas: This checks if the ROAS is at least as high as the target. If these conditions are met, the product is performing well and is labeled as ‘index’.

Near-index condition

This condition checks whether a product is performing slightly below the target.

convValuePerCost >= (config.targetRoas -(config.percentageDifferenceTarget / 100 * config.targetRoas)): This checks if the ROAS is at least as high as the target, minus a certain percentage. If this condition is met, the product is performing slightly below target but still close, and is labeled as ‘near-index’.

Under-index condition

This condition checks whether a product is performing below the target.

productData.conversions > 1 || productData.clicks > (100 / averageConversionRate): This checks if the product has more than one conversion or if it has enough clicks. If one of these conditions is met, but the product’s ROAS is not meeting the conditions of the above labels, the product is not performing well and is labeled as ‘under-index’.

This can be a rather small group if your clicks suddenly increase due to more display traffic.

No-index condition

If a product doesn’t meet any of the conditions for the above labels, it is labeled as ‘no-index’. This usually means it has very low conversions and clicks. But it can have a high number of items.

Here is it important to state that if your conversion rate drops you’ll have a lot of underperforming, but potential ‘no-index’ products in your feed.

💡 – Flowboost tip for the no-index condition

It’s best to create a separate campaign for the no-index products and give it sufficient budget to test with. This is the group of items that need to be activated and thus you don’t want it to be on a tROAS strategy.

How to install the script and how to fetch them with merchant center can be read in the additional resources.

Flowboost Labelizer: step-by-step instructions

// — install script: View scribe instructions
// — add as supplemental feed: View scribe instructions

If you want to give it a try, you can use our free version. See code underneath. Use the above step-by-step instructions to make it work. But, if you’re familiar with scripts, you can 100% figure it out yourself.

// Copyright 2024. All Rights Reserved.
// Flowboost Performance Labelizer
// Custom requests? arjan@flowboost.com
//
// V1.1 - free version
//
// Learn more about this script on ppclibrary.com
//
////////////////////////////////////////////////////////////////////

// Instruction for this script:
// 1. Copy the spreadsheet below and paste in the new spreadsheet url here.
// 2. Set the settings below.
// 3. Run the script daily.

// be aware: products with no impressions will not be added, include all other items in the no-index campaign or ad group

// ------- updates will be uploaded to the library first
// -- v4.1 is live since March 2024 > advanced settings, better segmentation and graphs

// make sure you register at https://library.flowboost.com for other free scripts, automation tips, how-to and much more (to come)

// Enter your data here >

var SPREADSHEET_URL = "https://docs.google.com/spreadsheets/d/1AHPMy__XMFBtufgsc-RQWcktAjX2K_GZyy3nTUqprWI/copy"; // make a copy of the spreadsheet, don’t change any tabs in the sheet

var breakevenRoas = 6.5; // set it 10-20% lower than the account target
var AverageCvr = 5; // conversion rate on average in shopping in past daysAgo
var impressionThreshold = 50; // less than 50 means a ‘zombie’ or ‘no-index’
var daysAgo = 90; // days you like to look back at

// Advanced settings only available in premium labelizer script (get it in the library)

// - conversion lag in days
// - segment on 1 or multiple campaign(s) only
// - capitalization bug
// - 4 graphs and over time improvement
// - bucket for proven bad products
// - creates buckets based relative- instead of absolute difference in ROAS
// - bucket based on tCPA or tROAS

// Start script don't edit anything below here

function main() {
  var products = getFilteredShoppingProducts(daysAgo);
  products.sort(function (a, b) {
    return a[0] > b[0];
  });
  products = products.slice(0, 999999);
  pushToSpreadsheet(products);
  Logger.log("Script finished");
  Logger.log(`---->
    Upgrade to premium script at library.flowboost.com.
    New features are available such as:
    - Graphs
    - Advanced settings
    - Future proof
    - Future updates included
    - Faster and more accurate
`);
}

function getFilteredShoppingProducts(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 segments.product_item_id, metrics.impressions, metrics.clicks, metrics.ctr, metrics.cost_micros, metrics.conversions, metrics.conversions_value " +
    "FROM shopping_performance_view " +
    "WHERE segments.date BETWEEN " +
    dateFrom +
    " AND " +
    dateTo;

  var products = [];
  var count = 0;
  var report = AdWordsApp.report(query);
  var rows = report.rows();
  while (rows.hasNext()) {
    var row = rows.next();
    var offer_id = row["segments.product_item_id"];
    var impressions = row["metrics.impressions"].toString();
    var clicks = row["metrics.clicks"].toString();
    var cost_micros = row["metrics.cost_micros"].toString();
    var cost = cost_micros / 1000000;
    var conversions = row["metrics.conversions"].toString();
    var conversionValue = row["metrics.conversions_value"].toString();
    var costStr = typeof cost === "string" ? cost : cost.toString();
    var convValuePerCost = (conversionValue.replace(",", "") / costStr.replace(",", "")).toString();
    if (isNaN(convValuePerCost)) {
      convValuePerCost = 0;
    }

    var isProductType = "";
    if (clicks > 300 / AverageCvr && convValuePerCost >= breakevenRoas + 1) {
      isProductType = "over-index";
    } else if (clicks >= 100 / AverageCvr && convValuePerCost >= breakevenRoas) {
      isProductType = "index";
    } else if (convValuePerCost >= breakevenRoas - 1) {
      isProductType = "near-index";
    } else if (impressions < impressionThreshold) {
      isProductType = "no-index";
    } else {
      isProductType = "under-index";
    }
    products.push([offer_id, impressions, clicks, cost, conversions, conversionValue, convValuePerCost, isProductType]);
    count += 1;
  }
  return products;
}

function pushToSpreadsheet(data) {
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = spreadsheet.getSheetByName("Flowbelizer");
  var lastRow = sheet.getMaxRows();
  sheet.getRange("A2:H" + lastRow).clearContent();
  var start_row = 2;
  var endRow = start_row + data.length - 1;
  var range = sheet.getRange("A" + start_row + ":" + "H" + endRow);
  if (data.length > 0) {
    range.setValues(data);
  }
  return;
}

Next to this shopping performance functions. There are other functions too. If you’d like to know how account level functions work just check out that article.