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

• Customer orders

• Date differences

• Time period rollups

• RFM

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).
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%”.