Methodology & Matching Architecture¶
This document details the technical and econometric methodology used to link product barcodes from the NielsenIQ Retail Scanner Database to parent manufacturing firms in the GS1 US Company Prefix Registry.
1. Barcode Anatomy & Encoding¶
In the NielsenIQ Retail Scanner Database, products are identified by a 12-digit string (upc).
UPC-A and EAN-13 Standards¶
Standard North American retail products use the UPC-A standard (12 digits) or the international EAN-13 standard (13 digits).
- A standard UPC-A barcode consists of:
- 1 Number System Digit (usually
0for standard groceries,2for random-weight store items,3for pharmaceuticals,5for coupons). - 5-digit Manufacturer Code.
- 5-digit Product Code.
- 1 Modulo-10 Check Digit.
- An EAN-13 barcode prepends a 1-digit GS1 country prefix (for US/Canada, prefixes
00through13).
NielsenIQ Ingestion and Formatting¶
- Check Digit Elimination: The Kilts Center Retail Scanner manual explains that Nielsen encodes barcodes as 12-character strings representing EAN-13 with the final check digit dropped.
- Leading Digit Handling: For standard US goods, the first digit is
0, followed by the UPC-A digits (excluding the check digit).
2. GS1 Global Company Prefix (GCP) Structure¶
GS1 US assigns Global Company Prefixes (GCP) to companies that license barcode blocks. The length of the prefix depends on how many products the firm intends to license:
| Prefix Length | Variable Capacity | Typical Firm Type |
|---|---|---|
| 6–7 digits | Up to 100,000 products | Large multinational consumer goods conglomerates (e.g. General Mills, Procter & Gamble) |
| 8–9 digits | Up to 1,000–10,000 products | Medium-sized food and consumer brands |
| 10–11 digits | Up to 10–100 products | Small artisan businesses, specialty craft items |
In our GS1 database of 681,101 licensed records:
- Length 7: 256,041 prefixes
- Length 10: 179,730 prefixes
- Length 11: 178,237 prefixes
- Length 9: 54,622 prefixes
- Length 8: 12,051 prefixes
- Length 6: 420 prefixes
3. The Longest-Prefix Matching Algorithm¶
To accurately link each unique 12-digit Nielsen UPC to its parent company:
-
Candidate Prefix Extraction: For a given UPC, we evaluate candidate prefix substrings across lengths 11 down to 6 digits.
-
Priority Order: We test from the longest candidate length (11 digits) down to the shortest (6 digits). The first exact match found against the GS1
PrefixStringdictionary is assigned. -
Rationale: A company with an 11-digit prefix must not be accidentally misassigned to a shorter prefix owned by another firm.
-
Private-Label Mask Detection: Retailers create store-brand (private label) items (e.g., Great Value, Kirkland Signature, Kroger Brand). Nielsen masks the true manufacturer identity of private-label items by substituting pseudo-prefixes starting with
8or9. These items are detected and flagged asPrivate_Label_Masked. -
In-Store Variable Weight Detection: UPCs starting with
02or04represent local store-packaged random-weight items (e.g. custom deli bakery items) and are classified accordingly.
4. Efficiency through Deduplication¶
Rather than joining across all 111,498,546 weekly store transactions (which would require gigabytes of RAM and hours of compute), our pipeline leverages dimensional modeling:
-
Dimension Extraction: We extract the set of distinct UPCs (N = 7,921) from the movement data using DuckDB in 10.7 seconds.
-
Match Engine: We match the 7,921 unique UPCs in memory in 6 seconds.
-
Star-Schema Join: Any analytical query joins the sales movement table to
upc_firm_lookupvia an indexed foreign key onupc, executing in milliseconds.