Daasity Data Model: Unified Wholesale Schema (UWS)

This article provides a detailed description of the Unified Wholesale Schema (UWS) within the Daasity Data Model. This article will help you understand the purpose of each table and column in this data model.

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 design is in beta

Entity Relationship Diagram (ERD)

The embedded diagram houses the ERD for the Unified Wholesale Schema (UTS) illustrating the different tables and keys to join across tables.  Hover over the embedded diagram to reveal controls to zoom and scroll.

Unified Wholesale Schema Tables

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

Inventory

Purpose: Enables you to track multiple statuses 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

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

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