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
From the Customer 360 tab, under All Databases, select the menu for the customer 360 database, and then click Edit.
From the Database Editor, click Add Table.
Name the table “Transaction_Attributes_Extended”.
Set the build mode to SQL.
From the Source Table drop-down, select the Transaction_Attributes_Extended table.
Click Next.
Add a table description. This enables a tooltip that is visible from other areas in Amperity.
Under Version History, select Enable table version history.
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:
Also in: Transaction_Attributes |
|
<x>_order_id |
String |
The ID of the order. Available columns:
Also in: Transaction_Attributes |
|
<x>_order_purchase_brand |
String |
The brand of the order made by the customer. Available columns:
Also in: Transaction_Attributes |
|
<x>_order_purchase_channel |
String |
The channel in which the customer’s order was made. Available columns:
Also in: Transaction_Attributes |
|
<x>_order_revenue |
Decimal |
The total revenue for orders at each interval. Available columns:
Also in: Transaction_Attributes |
|
<x>_store_id |
String |
The ID of the store where the customer made their order. This value may be
|
|
<x>_total_items |
Integer |
The total number of items in the order. Available columns:
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:
|
|
<x>_average_num_items |
Decimal |
The average number of items during the time period. Available columns:
|
|
<x>_average_order_value |
Decimal |
The average order value during the time period. Available columns:
|
|
<x>_order_frequency |
Integer |
The count of distinct order IDs that are associated with the customer during the time period. Available columns:
|
|
<x>_order_revenue |
Decimal |
The total revenue for orders in the time period. Available columns:
|
|
<x>_preferred_purchase_brand |
String |
The brand with the greatest number of orders during the time period. Available columns:
|
|
<x>_preferred_purchase_channel |
String |
The channel with the greatest number of orders during the time period. Available columns:
|
|
<x>_preferred_store_id |
String |
The store ID with the greatest number of orders during the time period. Available columns:
|
|
<x>_purchase_brands |
Integer |
The count of the distinct brands a customer interacted with during the time period. Available columns:
|
|
<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:
|
|
<x>_stores |
Integer |
The count of the distinct stores that the customer interacted with during the time period. Available columns:
|
|
<x>_total_items |
Integer |
The total number of items purchased by the customer during the time period. Available columns:
|
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.