Queries · q266 · Head of Growth Marketing · week 2, night 4

What do the no-campaign attribution touches actually represent?

“For the attribution rows with no linked campaign, what do their touch_point values look like — is this organic/referral/direct traffic we can legitimately call 'known source', or something else?”

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

   • Aisha's team flagged that loyalty members "cost" nearly a fifth of their own revenue in redeemed points, plus a large pile of points issued but not yet redeemed sitting as a liability. That's a very different lens from AOV — a program can look flat on AOV and still be expensive. I want these two views reconciled, not treated as separate facts.
   • Aisha also walked back the "loyalty members return more" claim once category mix was accounted for. That's a good catch, but it makes me nervous about the apparel-vs-electronics AOV comparison in your memo — is that difference also just a category mix artifact, or is it real?
   • Referrals look like our best-performing lever by far, yet reach is under 1% of customers. That gap between "strong where it exists" and "barely exists" feels like the single biggest opportunity I've seen all week.

   My questions for this week, roughly in priority order:

   1. Does discounting actually cost us margin, and in which verticals is it worst? This was already your next step — please keep it top of the list. I need a real cost picture (not just return rates) before I tell merchants to rein in sitewide sales.
   2. Is the loyalty programme actually worth running as-is? I want one clear picture that weighs what members cost us (redeemed and owed points) against what they're worth (spend, frequency, retention), controlling for the category mix issue Aisha found. I need to decide whether we fix it, shrink it, or let it be.
   3. What would it take to grow referrals, and who's a good referrer? Given how strong but tiny this program is, I want to understand what referring customers look like (so we can find more of them) and whether growth here looks more promising than fixing loyalty.
   4. Of the orders we can't currently link to a channel, is there any pattern at all (timing, merchant type, repeat vs new customer) that hints at what's driving them? I don't expect a clean answer — I just want to know if this blind spot is worth investing in closing, or if it's simply untrackable and I should stop asking.

   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é

   Show the whole note

2. Sofie Nilsen plans the night

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

   Read the whole plan
   What do the no-campaign attribution touches actually represent?

   Question

   For the attribution rows with no linked campaign, what do their touch_point values look like — is this organic/referral/direct traffic we can legitimately call 'known source', or something else?

   One row per

   touch_point value

   Columns

   • touch_point - attributions.touch_point, coalesced to '(none)' if null
   • distinct_orders_count - count of distinct order_id with occurred_at in 2025, order_id not null, campaign_id is null, for that touch_point value
   • share_of_no_campaign_orders_pct - distinct_orders_count / total distinct orders in the no-campaign-id population * 100, rounded to 1 decimal
   • attributed_revenue_eur - sum of attributed_revenue converted to EUR for that touch_point, rounded to 0 decimals
   • avg_weight - average of the weight column for these rows, rounded to 2 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 base AS (
     SELECT
       COALESCE(a.touch_point, '(none)') AS touch_point,
       a.order_id,
       a.attributed_revenue,
       a.currency_code,
       a.weight,
       a.occurred_at
     FROM attributions a
     WHERE a.campaign_id IS NULL
       AND a.order_id IS NOT NULL
       AND a.occurred_at >= DATE '2025-01-01'
       AND a.occurred_at < DATE '2026-01-01'
   ),
   converted AS (
     SELECT
       b.touch_point,
       b.order_id,
       b.weight,
       CASE WHEN b.currency_code = 'EUR' THEN b.attributed_revenue
            ELSE b.attributed_revenue / er.rate END AS 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)
   ),
   totals AS (
     SELECT COUNT(DISTINCT order_id) AS total_orders FROM base
   )
   SELECT
     c.touch_point,
     COUNT(DISTINCT c.order_id) AS distinct_orders_count,
     ROUND(COUNT(DISTINCT c.order_id) * 100.0 / t.total_orders, 1) AS share_of_no_campaign_orders_pct,
     ROUND(SUM(c.revenue_eur), 0) AS attributed_revenue_eur,
     ROUND(AVG(c.weight), 2) AS avg_weight
   FROM converted c
   CROSS JOIN totals t
   GROUP BY c.touch_point, t.total_orders
   ORDER BY distinct_orders_count DESC, touch_point ASC

   “Query compiles cleanly. 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 groups 2025 no-campaign attribution rows (with an order) by touch_point, showing distinct order counts, their share of the no-campaign population, EUR-converted attributed revenue, and average weight — ordered by order volume descending — which should let Sofie see directly whether the no-campaign bucket is dominated by organic/direct/referral touch points or something messier.”

4. The report comes back overnight

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

   touch_point    | distinct_orders_count | share_of_no_campaign_orders_pct | attributed_revenue_eur | avg_weight
   ---------------+-----------------------+---------------------------------+------------------------+-----------
   organic_search |                  6166 |                            43.3 |              2575380.0 |        1.0
   direct         |                  5475 |                            38.4 |              2519113.0 |        1.0
   social         |                  1696 |                            11.9 |               443992.0 |        1.0
   referral       |                  1218 |                             8.5 |               573766.0 |        1.0
   paid_social    |                     9 |                             0.1 |                 1368.0 |        1.0
   paid_search    |                     1 |                             0.0 |                   11.0 |        1.0
   (6 rows)

5. What came of it

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

   It builds on:

   • q265Why do last week's and this week's 'attributed orders' counts disagree so much?

6. It becomes benchmark query q266

   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 (9×)
   • 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 base AS (  SELECT    COALESCE(a.touch_point, '(none)') AS touch_point,    a.order_id,    a.attributed_revenue,    a.currency_code,    a.weight,    a.occurred_at    a.order_id AS order_id,    a.attributed_revenue AS attributed_revenue,    a.currency_code AS currency_code,    a.weight AS weight,    a.occurred_at AS occurred_at  FROM attributions a  WHERE a.campaign_id IS NULL    AND a.order_id IS NOT NULL    AND a.occurred_at >= DATE '2025-01-01'    AND a.occurred_at < DATE '2026-01-01'),converted AS (  SELECT    b.touch_point,    b.order_id,    b.weight,    b.touch_point AS touch_point,    b.order_id AS order_id,    b.weight AS weight,    CASE WHEN b.currency_code = 'EUR' THEN b.attributed_revenue         ELSE b.attributed_revenue / er.rate END AS revenue_eur         ELSE CAST(b.attributed_revenue / er.rate AS NUMERIC(18,6)) END AS 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)),totals AS (  SELECT COUNT(DISTINCT order_id) AS total_orders FROM base)SELECT  c.touch_point,  c.touch_point AS touch_point,  COUNT(DISTINCT c.order_id) AS distinct_orders_count,  ROUND(COUNT(DISTINCT c.order_id) * 100.0 / t.total_orders, 1) AS share_of_no_campaign_orders_pct,  ROUND(SUM(c.revenue_eur), 0) AS attributed_revenue_eur,  ROUND(AVG(c.weight), 2) AS avg_weight  COUNT(DISTINCT c.order_id) * 100.0 / t.total_orders AS share_of_no_campaign_orders_pct,  SUM(c.revenue_eur) AS attributed_revenue_eur,  AVG(c.weight) AS avg_weightFROM converted cCROSS JOIN totals tGROUP BY c.touch_point, t.total_ordersORDER BY distinct_orders_count DESC, touch_point ASCORDER BY distinct_orders_count DESC NULLS FIRST, touch_point 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 base AS (
     SELECT
       COALESCE(a.touch_point, '(none)') AS touch_point,
       a.order_id AS order_id,
       a.attributed_revenue AS attributed_revenue,
       a.currency_code AS currency_code,
       a.weight AS weight,
       a.occurred_at AS occurred_at
     FROM attributions a
     WHERE a.campaign_id IS NULL
       AND a.order_id IS NOT NULL
       AND a.occurred_at >= DATE '2025-01-01'
       AND a.occurred_at < DATE '2026-01-01'
   ),
   converted AS (
     SELECT
       b.touch_point AS touch_point,
       b.order_id AS order_id,
       b.weight AS weight,
       CASE WHEN b.currency_code = 'EUR' THEN b.attributed_revenue
            ELSE CAST(b.attributed_revenue / er.rate AS NUMERIC(18,6)) END AS 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)
   ),
   totals AS (
     SELECT COUNT(DISTINCT order_id) AS total_orders FROM base
   )
   SELECT
     c.touch_point AS touch_point,
     COUNT(DISTINCT c.order_id) AS distinct_orders_count,
     COUNT(DISTINCT c.order_id) * 100.0 / t.total_orders AS share_of_no_campaign_orders_pct,
     SUM(c.revenue_eur) AS attributed_revenue_eur,
     AVG(c.weight) AS avg_weight
   FROM converted c
   CROSS JOIN totals t
   GROUP BY c.touch_point, t.total_orders
   ORDER BY distinct_orders_count DESC NULLS FIRST, touch_point ASC NULLS LAST

   • Firebolt{ "errors": [ { "description": "Line 21, Column 41: 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": "f8e405e5-c508-4452-ad03-f533185d8c38", "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)), __table4.rate Nullable(Decimal(12, 6)) Nullable(size = 0, Decimal64

   Scale factor 1000, c8a.24xlarge, after the last load. q266 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: What do the no-campaign attribution touches actually represent?
     Question: For the attribution rows with no linked campaign, what do their touch_point values look like — is this organic/referral/direct traffic we can legitimately call 'known source', or something else?
     Grain: one row per touch_point value
     Columns:
     - touch_point - attributions.touch_point, coalesced to '(none)' if null
     - distinct_orders_count - count of distinct order_id with occurred_at in 2025, order_id not null, campaign_id is null, for that touch_point value
     - share_of_no_campaign_orders_pct - distinct_orders_count / total distinct orders in the no-campaign-id population * 100, rounded to 1 decimal
     - attributed_revenue_eur - sum of attributed_revenue converted to EUR for that touch_point, rounded to 0 decimals
     - avg_weight - average of the weight column for these rows, rounded to 2 decimals
     Details: Source: attributions where campaign_id is null, occurred_at in 2025, order_id is not null, all tenants. Convert attributed_revenue to EUR using exchange_rates as of occurred_at date (EUR=rate 1). Order by distinct_orders_count descending, tie-break touch_point ascending. This directly follows up on tonight's linkage-category report to explain the largest bucket in plain terms.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Omar calls dry_run

     WITH base AS (
       SELECT
         COALESCE(a.touch_point, '(none)') AS touch_point,
         a.order_id,
         a.attributed_revenue,
         a.currency_code,
         a.weight,
         a.occurred_at
       FROM attributions a
       WHERE a.campaign_id IS NULL
         AND a.order_id IS NOT NULL
         AND a.occurred_at >= DATE '2025-01-01'
         AND a.occurred_at < DATE '2026-01-01'
     ),
     converted AS (
       SELECT
         b.touch_point,
         b.order_id,
         b.weight,
         CASE WHEN b.currency_code = 'EUR' THEN b.attributed_revenue
              ELSE b.attributed_revenue / er.rate END AS 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)
     ),
     totals AS (
       SELECT COUNT(DISTINCT order_id) AS total_orders FROM base
     )
     SELECT
       c.touch_point,
       COUNT(DISTINCT c.order_id) AS distinct_orders_count,
       ROUND(COUNT(DISTINCT c.order_id) * 100.0 / t.total_orders, 1) AS share_of_no_campaign_orders_pct,
       ROUND(SUM(c.revenue_eur), 0) AS attributed_revenue_eur,
       ROUND(AVG(c.weight), 2) AS avg_weight
     FROM converted c
     CROSS JOIN totals t
     GROUP BY c.touch_point, t.total_orders
     ORDER BY distinct_orders_count DESC, touch_point ASC

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: touch_point, distinct_orders_count, share_of_no_campaign_orders_pct, attributed_revenue_eur, avg_weight

   • OmarQuery compiles cleanly. Submitting.
   • Omar calls submit

     WITH base AS (
       SELECT
         COALESCE(a.touch_point, '(none)') AS touch_point,
         a.order_id,
         a.attributed_revenue,
         a.currency_code,
         a.weight,
         a.occurred_at
       FROM attributions a
       WHERE a.campaign_id IS NULL
         AND a.order_id IS NOT NULL
         AND a.occurred_at >= DATE '2025-01-01'
         AND a.occurred_at < DATE '2026-01-01'
     ),
     converted AS (
       SELECT
         b.touch_point,
         b.order_id,
         b.weight,
         CASE WHEN b.currency_code = 'EUR' THEN b.attributed_revenue
              ELSE b.attributed_revenue / er.rate END AS 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)
     ),
     totals AS (
       SELECT COUNT(DISTINCT order_id) AS total_orders FROM base
     )
     SELECT
       c.touch_point,
       COUNT(DISTINCT c.order_id) AS distinct_orders_count,
       ROUND(COUNT(DISTINCT c.order_id) * 100.0 / t.total_orders, 1) AS share_of_no_campaign_orders_pct,
       ROUND(SUM(c.revenue_eur), 0) AS attributed_revenue_eur,
       ROUND(AVG(c.weight), 2) AS avg_weight
     FROM converted c
     CROSS JOIN totals t
     GROUP BY c.touch_point, t.total_orders
     ORDER BY distinct_orders_count DESC, touch_point ASC

   • The warehouse answers

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

   • OmarSubmitted. The report groups 2025 no-campaign attribution rows (with an order) by `touch_point`, showing distinct order counts, their share of the no-campaign population, EUR-converted attributed revenue, and average weight — ordered by order volume descending — which should let Sofie see directly whether the no-campaign bucket is dominated by organic/direct/referral touch points or something messier.

   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 (263 KB) · Sofie Nilsen's transcript (793 KB) · Chloé Marchetti's transcript (263 KB) · the night's plan (6 KB) · the night's reports (5 KB) · the week's memo (3 KB)