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

Inventory Report [urs.inventory_report]

Locations [urs.locations]

Products [urs.products]

Time Period Mappings [urs.time_period_mappings]

Wholesale Report [urs.wholesale_report]

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.

Inventory Report

Purpose: Stores daily inventory levels for each product at each location. Tracks stock on hand and on order, enabling metrics like Weeks on Hand and Weeks of Supply. Each row represents a unique location × product × day.

Table Name: urs.inventory_report

Table Type: Core

Column	Description
INVENTORY_REPORT_ID	Unique identifier for each inventory record.
LOCATION_ID	Links to the urs.locations table to identify the store, warehouse, or market.
PRODUCT_ID	Links to the urs.products table to identify the product.
INVENTORY_DATE	The date the inventory snapshot represents. Normalized to daily grain.
SOURCE_INVENTORY_DATE	Original inventory date provided by the source system (before normalization).
QTY_ON_HAND	Quantity of product physically on hand at the location on the given date.
QTY_ON_ORDER	Quantity of product currently on order but not yet received.
QTY_ON_HAND_LY	Prior-year quantity on hand for the same product/location/date (if provided by the source).

Locations

Purpose: Stores information about physical locations such as stores, warehouses, or markets. Each location has one row with flags for type, enabling consistent location joins across schemas.

Table Name: urs.locations

Table Type: Core

Column	Description
LOCATION_ID	Unique identifier for each location (store, warehouse, or market). Used to link sales, inventory, and wholesale data.
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.
MARKET_NAME	Market or region represented (e.g., “Northeast Region,” “Total U.S.”).
WAREHOUSE_NAME	Distribution center name, if data is provided at the warehouse/DC level.
RETAILER_DIVISION	The retailer’s internal division or regional grouping (e.g., Kroger East, Kroger West).
RETAILER_STORE_ID	The store identifier used by the retailer.
ADDRESS1	Street address details, if provided.
ADDRESS2	Street address details, if provided.
CITY	City where the store or warehouse is located.
STATE	State or province of the location.
COUNTRY	Country where the location is based.
ZIPCODE	Postal code of the location.
IS_STORE_LEVEL	Yes/No flag indicating the row represents an individual store.
IS_WAREHOUSE_LEVEL	Yes/No flag indicating the row represents a warehouse or distribution center.
IS_MARKET_LEVEL	Yes/No flag indicating the row represents a rolled-up market or regional total.

Products

Purpose: Captures product and brand details for reporting. Each row represents a unique entity (brand or product), with reporting_level indicating whether it is aggregated at the brand level or specific to an individual product.

Table Name: urs.products

Table Type: Core

Column	Description
PRODUCT_ID	Unique identifier key linking each record in sales, inventory, and wholesale tables to the master product attributes.
REPORTING_LEVEL	Some data sources like SPINS provide reporting at UPC-level, as well as brand and subcategory level (default to UPC-Level).
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.
UNIT_OF_MEASURE	Generic unit of measure (ex: ounces, lbs).
PACK_COUNT	Numeric value of unites in a multipack (default is typically 1).

Time Period Mappings

Purpose: Maps calendar dates in URS to standard rolling periods (01, 04, 12, 24, 52, 104 weeks) with latest / prior / year_ago variants. Enables one-to-many joins from daily fact tables to consistent comparison windows across retailers and in dm_retail reports.

Table Name: urs.time_period_mappings

Table Type: Core

Column	Description
TIME_PERIOD_MAPPING_ID	Unique identifier for the time period mapping record.
RECORD_DATE	The date that falls inside the window. For weekly retailer feeds this is the week-ending date; for aggregated windows it is the period-ending date repeated across the window.
TIME_PERIOD_NAME	Labeled length of the window (e.g., 01 Weeks, 12 Weeks, 52 Weeks). Does not include “Latest/Prior/Year Ago”.
TIME_PERIOD_TYPE	Version of the window: latest, prior, or year_ago. Example: TIME_PERIOD_NAME = 12 Weeks + TIME_PERIOD_TYPE = latest → “Latest 12 Weeks”.
TIME_PERIOD_START	Start date (inclusive) of the window.
TIME_PERIOD_END	End date (inclusive) of the window. For weekly feeds this typically equals RECORD_DATE; for aggregated windows it is the full period end date.
VIZ_DATE	Standardized display date used in dashboards/explores so comparisons line up: latest = TIME_PERIOD_END; prior = RECORD_DATE + window_length; year_ago = RECORD_DATE + 364 days.
MAX_RECORD_DATE	Most recent RECORD_DATE across all URS data at build time.
SOURCE_MAX_RECORD_DATE	Most recent RECORD_DATE for the specific source represented by the row (for __SOURCE_ID = 'unified', this mirrors MAX_RECORD_DATE).

Wholesale Report

Purpose: Stores daily sell-in (wholesale shipments) by product and location. Enables comparisons of shipments vs. retail sales and downstream metrics like base/incremental wholesale in the data mart. Each row is location × product × day.

Table Name: urs.wholesale_report

Table Type: Core

Column	Description
WHOLESALE_REPORT_ID	Unique identifier for the wholesale record.
WHOLESALE_DATE	Date the shipment is recorded for (normalized to daily grain).
LOCATION_ID	Links to urs.locations (retailer, region, DC, or store).
PRODUCT_ID	Links to urs.products (the shipped item).
WHOLESALE_DOLLAR_SALES	Wholesale dollar value shipped on this date.
SOURCE_WHOLESALE_DATE	Original shipment date from the source system (pre-normalization).
WHOLESALE_UNIT_SALES	Units shipped on this date.
WHOLESALE_CASE_SALES	Cases shipped on this date (if provided).
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.

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.