Queries · q270 · Head of Merchant Success · week 2, night 3
“Beyond the 9 formally churned merchants, which substantial merchants have gone quiet without yet being flagged as churned?”
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 Chloé Marchetti, Head of Growth Marketing and Daniel Achterberg, VP Customer Experience
Churn has become a board topic, for merchants and for their shoppers. A cross-functional task force wants to understand which experiences precede leaving, and which early signals would let us act before it happens.
The manager, at the start of week 2
Subject: Next week's priorities — QBR follow-ups + the churn task force
Hi Arjun,
Really useful memo — thank you. A few things jumped out that I want to understand better, plus a new cross-functional ask that's going to need a slot or two this week.
What surprised me / needs explaining first:
My questions for this week:
New context — the churn task force: Churn is now a board topic, both merchant churn and end-shopper churn, and I'm leading this jointly with Chloé Marchetti and Daniel Achterberg. The deliverable the three of us owe the board is a shortlist of early-warning signals — things we can observe weeks or months before a merchant (or a merchant's shoppers) leaves — and a recommendation on which of those signals is reliable enough to act on. It feeds directly into two decisions: whether Merchant Success should get a proactive outreach trigger for at-risk accounts, and whether Growth Marketing and CX get an equivalent retention trigger for end-shoppers. So anything you can tell me this week about what preceded past departures — on the merchant side especially, since that's my piece — is directly useful input, even if it's partial. I'll compare notes with Chloé's and Daniel's analysts before we draft anything for the board.
Given the slot limits, prioritize in this order: Castell explanation, then the task-force churn pattern, then support/CSAT if a slot remains.
Thanks, Samuel
Subject: CX signals for the churn task force
Arjun,
A few things I'd want the task force to look at from the shopper/support side:
If we can pin down even one or two of these as reliable early signals, it changes what Samuel's team and I should be monitoring week to week rather than finding out after the fact.
Daniel
Subject: Churn task force — what I'd like us to look at from Growth
Hi Arjun,
Glad this is getting cross-functional attention. From the marketing side, here's what I think matters:
Happy to jump on a call if useful — mainly want to make sure whatever "early warning" signals come out of this include the marketing-touch side, not just support or payment signals.
Best, Chloé
The analyst · week 2, night 3, request 1 of 3
Last night's result: only 9 merchants are formally 'churned', and they are all tiny (1-11 orders in their final quarter, many with zero prior-period history) - these look like accounts that never gained traction rather than established businesses declining, so there isn't much of a "warning sign" trend to extract from them, and ticket/refund signals are mostly flat at zero. To give the task force something more useful, tonight I broaden the net to substantial merchants who have gone quiet without being flagged 'churned' (silent churn), and start on the shopper-level asks from Chloe and Daniel: does marketing disengagement or worse support precede a shopper going quiet. These are both new, independent questions at a different grain, so each gets its own report.
The engineer · 1 dry run on the empty schema, then the submission
SELECT t.name AS merchant_name, t.industry, t.status, MAX(o.placed_at)::date AS last_order_date, (DATE '2026-01-05' - MAX(o.placed_at)::date) AS days_since_last_order, COUNT(*) AS lifetime_orders, ROUND(SUM(o.grand_total / er.rate), 2) AS lifetime_revenue_eur FROM tenants t JOIN orders o ON o.tenant_id = t.id AND o.status <> 'cancelled' AND o.placed_at IS NOT NULL JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = o.placed_at::date WHERE t.status <> 'churned' GROUP BY t.id, t.name, t.industry, t.status HAVING MAX(o.placed_at) < TIMESTAMP '2025-07-09' AND COUNT(*) >= 20 ORDER BY lifetime_revenue_eur DESC, t.id LIMIT 50
“Query compiles and matches the requested grain and columns. Submitting now.”
“Submitted. The report returns one row per non-churned merchant with ≥20 lifetime (non-cancelled) orders whose last order was before 2025-07-09, ordered by lifetime EUR revenue descending, capped at 50 rows.”
As Arjun Patel saw it, from the small dataset the agents worked on
merchant_name | industry | status | last_order_date | days_since_last_order | lifetime_orders | lifetime_revenue_eur --------------+----------+--------+-----------------+-----------------------+-----------------+--------------------- (0 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
It builds on:
Changed only where the benchmark needs the same result on every system
SELECT t.name AS merchant_name, t.industry, t.status, MAX(o.placed_at)::date AS last_order_date, (DATE '2026-01-05' - MAX(o.placed_at)::date) AS days_since_last_order, t.industry AS industry, t.status AS status, CAST(MAX(o.placed_at) AS date) AS last_order_date, (DATE '2026-01-05' - CAST(MAX(o.placed_at) AS date)) AS days_since_last_order, COUNT(*) AS lifetime_orders, ROUND(SUM(o.grand_total / er.rate), 2) AS lifetime_revenue_eur SUM(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) AS lifetime_revenue_eurFROM tenants tJOIN orders o ON o.tenant_id = t.id AND o.status <> 'cancelled' AND o.placed_at IS NOT NULLJOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = o.placed_at::date AND er.as_of = CAST(o.placed_at AS date)WHERE t.status <> 'churned'GROUP BY t.id, t.name, t.industry, t.statusHAVING MAX(o.placed_at) < TIMESTAMP '2025-07-09' AND COUNT(*) >= 20ORDER BY lifetime_revenue_eur DESC, t.idLIMIT 50ORDER BY lifetime_revenue_eur DESC NULLS FIRST, t.id NULLS LAST{{fetch_first(50)}}
SELECT t.name AS merchant_name, t.industry AS industry, t.status AS status, CAST(MAX(o.placed_at) AS date) AS last_order_date, (DATE '2026-01-05' - CAST(MAX(o.placed_at) AS date)) AS days_since_last_order, COUNT(*) AS lifetime_orders, SUM(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) AS lifetime_revenue_eur FROM tenants t JOIN orders o ON o.tenant_id = t.id AND o.status <> 'cancelled' AND o.placed_at IS NOT NULL 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 t.status <> 'churned' GROUP BY t.id, t.name, t.industry, t.status HAVING MAX(o.placed_at) < TIMESTAMP '2025-07-09' AND COUNT(*) >= 20 ORDER BY lifetime_revenue_eur DESC NULLS FIRST, t.id NULLS LAST FETCH FIRST 50 ROWS ONLY
SELECT t.name AS merchant_name, t.industry AS industry, t.status AS status, CAST(MAX(o.placed_at) AS date) AS last_order_date, (DATE '2026-01-05' - CAST(MAX(o.placed_at) AS date)) AS days_since_last_order, COUNT(*) AS lifetime_orders, SUM(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) AS lifetime_revenue_eur FROM tenants t JOIN orders o ON o.tenant_id = t.id AND o.status <> 'cancelled' AND o.placed_at IS NOT NULL 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 t.status <> 'churned' GROUP BY t.id, t.name, t.industry, t.status HAVING MAX(o.placed_at) < TIMESTAMP '2025-07-09' AND COUNT(*) >= 20 ORDER BY lifetime_revenue_eur DESC NULLS FIRST, t.id NULLS LAST LIMIT 50
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 Arjun Patel (analyst for the Head of Merchant Success): Title: Silent-churn merchants: substantial accounts gone quiet without a churned flag Question: Beyond the 9 formally churned merchants, which substantial merchants have gone quiet without yet being flagged as churned? Grain: one row per merchant (tenant), status not churned, no orders in 180+ days, lifetime orders at least 20 Columns: - merchant name - industry - status - last order date - days since last order - lifetime orders - lifetime revenue EUR Details: Tenants with status not equal to churned. From orders (excluding cancelled): last order date is max placed_at; include only tenants whose last order date is before 2025-07-09 (180+ days before 2026-01-05) and whose lifetime order count is at least 20. lifetime revenue EUR is sum of grand_total converted to EUR via exchange_rates matched to placed_at. days since last order is 2026-01-05 minus last order date. Order by lifetime revenue EUR descending, tie-break tenant_id. Cap 50 rows, note total count. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT t.name AS merchant_name, t.industry, t.status, MAX(o.placed_at)::date AS last_order_date, (DATE '2026-01-05' - MAX(o.placed_at)::date) AS days_since_last_order, COUNT(*) AS lifetime_orders, ROUND(SUM(o.grand_total / er.rate), 2) AS lifetime_revenue_eur FROM tenants t JOIN orders o ON o.tenant_id = t.id AND o.status <> 'cancelled' AND o.placed_at IS NOT NULL JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = o.placed_at::date WHERE t.status <> 'churned' GROUP BY t.id, t.name, t.industry, t.status HAVING MAX(o.placed_at) < TIMESTAMP '2025-07-09' AND COUNT(*) >= 20 ORDER BY lifetime_revenue_eur DESC, t.id LIMIT 50
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: merchant_name, industry, status, last_order_date, days_since_last_order, lifetime_orders, lifetime_revenue_eur
submitSELECT t.name AS merchant_name, t.industry, t.status, MAX(o.placed_at)::date AS last_order_date, (DATE '2026-01-05' - MAX(o.placed_at)::date) AS days_since_last_order, COUNT(*) AS lifetime_orders, ROUND(SUM(o.grand_total / er.rate), 2) AS lifetime_revenue_eur FROM tenants t JOIN orders o ON o.tenant_id = t.id AND o.status <> 'cancelled' AND o.placed_at IS NOT NULL JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = o.placed_at::date WHERE t.status <> 'churned' GROUP BY t.id, t.name, t.industry, t.status HAVING MAX(o.placed_at) < TIMESTAMP '2025-07-09' AND COUNT(*) >= 20 ORDER BY lifetime_revenue_eur DESC, t.id LIMIT 50
Submitted to tonight's batch. Your work on this request is done.