Segmentacja produktów w Google Ads poprzez skrypt

// Flowboost Labelizer 1.1 by Floris de Schrijver
// custom requests? floris@flowboost.co

// V1.1 – enter your data as variables

// make copy of the spreadsheet
// add the spreadsheet URL as supplemental feed to Google merchant center (check cel H1 for custom_label_1, label or product type)

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

// ——- updates will be uploaded in the library first
// — v3.1 is live since july 2023 > advanced settings, better segmentation en graphs

// ——- Newsletters to check >
// — PPC Edge newsletter > https://www.ppcmastery.com/blog/

// ——-

// let it run daily, smooth sailing

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

// ——-

// 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 = 4; // set it 10-20% lower than the account target
var AverageCvr = 5; // conversion rate on average in shopping in past daysAgo
var impressionThreshold = 10; // less than 50 means a ‘zombie’ or ‘no-index’
var daysAgo = 60; // 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);
}

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 OfferId, Impressions, Clicks, Ctr, Cost, Conversions, ConversionValue ” +
“FROM SHOPPING_PERFORMANCE_REPORT ” +
“DURING “+ dateFrom +”,”+ 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[‘OfferId’];
var impressions = row[‘Impressions’].toString();
var clicks = row[‘Clicks’].toString();
var cost = row[‘Cost’].toString();
var conversions = row[‘Conversions’].toString();
var conversionValue = row[‘ConversionValue’].toString();
var convValuePerCost = (conversionValue.replace(“,”, “”) / cost.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;
}
Logger.log(count);
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;
}