⚡ Swarm Architecture

Initiative 2.2 "En stock livraison 24h" — Baseline + Post Tracking Implementation Plan

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

# Initiative 2.2 "En stock livraison 24h" — Baseline + Post Tracking Implementation Plan

> For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (- [ ]) syntax for tracking.

> ⚠️ AS-BUILT CORRECTIONS (2026-05-26) — the committed script & spec are the source of truth. > During implementation the backfilled data overturned two assumptions baked into the task > text/code blocks below: > 1. Badge column: the "En stock - Livraison 24h" badge is in PRODUCT_STOCK > (value '1_available_En stock - Livraison 24h', match product_stock ILIKE '%livraison 24h%'), > not PRODUCT_DELIVERY_TIME (which is a sparse numeric lead-time). So badge_split() > queries product_stock and BADGE_LIKE = '%livraison 24h%'. > 2. Go-live / cutoff: the badge value first appears 2026-05-20 (not 13/05), matching > the backlog start_date. Windows are baseline 2026-02-13…05-19, post 2026-05-20…05-29. > 3. The script also gained a common_max_date() cap so the post window only covers dates > present in all three source tables (action/sessions/product) — avoids mixing a fresh > product backfill with a staler action pull. > Code/date/column literals in the tasks below are pre-correction; read them with these fixes.

Goal: Measure the product-view→add-to-cart→purchase funnel (PDP and vignette paths) on all FR traffic, before vs after the 2026-05-13 "En stock livraison 24h" PDP badge go-live, verify the badge is firing via the dimension25/PRODUCT_DELIVERY_TIME signal, and surface baseline-vs-actual KPIs in the Web Funnel Initiatives Backlog dashboard.

Architecture: A single idempotent analysis script (scripts/update_wf_test22_delivery_eta.py) reads ga4_action_events + ga4_sessions (funnels, both windows) and ga4_product_events (badge signal, post window), then writes wf_test_kpis rows and emits a result_markdown block. The badge columns (product_delivery_time, product_stock) are added to ga4_product_events and backfilled for FR over the post window only. No new dashboard endpoint/template — the existing /panel/wf-backlog already renders wf_test_kpis baseline/actual columns.

Tech Stack: Python 3.12 (project venv), psycopg2, TimescaleDB (:5434, ga4_), PostgreSQL (:5433, wf_), Oracle thin client (oracledb) for the ingestion backfill. Spec: docs/superpowers/specs/2026-05-26-initiative-2.2-delivery-eta-design.md.

Reference pattern: scripts/update_wf_test5_variants.py (Test 1) — same structure: compute → upsert wf_test_kpis → print human summary → emit ===RESULT_MARKDOWN_BEGIN===/===RESULT_MARKDOWN_END=== block.

Environment notes:

  • Run Python via the project venv: & "C:\Users\piesamso1\OneDrive - Publicis Groupe\Bureau\Lyréco\venv\Scripts\python.exe".
  • Oracle needs the correct TNS path (the .env value is stale): set $env:ORACLE_TNS_ADMIN = "C:\Users\piesamso1\OneDrive - Publicis Groupe\Bureau\Lyréco\oracle" before any Oracle pull. Requires GlobalProtect VPN connected.
  • wf_ tables live in PostgreSQL via database.crm_db.CRMDatabase; ga4_ in TimescaleDB via database.timeseries_db.TimeseriesDatabase. initiative_number is TEXT in the live DB (e.g. "2.2 · Pilot").

---

File Structure

| File | Responsibility | Action | |------|----------------|--------| | database/init_timescale_ga4.sql | ga4_product_events DDL | Modify — add 2 columns (fresh-install parity) | | workers/oracle_ga4_products_pull.py | Oracle→Timescale product pull | Modify — select + upsert the 2 new columns | | scripts/update_wf_test22_delivery_eta.py | Funnel + badge analysis, KPI upsert, markdown | Create |

---

Task 1: Add badge columns to the product-event warehouse + ingestion

Files:

  • Modify: database/init_timescale_ga4.sql (ga4_product_events DDL, around line 228 page_type)
  • Modify: workers/oracle_ga4_products_pull.py (SELECT ~line 57-80, upsert ~line 108-130, tuple ~line 151-170)

  • [ ] Step 1: Add columns to the DDL (fresh-install parity)

In database/init_timescale_ga4.sql, in the ga4_product_events CREATE TABLE, add two columns immediately after the page_type TEXT, line (line 228):

`sql page_type TEXT, product_delivery_time TEXT, -- GA4 dimension25 ("En stock livraison 24h" badge) product_stock TEXT, -- sibling stock-state custom dimension last_pulled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), `

  • [ ] Step 2: ALTER the live Timescale table (idempotent)

Run: `powershell & "C:\Users\piesamso1\OneDrive - Publicis Groupe\Bureau\Lyréco\venv\Scripts\python.exe" -c "from database.timeseries_db import TimeseriesDatabase; ts=TimeseriesDatabase(); ts.execute('ALTER TABLE ga4_product_events ADD COLUMN IF NOT EXISTS product_delivery_time TEXT, ADD COLUMN IF NOT EXISTS product_stock TEXT'); print('altered')" ` Expected: prints altered.

  • [ ] Step 3: Add the columns to the ingestion SELECT

In workers/oracle_ga4_products_pull.py, in PRODUCT_SQL_TEMPLATE (ends at PAGE_TYPE ~line 75), change the tail of the column list:

`sql PRODUCT_BMSM_FLAG, PRODUCT_BMSM_TIER, PRODUCT_COUPON_CODE, PAGE_TYPE, PRODUCT_DELIVERY_TIME, PRODUCT_STOCK FROM {schema}.T_ECOM_GA4_PRODUCT `

  • [ ] Step 4: Add the columns to the upsert INSERT list + ON CONFLICT set

In the upsert_sql string (~line 108-130), add the two columns to the INSERT column list (after page_type) and to the DO UPDATE SET so re-pulls populate them on existing rows:

`python upsert_sql = ( "INSERT INTO ga4_product_events (" "event_date, product_row_id, source_country, " "event_unique_id, visit_unique_id, visitor_id, " "event_name, event_timestamp, " "product_reference, product_name, product_brand, product_variant, " "web_category1, web_category2, web_category3, web_category4, " "product_quantity, local_product_price, local_sales_amount, local_refund_amount, " "currency_code, product_category_id, " "product_list_name, product_list_id, product_list_index, " "product_recommendation_program, sponsored_product, " "product_promotion_name, product_promotion_id, " "product_creative_name, product_creative_slot, " "product_deal_type, product_bmsm_flag, product_bmsm_tier, product_coupon_code, " "page_type, product_delivery_time, product_stock" ") VALUES %s " "ON CONFLICT (event_date, event_unique_id, product_row_id, source_country) DO UPDATE SET " " product_quantity = EXCLUDED.product_quantity, " " local_product_price = EXCLUDED.local_product_price, " " local_sales_amount = EXCLUDED.local_sales_amount, " " product_recommendation_program = EXCLUDED.product_recommendation_program, " " product_delivery_time = EXCLUDED.product_delivery_time, " " product_stock = EXCLUDED.product_stock, " " last_pulled_at = NOW()" ) `

  • [ ] Step 5: Add the two values to the row tuple

In the batch.append((...)) (~line 151-170), append the two new values at the end of the tuple, immediately after r["PAGE_TYPE"],:

`python r["PAGE_TYPE"], r["PRODUCT_DELIVERY_TIME"], r["PRODUCT_STOCK"], )) `

  • [ ] Step 6: Backfill FR over the post window (badge didn't exist before 05-13)

--days N filters EVENT_DATE >= TRUNC(SYSDATE) - N. With today = 2026-05-26, --days 16 covers from 2026-05-10 (a couple of days of margin before the 05-13 cutoff is fine and lets us confirm the badge is absent pre-cutoff). FR only.

Run (VPN must be up): `powershell $env:ORACLE_TNS_ADMIN = "C:\Users\piesamso1\OneDrive - Publicis Groupe\Bureau\Lyréco\oracle" & "C:\Users\piesamso1\OneDrive - Publicis Groupe\Bureau\Lyréco\venv\Scripts\python.exe" -m workers.oracle_ga4_products_pull --schemas FRANCE --days 16 ` Expected: log lines ending with FRANCE: events ( min) and DONE schemas=FRANCE. Non-trivial N (hundreds of thousands+).

  • [ ] Step 7: Verify the new columns are now populated in the warehouse

Run: `powershell & "C:\Users\piesamso1\OneDrive - Publicis Groupe\Bureau\Lyréco\venv\Scripts\python.exe" -c "from database.timeseries_db import TimeseriesDatabase; ts=TimeseriesDatabase(); print(ts.query(\"SELECT COUNT(*) n, COUNT(product_delivery_time) nd FROM ga4_product_events WHERE source_country='FR' AND event_name='view_item' AND page_type='product' AND event_date BETWEEN '2026-05-13' AND '2026-05-21'\"))" ` Expected: n > 0 and nd > 0 (delivery_time populated for a meaningful share of post-window PDP views).

  • [ ] Step 8: Commit

`bash git add database/init_timescale_ga4.sql workers/oracle_ga4_products_pull.py git commit -m "feat: ingest GA4 product_delivery_time + product_stock (dimension25)

Co-Authored-By: Claude Opus 4.7 " `

---

Task 2: Confirm the badge string value + first-appearance date

This determines the BADGE_LIKE predicate the analysis uses. Query our own warehouse (fast, indexed by event_date chunk) instead of the 113M-row Oracle table.

Files: none (verification only).

  • [ ] Step 1: List distinct delivery-time values on FR PDP views, post window

Run: `powershell & "C:\Users\piesamso1\OneDrive - Publicis Groupe\Bureau\Lyréco\venv\Scripts\python.exe" -c "from database.timeseries_db import TimeseriesDatabase; ts=TimeseriesDatabase(); rows=ts.query(\"SELECT product_delivery_time v, COUNT(*) n FROM ga4_product_events WHERE source_country='FR' AND event_name='view_item' AND page_type='product' AND event_date BETWEEN '2026-05-13' AND '2026-05-21' GROUP BY product_delivery_time ORDER BY n DESC LIMIT 20\"); [print(repr(r['v']), r['n']) for r in rows]" ` Expected: one of the values is the "En stock livraison 24h" badge (exact casing/wording visible). Note the literal.

  • [ ] Step 2: Confirm the badge is absent before the cutoff

Run: `powershell & "C:\Users\piesamso1\OneDrive - Publicis Groupe\Bureau\Lyréco\venv\Scripts\python.exe" -c "from database.timeseries_db import TimeseriesDatabase; ts=TimeseriesDatabase(); rows=ts.query(\"SELECT event_date, COUNT(*) n FROM ga4_product_events WHERE source_country='FR' AND event_name='view_item' AND page_type='product' AND product_delivery_time ILIKE '%24h%' AND event_date BETWEEN '2026-05-10' AND '2026-05-21' GROUP BY event_date ORDER BY event_date\"); [print(r['event_date'], r['n']) for r in rows]" ` Expected: near-zero rows before 2026-05-13, then non-trivial daily counts from 2026-05-13 onward. This is the functional confirmation that the badge went live on the stated date.

  • [ ] Step 3: Decide BADGE_LIKE

Default predicate for Task 3 is product_delivery_time ILIKE '%24h%'. If Step 1 shows the literal is cleanly 'En stock livraison 24h' with no unrelated 24h values, you may instead set BADGE_LIKE = 'En stock livraison 24h' (exact, no wildcards) for precision. Record the chosen value to use in Task 3 Step 1. No commit (verification only).

---

Task 3: Write the analysis script

Files:

  • Create: scripts/update_wf_test22_delivery_eta.py

  • [ ] Step 1: Write the full script

Create scripts/update_wf_test22_delivery_eta.py with this exact content. (If Task 2 Step 3 chose an exact literal, change BADGE_LIKE accordingly — it is the only value that may need adjustment.)

`python """Initiative 2.2 — "En stock livraison 24h" PDP badge: baseline vs post tracking.

Computes two session-grain funnels on ALL FR traffic for two windows (baseline = 3 months pre go-live; post = go-live..cutoff), plus the badge functional check + saw-badge-vs-not split using the dimension25 signal (ga4_product_events.product_delivery_time). Writes wf_test_kpis rows for '2.2 · Pilot' (FR) and emits a result_markdown block for the dashboard.

Idempotent / re-runnable. Mirrors scripts/update_wf_test5_variants.py.

Run: python scripts/update_wf_test22_delivery_eta.py """ from __future__ import annotations

import datetime as dt import sys from pathlib import Path

_REPO = Path(__file__).resolve().parent.parent if str(_REPO) not in sys.path: sys.path.insert(0, str(_REPO))

from database.crm_db import CRMDatabase from database.timeseries_db import TimeseriesDatabase

INITIATIVE = "2.2 · Pilot" SOURCE_COUNTRY = "FR"

# Go-live of "En stock livraison 24h" (user-confirmed). Baseline = 3 full # months before (team rule 2026-05-19); post = go-live .. analysis cutoff. CUTOFF = dt.date(2026, 5, 13) BASELINE_START = dt.date(2026, 2, 13) BASELINE_END = dt.date(2026, 5, 12) POST_START = dt.date(2026, 5, 13) POST_END = dt.date(2026, 5, 29)

# add_to_cart page_type contexts that represent a product tile in a listing # (confirmed present on FR add_to_cart events, probe 2026-05-26). PDP = 'product'. VIGNETTE_PAGE_TYPES = [ "search-results", "product-category", "product-list", "category", "search_with_results", "list", ]

# Badge match predicate value (see plan Task 2). Robust substring by default. BADGE_LIKE = "%24h%"

def pct(num: int, den: int) -> float: """Rate in [0,1]; 0 when denominator is 0.""" return (num / den) if den else 0.0

def funnel(ts: TimeseriesDatabase, start: dt.date, end: dt.date) -> dict: """Session-grain PDP + vignette funnel counts for [start, end] (FR).

Stage A = saw PDP (view_item@product) / saw vignette (select_item@listing) Stage B = + add_to_cart from that surface Stage C = + purchased in the same window's sessions """ rows = ts.query( """ WITH ev AS ( SELECT visit_unique_id, bool_or(event_name='view_item' AND page_type='product') AS pdp_view, bool_or(event_name='add_to_cart' AND page_type='product') AS pdp_atc, bool_or(event_name='select_item' AND page_type = ANY(%(vig)s)) AS vig_view, bool_or(event_name='add_to_cart' AND page_type = ANY(%(vig)s)) AS vig_atc FROM ga4_action_events WHERE source_country='FR' AND event_date BETWEEN %(s)s AND %(e)s AND visit_unique_id IS NOT NULL GROUP BY visit_unique_id ), buy AS ( SELECT DISTINCT visit_unique_id FROM ga4_sessions WHERE source_country='FR' AND session_date BETWEEN %(s)s AND %(e)s AND order_count > 0 ) SELECT COUNT(*) FILTER (WHERE pdp_view) AS pdp_a, COUNT(*) FILTER (WHERE pdp_view AND pdp_atc) AS pdp_b, COUNT(*) FILTER (WHERE pdp_view AND pdp_atc AND b.visit_unique_id IS NOT NULL) AS pdp_c, COUNT(*) FILTER (WHERE vig_view) AS vig_a, COUNT(*) FILTER (WHERE vig_view AND vig_atc) AS vig_b, COUNT(*) FILTER (WHERE vig_view AND vig_atc AND b.visit_unique_id IS NOT NULL) AS vig_c FROM ev LEFT JOIN buy b USING (visit_unique_id) """, {"vig": VIGNETTE_PAGE_TYPES, "s": start, "e": end}, ) r = {k: int(v or 0) for k, v in rows[0].items()} return r

def badge_split(ts: TimeseriesDatabase, start: dt.date, end: dt.date) -> list[dict]: """Post-window PDP sessions split by whether the PDP view carried the badge.

Returns up to 2 rows: saw_badge in {True, False} with pdp/atc/buy session counts. PDP-view population comes from ga4_product_events (item-scoped, where the badge lives); add_to_cart + purchase come from action/session tables. """ rows = ts.query( """ WITH pdp_badge AS ( SELECT visit_unique_id, bool_or(product_delivery_time ILIKE %(badge)s) AS saw_badge FROM ga4_product_events WHERE source_country='FR' AND event_date BETWEEN %(s)s AND %(e)s AND event_name='view_item' AND page_type='product' AND visit_unique_id IS NOT NULL GROUP BY visit_unique_id ), ev AS ( SELECT visit_unique_id, bool_or(event_name='add_to_cart' AND page_type='product') AS pdp_atc FROM ga4_action_events WHERE source_country='FR' AND event_date BETWEEN %(s)s AND %(e)s AND visit_unique_id IS NOT NULL GROUP BY visit_unique_id ), buy AS ( SELECT DISTINCT visit_unique_id FROM ga4_sessions WHERE source_country='FR' AND session_date BETWEEN %(s)s AND %(e)s AND order_count > 0 ) SELECT COALESCE(p.saw_badge, false) AS saw_badge, COUNT(*) AS pdp_sessions, COUNT(*) FILTER (WHERE e.pdp_atc) AS atc_sessions, COUNT(*) FILTER (WHERE e.pdp_atc AND b.visit_unique_id IS NOT NULL) AS buy_sessions FROM pdp_badge p LEFT JOIN ev e USING (visit_unique_id) LEFT JOIN buy b USING (visit_unique_id) GROUP BY COALESCE(p.saw_badge, false) """, {"badge": BADGE_LIKE, "s": start, "e": end}, ) return [{k: (bool(v) if k == "saw_badge" else int(v or 0)) for k, v in r.items()} for r in rows]

def upsert_kpi(crm: CRMDatabase, kpi_index: int, kpi_name: str, expected: str, baseline_value, baseline_text, actual_value, actual_text) -> None: """Insert-or-update one wf_test_kpis row for the 2.2 pilot.""" crm.execute( """ INSERT INTO wf_test_kpis ( initiative_number, source_country, kpi_index, kpi_name, expected_result, baseline_value, baseline_text, actual_value, actual_result_text, last_computed_at, last_ingested_at ) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,NOW(),NOW()) ON CONFLICT (initiative_number, source_country, kpi_index) DO UPDATE SET kpi_name = EXCLUDED.kpi_name, expected_result = EXCLUDED.expected_result, baseline_value = EXCLUDED.baseline_value, baseline_text = EXCLUDED.baseline_text, actual_value = EXCLUDED.actual_value, actual_result_text = EXCLUDED.actual_result_text, last_computed_at = NOW(), last_ingested_at = NOW() """, (INITIATIVE, SOURCE_COUNTRY, kpi_index, kpi_name, expected, baseline_value, baseline_text, actual_value, actual_text), )

def main() -> int: crm = CRMDatabase() ts = TimeseriesDatabase()

print("1. Computing baseline funnel (FR, %s → %s)…" % (BASELINE_START, BASELINE_END)) bl = funnel(ts, BASELINE_START, BASELINE_END) print(" PDP A=%(pdp_a)d B=%(pdp_b)d C=%(pdp_c)d" % bl) print(" VIGN A=%(vig_a)d B=%(vig_b)d C=%(vig_c)d" % bl)

print("2. Computing post funnel (FR, %s → %s)…" % (POST_START, POST_END)) po = funnel(ts, POST_START, POST_END) print(" PDP A=%(pdp_a)d B=%(pdp_b)d C=%(pdp_c)d" % po) print(" VIGN A=%(vig_a)d B=%(vig_b)d C=%(vig_c)d" % po)

print("3. Computing badge split (post)…") split = badge_split(ts, POST_START, POST_END) by = {r["saw_badge"]: r for r in split} saw = by.get(True, {"pdp_sessions": 0, "atc_sessions": 0, "buy_sessions": 0}) nob = by.get(False, {"pdp_sessions": 0, "atc_sessions": 0, "buy_sessions": 0}) total_pdp = saw["pdp_sessions"] + nob["pdp_sessions"] coverage = pct(saw["pdp_sessions"], total_pdp) print(" saw-badge PDP sessions=%d no-badge=%d coverage=%.1f%%" % (saw["pdp_sessions"], nob["pdp_sessions"], 100 * coverage))

# ---- rates (0..1) ---- bl_pdp_ab, po_pdp_ab = pct(bl["pdp_b"], bl["pdp_a"]), pct(po["pdp_b"], po["pdp_a"]) bl_pdp_bc, po_pdp_bc = pct(bl["pdp_c"], bl["pdp_b"]), pct(po["pdp_c"], po["pdp_b"]) bl_vig_ab, po_vig_ab = pct(bl["vig_b"], bl["vig_a"]), pct(po["vig_b"], po["vig_a"]) bl_vig_bc, po_vig_bc = pct(bl["vig_c"], bl["vig_b"]), pct(po["vig_c"], po["vig_b"])

def win(s, e): # human window text return "%s → %s" % (s, e)

bl_txt = "Baseline %s (FR, all traffic)" % win(BASELINE_START, BASELINE_END) po_txt = "Post %s (FR, all traffic; refresh as data lands)" % win(POST_START, POST_END)

print("4. Fixing 2.2 · Pilot start_date → %s…" % POST_START) crm.execute( "UPDATE wf_tests SET start_date=%s, last_ingested_at=NOW() " "WHERE initiative_number=%s AND source_country=%s", (POST_START, INITIATIVE, SOURCE_COUNTRY), )

print("5. Upserting wf_test_kpis rows…") upsert_kpi(crm, 1, "Conversion rate PDP to cart", "Improve conversion rate", bl_pdp_ab, "%s: %d PDP-view sessions, %d added (%.2f%%)" % (bl_txt, bl["pdp_a"], bl["pdp_b"], 100 * bl_pdp_ab), po_pdp_ab, "%s: %d PDP-view sessions, %d added (%.2f%%)" % (po_txt, po["pdp_a"], po["pdp_b"], 100 * po_pdp_ab)) upsert_kpi(crm, 2, "PDP cart → conversion", "Improve conversion rate", bl_pdp_bc, "%s: %d add-to-cart sessions, %d purchased (%.2f%%)" % (bl_txt, bl["pdp_b"], bl["pdp_c"], 100 * bl_pdp_bc), po_pdp_bc, "%s: %d add-to-cart sessions, %d purchased (%.2f%%)" % (po_txt, po["pdp_b"], po["pdp_c"], 100 * po_pdp_bc)) upsert_kpi(crm, 3, "Vignette → add to cart", "Improve conversion rate", bl_vig_ab, "%s: %d vignette-interaction sessions, %d added (%.2f%%)" % (bl_txt, bl["vig_a"], bl["vig_b"], 100 * bl_vig_ab), po_vig_ab, "%s: %d vignette-interaction sessions, %d added (%.2f%%)" % (po_txt, po["vig_a"], po["vig_b"], 100 * po_vig_ab)) upsert_kpi(crm, 4, "Vignette cart → conversion", "Improve conversion rate", bl_vig_bc, "%s: %d add-to-cart sessions, %d purchased (%.2f%%)" % (bl_txt, bl["vig_b"], bl["vig_c"], 100 * bl_vig_bc), po_vig_bc, "%s: %d add-to-cart sessions, %d purchased (%.2f%%)" % (po_txt, po["vig_b"], po["vig_c"], 100 * po_vig_bc)) upsert_kpi(crm, 5, "Badge PDP coverage (post)", "Badge fires on PDP views", None, "n/a (badge did not exist before %s)" % POST_START, coverage, "Post %s: %d of %d PDP-view sessions saw the badge (%.1f%%)" % (win(POST_START, POST_END), saw["pdp_sessions"], total_pdp, 100 * coverage))

# ---- result markdown ---- def rate(n, d): return "%.2f%%" % (100 * pct(n, d))

def dpp(b, p): # delta in percentage points d = round(100 * (p - b), 2) return ("+%.2f" % d) if d > 0 else ("%.2f" % d)

saw_ab = pct(saw["atc_sessions"], saw["pdp_sessions"]) nob_ab = pct(nob["atc_sessions"], nob["pdp_sessions"]) saw_ac = pct(saw["buy_sessions"], saw["pdp_sessions"]) nob_ac = pct(nob["buy_sessions"], nob["pdp_sessions"]) today = dt.date.today().isoformat()

body_md = f"""## Initiative 2.2 — "En stock livraison 24h" PDP badge (FR)

Data refreshed: {today} · script: scripts/update_wf_test22_delivery_eta.py Baseline: {BASELINE_START} → {BASELINE_END} · Post (badge live): {POST_START} → {POST_END} All FR traffic, session grain, same-session purchase.

Funnel — baseline vs post

| Path | Stage | Baseline | Post | Δ (pp) | |---|---|---:|---:|---:| | PDP | view → add-to-cart | {rate(bl['pdp_b'], bl['pdp_a'])} | {rate(po['pdp_b'], po['pdp_a'])} | {dpp(bl_pdp_ab, po_pdp_ab)} | | PDP | add-to-cart → purchase | {rate(bl['pdp_c'], bl['pdp_b'])} | {rate(po['pdp_c'], po['pdp_b'])} | {dpp(bl_pdp_bc, po_pdp_bc)} | | PDP | view → purchase (overall) | {rate(bl['pdp_c'], bl['pdp_a'])} | {rate(po['pdp_c'], po['pdp_a'])} | {dpp(pct(bl['pdp_c'], bl['pdp_a']), pct(po['pdp_c'], po['pdp_a']))} | | Vignette | interaction → add-to-cart | {rate(bl['vig_b'], bl['vig_a'])} | {rate(po['vig_b'], po['vig_a'])} | {dpp(bl_vig_ab, po_vig_ab)} | | Vignette | add-to-cart → purchase | {rate(bl['vig_c'], bl['vig_b'])} | {rate(po['vig_c'], po['vig_b'])} | {dpp(bl_vig_bc, po_vig_bc)} | | Vignette | interaction → purchase (overall) | {rate(bl['vig_c'], bl['vig_a'])} | {rate(po['vig_c'], po['vig_a'])} | {dpp(pct(bl['vig_c'], bl['vig_a']), pct(po['vig_c'], po['vig_a']))} |

> Baseline n: PDP-view sessions {bl['pdp_a']:,}, vignette sessions {bl['vig_a']:,}. > Post n: PDP-view sessions {po['pdp_a']:,}, vignette sessions {po['vig_a']:,}. > Vignette Stage-A uses select_item (tile click) as the interaction proxy > (view_item_list is not ingested) — read as "of sessions that clicked a tile…".

Badge functional check + saw-badge vs not (post window)

Badge PDP coverage: {rate(saw['pdp_sessions'], total_pdp)} of post PDP-view sessions carried dimension25 ("En stock livraison 24h") — match product_delivery_time ILIKE '{BADGE_LIKE}'.

| Cohort (post) | PDP-view sess | view→cart | view→purchase | |---|---:|---:|---:| | Saw badge | {saw['pdp_sessions']:,} | {rate(saw['atc_sessions'], saw['pdp_sessions'])} | {rate(saw['buy_sessions'], saw['pdp_sessions'])} | | Did not see badge | {nob['pdp_sessions']:,} | {rate(nob['atc_sessions'], nob['pdp_sessions'])} | {rate(nob['buy_sessions'], nob['pdp_sessions'])} | | Δ (saw − not, pp) | — | {dpp(nob_ab, saw_ab)} | {dpp(nob_ac, saw_ac)} |

> Coverage > 0 from {POST_START} confirms the badge is firing on PDPs. > The saw-vs-not split isolates the badge effect from seasonality better than raw pre/post.

Next action

  • Re-run scripts/update_wf_test22_delivery_eta.py (or the dashboard refresh button) as
more post-window days land, through {POST_END}. """

print("\n===RESULT_MARKDOWN_BEGIN===") print(body_md) print("===RESULT_MARKDOWN_END===") return 0

if __name__ == "__main__": sys.exit(main()) `

  • [ ] Step 2: Byte-compile to catch syntax errors

Run: `powershell & "C:\Users\piesamso1\OneDrive - Publicis Groupe\Bureau\Lyréco\venv\Scripts\python.exe" -m py_compile scripts/update_wf_test22_delivery_eta.py; if ($?) { "compile OK" } ` Expected: compile OK, no traceback.

---

Task 4: Run the analysis end-to-end and verify outputs

Files: none (execution + verification).

  • [ ] Step 1: Run the script

Run: `powershell & "C:\Users\piesamso1\OneDrive - Publicis Groupe\Bureau\Lyréco\venv\Scripts\python.exe" scripts/update_wf_test22_delivery_eta.py ` Expected: prints steps 1–5, then the ===RESULT_MARKDOWN_BEGIN===…===RESULT_MARKDOWN_END=== block. No traceback.

  • [ ] Step 2: Sanity-check the funnel numbers

Inspect the printed counts. Expected sanity properties:

  • pdp_a ≥ pdp_b ≥ pdp_c and vig_a ≥ vig_b ≥ vig_c (stages are nested) in both windows.
  • Rates are within (0%, 100%) — not 0 and not 100.
  • Baseline pdp_a (3 months) is much larger than post pdp_a (~2 weeks).
If a stage is 0 or a rate is exactly 0/100, stop and recheck the page_type sets and window dates before continuing.

  • [ ] Step 3: Verify KPI rows persisted with baseline + actual

Run: `powershell & "C:\Users\piesamso1\OneDrive - Publicis Groupe\Bureau\Lyréco\venv\Scripts\python.exe" -c "from database.crm_db import CRMDatabase; c=CRMDatabase(); rows=c.query(\"SELECT kpi_index,kpi_name,baseline_value,actual_value FROM wf_test_kpis WHERE initiative_number='2.2 · Pilot' AND source_country='FR' ORDER BY kpi_index\"); [print(r) for r in rows]" ` Expected: 5 rows. KPIs 1–4 have non-null baseline_value and actual_value; KPI 5 has null baseline and non-null actual (coverage).

  • [ ] Step 4: Verify the start_date fix

Run: `powershell & "C:\Users\piesamso1\OneDrive - Publicis Groupe\Bureau\Lyréco\venv\Scripts\python.exe" -c "from database.crm_db import CRMDatabase; c=CRMDatabase(); print(c.query(\"SELECT initiative_number,start_date FROM wf_tests WHERE initiative_number='2.2 · Pilot' AND source_country='FR'\"))" ` Expected: start_date = 2026-05-13.

  • [ ] Step 5: Verify idempotency (re-run changes nothing structural)

Re-run the script once more (Step 1 command). Expected: completes cleanly; Step 3 still shows exactly 5 KPI rows (values may refresh, no duplicates).

---

Task 5: Verify dashboard render, then commit

Files: none (verification + commit).

  • [ ] Step 1: Confirm the backlog panel renders the 2.2 KPIs

The dashboard service LeadContagionDashboard already serves /panel/wf-backlog. Fetch the FR panel and confirm the 2.2 KPI names + values appear (Basic Auth from .dashboard_extras.env; substitute a valid user/password): `powershell & "C:\Users\piesamso1\OneDrive - Publicis Groupe\Bureau\Lyréco\venv\Scripts\python.exe" -c "import urllib.request,base64; u='http://127.0.0.1:8001/panel/wf-backlog?country=FR'; r=urllib.request.Request(u); r.add_header('Authorization','Basic '+base64.b64encode(b'lyreco:REPLACE_WITH_PASSWORD').decode()); h=urllib.request.urlopen(r).read().decode('utf-8','replace'); print('Badge PDP coverage' in h, 'Vignette → add to cart' in h)" ` Expected: True True (both new KPI names present in the rendered HTML). If the dashboard isn't running or auth differs, instead confirm via the DB check in Task 4 Step 3 and note the panel reads the same table.

  • [ ] Step 2: Commit the analysis script

`bash git add scripts/update_wf_test22_delivery_eta.py git commit -m "feat: initiative 2.2 delivery-ETA baseline + post funnel tracking

PDP and vignette view->add-to-cart->purchase funnels (FR, session grain) for baseline (2026-02-13..05-12) vs post (2026-05-13..05-29), plus dimension25 badge coverage + saw-badge-vs-not split. Writes wf_test_kpis for 2.2 Pilot and emits result_markdown for the backlog panel.

Co-Authored-By: Claude Opus 4.7 " `

  • [ ] Step 3: Confirm working tree is clean of scratch files

Run: `bash git status --short ` Expected: no scripts/_probe_.py present (deleted during design); only intended changes committed. If any _probe_.py reappeared, delete them.

---

Self-Review (completed by plan author)

Spec coverage:

  • Windows (baseline/post, cutoff 05-13) → Task 3 constants, Task 4 verification. ✓
  • PDP + vignette funnels, session grain, page_type split → Task 3 funnel(). ✓
  • Vignette select_item denominator + caveat → Task 3 funnel() + markdown note. ✓
  • dimension25 = PRODUCT_DELIVERY_TIME ingestion (schema + pull + FR post backfill) → Task 1. ✓
  • Functional check + saw-badge-vs-not split → Task 3 badge_split() + markdown. ✓
  • 5 KPI rows (keep #1, add #2–#5) with baseline/actual → Task 3 upsert_kpi(), Task 4 Step 3. ✓
  • start_date fix 05-20 → 05-13 → Task 3 main(), Task 4 Step 4. ✓
  • result_markdown for backlog panel, no new endpoint → Task 3 + Task 5 Step 1. ✓
  • Cleanup of probe scripts → done during design; re-checked Task 5 Step 3. ✓

Placeholder scan: BADGE_LIKE is a concrete default (%24h%) with an explicit Task 2 confirmation step; REPLACE_WITH_PASSWORD is an intentional credential the operator substitutes. No TODO/TBD/"handle edge cases". ✓

Type/name consistency: funnel() returns dict keys pdp_a/b/c, vig_a/b/c used consistently; badge_split() returns saw_badge/pdp_sessions/atc_sessions/buy_sessions used consistently; upsert_kpi() signature matches all 5 call sites; pct() used throughout. ✓