Queries · q049 · Head of Growth Marketing · week 1, night 1

Website traffic channel mix, 2025 vs 2024

“Which acquisition channels bring the most site traffic across all merchants, and how has that mix shifted versus a year ago?”

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 SQLViktorOld-school warehouse engineer: explicit JOIN chains straight to the base tables, derived tables, GROUP BY with CASE pivots, few CTEs.

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 1, request 1 of 3
Chloé's #1 ask (channels) is pure orientation, so I'm spending two of tonight's three slots there to get both a traffic-side view (where sessions come from) and a conversion-side view (which channels actually get credit for orders/revenue) — these are different grains/sources (web_sessions vs attributions+campaigns) so they need separate reports. I collapsed each to a single 'channel' grain with a built-in YoY or share comparison instead of a month-by-channel matrix, to stay under the 50-row cap and still show 'what's normal' plus 'has it shifted'. Third slot starts Q2 (discounting) since it's next in priority and a simple monthly baseline will tell me quickly whether discounting is a big or small share of revenue before I dig into promo-order health specifics on a later night.
Website traffic channel mix, 2025 vs 2024
Question
Which acquisition channels bring the most site traffic across all merchants, and how has that mix shifted versus a year ago?
One row per
traffic channel (utm_source, bucketed)
Columns
- channel - web_sessions.utm_source, with null/blank coalesced to 'direct/none'
- sessions_2025 - count of web_sessions with started_at in 2025-01-01..2025-12-31
- share_of_2025_sessions_pct - sessions_2025 / total 2025 sessions across all channels * 100, rounded to 1 decimal
- sessions_2024 - count of web_sessions with started_at in 2024-01-01..2024-12-31, same channel bucketing
- yoy_change_pct - (sessions_2025 - sessions_2024) / nullif(sessions_2024,0) * 100, rounded to 1 decimal
- bounce_rate_2025_pct - share of 2025 sessions for that channel where is_bounce is true, *100, rounded to 1 decimal
- avg_page_views_2025 - average page_view_count for 2025 sessions in that channel, rounded to 1 decimal

Viktor 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
  channel,
  sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end) as sessions_2025,
  round(100.0 * sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)
    / nullif(sum(sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)) over (), 0), 1) as share_of_2025_sessions_pct,
  sum(case when started_at >= date '2024-01-01' and started_at < date '2025-01-01' then 1 else 0 end) as sessions_2024,
  round(100.0 * (
    sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)
    - sum(case when started_at >= date '2024-01-01' and started_at < date '2025-01-01' then 1 else 0 end)
  ) / nullif(sum(case when started_at >= date '2024-01-01' and started_at < date '2025-01-01' then 1 else 0 end), 0), 1) as yoy_change_pct,
  round(100.0 * sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' and is_bounce then 1 else 0 end)
    / nullif(sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end), 0), 1) as bounce_rate_2025_pct,
  round(avg(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then page_view_count end), 1) as avg_page_views_2025
from (
  select
    coalesce(nullif(trim(utm_source), ''), 'direct/none') as channel,
    started_at,
    is_bounce,
    page_view_count
  from web_sessions
) s
group by channel
order by sessions_2025 desc, channel asc
limit 12

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

“Submitted. The report ranks the top 12 utm_source channels (bucketed, with blanks/nulls as "direct/none") by 2025 session volume, showing each channel's share of 2025 traffic, 2024 session counts, year-over-year % change, 2025 bounce rate, and average 2025 page views per session — one row per channel as requested.”

The report comes back overnight

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

channel     | sessions_2025 | share_of_2025_sessions_pct | sessions_2024 | yoy_change_pct | bounce_rate_2025_pct | avg_page_views_2025
------------+---------------+----------------------------+---------------+----------------+----------------------+--------------------
direct/none |         20644 |                       57.4 |         16367 |           26.1 |                 44.1 |                 4.5
google      |          6895 |                       19.2 |          6433 |            7.2 |                 43.1 |                 4.4
instagram   |          5024 |                       14.0 |          4149 |           21.1 |                 59.9 |                 3.4
referral    |          2774 |                        7.7 |          2204 |           25.9 |                 45.9 |                 4.5
facebook    |           381 |                        1.1 |           876 |          -56.5 |                 57.5 |                 3.4
newsletter  |           253 |                        0.7 |           227 |           11.5 |                 35.6 |                 5.8
(6 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
- q052Direct traffic concentration by merchant

It becomes benchmark query q049

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

spellingfetch first
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: rounding removed; results keep full precision; AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
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×)
placeholderfetch first

The benchmark text, against the submitted SQL

select  channel,  sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end) as sessions_2025,  round(100.0 * sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)    / nullif(sum(sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)) over (), 0), 1) as share_of_2025_sessions_pct,  100.0 * sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)/ nullif(sum(sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)) over (), 0) as share_of_2025_sessions_pct,  sum(case when started_at >= date '2024-01-01' and started_at < date '2025-01-01' then 1 else 0 end) as sessions_2024,  round(100.0 * (    sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)    - sum(case when started_at >= date '2024-01-01' and started_at < date '2025-01-01' then 1 else 0 end)  ) / nullif(sum(case when started_at >= date '2024-01-01' and started_at < date '2025-01-01' then 1 else 0 end), 0), 1) as yoy_change_pct,  round(100.0 * sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' and is_bounce then 1 else 0 end)    / nullif(sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end), 0), 1) as bounce_rate_2025_pct,  round(avg(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then page_view_count end), 1) as avg_page_views_2025  100.0 * (sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)- sum(case when started_at >= date '2024-01-01' and started_at < date '2025-01-01' then 1 else 0 end)) / nullif(sum(case when started_at >= date '2024-01-01' and started_at < date '2025-01-01' then 1 else 0 end), 0) as yoy_change_pct,  100.0 * sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' and is_bounce then 1 else 0 end)/ nullif(sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end), 0) as bounce_rate_2025_pct,  avg(cast(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then page_view_count end as numeric(18,6))) as avg_page_views_2025from (  select    coalesce(nullif(trim(utm_source), ''), 'direct/none') as channel,    started_at,    is_bounce,    page_view_count  from web_sessions) sgroup by channelorder by sessions_2025 desc, channel asclimit 12order by sessions_2025 desc NULLS FIRST, channel asc NULLS LAST{{fetch_first(12)}}

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

select
  channel,
  sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end) as sessions_2025,
  100.0 * sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)
/ nullif(sum(sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)) over (), 0) as share_of_2025_sessions_pct,
  sum(case when started_at >= date '2024-01-01' and started_at < date '2025-01-01' then 1 else 0 end) as sessions_2024,
  100.0 * (
sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)
- sum(case when started_at >= date '2024-01-01' and started_at < date '2025-01-01' then 1 else 0 end)
) / nullif(sum(case when started_at >= date '2024-01-01' and started_at < date '2025-01-01' then 1 else 0 end), 0) as yoy_change_pct,
  100.0 * sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' and is_bounce then 1 else 0 end)
/ nullif(sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end), 0) as bounce_rate_2025_pct,
  avg(cast(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then page_view_count end as numeric(18,6))) as avg_page_views_2025
from (
  select
    coalesce(nullif(trim(utm_source), ''), 'direct/none') as channel,
    started_at,
    is_bounce,
    page_view_count
  from web_sessions
) s
group by channel
order by sessions_2025 desc NULLS FIRST, channel asc NULLS LAST
FETCH FIRST 12 ROWS ONLY

select
  channel,
  sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end) as sessions_2025,
  100.0 * sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)
/ nullif(sum(sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)) over (), 0) as share_of_2025_sessions_pct,
  sum(case when started_at >= date '2024-01-01' and started_at < date '2025-01-01' then 1 else 0 end) as sessions_2024,
  100.0 * (
sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)
- sum(case when started_at >= date '2024-01-01' and started_at < date '2025-01-01' then 1 else 0 end)
) / nullif(sum(case when started_at >= date '2024-01-01' and started_at < date '2025-01-01' then 1 else 0 end), 0) as yoy_change_pct,
  100.0 * sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' and is_bounce then 1 else 0 end)
/ nullif(sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end), 0) as bounce_rate_2025_pct,
  avg(cast(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then page_view_count end as numeric(18,6))) as avg_page_views_2025
from (
  select
    coalesce(nullif(trim(utm_source), ''), 'direct/none') as channel,
    started_at,
    is_bounce,
    page_view_count
  from web_sessions
) s
group by channel
order by sessions_2025 desc NULLS FIRST, channel asc NULLS LAST
LIMIT 12

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

Viktor's whole session, 6 turns

The request, as Viktor received it

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

Title: Website traffic channel mix, 2025 vs 2024
Question: Which acquisition channels bring the most site traffic across all merchants, and how has that mix shifted versus a year ago?
Grain: one row per traffic channel (utm_source, bucketed)
Columns:
- channel - web_sessions.utm_source, with null/blank coalesced to 'direct/none'
- sessions_2025 - count of web_sessions with started_at in 2025-01-01..2025-12-31
- share_of_2025_sessions_pct - sessions_2025 / total 2025 sessions across all channels * 100, rounded to 1 decimal
- sessions_2024 - count of web_sessions with started_at in 2024-01-01..2024-12-31, same channel bucketing
- yoy_change_pct - (sessions_2025 - sessions_2024) / nullif(sessions_2024,0) * 100, rounded to 1 decimal
- bounce_rate_2025_pct - share of 2025 sessions for that channel where is_bounce is true, *100, rounded to 1 decimal
- avg_page_views_2025 - average page_view_count for 2025 sessions in that channel, rounded to 1 decimal
Details: Source: web_sessions only, no tenant filter (aggregate across all merchants). Bucket channel as coalesce(nullif(trim(utm_source),''), 'direct/none'). Compute 2025 and 2024 metrics in the same row per channel. Rank by sessions_2025 descending and limit to the top 12 channels by that metric; break remaining ties by channel name ascending for a deterministic order. This is a single-grain (channel) summary, not a time series, to stay well under the row limit while still showing both current mix and year-over-year direction.

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

Viktor calls dry_run

select
  channel,
  sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end) as sessions_2025,
  round(100.0 * sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)
    / nullif(sum(sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)) over (), 0), 1) as share_of_2025_sessions_pct,
  sum(case when started_at >= date '2024-01-01' and started_at < date '2025-01-01' then 1 else 0 end) as sessions_2024,
  round(100.0 * (
    sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)
    - sum(case when started_at >= date '2024-01-01' and started_at < date '2025-01-01' then 1 else 0 end)
  ) / nullif(sum(case when started_at >= date '2024-01-01' and started_at < date '2025-01-01' then 1 else 0 end), 0), 1) as yoy_change_pct,
  round(100.0 * sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' and is_bounce then 1 else 0 end)
    / nullif(sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end), 0), 1) as bounce_rate_2025_pct,
  round(avg(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then page_view_count end), 1) as avg_page_views_2025
from (
  select
    coalesce(nullif(trim(utm_source), ''), 'direct/none') as channel,
    started_at,
    is_bounce,
    page_view_count
  from web_sessions
) s
group by channel
order by sessions_2025 desc, channel asc
limit 12

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: channel, sessions_2025, share_of_2025_sessions_pct, sessions_2024, yoy_change_pct, bounce_rate_2025_pct, avg_page_views_2025

Viktor calls submit

select
  channel,
  sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end) as sessions_2025,
  round(100.0 * sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)
    / nullif(sum(sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)) over (), 0), 1) as share_of_2025_sessions_pct,
  sum(case when started_at >= date '2024-01-01' and started_at < date '2025-01-01' then 1 else 0 end) as sessions_2024,
  round(100.0 * (
    sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end)
    - sum(case when started_at >= date '2024-01-01' and started_at < date '2025-01-01' then 1 else 0 end)
  ) / nullif(sum(case when started_at >= date '2024-01-01' and started_at < date '2025-01-01' then 1 else 0 end), 0), 1) as yoy_change_pct,
  round(100.0 * sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' and is_bounce then 1 else 0 end)
    / nullif(sum(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then 1 else 0 end), 0), 1) as bounce_rate_2025_pct,
  round(avg(case when started_at >= date '2025-01-01' and started_at < date '2026-01-01' then page_view_count end), 1) as avg_page_views_2025
from (
  select
    coalesce(nullif(trim(utm_source), ''), 'direct/none') as channel,
    started_at,
    is_bounce,
    page_view_count
  from web_sessions
) s
group by channel
order by sessions_2025 desc, channel asc
limit 12

The warehouse answers

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

ViktorSubmitted. The report ranks the top 12 `utm_source` channels (bucketed, with blanks/nulls as "direct/none") by 2025 session volume, showing each channel's share of 2025 traffic, 2024 session counts, year-over-year % change, 2025 bounce rate, and average 2025 page views per session — one row per channel as requested.

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

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

Website traffic channel mix, 2025 vs 2024

Chloé Marchetti writes to Sofie Nilsen

Sofie Nilsen plans the night

Viktor writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q049

Everything, as recorded