1# Phase 1 — Data audit and research-grade panel (WP37)23Generated by `src/reports/phase1_audit.py` from the processed tables on 2026-11-01 09:15 UTC. Snapshot `foodka-snapshot-20261101` (sha256 `3cd7684d6596b675…`).45## 1. Source-to-variable inventory67| variable | status | notes |8|---|---|---|9| product name | confirmed | all feeds; language FR/EN mixed (IGA/Voilà, Adonis, T&T in English) |10| brand | confirmed / partly unavailable | missing for 13 % of active listings (Walmart, Adonis, T&T, Akhavan, BocoBoco, Costco feeds lack a brand field; brand often embedded in the name) |11| UPC / GTIN | confirmed (Metro, Super C only) | 5,120 active listings with GS1-valid codes; other feeds expose internal SKUs. Open Food Facts barcodes attached by Food-Ka to 913 active listings are derived by name lookup → 'derivable, unreliable', used only as a diagnostic |12| package size / unit | derivable | re-parsed from size_label or name: 63.2 % of active listings (Food-Ka's own parser: 43.2 %); missing mainly for Giant Tiger, Nuvo and circular feeds |13| category | confirmed (18 canonical) | Food-Ka mapping from each banner's taxonomy; 'Autres' is a residual (19 % of active listings) |14| retailer / banner / company | derivable | 54 active feeds → banners → companies (retailers table); ownership basis recorded per feed |15| store | unavailable | no store identifier in any feed; online storefronts answered anonymous sessions (store unknown); circulars for postal code H2X 1Y4 |16| current price | confirmed | event log: first observation + every change (NULL when price disappears) |17| regular / reference price | current state only; circulars per item-week | not historised for storefront feeds; `original_price` in Flipp items |18| unit price | derivable | recomputed from price and parsed size; per-kg labels handled; multi-buy 'n / x $' labels flagged |19| promotion flag | current state only | historical promotion state must be inferred from price paths (storefront) or is implicit (circular items are promotions by construction) |20| observation timestamp | confirmed (source level) | 15,630 connector passes, median gap 7.2 h; product-level presence between events is inferred (see §3) |21| circular validity | confirmed | valid_from / valid_to per Flipp item (weekly, Thu→Wed) |22| availability / stock | unreliable | in_stock NULL for 23 % of active listings; current state only |23| geography | unavailable | see store |24| quantities / expenditure weights | unavailable | listings only; no transactions |2526## 2. Retail hierarchy (feeds → banners → companies)2728Active feeds in the registry: 57; with active listings in the snapshot: 54. Channel split: {'circular': 31, 'online_storefront': 25, 'circular_hybrid': 1}.2930| company | banners | feeds |31|---|---|---|32| Metro Inc. | 5 | 8 |33| Loblaw Companies | 5 | 7 |34| Sobeys / Empire | 4 | 5 |35| Metro Inc. (affiliate) | 3 | 3 |36| Walmart Canada | 1 | 2 |37| Avril Supermarché Santé | 1 | 2 |38| Supermarché PA | 2 | 2 |39| Costco Wholesale Canada | 1 | 2 |40| Loblaw Companies (affiliate) | 2 | 2 |41| Akhavan | 1 | 1 |42| Maturin | 1 | 1 |43| Marché Val-Mont | 1 | 1 |44| Pasquier | 1 | 1 |45| McKesson Canada | 1 | 1 |46| Marché Nuvo | 1 | 1 |47| Société des alcools du Québec | 1 | 1 |48| Supermarché Aurès | 1 | 1 |49| Marché Tau | 1 | 1 |50| Marché Bonanza | 1 | 1 |51| Marché Fu Tai | 1 | 1 |52| Marché C&T | 1 | 1 |53| Aliments Merci | 1 | 1 |54| Les Aliments M&M | 1 | 1 |55| Le Marché Végétarien | 1 | 1 |56| La Boîte à Grains | 1 | 1 |57| Kim Phat | 1 | 1 |58| Groupe Mayrand | 1 | 1 |59| Giant Tiger Stores | 1 | 1 |60| Euro Marché | 1 | 1 |61| Epipresto | 1 | 1 |62| BocoBoco | 1 | 1 |63| Aubut | 1 | 1 |64| Épiceries LOCO | 1 | 1 |6566Pharmacy feeds (4) and the SAQ are kept in the retailers table but excluded from grocery analyses; same-banner storefront/circular pairs are 'channel' links, not cross-banner comparisons.6768## 3. Panel reconstruction6970`price_log` is an event log (first observation + each change). A product's price is carried forward between events until `last_seen`; days are local (America/Toronto). Daily statistics per listing: time-weighted mode, min, max, mean, open/close, number of changes and distinct values. Weekly aggregation uses the Quebec circular week (Thursday→Wednesday). Tests (`tests/test_panel.py`) verify bounds, that covered hours equal the observation span, and that single-event products have exactly one distinct price.7172Limitations: (i) a listing that leaves and re-enters a feed is not logged as a gap unless its price changed — intra-span absence is unobserved; (ii) deactivation requires two consecutive misses (≈14 h), so `last_seen` dates removals to within one pass.7374Panel size: 5,327,181 listing-days over 13 circular weeks (2026-08-06 → 2026-10-29).7576## 4. Store-mixture noise (measurement finding)7778Share of listing-days with more than one observed price value, and the median relative range (max−min)/mode on those days, for feeds with > 20 000 listing-days:7980| source | product_days | multi_price_share | median_rel_range_when_multi |81|---|---|---|---|82| club_entrepot | 168,906 | 0.237 | 0.125 |83| costco | 39,304 | 0.058 | 0.026 |84| maxi | 149,947 | 0.056 | 0.143 |85| metro | 552,776 | 0.056 | 0.188 |86| avril | 35,974 | 0.033 | 0.200 |87| superc | 231,371 | 0.030 | 0.214 |88| tt | 33,947 | 0.029 | 0.175 |89| adonis | 106,131 | 0.025 | 0.200 |90| iga | 108,586 | 0.025 | 0.194 |91| provigo | 135,183 | 0.023 | 0.231 |92| walmart | 129,980 | 0.020 | 0.143 |93| superc_flyer | 35,634 | 0.014 | 0.257 |94| mayrand | 36,455 | 0.014 | 0.164 |95| metro_flyer | 45,600 | 0.013 | 0.334 |96| iga_flyer | 45,980 | 0.013 | 0.357 |97| boite_a_grains | 463,283 | 0.012 | 0.177 |98| pa | 275,853 | 0.012 | 0.251 |99| adonis_flyer | 25,745 | 0.010 | 0.247 |100| giant_tiger | 761,998 | 0.008 | 0.200 |101| aubut | 46,219 | 0.008 | 0.136 |102| maxi_flyer | 30,101 | 0.008 | 0.160 |103| pasquier | 20,733 | 0.005 | 0.201 |104| nuvo | 373,628 | 0.005 | 0.251 |105| provigo_flyer | 23,602 | 0.005 | 0.200 |106| walmart_flyer | 42,553 | 0.004 | 0.231 |107| aliments_merci | 216,835 | 0.002 | 0.167 |108| saq | 296,305 | 0.002 | 0.080 |109| bocoboco | 58,958 | 0.002 | 0.515 |110| maturin | 37,739 | 0.002 | 0.066 |111| akhavan | 54,497 | 0.001 | 0.200 |112| epipresto | 460,710 | 0.000 | |113114Interpretation: storefront feeds behind anti-bot protection (Club Entrepôt, Metro, Maxi, Super C, Provigo, Walmart) answer each anonymous session with the price of an unknown store; the resulting intra-day oscillation among 3–5 price levels is not repricing. Single-store online shops (Shopify/WooCommerce) and circulars do not show it. Consequences: weekly modal prices are the primary posted-price measure; within-banner daily range is reported as a store-unidentified lower bound on cross-store dispersion; sub-weekly volatility is not interpreted as price flexibility.115116## 5. Listing-level attrition117118| step | listings | share_of_initial |119|---|---|---|120| All listings in snapshot (products table) | 124,579 | 1.000 |121| excl. SAQ (alcohol monopoly) | 117,102 | 0.940 |122| excl. pharmacy feeds | 115,352 | 0.926 |123| excl. non-food categories (health & beauty, household, pets, baby, alcohol) | 112,233 | 0.901 |124| with at least one observed price | 111,924 | 0.898 |125| excl. unrealistic prices | 111,575 | 0.896 |126| … with parsable package size (unit-price / matching universe) | 66,340 | 0.533 |127| … in a cross-banner match group (tiers A–C) | 13,273 | 0.107 |128129## 6. Cross-banner matching130131Tiers: A = exact GS1-valid UPC (Metro ↔ Super C); B = brand + package (quantity and pack count) + identical cleaned token set + identical numeric tokens; C = brand + package + token-sort similarity ≥ 0.90 (not B); D = brand-less feed, package + identical full-name token set + branded side's brand tokens present. Multi-product circular offers ('X ou Y', 'assortis') and size-less listings are excluded from matching.132133Threshold calibration on UPC ground truth (all Metro×Super C candidate pairs within brand×package blocks; positives = same UPC): 134135| scorer | thr | n | precision | recall |136|---|---|---|---|---|137| sort_ratio | 0.700 | 9,958.000 | 0.432 | 0.994 |138| sort_ratio | 0.750 | 8,155.000 | 0.527 | 0.992 |139| sort_ratio | 0.800 | 6,695.000 | 0.641 | 0.991 |140| sort_ratio | 0.850 | 5,601.000 | 0.764 | 0.988 |141| sort_ratio | 0.900 | 4,913.000 | 0.870 | 0.987 |142| sort_ratio | 0.950 | 4,597.000 | 0.928 | 0.986 |143| sort_ratio | 1.000 | 4,563.000 | 0.932 | 0.982 |144| tokens_equal | 1.000 | 4,563.000 | 0.932 | 0.982 |145146Cross-banner pairs found: {'A': 4413, 'B': 4088, 'C': 714, 'D': 102}; channel (storefront↔circular, same banner) links: 1. A first version based on rapidfuzz `token_set_ratio` was rejected after validation (subset names scored 1.0); see `match_validation_sample_v0_tokenset_rejected.csv` and `_v1_round1.csv`.147148Recall against UPC truth (4 413 Metro↔Super C pairs): tier B recovers 0.974, B+C 0.979 — an upper bound for cross-company recall because the two banners share a platform and naming conventions. Food-Ka's own `product_links` (2 033 pairs) are recovered at 42.5 %; the remainder are size-less or multi-offer circular links that WP37 excludes by design.149150### 6.1 Validation sample (240 pairs, 60 per tier, stratified random, seed 20261101)151152| tier | n | same | different | unsure | precision_strict | ci_lo | ci_hi | precision_excl_unsure | ci2_lo | ci2_hi | llm_operator_agreement | upc_precision | upc_recall |153|---|---|---|---|---|---|---|---|---|---|---|---|---|---|154| A | 60 | 60 | 0 | 0 | 1.000 | 0.940 | 1.000 | 1.000 | 0.940 | 1.000 | 1.000 | 1.000 | |155| B | 60 | 60 | 0 | 0 | 1.000 | 0.940 | 1.000 | 1.000 | 0.940 | 1.000 | 1.000 | 0.932 | 0.982 |156| C | 60 | 25 | 30 | 5 | 0.417 | 0.301 | 0.543 | 0.455 | 0.330 | 0.585 | 0.983 | 0.870 | 0.987 |157| D | 60 | 59 | 0 | 1 | 0.983 | 0.911 | 0.997 | 1.000 | 0.939 | 1.000 | 1.000 | | |158159Labels: Claude (claude-opus-5-5) as second annotator; final label = operator review of every pair (research pipeline operator, not the PI); PI adjudication is pending and flagged in the paper. The UPC-based precision for tier B (0.913) is lower than the text-based precision because some listings with identical text carry different UPCs (packaging variants invisible in text) — both numbers are reported.160161**Decision for the primary specification: tiers A + B only; C and D enter sensitivity analyses.**162163## 7. Prospective collection plan (what cannot be done now)164165- **Store geography**: add a store-selection step to Loblaw/Metro/Walmart connectors (store id in the session) for a fixed panel of ~20 stores across Quebec regions, and pull Flipp circulars for ≥ 6 postal codes (Montréal, Québec, Gatineau, Sherbrooke, Saguenay, Trois-Rivières). Both are authorised, polite (one pass per store per day) and reversible changes to Food-Ka; they are **not** implemented in WP37 (no production change).166- **Reference prices**: log `regular_price` and `on_sale` changes in `price_log` (one extra column) so that promotion states become directly observed.167- **Minimum duration**: 26 weeks for promotion recurrence and reference-price adjustment (H3), 52 weeks for seasonal patterns and index validation against StatCan.168