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.

Important

Database tables for transactions must be configured in the following order:

1. Unified Itemized Transactions

2. Unified Transactions

3. Transaction Attributes Extended (this topic)

4. Transaction Attributes

The Unified Product Catalog table is optional and may be configured for use with building queries, database tables, and other non-audience workflows. Your brand’s product catalog must be integrated into the Unified Itemized Transactions table before you can use product catalog attributes to build audiences, segments, and campaigns.

Add table

The Transaction Attributes Extended table is a required table for the customer 360 database and is added using a SQL template.

To add the Transaction Attributes Extended table

1. Open your customer 360 database in edit mode, and then click Add Table.

2. Name the table “Transaction_Attributes_Extended” (with underscores).

3. Set the build mode to SQL, and then select “Transaction Attributes Extended” from the Apply template drop-down.

4. Review the list of optional attributes to determine if any of those should be enabled for your tenant.

5. Click Next to validate the SQL.

6. Select “Transaction Attributes Extended” from the Table Semantics drop-down, and then add a description for the table. (The description is available as a tooltip in other locations in Amperity.

7. Click Save.

Optional attributes

You can extend the set of attributes that are available from the Transaction Attributes Extended table to include any of the following:

• Cancellations

• Net order revenue

• Order costs

• Returns

• RFM

Note

These attributes are built into the SQL templates for the Transaction Attributes Extended table, but are commented out. Some sets of attributes require updates be made to both tables, while others may only require updates be made to one table. The steps for each set will link to a topic section that descibes the updates that should be made to the Unified Transactions table.

Cancellations

To add extended attributes for cancelled items

1. Open the Transaction Attributes Extended table in the SQL editor.

2. Uncomment the augmented_cancellations block of SQL.

3. Uncomment the can_attrs block of SQL.

4. Find the SELECT statement at the end of the SQL template, and then uncomment the following sets of calculated intervals:

   • Order Cancelled Frequency include 30 days, 3 months, 6 months, 12 months, 12-24 months, and lifetime.

   • Order Cancelled Revenue include 30 days, 3 months, 6 months, 12 months, 12-24 months, and lifetime.

   • Total Cancelled Items include 30 days, 3 months, 6 months, 12 months, 12-24 months, and lifetime.

   Note

   These attributes are not grouped together within the SELECT statement.

5. Uncomment the LEFT JOIN at the end of the SELECT statement:

   LEFT JOIN can_attrs ON attrs.amperity_id = can_attrs.amperity_id
   

6. Validate the SQL, and then click Next, and then click Save.

Net order revenue

Net order revenue is the revenue for all items in an order, including returned and cancelled items, but not including discounted amounts.

To add extended attributes for net order revenue

1. Required. Add Net Order Revenue to the Unified Transactions table.

2. Open the Transaction Attributes Extended table in the SQL editor.

3. Find the section named “attrs”, which contains a series of commented-out extended attributes for net order revenue.

4. Uncomment one (or more) of the following extended attributes:

   ,FIRST(txn.first_order.net_order_revenue IGNORE NULLS) AS first_net_order_revenue
   ,FIRST(txn.second_order.net_order_revenue IGNORE NULLS) AS second_net_order_revenue
   ,FIRST(txn.latest_order.net_order_revenue IGNORE NULLS) AS latest_net_order_revenue
   ,MAX(IF(txn.net_order_revenue > 0, txn.net_order_revenue, NULL)) AS lifetime_largest_net_order_value
   ,SUM(txn.net_order_revenue) AS lifetime_net_order_revenue
   ,SUM(IF(txn.ly12m, txn.net_order_revenue, 0)) AS LY12M_net_order_revenue
   ,SUM(IF(txn.l12m, txn.net_order_revenue, 0)) AS L12M_net_order_revenue
   ,SUM(IF(txn.l30d, txn.net_order_revenue, 0)) AS L30D_net_order_revenue
   ,SUM(IF(txn.l3m, txn.net_order_revenue, 0)) AS L3M_net_order_revenue
   ,SUM(IF(txn.l6m, txn.net_order_revenue, 0)) AS L6M_net_order_revenue
   

   Note

   These attributes are grouped together within the “attrs” section.

5. Find the SELECT statement at the end of the SQL template, and then uncomment the following sets of calculated intervals:

   • Average Net Item Price include 30 days, 3 months, 6 months, 12 months, 12-24 months, and lifetime.

   • Largest Net Order Value includes lifetime.

   • Net Order Revenue include first, latest, and second orders along with 12-24 months and lifetime.

   • Net Order Value include 30 days, 3 months, 6 months, 12 months, 12-24 months, and lifetime.

   Note

   These attributes are not grouped together within the SELECT statement.

6. Validate the SQL, and then click Next.

7. Click Save.

Order costs

Order cost represents the total cost of goods sold (COGS) for a single transaction, minus returns, cancellations, and discounts.

To add extended attributes for order cost

1. Required. Add Order Cost to the Unified Transactions table.

2. Open the Transaction Attributes Extended table in the SQL editor.

3. Find the section named “attrs”, which contains a series of commented-out extended attributes for order costs.

4. Uncomment one (or more) of the following extended attributes:

   ,FIRST(txn.first_order.order_cost IGNORE NULLS) AS first_order_cost
   ,FIRST(txn.latest_order.order_cost IGNORE NULLS) AS latest_order_cost
   ,FIRST(txn.second_order.order_cost IGNORE NULLS) AS second_order_cost
   ,SUM(IF(txn.l30d, txn.order_cost, 0)) AS L30D_order_cost
   ,SUM(IF(txn.l3m, txn.order_cost, 0)) AS L3M_order_cost
   ,SUM(IF(txn.l6m, txn.order_cost, 0)) AS L6M_order_cost
   ,SUM(IF(txn.l12m, txn.order_cost, 0)) AS L12M_order_cost
   ,SUM(IF(txn.ly12m, txn.order_cost, 0)) AS LY12M_order_cost
   ,SUM(txn.order_cost) AS lifetime_order_cost
   

   Note

   These attributes are grouped together within the “attrs” section.

5. Find the SELECT statement at the end of the SQL template, and then for each of the attributes enabled within “attrs”, uncomment the line to add the extended attribute to the Transaction Attributes Extended table:

   ,CAST(attrs.first_order_cost AS decimal(38,2)) AS first_order_cost
   ,CAST(attrs.latest_order_cost AS decimal(38,2)) AS latest_order_cost
   ,CAST(attrs.second_order_cost AS decimal(38,2)) AS second_order_cost
   ,CAST(attrs.L30d_order_cost AS decimal(38,2))
   ,CAST(attrs.l3m_order_cost AS decimal(38,2))
   ,CAST(attrs.l6m_order_cost AS decimal(38,2))
   ,CAST(attrs.l12m_order_cost AS decimal(38,2))
   ,CAST(attrs.ly12m_order_cost AS decimal(38,2))
   ,CAST(attrs.lifetime_order_cost AS decimal(38,2))
   

   Note

   These attributes are not grouped together within the SELECT statement.

6. Validate the SQL, and then click Next.

7. Click Save.

Returns

To add extended attributes for returned items

1. Open the Transaction Attributes Extended table in the SQL editor.

2. Uncomment the augmented_returns block of SQL.

3. Uncomment the ret_attrs block of SQL.

4. Find the SELECT statement at the end of the SQL template, and then uncomment the following sets of calculated intervals:

   • Order Returned Frequency include 30 days, 3 months, 6 months, 12 months, 12-24 months, and lifetime.

   • Order Returned Revenue include 30 days, 3 months, 6 months, 12 months, 12-24 months, and lifetime.

   • Total Returned Items include 30 days, 3 months, 6 months, 12 months, 12-24 months, and lifetime.

   Note

   These attributes are not grouped together within the SELECT statement.

5. Uncomment the LEFT JOIN at the end of the SELECT statement:

   LEFT JOIN ret_attrs ON attrs.amperity_id = ret_attrs.amperity_id
   

6. Validate the SQL, and then click Next, and then click Save.

RFM

RFM is a method used to analyze customer value that represents three dimensions:

• Recency. How recently did the customer purchase?

• Frequency. How often does the customer purchase?

• Monetary. How much does the customer spend?

A score for recency, frequency, and monetary is assigned on a scale of 1-10, and is then aggregated into a combined RFM score that is assigned on a scale of “111” (the lowest possible RFM score) to “101010” (the highest possible RFM score, or “three ‘10’ scores”). Maximum scores represent preferred behaviors.

To add extended attributes for RFM

1. Open the Transaction Attributes Extended table in the SQL editor.

2. Uncomment the l12m_rfm block of SQL. This section uses the NTILE() function to calculate recency, frequency, and monetary scores as deciles.

3. In the attrs block of SQL, uncomment the calculations for RFM attributes:

   ,FIRST(rfm.recency) AS L12M_recency
   ,FIRST(rfm.frequency) AS L12M_frequency
   ,FIRST(rfm.monetary) AS L12M_monetary
   ,FIRST(rfm.score) AS L12M_rfm_score
   

   and then uncomment the LEFT JOIN in the same section:

   LEFT JOIN l12m_rfm rfm ON txn.amperity_id = rfm.amperity_id
   

4. Find the SELECT statement at the end of the SQL template, and then uncomment the following extended attributes:

   ,attrs.L12M_rfm_score
   ,attrs.L12M_recency
   ,attrs.L12M_frequency
   ,attrs.L12M_monetary
   

5. Validate the SQL, and then click Next, and then 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 Net Item Price	Decimal		The average net item price during the time period, minus returns, cancellations, and discounts. Available columns: L30D Average Net Item Price L3M Average Net Item Price L6M Average Net Item Price L12M Average Net Item Price LY12M Average Net Item Price Lifetime Average Net Item Price Note These attributes must be enabled in the SQL template for the Transaction Attributes Extended table.
<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> Largest Net Order Value	Decimal		The total value for the largest orders in the time period, minus returns, cancellations, and discounts. Available columns: First Largest Net Order Value Second Largest Net Order Value Latest Largest Net Order Value LY12M Largest Net Order Value Lifetime Largest Net Order Value Note These attributes must be enabled in the SQL template for the Transaction Attributes Extended table.
<X> Net Order Revenue	Decimal		The net order revenue for orders in the time period, minus returns, cancellations, and discounts. Available columns: L30D Order Revenue L3M Order Revenue L6M Order Revenue L12M Order Revenue LY12M Order Revenue Lifetime Order Revenue Note These attributes must be enabled in the SQL template for the Transaction Attributes Extended table.
<X> Net Order Value	Decimal		The total value for orders in the time period, minus returns, cancellations, and discounts. Available columns: L30D Net Order Value L3M Net Order Value L6M Net Order Value L12M Net Order Value LY12M Net Order Value Lifetime Net Order Value Note These attributes must be enabled in the SQL template for the Transaction Attributes Extended table.
<X> Order Cost	Datetime		The cost for an order during the time period. Available columns: First Order Cost Second Order Cost Latest Order Cost L30D Average Order Cost L3M Average Order Cost L6M Average Order Cost L12M Average Order Cost LY12M Average Order Cost Lifetime Average Order Cost
<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 Cancelled Frequency	Integer		The count of distinct order IDs that are associated with cancelled items during the time period. Available columns: L30D Order Cancelled Frequency L3M Order Cancelled Frequency L6M Order Cancelled Frequency L12M Order Cancelled Frequency LY12M Order Cancelled Frequency Lifetime Order Cancelled Frequency Note These attributes must be enabled in the SQL template for the Transaction Attributes Extended table.
<X> Order Returned Frequency	Integer		The count of distinct order IDs that are associated with returned items during the time period. Available columns: L30D Order Returned Frequency L3M Order Returned Frequency L6M Order Returned Frequency L12M Order Returned Frequency LY12M Order Returned Frequency Lifetime Order Returned Frequency Note These attributes must be enabled in the SQL template for the Transaction Attributes Extended table.
<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> Order Cancelled Revenue	Decimal		The total revenue for cancelled items in the time period. Available columns: L30D Order Cancelled Revenue L3M Order Cancelled Revenue L6M Order Cancelled Revenue L12M Order Cancelled Revenue LY12M Order Cancelled Revenue Lifetime Order Cancelled Revenue Note These attributes must be enabled in the SQL template for the Transaction Attributes Extended table.
<X> Order Returned Revenue	Decimal		The total revenue for returned items in the time period. Available columns: L30D Order Returned Revenue L3M Order Returned Revenue L6M Order Returned Revenue L12M Order Returned Revenue LY12M Order Returned Revenue Lifetime Order Returned Revenue Note These attributes must be enabled in the SQL template for the Transaction Attributes Extended table.
<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
<X> Total Cancelled Items	Integer		The total number of items cancelled by the customer during the time period. Available columns: L30D Total Cancelled Items L3M Total Cancelled Items L6M Total Cancelled Items L12M Total Cancelled Items LY12M Total Cancelled Items Lifetime Total Cancelled Items Note These attributes must be enabled in the SQL template for the Transaction Attributes Extended table.
<X> Total Returned Items	Integer		The total number of items returned by the customer during the time period. Available columns: L30D Total Returned Items L3M Total Returned Items L6M Total Returned Items L12M Total Returned Items LY12M Total Returned Items Lifetime Total Returned Items Note These attributes must be enabled in the SQL template for the Transaction Attributes Extended table.

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