Column name | Column definition | Business rule |
|---|
individual_business_unit_id
| Identifies unique association between an individual and business unit. | Distinct individual_business_unit_id from TRANSACTION_DETAIL_SUMMARY. |
individual_id
| Uniquely identifies the individual record that can be an individual, a prospect, or a lead, and represents an integrated view of the individual. | individual_id from TRANSACTION_DETAIL_SUMMARY of the same group.
|
business_unit_code
| A code that uniquely identifies the brand and region within the organization. Default value is enterprise. | business_unit_code from TRANSACTION_DETAIL_SUMMARY of the same group.
|
net_sales_0_12_months_amount
| Net spend (amount of sales minus amount of returns and price adjustments) within specified time period for this individual_business_unit_id. | sum(TRANSACTION_DETAIL_SUMMARY.global_net_sales_amount) where transaction_detail_status_code NOT in (demand, cancel) and transaction_datetime is within specified time period for this individual_business_unit_id.
|
net_sales_13_24_months_amount
| Net spend (amount of sales minus amount of returns and price adjustments) within specified time period for this individual_business_unit_id. | sum(TRANSACTION_DETAIL_SUMMARY.global_net_sales_amount) where transaction_detail_status_code NOT in (demand, cancel) and transaction_datetime is within specified time period for this individual_business_unit_id.
|
net_sales_25_36_months_amount
| Net spend (amount of sales minus amount of returns and price adjustments) within specified time period for this individual_business_unit_id. | sum(TRANSACTION_DETAIL_SUMMARY.global_net_sales_amount) where transaction_detail_status_code NOT in (demand, cancel) and transaction_datetime is within specified time period for this individual_business_unit_id.
|
lifetime_value
| Total of net sales for this individual_business_unit_id (returns & cancelations included in calculations). | sum(TRANSACTION_DETAIL_SUMMARY.global_net_sales_amount) where transaction_detail_status_code NOT in (demand, cancel) for this individual_business_unit_id.
|
lifetime_net_units_sold_count
| Total number of purchased items for this individual_business_unit_id (returns & cancelations included in calculations). Does not represent item's quantity. | Count (*) of records from TRANSACTION_DETAIL_SUMMARY where transaction_detail_status_code NOT in (demand, cancel, price_adj) for this individual_business_unit_id. |
total_purchase_transactions_count
| Total number of purchase transactions for this individual_business_unit_id (returns & cancelations are NOT included in calculations). | Count (*) of records from TRANSACTION_HEADER where transaction_type_code in (sale, confirmed) for this individual_business_unit_id by join from TRANSACTION_CUSTOMER_MAP on individual_business_unit_id. |
total_returns_amount
| Sum of all transactions with a status code of RETURNED. | sum(TRANSACTION_DETAIL_SUMMARY.global_net_sales_amount where transaction_detail_status_code = returned for this individual_business_unit_id.
|
total_returns_count
| Total number of returned items by this individual_business_unit_id. This is a number of return transaction detail records and not a quantity of products returned. | Count (*) of records from TRANSACTION_DETAIL_SUMMARY where transaction_detail_status_code = returned for this individual_business_unit_id. |
average_order_value
| Average order value (AOV) tracks the average dollar amount spent each time a customer makes a purchase. | lifetime_value / lifetime_net_units_sale_count.
|
primary_shopping_channel
| The most frequent channel used by this individual_business_unit_id for past 24 months. | transaction_location_type_code from TRANSACTION_DETAIL_SUMMARY with the most number of records for this individual_business_unit_id within past 24 months. For high volume clients can be adjust to 12 month period.
|
primary_store_id
| Store of loyalty/most shopped store (either web or B&M); the store with a max number of transactions for the individual_business_id. | transaction_location_id with the max count (*) of records from TRANSACTION_DETAIL_SUMMARY for this individual_business_unit_id. Join to LOCATION and check location_status_code if <> open, get next store with the most transactions.
|
client_primary_store_id
| Store ID supplied by the client's customer feed. | Join to PARTY_PROFILE on individual_business_unit_id and select source_main_location_id from the record with max(source_rec_create_datetime) for this individual_business_unit_id. Join to LOCATION and check location_status_code if = open, else set to NULL. |
distance_to_primary_store_miles
| Distance to primary store, in miles, from the ZIP code of this individual_business_unit_id. | Calculate a distance in miles between std_address in LOCATION.location_id = of primary_store_id and INDIVIDUAL_BU_GOLDEN_RECORD_SUMMARY.address_id. |
distance_to_primary_store_km
| Distance to primary store, in kilometers, from the ZIP code of this individual_business_unit_id. | Calculate a distance in kms between std_address in LOCATION.location_id = of primary_store_id and INDIVIDUAL_BU_GOLDEN_RECORD_SUMMARY.address_id. |
closest_store_id
| Open B&M store that is located within the shortest distance to individual_business_unit_id. | Pick LOCATION.location_id where LOCATION.location_status_code = open with shortest distance from INDIVIDUAL_BU_GOLDEN_RECORD_SUMMARY.address_id (based on LOCATION.latitude & LOCATION.longitude & ADDRESS.longitude & ADDRESS.longitude where ADDRESS.address_id = INDIVIDUAL_BU_GOLDEN_RECORD_SUMMARY.address_id. |
distance_to_closest_store_miles
| Distance to closest store, in miles, from the ZIP code of an individual. | Calculate a distance in miles between LOCATION.location_id of currently open store with std_postal_code_1 that is within the shortest distance from INDIVIDUAL_BU_GOLDEN_RECORD_SUMMARY.postal_code_1 individual_business_unit_id. |
distance_to_closest_store_km
| Distance to closest store, in kilometers, from the ZIP code of an individual. | Calculate a distance in kms between LOCATION.location_id of currently open store with std_postal_code_1 that is within the shortest distance from INDIVIDUAL_BU_GOLDEN_RECORD_SUMMARY.postal_code_1 individual_business_unit_id. |
first_purchase_datetime
| First purchase date; the date of earliest known purchase transaction (in-store purchased items & eComm shipped items flagged as sale). | (min)TRANSACTION_DETAIL_SUMMARY.transaction_datetime where transaction_detail_status_code in (sale, confirmed) and global_net_sales_amount > 0 for this individual_business_unit_id.
|
first_purchase_amount
| Amount of the earliest known purchase transaction (in-store purchased items & eComm shipped items flagged as sale). | sum(TRANSACTION_DETAIL_SUMMARY.global_net_sales_amount) that share the same transaction_id with (min)transaction_datetime where transaction_detail_status_code in (sale, confirmed) for this individual_business_unit_id.
|
first_purchase_location_id
| The location ID (web or B&M) of the earliest known purchase transaction (in-store purchased items & eComm shipped items flagged as sale). | Select transaction_location_id of the record from TRANSACTION_DETAIL_SUMMARY with min(transaction_datetime) where transaction_detail_status_code in (sale, confirmed) for this individual_business_unit_id. |
days_since_first_purchase_count
| Number of days since the first_purchase_date for this individual_business_unit_id. | Calculate(DiffDays( CurrentDate( ), asdate(first_purchase_date )))
|
last_purchase_datetime
| Most recent purchase Date; the date of latest known purchase transaction (in-store purchased items & eComm shipped items flagged as sale). | (max)TRANSACTION_DETAIL_SUMMARY.transaction_datetime where transaction_detail_status_code in (sale, confirmed) and global_net_sales_amount > 0 for this individual_business_unit_id.
|
last_purchase_amount
| Amount of the most recent known purchase transaction (in-store purchased items & eComm shipped items flagged as sale). | sum(TRANSACTION_DETAIL_SUMMARY.global_net_sales_amount) that share the same transaction_id with (max)transaction_datetime where transaction_detail_status_code in (sale, confirmed) for this individual_business_unit_id.
|
last_purchase_location_id
| The location ID (web or B&M) of the most recent known purchase transaction (in-store purchased items & eComm shipped items flagged as sale). | Select transaction_location_id of the record from TRANSACTION_DETAIL_SUMMARY with max(transaction_datetime) where transaction_detail_status_code in (sale, confirmed) for this individual_business_unit_id. |
days_since_last_purchase_count
| Number of days since the last_purchase_date for this individual_business_unit_id. | Calculate(DiffDays( CurrentDate( ), asdate(last_purchase_date )))
|
