⚡ Swarm Architecture

Initiative 2.2 — "En stock livraison 24h" baseline + post tracking

📅 Published August 12, 2026 ⏱️ 6 min read 🔬 Peer Verified

# Initiative 2.2 — "En stock livraison 24h" baseline + post tracking

Date: 2026-05-26 Backlog item: 2.2 · Pilot (FR) — Add cart-level delivery ETA (or "En stock livraison 24h" on PDP) Friction hypothesis: "Cart shows stock, but not ETA" Funnel step: 2 — Product view → add to cart

Goal

Measure whether the "En stock - Livraison 24h" stock-label badge (live on PDPs since 2026-05-20) improves the product-view → add-to-cart → purchase funnel, and verify the badge is actually firing. Wire the result into the Web Funnel Initiatives Backlog dashboard panel as baseline-vs-actual KPI tracking, mirroring the existing Test 1 (1.5 · Test 1) pattern.

> Correction during implementation (2026-05-26): the backfilled data showed the badge > string '1_available_En stock - Livraison 24h' lives in PRODUCT_STOCK (not > PRODUCT_DELIVERY_TIME, a sparse numeric lead-time) and first appears 2026-05-20, not > the 13/05 originally given. The cutoff was realigned to 2026-05-20 (which matches the > backlog's stored start_date). Sections below reflect the corrected values.

Two funnels are measured on all FR traffic, at session grain, over two windows:

| Window | Range | Meaning | |--------|-------|---------| | Baseline | 2026-02-13 … 2026-05-19 | Pre go-live (team baseline rule, 2026-05-19) | | Post | 2026-05-20 … 2026-05-29 | Badge live. Script caps the post end to the latest date present in all three source tables (action/sessions/product) and re-runs as more arrives. |

Cutoff = 2026-05-20 (badge first appears in data; matches the stored 2.2 · Pilot start_date, which is therefore left unchanged).

Funnel definitions (session grain, FR, all traffic)

Both funnels are computed from ga4_action_events (event-level page_type distinguishes PDP from listing) joined to ga4_sessions (same-session purchase via order_count > 0). A session counts once at each stage if it has ≥1 qualifying event.

| Path | Stage A (denominator) | Stage B | Stage C | |------|----------------------|---------|---------| | PDP | sessions with a PDP view — event_name='view_item' AND page_type='product' | + add-to-cart from PDP — event_name='add_to_cart' AND page_type='product' | + purchased same session | | Vignette | sessions with a vignette interaction — event_name='select_item' on a listing page | + add-to-cart from vignette — event_name='add_to_cart' AND page_type ∈ VIGNETTE_PAGE_TYPES | + purchased same session |

`VIGNETTE_PAGE_TYPES = {search-results, product-category, product-list, category, search_with_results, list} — confirmed present on FR add_to_cart` events (probe 2026-05-26). page_type='product' is the PDP context.

Reported metrics per path × window:

  • A→B add rate, B→C add→buy rate, A→C overall conversion
  • Δ (percentage points) baseline → post

Known limitation — vignette Stage-A denominator

The lighter view_item_list browsing event is deliberately not ingested into the warehouse (see oracle_ga4_products_pull.py / oracle_ga4_action_pull.py skip lists), so there is no clean "viewed a listing" atom. select_item (GA4 "clicked/selected a product tile") is used as the vignette-interaction proxy. This slightly under-counts the vignette denominator (a user can add from a tile without a recorded select_item). Documented so the rate is interpreted as "of sessions that clicked a tile…". Fallback if undesirable: drop Stage A and report vignette as add(listing)→buy only.

The badge signal — PRODUCT_STOCK (the analyst's "dimension25")

The "En stock - Livraison 24h" badge is the item-scoped Oracle column T_ECOM_GA4_PRODUCT.PRODUCT_STOCK (VARCHAR2(255)) — specifically the value '1_available_En stock - Livraison 24h', matched by product_stock ILIKE '%livraison 24h%' (verified as the only value containing that substring). The sibling PRODUCT_DELIVERY_TIME (VARCHAR2(255)) is not the badge — it is a sparse numeric lead-time in days (mostly NULL); it was ingested too but is not used for the functional check. There is no literal DIMENSION25 column. Both columns were not ingested into ga4_product_events before this work.

The 'En stock - Livraison 24h' value first appears in FR data on 2026-05-20 (zero before; ~18.5k rows 05-20, ~36k 05-21), confirming go-live and driving the cutoff choice.

Ingestion extension (scoped, small)

1. ALTER TABLE ga4_product_events ADD COLUMN product_delivery_time TEXT, ADD COLUMN product_stock TEXT; (added to database/init_timescale_ga4.sql for fresh installs.) 2. Add PRODUCT_DELIVERY_TIME, PRODUCT_STOCK to the SELECT and the upsert column list/tuple in workers/oracle_ga4_products_pull.py (include both in the ON CONFLICT DO UPDATE set so re-pulls refresh them). 3. Backfill FR only over the recent window via the existing --schemas FRANCE --days N path (used --days 16). No GB / no full-history backfill.

Analysis outputs

1. Functional check (does the badge fire?)

On FR PDP view_item rows in the post window, count sessions where product_stock ILIKE '%livraison 24h%'. Confirms the badge appears from 2026-05-20 and its PDP coverage (% of PDP-view sessions seeing it). Surfaced as a badge-coverage KPI and in the markdown body.

2. Saw-badge vs not (effect isolation)

Within the post window, split PDP sessions into those whose PDP view carried the badge vs those that did not, and compare PDP→cart and PDP→conversion. Caveat: this cohort is confounded by product attributes (in-stock/fast-delivery SKUs convert better regardless of the label), so the gap is correlational, not a clean causal lift. Reported in the markdown body.

3. Baseline-vs-actual KPIs

wf_test_kpis rows for initiative_number='2.2 · Pilot', source_country='FR':

| kpi_index | kpi_name | baseline (pre) | actual (post) | |-----------|----------|----------------|---------------| | 1 (exists) | Conversion rate PDP to cart | PDP A→B baseline | PDP A→B post | | 2 | PDP cart → conversion | PDP B→C baseline | PDP B→C post | | 3 | Vignette → add to cart | Vignette A→B baseline | Vignette A→B post | | 4 | Vignette cart → conversion | Vignette B→C baseline | Vignette B→C post | | 5 | Badge PDP coverage (post only) | n/a | % of post PDP views with badge |

Each KPI stores baseline_value (numeric, 0–1 rate) + baseline_text (window + n), and actual_value + actual_result_text, matching the Test 1 storage convention. KPI #5 has no baseline (badge didn't exist pre-2026-05-20).

Components

| Component | Path | Change | |-----------|------|--------| | Warehouse schema | database/init_timescale_ga4.sql | + product_delivery_time, product_stock columns | | Product ingestion | workers/oracle_ga4_products_pull.py | pull the two new columns | | Refresh/analysis script | scripts/update_wf_test22_delivery_eta.py (new) | compute funnels + badge metrics, write KPIs, emit result_markdown | | Backlog test row | wf_tests (2.2 · Pilot, FR) | start_date set to 2026-05-20 (already matched; left as-is) | | KPI rows | wf_test_kpis (2.2 · Pilot, FR) | upsert KPIs #2–#5 (keep #1) |

The script follows scripts/update_wf_test5_variants.py: idempotent, re-runnable, prints a human summary, and emits the ===RESULT_MARKDOWN_BEGIN=== / ===RESULT_MARKDOWN_END=== block so the dashboard refresh button can extract and store the report body. No new dashboard endpoint or template is needed — the existing /panel/wf-backlog already renders wf_test_kpis baseline/actual columns and the report body.

Data-flow

` Oracle FRANCE.T_ECOM_GA4_PRODUCT ──(extended pull, FR --days 16)──────▶ ga4_product_events (+ delivery_time, stock) ga4_action_events + ga4_sessions ──(funnels, both windows)──┐ ga4_product_events (PRODUCT_STOCK badge) ─(coverage + saw-badge)─┤ ▼ scripts/update_wf_test22_delivery_eta.py │ ┌──────────────────────┴───────────────────────┐ ▼ ▼ wf_test_kpis (baseline/actual) result_markdown body └───────────────────────┬───────────────────────┘ ▼ /panel/wf-backlog (existing dashboard) `

Out of scope / non-goals

  • GB country (FR only).
  • Full-history backfill of the new product columns (post window only).
  • A/B variant split — this is a pre/post pilot, not a randomised test, so no
wf_test_variants rows.
  • Statistical significance testing — descriptive rates + Δpp only (post window is short).
  • New dashboard endpoints/templates.

Verification (results, 2026-05-26)

  • ✓ The badge value appears on FR PDP view_item rows from 2026-05-20 (zero before).
  • ✓ Funnel rates plausible & nested: PDP A→B baseline 39.16% / post 39.95%; PDP→purchase
16.35% / 16.70%; vignette A→B 36.26% / 36.70%. Post window auto-capped to 2026-05-21 (latest date in all three source tables).
  • ✓ wf_test_kpis for 2.2 · Pilot has populated baseline + actual for KPIs #1–#4 and
actual-only for #5 (badge coverage 53.8%); 5 rows, no duplicates (idempotent).
  • ✓ Saw-badge vs not (post): view→cart 55.97% vs 20.75%; view→purchase 23.83% vs 8.17%
(correlational — see caveat).

Cleanup

Delete temporary exploration scripts created during design/implementation: scripts/_probe_atc.py, scripts/_probe_22.py, scripts/_probe_22kpi.py, scripts/_probe_dim25.py, scripts/_probe_badge.py, scripts/_probe_verify.py.