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