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):

Important

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

1. Unified Itemized Transactions

2. Unified Transactions (this topic)

3. Transaction Attributes Extended

4. Transaction Attributes

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 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 added using a SQL template.

To add the Unified Transactions table

1. Open your customer 360 database in edit mode, and then click Add Table.

2. Name the table “Unified_Transactions” (with underscores).

3. Set the build mode to SQL, and then select “Unified Transactions” from the Apply template drop-down.

4. Review the list of optional attributes to determine if any of those should be enabled for your tenant.

5. Click Next to validate the SQL.

6. Select “Unified Transactions” from the Table Semantics drop-down, and then add a description for the table. (The description is available as a tooltip in other locations in Amperity.

7. Click Save.

Multiple Amperity IDs

The following section (located near the top of the SQL) prioritizes order IDs when order IDs are associated with more than one Amperity ID. This may be customized.

amp_priority AS (
  SELECT DISTINCT
    uit.order_id
    ,FIRST(uc.amperity_id) OVER (
      PARTITION BY uit.order_id
      ORDER BY
        uc.update_dt
        ,uc.amperity_id
      DESC
    ) AS amperity_id
  FROM (
    SELECT DISTINCT
      amperity_id
      ,update_dt
    FROM Unified_Coalesced
  ) uc
  JOIN (
    SELECT DISTINCT
      amperity_id
      ,order_id
     FROM Unified_Itemized_Transactions
     WHERE amperity_id IS NOT NULL
  ) uit
  ON uc.amperity_id = uit.amperity_id
)

Optional attributes

You can extend the set of attributes that are available from the Unified Transactions table to include any of the following:

• Fiscal calendars

• Net order revenue

• Order costs

• Order discount amounts

Note

These attributes are built into the SQL templates for the Unified Transactions table, but are commented out. Some sets of attributes require updates be made to both tables, while others may only require updates be made to one table. The steps for each set will link to a topic section that describes the updates that should be made to the Transaction Attributes Extended table.

Fiscal calendars

A fiscal calendar is a yearly accounting period that aligns the weeks and months in a calendar year with holidays and a brand’s marketing goals to align the business for an entire calendar year. A common fiscal calendar used by brands is the 4-5-4 fiscal calendar.

A 4-5-4 calendar divides years into months using a 4 weeks - 5 weeks - 4 weeks pattern. Each week starts on a Sunday and ends on a Saturday. Each quarter has the same number of days. A 4-5-4 calendar can be useful for comparing like days for sales reporting purposes.

To add fiscal calendar attributes

1. Open the Unified Transactions table in the SQL editor.

2. Find the SELECT statement at the end of the SQL template.

3. Uncomment the following section, which is located around line 45:

   ,fc.fiscal_year AS fiscal_year
   ,fc.fiscal_quarter AS fiscal_quarter
   ,fc.fiscal_month AS fiscal_month
   ,fc.fiscal_week_number AS fiscal_week_number
   ,fc.fiscal_week_start AS fiscal_week_start
   ,fc.day_of_week AS fiscal_day_of_week
   ,fc.holiday_sale_name AS holiday_sale_name
   

4. Uncomment the left join at the end of the SELECT statement:

   LEFT JOIN fiscal_calendar fc ON fc.calendar_date = DATE(order_datetime)
   

   where “fiscal_calendar” is the name of the fiscal calendar table in your tenant and “fc” is the alias for that table.

5. Validate the SQL, and then click Next, and then click Save.

Net order revenue

Net order revenue is the revenue for all items in an order, including returned and canceled items, but not including discounted amounts.

To add the net order revenue attribute

1. Open the Unified Transactions table in the SQL editor.

2. Find the SELECT statement at the end of the SQL template.

3. Uncomment the following section, which is located around line 45:

   ,order_revenue
     + order_returned_revenue
     + order_canceled_revenue
     - order_discount_amount
   AS net_order_revenue
   

4. Validate the SQL, and then click Next, and then click Save.

5. Optional. Extended attributes for net order revenue attributes may be added to the Transaction Attributes Extended table.

Order costs

Order cost represents the total cost of goods sold (COGS) for a single transaction, minus returns, cancellations, and discounts.

To add the order cost attribute

1. Ensure that cost information is available from the Unified Itemized Transactions table.

   Important

   If cost information is not available, review the data sources and/or custom domain tables in which txn-item semantic tags were applied and apply the txn-item/item-cost semantic tag to the appropriate fields.

2. Open the Unified Transactions table in the SQL editor.

3. Find the section named “rollup_uit”, which rolls-up individual attributes from the Unified Itemized Transactions custom domain table.

4. Uncomment the following section, which is located around line 30:

   ,SUM(
     IF(
       COALESCE(uit.is_return,FALSE) = FALSE
       AND COALESCE(uit.is_cancellation,FALSE) = FALSE
       ,uit.item_cost
       ,0
     )
   ) AS order_cost
   

5. Validate the SQL, and then click Next.

6. Click Save.

7. Optional. Extended attributes for calculated order cost attributes may be added to the Transaction Attributes Extended table.

Order discount amounts

An order discount amount represents the total amount for all discounts that were applied to all items in same transaction. You can extend the Unified Transactions table to include order discount amounts by rolling up itemized discount amount values from the Unified Itemized Transactions table. This is done by uncommenting a line of SQL that exists in the SQL template for the Unified Transactions table.

1. Use the MAX() function when discount amounts are consistent across records.

2. Update to use the SUM() function when discount amounts are prorated across records.

Discount amounts are consistent

When itemized discount amount values are consistent across records:

1. Open the Unified Transactions table in the SQL editor.

2. Find the section named “rollup_uit”, which rolls-up individual attributes from the Unified Itemized Transactions table.

3. Uncomment the following section, which is located around line 30:

   ,MAX(
     IF(
       COALESCE(uit.is_return,FALSE) = FALSE
       AND COALESCE(uit.is_cancellation,FALSE) = FALSE
       ,uit.order_discount_amount
       ,0
     )
   ) AS order_discount_amount
   

4. Validate the SQL, and then click Next.

5. Click Save.

Discount amounts are pro-rated

When itemized discount amount values are pro-rated across records:

1. Open the Unified Transactions table in the SQL editor.

2. Find the section named “rollup_uit”, which rolls-up individual attributes from the Unified Itemized Transactions table.

3. Uncomment the following section, which is located around line 30, and then change the MAX() function to SUM():

   ,SUM(
     IF(
       COALESCE(uit.is_return,FALSE) = FALSE
       AND COALESCE(uit.is_cancellation,FALSE) = FALSE
       ,uit.order_discount_amount
       ,0
     )
   ) AS order_discount_amount
   

4. Validate the SQL, and then click Next.

5. Click Save.

Column reference

The Unified Transactions table contains the following columns:

Column name	Data type	Description
Amperity ID	String	This column is input to 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.
Fiscal Calendars	Varies	A fiscal calendar is a yearly accounting period that aligns the weeks and months in a calendar year with holidays and a brand’s marketing goals to align the business for an entire calendar year. A common fiscal calendar used by brands is the 4-5-4 fiscal calendar. The SQL template for the Unified Transaction table contains a series of fields for use with fiscal calendars. For example: Fiscal Year Fiscal Quarter Fiscal Month Fiscal Week Number Fiscal Week Start Fiscal Day of Week Holiday Sale Name Before you can use fiscal calendar attributes in the Unified Transactions table you must configure the SQL template for your brand’s fiscal calendar.
Net Order Revenue	Decimal	Net order revenue is total revenue minus costs, returns, and discounts. This value must be configured in the SQL template for the Unified Transaction table.
Order Canceled Quantity	Integer	This column is input to predictive modeling. The total number of items in the original transaction that were canceled. This value should match the sum of all items in the itemized transactions that were canceled for the same order ID. Important This value must be less than or equal to 0 when is_canceled is TRUE.
Order Canceled Revenue	Decimal	This column is input to predictive modeling. The total amount of revenue for all canceled items in the transaction. This value should match the sum of the revenue for all items in the itemized transactions that were canceled. Important This value must be less than or equal to 0 when is_canceled is TRUE.
Order Cost	Decimal	Order cost represents the total cost of goods sold (COGS) for a single transaction, minus returns, cancellations, and discounts. 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. This value must be configured in the SQL template for the Unified Transaction table. 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 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 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 and must be configured in the SQL template for the Unified Transaction table. 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 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 canceled, the order ID is the unique identifier for the original order, including the returned or canceled 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 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). 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 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 canceled 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 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 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 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 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 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 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.