Crisp Retail Analytics harmonized data model

The Crisp Retail Analytics harmonized data model allows you to access unified product, retailer, store, distribution center, point of sale, purchase order, and inventory data, so you can easily analyze data consolidated from multiple retailers in third-party tools or pipe it to a cloud storage provider.

The following tables display each column in our harmonized model tables and help you understand the data available from each retailer. Because not all retailers provide the same metrics, the following tables indicate which retailers have data for each column and notes when Crisp uses a default value when data is not provided by a retailer. The following legend provides a breakdown of each table indicator.

Legend:

🟢 = Data provided by retailer
🔶 = Data transformed: Indicates that Crisp standardizes data provided in different formats or inserts a default value (listed in the Default Value column) when data is not provided by a retailer for a metric.
🔴 = No data provided: The retailer does not provide data for the corresponding metrics, so values will be null.

dim_calendar

Column	Default Value	Retailers
		Aldi	Amazon	Amazon Vendor Central	Best Buy	Dollar General	Harris Teeter	Harris Teeter Direct	Home Depot	Kroger	Kroger Stratum Direct	REI	Sam's Club
all	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢

dim_distribution_center

Column	Default Value	Retailers
		Aldi	Dollar General	Harris Teeter	Harris Teeter Direct	Kroger	Kroger Stratum Direct	Sam's Club	Target	Tesco	Walmart
distribution_center_id	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
distribution_center	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
distribution_center_address	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
distribution_center_city	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
distribution_center_country	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
distribution_center_fulfillment_method	Buy In Store (BIS)	🔶	🔶	🔶	🔶	🔶	🔶	🔶	🔶	🔶	🔶
distribution_center_key	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
distribution_center_lat_long	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
distribution_center_latitude	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
distribution_center_longitude	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
distribution_center_state	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
distribution_center_type	Distribution Center	🔶	🔶	🟢	🟢	🔶	🟢	🔶	🟢	🔶	🔶
distribution_center_zip	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢

dim_product

Column	Default Value	Retailers
		Aldi	Amazon	Amazon Vendor Central	Best Buy	Dollar General	Harris Teeter	Harris Teeter Direct	Home Depot	Kroger	Kroger Stratum Direct	REI	Sam's Club	Target	Tesco	Walmart	Wegmans	Whole Foods
product_id	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
brand	N/A	🔴	🔴	🟢	🔴	🟢	🟢	🟢	🟢	🔴	🔴	🟢	🟢	🟢	🔴	🔴	🔴	🟢
product	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
product_category	N/A	🔴	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
product_key	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
product_sub _category	N/A	🔴	🔴	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
retailer	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
retailer_product _key	N/A	🔴	🟢	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴
retailer_product _key_2	N/A	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴
supplier	N/A	🟢	🔴	🟢	🔴	🔴	🟢	🟢	🔴	🟢	🔴	🟢	🟢	🟢	🟢	🟢	🟢	🟢
upc	N/A	🔴	🔴	🟢	🟢	🔶*	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🔴	🟢	🟢	🟢

*Note: For Dollar General, sales data is provided at the consumer UPC level, while inventory data is given at the case UPC level. Because of this, each UPC will represent a unique SKU.

dim_retailer

Column	Default Value	Retailers
		Aldi	Amazon	Amazon Vendor Central	Best Buy	Dollar General	Harris Teeter	Harris Teeter Direct	Home Depot	Kroger	Kroger Stratum Direct	REI	Sam's Club	Target	Tesco	Walmart	Wegmans	Whole Foods
retailer_id	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
retailer	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢

dim_store

Column	Default Value	Retailers
		Aldi	Amazon	Amazon Vendor Central	Best Buy	Dollar General	Harris Teeter	Harris Teeter Direct	Home Depot	Kroger	Kroger Stratum Direct	REI	Sam's Club	Target	Tesco	Walmart	Wegmans	Whole Foods
store_id	N/A	🔶	🔶	🔶	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
chain	N/A	🔴	🔶	🔴	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
retailer	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
store	N/A	🔶	🔶	🔶	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
store_address	N/A	🔴	🔶	🔴	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
store_key	N/A	🔶	🔶	🔶	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
store_lat_long	N/A	🔴	🔶	🔴	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
store_latitude	N/A	🔴	🔶	🔴	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
store_longitude	N/A	🔴	🔶	🔴	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
store_market	store country (e.g., US)	🔶	🔶	🔶	🔶	🔶	🔶	🔶	🔶	🔶	🔶	🔶	🟢	🔶	🔶	🔶	🔶	🔶
store_mdm_id	N/A	🔶	🔶	🔶	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
store_number	N/A	🔴	🔶	🔴	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
store_state	N/A	🔴	🔶	🔴	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
store_type	N/A	🔶	🔶	🔶	🔴	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
store_ uniqueness _key	N/A	🔶	🔶	🔶	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
store_zip	N/A	🔴	🔴	🔴	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢

fact_inventory_distribution_center

Column	Default Value	Retailers
		Aldi	Dollar General	Harris Teeter	Harris Teeter Direct	Kroger	Kroger Stratum Direct	Sam's Club	Target	Tesco	Walmart
Granularity		weekly, week end monday	daily	daily	daily	daily	daily	weekly, week end saturday	daily	daily	daily
inventory_id	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
date_key	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
distribution_center_id	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
in_stock_denominator	1	🔶	🔶	🔶	🔶	🔶	🔶	🔶	🔶	🔶	🔶
in_stock_numerator	if on_hand_quantity > 0, set to 1, otherwise set to 0	🔶	🔶	🔶	🔶	🔶	🔶	🔶	🔶	🔶	🔶
on_hand_amount	N/A	🔴	🟢	🔴	🔴	🔴	🟢	🔴	🟢	🟢	🟢
on_hand_quantity	N/A	🔴	🟢	🟢	🟢	🟢	🔴	🟢	🟢	🟢	🟢
on_order_amount	N/A	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴
on_order_quantity	N/A	🔴	🔴	🟢	🟢	🔴	🔴	🟢	🔴	🔴	🔴
product_id	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
retailer_id	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢

fact_inventory_store

Column	Default Value	Retailers
		Best Buy	Dollar General	Harris Teeter	Harris Teeter Direct	Home Depot	Kroger	Kroger Stratum Direct	REI	Sam's Club	Target	Tesco	Walmart
granularity		weekly, week end sunday	daily	daily	daily	daily	daily	daily	weekly, week end saturday	weekly, week end saturday	weekly, week end saturday	weekly, week end saturday	daily
inventory_id	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
date_key	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
authorized_pod	N/A	🔴	🔴	🔴	🔴	🔴	🟢	🔴	🔴	🔴	🔴	🔴	🟢
in_stock_denominator	1	🔶	🔶	🔶	🔶	🔶	🔶	🔶	🔶	🔶	🔶	🔶	🔶
in_stock_numerator	if on_hand_quantity > 0, set to 1, otherwise set to 0	🔶	🟢* 0/100 converted to 0/1	🔶	🔶	🟢 *0, 100, or null converted to 1/0	🔶	🔶	🔶	🔶	🟢 * primarily 0/100, but if > 100 then 1, else divided by 100	🔶	🟢 *true percentage between 0-100 - i.e. 96.32 converted to .9632
in_transit_amount	N/A	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴
in_transit_quantity	N/A	🟢	🟢	🔴	🔴	🔴	🔴	🔴	🟢	🔴	🔴	🔴	🔴
on_hand_amount	N/A	🔴	🟢	🔴	🔴	🟢	🔴	🔴	🟢	🟢	🟢	🔴	🟢
on_hand_quantity	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
on_order_amount	N/A	🔴	🔴	🔴	🔴	🟢	🔴	🔴	🟢	🟢	🔴	🔴	🔴
on_order_quantity	N/A	🟢	🔴	🔴	🟢	🟢	🔴	🔴	🟢	🟢	🔴	🔴	🔴
product_id	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
retailer_id	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
store_id	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢

fact_purchase_order_distribution_center

Column	Retailers
	Harris Teeter Direct	Sam's Club	Walmart
Granularity	daily	weekly, week end saturday	weekly, week end saturday
date_key	🟢	🟢	🟢
distribution_center_id	🟢	🟢	🟢
early_quantity	🟢	🟢	🟢
late_quantity	🟢	🟢	🟢
on_time_quantity	🟢	🟢	🟢
ordered_quantity	🟢	🟢	🟢
overfilled_quantity	🟢	🟢	🟢
product_id	🟢	🟢	🟢
retailer_cogs	🔴	🔴	🔴
retailer_gross_margin	🔴	🔴	🔴
unfilled_quantity	🟢	🟢	🟢

fact_purchase_order_store

Column	Retailer
	Sam's Club
Granularity	weekly, week end saturday
date_key	🟢
early_quantity	🟢
late_quantity	🟢
on_time_quantity	🟢
ordered_quantity	🟢
overfilled_quantity	🟢
product_id	🔴
retailer_cogs	🔴
retailer_gross_margin	🟢
unfilled_quantity	🟢

fact_sales

Column	Retailers
	Aldi	Amazon	Amazon Vendor Central	Best Buy	Dollar General	Harris Teeter	Harris Teeter Direct	Home Depot	Kroger	Kroger Stratum Direct	REI	Sam's Club	Target	Tesco	Walmart	Wegmans	Whole Foods
Granularity	daily	daily	daily	weekly (sun)	daily	weekly (wed)	daily	daily	daily	daily	weekly (sat)	weekly (sat)	daily	weekly (mon)	daily	daily	daily

sales_id	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
date_key	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
is_online	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴	🔴
product_id	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
retailer_id	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
return_amount	🔴	🔴	🔴	🟢	🔴	🔴	🔴	🟢	🔴	🔴	🔴	🟢	🟢	🔴	🔴	🔴	🟢
return_quantity	🔴	🔴	🟢	🟢	🔴	🔴	🔴	🟢	🔴	🔴	🟢	🟢	🟢	🔴	🔴	🔴	🟢
sales_amuont	🔴	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
sales_quantity	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
store_id	🔶	🔶	🔶	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢