Unified_Product_Catalog Table

The Unified_Product_Catalog table contains a row for every product SKUs, broken down by product ID and base ID, along with list prices and costs.

Note

What is the Unified_Product_Catalog table? The Unified_Product_Catalog table represents the taxonomy for your products and brands. You may apply product catalog semantic tags to any table that contains the taxonomy for your products and brands, and then use the product identifier as the basis for 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 from Stitch output.

To add the Unified_Product_Catalog table

  1. From the Customer 360 tab, 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_Catelog 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_category

String

This column is input to AmpIQ predictive modeling when joined to Unified_Itemized_Transactions.

A category to which the product belongs.

product_description

String

A description of the product.

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_subcategory

String

A subcategory or secondary variant to which a product belongs.