Update Google Ads Prijsextensies met MySQL

Voor een side-project schrijf ik een script dat Prijsextensies automatisch aanmaakt, toevoegt en updatet op basis van een databaseverbinding. Het uiteindelijke doel is om nieuwe campagnes automatisch te koppelen met de gegenereerde Prijsextensies.

Voor het eerste deel heb ik gebruikgemaakt van het script van Tibbe van Asten van Adscripts.com. Dit script maakt met Google Merchants Center verbinding voor de actuele prijzen en vergelijkt deze met de bestaande prijzen. Door de recente aanpassingen kun je nu tussen een GMC- of een MySQL verbinding kiezen

Met de directe MySQL verbinding kunnen bijvoorbeeld data bewerkingen nog op de server uitgevoerd worden of zelfs gegevens vanuit een WordPress gebruikt worden. Bovendien zullen de gegevens altijd 100% real-time zijn.

De volgende stap voor het automatisering worden het koppelen van campagnes met de desbetreffende Prijsextensies, en daarnaast nog de mogelijkheid om de volgorde van de items te specificeren.

Het origineel en de instructies zijn terug te vinden op Adsscripts.com. Om het script aan de praat te krijgen is het van belang de variabelen te controleren in de checkPrice(), checkBrandPrice(), en checkCategoryPrice()-functies. Een voorbeeld voor de databasestructuur staat onder het script.

// Copyright 2019. Increase BV. All Rights Reserved.
//
// Created By: Tibbe van Asten
// for Increase B.V.
//
// Edit for MySQL by: Ewald van Kampen
//
// Created 15-08-2019
// Last update: 11-02-2020
//
// ABOUT THE SCRIPT
// This script will check all price-extensions for up-to-date prices
// When prices have changed, the script will update the extension.
//
// Adjustments in script are pre-alpha for a script to update
// Price-extentions automatically in Google Ads from MySQL,
// and subsequently link these with associated campaigns.
////////////////////////////////////////////////////////////////////

var config = {

   LOG : true,

   (https://adsscripts.com/scripts/google-ads-scripts/update-prices-in-textads)
   // Kindly borrowed and adjusted by Ewald van Kampen (ewaldvankampen.nl). 
   // 
   // Connect Merchant Center: Add user to MC that runs this script.
   // Connect to a MySQL Server: Configure details below.
   //
   // Also enable Advanced API 'Shopping content'.
   //

   // Select "MERCHANT" to use data from Google Merchant Center
   // Select "MYSQL" for connecting with a MySQL database.  
   // 
   CONNECT_TO : "MYSQL", 
   
   MERCHANT_ID : "123456789",
   INCLUDE_BRANDS : true,
   INCLUDE_CATEGORIES : false,
   INCLUDE_PRODUCT_TIERS : false,

   DB_SERVER : "",
   DB_USER : "",
   DB_PWD : "",
   DB_NAME : "auto_collectie",
   DB_PORT : 3306,

   DB_QUERY : "SELECT * FROM `overzicht_tweedehands` WHERE `availability` = 'in_stock' ORDER BY `price` ASC",

   // Specify the Produc-category field/column in your Merchant Center or MySQL table
   CATEGORY_FEEDFIELD : "productList", 

   // An email will be sent when price extensions are paused
   // Add multiple emailaddresses by serperating by comma.
   EMAIL_RECIPIENTS : "example@example.com",
   EMAIL_SUBJECT : "Update Price Extensions in " + AdsApp.currentAccount().getName(),
   EMAIL_CONTENT : "The following changes are made in price extensions:<br /><br />"

}
////////////////////////////////////////////////////////////////////

function main() {

   // Connect to either the Merchant Center or MySQL to collect active brands and products   
   switch(config.CONNECT_TO) {
      case "MYSQL" : var products = connectMySQL();
      break;
      case "MERCHANT" : var products = connectMerchant();
      break;
   }

   // Set builders
   var priceItemBuilder = AdsApp.extensions().newPriceItemBuilder();
   var priceBuilder = AdsApp.extensions().newPriceBuilder();
    .extensions()
    .prices()
    .withCondition("Status = ENABLED")
    .withCondition("PolicyApprovalStatus IN ['APPROVED','APPROVED_LIMITED']")
    .get();

   while(priceIterator.hasNext()){
      var price = priceIterator.next();
      var priceItems = price.getPriceItems();

      var numItems = priceItems.length;
                
      // Productprices are checked, when the price extension includes products
      // and config is set to include product tiers
      if(config.INCLUDE_PRODUCT_TIERS === true && !price.getEndDate() && price.getPriceType() == "PRODUCT_TIERS"){
         
         for(var i = 0;i < priceItems.length;i++){
            var priceItem = priceItems[i];
            var currentPrice = checkPrice(products, priceItem.getFinalUrl());

            numItems = checkPriceItem(currentPrice, price, priceItems, priceItem, numItems, priceItemBuilder);

         } // priceItemIterator     

      } // PRODUCT_TIERS

      // Brandprices are checked, when the price extension includes brands
      // and config is set to include brand prices
      if(config.INCLUDE_BRANDS === true && !price.getEndDate() && price.getPriceType() == "BRANDS"){

         for(var i = 0;i < priceItems.length;i++){
            var priceItem = priceItems[i];
            var currentPrice = checkBrandPrice(products, priceItem.getHeader());

            numItems = checkPriceItem(currentPrice, price, priceItems, priceItem, numItems, priceItemBuilder);

         } // priceItemIterator
         
      } // BRANDS
      
      // ProductCategories are checked, when the price extension includes categories
      // and config is set to include product categories
      if(config.INCLUDE_CATEGORIES === true && !price.getEndDate() && price.getPriceType() == "PRODUCT_CATEGORIES"){

         for(var i = 0;i < priceItems.length;i++){
            var priceItem = priceItems[i];
            var currentPrice = checkCategoryPrice(products, priceItem.getHeader());

            numItems = checkPriceItem(currentPrice, price, priceItems, priceItem, numItems, priceItemBuilder);

         } // priceItemIterator
         
      } // PRODUCT_CATEGORIES
      
   } // priceIterator
   
   sendEmail();
   
   Logger.log("Thanks for using this custom script originally written by Tibbe van Asten, updated to sync with MySQL by Ewald van Kampen. Extra Winning!");
   
} // function main

////////////////////////////////////////////////////////////////////

function connectMerchant() {
   
   if(config.MERCHANT == "123456789"){
      throw Error("Change the Merchant ID in the settings");
   }  
   
   var pageToken;
   var pageNum = 1;
   var maxResults = 250;
   var products;
   var products = [];
   
   do {
      var productList = ShoppingContent.Products.list(config.MERCHANT_ID, {
         pageToken: pageToken,
         maxResults: maxResults
      });
      
      if (productList.resources) {
         for (var i = 0; i < productList.resources.length; i++) {
            
            // We'll only check for products that are in stock
            if(productList.resources[i]["availability"] == "in stock"){
               products.push(productList.resources[i]);
            }
         }
      }

      pageToken = productList.nextPageToken;
      pageNum++;

   } while (pageToken);

   return products;

} // function connectMerchant()


////////////////////////////////////////////////////////////////////

function connectMySQL() {

   var connectTo = "jdbc:mysql://" + config.DB_SERVER + ":" + config.DB_PORT + "/" + config.DB_NAME;

   var i, object;
   var products = [];
   var conn = Jdbc.getConnection(connectTo, config.DB_USER, config.DB_PWD);
   var statement = conn.createStatement();
   var results = statement.executeQuery(config.DB_QUERY);        
   var metaData = results.getMetaData();
   var columnCount = metaData.getColumnCount() + 1;

   while (results.next()) {
      object = {};
      for (i = 1; i < columnCount; i++) {
            label = metaData.getColumnLabel(i);
            object[label] = results.getString(i);
      }
      products.push(object);
   }

   results.close();
   statement.close();
   conn.close();

   return products;

}// function connectMySQL()


////////////////////////////////////////////////////////////////////

function checkPrice(products, url){

   for (var i = 1; i < products.length; i++) { 
      
      // When the link in the feed is the same as the price-item url, the price is retrieved
      if(products[i]["link"] == url) { 
      
         // When the saleprice is in the feed, this will be used. If not, the regular price is used.
         if(products[i]["salePrice"]) {
            var price = (config.CONNECT_TO == "MYSQL") ? products[i]["salePrice"] : products[i]["salePrice"]["value"];
         } else {
            var price = (config.CONNECT_TO == "MYSQL") ? products[i]["price"] : products[i]["price"]["value"];
         }
         
         return price;
         
      } // if statement url
      
   } // for statement
   
} // function checkPrice()

////////////////////////////////////////////////////////////////////

function checkBrandPrice(products, brand){
 
   var brandPrice = 1000;
 
   for (var i = 1; i < products.length; i++) { 
    
      // When the brand in the feed is the same as the price-item brand, the price is retrieved
    
      if ((config.CONNECT_TO == "MYSQL") ? (products[i]["beschrijving"] == brand ) : 
            (config.CONNECT_TO == "MERCHANTS") ? ( products[i]["brand"] == brand ) :
            (products[i]["default-brand-column"] == brand ) )  {

         // When the saleprice is in the feed, this will be used. If not, the regular price is used.
         if(products[i]["salePrice"]) {
            var price = (config.CONNECT_TO == "MYSQL") ? products[i]["salePrice"] : products[i]["salePrice"]["value"];
         } else {
            var price = (config.CONNECT_TO == "MYSQL") ? products[i]["price"] : products[i]["price"]["value"];
         }
         
         if(price < brandPrice){ 
            brandPrice = price; 
         }
         
      } // if statement brand
      
   } // for statement

   if(brandPrice != 1000){
      return brandPrice;
   }
   
} // function checkBrandPrice()

////////////////////////////////////////////////////////////////////

function checkCategoryPrice(products, category){
   
   var categoryPrice = 1000;
   
   for (var i = 1; i < products.length; i++) { 
      
      // When the brand in the feed is the same as the price-item productType, the price is retrieved
      if(products[i][config.CATEGORY_FEEDFIELD] == category) { 

      // When the saleprice is in the feed, this will be used. If not, the regular price is used.
         if(products[i]["salePrice"]) {
            var price = (config.CONNECT_TO == "MYSQL") ? products[i]["salePrice"] : products[i]["salePrice"]["value"];
         } else {
            var price = (config.CONNECT_TO == "MYSQL") ? products[i]["price"] : products[i]["price"]["value"];
         }
         
         if(price < categoryPrice){
            categoryPrice = price;
         }
         
      } // if statement brand
      
   } // for statement
   
   if(categoryPrice != 1000){
      return categoryPrice;
   }
   
} // function checkCategoryPrice()

////////////////////////////////////////////////////////////////////

function checkPriceItem(currentPrice, price, priceItems, priceItem, numItems, priceItemBuilder){
   
   // If product is not found, the priceItem will be removed.
   // When price only exists of 3 items, the price will be removed
   if(currentPrice == null && numItems > 3){
      priceItem.remove();
      numItems--;

      // Summary changes in mail
      config.EMAIL_CONTENT += "Removed " + priceItem.getHeader() + " from price #" + price.getId() + "<br />";
   
      if(config.LOG === true){
         Logger.log("Removed " + priceItem.getHeader() + " from price #" + price.getId());
         Logger.log(" ");
      }
               
   } // remove priceItem, not in feed
   else if(currentPrice == null && numItems == 3){

      var date = new Date(Utilities.formatDate(new Date(), "Europe/Amsterdam", "yyyy/MM/dd HH:mm").toString());

      if(price.getEndDate() == null){

      
         price.setEndDate({year: date.getYear(), month: date.getMonth()+1, day: date.getDate()})
         price.setSchedules([{dayOfWeek: "MONDAY", startHour: 0, startMinute: 0, endHour: date.getHours(), endMinute: 0}]);
      
         if(config.LOG === true){
            Logger.log("Ended " + priceItem.getHeader() + " in #" + price.getId());
            Logger.log(" ");
         }
         
         // Summary changes in mail
         config.EMAIL_CONTENT += "Ended " + priceItem.getHeader() + " in #" + price.getId() + "<br />";  
      }    

   } // remove price, not in feed anymore

   // If price from priceItem is not the same as the one in the feed
   // we'll remove it.
   if(priceItem.getAmount() != currentPrice && currentPrice != null){

      Logger.log(priceItem.getHeader() + ". Current: " + priceItem.getAmount() + ", New: " + currentPrice);

      // Add dummy item
      var dummyPriceItemOperation = priceItemBuilder
      .withHeader("Dummy")
      .withDescription("Dummy")
      .withAmount(10)
      .withCurrencyCode(priceItem.getCurrencyCode())
      .withUnitType("NONE")
      .withFinalUrl("http://www.example.com/")
      .build();                        

      // Setting up new priceItem
      var newPriceItemOperation = priceItemBuilder
      .withHeader(priceItem.getHeader())
      .withDescription(priceItem.getDescription())
      .withAmount(parseFloat(currentPrice))
      .withUnitType(priceItem.getUnitType())
      .withCurrencyCode(priceItem.getCurrencyCode())
      .withFinalUrl(priceItem.getFinalUrl())
      .build();

      var dummyPriceItem = dummyPriceItemOperation.getResult();    
      var newPriceItem = newPriceItemOperation.getResult(); 

      // Adding dummyPriceitem and removing original
      price.addPriceItem(dummyPriceItem);
      priceItem.remove();
      price.addPriceItem(newPriceItem);    
      
         if(config.LOG === true){
            Logger.log("Renewed " + priceItem.getHeader());
            Logger.log(" ");
         }

      // Summary changes in mail
      config.EMAIL_CONTENT += "Renewed " + priceItem.getHeader() + "<br />";

   } // Replace item with wrong price

   // Remove dummy priceItem
   var priceItems = price.getPriceItems()
   for(var y = 0;y < priceItems.length;y++){
      var priceItem = priceItems[y];
      if(priceItem.getHeader() == "Dummy"){
         priceItem.remove();
      }     
   } // Remove dummy
   
   return numItems;
   
} // function checkPriceItem()
////////////////////////////////////////////////////////////////////

function sendEmail() {
   
   if(config.EMAIL_CONTENT != "The following changes are made in price extensions:<br /><br />"){
      MailApp.sendEmail({
         to: config.EMAIL_RECIPIENTS,
         subject: config.EMAIL_SUBJECT,
         htmlBody: config.EMAIL_CONTENT});

      Logger.log(config.EMAIL_CONTENT);
   }
   
} // function sendEmail()
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Database: `auto_collectie`
--

-- --------------------------------------------------------

--
-- Tabelstructuur voor tabel `overzicht_tweedehands`
--

CREATE TABLE `overzicht_tweedehands` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `land` text CHARACTER SET utf8 NOT NULL,
  `provincie` text CHARACTER SET utf8 NOT NULL,
  `dealer_naam` text CHARACTER SET utf8 NOT NULL,
  `auto_id` varchar(10) NOT NULL,
  `brand` text CHARACTER SET utf8 NOT NULL,
  `beschrijving` text CHARACTER SET utf8 NOT NULL,
  `auto_uitvoering` text CHARACTER SET utf8 NOT NULL,
  `auto_km` text CHARACTER SET utf8 NOT NULL,
  `price` text CHARACTER SET utf8 NOT NULL,
  `link` text CHARACTER SET utf8 NOT NULL,
  `availability` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Gegevens worden geëxporteerd voor tabel `overzicht_tweedehands`
--

INSERT INTO `overzicht_tweedehands` (`id`, `land`, `provincie`, `dealer_naam`, `auto_id`, `brand`, `beschrijving`, `auto_uitvoering`, `auto_km`, `price`, `link`, `availability`) VALUES
(1, 'NL', 'Groningen', 'Voorbeeld Dealer', 'bmw_3_1', 'BMW', 'BMW 318i', '318i', '318.000', '318.00', 'https://voorbeelddealer.nl/gebruikt/bmw/3-serie/318i.php', 'in_stock'),
(2, 'NL', 'Groningen', 'Voorbeeld Dealer', 'bmw_3_2', 'BMW', 'BMW 320i', '320i', '320.000', '320.00', 'https://voorbeelddealer.nl/gebruikt/bmw/3-serie/320i.php', 'in_stock'),
(3, 'NL', 'Groningen', 'Voorbeeld Dealer', 'bmw_3_3', 'BMW', 'BMW 330d', '330d', '330.000', '330.00', 'https://voorbeelddealer.nl/gebruikt/bmw/3-serie/330d.php', 'in_stock'),
(4, 'NL', 'Groningen', 'Voorbeeld Dealer', 'bmw_3_4', 'BMW', 'BMW 335i', '335i', '335.000', '335.00', 'https://voorbeelddealer.nl/gebruikt/bmw/3-serie/335d.php', 'in_stock'),
(5, 'NL', 'Groningen', 'Voorbeeld Dealer', 'merc_c_1', 'Mercedes', 'Mercedes C 200', 'C 200', '200.000', '200.00', 'https://voorbeelddealer.nl/gebruikt/mercedes/c-klasse/c200.php', 'in_stock'),
(6, 'NL', 'Groningen', 'Voorbeeld Dealer', 'merc_c_2', 'Mercedes', 'Mercedes C 220', 'C 220', '220.000', '220.00', 'https://voorbeelddealer.nl/gebruikt/mercedes/c-klasse/c220.php', 'in_stock'),
(7, 'NL', 'Groningen', 'Voorbeeld Dealer', 'merc_c_3', 'Mercedes', 'Mercedes C 300', 'C 300', '330.000', '300.00', 'https://voorbeelddealer.nl/gebruikt/mercedes/c-klasse/c300.php', 'in_stock'),
(8, 'NL', 'Groningen', 'Voorbeeld Dealer', 'merc_c_4', 'Mercedes', 'Mercedes C 320', 'C 320', '335.000', '320.00', 'https://voorbeelddealer.nl/gebruikt/mercedes/c-klasse/c320.php', 'in_stock'),
(9, 'NL', 'Groningen', 'Voorbeeld Dealer', 'bmw_3_5', 'BMW', 'BMW M3', 'M3', '333.000', '333.00', 'https://voorbeelddealer.nl/gebruikt/bmw/3-serie/m3.php', 'in_stock');

--
-- Indexen voor geëxporteerde tabellen
--

--
-- Indexen voor tabel `overzicht_tweedehands`
--
ALTER TABLE `overzicht_tweedehands`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT voor geëxporteerde tabellen
--

--
-- AUTO_INCREMENT voor een tabel `overzicht_tweedehands`
--
ALTER TABLE `overzicht_tweedehands`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
COMMIT;

Leave a Reply