# Unified Retail Sales (URS) 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](https://lucid.app/lucidchart/1ed49a6e-a625-44c7-b70b-5e0abda546ac/edit?invitationId=inv_f7105a47-c9fd-4818-8f34-2cbdadb472dd\&page=0_0) 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](#inventory-report) \[`urs.inventory_report`] [Locations](#locations) \[`urs.locations`] [Products](#products) \[`urs.products`] [Time Period Mappings](#time-period-mappings) \[`urs.time_period_mappings`] [Wholesale Report](#wholesale-report) \[`urs.wholesale_report`] {% hint style="success" %} 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. {% endhint %} ### 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](https://help.daasity.com/start-here/analyzing-your-data/retail-analytics-101).