Individual BU Product Category summary

The following table delineates the Individual BU Product Category summary.

All amounts are calculated based on global currency amounts (NOT local).

Table name: dbo.INDIVIDUAL_BU_PRODUCT_CATEGORY_SUMMARY

Column name	Column definition	Business rule
`individual_bu_product_category_id`	Uniquely identifies a record in individual product category summary that contains aggregates specific to the association of individual, business unit, and product category.	Concatenation of distinct combination of `individual_business_unit_id` from `INDIVIDUAL_BUSINESS_UNIT` and `product_category_code` from `PRODUCT_CATEGORY_LOOKUP` joined to `TRANSACTION_DETAIL_SUMMARY`.
`individual_business_unit_id`	Identifies unique association between an individual and business unit.	Distinct `individual_business_unit_id` from `INDIVIDUAL_BUSINESS_UNIT` joined to `TRANSACTION_DETAIL_SUMMARY`.
`product_category_code`	Code that defines product's category. The range of products sold by a retailer is broken down into discrete groups of similar or related products; examples of grocery categories might be: detergents, toothpastes.	`product_category_code` portion of `individual_bu_product_category_id`, select from `TRANSACTION_DETAIL_SUMMARY`.
`product_category_desc`	Product category description.	Select 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` portion of `individual_business_unit_id` joined to `TRANSACTION_DETAIL_SUMMARY`.
`business_unit_code`	A code that uniquely identifies the brand and region within the organization. Default value is `enterprise`.	`business_unit_code` from `INDIVIDUAL_BUSINESS_UNIT` based on a distinct combination of `individual_id` and `business_unit_code` joined to `TRANSACTION_DETAIL_SUMMARY`.
`lifetime_value`	Total of net sales for this `individual_business_unit_id` per distinct product category of the record (returns & cancelations included in calculations).	`sum(TRANSACTION_DETAIL_SUMMARY.global_net_sales_amount` where `transaction_detail_status_code` NOT in (`demand`, `canceled`) for this `individual_business_unit_id` for this `product_category_code`.
`lifetime_net_units_sold_count`	Total number of purchased items for this `individual_business_unit_id` per distinct product category of the record (returns & cancelations included in calculations).	Count (*) of records from `TRANSACTION_DETAIL_SUMMAR`Y where `transaction_detail_status_code` NOT in (`demand`, `canceled`) for this `individual_business_unit_id` for this `product_category_code`.
`first_purchase_datetime`	First purchase date; the date of earliest known purchase transaction per distinct product category of the record (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` for this `product_category_code`.
`first_purchase_amount`	Amount of the earliest known purchase transaction per distinct product category of the record (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` for this `product_category_code`.
`days_since_first_purchase_count`	Number of days since the `first_purchase_date` for this `individual_business_unit_id` per distinct product category of the record.	`Calculate(DiffDays( CurrentDate( ), asdate(first_purchase_date )))` for this `product_category_code`.
`last_purchase_datetime`	Most recent purchase date; the date of latest known purchase transaction per distinct product category of the record (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` for this `product_category_code`.
`last_purchase_amount`	Amount of the most recent known purchase transaction per distinct product category of the record (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` for this `product_category_code`.
`days_since_last_purchase_count`	Number of days since the `last_purchase_date` for this i`ndividual_business_unit_id` per distinct product category of the record.	`Calculate(DiffDays( CurrentDate( ), asdate(last_purchase_date )))` for this `product_category_code`.
`total_returns_amount`	Sum of all transactions with a status code of `RETURNED` per distinct product category of the record.	`sum(TRANSACTION_DETAIL_SUMMARY.global_net_sales_amount` where `transaction_detail_status_code` = `RETURNED` for this `individual_business_unit_id` for this `product_category_code`.
`total_returns_count`	Total number of returned items by this `individual_business_unit_id` per distinct product category of the record.	`sum(coalesce(casewhen TRANSACTION_DETAIL_SUMMARY.transaction_detail_status_code = RETURNED then coalesce(TRANSACTION_DETAIL_SUMMARY.product_qty, 0)end, 0))as total_returns_qty`
`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` per distinct product category of the record.	`sum(TRANSACTION_DETAIL_SUMMARY.global_net_sales_amount)` where `transaction_detail_status_code` NOT in (`demand`, `canceled`) and `transaction_datetime` is within specified time period for this `individual_business_unit_id` for this `product_category_code`.
`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` per distinct product category of the record.	`sum(TRANSACTION_DETAIL_SUMMARY.global_net_sales_amount)` where `transaction_detail_status_code` NOT in (`demand`, `canceled`) and `transaction_datetime` is within specified time period for this `individual_business_unit_id` for this `product_category_code`.
`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` per distinct product category of the record.	`sum(TRANSACTION_DETAIL_SUMMARY.global_net_sales_amount)` where `transaction_detail_status_code` NOT in (`demand`, `canceled`) and `transaction_datetime` is within specified time period for this `individual_business_unit_id` for this `product_category_code`.