Transaction Attributes table

The Transaction Attributes table describes the behavior and history of customer transactions, such as purchase history and frequency, brand preferences, order history, time between purchases, and so on.

Important

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

  1. Unified Itemized Transactions

  2. Unified Transactions

  3. Transaction Attributes Extended

  4. Transaction Attributes (this topic)

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 Transaction Attributes table is a required table for the customer 360 database and is added using a SQL template.

To add the Transaction Attributes table

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

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

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

  4. Click Next to validate the SQL.

  5. Select “Transaction Attributes” 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.

  6. Click Save.

Column reference

The Transaction Attributes table contains the following columns:

Column name

Data type

Description

Amperity ID

String

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.

This column is added when a transaction is associated with an Amperity ID from the Unified Transactions table.

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

Early Repeat Purchaser

Boolean

Early repeat purchaser is a flag that indicates if a customer made a repeat purchase within the previous 90 days.

Tip

Use the Early Repeat Purchaser attribute as a leading indicator for overall conversion rate of one-time buyers to repeat customers, even when it does not capture the total number of one-time buyers who have returned to purchase again.

This column is calculated from the First Order Datetime and Second Order Datetime columns in the Transaction Attributes table.

Also in: Transaction Attributes Extended

First Order Datetime

Datetime

First order date is the date on which the first order was placed by a customer.

This column is calculated from the Order Datetime column in the Unified Transactions table, which is created when the order-datetime semantic tag is applied to interaction records that contain transactions data.

Also in: Transaction Attributes Extended

First Order ID

String

First order ID is the order ID for the first order that was made by a customer.

This column is calculated from the Order ID column in the Unified Transactions and Unified Itemized Transactions tables, which is created when the order-id semantic tag is applied to interaction records that contain transactions and itemized transactions data.

Also in: Transaction Attributes Extended

First Order Is Retained

Boolean

First order is retained is a flag that indicates if a customer has made a repeat purchase within 365 days of their first order.

First Order Revenue

Decimal

First order revenue is the total revenue that is associated with a customer’s first order, ignoring returned items and/or cancelled items.

This column is calculated from the Order Revenue column in the Unified Transactions table, which is created when the order-revenue semantic tag is applied to interaction records that contain transactions data.

Also in: Transaction Attributes Extended

First Order Total Items

Integer

First order total items represents the number of items that were purchased as part of a customer’s first order, ignoring returned items and/or cancelled items.

This column is calculated from the Order Quantity column in the Unified Transactions table, which is created when the order-quantity semantic tag is applied to interaction records that contain transactions data.

Also in: Transaction Attributes Extended

Latest Order Datetime

Datetime

Latest order date is the date (and time) on which the most recent order was placed by a customer.

This column is calculated from the Order Datetime column in the Unified Transactions table, which is created when the order-datetime semantic tag is applied to interaction records that contain transactions data.

Also in: Transaction Attributes Extended

Latest Order ID

String

Latest order ID is the order ID for the most recent order that was made by a customer.

This column is calculated from the Order ID column in the Unified Transactions and Unified Itemized Transactions tables, which is created when the order-id semantic tag is applied to interaction records that contain transactions and itemized transactions data.

Also in: Transaction Attributes Extended

Latest Order Revenue

Decimal

Latest order revenue is the total revenue that is associated with a customer’s most recent order, ignoring returned items and/or cancelled items.

This column is calculated from the Order Revenue column in the Unified Transactions table, which is created when the order-revenue semantic tag is applied to interaction records that contain transactions data.

Also in: Transaction Attributes Extended

Latest Order Total Items

Integer

Latest order total items is the number of items that were purchased as part of a customer’s most recent order, ignoring returned items and/or cancelled items.

This column is calculated from the Order Quantity column in the Unified Transactions table, which is created when the order-quantity semantic tag is applied to interaction records that contain transactions data.

Also in: Transaction Attributes Extended

Lifetime Average Item Price

Decimal

The average individual item price for all orders, ignoring returns and/or cancellations.

This column is calculated from the Order Quantity and Order Revenue columns in the Unified Transactions table, which are created when the order-quantity and order-revenue semantic tags are applied to interaction records that contain transactions data.

Lifetime Average Num Items

Decimal

The average number of items purchased for all orders, ignoring returns and/or cancellations.

This column is calculated from the Order ID and Order Quantity columns in the Unified Transactions table, which are created when the order-id and order-quantity semantic tags are applied to interaction records that contain transactions data.

Lifetime Average Order Value

Decimal

The average lifetime revenue for all orders, ignoring returns and/or cancellations.

This column is calculated from the Order ID and Order Revenue columns in the Unified Transactions table, which are created when the order-id and order-revenue semantic tags are applied to interaction records that contain transactions data.

Lifetime Largest Order Value

Decimal

Lifetime largest order value identifies the largest order that is associated with a customer, ignoring returns and/or cancellations, for a customer’s entire purchase history.

This column is calculated from the Order Revenue column in the Unified Transactions table, which is created when the order-revenue semantic tag is applied to interaction records that contain transactions data.

Lifetime Order Frequency

Integer

A lifetime order frequency is the total number of orders that that a customer has made during their entire relationship with your brand.

This column is calculated from the Order ID column in the Unified Transactions and Unified Itemized Transactions tables, which is created when the order-id semantic tag is applied to interaction records that contain transactions and itemized transactions data.

Lifetime Order Revenue

Decimal

The lifetime revenue for all items, ignoring returns and/or cancellations.

This column is calculated from the Order Revenue column in the Unified Transactions table, which is created when the order-revenue semantic tag is applied to interaction records that contain transactions data.

Lifetime Preferred Purchase Brand

String

The most frequent brand for all orders.

Lifetime Preferred Purchase Channel

String

The most frequent purchase-channel for all orders.

Lifetime Total Items

Integer

The lifetime number of individual items associated with the transaction, ignoring returns and/or cancellations.

This column is calculated from the Order Quantity column in the Unified Transactions table, which is created when the order-quantity semantic tag is applied to interaction records that contain transactions data.

Multi Purchase Brand

Boolean

A flag that indicates if a customer has interacted with more than one brand.

This column is calculated from the Purchase Brand column in the Unified Transactions table, which is created when the purchase-brand semantic tag is applied to interaction records that contain transactions data.

Also in: Transaction Attributes Extended

Multi Purchase Channel

Boolean

Multi-purchase channel is a flag that indicates if a customer has purchased from more than one channel.

This column is calculated from the Purchase Channel column in the Unified Transactions table, which is created when the purchase-channel semantic tag is applied to interaction records that contain transactions data.

Also in: Transaction Attributes Extended

One And Done

Boolean

One and done is a flag that indicates if a customer has made only one purchase.

Important

Amperity uses the range of data that is provided to it to identify one-time buyers. For example, if Amperity is provided data for the years 2015-2022, purchases made in 2014 are not used to identify one-time buyers.

This column is calculated from the Lifetime Order Frequency column in the Transaction Attributes table.

Also in: Transaction Attributes Extended

Second Order Datetime

Datetime

Second order date is the date on which the second order was placed by a customer.

This column is calculated from the Order Datetime column in the Unified Transactions and Unified Itemized Transactions tables, which is created when the order-datetime semantic tag is applied to interaction records that contain transactions and itemized transactions data.

Also in: Transaction Attributes Extended

Second Order ID

String

Second order ID is the order ID for the second order that was made by a customer.

This column is calculated from the Order ID column in the Unified Transactions and Unified Itemized Transactions tables, which are created when the order-id semantic tag is applied to interaction records that contain transactions and itemized transactions data.

Also in: Transaction Attributes Extended

Second Order Is Retained

Boolean

Second order is retained is a flag that indicates if a customer has made a repeat purchase within 365 days of their second order.

Second Order Revenue

Decimal

Second order revenue is the total revenue that is associated with a customer’s second order, ignoring returned items and/or cancelled items.

This column is calculated from the Order Revenue column in the Unified Transactions table, which is created when the order-revenue semantic tag is applied to interaction records that contain transactions data.

Second Order Total Items

Integer

Second order total items is the number of items that were purchased as part of a customer’s second order, ignoring returned items and/or cancelled items.

This column is calculated from the Order Quantity column in the Unified Transactions table, which is created when the order-quantity semantic tag is applied to interaction records that contain transactions data.

Also in: Transaction Attributes Extended