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

Which marketing channels drive orders and revenue

“Among orders marketing can take credit for, which campaign channels generate the most attributed orders and revenue, and what's the resulting average order value?”

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

1. 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é

   Show the whole note

2. Sofie Nilsen plans the night

   The analyst · week 1, night 1, request 2 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.

   Read the whole plan
   Which marketing channels drive orders and revenue

   Question

   Among orders marketing can take credit for, which campaign channels generate the most attributed orders and revenue, and what's the resulting average order value?

   One row per

   campaign channel (campaigns.channel)

   Columns

   • channel - campaigns.channel
   • attributed_orders - count of distinct attributions.order_id where order_id is not null
   • attributed_revenue_eur - sum of attributions.attributed_revenue converted to EUR, rounded to 0 decimals
   • share_of_revenue_pct - attributed_revenue_eur / total attributed_revenue_eur across all channels * 100, rounded to 1 decimal
   • avg_order_value_eur - attributed_revenue_eur / attributed_orders, rounded to 0 decimals

3. Omar 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 attr AS (
     SELECT
       c.channel AS channel,
       a.order_id AS order_id,
       a.attributed_revenue AS attributed_revenue,
       CASE WHEN a.currency_code = 'EUR' THEN 1 ELSE er.rate END AS fx_rate
     FROM attributions a
     JOIN campaigns c ON c.id = a.campaign_id
     LEFT JOIN exchange_rates er
       ON er.base_code = 'EUR'
       AND er.quote_code = a.currency_code
       AND er.as_of = CAST(a.occurred_at AS date)
     WHERE a.occurred_at >= TIMESTAMP '2025-01-01'
       AND a.occurred_at < TIMESTAMP '2026-01-01'
       AND a.order_id IS NOT NULL
   ),
   per_channel AS (
     SELECT
       channel,
       COUNT(DISTINCT order_id) AS attributed_orders,
       SUM(attributed_revenue / fx_rate) AS attributed_revenue_eur
     FROM attr
     GROUP BY channel
   ),
   totals AS (
     SELECT SUM(attributed_revenue_eur) AS total_revenue_eur FROM per_channel
   )
   SELECT
     pc.channel,
     pc.attributed_orders,
     ROUND(pc.attributed_revenue_eur, 0) AS attributed_revenue_eur,
     ROUND(pc.attributed_revenue_eur / t.total_revenue_eur * 100, 1) AS share_of_revenue_pct,
     ROUND(pc.attributed_revenue_eur / pc.attributed_orders, 0) AS avg_order_value_eur
   FROM per_channel pc
   CROSS JOIN totals t
   ORDER BY attributed_revenue_eur DESC, channel ASC

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

   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 campaign channel with attributed orders, EUR-converted attributed revenue, share of total revenue, and average order value for 2025, sourced from attributions (purchase-linked rows only) joined to campaigns.”

4. The report comes back overnight

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

   channel     | attributed_orders | attributed_revenue_eur | share_of_revenue_pct | avg_order_value_eur
   ------------+-------------------+------------------------+----------------------+--------------------
   paid_search |              2503 |               640581.0 |                 78.6 |               256.0
   email       |               519 |               152494.0 |                 18.7 |               294.0
   paid_social |                70 |                17313.0 |                  2.1 |               247.0
   influencer  |                26 |                 4535.0 |                  0.6 |               174.0
   (4 rows)

5. What came of it

   The memo and the queries that followed; links found by title, so inferred

   Later queries that build on this report:

   • q263Does the unattributed-orders blind spot differ by vertical or by new-vs-repeat customers?
   • q264Is the attribution blind spot growing, shrinking, or stable over 2025?
   • q265Why do last week's and this week's 'attributed orders' counts disagree so much?

6. It becomes benchmark query q050

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

   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
   • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6)
   • made explicita selected t.col gets AS col, its standard name made explicit (2×)
   • 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 attr AS (  SELECT    c.channel AS channel,    a.order_id AS order_id,    a.attributed_revenue AS attributed_revenue,    CASE WHEN a.currency_code = 'EUR' THEN 1 ELSE er.rate END AS fx_rate  FROM attributions a  JOIN campaigns c ON c.id = a.campaign_id  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'    AND er.quote_code = a.currency_code    AND er.as_of = CAST(a.occurred_at AS date)  WHERE a.occurred_at >= TIMESTAMP '2025-01-01'    AND a.occurred_at < TIMESTAMP '2026-01-01'    AND a.order_id IS NOT NULL),per_channel AS (  SELECT    channel,    COUNT(DISTINCT order_id) AS attributed_orders,    SUM(attributed_revenue / fx_rate) AS attributed_revenue_eur    SUM(CAST(attributed_revenue / fx_rate AS NUMERIC(18,6))) AS attributed_revenue_eur  FROM attr  GROUP BY channel),totals AS (  SELECT SUM(attributed_revenue_eur) AS total_revenue_eur FROM per_channel)SELECT  pc.channel,  pc.attributed_orders,  ROUND(pc.attributed_revenue_eur, 0) AS attributed_revenue_eur,  ROUND(pc.attributed_revenue_eur / t.total_revenue_eur * 100, 1) AS share_of_revenue_pct,  ROUND(pc.attributed_revenue_eur / pc.attributed_orders, 0) AS avg_order_value_eur  pc.channel AS channel,  pc.attributed_orders AS attributed_orders,  pc.attributed_revenue_eur AS attributed_revenue_eur,  pc.attributed_revenue_eur / t.total_revenue_eur * 100 AS share_of_revenue_pct,  pc.attributed_revenue_eur / pc.attributed_orders AS avg_order_value_eurFROM per_channel pcCROSS JOIN totals tORDER BY attributed_revenue_eur DESC, channel ASCORDER BY attributed_revenue_eur DESC NULLS FIRST, channel ASC NULLS LAST

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

   DuckDB · Hyper · GizmoSQL · CedarDB · pg_duckdb · PostgreSQL · DataFusion · Firebolt · ClickHouse · chDB · clickhouse_brew

   WITH attr AS (
     SELECT
       c.channel AS channel,
       a.order_id AS order_id,
       a.attributed_revenue AS attributed_revenue,
       CASE WHEN a.currency_code = 'EUR' THEN 1 ELSE er.rate END AS fx_rate
     FROM attributions a
     JOIN campaigns c ON c.id = a.campaign_id
     LEFT JOIN exchange_rates er
       ON er.base_code = 'EUR'
       AND er.quote_code = a.currency_code
       AND er.as_of = CAST(a.occurred_at AS date)
     WHERE a.occurred_at >= TIMESTAMP '2025-01-01'
       AND a.occurred_at < TIMESTAMP '2026-01-01'
       AND a.order_id IS NOT NULL
   ),
   per_channel AS (
     SELECT
       channel,
       COUNT(DISTINCT order_id) AS attributed_orders,
       SUM(CAST(attributed_revenue / fx_rate AS NUMERIC(18,6))) AS attributed_revenue_eur
     FROM attr
     GROUP BY channel
   ),
   totals AS (
     SELECT SUM(attributed_revenue_eur) AS total_revenue_eur FROM per_channel
   )
   SELECT
     pc.channel AS channel,
     pc.attributed_orders AS attributed_orders,
     pc.attributed_revenue_eur AS attributed_revenue_eur,
     pc.attributed_revenue_eur / t.total_revenue_eur * 100 AS share_of_revenue_pct,
     pc.attributed_revenue_eur / pc.attributed_orders AS avg_order_value_eur
   FROM per_channel pc
   CROSS JOIN totals t
   ORDER BY attributed_revenue_eur DESC NULLS FIRST, channel ASC NULLS LAST

   • Firebolt{ "errors": [ { "description": "Line 21, Column 33: Operations between decimals with different precision and scale is not supported at the moment. Explicitly cast one of the decimals to other decimal precision and scale." } ], "query": { "query_id": "f6e62335-5e69-4422-a750-805656291d11", "query_lab
   • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table2.attributed_revenue Nullable(Decimal(14, 4)) Nullable(size = 0, Decimal64(size = 0), UInt8(size = 0)), __table2.fx_rate Nullable(Decimal(18, 6)) Nullable(size = 0, Decima

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

7. 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

   Omar's whole session, 7 turns

   • The request, as Omar received it

     Report request from Sofie Nilsen (analyst for the Head of Growth Marketing):
     
     Title: Which marketing channels drive orders and revenue
     Question: Among orders marketing can take credit for, which campaign channels generate the most attributed orders and revenue, and what's the resulting average order value?
     Grain: one row per campaign channel (campaigns.channel)
     Columns:
     - channel - campaigns.channel
     - attributed_orders - count of distinct attributions.order_id where order_id is not null
     - attributed_revenue_eur - sum of attributions.attributed_revenue converted to EUR, rounded to 0 decimals
     - share_of_revenue_pct - attributed_revenue_eur / total attributed_revenue_eur across all channels * 100, rounded to 1 decimal
     - avg_order_value_eur - attributed_revenue_eur / attributed_orders, rounded to 0 decimals
     Details: Source: attributions joined to campaigns on campaign_id to get channel; filter attributions.occurred_at in 2025-01-01..2025-12-31 and order_id is not null (i.e. actual purchase-linked touches, not view-through-only rows). Convert attributed_revenue to EUR using exchange_rates: join on base_code='EUR', quote_code = attributions.currency_code, as_of = the calendar date of occurred_at; amount_eur = attributed_revenue / rate (treat currency_code='EUR' as rate 1). No tenant filter — aggregate across all merchants. Order by attributed_revenue_eur descending, tie-break by channel ascending. Expect well under 50 rows since campaigns.channel is a small code list.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Omar calls dry_run

     WITH attr AS (
       SELECT
         c.channel AS channel,
         a.order_id AS order_id,
         a.attributed_revenue AS attributed_revenue,
         CASE WHEN a.currency_code = 'EUR' THEN 1 ELSE er.rate END AS fx_rate
       FROM attributions a
       JOIN campaigns c ON c.id = a.campaign_id
       LEFT JOIN exchange_rates er
         ON er.base_code = 'EUR'
         AND er.quote_code = a.currency_code
         AND er.as_of = CAST(a.occurred_at AS date)
       WHERE a.occurred_at >= TIMESTAMP '2025-01-01'
         AND a.occurred_at < TIMESTAMP '2026-01-01'
         AND a.order_id IS NOT NULL
     ),
     per_channel AS (
       SELECT
         channel,
         COUNT(DISTINCT order_id) AS attributed_orders,
         SUM(attributed_revenue / fx_rate) AS attributed_revenue_eur
       FROM attr
       GROUP BY channel
     ),
     totals AS (
       SELECT SUM(attributed_revenue_eur) AS total_revenue_eur FROM per_channel
     )
     SELECT
       pc.channel,
       pc.attributed_orders,
       ROUND(pc.attributed_revenue_eur, 0) AS attributed_revenue_eur,
       ROUND(pc.attributed_revenue_eur / t.total_revenue_eur * 100, 1) AS share_of_revenue_pct,
       ROUND(pc.attributed_revenue_eur / pc.attributed_orders, 0) AS avg_order_value_eur
     FROM per_channel pc
     CROSS JOIN totals t
     ORDER BY attributed_revenue_eur DESC, channel ASC

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: channel, attributed_orders, attributed_revenue_eur, share_of_revenue_pct, avg_order_value_eur

   • OmarQuery compiles and matches the requested grain and columns.
   • Omar calls submit

     WITH attr AS (
       SELECT
         c.channel AS channel,
         a.order_id AS order_id,
         a.attributed_revenue AS attributed_revenue,
         CASE WHEN a.currency_code = 'EUR' THEN 1 ELSE er.rate END AS fx_rate
       FROM attributions a
       JOIN campaigns c ON c.id = a.campaign_id
       LEFT JOIN exchange_rates er
         ON er.base_code = 'EUR'
         AND er.quote_code = a.currency_code
         AND er.as_of = CAST(a.occurred_at AS date)
       WHERE a.occurred_at >= TIMESTAMP '2025-01-01'
         AND a.occurred_at < TIMESTAMP '2026-01-01'
         AND a.order_id IS NOT NULL
     ),
     per_channel AS (
       SELECT
         channel,
         COUNT(DISTINCT order_id) AS attributed_orders,
         SUM(attributed_revenue / fx_rate) AS attributed_revenue_eur
       FROM attr
       GROUP BY channel
     ),
     totals AS (
       SELECT SUM(attributed_revenue_eur) AS total_revenue_eur FROM per_channel
     )
     SELECT
       pc.channel,
       pc.attributed_orders,
       ROUND(pc.attributed_revenue_eur, 0) AS attributed_revenue_eur,
       ROUND(pc.attributed_revenue_eur / t.total_revenue_eur * 100, 1) AS share_of_revenue_pct,
       ROUND(pc.attributed_revenue_eur / pc.attributed_orders, 0) AS avg_order_value_eur
     FROM per_channel pc
     CROSS JOIN totals t
     ORDER BY attributed_revenue_eur DESC, channel ASC

   • The warehouse answers

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

   • OmarSubmitted. The report returns one row per campaign channel with attributed orders, EUR-converted attributed revenue, share of total revenue, and average order value for 2025, sourced from `attributions` (purchase-linked rows only) joined to `campaigns`.

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

   Omar's transcript (261 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)