# 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).