Shopping Explorer Script

Script zur Erforschung erfolgreicher Google Shopping Suchanfragen

Shopping Explorer Script illustration

Finden Sie die verborgenen Schätze

Das “Shopping Explorer” Script filtert nicht nur Ihre Google Shopping Suchanfragen nach Zielländern sowie ROAS und Conversion-Kriterien eines beliebigen Zeitraumes, sondern beleuchtet auch die Produkte (Titel und URLs) die dank dieser Anfragen konvertiert sind. Sie können, zum Beispiel, nur Suchanfragen mit einer gewissen Anzahl an Conversions oder einem bestimmten ROAS betrachten. Die Voraussetzung: produktgenaue Kampagnen. Das Ergebnis kann als Spreadsheet exportiert und als Quelle für andere Scripts — z.B. das “Text Ad Builder” oder “Sitelink Builder” Script verwendet werden.

Gratis AdWords-Script herunterladen

Unverbindlich Potenziale entfesseln

Lesen Sie unsere Datenschutzrichtlinien.

Success image

Vielen Dank!

Nach unten scrollen und Script Code erhalten

Arrow down icon
/**
 * 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.0
 * Changelog:
 * - version 1.0
 *  - Released initial version.
 * Updated: 12-04-2018
 *
 * 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==", "");

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

Der Code wurde erfolgreich in deine Zwischenablage kopiert.

Beliebte AdWords Scripts

Martin Röttgerding

“Automation can take your PPC efforts to the next level – if you’re doing it right.”

Martin Röttgerding, Head of SEA – Bloofusion Germany

Welche verborgenen Schätze stecken in Ihrem Account?

Finden Sie es heraus.