Unified Itemized Transactions table¶

The Unified Itemized Transactions table has rows of 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 unique by 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.

Important

Database tables for transactions must be configured in the following order:

Unified Itemized Transactions (this topic)
Unified Transactions
Transaction Attributes Extended

The Unified Product Catalog table is optional and may be configured for use with building queries, database tables, and other non-audience workflows. Your brand’s product catalog must be integrated into the Unified Itemized Transactions table before you can use product catalog attributes to build audiences, segments, and campaigns.

Add table¶

The Unified Itemized Transactions table is a required table for the customer 360 database when transactions are available to your tenant and fields have been tagged with txn-items/ semantic tags. The Unified Itemized Transactions table is built from Stitch output and is added automatically.

Define product catalog¶

Your brand’s product catalog must be joined to the Unified Itemized Transactions table to enable the use of product catalog attributes when building audiences for use with segments and campaigns.

If your brand used the txn-items/ group of semantics to apply tags to fields that define your product catalog, this step happens automatically. If your brand used the pc/ group of semantics, you will need to use SQL to LEFT JOIN your product catalog to the Unified Itemized Transactions table to enable their use within segments and campaigns.

Important

The Unified Product Catalog table represents the taxonomy for your products and brands. Add attributes to the Unified Product Catalog table by applying pc/ semantic tags to fields in data sources that contain information about products.

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.

Column reference¶

The Unified Itemized Transactions table contains the following columns:

Column name	Data type	Description
Amperity ID	String	The unique identifier assigned to clusters of customer profiles that all represent the same individual. The Amperity ID does not replace primary, foreign, or other unique customer keys, but exists alongside them within unified profiles. Note The Amperity ID is a universally unique identifier (UUID) that is 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 customer ID is an identifier that uniquely identifies a customer within a dataset. For example, a loyalty ID, a mobile app ID, or a login name from a website. 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 currency 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 for a transaction. For example: Facebook, Google Ads, email, TikTok Ads, or Reddit. 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 identifies a canceled item. 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 predictive modeling. A flag that identifies a returned item. 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 applied to all units of 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 applied to all units of 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	Item list price is the manufacturer’s suggested retail price (MSRP) for all units of this item. The manufacturer’s suggested retail price (MSRP) is the price before applying shipping costs, taxes, and discounts. 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 earned after selling 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-profit semantic.
Item Quantity	Integer	This column is input to predictive modeling. Item quantity is the total number of items in an order. For returned and canceled items, item quantity is the total number of returned or canceled items. 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 predictive modeling. The total revenue for all units of an item, after applying discounts. For returned and canceled items, the total revenue for all returned or canceled items. 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. Tip A return or cancellation is stored as a separate record in the Unified Itemized Transactions table and is identified by a value of true in the Is Return or Is Cancellation column. 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 applying discounts. 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 paid for purchases. 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 predictive modeling. Order datetime is the date and time on which a customer places an order. 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 canceled. 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 Discount Amount	Decimal	This column is input to predictive modeling. Order discount amount is the total discount amount applied to the entire order. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. Caution This value should match the customer’s definition of an order-level discount. For example, some customers associate this value to order value, whereas others associate this value to a subtotal. Use domain SQL to update this field for the desired calculation. Also in: Unified Transactions
Order ID	String	This column is input to predictive modeling. An order ID is the unique identifier for the order. It links together all items in the same transaction. For returns and cancellations, the order ID is the unique identifier for the original order, including returned or canceled 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 canceled. 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 Catalogs	String	Optional Product catalog fields are added to the Unified Itemized Transactions table in two ways, depending on the approach your tenant used for defining your product catalog: When data sources use txn-item/ product catalog semantic tags. When product catalog fields are joined from the Unified Product Catalog table. Refer to the section in this topic about the columns that are available to product catalogs. Important The names of the columns that are available for product catalogs are identical. For example: Product Brand, Product Category, and Product Gender. The difference is the outcome of the approach your tenant used to define your product catalog within Amperity.
Product ID	String	This column is input to predictive modeling. The unique identifier for a product. A stock keeping unit (SKU) is an identifier that captures unique details for individual products, including specific attributes to differentiate by color, size, material, and other product details. 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 definition 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 Amperity, such as for product affinity and audience sizes.
Purchase Brand	String	The brand purchased by customer in a transaction. 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 predictive modeling. A purchase channel is the channel in which a customer makes a transaction. 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 for 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 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	Unit list price is 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 applying shipping costs, taxes, and discounts. 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 earned when selling a single unit 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/unit-profit semantic.
Unit Revenue	Decimal	The total revenue for a single unit of an item or the total revenue for a single unit of a returned or cancelled item. 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 applying discounts. 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 for 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 Amperity, 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. Important The Unified Product Catalog table represents the taxonomy for your products and brands. Add attributes to the Unified Product Catalog table by applying pc/ semantic tags to fields in data sources that contain information about products. 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.