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

  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 measures 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

Decimal

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 is based on transactions that occurred within the last 12 months. The RFM score is represented as an integer between “111” and “101010”. This is a concatenated score that uses each of the individual recency, frequency, and monetary scores. The order is recency, then frequency, and then monetary.

For example, you can build an audience that contains your top 20% customers for recency, your top 30% customers for frequency, and your top 10% customers for monetary by setting the L12M RFM Score attribute to “9810” (or “9” for recency, “8” for frequency, and then “10” for monetary).

Find approximate RFM frequency for middle 30 percent.

L12M_recency

Integer

L12M Recency is a score that sorts customers by how recently they purchased during the previous 12 months.

Each RFM score is split into ten percentile groups. The lowest percentile is 1 and the highest percentile is 10. Each percentile represents 10% of the customers who belong to that segment.

  • 10 represents the 90-100th percentile and the customers who have the highest recency, frequency, or monetary scores.

  • 9 represents the 80-90th percentile

  • 8 represents the 70-80th percentile

  • 7 represents the 60-70th percentile

  • 6 represents the 50-60th percentile

  • 5 represents the 40-50th percentile

  • 4 represents the 30-40th percentile

  • 3 represents the 20-30th percentile

  • 2 represents the 10-20th percentile

  • 1 represents the 0-10th percentile and the customers who have the lowest recency, frequency, or monetary scores.

Tip

Combine percentiles to build larger groups of customers. For example 9 and 10 together represent the “top 20%” while 8, 9, and 10 represent the “top 30%”.

L12M_frequency

Integer

L12M Frequency is a score that sorts customers by purchase frequency during the previous 12 months.

Each RFM score is split into ten percentile groups. The lowest percentile is 1 and the highest percentile is 10. Each percentile represents 10% of the customers who belong to that segment.

  • 10 represents the 90-100th percentile and the customers who have the highest recency, frequency, or monetary scores.

  • 9 represents the 80-90th percentile

  • 8 represents the 70-80th percentile

  • 7 represents the 60-70th percentile

  • 6 represents the 50-60th percentile

  • 5 represents the 40-50th percentile

  • 4 represents the 30-40th percentile

  • 3 represents the 20-30th percentile

  • 2 represents the 10-20th percentile

  • 1 represents the 0-10th percentile and the customers who have the lowest recency, frequency, or monetary scores.

Tip

Combine percentiles to build larger groups of customers. For example 9 and 10 together represent the “top 20%” while 8, 9, and 10 represent the “top 30%”.

L12M_monetary

Integer

L12M Monetary is a score that sorts customers by spend amount during the previous 12 months.

Each RFM score is split into ten percentile groups. The lowest percentile is 1 and the highest percentile is 10. Each percentile represents 10% of the customers who belong to that segment.

  • 10 represents the 90-100th percentile and the customers who have the highest recency, frequency, or monetary scores.

  • 9 represents the 80-90th percentile

  • 8 represents the 70-80th percentile

  • 7 represents the 60-70th percentile

  • 6 represents the 50-60th percentile

  • 5 represents the 40-50th percentile

  • 4 represents the 30-40th percentile

  • 3 represents the 20-30th percentile

  • 2 represents the 10-20th percentile

  • 1 represents the 0-10th percentile and the customers who have the lowest recency, frequency, or monetary scores.

Tip

Combine percentiles to build larger groups of customers. For example 9 and 10 together represent the “top 20%” while 8, 9, and 10 represent the “top 30%”.