Queries · q214 · Chief Executive Officer · week 2, night 3
“Which merchants are currently behind on paying us or already cut off, and how big/active are they — our clearest churn-risk signal?”
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 1 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 tenant_base AS ( SELECT t.id AS tenant_id, t.name AS merchant_name, t.industry AS vertical, t.status AS tenant_status, t.created_at FROM tenants t WHERE t.status IN ('past_due','suspended','churned') AND t.deleted_at IS NULL ), last_order AS ( SELECT tenant_id, MAX(placed_at) AS last_order_at FROM orders GROUP BY tenant_id ), last_paid_payout AS ( SELECT tenant_id, MAX(paid_at) AS last_paid_payout_at FROM payouts WHERE status = 'paid' GROUP BY tenant_id ), fee_revenue_ttm AS ( SELECT p.tenant_id, SUM(CASE WHEN p.currency_code = 'EUR' THEN p.fee_amount ELSE p.fee_amount / er.rate END) AS fee_revenue_eur FROM payouts p LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = CAST(p.paid_at AS date) WHERE p.status = 'paid' AND p.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' GROUP BY p.tenant_id ) SELECT tb.merchant_name, tb.vertical, tb.tenant_status, (DATE '2026-01-05' - CAST(tb.created_at AS date)) AS tenure_days, (DATE '2026-01-05' - CAST(lo.last_order_at AS date)) AS days_since_last_order, (DATE '2026-01-05' - CAST(lp.last_paid_payout_at AS date)) AS days_since_last_paid_payout, ROUND(COALESCE(fr.fee_revenue_eur, 0), 2) AS fee_revenue_ttm_eur FROM tenant_base tb LEFT JOIN last_order lo ON lo.tenant_id = tb.tenant_id LEFT JOIN last_paid_payout lp ON lp.tenant_id = tb.tenant_id LEFT JOIN fee_revenue_ttm fr ON fr.tenant_id = tb.tenant_id ORDER BY CASE tb.tenant_status WHEN 'churned' THEN 1 WHEN 'suspended' THEN 2 WHEN 'past_due' THEN 3 END, fee_revenue_ttm_eur DESC, tb.merchant_name ASC
“Query compiles cleanly with the expected columns.”
“Submitted. Summary of what the report does:
- Grain: one row per merchant (tenant) currently in past_due, suspended, or churned status (excluding soft-deleted tenants).
- Columns: merchant name, vertical, tenant status, tenure in days, days since last order placed, days since last settled (paid) payout, and trailing-12-month fee revenue in EUR.
- Logic: last order and last paid payout are pulled via per-tenant MAX() CTEs; fee revenue sums payouts.fee_amount for status='paid' within 2025-01-01–2025-12-31, converting non-EUR currencies using the EUR→currency rate on the payout's paid_at date.
- Ordering: churned → suspended → past_due, then by fee revenue descending, with merchant name as a final deterministic tie-break.
I noted in the submission that the EUR conversion assumes exchange_rates gives an EUR→currency rate (dividing to get EUR), treats EUR payouts as already-EUR, and that recency columns are null for merchants with no qualifying orders/payouts.”
As Hannah Brooks saw it, from the small dataset the agents worked on
merchant_name | vertical | tenant_status | tenure_days | days_since_last_order | days_since_last_paid_payout | fee_revenue_ttm_eur -----------------+-----------------------+---------------+-------------+-----------------------+-----------------------------+-------------------- Silverthread | Jewelry & Accessories | suspended | 760 | 16 | 14 | 94.56 Grind Collective | Food & Coffee | past_due | 2744 | 5 | 5 | 1265.15 Meadow Row | Apparel & Footwear | past_due | 720 | 22 | 20 | 295.05 Saffron & Sage | Beauty | past_due | 1178 | 7 | 5 | 273.59 (4 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 tenant_base AS ( SELECT t.id AS tenant_id, t.name AS merchant_name, t.industry AS vertical, t.status AS tenant_status, t.created_at SELECT t.id AS tenant_id, t.name AS merchant_name, t.industry AS vertical, t.status AS tenant_status, t.created_at AS created_at FROM tenants t WHERE t.status IN ('past_due','suspended','churned') AND t.deleted_at IS NULL),last_order AS ( SELECT tenant_id, MAX(placed_at) AS last_order_at FROM orders GROUP BY tenant_id),last_paid_payout AS ( SELECT tenant_id, MAX(paid_at) AS last_paid_payout_at FROM payouts WHERE status = 'paid' GROUP BY tenant_id),fee_revenue_ttm AS ( SELECT p.tenant_id, SUM(CASE WHEN p.currency_code = 'EUR' THEN p.fee_amount ELSE p.fee_amount / er.rate END) AS fee_revenue_eur SELECT p.tenant_id AS tenant_id, SUM(CASE WHEN p.currency_code = 'EUR' THEN p.fee_amount ELSE CAST(p.fee_amount / er.rate AS NUMERIC(18,6)) END) AS fee_revenue_eur FROM payouts p LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = CAST(p.paid_at AS date) WHERE p.status = 'paid' AND p.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' GROUP BY p.tenant_id)SELECT tb.merchant_name, tb.vertical, tb.tenant_status, tb.merchant_name AS merchant_name, tb.vertical AS vertical, tb.tenant_status AS tenant_status, (DATE '2026-01-05' - CAST(tb.created_at AS date)) AS tenure_days, (DATE '2026-01-05' - CAST(lo.last_order_at AS date)) AS days_since_last_order, (DATE '2026-01-05' - CAST(lp.last_paid_payout_at AS date)) AS days_since_last_paid_payout, ROUND(COALESCE(fr.fee_revenue_eur, 0), 2) AS fee_revenue_ttm_eur COALESCE(fr.fee_revenue_eur, 0) AS fee_revenue_ttm_eurFROM tenant_base tbLEFT JOIN last_order lo ON lo.tenant_id = tb.tenant_idLEFT JOIN last_paid_payout lp ON lp.tenant_id = tb.tenant_idLEFT JOIN fee_revenue_ttm fr ON fr.tenant_id = tb.tenant_idORDER BY CASE tb.tenant_status WHEN 'churned' THEN 1 WHEN 'suspended' THEN 2 WHEN 'past_due' THEN 3 END, fee_revenue_ttm_eur DESC, tb.merchant_name ASC CASE tb.tenant_status WHEN 'churned' THEN 1 WHEN 'suspended' THEN 2 WHEN 'past_due' THEN 3 END NULLS LAST, fee_revenue_ttm_eur DESC NULLS FIRST, tb.merchant_name ASC NULLS LAST, tb.tenant_id ASC NULLS LAST
WITH tenant_base AS ( SELECT t.id AS tenant_id, t.name AS merchant_name, t.industry AS vertical, t.status AS tenant_status, t.created_at AS created_at FROM tenants t WHERE t.status IN ('past_due','suspended','churned') AND t.deleted_at IS NULL ), last_order AS ( SELECT tenant_id, MAX(placed_at) AS last_order_at FROM orders GROUP BY tenant_id ), last_paid_payout AS ( SELECT tenant_id, MAX(paid_at) AS last_paid_payout_at FROM payouts WHERE status = 'paid' GROUP BY tenant_id ), fee_revenue_ttm AS ( SELECT p.tenant_id AS tenant_id, SUM(CASE WHEN p.currency_code = 'EUR' THEN p.fee_amount ELSE CAST(p.fee_amount / er.rate AS NUMERIC(18,6)) END) AS fee_revenue_eur FROM payouts p LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = CAST(p.paid_at AS date) WHERE p.status = 'paid' AND p.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' GROUP BY p.tenant_id ) SELECT tb.merchant_name AS merchant_name, tb.vertical AS vertical, tb.tenant_status AS tenant_status, (DATE '2026-01-05' - CAST(tb.created_at AS date)) AS tenure_days, (DATE '2026-01-05' - CAST(lo.last_order_at AS date)) AS days_since_last_order, (DATE '2026-01-05' - CAST(lp.last_paid_payout_at AS date)) AS days_since_last_paid_payout, COALESCE(fr.fee_revenue_eur, 0) AS fee_revenue_ttm_eur FROM tenant_base tb LEFT JOIN last_order lo ON lo.tenant_id = tb.tenant_id LEFT JOIN last_paid_payout lp ON lp.tenant_id = tb.tenant_id LEFT JOIN fee_revenue_ttm fr ON fr.tenant_id = tb.tenant_id ORDER BY CASE tb.tenant_status WHEN 'churned' THEN 1 WHEN 'suspended' THEN 2 WHEN 'past_due' THEN 3 END NULLS LAST, fee_revenue_ttm_eur DESC NULLS FIRST, tb.merchant_name ASC NULLS LAST, tb.tenant_id 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: Platform billing risk: merchants past_due, suspended or churned
Question: Which merchants are currently behind on paying us or already cut off, and how big/active are they — our clearest churn-risk signal?
Grain: one row per merchant (tenant), filtered to status in ('past_due','suspended','churned')
Columns:
- merchant name
- vertical
- tenant status
- tenure (days since created_at, as of 2026-01-05)
- days since last order placed
- days since last settled ('paid') payout
- fee revenue trailing 12 months (EUR)
Details: Source: tenants filtered to status in ('past_due','suspended','churned') and deleted_at is null, left joined to orders (max placed_at per tenant_id, any status) and payouts (max paid_at per tenant_id where status='paid') for recency, and to payouts again for fee revenue (status='paid', paid_at in [2025-01-01,2026-01-01), converted to EUR via exchange_rates as_of paid_at date, matching the validated methodology). This is meant to be read as the platform-billing-health complement to last night's customer-receivables report (that one was about merchants' own customers owing them money; this one is about whether the merchant itself is behind with Tidewick). Order by tenant status (churned first, then suspended, then past_due), tie-break by fee revenue descending. Note total row count.
Check your query with the warehouse's dry_run tool, then hand it in with submit.dry_runWITH tenant_base AS ( SELECT t.id AS tenant_id, t.name AS merchant_name, t.industry AS vertical, t.status AS tenant_status, t.created_at FROM tenants t WHERE t.status IN ('past_due','suspended','churned') AND t.deleted_at IS NULL ), last_order AS ( SELECT tenant_id, MAX(placed_at) AS last_order_at FROM orders GROUP BY tenant_id ), last_paid_payout AS ( SELECT tenant_id, MAX(paid_at) AS last_paid_payout_at FROM payouts WHERE status = 'paid' GROUP BY tenant_id ), fee_revenue_ttm AS ( SELECT p.tenant_id, SUM(CASE WHEN p.currency_code = 'EUR' THEN p.fee_amount ELSE p.fee_amount / er.rate END) AS fee_revenue_eur FROM payouts p LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = CAST(p.paid_at AS date) WHERE p.status = 'paid' AND p.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' GROUP BY p.tenant_id ) SELECT tb.merchant_name, tb.vertical, tb.tenant_status, (DATE '2026-01-05' - CAST(tb.created_at AS date)) AS tenure_days, (DATE '2026-01-05' - CAST(lo.last_order_at AS date)) AS days_since_last_order, (DATE '2026-01-05' - CAST(lp.last_paid_payout_at AS date)) AS days_since_last_paid_payout, ROUND(COALESCE(fr.fee_revenue_eur, 0), 2) AS fee_revenue_ttm_eur FROM tenant_base tb LEFT JOIN last_order lo ON lo.tenant_id = tb.tenant_id LEFT JOIN last_paid_payout lp ON lp.tenant_id = tb.tenant_id LEFT JOIN fee_revenue_ttm fr ON fr.tenant_id = tb.tenant_id ORDER BY CASE tb.tenant_status WHEN 'churned' THEN 1 WHEN 'suspended' THEN 2 WHEN 'past_due' THEN 3 END, fee_revenue_ttm_eur DESC, tb.merchant_name ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: merchant_name, vertical, tenant_status, tenure_days, days_since_last_order, days_since_last_paid_payout, fee_revenue_ttm_eur
submitWITH tenant_base AS ( SELECT t.id AS tenant_id, t.name AS merchant_name, t.industry AS vertical, t.status AS tenant_status, t.created_at FROM tenants t WHERE t.status IN ('past_due','suspended','churned') AND t.deleted_at IS NULL ), last_order AS ( SELECT tenant_id, MAX(placed_at) AS last_order_at FROM orders GROUP BY tenant_id ), last_paid_payout AS ( SELECT tenant_id, MAX(paid_at) AS last_paid_payout_at FROM payouts WHERE status = 'paid' GROUP BY tenant_id ), fee_revenue_ttm AS ( SELECT p.tenant_id, SUM(CASE WHEN p.currency_code = 'EUR' THEN p.fee_amount ELSE p.fee_amount / er.rate END) AS fee_revenue_eur FROM payouts p LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = CAST(p.paid_at AS date) WHERE p.status = 'paid' AND p.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' GROUP BY p.tenant_id ) SELECT tb.merchant_name, tb.vertical, tb.tenant_status, (DATE '2026-01-05' - CAST(tb.created_at AS date)) AS tenure_days, (DATE '2026-01-05' - CAST(lo.last_order_at AS date)) AS days_since_last_order, (DATE '2026-01-05' - CAST(lp.last_paid_payout_at AS date)) AS days_since_last_paid_payout, ROUND(COALESCE(fr.fee_revenue_eur, 0), 2) AS fee_revenue_ttm_eur FROM tenant_base tb LEFT JOIN last_order lo ON lo.tenant_id = tb.tenant_id LEFT JOIN last_paid_payout lp ON lp.tenant_id = tb.tenant_id LEFT JOIN fee_revenue_ttm fr ON fr.tenant_id = tb.tenant_id ORDER BY CASE tb.tenant_status WHEN 'churned' THEN 1 WHEN 'suspended' THEN 2 WHEN 'past_due' THEN 3 END, fee_revenue_ttm_eur DESC, tb.merchant_name ASC
Submitted to tonight's batch. Your work on this request is done.