Unified Retail Sales (URS)
This page provides a detailed description of the Unified Retail Sales (URS) data model within the Daasity Data Model and defines each table and column in this schema.
The Unified Retail Sales (URS) schema is Daasity’s core data model for store-level retail sales, inventory, and wholesale data. It ingests POS feeds, shipment data, and on-hand inventory reports from retailer partners and normalizes them into a consistent structure that enables cross-retailer analysis.
URS provides a unified view of retail account activity by combining:
Sell-Out (POS sales): Units and dollars sold to consumers at the retailer.
Inventory: On-hand and on-order quantities reported at the retailer or store level.
Sell-In (Wholesale): Shipments from the brand into retailer warehouses or stores.
All URS fact tables are standardized at a daily grain (location × product × day), even when retailer data is provided at weekly or other intervals. Supporting dimension tables for products, locations, and time periods ensure consistent joins and reporting across sources.
The URS schema is designed to support account-level retail analytics across diverse retailer feeds, enabling brands to calculate metrics such as velocity, % stores selling, sell-through, weeks of supply, and inventory turnover. It serves as the single source of truth for store-level retail performance and is the foundation for retail-facing dashboards and reporting.
Entity Relationship Diagram (ERD)
Click on this link to view the ERD for the Unified Retail Sales (URS) integration illustrating the different tables and keys to join across tables.
Unified Retail Sales Tables
Invoice Sales [urs.invoice_sales]
Depletion Sales [urs.depletion_sales]
POS Sales [urs.pos_sales]
Product Listings [urs.product_listings]
Product Listing Numbers [urs.product_listing_numbers]
Distribution Centers [urs.distribution_center]
Stores [urs.stores]
Current Inventory Levels [urs.current_inventory_levels]
Inventory Levels History [urs.inventory_levels_history]
Within the retailer_inventory.explore.lkml file, you will see lines such as include: "/views/bsd/*.view.lkml", include: "/views/dm_retail/*.view.lkml", and others. These inclusions ensure that all relevant URS views—covering retailer inventory, product attributes, and business-specific customizations (BSD)—are available directly in this schema. This setup allows business users to analyze retailer inventory with both standardized URS fields and any brand-specific attributes that have been layered on.
Invoice Sales
Purpose: Stores Sell In orders from Purchasing Partners. Tracks SKU Sell In Cases, Units and SKU Cost by Distributor or Retailer on the date the Order, Receipt or Invoice was created. Each row represents a unique sell in x purchasing partner × product × day.
Table Name: urs.invoice_sales
Table Type: Core
SELL_IN_ID
Unique identifier for each Invoice Sales record.
ORDER_DATE
Date order was placed.
RECEIPT_DATE
Date order was placed and received by customer.
INVOICE_DATE
Date order was invoiced.
PURCHASING_PARTNER_TYPE
"distributor" or "retailer".
PURCHASING_PARTNER_ID
Unique combination of Store ID and Distribution Center ID. Links to urs.stores and urs.distribution_center
LISTING_SKU
SKU used by the retailer. Links to urs.product_listing
LISTING_SKU_VENDOR_FIELD
This contains the field from the POS sales that we are using for listing_sku - ex: DPCI, TCIN, Vendor Number.
VENDOR_SKU_VALUE
Vendor's reference number that links to their ERP.
MASTER_SKU
Vendor's SKU reference in their ERP.
CASE_QTY
Quantity in Cases as part of the Sell In. For data not originally in Cases a conversion factor from Units can be used to determine Case Qty.
UNIT_QTY
Quantity in Units as part of the Sell In. For data not originally in Units a conversion factor from Case Qty can be used to determine Unit Qty.
SKU_COST
Sku Cost as determined from the Vendor's data. Provided only if the source data contains this value.
PRICE_TO_PURCHASING_PARTNER
Price of the Sell In to the Purchasing Partner. Provided only if the source data contains this value.
Depletion Sales
Purpose: Stores Depletion Sales orders from Purchasing Partners. Tracks SKU Depletion In Cases, Units and SKU Cost by Distributor on the date the Depletion was created. Each row represents a unique depletion event x purchasing partner × product × day.
Table Name: urs.depletion_sales
Table Type: Core
DEPLETION_ID
Unique identifier for each Depletion Sale event.
ORDER_DATE
Date order was placed.
DEPLETION_DATE
Date order was shipped out of warehouse.
RECEIPT_DATE
Date order was placed and received by customer.
INVOICE_DATE
Date order was invoiced.
DISTRIBUTION_CENTER_ID
Distribution Center ID. Links to urs.distribution_center
RETAILER
The store identifier used by the retailer.
STORE_ID
Unique identifier for each store location. Links to urs.stores
STORE_NAME
Name of an individual store, if store-level data is available.
UPC
UPC of the product used by the Retailer.
LISTING_SKU
SKU used by the retailer. Links to urs.product_listing
LISTING_SKU_VENDOR_FIELD
This contains the field from the POS sales that we are using for listing_sku - ex: DPCI, TCIN, Vendor Number
VENDOR_SKU_VALUE
Vendor's reference number that links to their ERP.
MASTER_SKU
Vendor's SKU reference in their ERP.
PRICE
Price of the product. Provided only if the source data contains this value.
PRICE_TO_DISTRIBUTOR
Price of the product to distributor. Provided only if the source data contains this value.
SKU_COST
Sku Cost as determined from the Vendor's data. Provided only if the source data contains this value.
DOLLAR_SALES
Depletion Sales in Dollar value. Generally converted to your preferred account currency.
UNIT_SALES_AMOUNT
If SKU is single unit then quantity otherwise case size * case units
CASE_SALES_AMOUNT
If SKU is case then # of cases otherwise NULL
UNIT_QTY
Quantity in Units as part of the Depletion Sale. For data not originally in Units a conversion factor from Case Qty can be used to determine Unit Qty.
CASE_QTY
Quantity in Cases as part of the Depletion Sale. For data not originally in Cases a conversion factor from Units can be used to determine Case Qty.
UNITS_PER_CASE
Default to "1", unless source data contains a different value.
POS Sales
Purpose: Store Sell Out orders from Brick & Mortar or Digital Purchasing Partners. Tracks SKU Sell Out Cases, Units and SKU Cost by Retailer on the date the date of the sale. Each row represents a unique sell out x purchasing partner × product × day.
Table Name: urs.pos_sales
Table Type: Core
SALES_ID
Unique identifier for each POS store by SKU
SALES_DATE
Sales of the POS Sale
CHANNEL
Brick & Mortar or Digital
RETAILER
The retailer or account name (e.g., Target, Whole Foods).
STORE_ID
Unique identifier for each store location. Links to urs.stores
STORE_NAME
Name of an individual store, if store-level data is available.
UPC
Global barcode identifier, used to align retailer data with syndicated sources.
LISTING_SKU
SKU used by the retailer. Links to urs.product_listing
LISTING_SKU_VENDOR_FIELD
This contains the field from the POS sales that we are using for listing_sku - ex: DPCI, TCIN, Vendor Number.
MASTER_SKU
Vendor's SKU reference in their ERP.
PRICE
Price of the product. Provided only if the source data contains this value.
PRICE_TO_RETAILER
Price of the product to retailer. Provided only if the source data contains this value.
SKU_COST
Sku Cost as determined from the Vendor's data. Provided only if the source data contains this value.
UNIT_SALES_AMOUNT
If SKU is single unit then quantity otherwise case size * case units.
CASE_SALES_AMOUNT
If SKU is case then # of cases otherwise NULL.
UNIT_QTY
Quantity in Units as part of the Sell In. For data not originally in Units a conversion factor from Case Qty can be used to determine Unit Qty.
CASE_QTY
Quantity in Cases as part of the Sell In. For data not originally in Cases a conversion factor from Units can be used to determine Case Qty.
UNITS_PER_CASE
Default to "1", unless source data contains a different value.
ORIGINAL_CURRENCY
Currency reported by the source (e.g., USD, CAD).
CURRENCY_CONVERSION_RATE
Rate used to convert from original to converted currency.
CONVERTED_CURRENCY
Target currency after conversion.
Product Listings
Purpose: Captures product and brand details for reporting. Each row represents a unique entity (brand or product), and includes hierarchical product classification fields.
Table Name: urs.product_listings
Table Type: Core
PRODUCT_LISTING_ID
Unique identifier for each product listing.
LISTING_SKU
SKU used by the retailer.
RETAILER_NAME
The retailer or account name (e.g., Target, Whole Foods).
VENDOR_SKU_VALUE
Vendor's reference number that links to their ERP.
MASTER_SKU
Vendor's SKU reference in their ERP.
PRODUCT_NAME
Syndicated item or brand descriptor (may include category totals or “All Other” rows).
UPC
Global barcode identifier, used to align retailer data with syndicated sources.
BRAND_NAME
Brand associated with the syndicated record.
DEPARTMENT
Hierarchical product classification standardized across all retailers.
CATEGORY
Hierarchical product classification standardized across all retailers.
SUBCATEGORY
Hierarchical product classification standardized across all retailers.
PRODUCT_CLASS
Hierarchical product classification standardized across all retailers.
PRODUCT_TYPE
The type of product
PRODUCT_SIZE
Numerical size value related to unit of measure.
PACK_COUNT
Numeric value of unites in a multipack (default is typically 1).
WEIGHT
Numerical size value related to weight.
WEIGHT_UOM
Generic unit of measure (ex: ounces, lbs).
CREATED_AT
Date the product listing was created at, if available.
UPDATED_AT
Date the report was last updated at.
Product Listing Numbers
Purpose: Stores product identification fields. Each row represents a unique entity (brand or product).
Table Name: urs.product_listing_numbers
Table Type: Core
PRODUCT_LISTING_NUMBERS_ID
Unique identifier for each product listing number.
LISTING_SKU
SKU used by the retailer. Links to urs.product_listing
RETAILER_NAME
The retailer or account name (e.g., Target, Whole Foods).
VENDOR_SKU_TYPE
indicates which type of identifier is being used (ASIN, TCIN, etc).
VENDOR_SKU_VALUE
Vendor's reference number that links to their ERP.
CREATED_AT
Date the product listing was created at, if available.
UPDATED_AT
Date the report was last updated at.
Distribution Centers
Purpose: Stores information related to a Retailer Distribution Center. Each records represents a unique Distribution Center
Table Name: urs.distribution_center
Table Type: Core
DISTRIBUTION_CENTER_ID
Unique identifier for each Distribution Center.
PARTNER_TYPE
"Distributor"
WAREHOUSE_OWNER
UNFI, KeHE, etc..
WAREHOUSE_NAME
Name of the Warehouse
ADDRESS1
Address component for Distribution Center.
ADDRESS2
Address component for Distribution Center.
CITY
Address component for Distribution Center.
STATE
Address component for Distribution Center.
COUNTRY
Address component for Distribution Center.
ZIPCODE
Address component for Distribution Center.
CONTACT
Contact for the Distribution Center, generally name or position.
Email address of the Distribution Center
PHONE
Phone Number of the Distribution Center.
CREATED_AT
Date the Distribution center was first created in reporting.
UPDATED_AT
Date this record was last updated at.
Stores
Purpose: Stores information related to a Retailer Store. Each records represents a unique Retailer Store Name.
Table Name: urs.stores
Table Type: Core
STORE_ID
Unique identifier for each store location.
PARTNER_TYPE
"Retailer"
RETAILER_NAME
The retailer or account name (e.g., Target, Whole Foods).
STORE_NAME
Name of an individual store, if store-level data is available.
REGION
Name of the region the store occupies.
RETAILER_DIVISION
Name of the division the store occupies.
ADDRESS1
Address component for Retailer.
ADDRESS2
Address component for Retailer.
CITY
Address component for Retailer.
STATE
Address component for Retailer.
COUNTRY
Address component for Retailer.
ZIPCODE
Address component for Retailer.
CONTACT
Contact for the Retailer, generally name or position.
Email address of the Retailer.
PHONE
Phone number of the Retailer.
CREATED_AT
Date the Retailer was first created in reporting.
UPDATED_AT
Date this record was last updated at.
Current Inventory Levels
Purpose: Captures the inventory for the most recent inventory related data load. Each record represents a unique distributor or retailer partner product.
Table Name: urs.current_inventory_levels
Table Type: Core
INVENTORY_SNAPSHOT_ID
Unique identifier for each inventory record.
SNAPSHOT_DATE
The date the inventory snapshot represents. Normalized to daily grain.
PURCHASING_PARTNER_TYPE
"Distributor" or "Retailer"
PURCHASING_PARTNER_ID
Store_ID from urs.stores or Distribution_center_id from urs.distribution_center .
UPC
Global barcode identifier, used to align retailer data with syndicated sources.
LISTING_SKU
SKU used by the retailer. Links to urs.product_listing
LISTING_SKU_VENDOR_FIELD
This contains the field from the POS sales that we are using for listing_sku - ex: DPCI, TCIN, Vendor Number.
VENDOR_SKU_VALUE
Vendor's reference number that links to their ERP.
MASTER_SKU
Vendor's SKU reference in their ERP.
ON_HAND_QTY
Quantity of product physically on hand at the location on the given date.
COMMITTED_QTY
Quantity of product physically committed at the location on the given date.
AVAILABLE_QTY
On Hand Qty - Committed Qty
ON_ORDER_QTY
Quantity of product currently on order but not yet received.
IN_TRANSIT_QTY
Quantity of product currently in transit to retailer, warehouse, etc..
BACK_ORDER_QTY
Quantity of product that cannot be fulfilled as it is out of stock.
Inventory Levels History
Purpose: Stores each historical inventory snapshot. Each record represents a unique distributor or retailer partner product, and unique date.
Table Name: urs.inventory_levels_history
Table Type: Core
INVENTORY_SNAPSHOT_ID
Unique identifier for each inventory record.
INVENTORY_DATE
The date the inventory snapshot represents. Normalized to daily grain.
PURCHASING_PARTNER_TYPE
"Distributor" or "Retailer"
PURCHASING_PARTNER_ID
Store_ID from urs.stores or Distribution_center_id from urs.distribution_center .
UPC
Global barcode identifier, used to align retailer data with syndicated sources.
LISTING_SKU
SKU used by the retailer. Links to urs.product_listing
LISTING_SKU_VENDOR_FIELD
This contains the field from the POS sales that we are using for listing_sku - ex: DPCI, TCIN, Vendor Number.
VENDOR_SKU_VALUE
Vendor's reference number that links to their ERP.
MASTER_SKU
Vendor's SKU reference in their ERP.
ON_HAND_QTY
Quantity of product physically on hand at the location on the given date.
COMMITTED_QTY
Quantity of product physically committed at the location on the given date.
AVAILABLE_QTY
On Hand Qty - Committed Qty
ON_ORDER_QTY
Quantity of product currently on order but not yet received.
IN_TRANSIT_QTY
Quantity of product currently in transit to retailer, warehouse, etc..
BACK_ORDER_QTY
Quantity of product that cannot be fulfilled as it is out of stock.
For guidance on analyzing your own retailer data (POS, Inventory, and Wholesale) and how to use URS in reporting, please see our Knowledge Base articles under: Retail Analytics 101.
Last updated
Was this helpful?