PPC Leap

Menu

Leap Labelizer – V1.2

Last update: 08/09/2024

Leap Labelizer is a free performance bucketing tool, that help ecommerce businesses allocate their higher portion of ads spent to high potential products that are generating higher percentage of revenue.

It’s more than just a labelizer; it features an insightful dashboard that displays daily performance data for all shopping products, both product-wise and bucket-wise.

Look inside the dashboard: Link

What’s in V1.2
  • Create sup feed for yourself or use merchant centre next template
  • Product item ID’s letter case issue fixed.

Note:

If you have the large number of products listings, you might get an error for crossing Google sheet rows and cells limits.

Performance Labels

It creates 6 performance based buckets.

  • Elites
  • Regulars
  • Climbers
  • Strugglers
  • Ghosts
  • Sleepers

Elites

Products way above the target.

Regulars

Products are meeting the target.

Climbers

Products are behind the target.

Strugglers

Products are way behind the target, aka budget busters.

Ghosts

Products that show up sometimes and have fewer clicks.

Sleepers

Products with zero clicks. 

You don’t need to create a separate campaign for each label. Since elite and regular products will be fewer, combine them into a single campaign.

Combine Ghosts and climbers in a single campaign.

Rest, you know the drill. 

The purpose is to analyze the spending and act accordingly without putting ourselves into panic mode.

You can use the script just for data analysis, without putting labels into campaigns.

Full Customizations

It’s the first free script with extensive customization options. You can tailor the script to meet your specific business goals with the following features.

  • Break Even ROAS
  • Average Conversion Rate
  • Ghost Clicks Threshold
  • Data Time Range in Days
  • Skipping Conversion Lag Data in Days
  • Feed Label
  • Product Type Level
  • Leap Label (Performance Based)
  • Product Type Based Leap Label
  • Clicks Multiplier For Elites

You can set these customizations from the sub sheet called “Configurations”

Break Even ROAS

Give your breakeven ROAS number.

Average Conversion Rate

Give your Average CVR you see in your shopping ads. 

Ghost Clicks Threshold

How many clicks on product you want to include in the Ghosts. We see a lot of products with fewer clicks and good conversion value. But they rarely get a chance to show up.

Default is 20 clicks.

Data Time Range in Days

Give a number to see how many days, you want to look back for a data. 

Default is 30 days.

Skipping Conversion Lag Data in Days

You don’t get a lot of data for recent days, You can skip them by simply putting a number there. 

Default is 3 days. 

Feed Label

Accounts with many countries labels, I’ve got your back. Give a feed label, you want to analyze. A product that worked in the United States might not be good in the United Kingdom. So it’s better to run separately.

Product Type Level

Get performance based bucketing on product type level. We get up to 5 levels of type in it. You can see your performance by any type. Range is from 1 to 5. 

Default is 1.

Product Type Based Leap Label

It’s product type level performance custom label, (shoes Elites, shoes Regulars, shoes Climbers).

Custom Labels range: 1-5.

Default is 1.

Leap Label

It’s the main performance custom label, (Elites, Regulars, Climbers, Strugglers, Ghosts, and sleepers).

Custom Labels range: 1-5.

Default is 2.

Clicks Multiplier For Elites

It’s a threshold to qualify products to be measure for elite label, (3 mean 300, 2 mean 200, 2.5 mean 250).

Default is 3 which mean 300 clicks products will qualify for elites.

It’s using free flowboost labelizer logic for the multiplier.

How it works?

It has 2 sheets.

  • Supplementary Feed
  • Dashboard

Please create supplementary feed for yourself or use Google merchant Next template URL.

Paste that supplementary feed URL in the configuration sheet.

Duplicate this dashboard sheet. Link

Paste the Dashboard link to the script.

Schedule your script for daily, 

Note: Schedule your script time before your sup feed in the merchant centre.

Once it’s scheduled, just monitor the dashboard only. It has everything there. 

How to use the script? 
  • Go to 
  • Google Ads
  • Settings
  • Bulk Actions
  • Scripts
  • New Script
  • Paste the Leap Labelizer script
  • Duplicate the sheet
  • Paste the URL in the script
  • Update your configurations sheet
  • Authorize the script
  • Run the script

Still confused, running the script? Watch this YouTube Video.

Wait for data to show up in the dashboard.

If you like the script, please share in your network, so every PPCer could take a benefit from it.

Script Code:

// Leap Labelizer V1.2 - 08/09/2024

// copy the template https://docs.google.com/spreadsheets/d/1ENC6Jw-cEi54c3n_rhji_AUdezir57AwTv5mYgAiIR0/copy
var SPREADSHEET_URL = "PASTE_YOUR_LINK_HERE"; // Paste the url here

// No updates area --------------------------------------------------------------------------------------
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
const configSheet = spreadsheet.getSheetByName('Configurations');
var feedLabel = configSheet.getRange('B7').getValue();
var productIDCase = configSheet.getRange('B12').getValue(); 
var SUPFEED_URL = configSheet.getRange('B13').getValue(); 
var breakevenRoas = configSheet.getRange('B2').getValue(); 
var AverageCvr = configSheet.getRange('B3').getValue(); 
var ghostThreshold = configSheet.getRange('B4').getValue(); 
var daysAgo = configSheet.getRange('B5').getValue(); 
var numDaysToSkip = configSheet.getRange('B6').getValue();
var productTypeLevel = configSheet.getRange('B8').getValue(); 
var labelFirst = configSheet.getRange('B9').getValue(); 
var labelSecond = configSheet.getRange('B10').getValue(); 
var clicksMultiplier = configSheet.getRange('B11').getValue();

const dateRange = getDateRange(numDaysToSkip, daysAgo);
let segFeedLabel = ' segments.product_feed_label ';
let segItemId = ' segments.product_item_id ';
let metImpressions = ' metrics.impressions ';
let metClicks = ' metrics.clicks ';
let metCtr = ' metrics.ctr ';
let metCostMicros = ' metrics.cost_micros ';
let metConversions = ' metrics.conversions ';
let metConversionsValue = ' metrics.conversions_value ';
let segProductTitle = ' segments.product_title ';
let segProductType = ` segments.product_type_l${productTypeLevel} `;
let segDate = ' segments.date ';
let whereFeedLabel = ` segments.product_feed_label = "${feedLabel}" `;
let whereDate = ` segments.date BETWEEN ${dateRange} `;

// -----------------------------------------------------------------------------------------------------------------

function main(){
 setDetails();
 var products = getShoppingProducts(daysAgo);
 pushToDashboard(products);
 SUPFEED_URL ? pushToSupFeed(products) : Logger.log('Please provide your sup feed URL');
 exportRawData();
}

// -----------------------------------------------------------------------------------------------------------------

function setDetails() {
  var account = AdsApp.currentAccount();
  var accountName = account.getName();
  var dateText = formatDateRange(dateRange);

  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = spreadsheet.getSheetByName('Dashboard');
  
  sheet.getRange('C2').setValue(`${accountName} ${feedLabel}`);
  sheet.getRange('C3').setValue(dateText);
}

// -----------------------------------------------------------------------------------------------------------------




// -----------------------------------------------------------------------------------------------------------------

function getShoppingProducts(daysAgo){
  // Fetching Data for processing
let dataMetrics = [segFeedLabel, segItemId, metImpressions , metClicks , metCtr , metCostMicros , metConversions , metConversionsValue , segProductTitle , segProductType];
var query = 'SELECT ' + dataMetrics.join(',')  + 
      ' FROM shopping_performance_view  ' + 
      ' WHERE ' + whereFeedLabel + ' AND ' +  whereDate; 
              
var products = [];
var count = 0;
var report = AdWordsApp.report(query);
var rows = report.rows();
while (rows.hasNext()){
var row = rows.next();
var itemId = row['segments.product_item_id'];
var productType = row[`segments.product_type_l${productTypeLevel}`];
var title = row['segments.product_title'];
var impressions = row['metrics.impressions'].toString();
var clicks = row['metrics.clicks'].toString();
var cost = row['metrics.cost_micros']/1000000;
var conversions = row['metrics.conversions'].toString();
var conversionValue = row['metrics.conversions_value'];
var convValuePerCost = conversionValue/cost;
  
  
if (isNaN(convValuePerCost)){
 convValuePerCost = 0;
} 
  
var leapLabel = '';
  
if (clicks == 0) {
  leapLabel = "Sleepers"
}else if (clicks <= ghostThreshold) {
  leapLabel = "Ghosts"
} else if (clicks > ( clicksMultiplier * 100 / AverageCvr ) && convValuePerCost >= breakevenRoas + 1){
 leapLabel = 'Elites';
} else if (clicks >= ( 80 / AverageCvr ) && convValuePerCost >= breakevenRoas ){
 leapLabel = 'Regulars';
} else if (convValuePerCost >= breakevenRoas - 1){
 leapLabel = 'Climbers'; 
}else{
  leapLabel = 'Strugglers';
}
  
  
var productTypeLeapLabel = productType + " " + leapLabel;
  
products.push([itemId, impressions, clicks, cost, conversions, conversionValue, convValuePerCost, leapLabel, productType, productTypeLeapLabel, title]);
count+= 1;
}
Logger.log(`All Products: ${count}`);  
var uniqueProducts = prioritizeAndFilterProducts(products);
Logger.log(`Unique Produccts: ${uniqueProducts.length}`);  
return uniqueProducts;
}

// -----------------------------------------------------------------------------------------------------------------

// export all raw data
function exportRawData() {
let dataMetrics = [segFeedLabel, segDate, segItemId, metImpressions , metClicks , metCtr , metCostMicros , metConversions , metConversionsValue , segProductTitle , segProductType];
var query = 'SELECT ' + dataMetrics.join(',')  + 
      ' FROM shopping_performance_view  ' + 
      ' WHERE ' + whereFeedLabel + ' AND ' +  whereDate; 
const report = AdsApp.report(query);
report.exportToSheet(spreadsheet.getSheetByName('r_data'));  
}

// -----------------------------------------------------------------------------------------------------------------

// Pushing Products data to Dashboard sheet
function pushToDashboard(data){
var sheet = spreadsheet.getSheetByName('Leap Data');
sheet.clear();
let header = ['Item Id','Impressions','Clicks','Cost','Conversions','Conv. Value','ROAS',`Custom Label ${labelSecond}`,`Product Type ${productTypeLevel}`,`Custom Label ${labelFirst}`,'Title'];
data.unshift(header);
sheet.getRange(1, 1, data.length, header.length).setValues(data);
sheet.sort(4, false);
}

// -----------------------------------------------------------------------------------------------------------------

// Pushing sup feed data
function pushToSupFeed(data){
var data = data.slice(1);
var spreadsheet = SpreadsheetApp.openByUrl(SUPFEED_URL);
var sheet = spreadsheet.getActiveSheet();
let supFeedValues = data.map(item => [
    formatItemId(item[0]), // Index 0 contains itemId
    item[7], // Index 7 contains label
    item[9] // Index 9 contains label2
]);
  
var supFeedHeader = [`id`, `custom label ${labelSecond}`, `custom label ${labelFirst}`];
supFeedValues.unshift(supFeedHeader);
sheet.clear();
sheet.getRange(1, 1, supFeedValues.length, supFeedHeader.length).setValues(supFeedValues);
}

// -----------------------------------------------------------------------------------------------------------------

// incase products are duplicated, it will deduplicate and pick one with better data.

function prioritizeAndFilterProducts(products) {
    const prioritySequence = ["Elites", "Regulars", "Climbers", "Strugglers", "Ghosts", "Sleepers"];
    const uniqueProducts = {};

    for (const product of products) {
        const key = product[0];
        const priorityIndex = prioritySequence.indexOf(product[7]);

        if (!(key in uniqueProducts) || priorityIndex < prioritySequence.indexOf(uniqueProducts[key][7])) {
            uniqueProducts[key] = product;
        }
    }

    return Object.values(uniqueProducts);
}

// -----------------------------------------------------------------------------------------------------------------

function formatItemId(itemID) {
 if (itemID) {  // Short check for not null/undefined
    if (productIDCase === 'Upper') {
      return itemID.toUpperCase(); // Return itemID in uppercase
    } else if (productIDCase === 'Small') {
      return itemID.toLowerCase(); // Return itemID in lowercase
    } else {
  var parts = itemID.split('_');
  if (parts.length >= 2) {
    var countryCode = parts[1];
    var countryCodeInUpperCase = countryCode.toUpperCase();
    parts[1] = countryCodeInUpperCase;
    return parts.join('_');
  } else {
    // If the itemID doesn't have the expected structure, return the original itemID
    return itemID;
  }
    }
  } else {
    return ''; // Return an empty string if itemID is null or undefined
  }
}

// -----------------------------------------------------------------------------------------------------------------

// function to get date range 
function getDateRange(numDaysToSkip, numDaysToLookBack) {
const endDate = new Date();
endDate.setDate(endDate.getDate() - numDaysToSkip);
const startDate = new Date();
startDate.setDate(startDate.getDate() - numDaysToLookBack - numDaysToSkip + 1);
    
const format = date => Utilities.formatDate(date, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
    
return `${format(startDate)} AND ${format(endDate)}`;
}

// -----------------------------------------------------------------------------------------------------------------

function formatDateRange(dateString) {
var dates = dateString.split(" AND ");
return dates.map(date => date.replace(/(\d{4})(\d{2})(\d{2})/, "$3-$2-$1")).join(" to ");
}
Scroll to Top