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
all	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢

dim_distribution_center

Column	Default Value	Retailers
		Aldi	Dollar General	Harris Teeter	Harris Teeter Direct	Kroger	Kroger Stratum Direct	Target	Tesco
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	Target	Tesco	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	Target	Tesco	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	Target	Tesco	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	Amazon Vendor Central	Dollar General	Harris Teeter	Harris Teeter Direct	Kroger	Kroger Stratum Direct	Target	Tesco
Granularity		weekly, week end monday	daily	daily	daily	daily	daily	daily	daily	daily
inventory_id	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
date_key	N/A	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢	🟢
distribution_center_id	1	🟢	🔶	🟢	🟢	🟢	🟢	🟢	🟢	🟢
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	Target	Tesco
granularity		weekly, week end sunday	daily	daily	daily	daily	daily	daily	weekly, week end saturday	weekly, week end saturday	weekly, week end saturday
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	🔶
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
Granularity	daily
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

Granularity
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	Target	Tesco	Wegmans	Whole Foods
Granularity	daily	daily	daily	weekly (sun)	daily	weekly (wed)	daily	daily	daily	daily	weekly (sat)	daily	weekly (mon)	daily	daily

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