PPC Leap

Menu

Leap Labelizer Without Overtime Charts Data – V1.5

Last update: 16/04/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.

What’s Without Overtime Charts Data?

In the main Leap Labelizer, you can see your labels, product types and products daily overtime performance data. But if you have more products or going into higher date ranges which results in more data, you can experience “Exceeded maximum execution time”.

Because Google Ads takes a lot of time to export that data to Spreadsheet, the only option left now is running the script without overtime data, and it will take less time to run successfully.

What’s in V1.5
  • Feed label is now optional in configuration sheet.
  • If you don’t have a feed label in merchant centre, skip the option.

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 also 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.

Script Code:

// Leap Labelizer Without Overtime Charts Data V1.5 - 16 APR 2025

// copy this template https://docs.google.com/spreadsheets/d/1vLJ8IQZzBSpJ0w9lrv7SJQuZqSLB4N8tRY-m5kaIazs/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 breakevenRoas = configSheet.getRange('B2').getValue(); 
var excludeBrand = configSheet.getRange('B3').getValue(); // true/false
var ghostThreshold = configSheet.getRange('B4').getValue(); 
var daysAgo = configSheet.getRange('B5').getValue(); 
var numDaysToSkip = configSheet.getRange('B6').getValue();
var isFeedLabel = configSheet.getRange('B7').getValue();
var feedLabel = configSheet.getRange('B8').getValue();
var productTypeLevel = configSheet.getRange('B9').getValue(); 
var labelFirst = configSheet.getRange('B10').getValue(); 
var labelSecond = configSheet.getRange('B11').getValue(); 
var productIDCase = configSheet.getRange('B12').getValue(); 
var SUPFEED_URL = configSheet.getRange('B13').getValue(); 
var excludeSale = configSheet.getRange('B14').getValue();  // true/false
var saleStartDate = formatYYYYMMDD(configSheet.getRange('C15').getValue()); //"20241224";  Format: YYYYMMDD
var saleEndDate = formatYYYYMMDD(configSheet.getRange('E15').getValue()); //"20241228";  Format: YYYYMMDD


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} `;



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

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

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 buildQuery(dataMetrics) {
  let baseQuery = 'SELECT ' + dataMetrics.join(',') + ' FROM shopping_performance_view';
  let conditions = [];

  if (isFeedLabel) {
    conditions.push(whereFeedLabel);
  }

  conditions.push(whereDate); // always included

  return baseQuery + ' WHERE ' + conditions.join(' AND ');
}

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


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 = buildQuery(dataMetrics);
                
  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