Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
165 lines (132 sloc) 5.72 KB
title categories
Google Shopping products import into Adobe Campaign
google
opensource
adobe campaign

Create a custom table to store products from Google Shopping to target recipient based on shopping behavior and send marketing communications with Product data such as Image, Title and Price!

💲📥🌍

Schema, form and navigation creation

Data Schema grl:productExtensions

Schema with basic fields (string, memo [can contain XML/HTML], long)

<srcSchema entitySchema="xtk:srcSchema" img="xtk:schema.png" label="Product Extensions"
           mappingType="sql" name="productExtension" namespace="grl" xtkschema="xtk:srcSchema">
  <element autopk="true" label="Product Extensions" labelSingular="Product Extension" name="productExtension">
    <!-- Key -->
    <key>
      <keyfield xpath="@code"/>
    </key>
    <!-- Codes -->
    <attribute label="Code" name="code" type="string"/>
    <!-- Fields -->
    <attribute label="Title" name="title" type="string"/>
    <attribute label="Description" name="description" type="memo"/>
    <attribute label="Product Page URL" length="500" name="productPageUrl" type="string"/>
    <attribute label="Product Image URL" length="500" name="imageUrl" type="string"/>
    <attribute label="Availability" name="availability" type="string"/>
    <attribute label="Price" length="10" name="price" type="string"/>
    <attribute label="Product Category (Google)" name="googleCategory" type="string"/>
    <attribute label="Product type" name="type" type="string"/>
    <attribute label="Brand" name="brand" type="string"/>
    <attribute label="GTIN" name="gtin" type="string"/>
    <attribute label="MPN" name="mpn" type="string"/>
    <!-- Technical -->
    <element aggregate="xtk:common:auditTrail" name="auditTrail"/>
  </element>
</srcSchema>

Input form grl:productExtensions

To create and edit records.

<form entitySchema="xtk:form" img="xtk:form.png" label="productExtension" name="productExtension"
      namespace="grl" xtkschema="xtk:form">
  <container colcount="2">
    <container>
      <input xpath="@code"/>
      <input xpath="@title"/>
      <input xpath="@gtin"/>
      <input xpath="@mpn"/>
      <input xpath="@price"/>
      <input xpath="@productPageUrl"/>
      <input xpath="@googleCategory"/>
      <input xpath="@type"/>
      <input xpath="@description"/>
      <input xpath="@created"/>
      <input xpath="@lastModified"/>
    </container>
    <container label="Preview">
      <input forceNavigate="true" type="urlViewer" urlExpr="@imageUrl"/>
    </container>
  </container>
</form>

Navigation Hierarchy grl:core

To add a folder in the Navigation Tree.

<navtree entitySchema="xtk:navtree" img="nl:folders.png" label="core" name="core"
         namespace="grl" xtkschema="xtk:navtree">
  <model name="root">
    <model label="Custom" name="custom">
      <nodeModel img="nms:webformmodel.png" name="refProductExtension">
        <view img="nms:webformmodel.png" label="Product Extensions" name="listdet" schema="grl:productExtension" type="listdet">
          <columns>
            <node xpath="@code"/>
            <node xpath="@title"/>
            <node xpath="@gtin"/>
            <node xpath="@mpn"/>
            <node xpath="@created"/>
          </columns>
        </view>
      </nodeModel>

    </model>
  </model>
</navtree>

Recap

Update SQL database with Tools > Advanced > Update database structure.

Disconnect, Reconnect. No need to clear the cache.

Create a new "Products" folder: todo

Import products from XML Web Feed

Test with Sample XML from https://raw.githubusercontent.com/darkslategrey/cd/master/fr_eur_googlebase.xml.

  1. Download XML to local ACC [Web Download activity]
  2. Init empty query for workflow [Query activity]
  3. Add columns corresponding to XML fields [Enrichment activity]
  4. Fill in columns from XML data with JavaScript DOMDocument standard lib [JavaScript activity]
  5. Keep only valid records (add any business rules / filtering) [Split activity]
  6. Update/insert into ACC [Update Data activity]

Overview:

todo

Details:

  1. Set URL to https://raw.githubusercontent.com/darkslategrey/cd/master/fr_eur_googlebase.xml
  2. Set schema to grl:productExtensions and condition to @id > 0
  3. Add 2 custom string fields @code and @title with expression ''

todo

  1. Set JS code to
var xmlDoc = DOMDocument.load(vars.filename);
var entries = xmlDoc.getElementsByTagName('item');
for each(var entry in entries){
  var sql = "INSERT INTO "+vars.tableName+" (iId, sCode, sTitle) "+
    "VALUES ($(id), $(sz), $(sz))";
  var recCount = sqlExec(sql, 
    entry.getValue('g:id'),
    entry.getValue('title')
  );
}
  1. Split condition should be @id is not empty, or any other rule you define
  2. Update with Record identification on Using reconciliation keys > @code = @code

Start and check data: todo

Bug Split shows 40 but Display target is empty

todo

Remove "Targeting dimension" from the Output Columns (last field in the list): todo

Going further

  • Combine Query and Enrichment into 1 Query with Additional Data
  • Trigger different behaviour based on availability (in/out of stock) with Alert
  • Add all fields
  • Convert prices to float

Download workflow full version.

You can’t perform that action at this time.