Queries · q219 · Chief Executive Officer · week 2, night 4
“Is shipping actually a net cost exposure, or does what we collect from customers for shipping roughly cover what we pay carriers?”
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 4, request 3 of 3
Big catch from last night: the "directional unit economics" report is mathematically misleading, not just rough — every vertical shows net contribution of -300% to -1450% of fee revenue, because it nets full order-value refunds and full shipping outlays against our thin processing-fee take (e.g. Apparel refunds of €281k vs. €33k fee revenue). Refunds and shipping are gross merchandise/logistics flows, not Tidewick's P&L costs on a 1:1 basis with our fee revenue — comparing them directly produces a result that would badly mislead the board if presented as-is. The Deskline Supplies shipping-cost check came back ambiguous rather than conclusive: costs are spread across many carrier/service lanes (not one bad row), but with extreme within-lane variance (€5 to €9,509 in the same lane) that's plausible for a B2B freight mix but still needs an Ops/Finance sanity check before I'd trust it in a model. On the platform-billing-risk side: only 4 merchants are past_due/suspended/churned, all small (<€1.3k TTM fee revenue each) — not a board-level concern on its own. Tonight I'm using all 3 slots to put the unit-economics scoping on solid footing before month-end: (1) redo the vertical view as comparable rates against gross volume rather than raw euros against fee revenue, (2) the same clean rate view at merchant grain for the top accounts (since the board wants merchant-level too), and (3) a direct check on whether shipping cost is actually a net cost or a wash once customer-paid shipping is netted against carrier cost — this resolves a real "whose cost is this" ambiguity I flagged rather than assuming an answer. I'm not spending a further slot chasing Finance's €93,583 discrepancy blindly; that needs a joint query walkthrough with Tobias's team, which I'll propose directly rather than guess at in another report.
The engineer · 1 dry run on the empty schema, then the submission
WITH order_rev AS ( SELECT COALESCE(NULLIF(t.industry, ''), 'Unspecified') AS vertical, COUNT(*) AS order_count, SUM(o.shipping_total / er.rate) AS shipping_revenue_eur FROM orders o JOIN tenants t ON t.id = o.tenant_id JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS date) WHERE o.status IN ('paid', 'partially_fulfilled', 'fulfilled', 'refunded') AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' GROUP BY 1 ), ship_cost AS ( SELECT COALESCE(NULLIF(t.industry, ''), 'Unspecified') AS vertical, SUM(s.shipping_cost / er.rate) AS shipping_cost_eur 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 exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = s.currency_code AND er.as_of = CAST(s.shipped_at AS date) WHERE s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2026-01-01' GROUP BY 1 ) SELECT COALESCE(r.vertical, c.vertical) AS vertical, COALESCE(r.order_count, 0) AS order_count, ROUND(COALESCE(r.shipping_revenue_eur, 0), 2) AS shipping_revenue_eur, ROUND(COALESCE(c.shipping_cost_eur, 0), 2) AS shipping_cost_eur, ROUND(COALESCE(r.shipping_revenue_eur, 0) - COALESCE(c.shipping_cost_eur, 0), 2) AS shipping_margin_eur, ROUND( CASE WHEN COALESCE(r.shipping_revenue_eur, 0) <> 0 THEN (COALESCE(r.shipping_revenue_eur, 0) - COALESCE(c.shipping_cost_eur, 0)) / r.shipping_revenue_eur * 100 ELSE NULL END, 1 ) AS shipping_margin_pct FROM order_rev r FULL OUTER JOIN ship_cost c ON r.vertical = c.vertical ORDER BY shipping_margin_eur ASC, vertical ASC
“Query compiles correctly with the expected columns. Submitting now.”
“Submitted. Summary of what the report delivers:
- Grain: one row per merchant vertical (tenants.industry, nulls/blanks bucketed as "Unspecified")
- Revenue side: 2025 orders in status paid/partially_fulfilled/fulfilled/refunded, shipping_total converted to EUR using the exchange rate as of placed_at's date
- Cost side: shipments with shipped_at in 2025, joined through fulfillments→orders→tenants, shipping_cost converted to EUR as of shipped_at's date
- Output columns: vertical, order count, shipping revenue (EUR), shipping cost (EUR), shipping margin (EUR), shipping margin % of revenue
- Ordered worst-recovery-first (most negative margin at top), tie-broken alphabetically by vertical
One caveat noted to the analyst: since revenue and cost are aggregated on separate fact tables at different grains (orders vs. shipments) before being joined on vertical, order count reflects only the orders-side filter, and any row missing a matching exchange rate for its currency/date would drop out of that side's sum.”
As Hannah Brooks saw it, from the small dataset the agents worked on
vertical | order_count | shipping_revenue_eur | shipping_cost_eur | shipping_margin_eur | shipping_margin_pct --------------------------+-------------+----------------------+-------------------+---------------------+-------------------- Office Supplies (B2B) | 242 | 567.09 | 82825.01 | -82257.92 | -14505.4 Apparel & Footwear | 5688 | 11426.33 | 28460.58 | -17034.24 | -149.1 Food & Coffee | 1757 | 3141.15 | 10148.9 | -7007.75 | -223.1 Industrial Supplies (B2B) | 156 | 85.53 | 5946.97 | -5861.45 | -6853.2 Pet Supplies | 1087 | 2756.37 | 7488.79 | -4732.42 | -171.7 Home & Living | 811 | 2541.68 | 4882.02 | -2340.34 | -92.1 Beauty | 1215 | 3604.29 | 5840.19 | -2235.9 | -62.0 Sports & Outdoor | 529 | 901.75 | 2703.33 | -1801.58 | -199.8 Consumer Electronics | 45 | 151.41 | 359.06 | -207.65 | -137.1 Jewelry & Accessories | 13 | 14.13 | 47.27 | -33.14 | -234.5 Books & Stationery | 114 | 374.1 | 335.04 | 39.06 | 10.4 (11 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 order_rev AS ( SELECT COALESCE(NULLIF(t.industry, ''), 'Unspecified') AS vertical, COUNT(*) AS order_count, SUM(o.shipping_total / er.rate) AS shipping_revenue_eur SUM(CAST(o.shipping_total / er.rate AS NUMERIC(18,6))) AS shipping_revenue_eur FROM orders o JOIN tenants t ON t.id = o.tenant_id JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS date) WHERE o.status IN ('paid', 'partially_fulfilled', 'fulfilled', 'refunded') AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' GROUP BY 1),ship_cost AS ( SELECT COALESCE(NULLIF(t.industry, ''), 'Unspecified') AS vertical, SUM(s.shipping_cost / er.rate) AS shipping_cost_eur SUM(CAST(s.shipping_cost / er.rate AS NUMERIC(18,6))) AS shipping_cost_eur 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 exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = s.currency_code AND er.as_of = CAST(s.shipped_at AS date) WHERE s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2026-01-01' GROUP BY 1)SELECT COALESCE(r.vertical, c.vertical) AS vertical, COALESCE(r.order_count, 0) AS order_count, ROUND(COALESCE(r.shipping_revenue_eur, 0), 2) AS shipping_revenue_eur, ROUND(COALESCE(c.shipping_cost_eur, 0), 2) AS shipping_cost_eur, ROUND(COALESCE(r.shipping_revenue_eur, 0) - COALESCE(c.shipping_cost_eur, 0), 2) AS shipping_margin_eur, ROUND( CASE WHEN COALESCE(r.shipping_revenue_eur, 0) <> 0 THEN (COALESCE(r.shipping_revenue_eur, 0) - COALESCE(c.shipping_cost_eur, 0)) / r.shipping_revenue_eur * 100 ELSE NULL END, 1 ) AS shipping_margin_pct COALESCE(r.shipping_revenue_eur, 0) AS shipping_revenue_eur, COALESCE(c.shipping_cost_eur, 0) AS shipping_cost_eur, COALESCE(r.shipping_revenue_eur, 0) - COALESCE(c.shipping_cost_eur, 0) AS shipping_margin_eur, CASE WHEN COALESCE(r.shipping_revenue_eur, 0) <> 0 THEN (COALESCE(r.shipping_revenue_eur, 0) - COALESCE(c.shipping_cost_eur, 0)) / r.shipping_revenue_eur * 100 ELSE NULL END AS shipping_margin_pctFROM order_rev rFULL OUTER JOIN ship_cost c ON r.vertical = c.verticalORDER BY shipping_margin_eur ASC, vertical ASCORDER BY shipping_margin_eur ASC NULLS LAST, vertical ASC NULLS LAST
WITH order_rev AS ( SELECT COALESCE(NULLIF(t.industry, ''), 'Unspecified') AS vertical, COUNT(*) AS order_count, SUM(CAST(o.shipping_total / er.rate AS NUMERIC(18,6))) AS shipping_revenue_eur FROM orders o JOIN tenants t ON t.id = o.tenant_id JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS date) WHERE o.status IN ('paid', 'partially_fulfilled', 'fulfilled', 'refunded') AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' GROUP BY 1 ), ship_cost AS ( SELECT COALESCE(NULLIF(t.industry, ''), 'Unspecified') AS vertical, SUM(CAST(s.shipping_cost / er.rate AS NUMERIC(18,6))) AS shipping_cost_eur 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 exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = s.currency_code AND er.as_of = CAST(s.shipped_at AS date) WHERE s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2026-01-01' GROUP BY 1 ) SELECT COALESCE(r.vertical, c.vertical) AS vertical, COALESCE(r.order_count, 0) AS order_count, COALESCE(r.shipping_revenue_eur, 0) AS shipping_revenue_eur, COALESCE(c.shipping_cost_eur, 0) AS shipping_cost_eur, COALESCE(r.shipping_revenue_eur, 0) - COALESCE(c.shipping_cost_eur, 0) AS shipping_margin_eur, CASE WHEN COALESCE(r.shipping_revenue_eur, 0) <> 0 THEN (COALESCE(r.shipping_revenue_eur, 0) - COALESCE(c.shipping_cost_eur, 0)) / r.shipping_revenue_eur * 100 ELSE NULL END AS shipping_margin_pct FROM order_rev r FULL OUTER JOIN ship_cost c ON r.vertical = c.vertical ORDER BY shipping_margin_eur ASC NULLS LAST, vertical 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: Shipping cost recovery: customer-paid shipping vs. carrier cost, by vertical, 2025 Question: Is shipping actually a net cost exposure, or does what we collect from customers for shipping roughly cover what we pay carriers? Grain: one row per merchant vertical (tenants.industry) Columns: - vertical - order count (2025) - shipping revenue collected from customers (EUR, sum of orders.shipping_total) - shipping cost paid to carriers (EUR, sum of shipments.shipping_cost) - shipping margin (EUR, revenue minus cost) - shipping margin as % of shipping revenue collected Details: Source: orders (status in 'paid','partially_fulfilled','fulfilled','refunded', placed_at in [2025-01-01,2026-01-01), shipping_total converted to EUR via exchange_rates as_of placed_at date) joined to tenants for industry, for the revenue side; shipments (shipped_at in the same 2025 window, shipping_cost converted to EUR as_of shipped_at date) joined via fulfillments->orders->tenant_id, for the cost side. Both sums rolled up to vertical. This is meant to settle whether 'shipping cost' from the cost-driver inventory represents a real net cost exposure (if margin is sharply negative) or is largely self-funded by what customers already pay for shipping (if margin is near zero or positive) — directly feeding how we frame shipping in the unit-economics model. Group null/blank industry into 'Unspecified'. Order by shipping margin EUR ascending (worst recovery first), tie-break by vertical name ascending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH order_rev AS ( SELECT COALESCE(NULLIF(t.industry, ''), 'Unspecified') AS vertical, COUNT(*) AS order_count, SUM(o.shipping_total / er.rate) AS shipping_revenue_eur FROM orders o JOIN tenants t ON t.id = o.tenant_id JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS date) WHERE o.status IN ('paid', 'partially_fulfilled', 'fulfilled', 'refunded') AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' GROUP BY 1 ), ship_cost AS ( SELECT COALESCE(NULLIF(t.industry, ''), 'Unspecified') AS vertical, SUM(s.shipping_cost / er.rate) AS shipping_cost_eur 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 exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = s.currency_code AND er.as_of = CAST(s.shipped_at AS date) WHERE s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2026-01-01' GROUP BY 1 ) SELECT COALESCE(r.vertical, c.vertical) AS vertical, COALESCE(r.order_count, 0) AS order_count, ROUND(COALESCE(r.shipping_revenue_eur, 0), 2) AS shipping_revenue_eur, ROUND(COALESCE(c.shipping_cost_eur, 0), 2) AS shipping_cost_eur, ROUND(COALESCE(r.shipping_revenue_eur, 0) - COALESCE(c.shipping_cost_eur, 0), 2) AS shipping_margin_eur, ROUND( CASE WHEN COALESCE(r.shipping_revenue_eur, 0) <> 0 THEN (COALESCE(r.shipping_revenue_eur, 0) - COALESCE(c.shipping_cost_eur, 0)) / r.shipping_revenue_eur * 100 ELSE NULL END, 1 ) AS shipping_margin_pct FROM order_rev r FULL OUTER JOIN ship_cost c ON r.vertical = c.vertical ORDER BY shipping_margin_eur ASC, vertical ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: vertical, order_count, shipping_revenue_eur, shipping_cost_eur, shipping_margin_eur, shipping_margin_pct
submitWITH order_rev AS ( SELECT COALESCE(NULLIF(t.industry, ''), 'Unspecified') AS vertical, COUNT(*) AS order_count, SUM(o.shipping_total / er.rate) AS shipping_revenue_eur FROM orders o JOIN tenants t ON t.id = o.tenant_id JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS date) WHERE o.status IN ('paid', 'partially_fulfilled', 'fulfilled', 'refunded') AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' GROUP BY 1 ), ship_cost AS ( SELECT COALESCE(NULLIF(t.industry, ''), 'Unspecified') AS vertical, SUM(s.shipping_cost / er.rate) AS shipping_cost_eur 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 exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = s.currency_code AND er.as_of = CAST(s.shipped_at AS date) WHERE s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2026-01-01' GROUP BY 1 ) SELECT COALESCE(r.vertical, c.vertical) AS vertical, COALESCE(r.order_count, 0) AS order_count, ROUND(COALESCE(r.shipping_revenue_eur, 0), 2) AS shipping_revenue_eur, ROUND(COALESCE(c.shipping_cost_eur, 0), 2) AS shipping_cost_eur, ROUND(COALESCE(r.shipping_revenue_eur, 0) - COALESCE(c.shipping_cost_eur, 0), 2) AS shipping_margin_eur, ROUND( CASE WHEN COALESCE(r.shipping_revenue_eur, 0) <> 0 THEN (COALESCE(r.shipping_revenue_eur, 0) - COALESCE(c.shipping_cost_eur, 0)) / r.shipping_revenue_eur * 100 ELSE NULL END, 1 ) AS shipping_margin_pct FROM order_rev r FULL OUTER JOIN ship_cost c ON r.vertical = c.vertical ORDER BY shipping_margin_eur ASC, vertical ASC
Submitted to tonight's batch. Your work on this request is done.