Retail summaries

Transaction Detail summary

dbo.TRANSACTION_DETAIL_SUMMARY

Contains flattened transaction records with information about the individual, customer profile, account to whom the transaction has been attributed, and expanded Product and Location information that is relevant to this transaction. Records must be filtered on transaction_detail_status_code, possible values are: RETURNED, SOLD, DEMAND, PRICE_ADJ.

• To retrieve any information for fulfilled purchased items, select records where transaction_detail_status_code  = SOLD.

• To retrieve any information for web orders (Instagram, SFMC, any eComm), select records where transaction_detail_status_code = DEMAND.

• To retrieve any information for returned items, select records where transaction_detail_status_code  = RETURNED.

Incremental load (we are loading only new/updated records; we do not re-calculate the entire aggregate data set each time this runs) 

Individual BU Retail summary

dbo.INDIVIDUAL_BU_RETAIL_SUMMARY

Aggregated summaries of retail transactions at the unique (Individual + Business Unit) grain. Some of the typical aggregates: first and last purchase amounts, locations and dates, favorite and closest stores as well as distance to these stores, preferred shopping channel, and lifetime value.

Examples:

• Individual ID+BU: 12345000001

• Lifetime value: 300

• Days since first purchase count: 15 

Full refresh

Individual BU Product Category summary

dbo.INDIVIDUAL_BU_PRODUCT_CATEGORY_SUMMARY

Aggregated summaries of retail transactions by Product Category at the unique (Individual + Business Unit + Product Category) grain. Some of the typical aggregates: first and last purchase amounts and dates, lifetime value, and number of purchases/returns.

Examples:

Individual ID+BU+ProductCategory

Individual ID+BU+Product Categ Code: 12345000001WSHOE

lifetime_value: 100

first_purchase_date: 2019/03/15 

Full refresh