Queries · q273 · Head of Merchant Success · week 2, night 4
“Among established merchants (not formally churned), how long since their last order, and were they already slowing down before going quiet?”
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 4, request 1 of 3
Key read from last night: no merchant with 20+ lifetime orders has gone silent for 180+ days (0 rows) - established merchants aren't quietly churning, narrowing real merchant churn to the 9 small/new accounts already found. Marketing engagement barely differs between shoppers who later lapse and those who stay active, and support response times before a lapse track the same general degradation active shoppers also saw (likely the holiday ticket surge other teams flagged) rather than a lapse-specific pattern - both look like weak signals so far. Tonight (last night): (A) loosen the silent-churn threshold to check for a softer "slowing down" tier among established merchants, (B) test the refund-friction hypothesis directly, and (C) finally deliver the deferred top-15 support/CSAT read.
The engineer · 1 dry run on the empty schema, then the submission
SELECT t.name AS merchant_name, t.industry, t.status, CAST(o.last_order_ts AS DATE) AS last_order_date, (DATE '2026-01-05' - CAST(o.last_order_ts AS DATE)) AS days_since_last_order, o.lifetime_orders, (SELECT COUNT(*) FROM orders o2 WHERE o2.tenant_id = t.id AND o2.status <> 'cancelled' AND o2.placed_at > o.last_order_ts - INTERVAL '90 days' AND o2.placed_at <= o.last_order_ts ) AS orders_90d_before_last, (SELECT COUNT(*) FROM orders o3 WHERE o3.tenant_id = t.id AND o3.status <> 'cancelled' AND o3.placed_at > o.last_order_ts - INTERVAL '180 days' AND o3.placed_at <= o.last_order_ts - INTERVAL '90 days' ) AS orders_90d_before_prior FROM tenants t JOIN ( SELECT tenant_id, MAX(placed_at) AS last_order_ts, COUNT(*) AS lifetime_orders FROM orders WHERE status <> 'cancelled' GROUP BY tenant_id HAVING COUNT(*) >= 20 ) o ON o.tenant_id = t.id WHERE t.status <> 'churned' ORDER BY days_since_last_order DESC, t.id ASC LIMIT 50
“Query compiles cleanly with the expected columns. Submitting.”
“Submitted. The report returns one row per established merchant (lifetime orders ≥ 20, status not churned) with last order date, days since last order, lifetime orders, and the two trailing 90-day order-count windows leading up to their last order, ordered by days since last order descending (capped at 50 rows, tie-broken by tenant_id).”
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 | orders_90d_before_last | orders_90d_before_prior ------------------------------+---------------------------+-----------+-----------------+-----------------------+-----------------+------------------------+------------------------ Little Larch Kids | Apparel & Footwear | active | 2025-11-30 | 36 | 28 | 9 | 4 Tiny Atelier | Home & Living | active | 2025-12-01 | 35 | 96 | 8 | 13 Trailmark | Sports & Outdoor | active | 2025-12-09 | 27 | 34 | 12 | 6 Courtside Collective | Sports & Outdoor | active | 2025-12-13 | 23 | 27 | 6 | 5 Meadow Row | Apparel & Footwear | past_due | 2025-12-14 | 22 | 44 | 6 | 6 Fjordline Wear | Apparel & Footwear | active | 2025-12-16 | 20 | 134 | 23 | 8 Brick & Bobbin | Apparel & Footwear | active | 2025-12-16 | 20 | 69 | 10 | 4 OfficeHub Nordic | Office Supplies (B2B) | active | 2025-12-18 | 18 | 440 | 61 | 37 Castell Fasteners | Industrial Supplies (B2B) | active | 2025-12-19 | 17 | 389 | 48 | 30 Silverthread | Jewelry & Accessories | suspended | 2025-12-20 | 16 | 24 | 7 | 0 Circuit Harbor | Consumer Electronics | active | 2025-12-21 | 15 | 23 | 5 | 5 Driftwood Home | Home & Living | active | 2025-12-22 | 14 | 51 | 9 | 6 Barkley & Co. | Pet Supplies | active | 2025-12-22 | 14 | 54 | 10 | 8 Ondine Botanicals | Beauty | active | 2025-12-22 | 14 | 35 | 7 | 3 Purrfect Pantry | Pet Supplies | active | 2025-12-23 | 13 | 166 | 17 | 14 Deskline Supplies | Office Supplies (B2B) | active | 2025-12-23 | 13 | 161 | 25 | 16 Polder Packaging | Industrial Supplies (B2B) | active | 2025-12-24 | 12 | 112 | 17 | 18 Ballycorran Industrial Supply | Industrial Supplies (B2B) | active | 2025-12-26 | 10 | 839 | 93 | 75 Brewline Coffee | Food & Coffee | active | 2025-12-26 | 10 | 42 | 18 | 9 Willow & Wren | Home & Living | active | 2025-12-27 | 9 | 307 | 33 | 15 Hound Hamper | Pet Supplies | active | 2025-12-28 | 8 | 112 | 15 | 11 Pfotenglück | Pet Supplies | active | 2025-12-28 | 8 | 334 | 28 | 25 Buchstabe | Books & Stationery | active | 2025-12-28 | 8 | 63 | 16 | 4 Lamplight Living | Home & Living | active | 2025-12-28 | 8 | 83 | 19 | 15 Basecamp Nord | Sports & Outdoor | active | 2025-12-28 | 8 | 64 | 11 | 11 Wiesgrund Wohnen | Home & Living | active | 2025-12-29 | 7 | 729 | 110 | 59 Kayak Corner | Sports & Outdoor | active | 2025-12-29 | 7 | 321 | 21 | 22 Tail Wag Supply | Pet Supplies | active | 2025-12-29 | 7 | 213 | 29 | 23 Lumen Collective | Apparel & Footwear | active | 2025-12-29 | 7 | 148 | 21 | 10 Haven Linen | Home & Living | active | 2025-12-29 | 7 | 61 | 6 | 7 Saffron & Sage | Beauty | past_due | 2025-12-29 | 7 | 270 | 44 | 22 Sorella Skin | Beauty | active | 2025-12-29 | 7 | 133 | 43 | 22 Thistlecombe | Apparel & Footwear | active | 2025-12-29 | 7 | 107 | 43 | 22 Inkwell & Quire | Books & Stationery | active | 2025-12-29 | 7 | 22 | 5 | 2 Juniper Apothecary | Beauty | active | 2025-12-30 | 6 | 387 | 55 | 28 Wohnglück | Home & Living | active | 2025-12-30 | 6 | 311 | 59 | 28 Rue Lafayette Studio | Apparel & Footwear | active | 2025-12-30 | 6 | 375 | 59 | 37 Ostwind Mode | Apparel & Footwear | active | 2025-12-30 | 6 | 277 | 48 | 21 Ampfeld Elektronik | Consumer Electronics | active | 2025-12-30 | 6 | 1009 | 187 | 77 Sourdough Club | Food & Coffee | active | 2025-12-30 | 6 | 800 | 92 | 56 Mirelle | Beauty | active | 2025-12-30 | 6 | 99 | 23 | 14 Fern & Fable | Home & Living | active | 2025-12-30 | 6 | 112 | 19 | 7 Velvet Ash | Apparel & Footwear | active | 2025-12-30 | 6 | 339 | 50 | 28 Ampere & Co. | Consumer Electronics | active | 2025-12-30 | 6 | 152 | 23 | 8 Fell & Fjell | Sports & Outdoor | active | 2025-12-30 | 6 | 186 | 17 | 20 Paperplane Press | Books & Stationery | active | 2025-12-30 | 6 | 127 | 15 | 7 Roast Republic | Food & Coffee | active | 2025-12-30 | 6 | 105 | 21 | 7 Ridgeback Gear | Sports & Outdoor | active | 2025-12-30 | 6 | 236 | 29 | 24 Stoneware Studio | Home & Living | active | 2025-12-30 | 6 | 51 | 12 | 4 Hautnah | Beauty | active | 2025-12-30 | 6 | 174 | 35 | 13 (50 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 t.name AS merchant_name, t.industry, t.status, t.industry AS industry, t.status AS status, CAST(o.last_order_ts AS DATE) AS last_order_date, (DATE '2026-01-05' - CAST(o.last_order_ts AS DATE)) AS days_since_last_order, o.lifetime_orders, o.lifetime_orders AS lifetime_orders, (SELECT COUNT(*) FROM orders o2 WHERE o2.tenant_id = t.id AND o2.status <> 'cancelled' AND o2.placed_at > o.last_order_ts - INTERVAL '90 days' AND o2.placed_at <= o.last_order_ts ) AS orders_90d_before_last, (SELECT COUNT(*) FROM orders o3 WHERE o3.tenant_id = t.id AND o3.status <> 'cancelled' AND o3.placed_at > o.last_order_ts - INTERVAL '180 days' AND o3.placed_at <= o.last_order_ts - INTERVAL '90 days' ) AS orders_90d_before_priorFROM tenants tJOIN ( SELECT tenant_id, MAX(placed_at) AS last_order_ts, COUNT(*) AS lifetime_orders FROM orders WHERE status <> 'cancelled' GROUP BY tenant_id HAVING COUNT(*) >= 20) o ON o.tenant_id = t.idWHERE t.status <> 'churned'ORDER BY days_since_last_order DESC, t.id ASCLIMIT 50ORDER BY days_since_last_order DESC NULLS FIRST, t.id ASC NULLS LAST{{fetch_first(50)}}
SELECT t.name AS merchant_name, t.industry AS industry, t.status AS status, CAST(o.last_order_ts AS DATE) AS last_order_date, (DATE '2026-01-05' - CAST(o.last_order_ts AS DATE)) AS days_since_last_order, o.lifetime_orders AS lifetime_orders, (SELECT COUNT(*) FROM orders o2 WHERE o2.tenant_id = t.id AND o2.status <> 'cancelled' AND o2.placed_at > o.last_order_ts - INTERVAL '90 days' AND o2.placed_at <= o.last_order_ts ) AS orders_90d_before_last, (SELECT COUNT(*) FROM orders o3 WHERE o3.tenant_id = t.id AND o3.status <> 'cancelled' AND o3.placed_at > o.last_order_ts - INTERVAL '180 days' AND o3.placed_at <= o.last_order_ts - INTERVAL '90 days' ) AS orders_90d_before_prior FROM tenants t JOIN ( SELECT tenant_id, MAX(placed_at) AS last_order_ts, COUNT(*) AS lifetime_orders FROM orders WHERE status <> 'cancelled' GROUP BY tenant_id HAVING COUNT(*) >= 20 ) o ON o.tenant_id = t.id WHERE t.status <> 'churned' ORDER BY days_since_last_order DESC NULLS FIRST, t.id ASC NULLS LAST FETCH FIRST 50 ROWS ONLY
SELECT t.name AS merchant_name, t.industry AS industry, t.status AS status, CAST(o.last_order_ts AS DATE) AS last_order_date, (DATE '2026-01-05' - CAST(o.last_order_ts AS DATE)) AS days_since_last_order, o.lifetime_orders AS lifetime_orders, (SELECT COUNT(*) FROM orders o2 WHERE o2.tenant_id = t.id AND o2.status <> 'cancelled' AND o2.placed_at > o.last_order_ts - INTERVAL '90 days' AND o2.placed_at <= o.last_order_ts ) AS orders_90d_before_last, (SELECT COUNT(*) FROM orders o3 WHERE o3.tenant_id = t.id AND o3.status <> 'cancelled' AND o3.placed_at > o.last_order_ts - INTERVAL '180 days' AND o3.placed_at <= o.last_order_ts - INTERVAL '90 days' ) AS orders_90d_before_prior FROM tenants t JOIN ( SELECT tenant_id, MAX(placed_at) AS last_order_ts, COUNT(*) AS lifetime_orders FROM orders WHERE status <> 'cancelled' GROUP BY tenant_id HAVING COUNT(*) >= 20 ) o ON o.tenant_id = t.id WHERE t.status <> 'churned' ORDER BY days_since_last_order DESC NULLS FIRST, t.id ASC 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: Merchants slowing down: order-gap distribution among established merchants Question: Among established merchants (not formally churned), how long since their last order, and were they already slowing down before going quiet? Grain: one row per merchant (tenant) with lifetime orders at least 20 and status not churned Columns: - merchant name - industry - status - last order date - days since last order - lifetime orders - orders in 90 days before last order - orders in 90 days before that Details: From orders (excluding cancelled): last order date is max placed_at per tenant; lifetime orders is total count. Restrict to tenants with lifetime orders at least 20 and status not equal to churned. days since last order is 2026-01-05 minus last order date. Also compute orders in the 90 days ending at the last order date, and orders in the 90 days before that, to show the trend into their most recent activity. Order by days since last order descending, tie-break tenant_id ascending. 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, CAST(o.last_order_ts AS DATE) AS last_order_date, (DATE '2026-01-05' - CAST(o.last_order_ts AS DATE)) AS days_since_last_order, o.lifetime_orders, (SELECT COUNT(*) FROM orders o2 WHERE o2.tenant_id = t.id AND o2.status <> 'cancelled' AND o2.placed_at > o.last_order_ts - INTERVAL '90 days' AND o2.placed_at <= o.last_order_ts ) AS orders_90d_before_last, (SELECT COUNT(*) FROM orders o3 WHERE o3.tenant_id = t.id AND o3.status <> 'cancelled' AND o3.placed_at > o.last_order_ts - INTERVAL '180 days' AND o3.placed_at <= o.last_order_ts - INTERVAL '90 days' ) AS orders_90d_before_prior FROM tenants t JOIN ( SELECT tenant_id, MAX(placed_at) AS last_order_ts, COUNT(*) AS lifetime_orders FROM orders WHERE status <> 'cancelled' GROUP BY tenant_id HAVING COUNT(*) >= 20 ) o ON o.tenant_id = t.id WHERE t.status <> 'churned' ORDER BY days_since_last_order DESC, t.id ASC 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, orders_90d_before_last, orders_90d_before_prior
submitSELECT t.name AS merchant_name, t.industry, t.status, CAST(o.last_order_ts AS DATE) AS last_order_date, (DATE '2026-01-05' - CAST(o.last_order_ts AS DATE)) AS days_since_last_order, o.lifetime_orders, (SELECT COUNT(*) FROM orders o2 WHERE o2.tenant_id = t.id AND o2.status <> 'cancelled' AND o2.placed_at > o.last_order_ts - INTERVAL '90 days' AND o2.placed_at <= o.last_order_ts ) AS orders_90d_before_last, (SELECT COUNT(*) FROM orders o3 WHERE o3.tenant_id = t.id AND o3.status <> 'cancelled' AND o3.placed_at > o.last_order_ts - INTERVAL '180 days' AND o3.placed_at <= o.last_order_ts - INTERVAL '90 days' ) AS orders_90d_before_prior FROM tenants t JOIN ( SELECT tenant_id, MAX(placed_at) AS last_order_ts, COUNT(*) AS lifetime_orders FROM orders WHERE status <> 'cancelled' GROUP BY tenant_id HAVING COUNT(*) >= 20 ) o ON o.tenant_id = t.id WHERE t.status <> 'churned' ORDER BY days_since_last_order DESC, t.id ASC LIMIT 50
Submitted to tonight's batch. Your work on this request is done.