Transaction_Attributes_Extended Table

The Transaction_Attributes_Extended table contains additional attributes for customer flags, customer orders, data differences, time period rollups, and RFM scores, many of which are represented by duration (days, months, years, lifetime), order position (first, second, last), frequency, and revenue.

Extended transaction attributes–customer flags, customer orders, date differences, time period rollups, and RFM–are also calculated by Amperity based on data sources that contain interaction records that were tagged with transactions and itemized transaction semantics.

Extended transaction attributes are added to the Transaction_Attributes_Extended table, which is an output of Stitch. Extended transaction attributes are fully available to the customer 360 database and to segments.

Add table

The Transaction_Attributes_Extended 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_Extended table. This section documents how to manually add this table, should it be necessary.

To add the Transaction_Attributes_Extended 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_Extended”.

  4. Set the build mode to Passthrough.

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

  6. Click Next.

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

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

  9. Click Save.

Column reference

Extended transaction attributes are presented as a single table (with many columns), including an Amperity ID, and fit into the following categories:

Customer flags

Each record has a set of flags that indicate if a customer has purchased, the number of brand interactions, the number of brand channels, and if that customer is an early repeat purchaser.

Column Name

Data type

PII

Description

amperity_id

String

An Amperity ID is a patented unique identifier that is assigned to clusters of customer records. A single Amperity ID represents a single individual. Unlike other systems, the Amperity ID is reassessed every day for the most comprehensive view of your customers.

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.

Also in: Transaction_Attributes

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

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

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.

Also in: Transaction_Attributes

Customer orders

Each record contains a subset of order data from a customers first, second, and latest order. Each set of attributes is prefixed by first, second, or latest, depending on the order data that is being summarized.

Column Name

Data type

PII

Description

<x>_order_datetime

Datetime

The datetime on which the order was made. Available columns:

  • first_order_datetime

  • second_order_datetime

  • latest_order_datetime

Also in: Transaction_Attributes

<x>_order_id

String

The ID of the order. Available columns:

  • first_order_id

  • second_order_id

  • latest_order_id

Also in: Transaction_Attributes

<x>_order_purchase_brand

String

The brand of the order made by the customer. Available columns:

  • first_order_purchase_brand

  • second_order_purchase_brand

  • latest_order_purchase_brand

Also in: Transaction_Attributes

<x>_order_purchase_channel

String

The channel in which the customer’s order was made. Available columns:

  • first_order_purchase_channel

  • second_order_purchase_channel

  • latest_order_purchase_channel

Also in: Transaction_Attributes

<x>_order_revenue

Decimal

The total revenue for orders at each interval. Available columns:

  • first_order_revenue

  • second_order_revenue

  • latest_order_revenue

Also in: Transaction_Attributes

<x>_store_id

String

The ID of the store where the customer made their order. This value may be NULL if the associated channel is not retail or some equivalent. Available columns:

  • first_order_store_id

  • second_order_store_id

  • latest_order_store_id

<x>_total_items

Integer

The total number of items in the order. Available columns:

  • first_order_total_items

  • second_order_total_items

  • latest_order_total_items

Also in: Transaction_Attributes

Date differences

Each record contains three attributes that specify the number of days between certain events.

Column Name

Data type

PII

Description

days_since_latest_order

Integer

Days since latest order is the number of days that have elapsed since a customer has placed an order.

This column is calculated from the latest_order_datetime column in the Transaction_Attributes table.

first_to_latest_order_days

Integer

First-to-latest order days is the number of days that have elapsed between the date of the first order and the date of the latest order.

This column is calculated from the first_order_datetime and latest_order_datetime columns in the Transaction_Attributes table.

first_to_second_order_days

Integer

First-to-second order days is the number of days that have elapsed between the date of the first order and the date of the second order.

This column is calculated from the first_order_datetime and second_order_datetime columns in the Transaction_Attributes table.

Time period rollups

Each record contains time period rollups of the customer’s transaction history. The time periods used are lifetime, L12M (the 12 months of transaction history starting 12 months ago), LY12M (the 12 months of transaction history starting 24 months ago), and L30D (the last 30 days).

Column Name

Data type

PII

Description

<x>_average_item_price

Decimal

The average item price during the time period. Available columns:

  • L30D_average_item_price

  • L3M_average_item_price

  • L6M_average_item_price

  • L12M_average_item_price

  • LY12M_average_item_price

  • lifetime_average_item_price

<x>_average_num_items

Decimal

The average number of items during the time period. Available columns:

  • L30D_average_num_items

  • L3M_average_num_items

  • L6M_average_num_items

  • L12M_average_num_items

  • LY12M_average_num_items

  • lifetime_average_num_items

<x>_average_order_value

Decimal

The average order value during the time period. Available columns:

  • L30D_average_order_value

  • L3M_average_order_value

  • L6M_average_order_value

  • L12M_average_order_value

  • LY12M_average_order_value

  • lifetime_average_order_value

<x>_order_frequency

Integer

The count of distinct order IDs that are associated with the customer during the time period. Available columns:

  • L30D_order_frequency

  • L3M_order_frequency

  • L6M_order_frequency

  • L12M_order_frequency

  • LY12M_order_frequency

  • lifetime_order_frequency

<x>_order_revenue

Integer

The total revenue for orders in the time period. Available columns:

  • L30D_order_revenue

  • L3M_order_revenue

  • L6M_order_revenue

  • L12M_order_revenue

  • LY12M_order_revenue

  • lifetime_order_revenue

<x>_preferred_purchase_brand

String

The brand with the greatest number of orders during the time period. Available columns:

  • L30D_preferred_purchase_brand

  • L3M_preferred_purchase_brand

  • L6M_preferred_purchase_brand

  • L12M_preferred_purchase_brand

  • LY12M_preferred_purchase_brand

  • lifetime_preferred_purchase_brand

<x>_preferred_purchase_channel

String

The channel with the greatest number of orders during the time period. Available columns:

  • L30D_preferred_purchase_channel

  • L3M_preferred_purchase_channel

  • L6M_preferred_purchase_channel

  • L12M_preferred_purchase_channel

  • LY12M_preferred_purchase_channel

  • lifetime_preferred_purchase_channel

<x>_preferred_store_id

String

The store ID with the greatest number of orders during the time period. Available columns:

  • L30D_preferred_store_id

  • L3M_preferred_store_id

  • L6M_preferred_store_id

  • L12M_preferred_store_id

  • LY12M_preferred_store_id

  • lifetime_preferred_store_id

<x>_purchase_brands

Integer

The count of the distinct brands a customer interacted with during the time period. Available columns:

  • L30D_purchase_brands

  • L3M_purchase_brands

  • L6M_purchase_brands

  • L12M_purchase_brands

  • LY12M_purchase_brands

  • lifetime_purchase_brands

<x>_purchase_channels

Integer

The count of the distinct channels (online, in store, etc.) that the customer interacted with during during the time period. Available columns:

  • L30D_purchase_channels

  • L3M_purchase_channels

  • L6M_purchase_channels

  • L12M_purchase_channels

  • LY12M_purchase_channels

  • lifetime_purchase_channels

<x>_stores

Integer

The count of the distinct stores that the customer interacted with during the time period. Available columns:

  • L30D_stores

  • L3M_stores

  • L6M_stores

  • L12M_stores

  • LY12M_stores

  • lifetime_stores

<x>_total_items

Integer

The total number of items purchased by the customer during the time period. Available columns:

  • L30D_total_items

  • L3M_total_items

  • L6M_total_items

  • L12M_total_items

  • LY12M_total_items

  • lifetime_total_items

RFM

Amperity calculates RFM scores against transactions that occurred within the last 12 months.

Each of the recency (R), frequency (F), and monetary (M) scores are represented by a number between 0 and 9. The final RFM score is a concatenation of the individual scores: recency first, then frequency, monetary last. The final RFM score is a number between 0 and 999.

Column Name

Data type

PII

Description

L12M_rfm_score

Integer

The RFM score for the customer based on transactions that occurred within the last 12 months. The RFM score is represented as an integer between 0 and 999 and is concatenated using the individual recency, frequency, and monetary scores or is NULL if the customer has zero purchases within the last 12 months.

L12M_recency

Integer

The recency score for customer transactions that occurred within the last 12 months. The recency score is represented as an integer between 0 and 9. Each integer represents 10%:

  • 0: 0-10th percentile

  • 1: 10-20th percentile

  • 2: 20-30th percentile

  • 3: 30-40th percentile

  • 4: 4-50th percentile

  • 5: 5-60th percentile

  • 6: 60-70th percentile

  • 7: 70-80th percentile

  • 8: 80-90th percentile

  • 9: 90-100th percentile

L12M_frequency

Integer

The frequency score for customer transactions that occurred within the last 12 months. The frequency score is represented as an integer between 0 and 9. Each integer represents 10%:

  • 0: 0-10th percentile

  • 1: 10-20th percentile

  • 2: 20-30th percentile

  • 3: 30-40th percentile

  • 4: 4-50th percentile

  • 5: 5-60th percentile

  • 6: 60-70th percentile

  • 7: 70-80th percentile

  • 8: 80-90th percentile

  • 9: 90-100th percentile

L12M_monetary

Integer

The monetary score for customer transactions that occurred within the last 12 months. The monetary score is represented as an integer between 0 and 9. Each integer represents 10%:

  • 0: 0-10th percentile

  • 1: 10-20th percentile

  • 2: 20-30th percentile

  • 3: 30-40th percentile

  • 4: 4-50th percentile

  • 5: 5-60th percentile

  • 6: 60-70th percentile

  • 7: 70-80th percentile

  • 8: 80-90th percentile

  • 9: 90-100th percentile