Unified_Itemized_Transactions Table

The Unified_Itemized_Transactions table contains every row of every stitched table with all transactional data summarized to the item level, and then coalesced into a single column for each unique combination of order ID and product ID. The order ID is associated with an Amperity ID.

The following diagram shows an example of the Unified_Itemized_Transactions table (click to view a larger diagram):

An example of the Unified_Itemized_Transactions data table.

Add table

The Unified_Itemized_Transactions 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 txn-items/ semantic tags. The Unified_Itemized_Transactions table is built as a passthrough table from Stitch output.

Important

This table is added automatically when the “Customer 360” template is used to add the customer 360 database. Using a template is the recommended way to add the Unified_Itemized_Transactions table. This section documents how to manually add this table, should it be necessary.

To add the Unified_Itemized_Transactions 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_Itemized_Transactions”.

  4. Set the build mode to Passthrough.

  5. From the Source Table drop-down, select the Unified_Itemized_Transactions 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.

    Caution

    Revenue and quantity fields should contain positive values when they represent a sale and negative values when they represent a return or cancellation.

  9. From the Table Semantics drop-down, select Unified_Itemized_Transactions.

  10. Under Version History, select Enable table version history.

  11. Click Save.

Define product catalog

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.

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_Itemized_Transactions table contains the following columns:

Column Name

Data type

Description

amperity_id

String

This column is input to AmpIQ predictive modeling.

The unique identifier that is assigned to clusters of customer records that all represent the same individual. The Amperity ID does not replace primary and foreign keys, but exists alongside them within unified profiles.

Note

The Amperity ID is a universally unique identifier (UUID) that is represented by 36 characters spread across five groups separated by hyphens: 8-4-4-4-12.

For example:

123e4567-e89b-12d3-a456-426614174000

customer_id

String

A custom semantic tag that is applied to interaction records to identify a field that is used in downstream processes to associate interaction records to the Amperity ID.

A customer ID may appear once for each unique order ID in the itemized transactions table.

Note

A namespaced foreign key may be used instead of (or in addition to) a customer ID.

Values in this column depend on fields that are tagged with the txn-item/customer-id semantic or a foreign key.

Also in: Unified_Transactions

currency

String

Currency represents the type of currency that was used to pay for an item. For example: dollar.

Note

Currency must be consistent across all orders from the same data source.

Values in this column depend on fields that are tagged with the txn-item/currency semantic.

digital_channel

String

The digital channel by which a transaction was made. For example: Facebook, Google Ads, email, etc.

Note

This column should only have values when purchase-channel specifies an online channel.

Values in this column depend on fields that are tagged with the txn-item/digital-channel semantic.

is_cancellation

Boolean

A flag that indicates if the item was cancelled.

Values in this column depend on fields that are tagged with the txn-item/is-cancellation semantic.

Important

This value should be TRUE when items are canceled and FALSE when items are purchases and NULL when the value is unknown.

is_return

Boolean

This column is input to AmpIQ predictive modeling.

A flag that indicates if the item was returned.

Values in this column depend on fields that are tagged with the txn-item/is-return semantic.

Important

This value should be TRUE when items are returns and FALSE when items are purchases and NULL when the value is unknown.

item_cost

Decimal

Item cost is the cost to produce all units of an item.

Note

This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations.

Values in this column depend on fields that are tagged with the txn-item/item-cost semantic.

item_discount_amount

Decimal

Item discount amount is the discount amount that is applied to all units that are associated with a single item within a single transaction.

This value should equal item quantity multiplied by unit discount amounts.

This value is used by Amperity for discount sensitivity analysis.

Note

This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations.

Values in this column depend on fields that are tagged with the txn-item/item-discount-amount semantic.

item_discount_percent

Decimal

Item discount percent is the percentage discount that is applied to all units that are associated with a single item within a single transaction.

This value is used by Amperity for discount sensitivity analysis.

Note

This value must be between 0 and 1.

Values in this column depend on fields that are tagged with the txn-item/item-discount-percent semantic.

item_list_price

Decimal

The manufacturer’s suggested retail price (MSRP) for all units of this 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.

This value should equal item revenue plus item discount amount.

Note

This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations.

Values in this column depend on fields that are tagged with the txn-item/item-list-price semantic.

item_profit

Decimal

Item profit represents the amount of profit that is earned when all units of an item are sold.

Note

This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations.

Values in this column depend on fields that are tagged with the txn-item/item-profit semantic.

item_quantity

Integer

This column is input to AmpIQ predictive modeling.

Item quantity is the total number of items in an order. When an item has been returned or an order has been cancelled, item quantity is the total number of items that were returned and/or cancelled.

Note

This value must be less than or equal to 0 when is-return or is-cancellation are true.

Values in this column depend on fields that are tagged with the txn-item/item-quantity semantic.

item_revenue

Decimal

This column is input to AmpIQ predictive modeling.

The total revenue for all units of an item, after discounts are applied. When an item has been returned or the order has been cancelled, the total revenue for all items that were returned and/or cancelled.

This value should equal item quantity multiplied by unit revenue.

Note

This value must be less than or equal to 0 when is-return or is-cancellation are true.

Values in this column depend on fields that are tagged with the txn-item/item-revenue semantic.

item_subtotal

Decimal

An item subtotal is the amount for an item, before discounts are applied.

This value should equal unit list price times item quantity.

This value is used by Amperity for discount sensitivity analysis.

Note

This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations.

Values in this column depend on fields that are tagged with the txn-item/item-subtotal semantic.

item_tax_amount

Decimal

An item tax amount is the total amount of taxes that are associated with the purchase of an item.

Note

This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations.

Values in this column depend on fields that are tagged with the txn-item/item-tax-amount semantic.

order_datetime

Datetime

This column is input to AmpIQ predictive modeling.

Order datetime is the date (and time) on which an order was placed.

Note

Other dates associated with an order that are not specific to a transactions, such as dates associated with hotel stays and reservations, should be added to the Unified_Product_Catalog table.

When is_return is TRUE, the date and time on which the order was returned.

When is_cancellation is TRUE, the date and time on which the order was cancelled.

Values in this column depend on fields that are tagged with the txn-item/order-datetime semantic.

This column is used to calculate the following transaction attributes:

  • first_order_datetime

  • latest_order_datetime

  • second_order_datetime

Also in: Unified_Transactions

order_id

String

This column is input to AmpIQ predictive modeling.

An order ID is the unique identifier for the order and links together all of the items that were part of the same transaction. When an item has been returned or when an order has been cancelled, the order ID is the unique identifier for the original order, including the returned or cancelled items.

This field is often the primary key and associated with the pk semantic tag.

Note

For data that contains itemized transactions, where a single transaction includes more than one of the same item, the order ID will appear more than once.

Note

The order ID should never change, even when an item in the order is returned or cancelled.

Caution

If order IDs are recycled and/or are otherwise not guaranteed to be unique over time, the unique identifier for the order must be updated to be a combination of the order ID and the date on which the order occurred. This must be done using domain SQL similar to: CONCAT(order_id, order_date).

Values in this column depend on fields that are tagged with the txn-item/order-id semantic. The combination of order_id and product_id must be unique for each row in this table.

This column is used to calculate the following transaction attributes:

  • first_order_id

  • L30D_average_num_items

  • L30D_average_order_value

  • L30D_order_frequency

  • latest_order_id

  • lifetime_average_num_items

  • lifetime_average_order_value

  • lifetime_order_frequency

  • second_order_id

Also in: Unified_Transactions

payment_method

String

A payment method is how a customer chose to pay for the items they have purchased. For example: credit card, gift card, or cash.

Values in this column depend on fields that are tagged with the txn-item/payment-method semantic.

product_id

String

The unique identifier for a product.

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.

For example, a shirt with the same color and material, but with three different sizes would be represented by three unique SKUs and would also be represented by three unique product IDs.

Note

For data that contains itemized transactions, where a single transaction includes more than one of the same product, the product ID will appear more than once.

Caution

Every customer has their own definition for SKUs and product IDs. Be sure to understand this defintition before applying semantic tags to fields with product IDs to ensure they accurately reflect the customer’s definition.

Values in this column depend on fields that are tagged with the txn-item/product-id semantic. The combination of order_id and product_id must be unique for each row in this table; product quantity is counted using item_quantity.

Important

This column is recommended when using a product catalog. A product catalog is a requirement for certain predictive features of AmpIQ, such as for product affinity and audience sizes.

purchase_brand

String

The brand for which a transaction was made.

Note

This column should only have values when interaction records contain transactions data for more than one brand.

Values in this column depend on fields that are tagged with the txn-item/purchase-brand semantic.

This column is used to calculate the following transaction attributes: multi_purchase_brand.

purchase_channel

String

This column is input to AmpIQ predictive modeling.

A purchase channel is the channel from which a transaction was made. For example: in-store or online.

Values in this column depend on fields that are tagged with the txn-item/purchase-channel semantic.

store_id

String

A store ID is a unique identifier that is identified with the location of a store.

Values in this column depend on fields that are tagged with the txn-item/store-id semantic.

unit_cost

Decimal

Unit cost is the cost to produce a single unit of one item.

Note

This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations.

Values in this column depend on fields that are tagged with the txn-item/unit-cost semantic.

unit_discount_amount

Decimal

Unit discount amount is the discount amount that is applied to a single unit of one item.

This discount is often applied to all units of the same item within a single transaction.

This value is used by Amperity for discount sensitivity analysis.

Note

This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations.

Values in this column depend on fields that are tagged with the txn-item/unit-discount-amount semantic.

unit_list_price

Decimal

The manufacturer’s suggested retail price (MSRP) for a single unit of an 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.

This value should equal the unit discount amount plus the unit subtotal.

Note

This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations.

Values in this column depend on fields that are tagged with the txn-item/unit-list-price semantic.

unit_profit

Decimal

Unit profit represents the amount of profit that is earned when a single unit of an item is sold.

Note

This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations.

Values in this column depend on fields that are tagged with the txn-item/unit-profit semantic.

unit_revenue

Decimal

The total revenue for a single unit of an item. When an item has been returned or the order has been cancelled, the total revenue for a single unit of an item that was returned and/or cancelled.

Note

This value must be less than or equal to 0 when is-return or is-cancellation are true.

Values in this column depend on fields that are tagged with the txn-item/unit-revenue semantic.

unit_subtotal

Decimal

A unit subtotal is the amount for a single unit of one item, before discounts have been applied.

This value is used by Amperity for discount sensitivity analysis.

Note

This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations.

Values in this column depend on fields that are tagged with the txn-item/unit-subtotal semantic.

unit_tax_amount

Decimal

A unit tax amount is the total amount of taxes that are associated with a single unit.

Note

This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations.

Values in this column depend on fields that are tagged with the txn-item/unit-tax-amount semantic.

Custom attributes

Varies

You may extend the Unified_Itemized_Transactions table to define custom attributes for use with a product catalog.

Important

This column is recommended when using a product catalog. A product catalog is a requirement for certain predictive features of AmpIQ, such as for product affinity and audience sizes.

Add custom attributes to the Unified_Itemized_Transactions table by extending it to add columns that support using a product catalog. Custom attributes should include the columns to which product catalog semantic tags were applied, but may include additional custom attributes that are unique to your tenant and your brands.

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.