Data Science Due Diligence Appendix

Proven

Metadata-backed facts

Table existence, row counts, schema fields, BQML model type, feature columns, model location, evaluation metrics, and aggregate completeness checks.

bq show --format=prettyjson

Code-backed

Implementation paths

SQL DDL, agent BigQuery tools, graph sync code, analytics loggers, pantry models, recipe bridge SQL, and protocol/server code paths.

delectable/ml · grocery-agent · analytics · db

Needs care

Interpretive claims

Cluster names, health intent, household persona, dietary restrictions, flavor pairing, uplift, and business impact require additional validation before being presented as measured truth.

Use "evidence suggests", not "proves".

Plane	Object	Evidence	Purpose
Food intelligence	`giant_eagle_enriched`	70,472 rows, 102 fields	Retail SKU enrichment with USDA/OFF match data, nutrition, dietary flags, health scores, tiers, scientific profile, and allergens.
Food intelligence	`cleaned_food_intelligence`	70,472 rows, 70 fields	Math-ready feature store for per-100g normalization, fallback tracking, internal health rank, and nutrient density score.
Food intelligence	`vertex_ingestion_ready`	4,742,992 rows, 13 fields	GTIN/UPC enrichment rows for Vertex ingestion with ingredients, dietary claims, allergens, health tier, Nutri-Score, NOVA, and Food Compass score.
Food intelligence	`ingredient_product_bridge`	595,878 rows, 138,729 ingredients with matches	Ingredient-to-product matching across recipe ingredients and product embeddings.
Retail analytics	`ge_shopping_events`	6,526,954 rows, 16 fields	Behavioral event spine with MPID, session, store, action, basket, SKU, price, quantity, brand, and department hierarchy.
Retail analytics	`ge_user_propensity_profiles`	9,366 profiles, 43 fields	Precomputed shopper profile hot path for mission mix, purchase-share propensities, likely restriction flags, engagement, top products, and recency JSON.
Retail analytics	`ge_co_purchase_affinity`	1,318,788 rows, 12 fields	Association-rule style SKU pairings with support, confidence in both directions, and lift.
Retail analytics	`ge_promotions`	22,853 rows, 9,044 active rows	Promotion-aware offer lookup by SKU/category, price, discount, dates, active flag, and scope.

BQML

Model identity

Model ID `delectable-cloud-stage.retail_analytics.ge_mission_clusters`. Type K-means. Location US. Created February 12, 2026 at 11:04:59 AM Pacific.

bq show --model retail_analytics.ge_mission_clusters

Features

Feature family

25 features: day/hour, unique products, basket value, average price point, department diversity, category mix, fresh-perimeter share, and center-store share.

day_of_week · hour_of_day · pct_* · basket_value

Training

Training summary

Five BQML iterations. Loss declined from 26.28 to 19.75. Final cluster sizes in training info: 1,866, 42,601, 26,892, 99,227, 21,196, and 1,337.

ML.TRAINING_INFO(...)

Evaluation

Evaluation metrics

`ML.EVALUATE` returns mean squared distance 19.747 and Davies-Bouldin index 2.257. These are clustering quality diagnostics, not business-outcome measures.

ML.EVALUATE(MODEL retail_analytics.ge_mission_clusters)

Cluster	App label	Scored sessions	Centroid summary	Distance check
1	Candy/Snack Run	1,894	Basket $29.15, 3.90 products, 2.11 dept diversity, 85.9% center store.	Avg 3.98, p50 4.04, p95 5.39.
2	Dairy & Deli Stop	43,370	Basket $43.72, 5.62 products, 33.8% dairy, 69.5% fresh perimeter.	Avg 4.04, p50 3.57, p95 7.55.
3	Fresh Produce Run	27,401	Basket $40.83, 6.49 products, 65.6% produce, 81.2% fresh perimeter.	Avg 2.66, p50 2.52, p95 4.05.
4	Weekly Stock-Up	101,161	Basket $108.22, 13.57 products, 4.67 dept diversity, 68.3% center store.	Avg 3.74, p50 3.53, p95 5.91.
5	Non-Food Essentials	21,474	Basket $44.83, 3.22 products, $9.66 avg price, low fresh/center grocery share.	Avg 6.00, p50 4.75, p95 16.03. Needs review.
6	Quick Meal Assembly	1,371	Basket $34.38, 3.68 products, 74.7% fresh perimeter, $6.86 avg price.	Avg 5.12, p50 4.85, p95 8.60.

Remote model

Embedding model

BigQuery remote model `food_intelligence.text_embedding_005` points to Vertex AI `text-embedding-005` in `us-central1` through the default cloud resource connection.

projects/delectable-cloud-stage/locations/us-central1/publishers/google/models/text-embedding-005

Embeddings

Product vectors

Identity embeddings: 70,472 vectors. Description embeddings: 53,193 vectors. Nutrition embeddings: 30,632 vectors.

ge_embeddings_identity · ge_embeddings_description · ge_embeddings_nutrition

Calibration

Bridge confidence

595,878 bridge rows, 138,729 ingredients with matches, 25,264 matched SKUs. Current confidence distribution is 818 medium and 595,060 low, with no high-confidence rows.

ingredient_product_bridge

Coverage

Health scoring coverage

Cleaned food intelligence has Food Compass on all 70,472 SKUs, NOVA on 20,910 SKUs, and Nutri-Score grade on 32,181 SKUs.

cleaned_food_intelligence

Science corpus

Reference scale

Open Food Facts has 4.3M rows, USDA branded has 454K rows, chemical compounds has 70K rows, and food-compound content has 5.1M rows.

open_food_facts · usda_branded_food · chemical_compounds

Naming drift

DDL drift to fix

The deployed remote model is `text_embedding_005`; repo SQL still references `text_embedding_model` in the recipe ingredient embedding DDL.

semantic_bridge/ddl/recipe_ingredients.sql

Profiles

Profile completeness

All 9,366 propensity profiles have `profile_updated_at`, purchase SKU map, top products JSON, and recent purchases JSON populated. Current profile timestamp is February 12, 2026 19:07:23 UTC.

ge_user_propensity_profiles

Pantry

Virtual pantry

344,781 pantry rows across 8,262 households. 130,213 rows are high/urgent replenishment candidates. Average estimated stock level is 0.091.

ge_virtual_pantry

Offers

Promotion table

22,853 promotion rows, 9,044 active rows, 17,439 distinct promo SKUs. Current sponsored fields are not populated, so avoid RMN sponsored-performance claims from this table alone.

ge_promotions

Pairing

Co-purchase graph

1.3M SKU pair rows with co-purchase count, support, directional confidence, and lift. This is the strongest current proof point for complete-the-kit and cart pairings.

ge_co_purchase_affinity

Telemetry

Agent observability

The platform has both reusable `analytics_*` tables and grocery-specific `eagle_ai_*` tables for searches, selections, conversations, sessions, and traces.

analytics/src/analytics/events/tables.py

Semantic caveat

Propensity semantics

`propensity_*` values represent purchase share of products with attributes. They should inform ranking, not hard dietary restriction filtering, unless corroborated by declared or high-confidence restriction evidence.

ml/propensity/RESTRICTION_MODEL_DESIGN.md

Unsupervised

Mission labels are interpretive

K-means assigns clusters; labels like "Weekly Stock-Up" are app interpretation. Add human validation, label stability analysis, and downstream lift tests.

Monitoring

Freshness and drift

Profile timestamps indicate a batch snapshot. Add scheduled freshness SLAs, population drift checks, feature distribution monitoring, and automatic retrain criteria.

Confidence

Ingredient bridge calibration

The bridge is large and useful, but the confidence tiers are currently dominated by low-confidence rows. Recalibrate thresholds and add sampled human review.

Restrictions

Dietary restriction separation

Split purchase-share propensities from true restriction confidence. Use declared preferences and avoidance evidence before activating hard filters.

Causal

Business impact measurement

Co-purchase and recommender metrics prove capability, not incremental lift. Add holdouts, uplift models, and surface-level attribution before ROI claims.

Governance

External data room package

Create exportable model cards, data dictionaries, schema snapshots, aggregate notebooks, IAM diagrams, retention policy, and privacy review for external diligence.

bq show --model --format=prettyjson \
  delectable-cloud-stage:retail_analytics.ge_mission_clusters

bq query --use_legacy_sql=false \
  'SELECT * FROM ML.EVALUATE(
     MODEL `delectable-cloud-stage.retail_analytics.ge_mission_clusters`
   )'

bq query --use_legacy_sql=false \
  'SELECT mission_cluster, COUNT(*) AS sessions,
          ROUND(AVG(cluster_distance), 3) AS avg_distance
   FROM `delectable-cloud-stage.retail_analytics.ge_session_clusters`
   GROUP BY mission_cluster
   ORDER BY mission_cluster'

bq show --format=prettyjson \
  delectable-cloud-stage:food_intelligence.cleaned_food_intelligence

bq query --use_legacy_sql=false \
  'SELECT COUNT(*) AS bridge_rows,
          COUNT(DISTINCT normalized_ingredient) AS ingredients_with_matches,
          COUNT(DISTINCT sku) AS matched_skus,
          ROUND(AVG(cosine_similarity), 3) AS avg_similarity
   FROM `delectable-cloud-stage.food_intelligence.ingredient_product_bridge`'

Data science proof appendix.

Due diligence needs proof, not narrative.