The Retail predefined schema
When you enable the retail schema, CX Audience will create the following tables and pre-calculated attributes for your account.
Orders table
The orders table has the following columns:
- Customer ID
- Email address
- Mobile number
- Total amount
- Order date
- Order ID
- Purchase channel
Order line table
The order line table has the following columns:
- Item quantity
- Product ID
- Order line price
- Order line ID
- Order line discount amount
- Order ID
Product lookup table
The product lookup table has the following columns:
- Product name
- Product ID
- Product category 1
- Product category 2
- Product price
Pre-calculated attributes
Review the table below to see how each pre-calculated attribute is calculated, the columns it requires to calculate the attribute, and how the attribute is displayed in the Retail dashboard.
|
Attribute name |
How it's calculated |
Columns required |
Retail dashboard visualization |
| Last purchase date | The date of the most recent order. | CREATED_AT | None |
| Last purchase amount | The amount from the Total column of the Orders table for the most recent order. | TOTAL , CREATED_AT | Binned Histogram |
| Max purchase amount | The highest purchase amount in the Total column of the Orders table. | TOTAL | Binned Histogram |
| Last purchase channel | The purchase channel from the most recent purchase. | PURCHASE_CHANNEL | Pie Chart |
| Lifetime order count | The total number of orders. | ORDER_ID , CUSTOMER_ID | Binned Histogram |
| Order count 12 mo | The number of orders over the past 12 months. | ORDER_ID , CREATED_AT | Binned Histogram |
| Order count 9 mo | The number of orders over the past 9 months. | ORDER_ID , CREATED_AT | Binned Histogram |
| Order count 6 mo | The number of orders over the past 6 months. | ORDER_ID , CREATED_AT | Binned Histogram |
| Order count 3 mo | The number of orders over the past 3 months. | ORDER_ID , CREATED_AT | Binned Histogram |
| Order count 1 mo | The number of orders over the past month. | ORDER_ID , CREATED_AT | Binned Histogram |
| Spent lifetime | The sum of the Total column from the Orders table. | TOTAL | Binned Histogram |
| Spent 12 mo | The sum of the Total column from the Orders table over the past 12 months. | TOTAL, CREATED_AT | Binned Histogram |
| Spent 9 mo | The sum of the Total column from the Orders table over the past 9 months. | TOTAL, CREATED_AT | Binned Histogram |
| Spent 6 mo | The sum of the Total column from the Orders table over the past 6 months. | TOTAL, CREATED_AT | Binned Histogram |
| Spent 3 mo | The sum of the Total column from the Orders table over the past 3 months. | TOTAL, CREATED_AT | Binned Histogram |
| Spent 1 mo | The sum of the Total column from the Orders table over the past month. | TOTAL, CREATED_AT | Binned Histogram |
| Frequent shopper | Customers are considered frequent shoppers if they've been a customer for at least three months and their average number of purchases each month is greater than 1. | ORDER_ID , CREATED_AT | Pie Chart |
| Highly frequent shopper | Customers are considered frequent shoppers if they've been a customer for at least three months and their average number of purchases each month is greater than 2. | ORDER_ID , CREATED_AT | Pie Chart |
| Avg order spend | The average amount the customer spends for each purchase. | TOTAL , ORDER_ID | Binned Histogram |
| Highest spent category 1 | The order item in product category 1 that the customers spends the highest amount on. | Pie Chart | |
| Highest spent category 2 | The order item in product category 2 that the customers spends the highest amount on. | Pie Chart |