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. This table is added automatically when semantics related to transactions are applied to interaction records.
Add table¶
The Transaction_Attributes table is a required table for the customer 360 database and is always built as a passthrough table from 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 Transaction_Attributes table. This section documents how to manually add this table, should it be necessary.
To add the Transaction_Attributes table
From the Customer 360 tab, 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 “Transaction_Attributes”.
Set the build mode to Passthrough.
From the Source Table drop-down, select the Transaction_Attributes table.
Click Next.
Add a table description. This enables a tooltip that is visible from other areas in Amperity.
From the Table Semantics drop-down, select Transaction_Attributes.
Under Version History, select Enable table version history.
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 |