Unified Wholesale Schema (UWS)

This page provides a detailed description of the Unified Wholesale Schema (UWS) within the Daasity Data Model and defines each table and column in this schema

Overview

The Unified Wholesale Schema (UWS) is a core data model within the Daasity transformation module that helps accelerate development of analytical capability by normalizing the sell-in, sell-out and inventory data that is often provided by retailers in a variety of formats and layouts.

The normalized schema was designed to help you in several key areas:

1. Utilize the Daasity Wholesale feature to easily convert your raw retailer source files into a format that can be transformed into the UWS schema

2. Understand overall consumer demand across all retailers at both the item level and the geographic level

3. Track your inventory across all retailers to determine your supply chain requirements

4. Understand the velocity of your sales by combining sell-in and sell-out

5. Act as a staging source for ingestion into our Unified Order Schema (UOS) to combine with your digital and owned retail consumer sales.

NOTE: This schema is still being developed and minor changes should be expected

Entity Relationship Diagram (ERD)

Click on this link to view the ERD for the Unified Wholesale Schema (UTS) illustrating the different tables and keys to join across tables.

Unified Wholesale Schema Tables

• Sell-out by Day, SKU and Location [uws.sell_out_day_sku_location]

• Sell-out by Day and Location [uws.sell_out_day_location]

• Sell-in [uws.sell_in_sku]

• SKU Inventory [uws.sku_inventory]

• Current SKU Inventory [uws.current_sku_inventory]

• SKU Mapping [uws.sku_mapping]

• Location Mapping [uws.location_mapping]

TIP: All the tables contain the following fields which can be used to track the data flow from the source system to the integration schema within the database and then to UWS, our normalized wholesale schema

• __shop_id: an identifier for the retailer

• __uws_integration_id: the identifier in the Daasity platform for the source data

• __loaded_at: defines when the record was last loaded into this table

• __synced_at: defines when the record was last replicated from the source system into the integration tables

Sell-out by Day, SKU and Location

Purpose: Enables you to compare retailer sell-out data at the day level (either provided or calculated), SKU level by using the retailer provided sales/SKU data and Location.

For retailer that cannot (or do not) provide data at the SKU/Location level this table will default to the SKU level in order to enable transformation into the UOS data model

Table Name: uws.sell_out_day_sku_location

Column	Description
id	Unique id for each row
retailer	Friendly name of the retailer
country_code	Two digit code for the country of the retailer
location_id	Identifier of the location if the sales channel is retail
start_date	Start date of the reporting period
end_date	End date of the reporting period
order_date	Date of the order
original_currency	Currency of the supplied source data
currency_conversion_rate	Conversion rate applied to put into platform currency
converted_currency	Currency of the converted sales
listing_sku	Standard SKU for the item level data
item_description	Description of the item / item name
sales_channel	Channel of the sales - usually online or retail
sales_dollars_daily	If data provided daily then raw data otherwise calculation of weekly to daily data
sales_units_daily	If data provided daily then raw data otherwise calculation of weekly to daily data
sales_price_daily	Calculated value of the daily price
refunds_dollars_daily	If data provided daily then raw data otherwise calculation of weekly to daily data
refunds_units_daily	If data provided daily then raw data otherwise calculation of weekly to daily data
refunds_price_daily	Calculated value of the daily price

Sell-out by Day and Location

Purpose: Enables you to compare retailer sell-out data at the day level (either provided or calculated) and location level by using the retailer provided sales/location data

This table was designed separately for retailers that cannot provide detailed data at the SKU/Location level and thus a secondary table is needed to enable tracking data the location level.

Table Name: uws.sell_out_day_location

Column	Description
id	Unique id for each row
retailer	Friendly name of the retailer
country_code	Two digit code for the country of the retailer
start_date	Start date of the reporting period
end_date	End date of the reporting period
order_date	Date of the order
original_currency	Currency of the supplied source data
currency_conversion_rate	Conversion rate applied to put into platform currency
converted_currency	Currency of the converted sales
location_id	Identifier of the location if the sales channel is retail
sales_channel	Channel of the sales - usually online or retail
sales_dollars_daily	If data provided weekly then replica of raw data otherwise empty
sales_units_daily	If data provided weekly then replica of raw data otherwise empty
sales_price_daily	Derived value of the average price for the week
refunds_dollars_daily	If data provided daily then raw data otherwise calculation of weekly to daily data
refunds_units_daily	If data provided daily then raw data otherwise calculation of weekly to daily data
refunds_price_daily	Calculated value of the daily price

Sell-in

Purpose: Enables you to compare retailer sell-in data across all your retailers

Table Name: uws.sell_in_sku

Column	Description
id	Unique id for each row
retailer	Friendly name of the retailer
country_code	Two digit code for the country of the retailer
order_date	Date of the sell-in order
original_currency	Currency of the supplied source data
currency_conversion_rate	Conversion rate applied to put into platform currency
converted_currency	Currency of the converted sales
listing_sku	Standard SKU for the item level data
item_description	Description of the item / item name
sell_in_dollars	Dollar amount of the sell-in
sell_in_units	Units of the sell-in
sell_in_price	Calculated value of the price for the sell-in SKU

SKU Inventory

Purpose: Enables you to track history of inventory levels held by the retailer to provider a deeper picture of your inventory

Table Name: uws.sku_inventory

Column	Description
id	Unique id for each row
retailer	Friendly name of the retailer
country_code	Two digit code for the country of the retailer
location_id	Identifier of the location if the sales channel is retail
inventory_date	Date of the inventory data
listing_sku	Standard SKU for the item level data
retail_price	Retail price (price charged to the consumer) of the SKU at the time of the inventory quantity
cost_price	Cost price (price the retailer paid) of the SKU at the time of the inventory quantity
on_hand_quantity	Total quantity of inventory at the warehouse
available_quantity	Total quantity of inventory at the warehouse to sell
committed_quantity	Total quantity of inventory that is already committed as sold but not shipped
in_transit_quantity	Total quantity of inventory that is in-transit to the warehouse
on_order_quantity	Total quantity of inventory has been ordered as part of a PO
back_ordered_quantity	Total quantity of inventory that has been sold and needs to be fulfilled that is not available

Current SKU Inventory

Purpose: Replica of the SKU inventory table created to store the current inventory to make reporting simpler by exposing this table in the schema

Table Name: uws.current_sku_inventory

Column	Description
id	Unique id for each row
retailer	Friendly name of the retailer
country_code	Two digit code for the country of the retailer
location_id	Identifier of the location if the sales channel is retail
inventory_date	Date of the inventory data
listing_sku	Standard SKU for the item level data
retail_price	Retail price (price charged to the consumer) of the SKU at the time of the inventory quantity
cost_price	Cost price (price the retailer paid) of the SKU at the time of the inventory quantity
on_hand_quantity	Total quantity of inventory at the warehouse
available_quantity	Total quantity of inventory at the warehouse to sell
committed_quantity	Total quantity of inventory that is already committed as sold but not shipped
in_transit_quantity	Total quantity of inventory that is in-transit to the warehouse
on_order_quantity	Total quantity of inventory has been ordered as part of a PO
back_ordered_quantity	Total quantity of inventory that has been sold and needs to be fulfilled that is not available

SKU Mapping

Purpose: Enables you to map your item information across retailers to enable analysis by internal or retailer item naming methods

Table Name: uws.sku_mapping

Column	Description
id	Unique id for each row
retailer	Friendly name of the retailer
listing_sku	Standard SKU for the item level data
master_sku	Normalized SKU across all system for the item
upc_code	The UPC Code for the item
dcpi	Item identifier in the wholesale system
item_description	Description of the item / item name
brand_name	Name of the brand in the wholesale system
brand_sku	Item identifier from the brand in the wholesale system

Location Mapping

Purpose: Enables you to add geo-location information to brick and mortar retail locations to provide geographical analytics

Table Name: uws.location_mapping

Column	Description
location_id	Identifier of the location if the sales channel is retail
retailer	Friendly name of the retailer
location_name	Name of the location
region	Region the retailer assigns to the store
city	City of the location
state	State of the location
zip	Zipcode of the location
longitude	Longitude of the location
latitude	Latitude of the location