Queries · q408 · Support Operations Manager · week 2, night 4
“Which of Studio Vrij's carriers caused the May-July 2025 delivery breakdown, and did its performance fully recover afterward?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
Week 1; shared with Daniel Achterberg, VP Customer Experience
Review how the helpdesk runs: queues and agent groups, use of macros and knowledge articles, SLA targets and breaches, and what makes tickets slow.
The manager, at the start of week 2
Subject: Re: Helpdesk Operating Review — great start, here's where to push next
Ben,
This is exactly the kind of baseline I wanted — and the speed-to-CSAT finding alone is worth putting in front of Daniel as-is. A few things stood out that I want sharpened before we present or act on them.
What surprised me / needs explaining:
Questions for next week, in priority order:
Thanks — this is shaping up well. Let's make next week about turning the Marrowby & Vane / Studio Vrij question into something actionable.
Mia
The analyst · week 2, night 4, request 2 of 3
The picture is now very clear and it changes the recommendation completely. Report 1 shows Marrowby & Vane gets 51.6% of shipments from Albion Parcel — the exact carrier the COO team flagged as broken from November onward — while Report 2 shows Marrowby & Vane's delivery-tagged tickets were near-normal all year (240-1,196 min/ticket, Feb-Oct) until November (9,533 min, 61.8% breach) and December (19,274 min, 100% breach). That's a live, ongoing carrier failure, not a staffing gap. Studio Vrij tells a different story: its delivery-ticket resolution time exploded in May-July 2025 (8,528 / 29,549 / 8,763 minutes) and then fully recovered by September-December (83-680 minutes, 0% breach) — this matches almost exactly what the VP Customer Experience team posted last week about a merchant's 'local May 2025 delivery breakdown... not a platform-wide issue.' That was very likely Studio Vrij, and it's already over. Neither merchant looks like a 'hire more agents' case. Tonight I want to (1) pin the Albion Parcel collapse specifically to Marrowby & Vane's own shipments month-by-month to nail the mechanism and check if it's still ongoing, (2) find which of Studio Vrij's carriers caused the May-July spike and confirm it's genuinely resolved, and (3) run the clean controlled comparison Mia actually needs for a decision: strip the known incident months out of each tenant's numbers and see if what's left still looks worse than Cragsley's baseline, or whether the whole gap was the carrier event. On the knowledge-base question: the event_name inventory (Report 3) shows only 8 tracked events platform-wide — none related to viewing a help/KB article — so combined with last week's zero-match page_views test, I'm closing that question as 'not measurable with current instrumentation' rather than spending another slot guessing.
The engineer · 1 dry run on the empty schema, then the submission
SELECT s.carrier AS carrier, cd.month_label AS month_label, COUNT(*) AS shipment_count, ROUND(AVG(EXTRACT(EPOCH FROM (s.delivered_at - s.shipped_at)) / 86400.0) FILTER (WHERE s.delivered_at IS NOT NULL), 1) AS avg_delivery_days, ROUND(100.0 * COUNT(*) FILTER (WHERE s.delivered_at IS NOT NULL AND s.delivered_at - s.shipped_at <= INTERVAL '3 days') / NULLIF(COUNT(*) FILTER (WHERE s.delivered_at IS NOT NULL), 0), 1) AS pct_delivered_within_3_days FROM shipments s JOIN fulfillments f ON f.id = s.fulfillment_id JOIN orders o ON o.id = f.order_id JOIN tenants t ON t.id = o.tenant_id JOIN calendar_days cd ON cd.calendar_date = CAST(s.shipped_at AS date) WHERE t.name = 'Studio Vrij' AND s.carrier IN ('Lowlands Express', 'Polder Post') AND s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2026-01-01' GROUP BY s.carrier, cd.month_label ORDER BY s.carrier, cd.month_label
“Query compiles and returns exactly the requested columns. Submitting.”
“Submitted. The report covers all 12 months of 2025 for both carriers at Studio Vrij, so Ben can see the May–July dip against the surrounding months and judge whether each carrier's performance (avg delivery days and % delivered within 3 days) returned to baseline afterward.”
As Ben Carter saw it, from the small dataset the agents worked on
carrier | month_label | shipment_count | avg_delivery_days | pct_delivered_within_3_days -----------------+-------------+----------------+-------------------+---------------------------- Lowlands Express | 2025-01 | 23 | 2.4 | 78.3 Lowlands Express | 2025-02 | 12 | 2.0 | 83.3 Lowlands Express | 2025-03 | 23 | 2.2 | 73.9 Lowlands Express | 2025-04 | 17 | 2.0 | 76.5 Lowlands Express | 2025-05 | 49 | 2.4 | 71.4 Lowlands Express | 2025-06 | 37 | 2.5 | 64.9 Lowlands Express | 2025-07 | 28 | 2.6 | 60.7 Lowlands Express | 2025-08 | 11 | 2.1 | 81.8 Lowlands Express | 2025-09 | 24 | 2.4 | 75.0 Lowlands Express | 2025-10 | 34 | 3.1 | 52.9 Lowlands Express | 2025-11 | 35 | 2.2 | 88.6 Lowlands Express | 2025-12 | 54 | 2.8 | 67.3 Polder Post | 2025-01 | 16 | 2.2 | 75.0 Polder Post | 2025-02 | 7 | 2.0 | 85.7 Polder Post | 2025-03 | 13 | 3.1 | 30.8 Polder Post | 2025-04 | 12 | 3.1 | 50.0 Polder Post | 2025-05 | 18 | 2.1 | 88.9 Polder Post | 2025-06 | 17 | 2.6 | 70.6 Polder Post | 2025-07 | 8 | 2.3 | 75.0 Polder Post | 2025-08 | 10 | 2.7 | 60.0 Polder Post | 2025-09 | 7 | 3.2 | 42.9 Polder Post | 2025-10 | 16 | 3.1 | 50.0 Polder Post | 2025-11 | 20 | 2.6 | 65.0 Polder Post | 2025-12 | 44 | 2.7 | 61.0 (24 rows)
The memo and the queries that followed; links found by title, so inferred
It builds on:
Changed only where the benchmark needs the same result on every system
SELECT s.carrier AS carrier, cd.month_label AS month_label, COUNT(*) AS shipment_count, ROUND(AVG(EXTRACT(EPOCH FROM (s.delivered_at - s.shipped_at)) / 86400.0) FILTER (WHERE s.delivered_at IS NOT NULL), 1) AS avg_delivery_days, ROUND(100.0 * COUNT(*) FILTER (WHERE s.delivered_at IS NOT NULL AND s.delivered_at - s.shipped_at <= INTERVAL '3 days') / NULLIF(COUNT(*) FILTER (WHERE s.delivered_at IS NOT NULL), 0), 1) AS pct_delivered_within_3_days AVG({{seconds_between(s.shipped_at, s.delivered_at)}} / 86400.0) FILTER (WHERE s.delivered_at IS NOT NULL) AS avg_delivery_days, 100.0 * COUNT(*) FILTER (WHERE s.delivered_at IS NOT NULL AND s.delivered_at - s.shipped_at <= INTERVAL '3 days') / NULLIF(COUNT(*) FILTER (WHERE s.delivered_at IS NOT NULL), 0) AS pct_delivered_within_3_daysFROM shipments sJOIN fulfillments f ON f.id = s.fulfillment_idJOIN orders o ON o.id = f.order_idJOIN tenants t ON t.id = o.tenant_idJOIN calendar_days cd ON cd.calendar_date = CAST(s.shipped_at AS date)WHERE t.name = 'Studio Vrij' AND s.carrier IN ('Lowlands Express', 'Polder Post') AND s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2026-01-01'GROUP BY s.carrier, cd.month_labelORDER BY s.carrier, cd.month_labelORDER BY s.carrier NULLS LAST, cd.month_label NULLS LAST
SELECT s.carrier AS carrier, cd.month_label AS month_label, COUNT(*) AS shipment_count, AVG(CAST(EXTRACT(EPOCH FROM (s.delivered_at - s.shipped_at)) AS NUMERIC(18,6)) / 86400.0) FILTER (WHERE s.delivered_at IS NOT NULL) AS avg_delivery_days, 100.0 * COUNT(*) FILTER (WHERE s.delivered_at IS NOT NULL AND s.delivered_at - s.shipped_at <= INTERVAL '3 days') / NULLIF(COUNT(*) FILTER (WHERE s.delivered_at IS NOT NULL), 0) AS pct_delivered_within_3_days FROM shipments s JOIN fulfillments f ON f.id = s.fulfillment_id JOIN orders o ON o.id = f.order_id JOIN tenants t ON t.id = o.tenant_id JOIN calendar_days cd ON cd.calendar_date = CAST(s.shipped_at AS date) WHERE t.name = 'Studio Vrij' AND s.carrier IN ('Lowlands Express', 'Polder Post') AND s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2026-01-01' GROUP BY s.carrier, cd.month_label ORDER BY s.carrier NULLS LAST, cd.month_label NULLS LAST
SELECT s.carrier AS carrier, cd.month_label AS month_label, COUNT(*) AS shipment_count, AVG(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', s.shipped_at, s.delivered_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 86400.0) FILTER (WHERE s.delivered_at IS NOT NULL) AS avg_delivery_days, 100.0 * COUNT(*) FILTER (WHERE s.delivered_at IS NOT NULL AND s.delivered_at - s.shipped_at <= INTERVAL '3 days') / NULLIF(COUNT(*) FILTER (WHERE s.delivered_at IS NOT NULL), 0) AS pct_delivered_within_3_days FROM shipments s JOIN fulfillments f ON f.id = s.fulfillment_id JOIN orders o ON o.id = f.order_id JOIN tenants t ON t.id = o.tenant_id JOIN calendar_days cd ON cd.calendar_date = CAST(s.shipped_at AS date) WHERE t.name = 'Studio Vrij' AND s.carrier IN ('Lowlands Express', 'Polder Post') AND s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2026-01-01' GROUP BY s.carrier, cd.month_label ORDER BY s.carrier NULLS LAST, cd.month_label NULLS LAST
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Ben Carter (analyst for the Support Operations Manager):
Title: Studio Vrij carrier performance by month, May-July focus
Question: Which of Studio Vrij's carriers caused the May-July 2025 delivery breakdown, and did its performance fully recover afterward?
Grain: one row per (carrier, month) in 2025, for Studio Vrij's two main carriers (Lowlands Express, Polder Post)
Columns:
- carrier
- month_label (YYYY-MM)
- shipment_count (shipped_at that month)
- avg_delivery_days
- pct_delivered_within_3_days
Details: Window: shipments.shipped_at between 2025-01-01 and 2025-12-31, restricted to tenant = Studio Vrij and carrier in ('Lowlands Express', 'Polder Post'). Source table: shipments. Compute avg_delivery_days and pct_delivered_within_3_days only over shipments with both shipped_at and delivered_at populated. Order by carrier, then month_label ascending. Round avg_delivery_days to 1 decimal, pct to 1 decimal percent.
Check your query with the warehouse's dry_run tool, then hand it in with submit.dry_runSELECT s.carrier AS carrier, cd.month_label AS month_label, COUNT(*) AS shipment_count, ROUND(AVG(EXTRACT(EPOCH FROM (s.delivered_at - s.shipped_at)) / 86400.0) FILTER (WHERE s.delivered_at IS NOT NULL), 1) AS avg_delivery_days, ROUND(100.0 * COUNT(*) FILTER (WHERE s.delivered_at IS NOT NULL AND s.delivered_at - s.shipped_at <= INTERVAL '3 days') / NULLIF(COUNT(*) FILTER (WHERE s.delivered_at IS NOT NULL), 0), 1) AS pct_delivered_within_3_days FROM shipments s JOIN fulfillments f ON f.id = s.fulfillment_id JOIN orders o ON o.id = f.order_id JOIN tenants t ON t.id = o.tenant_id JOIN calendar_days cd ON cd.calendar_date = CAST(s.shipped_at AS date) WHERE t.name = 'Studio Vrij' AND s.carrier IN ('Lowlands Express', 'Polder Post') AND s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2026-01-01' GROUP BY s.carrier, cd.month_label ORDER BY s.carrier, cd.month_label
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: carrier, month_label, shipment_count, avg_delivery_days, pct_delivered_within_3_days
submitSELECT s.carrier AS carrier, cd.month_label AS month_label, COUNT(*) AS shipment_count, ROUND(AVG(EXTRACT(EPOCH FROM (s.delivered_at - s.shipped_at)) / 86400.0) FILTER (WHERE s.delivered_at IS NOT NULL), 1) AS avg_delivery_days, ROUND(100.0 * COUNT(*) FILTER (WHERE s.delivered_at IS NOT NULL AND s.delivered_at - s.shipped_at <= INTERVAL '3 days') / NULLIF(COUNT(*) FILTER (WHERE s.delivered_at IS NOT NULL), 0), 1) AS pct_delivered_within_3_days FROM shipments s JOIN fulfillments f ON f.id = s.fulfillment_id JOIN orders o ON o.id = f.order_id JOIN tenants t ON t.id = o.tenant_id JOIN calendar_days cd ON cd.calendar_date = CAST(s.shipped_at AS date) WHERE t.name = 'Studio Vrij' AND s.carrier IN ('Lowlands Express', 'Polder Post') AND s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2026-01-01' GROUP BY s.carrier, cd.month_label ORDER BY s.carrier, cd.month_label
Submitted to tonight's batch. Your work on this request is done.