Queries · q265 · Head of Growth Marketing · week 2, night 4
“Last week's channel-revenue report (joined attributions to campaigns) found ~14% order coverage; this week's blind-spot report (just checking attributions.order_id) found ~75-83%. What's driving that gap — is it mostly attribution rows with no campaign_id?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
The manager, at the start of week 2
Subject: Good progress — four things I want nailed down this week
Hi Sofie,
Thanks for this — genuinely useful, and the vertical breakdown on discounting is exactly the kind of "don't trust the pooled number" catch I was worried about. A few things surprised me and I want to go deeper before I take anything to leadership.
What surprised me / needs explaining:
My questions for this week, roughly in priority order:
What I need this for: I'm trying to turn this into a short list of 2-3 concrete recommendations for merchants and for our own roadmap — where to tell them to pull back (discounting), where to double down (referrals), and where we might be quietly burning money (loyalty payouts). I'd rather have fewer, firmer answers than a wider, shakier set.
Thanks again — this is coming together well.
Chloé
The analyst · week 2, night 4, request 1 of 3
Urgent discrepancy to resolve before Friday: last week I told the whole company "only 1 in 7 orders can be traced to a marketing channel" (based on an attributions-to-campaigns join), but tonight's report counted attributed orders a different way (any attributions row at all) and got ~75-83% coverage for 2025 — a 5x gap on a number leadership already saw. I need to find out whether that's because many attribution rows have no campaign_id (meaning there's a broader 'we know the source' population that isn't tied to a paid campaign, e.g. organic/referral touches) before I say anything more about Q4. That's my top slot tonight. Second slot characterizes whatever that gap turns out to be, so I can give Chloé (and correct the record for everyone else) one honest, specific number instead of two conflicting ones. Third slot resolves the loyalty data-quality flag from last night (member_revenue_eur reading as exactly €0 for three small verticals) so the Q2 recommendation isn't sitting on an unverified caveat going into the memo. Referral timing (report 1 tonight) is clear enough to use as-is — 43% of referrers only refer after 6+ orders, so no further query needed there.
The engineer · 1 dry run on the empty schema, then the submission
WITH base AS ( SELECT a.order_id, a.touch_point, a.model, a.attributed_revenue, a.currency_code, a.occurred_at, CASE WHEN a.campaign_id IS NULL THEN 'campaign_id is null' WHEN c.id IS NULL THEN 'campaign_id set but no matching campaigns row' ELSE 'campaign_id set and matches a campaigns row' END AS linkage_category FROM attributions a LEFT JOIN campaigns c ON c.id = a.campaign_id WHERE a.occurred_at >= DATE '2025-01-01' AND a.occurred_at < DATE '2026-01-01' AND a.order_id IS NOT NULL ) SELECT b.linkage_category, COUNT(*) AS attribution_rows_count, COUNT(DISTINCT b.order_id) AS distinct_orders_count, COUNT(DISTINCT b.touch_point) AS distinct_touch_point_values, COUNT(DISTINCT b.model) AS distinct_model_values, ROUND(SUM( CASE WHEN COALESCE(b.currency_code, 'EUR') = 'EUR' THEN b.attributed_revenue ELSE b.attributed_revenue / er.rate END ), 0) AS attributed_revenue_eur FROM base b LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = b.currency_code AND er.as_of = CAST(b.occurred_at AS date) GROUP BY b.linkage_category ORDER BY attribution_rows_count DESC, b.linkage_category ASC
“Query compiles and returns the expected columns. Submitting.”
“Submitted. The report breaks the ~14% vs ~75-83% gap into three linkage categories (null campaign_id, orphaned campaign_id, and properly matched campaign_id), each with row counts, distinct order counts, distinct touch_point/model variety, and EUR revenue — so Sofie can see directly whether the shortfall in last week's channel-revenue report is concentrated in attributions with no campaign_id at all, or in campaign_ids that don't resolve to a campaigns row.”
As Sofie Nilsen saw it, from the small dataset the agents worked on
linkage_category | attribution_rows_count | distinct_orders_count | distinct_touch_point_values | distinct_model_values | attributed_revenue_eur -----------------------------------------+------------------------+-----------------------+-----------------------------+-----------------------+----------------------- campaign_id is null | 16516 | 14247 | 6 | 2 | 6113629.0 campaign_id set and matches a campaigns | 3759 | 3104 | 4 | 2 | 814924.0 (2 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
It builds on:
Changed only where the benchmark needs the same result on every system
WITH base AS ( SELECT a.order_id, a.touch_point, a.model, a.attributed_revenue, a.currency_code, a.occurred_at, a.order_id AS order_id, a.touch_point AS touch_point, a.model AS model, a.attributed_revenue AS attributed_revenue, a.currency_code AS currency_code, a.occurred_at AS occurred_at, CASE WHEN a.campaign_id IS NULL THEN 'campaign_id is null' WHEN c.id IS NULL THEN 'campaign_id set but no matching campaigns row' ELSE 'campaign_id set and matches a campaigns row' END AS linkage_category FROM attributions a LEFT JOIN campaigns c ON c.id = a.campaign_id WHERE a.occurred_at >= DATE '2025-01-01' AND a.occurred_at < DATE '2026-01-01' AND a.order_id IS NOT NULL)SELECT b.linkage_category, b.linkage_category AS linkage_category, COUNT(*) AS attribution_rows_count, COUNT(DISTINCT b.order_id) AS distinct_orders_count, COUNT(DISTINCT b.touch_point) AS distinct_touch_point_values, COUNT(DISTINCT b.model) AS distinct_model_values, ROUND(SUM( CASE WHEN COALESCE(b.currency_code, 'EUR') = 'EUR' THEN b.attributed_revenue ELSE b.attributed_revenue / er.rate END ), 0) AS attributed_revenue_eur SUM(CASEWHEN COALESCE(b.currency_code, 'EUR') = 'EUR' THEN b.attributed_revenueELSE CAST(b.attributed_revenue / er.rate AS NUMERIC(18,6))END) AS attributed_revenue_eurFROM base bLEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = b.currency_code AND er.as_of = CAST(b.occurred_at AS date)GROUP BY b.linkage_categoryORDER BY attribution_rows_count DESC, b.linkage_category ASCORDER BY attribution_rows_count DESC NULLS FIRST, b.linkage_category ASC NULLS LAST
WITH base AS ( SELECT a.order_id AS order_id, a.touch_point AS touch_point, a.model AS model, a.attributed_revenue AS attributed_revenue, a.currency_code AS currency_code, a.occurred_at AS occurred_at, CASE WHEN a.campaign_id IS NULL THEN 'campaign_id is null' WHEN c.id IS NULL THEN 'campaign_id set but no matching campaigns row' ELSE 'campaign_id set and matches a campaigns row' END AS linkage_category FROM attributions a LEFT JOIN campaigns c ON c.id = a.campaign_id WHERE a.occurred_at >= DATE '2025-01-01' AND a.occurred_at < DATE '2026-01-01' AND a.order_id IS NOT NULL ) SELECT b.linkage_category AS linkage_category, COUNT(*) AS attribution_rows_count, COUNT(DISTINCT b.order_id) AS distinct_orders_count, COUNT(DISTINCT b.touch_point) AS distinct_touch_point_values, COUNT(DISTINCT b.model) AS distinct_model_values, SUM( CASE WHEN COALESCE(b.currency_code, 'EUR') = 'EUR' THEN b.attributed_revenue ELSE CAST(b.attributed_revenue / er.rate AS NUMERIC(18,6)) END ) AS attributed_revenue_eur FROM base b LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = b.currency_code AND er.as_of = CAST(b.occurred_at AS date) GROUP BY b.linkage_category ORDER BY attribution_rows_count DESC NULLS FIRST, b.linkage_category ASC NULLS LAST
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 Sofie Nilsen (analyst for the Head of Growth Marketing): Title: Why do last week's and this week's 'attributed orders' counts disagree so much? Question: Last week's channel-revenue report (joined attributions to campaigns) found ~14% order coverage; this week's blind-spot report (just checking attributions.order_id) found ~75-83%. What's driving that gap — is it mostly attribution rows with no campaign_id? Grain: one row per attribution-linkage category Columns: - linkage_category - 'campaign_id is null', 'campaign_id set but no matching campaigns row', or 'campaign_id set and matches a campaigns row' - attribution_rows_count - count of attributions rows with occurred_at in 2025 and order_id not null, in that category - distinct_orders_count - count of distinct order_id in that category - distinct_touch_point_values - count of distinct non-null touch_point values present in that category - distinct_model_values - count of distinct non-null model values present in that category - attributed_revenue_eur - sum of attributed_revenue converted to EUR for that category, rounded to 0 decimals Details: Source: attributions left joined to campaigns on campaign_id. Filter attributions.occurred_at in 2025-01-01..2026-01-01 and order_id is not null, all tenants. Convert attributed_revenue to EUR using exchange_rates (base_code='EUR', quote_code=attributions.currency_code, as_of=occurred_at date, EUR=rate 1). Order by attribution_rows_count descending. Expect exactly 3 rows. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH base AS ( SELECT a.order_id, a.touch_point, a.model, a.attributed_revenue, a.currency_code, a.occurred_at, CASE WHEN a.campaign_id IS NULL THEN 'campaign_id is null' WHEN c.id IS NULL THEN 'campaign_id set but no matching campaigns row' ELSE 'campaign_id set and matches a campaigns row' END AS linkage_category FROM attributions a LEFT JOIN campaigns c ON c.id = a.campaign_id WHERE a.occurred_at >= DATE '2025-01-01' AND a.occurred_at < DATE '2026-01-01' AND a.order_id IS NOT NULL ) SELECT b.linkage_category, COUNT(*) AS attribution_rows_count, COUNT(DISTINCT b.order_id) AS distinct_orders_count, COUNT(DISTINCT b.touch_point) AS distinct_touch_point_values, COUNT(DISTINCT b.model) AS distinct_model_values, ROUND(SUM( CASE WHEN COALESCE(b.currency_code, 'EUR') = 'EUR' THEN b.attributed_revenue ELSE b.attributed_revenue / er.rate END ), 0) AS attributed_revenue_eur FROM base b LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = b.currency_code AND er.as_of = CAST(b.occurred_at AS date) GROUP BY b.linkage_category ORDER BY attribution_rows_count DESC, b.linkage_category ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: linkage_category, attribution_rows_count, distinct_orders_count, distinct_touch_point_values, distinct_model_values, attributed_revenue_eur
submitWITH base AS ( SELECT a.order_id, a.touch_point, a.model, a.attributed_revenue, a.currency_code, a.occurred_at, CASE WHEN a.campaign_id IS NULL THEN 'campaign_id is null' WHEN c.id IS NULL THEN 'campaign_id set but no matching campaigns row' ELSE 'campaign_id set and matches a campaigns row' END AS linkage_category FROM attributions a LEFT JOIN campaigns c ON c.id = a.campaign_id WHERE a.occurred_at >= DATE '2025-01-01' AND a.occurred_at < DATE '2026-01-01' AND a.order_id IS NOT NULL ) SELECT b.linkage_category, COUNT(*) AS attribution_rows_count, COUNT(DISTINCT b.order_id) AS distinct_orders_count, COUNT(DISTINCT b.touch_point) AS distinct_touch_point_values, COUNT(DISTINCT b.model) AS distinct_model_values, ROUND(SUM( CASE WHEN COALESCE(b.currency_code, 'EUR') = 'EUR' THEN b.attributed_revenue ELSE b.attributed_revenue / er.rate END ), 0) AS attributed_revenue_eur FROM base b LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = b.currency_code AND er.as_of = CAST(b.occurred_at AS date) GROUP BY b.linkage_category ORDER BY attribution_rows_count DESC, b.linkage_category ASC
Submitted to tonight's batch. Your work on this request is done.