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

  1. From the Customer 360 tab, under All Databases, select the menu for the customer 360 database, and then click Edit.

  2. From the Database Editor, click Add Table.

  3. Name the table “Transaction_Attributes”.

  4. Set the build mode to Passthrough.

  5. From the Source Table drop-down, select the Transaction_Attributes table.

  6. Click Next.

  7. Add a table description. This enables a tooltip that is visible from other areas in Amperity.

  8. From the Table Semantics drop-down, select Transaction_Attributes.

  9. Under Version History, select Enable table version history.

  10. 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