Unified_Transactions table¶

The Unified_Transactions table contains one row for each unique transaction record, with each order ID associated to an Amperity ID.

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

An example of the Unified_Transactions data table.

Add table¶

The Unified_Transactions table is a required table for the customer 360 database when transactions and itemized transaction data is available to your tenant. The Unified_Transactions table is built as a SQL table that is based on 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_Transactions table. This section documents how to manually add this table, should it be necessary.

To add the Unified_Transactions table

From the Customer 360 page, under All Databases, select the menu for the customer 360 database, and then click Edit.
From the Database Editor, click Add Table.
Name the table “Unified_Transactions”.
Set the build mode to SQL.
Click Apply template, and then select Unified_Transactions.
Update the placeholder names (shown as “Domain:Table” in the query) and set field-level priorities.
Click Validate to verify the SQL runs without error.
Click Next. This opens the Database Table Definition page.
Add a table description. This enables a tooltip that is visible from other areas in Amperity.
Verify that the db/unique database field semantics were applied to the amperity_id column.
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.
From the Table Semantics drop-down, select Unified_Transactions.
Click Save.

Multiple Amperity ID template¶

If orders are associated with more than one Amperity ID, prioritize the IDs using the following template:

WITH
  amp_priority AS (
    SELECT DISTINCT
      ut.order_id
      ,ut.datasource
      ,FIRST_VALUE(uc.amperity_id) OVER (
        PARTITION BY ut.order_id, ut.datasource
        ORDER BY uc.update_dt DESC
      ) AS amperity_id
    FROM (SELECT amperity_id, datasource, update_dt FROM Unified_Coalesced) uc
    JOIN (SELECT amperity_id, datasource, order_id FROM Unified_Transactions) ut
    ON uc.amperity_id = ut.amperity_id
  )

SELECT t.* FROM table_name t
JOIN amp_priority ap ON t.order_id=ap.order_id

Order discount amounts¶

Order discount amounts should be rolled-up to the Unified_Transactions table (from the Unified_Itemized_Transactions table) using one of the following approaches:

A pro-rated amount for individual line items:

SUM(uit.order_discount_amount) = ut.order_discount_amount

An amount for the whole order:

MAX(uit.order_discount_amount) = ut.order_discount_amount

Column reference¶

The Unified_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
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.
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 order ID in the transactions table. When a foreign key is not present, the customer ID is used to join interaction records (transactions and itemized transactions) to tables that contain the Amperity ID. Note A namespaced foreign key should be used along with a customer ID. When a foreign key is added to transactions data it: Must match a foreign key in a table that is output by Stitch Must be well-distributed across the data source (a high percentage of values must not be 0) May contain a NULL value Must be unique for each order ID in the Unified_Transactions table. Also in: Unified_Itemized_Transactions
digital_channel	String	The digital channel through 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.
order_cancelled_quantity	Integer	This column is input to AmpIQ predictive modeling. The total number of items in the original transaction that were cancelled. This value should match the sum of all items in the itemized transactions that were cancelled for the same order ID. Important This value must be less than or equal to 0 when is_cancelled is `TRUE`.
order_cancelled_revenue	Decimal	This column is input to AmpIQ predictive modeling. The total amount of revenue for all cancelled items in the transaction. This value should match the sum of the revenue for all items in the itemized transactions that were cancelled. Important This value must be less than or equal to 0 when is_cancelled is `TRUE`.
order_cost	Decimal	Order cost represents the total cost of goods sold (COGS) for a single transaction. Cost of goods sold (COGS) are the direct costs of producing goods that are sold by a brand, including the costs of materials and labor to produce the item, but excluding indirect expenses like distribution or sales. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. Warning Only one of order-profit and order-cost may be present for a transaction.
order_datetime	Datetime	This column is input to AmpIQ predictive modeling. Order datetime is the date (and time) on which an order was placed. The order date: Must have a consistent time zone across all dates in the transactions data. Should be a local time zone. Should be a timestamp, which is converted to datetime automatically when a date is present in the timestamp. 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. This column is used to calculate the following transaction attributes: first_order_datetime latest_order_datetime second_order_datetime Also in: Unified_Itemized_Transactions
order_discount_amount	Decimal	This column is input to AmpIQ predictive modeling. Order discount amount is the total discount amount that is 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. This value is used by Amperity for discount sensitivity analysis. 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_Itemized_Transactions
order_discount_percent	Decimal	Order discount percent is the percentage discount that is applied to the order value for the entire transaction, in addition to any item or unit-specific discount percentages. This value may be used as an input to order discount amount. Note This value must be between 0 and 1. This value is used by Amperity for discount sensitivity analysis. Caution This value should match the customer’s definition of an order-level discount percentage. 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.
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 column is the primary key and must be associated with the pk semantic tag. Each unique order ID must: Appear only once in the Unified_Transactions. Match an order ID in the Unified_Itemized_Transactions table. (This order ID might be associated to multiple items within a single transactions.) 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)`. 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_Itemized_Transactions
order_list_price	Decimal	Order list price is the total value for a transaction, as defined by 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 match the sum of item list price amounts in the itemized transactions that are associated with the same order ID. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations.
order_profit	Decimal	Order profit is the amount of profit that is earned from a single transaction. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. Warning Only one of order-profit and order-cost may be present for a transaction.
order_quantity	Integer	This column is input to AmpIQ predictive modeling. Order quantity is the total number of individual items associated with the transaction. This value should match the sum of all items in the itemized transactions that have not been returned or cancelled for the same order ID. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. This column is used to calculate the following transaction attributes: first_order_total_items L30D_average_item_price L30D_average_num_items L30D_total_items latest_order_total_items lifetime_average_item_price lifetime_average_num_items lifetime_total_items second_order_total_items
order_returned_quantity	Integer	This column is input to AmpIQ predictive modeling. Order returned quantity is the total number of items in the original transaction that were returned. This value should match the sum of all items in the itemized transaction that were returned for the same order ID. Note This value must be less than or equal to 0. Important This value must be less than or equal to 0 when is_return is `TRUE`.
order_returned_revenue	Decimal	This column is input to AmpIQ predictive modeling. Order returned revenue total amount of revenue for all returned items in a transaction. This value should match the sum of the revenue for all items in the itemized transactions that were returned. Important This value must be less than or equal to 0 when is_return is `TRUE`.
order_revenue	Decimal	This column is input to AmpIQ predictive modeling. The total amount of revenue for all items in a transaction after discounts are applied, ignoring returns and/or cancellations. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations. This column is used to calculate the following transaction attributes: first_order_revenue L30D_average_item_price L30D_average_order_value L30D_largest_order_value L30D_order_revenue latest_order_revenue lifetime_average_item_price lifetime_average_order_value lifetime_largest_order_value lifetime_order_revenue second_order_revenue
order_shipping_amount	Decimal	The order shipping amount is the total cost of shipping all items in the same transaction. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations.
order_subtotal	Decimal	An order subtotal is the amount for an order, before discounts are applied. This value should match the sum of item subtotal revenue in the itemized transactions that are associated with the same order ID.
order_tax_amount	Decimal	An order tax amount is the total amount of taxes that are associated with an 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.
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.
purchase_brand	String	This column is input to AmpIQ predictive modeling. 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. 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.
store_id	String	This column is input to AmpIQ predictive modeling. A store ID is a unique identifier that is identified with the location of a store.
sum_item_discount_amount	Decimal	The sum of discount amounts is the total of all discount amounts that were applied to each item within a transaction. This value should match the sum of item discount amounts in the itemized transactions that are associated with the same order ID.
sum_item_revenue	Decimal	The sum of itemized revenue for the original order, not including returns and/or cancellations. This value may be used as an input to order revenue. This value should match the sum of item revenue in the itemized transactions that are associated with the same order ID. Note This value must be greater than or equal to 0 for purchases, but less than or equal to 0 for returns or cancellations.