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:
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
Open your customer 360 database in edit mode, and then click Add Table.
Name the table “Transaction_Attributes” (with underscores).
Set the build mode to SQL, and then select “Transaction Attributes” from the Apply template drop-down.
Click Next to validate the SQL.
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.
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 canceled 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 canceled 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 canceled 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 canceled 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 canceled 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 canceled 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 |