Queries · q271 · Head of Merchant Success · week 2, night 3

Marketing engagement before shoppers go quiet: lapsed vs active shoppers

“Do shoppers who stop engaging with campaigns go quiet sooner, and does disengagement show up before they lapse?”

Manager · asked the questionSamuel WhitcombeHead of Merchant Success. New in the role, owns the relationship with the largest merchants and quarterly business reviews with them in January.

Analyst · planned the reportArjun PatelSegmenter: breaks totals down by merchant size, vertical, country and channel.

Engineer · wrote the SQLInesThinks in window functions: ranks, running totals, LAG/LEAD for comparisons, percent-of-total.

Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told

A business event lands on the Head of Merchant Success's desk
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.
One of the 35 events we wrote for the role play; the manager heard of it at the start of week 2 and had to cover it in that week's note. How events arise · All the events
Samuel Whitcombe writes to Arjun Patel
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:
- Castell Fasteners is a contradiction — our #4 merchant, highly sticky customer base (88.6% repeat), but the weakest YoY growth of the top tier and a steep traffic drop. I want to know whether that traffic drop is a real warning sign or just how B2B industrial buying seasonally looks, before I raise it with them in their QBR.
- Good catch on the refund-rate math — please don't let anything with the >100% problem anywhere near a client-facing page until it's fixed.
My questions for this week:
1. Is Castell's traffic decline a real risk or seasonal noise? I want a clear enough answer that I either flag it to them directly in the QBR or leave it alone.
2. How are our top ~15 merchants' shoppers being supported, and does it show up in how those merchants feel about us? This was cut last week — please pick it back up.
3. For the task force below: which merchants have actually left the platform (or are clearly heading that way), and what did their last few months look like beforehand — their sales trend, their shoppers' buying behavior, how much support friction they had? I'm less interested in a single root cause and more in whatever pattern shows up consistently across the merchants who left, since that's what would let us catch the next one early.
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
A note from the VP Customer Experience, Daniel Achterberg
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:
1. Does ticket experience precede merchant churn? For merchants who later left, did their shoppers see slower first responses, more reopened tickets, or more escalations in the months before? I'd want to know how far out a warning shows up.
2. Do returns/refunds friction and churn move together? Where refunds get delayed, disputed, or partially handled, do we see both unhappy shoppers (low ratings/CSAT) and merchants churning shortly after? I'm curious whether refund delay is a leading indicator or just a symptom.
3. Are complaint themes shifting before a merchant leaves? Is there a recognizable pattern in what shoppers are complaining about (e.g. shipping, product quality, billing) in the weeks before a merchant churns, versus a stable merchant?
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
A note from the Head of Growth Marketing, Chloé Marchetti
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:
1. Do shoppers who stop engaging with our marketing touches (emails, campaigns, loyalty) churn sooner than the ones who stay engaged? If disengagement shows up weeks before a churn, that's an early signal worth acting on.
2. Does a merchant's own marketing activity level relate to whether their shoppers stick around? I want to know if merchants who run fewer campaigns, promotions, or loyalty programs are the ones seeing higher shopper churn — that would tell us whether under-investment in marketing tools is part of the story.
3. Is there a pattern in how promo- or discount-driven shoppers behave right before leaving — e.g. do they go quiet after a coupon or loyalty redemption, or does spend drop off without any further marketing response from us?
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é
Arjun Patel plans the night
The analyst · week 2, night 3, request 2 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.
Marketing engagement before shoppers go quiet: lapsed vs active shoppers
Question
Do shoppers who stop engaging with campaigns go quiet sooner, and does disengagement show up before they lapse?
One row per
shopper group and time window before reference date
Columns
- shopper group
- window before reference
- shopper count
- messages sent
- messages opened
- open rate pct
- messages clicked
- click rate pct

Ines writes the SQL

The engineer · 1 dry run on the empty schema, then the submission

Dry run 1 · STATUS: COMPILES (dry run on the empty schema, no data read)

WITH user_orders AS (
  SELECT
    tenant_id,
    user_id,
    MAX(placed_at) AS last_order_at,
    COUNT(*) AS order_count
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY tenant_id, user_id
),
classified AS (
  SELECT
    tenant_id,
    user_id,
    CASE
      WHEN order_count >= 3
        AND last_order_at::date <= DATE '2025-12-31' - INTERVAL '180 days'
        AND last_order_at::date >= DATE '2025-12-31' - INTERVAL '365 days'
        THEN 'lapsed'
      WHEN order_count >= 3
        AND last_order_at::date > DATE '2025-12-31' - INTERVAL '90 days'
        AND last_order_at::date <= DATE '2025-12-31'
        THEN 'active'
      ELSE NULL
    END AS shopper_group,
    CASE
      WHEN order_count >= 3
        AND last_order_at::date <= DATE '2025-12-31' - INTERVAL '180 days'
        AND last_order_at::date >= DATE '2025-12-31' - INTERVAL '365 days'
        THEN last_order_at::date
      WHEN order_count >= 3
        AND last_order_at::date > DATE '2025-12-31' - INTERVAL '90 days'
        AND last_order_at::date <= DATE '2025-12-31'
        THEN DATE '2025-12-31'
      ELSE NULL
    END AS reference_date
  FROM user_orders
),
eligible AS (
  SELECT tenant_id, user_id, shopper_group, reference_date
  FROM classified
  WHERE shopper_group IS NOT NULL
),
messages AS (
  SELECT
    e.shopper_group,
    e.tenant_id,
    e.user_id,
    CASE
      WHEN (e.reference_date - cm.sent_at::date) BETWEEN 0 AND 90 THEN '0-90 days before reference'
      WHEN (e.reference_date - cm.sent_at::date) BETWEEN 91 AND 180 THEN '91-180 days before reference'
      ELSE NULL
    END AS window_label,
    cm.opened_at,
    cm.clicked_at
  FROM eligible e
  JOIN campaign_messages cm
    ON cm.tenant_id = e.tenant_id
   AND cm.user_id = e.user_id
   AND cm.sent_at IS NOT NULL
)
SELECT
  shopper_group,
  window_label AS window_before_reference,
  COUNT(DISTINCT tenant_id || '-' || user_id) AS shopper_count,
  COUNT(*) AS messages_sent,
  COUNT(opened_at) AS messages_opened,
  ROUND(100.0 * COUNT(opened_at) / NULLIF(COUNT(*), 0), 1) AS open_rate_pct,
  COUNT(clicked_at) AS messages_clicked,
  ROUND(100.0 * COUNT(clicked_at) / NULLIF(COUNT(*), 0), 1) AS click_rate_pct
FROM messages
WHERE window_label IS NOT NULL
GROUP BY shopper_group, window_label
ORDER BY shopper_group, window_label

“Query compiles and matches the requested grain and columns. Submitting.”

Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

“Submitted. Summary: the query classifies shoppers into 'lapsed' and 'active' groups based on their last non-cancelled order date and lifetime order count (≥3), sets each shopper's reference date accordingly, buckets their campaign messages into the two 90-day windows before that reference date, and reports shopper counts plus sent/opened/clicked volumes and rates per group/window — yielding the expected 4 rows.”

The report comes back overnight

As Arjun Patel saw it, from the small dataset the agents worked on

shopper_group | window_before_reference      | shopper_count | messages_sent | messages_opened | open_rate_pct | messages_clicked | click_rate_pct
--------------+------------------------------+---------------+---------------+-----------------+---------------+------------------+---------------
active        | 0-90 days before reference   |           704 |          5896 |            2149 |          36.4 |              274 |            4.6
active        | 91-180 days before reference |           694 |          5958 |            2225 |          37.3 |              251 |            4.2
lapsed        | 0-90 days before reference   |           283 |          2592 |             933 |          36.0 |              144 |            5.6
lapsed        | 91-180 days before reference |           264 |          2233 |             801 |          35.9 |               86 |            3.9
(4 rows)

It becomes benchmark query q271

Changed only where the benchmark needs the same result on every system

spellingcast (11×)
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
made explicita selected t.col gets AS col, its standard name made explicit (5×)
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (2×)

The benchmark text, against the submitted SQL

WITH user_orders AS (  SELECT    tenant_id,    user_id,    MAX(placed_at) AS last_order_at,    COUNT(*) AS order_count  FROM orders  WHERE status <> 'cancelled'  GROUP BY tenant_id, user_id),classified AS (  SELECT    tenant_id,    user_id,    CASE      WHEN order_count >= 3        AND last_order_at::date <= DATE '2025-12-31' - INTERVAL '180 days'        AND last_order_at::date >= DATE '2025-12-31' - INTERVAL '365 days'        AND CAST(last_order_at AS date) <= DATE '2025-12-31' - INTERVAL '180 days'        AND CAST(last_order_at AS date) >= DATE '2025-12-31' - INTERVAL '365 days'        THEN 'lapsed'      WHEN order_count >= 3        AND last_order_at::date > DATE '2025-12-31' - INTERVAL '90 days'        AND last_order_at::date <= DATE '2025-12-31'        AND CAST(last_order_at AS date) > DATE '2025-12-31' - INTERVAL '90 days'        AND CAST(last_order_at AS date) <= DATE '2025-12-31'        THEN 'active'      ELSE NULL    END AS shopper_group,    CASE      WHEN order_count >= 3        AND last_order_at::date <= DATE '2025-12-31' - INTERVAL '180 days'        AND last_order_at::date >= DATE '2025-12-31' - INTERVAL '365 days'        THEN last_order_at::date        AND CAST(last_order_at AS date) <= DATE '2025-12-31' - INTERVAL '180 days'        AND CAST(last_order_at AS date) >= DATE '2025-12-31' - INTERVAL '365 days'        THEN CAST(last_order_at AS date)      WHEN order_count >= 3        AND last_order_at::date > DATE '2025-12-31' - INTERVAL '90 days'        AND last_order_at::date <= DATE '2025-12-31'        AND CAST(last_order_at AS date) > DATE '2025-12-31' - INTERVAL '90 days'        AND CAST(last_order_at AS date) <= DATE '2025-12-31'        THEN DATE '2025-12-31'      ELSE NULL    END AS reference_date  FROM user_orders),eligible AS (  SELECT tenant_id, user_id, shopper_group, reference_date  FROM classified  WHERE shopper_group IS NOT NULL),messages AS (  SELECT    e.shopper_group,    e.tenant_id,    e.user_id,    e.shopper_group AS shopper_group,    e.tenant_id AS tenant_id,    e.user_id AS user_id,    CASE      WHEN (e.reference_date - cm.sent_at::date) BETWEEN 0 AND 90 THEN '0-90 days before reference'      WHEN (e.reference_date - cm.sent_at::date) BETWEEN 91 AND 180 THEN '91-180 days before reference'      WHEN (e.reference_date - CAST(cm.sent_at AS date)) BETWEEN 0 AND 90 THEN '0-90 days before reference'      WHEN (e.reference_date - CAST(cm.sent_at AS date)) BETWEEN 91 AND 180 THEN '91-180 days before reference'      ELSE NULL    END AS window_label,    cm.opened_at,    cm.clicked_at    cm.opened_at AS opened_at,    cm.clicked_at AS clicked_at  FROM eligible e  JOIN campaign_messages cm    ON cm.tenant_id = e.tenant_id   AND cm.user_id = e.user_id   AND cm.sent_at IS NOT NULL)SELECT  shopper_group,  window_label AS window_before_reference,  COUNT(DISTINCT tenant_id || '-' || user_id) AS shopper_count,  COUNT(*) AS messages_sent,  COUNT(opened_at) AS messages_opened,  ROUND(100.0 * COUNT(opened_at) / NULLIF(COUNT(*), 0), 1) AS open_rate_pct,  100.0 * COUNT(opened_at) / NULLIF(COUNT(*), 0) AS open_rate_pct,  COUNT(clicked_at) AS messages_clicked,  ROUND(100.0 * COUNT(clicked_at) / NULLIF(COUNT(*), 0), 1) AS click_rate_pct  100.0 * COUNT(clicked_at) / NULLIF(COUNT(*), 0) AS click_rate_pctFROM messagesWHERE window_label IS NOT NULLGROUP BY shopper_group, window_labelORDER BY shopper_group, window_labelORDER BY shopper_group NULLS LAST, window_label NULLS LAST

As each system received it; highlighted, the parts spelled for that system

WITH user_orders AS (
  SELECT
    tenant_id,
    user_id,
    MAX(placed_at) AS last_order_at,
    COUNT(*) AS order_count
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY tenant_id, user_id
),
classified AS (
  SELECT
    tenant_id,
    user_id,
    CASE
      WHEN order_count >= 3
        AND CAST(last_order_at AS date) <= DATE '2025-12-31' - INTERVAL '180 days'
        AND CAST(last_order_at AS date) >= DATE '2025-12-31' - INTERVAL '365 days'
        THEN 'lapsed'
      WHEN order_count >= 3
        AND CAST(last_order_at AS date) > DATE '2025-12-31' - INTERVAL '90 days'
        AND CAST(last_order_at AS date) <= DATE '2025-12-31'
        THEN 'active'
      ELSE NULL
    END AS shopper_group,
    CASE
      WHEN order_count >= 3
        AND CAST(last_order_at AS date) <= DATE '2025-12-31' - INTERVAL '180 days'
        AND CAST(last_order_at AS date) >= DATE '2025-12-31' - INTERVAL '365 days'
        THEN CAST(last_order_at AS date)
      WHEN order_count >= 3
        AND CAST(last_order_at AS date) > DATE '2025-12-31' - INTERVAL '90 days'
        AND CAST(last_order_at AS date) <= DATE '2025-12-31'
        THEN DATE '2025-12-31'
      ELSE NULL
    END AS reference_date
  FROM user_orders
),
eligible AS (
  SELECT tenant_id, user_id, shopper_group, reference_date
  FROM classified
  WHERE shopper_group IS NOT NULL
),
messages AS (
  SELECT
    e.shopper_group AS shopper_group,
    e.tenant_id AS tenant_id,
    e.user_id AS user_id,
    CASE
      WHEN (e.reference_date - CAST(cm.sent_at AS date)) BETWEEN 0 AND 90 THEN '0-90 days before reference'
      WHEN (e.reference_date - CAST(cm.sent_at AS date)) BETWEEN 91 AND 180 THEN '91-180 days before reference'
      ELSE NULL
    END AS window_label,
    cm.opened_at AS opened_at,
    cm.clicked_at AS clicked_at
  FROM eligible e
  JOIN campaign_messages cm
    ON cm.tenant_id = e.tenant_id
   AND cm.user_id = e.user_id
   AND cm.sent_at IS NOT NULL
)
SELECT
  shopper_group,
  window_label AS window_before_reference,
  COUNT(DISTINCT tenant_id || '-' || user_id) AS shopper_count,
  COUNT(*) AS messages_sent,
  COUNT(opened_at) AS messages_opened,
  100.0 * COUNT(opened_at) / NULLIF(COUNT(*), 0) AS open_rate_pct,
  COUNT(clicked_at) AS messages_clicked,
  100.0 * COUNT(clicked_at) / NULLIF(COUNT(*), 0) AS click_rate_pct
FROM messages
WHERE window_label IS NOT NULL
GROUP BY shopper_group, window_label
ORDER BY shopper_group NULLS LAST, window_label NULLS LAST

Scale factor 1000, c8a.24xlarge, after the last load. q271 on the start page, with every configuration.

Everything, as recorded

The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted

Ines's whole session, 7 turns

The request, as Ines received it

Report request from Arjun Patel (analyst for the Head of Merchant Success):

Title: Marketing engagement before shoppers go quiet: lapsed vs active shoppers
Question: Do shoppers who stop engaging with campaigns go quiet sooner, and does disengagement show up before they lapse?
Grain: one row per shopper group and time window before reference date
Columns:
- shopper group
- window before reference
- shopper count
- messages sent
- messages opened
- open rate pct
- messages clicked
- click rate pct
Details: Two groups, each needing at least 3 lifetime orders excluding cancelled: 'lapsed' = last order placed_at 180 to 365 days before 2025-12-31; 'active' = last order within 90 days of 2025-12-31. Reference date is the user's own last order date for lapsed, and 2025-12-31 for active. Two windows per group: '0-90 days before reference' and '91-180 days before reference'. From campaign_messages (sent_at, opened_at, clicked_at) count sent, opened, clicked per group/window; open rate pct is opened divided by sent; click rate pct is clicked divided by sent; shopper count is distinct users in that window. Order by shopper group, then window ascending. Expect 4 rows.

Check your query with the warehouse's dry_run tool, then hand it in with submit.

Ines calls dry_run

WITH user_orders AS (
  SELECT
    tenant_id,
    user_id,
    MAX(placed_at) AS last_order_at,
    COUNT(*) AS order_count
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY tenant_id, user_id
),
classified AS (
  SELECT
    tenant_id,
    user_id,
    CASE
      WHEN order_count >= 3
        AND last_order_at::date <= DATE '2025-12-31' - INTERVAL '180 days'
        AND last_order_at::date >= DATE '2025-12-31' - INTERVAL '365 days'
        THEN 'lapsed'
      WHEN order_count >= 3
        AND last_order_at::date > DATE '2025-12-31' - INTERVAL '90 days'
        AND last_order_at::date <= DATE '2025-12-31'
        THEN 'active'
      ELSE NULL
    END AS shopper_group,
    CASE
      WHEN order_count >= 3
        AND last_order_at::date <= DATE '2025-12-31' - INTERVAL '180 days'
        AND last_order_at::date >= DATE '2025-12-31' - INTERVAL '365 days'
        THEN last_order_at::date
      WHEN order_count >= 3
        AND last_order_at::date > DATE '2025-12-31' - INTERVAL '90 days'
        AND last_order_at::date <= DATE '2025-12-31'
        THEN DATE '2025-12-31'
      ELSE NULL
    END AS reference_date
  FROM user_orders
),
eligible AS (
  SELECT tenant_id, user_id, shopper_group, reference_date
  FROM classified
  WHERE shopper_group IS NOT NULL
),
messages AS (
  SELECT
    e.shopper_group,
    e.tenant_id,
    e.user_id,
    CASE
      WHEN (e.reference_date - cm.sent_at::date) BETWEEN 0 AND 90 THEN '0-90 days before reference'
      WHEN (e.reference_date - cm.sent_at::date) BETWEEN 91 AND 180 THEN '91-180 days before reference'
      ELSE NULL
    END AS window_label,
    cm.opened_at,
    cm.clicked_at
  FROM eligible e
  JOIN campaign_messages cm
    ON cm.tenant_id = e.tenant_id
   AND cm.user_id = e.user_id
   AND cm.sent_at IS NOT NULL
)
SELECT
  shopper_group,
  window_label AS window_before_reference,
  COUNT(DISTINCT tenant_id || '-' || user_id) AS shopper_count,
  COUNT(*) AS messages_sent,
  COUNT(opened_at) AS messages_opened,
  ROUND(100.0 * COUNT(opened_at) / NULLIF(COUNT(*), 0), 1) AS open_rate_pct,
  COUNT(clicked_at) AS messages_clicked,
  ROUND(100.0 * COUNT(clicked_at) / NULLIF(COUNT(*), 0), 1) AS click_rate_pct
FROM messages
WHERE window_label IS NOT NULL
GROUP BY shopper_group, window_label
ORDER BY shopper_group, window_label

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: shopper_group, window_before_reference, shopper_count, messages_sent, messages_opened, open_rate_pct, messages_clicked, click_rate_pct

InesQuery compiles and matches the requested grain and columns. Submitting.

Ines calls submit

WITH user_orders AS (
  SELECT
    tenant_id,
    user_id,
    MAX(placed_at) AS last_order_at,
    COUNT(*) AS order_count
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY tenant_id, user_id
),
classified AS (
  SELECT
    tenant_id,
    user_id,
    CASE
      WHEN order_count >= 3
        AND last_order_at::date <= DATE '2025-12-31' - INTERVAL '180 days'
        AND last_order_at::date >= DATE '2025-12-31' - INTERVAL '365 days'
        THEN 'lapsed'
      WHEN order_count >= 3
        AND last_order_at::date > DATE '2025-12-31' - INTERVAL '90 days'
        AND last_order_at::date <= DATE '2025-12-31'
        THEN 'active'
      ELSE NULL
    END AS shopper_group,
    CASE
      WHEN order_count >= 3
        AND last_order_at::date <= DATE '2025-12-31' - INTERVAL '180 days'
        AND last_order_at::date >= DATE '2025-12-31' - INTERVAL '365 days'
        THEN last_order_at::date
      WHEN order_count >= 3
        AND last_order_at::date > DATE '2025-12-31' - INTERVAL '90 days'
        AND last_order_at::date <= DATE '2025-12-31'
        THEN DATE '2025-12-31'
      ELSE NULL
    END AS reference_date
  FROM user_orders
),
eligible AS (
  SELECT tenant_id, user_id, shopper_group, reference_date
  FROM classified
  WHERE shopper_group IS NOT NULL
),
messages AS (
  SELECT
    e.shopper_group,
    e.tenant_id,
    e.user_id,
    CASE
      WHEN (e.reference_date - cm.sent_at::date) BETWEEN 0 AND 90 THEN '0-90 days before reference'
      WHEN (e.reference_date - cm.sent_at::date) BETWEEN 91 AND 180 THEN '91-180 days before reference'
      ELSE NULL
    END AS window_label,
    cm.opened_at,
    cm.clicked_at
  FROM eligible e
  JOIN campaign_messages cm
    ON cm.tenant_id = e.tenant_id
   AND cm.user_id = e.user_id
   AND cm.sent_at IS NOT NULL
)
SELECT
  shopper_group,
  window_label AS window_before_reference,
  COUNT(DISTINCT tenant_id || '-' || user_id) AS shopper_count,
  COUNT(*) AS messages_sent,
  COUNT(opened_at) AS messages_opened,
  ROUND(100.0 * COUNT(opened_at) / NULLIF(COUNT(*), 0), 1) AS open_rate_pct,
  COUNT(clicked_at) AS messages_clicked,
  ROUND(100.0 * COUNT(clicked_at) / NULLIF(COUNT(*), 0), 1) AS click_rate_pct
FROM messages
WHERE window_label IS NOT NULL
GROUP BY shopper_group, window_label
ORDER BY shopper_group, window_label

The warehouse answers

Submitted to tonight's batch. Your work on this request is done.

InesSubmitted. Summary: the query classifies shoppers into 'lapsed' and 'active' groups based on their last non-cancelled order date and lifetime order count (≥3), sets each shopper's reference date accordingly, buckets their campaign messages into the two 90-day windows before that reference date, and reports shopper counts plus sent/opened/clicked volumes and rates per group/window — yielding the expected 4 rows.

Not shown here: 240 KB of context the agent was given, its instructions and the schema documentation; it is in the raw transcript.

Ines's transcript (276 KB) · Arjun Patel's transcript (881 KB) · Samuel Whitcombe's transcript (261 KB) · the night's plan (4 KB) · the night's reports (3 KB) · the week's memo (4 KB)

Marketing engagement before shoppers go quiet: lapsed vs active shoppers

A business event lands on the Head of Merchant Success's desk

Samuel Whitcombe writes to Arjun Patel

Arjun Patel plans the night

Ines writes the SQL

The report comes back overnight

It becomes benchmark query q271

Everything, as recorded