Data Dictionary & Schema Specifications¶
This reference documents the variables across raw input tables and our generated analytical outputs.
1. Final Output Table (upc_firm_lookup)¶
Saved at output/upc_firm_lookup.tsv, data/parquet/upc_firm_lookup.parquet, and embedded in data/duckdb/niq_database.duckdb.
| Field Name | Type | Description | Example |
|---|---|---|---|
upc |
VARCHAR | 12-digit Nielsen product code | 001600044030 |
is_matched |
BOOLEAN | True if successfully matched to GS1 firm; False otherwise |
True |
match_type |
VARCHAR | Algorithm classification: GS1_prefix_7digit, Private_Label_Masked, etc. |
GS1_prefix_7digit |
matched_prefix |
VARCHAR | Matched GS1 company prefix string | 0016000 |
prefix_length |
INTEGER | Length of matched prefix in digits (6–11) | 7 |
company_name |
VARCHAR | Legal entity name registered with GS1 US | General Mills, Inc. |
city |
VARCHAR | City of the registered company headquarters | Minneapolis |
state |
VARCHAR | Two-letter state or province code | MN |
country |
VARCHAR | Country of incorporation or licensing | USA |
entity_gln |
VARCHAR | 13-digit Global Location Number (GLN) | 0016000110007 |
prefix_status |
VARCHAR | Status of the prefix license (Active / Inactive) |
Active |
is_private_label_masked |
BOOLEAN | Flag indicating whether this is a Nielsen masked store-brand product | False |
total_transactions |
BIGINT | Total store-week sales observations in 2010 | 482,546 |
total_units_sold |
DOUBLE | Total physical units sold across all stores in 2010 | 1,865,467 |
total_revenue_est |
DOUBLE | Estimated aggregate consumer expenditure (USD) in 2010 | $4,540,788.00 |
distinct_stores |
INTEGER | Number of distinct retail store locations selling this product | 17,193 |
2. GS1 Company Registry Table (gs1_company_prefixes)¶
Saved at data/parquet/gs1_company_prefixes.parquet and embedded in DuckDB.
| Field Name | Type | Description |
|---|---|---|
EntityGLN |
VARCHAR | Global Location Number for the legal entity |
UPCPrefix |
VARCHAR | UPC company prefix (digits without leading zero) |
PrefixString |
VARCHAR | Full Global Company Prefix string including leading country code |
CompanyName |
VARCHAR | Registered company name |
AddressLine1-3 |
VARCHAR | Physical mailing address of company |
City |
VARCHAR | City of registration |
StateCode |
VARCHAR | State or province abbreviation |
ZipCode |
VARCHAR | Postal code |
Country |
VARCHAR | Country (ISO 3-letter code or name) |
PrefixStatus |
VARCHAR | License status (Active or Inactive) |
DateModified |
VARCHAR | Timestamp of last registry update |
3. Nielsen Retail Scanner Movement Data (data/product_sales/)¶
Raw weekly sales records across stores and product modules.
| Field Name | Type | Description |
|---|---|---|
store_code_uc |
INTEGER | Unique identifier for the retail store |
upc |
VARCHAR | 12-digit product barcode (without check digit) |
week_end |
INTEGER | Date of week ending in YYYYMMDD format |
units |
INTEGER | Number of packages/units sold during the week |
prmult |
INTEGER | Price multiplier (e.g., 2 if price is "2 for $5.00") |
price |
DOUBLE | Selling price corresponding to prmult |
feature |
VARCHAR | Flag indicating if product was advertised in retailer circular |
display |
VARCHAR | Flag indicating if product had a special in-store promotional display |