Shopping Explorer Script

/**
 * 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);
}