Queries · q215 · Chief Executive Officer · week 2, night 3
“Is Deskline Supplies' unusually high average shipping cost (~€1,038/shipment vs. ~€4-30/shipment for comparable merchants) a real freight-cost pattern or a data outlier?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
Week 2; shared with Tobias Lindqvist, Chief Financial Officer
The board meets at the end of January and wants a clear view of unit economics: per merchant, vertical and country, what Tidewick earns and what it costs us to serve them. Which merchants are worth growing, and which cost more than they bring?
The manager, at the start of week 2
Subject: Great first week — here's where I want to go next
Hannah,
This was exactly the kind of grounding I needed — thank you. A few things surprised me and one big new ask from the board that I need us to lead together with Tobias.
What surprised me / needs explaining:
The new board ask — unit economics by merchant, vertical and country:
The board wants to know, plainly, what we earn from serving each merchant/vertical/country and what it costs us — so we can say which parts of the business are worth leaning into and which are quietly expensive. This is bigger than one report, so here's what I think it needs, and I want you and Tobias to scope it jointly this week rather than me prescribing the method:
My questions for this week:
Let's talk briefly before you lock in this week's requests so we spend the slots on the unit economics scoping rather than re-running what we already mostly know.
Thanks, Maren
Note to Hannah Brooks
Hannah — a few things from the finance side as you scope the board unit-economics piece:
If there's room, a rough read on how refund patterns differ by vertical or country would help round out the "which merchants are worth growing" question.
Happy to talk through before Friday's memo.
— Tobias
The analyst · week 2, night 3, request 2 of 3
Big win on refunds: Q3 data confirms Kofi's example precisely — OfficeHub Nordic's refund rate jumped from a 2.99% baseline to 20.98% in Q3 (€23,268 refunded on just 45 orders), and it's already back to 0% by Q4, so it reads as a resolved one-off incident, not a trend. Combined with Q4's pattern (big Apparel merchants Cragsley/Marrowby actually improved), I'm confident the refund story is "a few concentrated incidents in small/mid merchants," not a broad trend — ready to write this up. On receivables, the aging report confirms ~€196.9k total outstanding, concentrated almost entirely in 7 B2B wholesalers, with Kontor24 Bürobedarf alone at €116.7k (59% of it, half of that in one single €49.4k invoice) — a much bigger story than Castell Fasteners alone. I also noticed something important for the "at risk" question: tenants.status itself (past_due/suspended/churned) looks like it reflects whether a merchant is behind on paying us (platform billing), distinct from the receivables report (which is their own customers owing them) — worth pulling directly as the cleanest churn signal. Separately, the cost-driver inventory showed Deskline Supplies at ~€1,038/shipment vs. Cragsley at ~€4/shipment — a 237x gap within similar/different verticals that's too extreme to use in a cost model without checking whether it's a real freight-cost pattern or a data outlier. Tonight: (1) the platform-billing-risk list, (2) a sanity check on the Deskline shipping-cost outlier, (3) a first directional vertical-level unit-economics rollup using only the inputs I already trust (fee revenue, shipping cost, refunds, tickets), explicitly caveated as a first pass.
The engineer · 1 dry run on the empty schema, then the submission
WITH t0 AS ( SELECT t1.id AS tenant_id FROM tenants t1 WHERE t1.name = 'Deskline Supplies' ), t2 AS ( SELECT s.carrier AS carrier, s.service_level AS service_level, s.shipping_cost AS shipping_cost, COALESCE(s.currency_code, tn.default_currency_code) AS ship_currency_code, s.shipped_at AS shipped_at FROM shipments s JOIN fulfillments f ON f.id = s.fulfillment_id JOIN orders o ON o.id = f.order_id JOIN t0 ON t0.tenant_id = s.tenant_id JOIN tenants tn ON tn.id = s.tenant_id WHERE s.shipped_at >= TIMESTAMP '2025-01-01' AND s.shipped_at < TIMESTAMP '2026-01-01' ), t3 AS ( SELECT t2.carrier AS carrier, t2.service_level AS service_level, t2.shipping_cost AS shipping_cost, CASE WHEN t2.ship_currency_code = 'EUR' THEN t2.shipping_cost ELSE t2.shipping_cost / er.rate END AS shipping_cost_eur FROM t2 LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = t2.ship_currency_code AND er.as_of = CAST(t2.shipped_at AS date) ) SELECT t3.carrier AS carrier, t3.service_level AS service_level, COUNT(*) AS shipment_count_2025, ROUND(SUM(t3.shipping_cost_eur), 2) AS total_shipping_cost_eur, ROUND(AVG(t3.shipping_cost_eur), 2) AS avg_shipping_cost_eur, ROUND(MIN(t3.shipping_cost_eur), 2) AS min_shipping_cost_eur, ROUND(MAX(t3.shipping_cost_eur), 2) AS max_shipping_cost_eur FROM t3 GROUP BY t3.carrier, t3.service_level ORDER BY total_shipping_cost_eur DESC, t3.carrier ASC, t3.service_level ASC
“Query compiles cleanly. Submitting.”
“Submitted. The report groups Deskline Supplies' 2025 shipments by carrier and service_level, converting shipping_cost to EUR via the daily exchange rate matching each shipment's own currency and ship date. Alongside shipment count and total/average cost, I included min and max per lane — that's the key diagnostic: if a lane's max is wildly above its min while the count is non-trivial, it points to a data entry error or currency mismatch on one or two rows rather than a genuine bulk-freight cost structure.”
As Hannah Brooks saw it, from the small dataset the agents worked on
carrier | service_level | shipment_count_2025 | total_shipping_cost_eur | avg_shipping_cost_eur | min_shipping_cost_eur | max_shipping_cost_eur -----------------+---------------+---------------------+-------------------------+-----------------------+-----------------------+---------------------- Albion Parcel | standard | 25 | 21754.7 | 870.19 | 5.33 | 7655.94 Kennet Freight | express | 4 | 17701.97 | 4425.49 | 144.97 | 9509.06 Kennet Freight | standard | 12 | 17040.84 | 1420.07 | 5.0 | 6680.11 Landfall Freight | standard | 12 | 9635.74 | 802.98 | 26.86 | 5052.49 Lowlands Express | standard | 8 | 8862.08 | 1107.76 | 21.25 | 4104.32 Regent Parcels | standard | 7 | 1704.74 | 243.53 | 10.51 | 1077.12 Albion Parcel | economy | 4 | 971.58 | 242.89 | 9.88 | 921.81 Landfall Freight | express | 2 | 147.61 | 73.81 | 34.26 | 113.36 Albion Parcel | express | 1 | 56.91 | 56.91 | 56.91 | 56.91 (9 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
WITH t0 AS ( SELECT t1.id AS tenant_id FROM tenants t1 WHERE t1.name = 'Deskline Supplies'),t2 AS ( SELECT s.carrier AS carrier, s.service_level AS service_level, s.shipping_cost AS shipping_cost, COALESCE(s.currency_code, tn.default_currency_code) AS ship_currency_code, s.shipped_at AS shipped_at FROM shipments s JOIN fulfillments f ON f.id = s.fulfillment_id JOIN orders o ON o.id = f.order_id JOIN t0 ON t0.tenant_id = s.tenant_id JOIN tenants tn ON tn.id = s.tenant_id WHERE s.shipped_at >= TIMESTAMP '2025-01-01' AND s.shipped_at < TIMESTAMP '2026-01-01'),t3 AS ( SELECT t2.carrier AS carrier, t2.service_level AS service_level, t2.shipping_cost AS shipping_cost, CASE WHEN t2.ship_currency_code = 'EUR' THEN t2.shipping_cost ELSE t2.shipping_cost / er.rate ELSE CAST(t2.shipping_cost / er.rate AS NUMERIC(18,6)) END AS shipping_cost_eur FROM t2 LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = t2.ship_currency_code AND er.as_of = CAST(t2.shipped_at AS date))SELECT t3.carrier AS carrier, t3.service_level AS service_level, COUNT(*) AS shipment_count_2025, ROUND(SUM(t3.shipping_cost_eur), 2) AS total_shipping_cost_eur, ROUND(AVG(t3.shipping_cost_eur), 2) AS avg_shipping_cost_eur, ROUND(MIN(t3.shipping_cost_eur), 2) AS min_shipping_cost_eur, ROUND(MAX(t3.shipping_cost_eur), 2) AS max_shipping_cost_eur SUM(t3.shipping_cost_eur) AS total_shipping_cost_eur, AVG(t3.shipping_cost_eur) AS avg_shipping_cost_eur, MIN(t3.shipping_cost_eur) AS min_shipping_cost_eur, MAX(t3.shipping_cost_eur) AS max_shipping_cost_eurFROM t3GROUP BY t3.carrier, t3.service_levelORDER BY total_shipping_cost_eur DESC, t3.carrier ASC, t3.service_level ASCORDER BY total_shipping_cost_eur DESC NULLS FIRST, t3.carrier ASC NULLS LAST, t3.service_level ASC NULLS LAST
WITH t0 AS ( SELECT t1.id AS tenant_id FROM tenants t1 WHERE t1.name = 'Deskline Supplies' ), t2 AS ( SELECT s.carrier AS carrier, s.service_level AS service_level, s.shipping_cost AS shipping_cost, COALESCE(s.currency_code, tn.default_currency_code) AS ship_currency_code, s.shipped_at AS shipped_at FROM shipments s JOIN fulfillments f ON f.id = s.fulfillment_id JOIN orders o ON o.id = f.order_id JOIN t0 ON t0.tenant_id = s.tenant_id JOIN tenants tn ON tn.id = s.tenant_id WHERE s.shipped_at >= TIMESTAMP '2025-01-01' AND s.shipped_at < TIMESTAMP '2026-01-01' ), t3 AS ( SELECT t2.carrier AS carrier, t2.service_level AS service_level, t2.shipping_cost AS shipping_cost, CASE WHEN t2.ship_currency_code = 'EUR' THEN t2.shipping_cost ELSE CAST(t2.shipping_cost / er.rate AS NUMERIC(18,6)) END AS shipping_cost_eur FROM t2 LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = t2.ship_currency_code AND er.as_of = CAST(t2.shipped_at AS date) ) SELECT t3.carrier AS carrier, t3.service_level AS service_level, COUNT(*) AS shipment_count_2025, SUM(t3.shipping_cost_eur) AS total_shipping_cost_eur, AVG(t3.shipping_cost_eur) AS avg_shipping_cost_eur, MIN(t3.shipping_cost_eur) AS min_shipping_cost_eur, MAX(t3.shipping_cost_eur) AS max_shipping_cost_eur FROM t3 GROUP BY t3.carrier, t3.service_level ORDER BY total_shipping_cost_eur DESC NULLS FIRST, t3.carrier ASC NULLS LAST, t3.service_level ASC 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 Hannah Brooks (analyst for the Chief Executive Officer): Title: Deskline Supplies shipping cost breakdown by carrier Question: Is Deskline Supplies' unusually high average shipping cost (~€1,038/shipment vs. ~€4-30/shipment for comparable merchants) a real freight-cost pattern or a data outlier? Grain: one row per carrier and service_level, for Deskline Supplies shipments only Columns: - carrier - service_level - shipment count (2025) - total shipping cost (EUR) - average shipping cost per shipment (EUR) - minimum shipping cost (EUR) - maximum shipping cost (EUR) Details: Source: shipments joined to fulfillments->orders to filter to Deskline Supplies' tenant_id only, shipped_at in [2025-01-01,2026-01-01), shipping_cost converted to EUR via exchange_rates as_of shipped_at date. Group by carrier and service_level. I want to see whether the high average is spread evenly (suggesting a genuine bulk-freight cost structure for this B2B merchant) or driven by one or two extreme max values in an otherwise normal-looking carrier lane (suggesting a data entry error or currency mismatch). Order by total shipping cost EUR descending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH t0 AS ( SELECT t1.id AS tenant_id FROM tenants t1 WHERE t1.name = 'Deskline Supplies' ), t2 AS ( SELECT s.carrier AS carrier, s.service_level AS service_level, s.shipping_cost AS shipping_cost, COALESCE(s.currency_code, tn.default_currency_code) AS ship_currency_code, s.shipped_at AS shipped_at FROM shipments s JOIN fulfillments f ON f.id = s.fulfillment_id JOIN orders o ON o.id = f.order_id JOIN t0 ON t0.tenant_id = s.tenant_id JOIN tenants tn ON tn.id = s.tenant_id WHERE s.shipped_at >= TIMESTAMP '2025-01-01' AND s.shipped_at < TIMESTAMP '2026-01-01' ), t3 AS ( SELECT t2.carrier AS carrier, t2.service_level AS service_level, t2.shipping_cost AS shipping_cost, CASE WHEN t2.ship_currency_code = 'EUR' THEN t2.shipping_cost ELSE t2.shipping_cost / er.rate END AS shipping_cost_eur FROM t2 LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = t2.ship_currency_code AND er.as_of = CAST(t2.shipped_at AS date) ) SELECT t3.carrier AS carrier, t3.service_level AS service_level, COUNT(*) AS shipment_count_2025, ROUND(SUM(t3.shipping_cost_eur), 2) AS total_shipping_cost_eur, ROUND(AVG(t3.shipping_cost_eur), 2) AS avg_shipping_cost_eur, ROUND(MIN(t3.shipping_cost_eur), 2) AS min_shipping_cost_eur, ROUND(MAX(t3.shipping_cost_eur), 2) AS max_shipping_cost_eur FROM t3 GROUP BY t3.carrier, t3.service_level ORDER BY total_shipping_cost_eur DESC, t3.carrier ASC, t3.service_level ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: carrier, service_level, shipment_count_2025, total_shipping_cost_eur, avg_shipping_cost_eur, min_shipping_cost_eur, max_shipping_cost_eur
submitWITH t0 AS ( SELECT t1.id AS tenant_id FROM tenants t1 WHERE t1.name = 'Deskline Supplies' ), t2 AS ( SELECT s.carrier AS carrier, s.service_level AS service_level, s.shipping_cost AS shipping_cost, COALESCE(s.currency_code, tn.default_currency_code) AS ship_currency_code, s.shipped_at AS shipped_at FROM shipments s JOIN fulfillments f ON f.id = s.fulfillment_id JOIN orders o ON o.id = f.order_id JOIN t0 ON t0.tenant_id = s.tenant_id JOIN tenants tn ON tn.id = s.tenant_id WHERE s.shipped_at >= TIMESTAMP '2025-01-01' AND s.shipped_at < TIMESTAMP '2026-01-01' ), t3 AS ( SELECT t2.carrier AS carrier, t2.service_level AS service_level, t2.shipping_cost AS shipping_cost, CASE WHEN t2.ship_currency_code = 'EUR' THEN t2.shipping_cost ELSE t2.shipping_cost / er.rate END AS shipping_cost_eur FROM t2 LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = t2.ship_currency_code AND er.as_of = CAST(t2.shipped_at AS date) ) SELECT t3.carrier AS carrier, t3.service_level AS service_level, COUNT(*) AS shipment_count_2025, ROUND(SUM(t3.shipping_cost_eur), 2) AS total_shipping_cost_eur, ROUND(AVG(t3.shipping_cost_eur), 2) AS avg_shipping_cost_eur, ROUND(MIN(t3.shipping_cost_eur), 2) AS min_shipping_cost_eur, ROUND(MAX(t3.shipping_cost_eur), 2) AS max_shipping_cost_eur FROM t3 GROUP BY t3.carrier, t3.service_level ORDER BY total_shipping_cost_eur DESC, t3.carrier ASC, t3.service_level ASC
Submitted to tonight's batch. Your work on this request is done.