# Phase 1 — Data audit and research-grade panel (WP37)

Generated by `src/reports/phase1_audit.py` from the processed tables on 2026-11-01 09:15 UTC. Snapshot `foodka-snapshot-20261101` (sha256 `3cd7684d6596b675…`).

## 1. Source-to-variable inventory

| variable | status | notes |
|---|---|---|
| product name | confirmed | all feeds; language FR/EN mixed (IGA/Voilà, Adonis, T&T in English) |
| 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) |
| 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 |
| 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 |
| category | confirmed (18 canonical) | Food-Ka mapping from each banner's taxonomy; 'Autres' is a residual (19 % of active listings) |
| retailer / banner / company | derivable | 54 active feeds → banners → companies (retailers table); ownership basis recorded per feed |
| store | unavailable | no store identifier in any feed; online storefronts answered anonymous sessions (store unknown); circulars for postal code H2X 1Y4 |
| current price | confirmed | event log: first observation + every change (NULL when price disappears) |
| regular / reference price | current state only; circulars per item-week | not historised for storefront feeds; `original_price` in Flipp items |
| unit price | derivable | recomputed from price and parsed size; per-kg labels handled; multi-buy 'n / x $' labels flagged |
| promotion flag | current state only | historical promotion state must be inferred from price paths (storefront) or is implicit (circular items are promotions by construction) |
| observation timestamp | confirmed (source level) | 15,630 connector passes, median gap 7.2 h; product-level presence between events is inferred (see §3) |
| circular validity | confirmed | valid_from / valid_to per Flipp item (weekly, Thu→Wed) |
| availability / stock | unreliable | in_stock NULL for 23 % of active listings; current state only |
| geography | unavailable | see store |
| quantities / expenditure weights | unavailable | listings only; no transactions |

## 2. Retail hierarchy (feeds → banners → companies)

Active feeds in the registry: 57; with active listings in the snapshot: 54. Channel split: {'circular': 31, 'online_storefront': 25, 'circular_hybrid': 1}.

| company | banners | feeds |
|---|---|---|
| Metro Inc. | 5 | 8 |
| Loblaw Companies | 5 | 7 |
| Sobeys / Empire | 4 | 5 |
| Metro Inc. (affiliate) | 3 | 3 |
| Walmart Canada | 1 | 2 |
| Avril Supermarché Santé | 1 | 2 |
| Supermarché PA | 2 | 2 |
| Costco Wholesale Canada | 1 | 2 |
| Loblaw Companies (affiliate) | 2 | 2 |
| Akhavan | 1 | 1 |
| Maturin | 1 | 1 |
| Marché Val-Mont | 1 | 1 |
| Pasquier | 1 | 1 |
| McKesson Canada | 1 | 1 |
| Marché Nuvo | 1 | 1 |
| Société des alcools du Québec | 1 | 1 |
| Supermarché Aurès | 1 | 1 |
| Marché Tau | 1 | 1 |
| Marché Bonanza | 1 | 1 |
| Marché Fu Tai | 1 | 1 |
| Marché C&T | 1 | 1 |
| Aliments Merci | 1 | 1 |
| Les Aliments M&M | 1 | 1 |
| Le Marché Végétarien | 1 | 1 |
| La Boîte à Grains | 1 | 1 |
| Kim Phat | 1 | 1 |
| Groupe Mayrand | 1 | 1 |
| Giant Tiger Stores | 1 | 1 |
| Euro Marché | 1 | 1 |
| Epipresto | 1 | 1 |
| BocoBoco | 1 | 1 |
| Aubut | 1 | 1 |
| Épiceries LOCO | 1 | 1 |

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

## 3. Panel reconstruction

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

Limitations: (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.

Panel size: 5,327,181 listing-days over 13 circular weeks (2026-08-06 → 2026-10-29).

## 4. Store-mixture noise (measurement finding)

Share 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:

| source | product_days | multi_price_share | median_rel_range_when_multi |
|---|---|---|---|
| club_entrepot | 168,906 | 0.237 | 0.125 |
| costco | 39,304 | 0.058 | 0.026 |
| maxi | 149,947 | 0.056 | 0.143 |
| metro | 552,776 | 0.056 | 0.188 |
| avril | 35,974 | 0.033 | 0.200 |
| superc | 231,371 | 0.030 | 0.214 |
| tt | 33,947 | 0.029 | 0.175 |
| adonis | 106,131 | 0.025 | 0.200 |
| iga | 108,586 | 0.025 | 0.194 |
| provigo | 135,183 | 0.023 | 0.231 |
| walmart | 129,980 | 0.020 | 0.143 |
| superc_flyer | 35,634 | 0.014 | 0.257 |
| mayrand | 36,455 | 0.014 | 0.164 |
| metro_flyer | 45,600 | 0.013 | 0.334 |
| iga_flyer | 45,980 | 0.013 | 0.357 |
| boite_a_grains | 463,283 | 0.012 | 0.177 |
| pa | 275,853 | 0.012 | 0.251 |
| adonis_flyer | 25,745 | 0.010 | 0.247 |
| giant_tiger | 761,998 | 0.008 | 0.200 |
| aubut | 46,219 | 0.008 | 0.136 |
| maxi_flyer | 30,101 | 0.008 | 0.160 |
| pasquier | 20,733 | 0.005 | 0.201 |
| nuvo | 373,628 | 0.005 | 0.251 |
| provigo_flyer | 23,602 | 0.005 | 0.200 |
| walmart_flyer | 42,553 | 0.004 | 0.231 |
| aliments_merci | 216,835 | 0.002 | 0.167 |
| saq | 296,305 | 0.002 | 0.080 |
| bocoboco | 58,958 | 0.002 | 0.515 |
| maturin | 37,739 | 0.002 | 0.066 |
| akhavan | 54,497 | 0.001 | 0.200 |
| epipresto | 460,710 | 0.000 |  |

Interpretation: 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.

## 5. Listing-level attrition

| step | listings | share_of_initial |
|---|---|---|
| All listings in snapshot (products table) | 124,579 | 1.000 |
| excl. SAQ (alcohol monopoly) | 117,102 | 0.940 |
| excl. pharmacy feeds | 115,352 | 0.926 |
| excl. non-food categories (health & beauty, household, pets, baby, alcohol) | 112,233 | 0.901 |
| with at least one observed price | 111,924 | 0.898 |
| excl. unrealistic prices | 111,575 | 0.896 |
| … with parsable package size (unit-price / matching universe) | 66,340 | 0.533 |
| … in a cross-banner match group (tiers A–C) | 13,273 | 0.107 |

## 6. Cross-banner matching

Tiers: 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.

Threshold calibration on UPC ground truth (all Metro×Super C candidate pairs within brand×package blocks; positives = same UPC): 

| scorer | thr | n | precision | recall |
|---|---|---|---|---|
| sort_ratio | 0.700 | 9,958.000 | 0.432 | 0.994 |
| sort_ratio | 0.750 | 8,155.000 | 0.527 | 0.992 |
| sort_ratio | 0.800 | 6,695.000 | 0.641 | 0.991 |
| sort_ratio | 0.850 | 5,601.000 | 0.764 | 0.988 |
| sort_ratio | 0.900 | 4,913.000 | 0.870 | 0.987 |
| sort_ratio | 0.950 | 4,597.000 | 0.928 | 0.986 |
| sort_ratio | 1.000 | 4,563.000 | 0.932 | 0.982 |
| tokens_equal | 1.000 | 4,563.000 | 0.932 | 0.982 |

Cross-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`.

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

### 6.1 Validation sample (240 pairs, 60 per tier, stratified random, seed 20261101)

| 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 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | 60 | 60 | 0 | 0 | 1.000 | 0.940 | 1.000 | 1.000 | 0.940 | 1.000 | 1.000 | 1.000 |  |
| B | 60 | 60 | 0 | 0 | 1.000 | 0.940 | 1.000 | 1.000 | 0.940 | 1.000 | 1.000 | 0.932 | 0.982 |
| C | 60 | 25 | 30 | 5 | 0.417 | 0.301 | 0.543 | 0.455 | 0.330 | 0.585 | 0.983 | 0.870 | 0.987 |
| D | 60 | 59 | 0 | 1 | 0.983 | 0.911 | 0.997 | 1.000 | 0.939 | 1.000 | 1.000 |  |  |

Labels: 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.

**Decision for the primary specification: tiers A + B only; C and D enter sensitivity analyses.**

## 7. Prospective collection plan (what cannot be done now)

- **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).
- **Reference prices**: log `regular_price` and `on_sale` changes in `price_log` (one extra column) so that promotion states become directly observed.
- **Minimum duration**: 26 weeks for promotion recurrence and reference-price adjustment (H3), 52 weeks for seasonal patterns and index validation against StatCan.