Queries · q052 · Head of Growth Marketing · week 1, night 2

Direct traffic concentration by merchant

“Is the dominant 'direct/none' traffic channel spread evenly across merchants, or concentrated in a handful of them?”

Manager · asked the questionChloé MarchettiHead of Growth Marketing. Joined from a direct-to-consumer brand. Wants to sell merchants more of Tidewick's marketing tools.

Analyst · planned the reportSofie NilsenStoryteller: looks for the two or three findings that matter and digs until each is explained.

Engineer · wrote the SQLBeaWrites SQL the way the company's BI tool generates it: verbose fully qualified aliases (t0, t1, ...), a subquery per measure joined back on the grain keys, COALESCE everywhere.

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

Chloé Marchetti writes to Sofie Nilsen
The manager, at the start of week 1
Subject: What I need to get oriented this week

Hi Sofie,

Thrilled to be here — here's where my head's at. I know you've only got a handful of report slots a night, so I've ordered these by what I need most. Please push back if any of these are too broad or should split into more than one report.
1. How are merchants actually acquiring shoppers, and through which channels? I want to understand where traffic and new customers are coming from across our merchant base — which channels show up most, and whether that's shifted recently. This is really just orientation for me: I don't yet know what "normal" looks like here.
2. Are the coupons and promotions merchants run actually paying off? I'd like a sense of how much discounting is happening, how often codes get used, and whether promoted orders look healthy (not just cheaper). I'm trying to figure out if we should be telling merchants to promote more carefully — this is relevant for your product-pitch angle too.
3. What does our email/campaign activity look like, and is anyone opening or clicking? I want a baseline read on send volume and engagement so I know whether campaigns are a strength or a weak spot for merchants on the platform.
4. Is anyone actually using loyalty and referrals, and does it seem to matter? I keep hearing these are "nice to have" features. I want to know whether merchants who use them have any different shopper behavior, so I know whether to invest in pushing adoption.
5. (If there's room this week) Do we run experiments/A-B tests at all, and does anyone act on the results? Lower priority — just curious whether this is a muscle we have or one I need to build from scratch.
What I need this for: I'm meeting the leadership team Friday and want a grounded, numbers-based view of growth marketing's current state before I propose anything. No need for polish — I just want to know what's really happening so I don't propose things that are already working, or miss things that are quietly broken.

Thanks for taking this on — let me know if you want me to cut this list down.

Chloé
Sofie Nilsen plans the night
The analyst · week 1, night 2, request 1 of 3
Two things from last night need explaining before I trust the headline numbers, plus one gap Chloé explicitly asked about that I haven't covered yet. First, 'direct/none' is 57% of 2025 sessions and growing 26% YoY — before I call that normal I want to see whether it's a tracking artifact concentrated in a few big merchants (consistent with the 'handful of large merchants' pattern) or spread evenly. Second, the discount-rate report showed two anomalous months (Jan 53%, Jul 59%) versus a ~35% baseline the rest of the year, with discount size per order flat — meaning more orders got a code, not bigger discounts. I want to know if that's a few tenants running sitewide promos. Third, Chloé specifically asked whether promoted orders 'look healthy, not just cheaper' — I haven't produced that comparison yet, so it goes in tonight regardless of the anomalies.
Direct traffic concentration by merchant
Question
Is the dominant 'direct/none' traffic channel spread evenly across merchants, or concentrated in a handful of them?
One row per
tenant
Columns
- tenant_name - tenants.name
- total_sessions_2025 - count of web_sessions for that tenant with started_at in 2025
- direct_sessions_2025 - count of those sessions where utm_source is null/blank
- direct_share_of_tenant_pct - direct_sessions_2025 / total_sessions_2025 * 100, rounded to 1 decimal
- pct_of_platform_direct_sessions - direct_sessions_2025 / (sum of direct_sessions_2025 across all tenants) * 100, rounded to 1 decimal

Bea 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)

SELECT
  t0.name AS tenant_name,
  COALESCE(t1.total_sessions_2025, 0) AS total_sessions_2025,
  COALESCE(t2.direct_sessions_2025, 0) AS direct_sessions_2025,
  ROUND(COALESCE(t2.direct_sessions_2025, 0) * 100.0 / NULLIF(COALESCE(t1.total_sessions_2025, 0), 0), 1) AS direct_share_of_tenant_pct,
  ROUND(COALESCE(t2.direct_sessions_2025, 0) * 100.0 / NULLIF(t3.platform_direct_sessions_2025, 0), 1) AS pct_of_platform_direct_sessions
FROM tenants t0
LEFT JOIN (
  SELECT tenant_id, COUNT(*) AS total_sessions_2025
  FROM web_sessions
  WHERE started_at >= DATE '2025-01-01' AND started_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t1 ON t1.tenant_id = t0.id
LEFT JOIN (
  SELECT tenant_id, COUNT(*) AS direct_sessions_2025
  FROM web_sessions
  WHERE started_at >= DATE '2025-01-01' AND started_at < DATE '2026-01-01'
    AND (utm_source IS NULL OR TRIM(utm_source) = '')
  GROUP BY tenant_id
) t2 ON t2.tenant_id = t0.id
CROSS JOIN (
  SELECT COUNT(*) AS platform_direct_sessions_2025
  FROM web_sessions
  WHERE started_at >= DATE '2025-01-01' AND started_at < DATE '2026-01-01'
    AND (utm_source IS NULL OR TRIM(utm_source) = '')
) t3
ORDER BY direct_sessions_2025 DESC, tenant_name ASC
LIMIT 15

“Query compiles and returns the expected columns. Submitting.”

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

“Submitted. The report returns one row per tenant (top 15 by direct session volume) with total sessions, direct sessions, each tenant's own direct share, and that tenant's share of platform-wide direct traffic — which directly tests the concentration hypothesis behind report 1's headline numbers.”

The report comes back overnight

As Sofie Nilsen saw it, from the small dataset the agents worked on

tenant_name            | total_sessions_2025 | direct_sessions_2025 | direct_share_of_tenant_pct | pct_of_platform_direct_sessions
-----------------------+---------------------+----------------------+----------------------------+--------------------------------
Cragsley Outfitters    |                8134 |                 4345 |                       53.4 |                            21.0
Marrowby & Vane        |                3699 |                 2057 |                       55.6 |                            10.0
Brennquell Kaffee      |                2206 |                 1329 |                       60.2 |                             6.4
Ampfeld Elektronik     |                1483 |                 1188 |                       80.1 |                             5.8
Studio Vrij            |                1404 |                  803 |                       57.2 |                             3.9
Petalcourt Beauty      |                1221 |                  648 |                       53.1 |                             3.1
Fennel & Flint         |                1138 |                  577 |                       50.7 |                             2.8
Cinder Peak Sports Co. |                 842 |                  487 |                       57.8 |                             2.4
Grind Collective       |                 697 |                  406 |                       58.2 |                             2.0
Gratlinie Outdoor      |                 739 |                  397 |                       53.7 |                             1.9
Linden & Loom          |                 699 |                  368 |                       52.6 |                             1.8
Wiesgrund Wohnen       |                 678 |                  359 |                       52.9 |                             1.7
Kibblewick             |                 667 |                  296 |                       44.4 |                             1.4
Honeycomb Larder       |                 455 |                  272 |                       59.8 |                             1.3
Spice Route Box        |                 465 |                  260 |                       55.9 |                             1.3
(15 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
It builds on:
- q049Website traffic channel mix, 2025 vs 2024

It becomes benchmark query q052

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

spellingfetch first
determinismtie-breaker: tenant names are not unique
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (3×)
placeholderfetch first

The benchmark text, against the submitted SQL

SELECT  t0.name AS tenant_name,  COALESCE(t1.total_sessions_2025, 0) AS total_sessions_2025,  COALESCE(t2.direct_sessions_2025, 0) AS direct_sessions_2025,  ROUND(COALESCE(t2.direct_sessions_2025, 0) * 100.0 / NULLIF(COALESCE(t1.total_sessions_2025, 0), 0), 1) AS direct_share_of_tenant_pct,  ROUND(COALESCE(t2.direct_sessions_2025, 0) * 100.0 / NULLIF(t3.platform_direct_sessions_2025, 0), 1) AS pct_of_platform_direct_sessions  COALESCE(t2.direct_sessions_2025, 0) * 100.0 / NULLIF(COALESCE(t1.total_sessions_2025, 0), 0) AS direct_share_of_tenant_pct,  COALESCE(t2.direct_sessions_2025, 0) * 100.0 / NULLIF(t3.platform_direct_sessions_2025, 0) AS pct_of_platform_direct_sessionsFROM tenants t0LEFT JOIN (  SELECT tenant_id, COUNT(*) AS total_sessions_2025  FROM web_sessions  WHERE started_at >= DATE '2025-01-01' AND started_at < DATE '2026-01-01'  GROUP BY tenant_id) t1 ON t1.tenant_id = t0.idLEFT JOIN (  SELECT tenant_id, COUNT(*) AS direct_sessions_2025  FROM web_sessions  WHERE started_at >= DATE '2025-01-01' AND started_at < DATE '2026-01-01'    AND (utm_source IS NULL OR TRIM(utm_source) = '')  GROUP BY tenant_id) t2 ON t2.tenant_id = t0.idCROSS JOIN (  SELECT COUNT(*) AS platform_direct_sessions_2025  FROM web_sessions  WHERE started_at >= DATE '2025-01-01' AND started_at < DATE '2026-01-01'    AND (utm_source IS NULL OR TRIM(utm_source) = '')) t3ORDER BY direct_sessions_2025 DESC, tenant_name ASCLIMIT 15ORDER BY direct_sessions_2025 DESC NULLS FIRST, tenant_name ASC NULLS LAST, t0.id ASC NULLS LAST{{fetch_first(15)}}

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

SELECT
  t0.name AS tenant_name,
  COALESCE(t1.total_sessions_2025, 0) AS total_sessions_2025,
  COALESCE(t2.direct_sessions_2025, 0) AS direct_sessions_2025,
  COALESCE(t2.direct_sessions_2025, 0) * 100.0 / NULLIF(COALESCE(t1.total_sessions_2025, 0), 0) AS direct_share_of_tenant_pct,
  COALESCE(t2.direct_sessions_2025, 0) * 100.0 / NULLIF(t3.platform_direct_sessions_2025, 0) AS pct_of_platform_direct_sessions
FROM tenants t0
LEFT JOIN (
  SELECT tenant_id, COUNT(*) AS total_sessions_2025
  FROM web_sessions
  WHERE started_at >= DATE '2025-01-01' AND started_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t1 ON t1.tenant_id = t0.id
LEFT JOIN (
  SELECT tenant_id, COUNT(*) AS direct_sessions_2025
  FROM web_sessions
  WHERE started_at >= DATE '2025-01-01' AND started_at < DATE '2026-01-01'
    AND (utm_source IS NULL OR TRIM(utm_source) = '')
  GROUP BY tenant_id
) t2 ON t2.tenant_id = t0.id
CROSS JOIN (
  SELECT COUNT(*) AS platform_direct_sessions_2025
  FROM web_sessions
  WHERE started_at >= DATE '2025-01-01' AND started_at < DATE '2026-01-01'
    AND (utm_source IS NULL OR TRIM(utm_source) = '')
) t3
ORDER BY direct_sessions_2025 DESC NULLS FIRST, tenant_name ASC NULLS LAST, t0.id ASC NULLS LAST
FETCH FIRST 15 ROWS ONLY

SELECT
  t0.name AS tenant_name,
  COALESCE(t1.total_sessions_2025, 0) AS total_sessions_2025,
  COALESCE(t2.direct_sessions_2025, 0) AS direct_sessions_2025,
  COALESCE(t2.direct_sessions_2025, 0) * 100.0 / NULLIF(COALESCE(t1.total_sessions_2025, 0), 0) AS direct_share_of_tenant_pct,
  COALESCE(t2.direct_sessions_2025, 0) * 100.0 / NULLIF(t3.platform_direct_sessions_2025, 0) AS pct_of_platform_direct_sessions
FROM tenants t0
LEFT JOIN (
  SELECT tenant_id, COUNT(*) AS total_sessions_2025
  FROM web_sessions
  WHERE started_at >= DATE '2025-01-01' AND started_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t1 ON t1.tenant_id = t0.id
LEFT JOIN (
  SELECT tenant_id, COUNT(*) AS direct_sessions_2025
  FROM web_sessions
  WHERE started_at >= DATE '2025-01-01' AND started_at < DATE '2026-01-01'
    AND (utm_source IS NULL OR TRIM(utm_source) = '')
  GROUP BY tenant_id
) t2 ON t2.tenant_id = t0.id
CROSS JOIN (
  SELECT COUNT(*) AS platform_direct_sessions_2025
  FROM web_sessions
  WHERE started_at >= DATE '2025-01-01' AND started_at < DATE '2026-01-01'
    AND (utm_source IS NULL OR TRIM(utm_source) = '')
) t3
ORDER BY direct_sessions_2025 DESC NULLS FIRST, tenant_name ASC NULLS LAST, t0.id ASC NULLS LAST
LIMIT 15

Scale factor 1000, c8a.24xlarge, after the last load. q052 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

Bea's whole session, 7 turns

The request, as Bea received it

Report request from Sofie Nilsen (analyst for the Head of Growth Marketing):

Title: Direct traffic concentration by merchant
Question: Is the dominant 'direct/none' traffic channel spread evenly across merchants, or concentrated in a handful of them?
Grain: one row per tenant
Columns:
- tenant_name - tenants.name
- total_sessions_2025 - count of web_sessions for that tenant with started_at in 2025
- direct_sessions_2025 - count of those sessions where utm_source is null/blank
- direct_share_of_tenant_pct - direct_sessions_2025 / total_sessions_2025 * 100, rounded to 1 decimal
- pct_of_platform_direct_sessions - direct_sessions_2025 / (sum of direct_sessions_2025 across all tenants) * 100, rounded to 1 decimal
Details: Source: web_sessions joined to tenants on tenant_id. Filter started_at between 2025-01-01 and 2025-12-31. 'direct/none' = utm_source is null or trimmed empty string. Compute per-tenant totals and the platform-wide direct total as a window/subquery for the share column. Order by direct_sessions_2025 descending, tie-break by tenant_name ascending, limit to top 15 tenants. This tests whether report 1's 57%-direct / +26% YoY finding is a concentration effect from a few large merchants.

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

Bea calls dry_run

SELECT
  t0.name AS tenant_name,
  COALESCE(t1.total_sessions_2025, 0) AS total_sessions_2025,
  COALESCE(t2.direct_sessions_2025, 0) AS direct_sessions_2025,
  ROUND(COALESCE(t2.direct_sessions_2025, 0) * 100.0 / NULLIF(COALESCE(t1.total_sessions_2025, 0), 0), 1) AS direct_share_of_tenant_pct,
  ROUND(COALESCE(t2.direct_sessions_2025, 0) * 100.0 / NULLIF(t3.platform_direct_sessions_2025, 0), 1) AS pct_of_platform_direct_sessions
FROM tenants t0
LEFT JOIN (
  SELECT tenant_id, COUNT(*) AS total_sessions_2025
  FROM web_sessions
  WHERE started_at >= DATE '2025-01-01' AND started_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t1 ON t1.tenant_id = t0.id
LEFT JOIN (
  SELECT tenant_id, COUNT(*) AS direct_sessions_2025
  FROM web_sessions
  WHERE started_at >= DATE '2025-01-01' AND started_at < DATE '2026-01-01'
    AND (utm_source IS NULL OR TRIM(utm_source) = '')
  GROUP BY tenant_id
) t2 ON t2.tenant_id = t0.id
CROSS JOIN (
  SELECT COUNT(*) AS platform_direct_sessions_2025
  FROM web_sessions
  WHERE started_at >= DATE '2025-01-01' AND started_at < DATE '2026-01-01'
    AND (utm_source IS NULL OR TRIM(utm_source) = '')
) t3
ORDER BY direct_sessions_2025 DESC, tenant_name ASC
LIMIT 15

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: tenant_name, total_sessions_2025, direct_sessions_2025, direct_share_of_tenant_pct, pct_of_platform_direct_sessions

BeaQuery compiles and returns the expected columns. Submitting.

Bea calls submit

SELECT
  t0.name AS tenant_name,
  COALESCE(t1.total_sessions_2025, 0) AS total_sessions_2025,
  COALESCE(t2.direct_sessions_2025, 0) AS direct_sessions_2025,
  ROUND(COALESCE(t2.direct_sessions_2025, 0) * 100.0 / NULLIF(COALESCE(t1.total_sessions_2025, 0), 0), 1) AS direct_share_of_tenant_pct,
  ROUND(COALESCE(t2.direct_sessions_2025, 0) * 100.0 / NULLIF(t3.platform_direct_sessions_2025, 0), 1) AS pct_of_platform_direct_sessions
FROM tenants t0
LEFT JOIN (
  SELECT tenant_id, COUNT(*) AS total_sessions_2025
  FROM web_sessions
  WHERE started_at >= DATE '2025-01-01' AND started_at < DATE '2026-01-01'
  GROUP BY tenant_id
) t1 ON t1.tenant_id = t0.id
LEFT JOIN (
  SELECT tenant_id, COUNT(*) AS direct_sessions_2025
  FROM web_sessions
  WHERE started_at >= DATE '2025-01-01' AND started_at < DATE '2026-01-01'
    AND (utm_source IS NULL OR TRIM(utm_source) = '')
  GROUP BY tenant_id
) t2 ON t2.tenant_id = t0.id
CROSS JOIN (
  SELECT COUNT(*) AS platform_direct_sessions_2025
  FROM web_sessions
  WHERE started_at >= DATE '2025-01-01' AND started_at < DATE '2026-01-01'
    AND (utm_source IS NULL OR TRIM(utm_source) = '')
) t3
ORDER BY direct_sessions_2025 DESC, tenant_name ASC
LIMIT 15

The warehouse answers

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

BeaSubmitted. The report returns one row per tenant (top 15 by direct session volume) with total sessions, direct sessions, each tenant's own direct share, and that tenant's share of platform-wide direct traffic — which directly tests the concentration hypothesis behind report 1's headline numbers.

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

Bea's transcript (262 KB) · Sofie Nilsen's transcript (793 KB) · Chloé Marchetti's transcript (263 KB) · the night's plan (5 KB) · the night's reports (7 KB) · the week's memo (3 KB)

Direct traffic concentration by merchant

Chloé Marchetti writes to Sofie Nilsen

Sofie Nilsen plans the night

Bea writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q052

Everything, as recorded