# Unified Retail Market Schema (URMS)
### Overview
The **Unified Retail Market Schema (URMS)** is Daasity’s data model for **syndicated retail data and category analytics**. Unlike URS which is based on a brand’s own retail sales, URMS incorporates data from third-party providers like **NielsenIQ, IRI (Circana), SPINS, Byzzer**, etc., which track retail sales across entire markets (including competitors). The goal of URMS is to normalize these syndicated datasets – which often include market-level metrics such as distribution, market share, and total category sales – into a consistent structure for analysis. URMS enables a brand to see not just its own sales, but how it stacks up in the broader category: e.g. total category size, competitor performance, and distribution gaps.
A key aspect of syndicated data is the presence of **non-additive metrics** (especially distribution measures like ACV%). These metrics can’t be simply summed across weeks (e.g. you cannot sum weekly distribution percentages to get a monthly figure). The URMS model addresses this by maintaining both granular **weekly data** and pre-calculated **aggregated period data** for longer time spans (e.g. Quarter, Year or Last 52 Weeks). This ensures that metrics like “% ACV Distribution” or “# of Stores Selling” are accurate at any time grain.
**Structure:** URMS includes:
* A **Weekly Sales/Market Data table** (fact table at week x product/brand x market).
* An **Aggregated Period Sales table** (fact table at aggregated periods x product/brand x market) – or alternatively, a mechanism to roll up weeks using reference tables without losing integrity of non-additive metrics.
* Reference tables for **Time Periods** (defining how weeks roll into QTR, YTD, Latest 52, etc., especially for use in the BI layer).
* Dimension tables for **Products** (including brand, category info) and **Markets/Locations** (e.g. retail channels or regions covered by the data).
The URMS schema is built to handle various syndicated feeds. For example, Nielsen may provide fields like ACV% and TDP, while SPINS provides spins-specific metrics – URMS aligns these under common fields where possible, and any source-specific fields are kept in separate “specific\_to\_\*” tables if needed (similar to URS). The result is a unified **category data model** that powers analyses like Category Performance, Market Share, and Key Driver (velocity & distribution) dashboards.
### 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 Market Schema (URMS) integration illustrating the different tables and keys to join across tables.
### Unified Retail Market Schema Tables
[Geography Rollup](#geography-rollup) \[`urms.geography_rollup`]
[Locations](#locations) \[`urs.locations`]
[Products](#products) \[`urs.products`]
[Time Period Mappings](#time-period-mappings) \[`urms.time_period_mappings`]
{% hint style="success" %}
Within the `urms_latest.explore.lkml` file, you will see `include: "/views/bsd/.view.lkml"`. This notes that all BSD views, where data customizations are managed, are made available at this unified schema level.
{% endhint %}
{% hint style="info" %}
**NOTE:** We define tables with three different types:
1. **Core** – These are the essential tables required to have a well-defined retail market model. They must be populated to support reliable market-level reporting (e.g., `urms.sales_report`, `urs.products`, `urs.locations`).
2. **Secondary** – Supporting tables that enrich or extend the Core tables, but are not strictly required for the model to function. They provide additional flexibility for analytics (e.g., `urms.time_period_mappings`).
{% endhint %}
### Geography Rollup
Purpose: Provides pre-aggregated market/category performance data with year-over-year comparisons at the geography level.
Table Name: `[urms.geography_rollup]`
Table Type: Secondary
| Column | Description |
| --------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| LOCATION\_ID | Key for the market/location |
| TIME\_PERIOD\_NAME | Labeled period length (e.g., `12 weeks`, `52 weeks`). The “Latest/Year Ago” context is determined separately by `TIME_PERIOD_TYPE` in `time_period_mappings`. |
| CATEGORY\_DOLLAR\_SALES | Sum of `sales_report.dollar_sales` for the **latest** period in the selected market/category filters. |
| CATEGORY\_DOLLAR\_SALES\_YA | Sum of `sales_report.dollar_sales` for the aligned **year-ago** period. |
| CATEGORY\_UNIT\_SALES | Sum of `sales_report.unit_sales` for the **latest** period. |
| CATEGORY\_UNIT\_SALES\_YA | Sum of `sales_report.unit_sales` for the **year-ago** period. |
| CATEGORY\_TDP | Sum of `sales_report.tdp` (Total Distribution Points) for the **latest** period. |
| CATEGORY\_TDP\_YA | Sum of `sales_report.tdp` for the **year-ago** period. |
### 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 variable time segments in URMS fact tables to standard reporting periods (e.g., Latest 4, 12, 24, 52 weeks). Enables one-to-many joins from sales data to consistent time ranges across sources.
Table Name: `urms.time_period_mappings`
Table Type: Core
| Column | Description |
| ------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TIME\_PERIOD\_MAPPING\_ID | Unique identifier for the time period mapping record. |
| RECORD\_DATE | The week-ending date (if weekly data) or the period-ending date (if aggregated data) from the raw source. |
| TIME\_PERIOD\_NAME | The labeled length of the time period (e.g., 12 weeks, 52 weeks).
Note: This does not include “Latest” or “Prior” — those are stored in TIME\_PERIOD\_TYPE.
|
| TIME\_PERIOD\_TYPE | Indicates whether the record corresponds to the latest, prior, or year\_ago version of the period.
Example: TIME\_PERIOD\_NAME = 12 weeks + TIME\_PERIOD\_TYPE = latest → “Latest 12 Weeks”.
|
| TIME\_PERIOD\_START | The actual start date of the time period window. |
| TIME\_PERIOD\_END | The actual end date of the time period window.
Typically the same as RECORD\_DATE in weekly data; in aggregated data, this represents the full period end date.
|
| VIZ\_DATE | The standardized date used in dashboards/explores. Always the period-ending (or week-ending) date of the **latest** period in a comparison set (latest, prior, year\_ago). |
| MAX\_RECORD\_DATE | The maximum available `RECORD_DATE` across **all URMS sources** (system-wide). |
| SOURCE\_MAX\_RECORD\_DATE | The maximum available `RECORD_DATE` for the **specific source** represented by this row. |
For guidance on analyzing syndicated data and making the most of URMS, please see our Knowledge Base article: [**How to Use Syndicated Data**](https://help.daasity.com/start-here/analyzing-your-data/retail-analytics-101/how-to-use-syndicated-data?utm_source=chatgpt.com).
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://help.daasity.com/core-concepts/data-models/unified-schemas/unified-retail-market-schema-urms.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.