Individual BU Retail summary

individual_business_unit_id

Identifies unique association between an individual and business unit.

BIGINT

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.

BIGINT

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.

NVARCHAR(50)

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.

NUMERIC(20, 6)

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.

NUMERIC(20, 6)

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.

NUMERIC(20, 6)

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

NUMERIC(20, 6)

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.

INT

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

INT

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.

NUMERIC(20, 6)

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.

INT

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.

NUMERIC(20, 6)

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.

NVARCHAR(50)

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.

BIGINT

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.

BIGINT

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.

DECIMAL(4, 1)

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.

DECIMAL(4, 1)

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.

BIGINT

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.

DECIMAL(4, 1)

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.

DECIMAL(4, 1)

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

DATETIME

(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).

NUMERIC(20, 6)

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

BIGINT

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.

INT

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

DATETIME

(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).

NUMERIC(20, 6)

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

BIGINT

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.

INT

Calculate(DiffDays( CurrentDate(  ), asdate(last_purchase_date )))