Unified Retail Market Schema (URMS)

This page provides a detailed description of the Unified Retail Market Schema (URMS) within the Daasity Data Model and defines each table and column in this schema.

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 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 [urms.geography_rollup]

Locations [urs.locations]

Products [urs.products]

Time Period Mappings [urms.time_period_mappings]

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.

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

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.