Skip to content

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