The “Shopping Explorer” script filters Google Shopping queries by target country, ROAS and conversion criteria. You might, for example, only want to consider queries that have already generated a certain ROAS or number of conversions during the last 30 days. Key prerequisite: item-specific campaigns. The output is then saved into a spreadsheet and can be used as a source for other scripts — like our text ad and sitelink builder scripts for Google Ads.
No commitments & no hidden costs — Just a great script
You can get the script code below
/**
* Shopping Explorer Script . * ・ 。゚☆━੧[ ✪ ᗜ ✪ ]⊃
*
* Overview: This exploration script filters Google Shopping campaigns based on
* ROAS and conversion criteria. For example, you might consider only campaigns
* that have already generated x conversions above y ROAS in a given time period.
* The output is saved into a spreadsheet.
*
* Author: smec [smarter-ecommerce.com]
*
* Version: 1.01
* Changelog:
* - version 1.00
* - Released initial version.
* - version 1.01
* - Remove thousands separator for big conversion values
* Updated: 13-01-2020
*
* Twitter: @smec
* Email: [email protected]
* (c) Smarter Ecommerce GmbH. All rights reserved.
* License: MIT
*/
/** ============================== Attention ===================================
* This script writes data into the spreadsheet even when run in preview mode.
*/
/** ============================ Prerequisites =================================
* You need to enable the Content API for Shopping (ShoppingContent).
* This is done via Advanced APIs. Additonally, you must enable the API for the
* script via the Google Developers Console.
* https://developers.google.com/adwords/scripts/docs/features/advanced-apis
*/
/** ============================ Configuration ============================== */
// Google spreadsheet to save item data needed for text ads or sitelinks creation.
var SPREADSHEET_ID = "YOUR_SPREADSHEET_ID";
// Choose (Shopping) campaigns you want to create ads or sitelinks from.
var CAMPAIGN_IDS = "[YOUR_CAMPAIGN_ID_1, YOUR_CAMPAIGN_ID_2, YOUR_CAMPAIGN_ID_N]";
// Merchant ID - The ID of the account that contains the product.
var MERCHANT_ID = "YOUR_MERCHANT_ID";
/**
* MC Product ID configuration:
* The script makes use of SEARCH_QUERY_PERFORMANCE_REPORT, which returns KPIs on
* the product level (=offerId). Products in the MC can only be accessed via
* productId (channel:targetLanguage:targetCountry:offerId).
* In the configuration below we set CHANNEL, TARGET_LANGUAGE and TARGET_COUNTRY.
*/
var CHANNEL = "online";
var TARGET_COUNTRY = "AT";
var TARGET_LANGUAGE = "de";
/* ============================== Preferences =============================== */
// These variables define your preferred thresholds.
var MIN_ROAS = 3;
var MIN_NUMBER_OF_CONVERSIONS = 0.1;
var TIME_RANGE = "LAST_30_DAYS";
/* =============================== Execution ================================ */
function main() {
var QUERY_PERFORMANCE_REPORT =
'SELECT KeywordTextMatchingQuery, Query, Cost, ConversionValue, ' +
'Conversions, AdGroupName, CampaignName ' +
'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
'WHERE KeywordTextMatchingQuery CONTAINS "id="' +
' AND CampaignId IN ' + CAMPAIGN_IDS +
' AND Conversions > ' + MIN_NUMBER_OF_CONVERSIONS + " " +
'DURING ' + TIME_RANGE;
var spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
var queryPerformanceReport = AdWordsApp.report(QUERY_PERFORMANCE_REPORT);
var data = spreadsheet.getDataRange().getValues();
var existingItems = [];
for (var i = 1; i < data.length; i++) {
existingItems.push(data[i][2]);
}
initializeHeaderRow(queryPerformanceReport, spreadsheet, existingItems);
updateSpreadsheet(queryPerformanceReport, spreadsheet, existingItems);
}
/**
* Initialize the header row of the spreadsheet with the corresponding headings.
*/
function initializeHeaderRow(report, spreadsheet, existingItems) {
var headers = getReportColumnHeadersFor(
report, "CampaignName", "AdGroupName", "KeywordTextMatchingQuery",
"Query", "Conversions", "Cost", "ConversionValue");
if (existingItems.length === 0){
headers.push("ROAS");
headers.push("title");
headers.push("price");
headers.push("url");
headers.push("CpC");
spreadsheet.appendRow(headers);
}
}
/**
* Append all rows matching the ROAS Criteria to the provided spreadsheet.
* Delete items that are not in the MC anymore.
*/
function updateSpreadsheet(report, spreadsheet, existingItems) {
var rows = report.rows();
var activeItems = [];
while (rows.hasNext()) {
var row = rows.next();
var itemId = row["KeywordTextMatchingQuery"].replace("id==", "");
row["ConversionValue"] = row["ConversionValue"].replace(",",""); //Remove thousands separator
activeItems.push(itemId);
if (!arrayContains(itemId, existingItems)) {
if (row["Cost"] > 0 && row["ConversionValue"] > 0) {
var roas = row["ConversionValue"] / row["Cost"];
if (roas >= MIN_ROAS) {
var dataRow = getReportValuesFor(row, "CampaignName", "AdGroupName",
"KeywordTextMatchingQuery", "Query", "Conversions", "Cost",
"ConversionValue");
dataRow.push(roas);
appendMerchantCenterData(MERCHANT_ID, dataRow, itemId);
spreadsheet.appendRow(dataRow);
existingItems.push(row["KeywordTextMatchingQuery"]);
}
}
}
}
deleteNonActiveItems(spreadsheet, activeItems);
}
/**
* Get MC data (title, price and link) from MC using the Content API for Shopping.
* Append the obtained data to the corresponding data row object for writing
* into the spreadsheet in the last step.
*/
function appendMerchantCenterData (merchantCenterId, row, offerId) {
var itemId = CHANNEL + ":" + TARGET_LANGUAGE + ":" + TARGET_COUNTRY + ":" + offerId;
try {
var item = ShoppingContent.Products.get(merchantCenterId, itemId);
var price = item.price["value"];
var salePrice = price;
try {
salePrice = item.salePrice["value"];
}
catch (err){
Logger.log(err);
}
if (price > salePrice) {
price = salePrice;
}
row.push(item.title);
row.push(price);
row.push(item.link);
}
catch (err){
/**
* The lookback in the PERFORMANCE_QUERY_REPORT is set for a certain period
* of time when the item existed. This may result in output for an item that
* is no longer available in the MC.
*/
Logger.log(itemId + " not found" + " | " + "original Message: " + err.message);
row.push("item not available in MC");
row.push("item not available in MC");
row.push("item not available in MC");
}
}
/**
* Deletes inactive items from the spreadsheet.
* Note: Loops in reversed order to maintain the proper indexes if more than one
* row is deleted.
*/
function deleteNonActiveItems(spreadsheet, activeItems) {
var dataSnapshot = spreadsheet.getDataRange().getValues();
for (var i = dataSnapshot.length-1; i > 0; i--) {
if(!arrayContains(dataSnapshot[i][2], activeItems)) {
Logger.log("deletedItem: " + dataSnapshot[i][2]);
spreadsheet.deleteRow(i+1);
}
}
}
function getReportColumnHeadersFor(r) {
var headers = [];
for (var i = 1; i < arguments.length; i++) {
headers.push(r.getColumnHeader(arguments[i]).getReportColumnName());
}
return headers;
}
function getReportValuesFor(row) {
var result = [];
for (var i = 1; i < arguments.length; i++) {
if (arguments[i] != "KeywordTextMatchingQuery") {
result.push(row[arguments[i]]);
}
else {
result.push(row[arguments[i]].replace("id==", ""));
}
}
return result;
}
function arrayContains(needle, arrhaystack) {
return (arrhaystack.indexOf(needle) > -1);
}
The code was successfully copied to your Clipboard.
Martin Röttgerding, Head of SEA – Bloofusion Germany