PPC Leap

Menu

Leap Labelizer – V1.4

Last update: 14/03/2025

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.

What’s in V1.4
  • Comparison table of current and previous buckets

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.

How it works?

It has 2 sheets.

  • Supplementary Feed
  • Dashboard

Please create supplementary feed for yourself or use Google merchant Next template URL, if you don’t have any sup feed before Or Use your current supplementary feed URL.

Paste that supplementary feed URL in the configuration sheet B12 cell.

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. 

Before you run the script, set up your configuration sheet. Especially Feed Label.

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

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.

If you want to see inside view dashboard template? Watch here

Script Code:

// Leap Labelizer V1.4 - 14 March 2025

// copy this template https://docs.google.com/spreadsheets/d/1XhMVqkRF4KC_PJYo94TDqs2g816hDnWJRffQ2DO0d2M/copy
var SPREADSHEET_URL = "PASTE_YOUR_SHEET_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('B11').getValue(); 
var SUPFEED_URL = configSheet.getRange('B12').getValue(); 
var breakevenRoas = configSheet.getRange('B2').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(); 

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 segLabelFirst = ` segments.product_custom_attribute${labelFirst} `;
let segLabelSecond = ` segments.product_custom_attribute${labelSecond} `;
let segDate = ' segments.date ';
let campName = ' campaign.name ';
let whereFeedLabel = ` segments.product_feed_label = "${feedLabel}" `;
let whereDate = ` segments.date BETWEEN ${dateRange} `;

// New configuration variables
var excludeBrand = configSheet.getRange('B3').getValue(); // true/false
var excludeSale = configSheet.getRange('B13').getValue();  // true/false
var saleStartDate = formatYYYYMMDD(configSheet.getRange('C14').getValue()); //"20241224";  Format: YYYYMMDD
var saleEndDate = formatYYYYMMDD(configSheet.getRange('E14').getValue()); //"20241228";  Format: YYYYMMDD


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

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

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 isBrandCampaign(campaignName) {
  return campaignName.toLowerCase().includes('brand');
}

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

function isWithinSalePeriod(date) {
  //let dateClean = date.replace(/-/g, '');
  //return date >= saleStartDate && date <= saleEndDate;
  
   // Ensure the date is stripped of dashes and converted to an integer
  let cleanDate = parseInt(date.replace(/-/g, ''), 10);
  let saleStart = parseInt(saleStartDate, 10); // Ensure numeric comparison
  let saleEnd = parseInt(saleEndDate, 10);     // Ensure numeric comparison

  // Perform numeric comparison for accuracy
  return cleanDate >= saleStart && cleanDate <= saleEnd;
  
}

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

function exportRawData(products) {
  Logger.log("Exporting overtime data now");
  
  let dataMetrics = [
    segFeedLabel, segDate, segItemId, metImpressions, metClicks, metCtr,
    metCostMicros, metConversions, metConversionsValue, segProductTitle,
    segProductType, campName
  ];
  
  let query = 'SELECT ' + dataMetrics.join(',') + 
              ' FROM shopping_performance_view ' + 
              ' WHERE ' + whereFeedLabel + 
              ' AND ' + whereDate;

  const report = AdsApp.report(query);
  const rows = report.rows();
  let processedData = []; // Array to store processed rows

  while (rows.hasNext()) {
    let row = rows.next();
    let itemId = row['segments.product_item_id'];
    let date = row['segments.date'];
    let campaignName = row['campaign.name'];

    // Skip if conditions match exclusion criteria
    if (excludeBrand && isBrandCampaign(campaignName)) continue;
    if (excludeSale && isWithinSalePeriod(date)) continue;

    

    // Get leapLabel from the product data
    let product = products.find(p => p[0] === itemId); // Find the product by itemId
    let leapLabel = product ? product[7] : 'N/A'; 
  

    // Process and store filtered data in array
    let rowData = [
      row['segments.product_feed_label'],
      row['segments.date'],
      row['segments.product_item_id'],
      row['metrics.impressions'],
      row['metrics.clicks'],
      row['metrics.ctr'],
      row['metrics.cost_micros'],
      row['metrics.conversions'],
      row['metrics.conversions_value'],
      row['segments.product_title'],
      row[`segments.product_type_l${productTypeLevel}`],
      row['campaign.name'],
      leapLabel // Include leapLabel
    ];
    processedData.push(rowData);
  }

  // Add header row
  const headerRow = [
    'segments.product_feed_label', 'segments.date', 'segments.product_item_id',
    'metrics.impressions', 'metrics.clicks', 'metrics.ctr', 'metrics.cost_micros',
    'metrics.conversions', 'metrics.conversions_value', 'segments.product_title',
    `segments.product_type_l${productTypeLevel}`, 'campaign.name', 'leapLabel'
  ];
  processedData.unshift(headerRow); // Add header at the start of the array
  Logger.log(`Total rows to be exported: ${processedData.length}`);
  
  // Export to spreadsheet
  const sheet = spreadsheet.getSheetByName('r_data');
  sheet.clearContents(); // Clear existing data
  sheet.getRange(1, 1, processedData.length, processedData[0].length).setValues(processedData);
  Logger.log("All rows exported.");
}



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


function aggregateMetrics(rows) {
  let productMetrics = {};
  
  while (rows.hasNext()) {
    let row = rows.next();
    let itemId = row['segments.product_item_id'];
    let date = row['segments.date'];
    let campaignName = row['campaign.name'];
    
    // Skip if conditions match exclusion criteria
    if (excludeBrand && isBrandCampaign(campaignName)) continue;
    if (excludeSale && isWithinSalePeriod(date)) continue;
    
    if (!productMetrics[itemId]) {
      productMetrics[itemId] = {
        itemId: itemId,
        productType: row[`segments.product_type_l${productTypeLevel}`],
        title: row['segments.product_title'],
        labelFirst: row[`segments.product_custom_attribute${labelFirst}`],
        labelSecond: row[`segments.product_custom_attribute${labelSecond}`],
        impressions: 0,
        clicks: 0,
        cost: 0,
        conversions: 0,
        conversionValue: 0
      };
    }
    
    // Aggregate metrics
    productMetrics[itemId].impressions += Number(row['metrics.impressions']);
    productMetrics[itemId].clicks += Number(row['metrics.clicks']);
    productMetrics[itemId].cost += Number(row['metrics.cost_micros'])/1000000;
    productMetrics[itemId].conversions += Number(row['metrics.conversions']);
    productMetrics[itemId].conversionValue += Number(row['metrics.conversions_value']);
  }
  
  return Object.values(productMetrics);
}

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

function getShoppingProducts(daysAgo){
  // Fetching Data for processing
  let dataMetrics = [segFeedLabel, segItemId, metImpressions, metClicks, metCtr, metCostMicros, 
                     metConversions, metConversionsValue, segProductTitle, segProductType, segDate, campName, segLabelFirst, segLabelSecond];
  var query = 'SELECT ' + dataMetrics.join(',')  + 
        ' FROM shopping_performance_view  ' + 
        ' WHERE ' + whereFeedLabel + ' AND ' +  whereDate; 
                
  var products = [];
  var report = AdWordsApp.report(query);
  var rows = report.rows();
  // Aggregate metrics after filtering
  var aggregatedProducts = aggregateMetrics(rows);
  
  // Process aggregated products and apply labels
  for (let product of aggregatedProducts) {
    var roas = product.conversionValue/product.cost;
    
    if (isNaN(roas)) {
      roas = 0;
    } 
    
    var leapLabel = '';
    
    if (product.clicks == 0) {
      leapLabel = "Sleepers";
    } else if (product.clicks <= ghostThreshold) {
      leapLabel = "Ghosts";
    } else if (roas >= breakevenRoas + 1) {
      leapLabel = 'Elites';
    } else if (roas >= breakevenRoas) {
      leapLabel = 'Regulars';
    } else if (roas >= breakevenRoas - 1) {
      leapLabel = 'Climbers'; 
    } else {
      leapLabel = 'Strugglers';
    }
    
    var productTypeLeapLabel = product.productType + " " + leapLabel;
    
    products.push([
      product.itemId, 
      product.impressions.toString(), 
      product.clicks.toString(), 
      product.cost, 
      product.conversions.toString(), 
      product.conversionValue, 
      roas, 
      leapLabel, 
      product.labelSecond,
      productTypeLeapLabel,
      product.labelFirst,
      product.productType, 
      product.title,
    ]);
  }
  
  return products;
}

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

// 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}`, `Prev Custom Label ${labelSecond}` ,`Custom Label ${labelFirst}`, `Prev Custom Label ${labelFirst}` ,`Product Type ${productTypeLevel}`,'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);
}


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

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 ");
}

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

function formatYYYYMMDD(date) {
if (!date) return null;
  
  // If date is already a string in YYYYMMDD format, return as is
  if (typeof date === 'string' && date.match(/^\d{8}$/)) {
    return date;
  }
  
  // Convert to Date object if it's not already
  const dateObj = (date instanceof Date) ? date : new Date(date);
  
  // Use Utilities.formatDate with our spreadsheet's timezone
  const formattedDate = Utilities.formatDate(dateObj, spreadsheet.getSpreadsheetTimeZone(), 'yyyyMMdd');
  
  return formattedDate;
}


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

// 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)}`;
}
Scroll to Top