Unified Product Catalog table

The Unified Product Catalog table contains a row for every item in your product catalog, including descriptive attributes, such as name, brand, size, and color, grouping attributes, such as category, sub-category, class, and sub-class, and other attributes like MSRP, gender, and description.

Important

The Unified Product Catalog table represents the taxonomy for your products and brands. Attributes are added to the Unified Product Catalog table when pc/ semantic tags are applied to your data sources. All pc/ semantic tags are optional. Use the ones that best define the shape of your product catalog and best describe the individual items within it. The product ID is used as an input to AmpIQ predictive modeling.

Add table

The Unified Product Catalog table is a required table for the customer 360 database when transactions and itemized transaction data is available to your tenant and fields are tagged with pc/ semantic tags. The Unified Product Catalog table is built as a passthrough table.

To add the Unified Product Catalog table

1. From the Customer 360 page, under All Databases, select the menu for the customer 360 database, and then click Edit.

2. From the Database Editor, click Add Table.

3. Name the table “Unified_Product_Catalog”.

4. Set the build mode to Passthrough.

5. From the Source Table drop-down, select the Unified Product Catalog table.

6. Click Next.

7. Add a table description. This enables a tooltip that is visible from other areas in Amperity.

8. Verify that semantic tags were applied correctly.

   Tip

   You can clear incorrectly tagged semantics individually or for the entire table. To clear semantic tags for the entire table, under Semantic Suggestions click Clear semantics.

9. From the Table Semantics drop-down, select Unified Product Catalog.

10. Click Save.

Define product catalog

A product catalog must be defined, and then joined to the Unified Itemized Transactions table.

1. A data source must be available with product catalog semantic tags applied.

   Note

   Amperity documentation refers to this data source as the Unified_Product_Catalog table, but it may have a different name in your tenant.

2. Apply the product-id semantic tag to product catalog data as required for AmpIQ predictive modeling; the product-category, product-description, and product-subcategory semantic tags are optional.

   Important

   Product affinity modeling requires the field that defines product categories – product-category in the Unified_Product_Catalog table – to contain between 20 and 2000 unique values.

3. A list of custom attributes may be defined. These must be available as columns in the Customer 360 database and must be added to the Unified Itemized Transactions table.

4. Tables that define product catalog semantic tags and custom attributes are managed as a data source (and associated feed) and should be maintained as part of your organization’s regular Amperity workflow.

Extend the Unified Itemized Transactions to include the taxonomy that defines your products and brands:

1. Add the column to which the product-id semantic tag was applied

2. Add other columns to which product catalog semantic tags were applied

3. Add any columns that define custom attributes, as necessary

The following example extends the Unified Itemized Transactions table using a table named Unified Product Catalog that has columns for each of the optional product catalog semantic tags:

SELECT
  uit.*
  ,upc.product_category AS `product_category`
  ,upc.product_description AS `product_description`
  ,upc.product_subcategory AS `product_subcategory`
FROM Unified_Itemized_Transactions uit
LEFT JOIN Unified_Product_Catalog upc ON uit.product_id = upc.product_id

Some brands require a single parameter to define a taxonomy and some require more than one. The following example shows a taxonomy that defines custom attributes for a class and subclass:

SELECT
  uit.*,
  ,product_class AS `product_class`
  ,product_subclass AS `product_subclass`
FROM Unified_Itemized_Transactions uit
LEFT JOIN Product_Metadata pm ON uit.product_id = pm.product_id
WHERE uit.is_return IS NULL AND uit.is_cancellation IS NULL
AND uit.item_revenue >= 0

Important

Every brand is unique. Discuss with your Amperity representative how to extend the Unified_Itemized_Transactions table to support the taxonomy that is required for your products and brands.

Column reference

The Unified Product Catalog table contains the following columns:

Column Name	Data type	Description
Datasource	String	The name of the data source from which this customer record originated. Tip The combination of PK and Datasource uniquely identifies a row in the Unified Coalesced table, which correlates to a single row in a domain table.
PK	String	The combination of data source and primary key allows Amperity to uniquely identify every row in every data table across the entirety of customer data input to Amperity. Tip The combination of PK and Datasource uniquely identifies a row in the Unified Coalesced table, which correlates to a single row in a domain table.
Product Brand	String	The brand name of a product or item.
Product Brand ID	String	The ID for the brand name of a product or item.
Product Category	String	This column is input to AmpIQ predictive modeling when joined to Unified Itemized Transactions. A category to which the product belongs.
Product Category ID	String	The ID for the category to which a product belongs.
Product Class	String	The name of the class (or grouping) to which a product or item belongs.
Product Class ID	String	The ID for the name of the class (or grouping) to which a product or item belongs.
Product Collection	String	The name of the collection to which a product or item belongs.
Product Collection ID	String	The ID for the name of the collection to which a product or item belongs.
Product Color	String	The color of a product or item.
Product Color ID	String	The ID for the color of a product or item.
Product Department	String	The department to which a product or item belongs.
Product Department ID	String	The ID for the department to which a product or item belongs.
Product Description	String	This column is input to AmpIQ predictive modeling when joined to Unified Itemized Transactions. A description of the product.
Product Division	String	The division to which a product or item belongs.
Product Division ID	String	The ID for the division to which a product or item belongs.
Product Fabric	String	The fabric used for a product or item.
Product Fabric ID	String	The ID for the fabric used for a product or item.
Product Gender	String	A list of gender options for products. For example: F, M, unisex, NULL (for unknown).
Product Group	String	The group to which a product or item belongs.
Product ID	String	The unique identifier for a product. Values in this column depend on fields that are tagged with the pc/product-id semantic.
Product Material	String	The material used for a product or item.
Product Material ID	String	The ID for the material used for a product or item.
Product MSRP	String	The manufacturer’s suggested retail price (MSRP) for a product or item. The manufacturer’s suggested retail price (MSRP) is the price before shipping costs, taxes, and/or discounts have been applied. MSRP is sometimes referred to as the base price.
Product Name	String	The name of the product or item.
Product Season	String	The season to which a product or item is associated.
Product Season ID	String	The ID for the season to which a product or item is associated.
Product Silhouette	String
Product Size	String	The size of a product or item.
Product Size ID	String	The ID for the size of a product or item.
Product Sku	String	The stock keeping unit, or SKU, for the product or item. A stock keeping unit (SKU) is an identifier that captures all of the unique details of any individual product, including specific attributes that differentiate by color, size, material, and so on.
Product Style	String	The style of a product or item.
Product Subcategory	String	This column is input to AmpIQ predictive modeling when joined to Unified Itemized Transactions. A subcategory or secondary variant to which a product belongs.
Product Subcategory ID	String	The ID for the subcategory or secondary variant to which a product belongs.
Product Subclass	String	The subclass to which a product or item is assigned.
Product Subclass ID	String	The ID for the subclass to which a product or item is assigned.
Product Subdepartment	String	The sub-department to which a product or item is assigned.
Product Subdepartment ID	String	The ID for the sub-department to which a product or item is assigned.
Product Type	String	The type assigned to a product or item.
Product UPC	String	The UPC code for the product or item. A Universal Product Code (UPC or UPC code) is a barcode that is widely used to track items in stores.