Queries · q263 · Head of Growth Marketing · week 2, night 3

Does the unattributed-orders blind spot differ by vertical or by new-vs-repeat customers?

“Among orders with no linked marketing attribution, is there a pattern by vertical or by whether the buyer is a new or repeat customer that would tell us if this blind spot is worth chasing?”

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 3, request 2 of 3

   Loyalty picture is now reconcilable: Apparel carries the lion's share of both the redemption cost (€40k, 23.7% of member revenue) and the outstanding liability (€192k of ~€354k total), yet its repeat-purchase rate is flat between members and non-members (58.5% vs 59.6%) — expensive with no retention payoff, the clearest 'fix or shrink' case. Electronics and Home & Living cost far less but show a real repeat-rate lift for members (+27pp, +13pp) — worth leaving alone. (Caveat: member_revenue_eur reads as exactly €0 for Books, Food & Coffee, Pet Supplies and Jewelry despite those segments having real members and orders in the companion report — looks like a currency-conversion gap for a few small tenants; doesn't change the Apparel/Sports-driven headline since those two verticals hold ~85% of the cost, but I'm flagging it as unverified rather than re-querying tonight.) The referral report found something clean and robust: referrers order 1.5-2.4x more often than non-referrers in every single vertical, even where their average order value is actually lower — frequency, not basket size, is the referrer signature. Tonight I want to find the lifecycle trigger point (how many orders in before someone typically refers) to make the 'how do we get more of them' question concrete, then move to Chloé's lowest-priority but still-requested Q4 (the unattributed-orders blind spot) since this is likely my last real chance before Friday.

   Read the whole plan
   Does the unattributed-orders blind spot differ by vertical or by new-vs-repeat customers?

   Question

   Among orders with no linked marketing attribution, is there a pattern by vertical or by whether the buyer is a new or repeat customer that would tell us if this blind spot is worth chasing?

   One row per

   industry and attribution status ('attributed' vs 'unattributed')

   Columns

   • industry - tenants.industry
   • attribution_status - 'attributed' if the order's id appears in attributions.order_id, else 'unattributed'
   • orders_count - count of orders placed_at in 2025 in that industry/status
   • share_of_industry_orders_pct - orders_count / total 2025 orders for that industry * 100, rounded to 1 decimal
   • new_customer_order_pct - share of orders in that cell that are the buyer's first order ever (placed_at equal to the minimum placed_at across all of that user's orders in the full dataset, not just 2025), *100, rounded to 1 decimal
   • avg_order_value_eur - average grand_total (EUR) of orders in that cell, rounded to 0 decimals

3. Felix 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
     industry,
     attribution_status,
     orders_count,
     share_of_industry_orders_pct,
     new_customer_order_pct,
     avg_order_value_eur
   FROM (
     SELECT
       industry,
       attribution_status,
       COUNT(*) AS orders_count,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY industry), 1) AS share_of_industry_orders_pct,
       ROUND(100.0 * SUM(is_new) / COUNT(*), 1) AS new_customer_order_pct,
       ROUND(AVG(amount_eur), 0) AS avg_order_value_eur
     FROM (
       SELECT
         t.industry AS industry,
         CASE WHEN EXISTS (
           SELECT 1 FROM attributions a WHERE a.order_id = o.id
         ) THEN 'attributed' ELSE 'unattributed' END AS attribution_status,
         CASE WHEN o.placed_at = (
           SELECT MIN(o3.placed_at)
           FROM orders o3
           WHERE o3.tenant_id = o.tenant_id
             AND o3.user_id = o.user_id
             AND o3.placed_at IS NOT NULL
         ) THEN 1 ELSE 0 END AS is_new,
         CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
              ELSE o.grand_total / (
                SELECT er.rate FROM exchange_rates er
                WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code
                  AND er.as_of = CAST(o.placed_at AS DATE)
              )
         END AS amount_eur
       FROM orders o
       JOIN tenants t ON t.id = o.tenant_id
       WHERE o.placed_at >= TIMESTAMP '2025-01-01'
         AND o.placed_at < TIMESTAMP '2026-01-01'
     ) base
     GROUP BY industry, attribution_status
   ) agg
   ORDER BY industry ASC, attribution_status ASC

   “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 groups 2025 orders by tenant industry and attribution status (via an EXISTS check against attributions.order_id), showing order counts, each status's share within its industry, the percentage of orders that were the buyer's first-ever order (based on minimum placed_at per tenant-scoped user), and average order value converted to EUR using the daily exchange rate as of each order's placed_at date. One caveat flagged to the analyst: industry is a free-text field on tenants rather than a fixed enum, so the actual row count/labels may not perfectly match the "9-10 verticals" expectation if tenants have entered it inconsistently.”

4. The report comes back overnight

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

   industry                  | attribution_status | orders_count | share_of_industry_orders_pct | new_customer_order_pct | avg_order_value_eur
   --------------------------+--------------------+--------------+------------------------------+------------------------+--------------------
   Apparel & Footwear        | attributed         |         6968 |                         97.1 |                   22.0 |               184.0
   Apparel & Footwear        | unattributed       |          207 |                          2.9 |                   21.3 |               181.0
   Beauty                    | attributed         |         1424 |                         90.9 |                   19.9 |               103.0
   Beauty                    | unattributed       |          142 |                          9.1 |                    7.0 |                32.0
   Books & Stationery        | attributed         |          152 |                         95.6 |                   17.1 |                53.0
   Books & Stationery        | unattributed       |            7 |                          4.4 |                   14.3 |                29.0
   Consumer Electronics      | attributed         |          562 |                         97.6 |                   29.5 |              1026.0
   Consumer Electronics      | unattributed       |           14 |                          2.4 |                   28.6 |              1942.0
   Food & Coffee             | attributed         |         3151 |                         43.6 |                    9.1 |                67.0
   Food & Coffee             | unattributed       |         4082 |                         56.4 |                    1.5 |                 1.0
   Home & Living             | attributed         |         1617 |                         97.2 |                   24.1 |               149.0
   Home & Living             | unattributed       |           46 |                          2.8 |                   17.4 |               153.0
   Industrial Supplies (B2B) | attributed         |          768 |                         99.2 |                    8.5 |              1889.0
   Industrial Supplies (B2B) | unattributed       |            6 |                          0.8 |                    0.0 |              2253.0
   Jewelry & Accessories     | attributed         |           49 |                         98.0 |                   36.7 |               216.0
   Jewelry & Accessories     | unattributed       |            1 |                          2.0 |                  100.0 |               286.0
   Office Supplies (B2B)     | attributed         |          522 |                         99.1 |                    8.0 |              3050.0
   Office Supplies (B2B)     | unattributed       |            5 |                          0.9 |                    0.0 |               643.0
   Pet Supplies              | attributed         |          822 |                         56.6 |                   12.9 |                63.0
   Pet Supplies              | unattributed       |          631 |                         43.4 |                    2.5 |                 3.0
   Sports & Outdoor          | attributed         |          992 |                         97.5 |                   26.3 |               368.0
   Sports & Outdoor          | unattributed       |           25 |                          2.5 |                   24.0 |               288.0
   (22 rows)

5. What came of it

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

   It builds on:

   • q050Which marketing channels drive orders and revenue

6. It becomes benchmark query q263

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

   • 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 (2×)
   The benchmark text, against the submitted SQL

   SELECT  industry,  attribution_status,  orders_count,  share_of_industry_orders_pct,  new_customer_order_pct,  avg_order_value_eurFROM (  SELECT    industry,    attribution_status,    COUNT(*) AS orders_count,    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY industry), 1) AS share_of_industry_orders_pct,    ROUND(100.0 * SUM(is_new) / COUNT(*), 1) AS new_customer_order_pct,    ROUND(AVG(amount_eur), 0) AS avg_order_value_eur    COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY industry) AS share_of_industry_orders_pct,    100.0 * SUM(is_new) / COUNT(*) AS new_customer_order_pct,    AVG(amount_eur) AS avg_order_value_eur  FROM (    SELECT      t.industry AS industry,      CASE WHEN EXISTS (        SELECT 1 FROM attributions a WHERE a.order_id = o.id      ) THEN 'attributed' ELSE 'unattributed' END AS attribution_status,      CASE WHEN o.placed_at = (        SELECT MIN(o3.placed_at)        FROM orders o3        WHERE o3.tenant_id = o.tenant_id          AND o3.user_id = o.user_id          AND o3.placed_at IS NOT NULL      ) THEN 1 ELSE 0 END AS is_new,      CASE WHEN o.currency_code = 'EUR' THEN o.grand_total           ELSE o.grand_total / (             SELECT er.rate FROM exchange_rates er             WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code               AND er.as_of = CAST(o.placed_at AS DATE)           )      END AS amount_eur    FROM orders o    JOIN tenants t ON t.id = o.tenant_id    WHERE o.placed_at >= TIMESTAMP '2025-01-01'      AND o.placed_at < TIMESTAMP '2026-01-01'  ) base  GROUP BY industry, attribution_status) aggORDER BY industry ASC, attribution_status ASCORDER BY industry ASC NULLS LAST, attribution_status 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

   SELECT
     industry,
     attribution_status,
     orders_count,
     share_of_industry_orders_pct,
     new_customer_order_pct,
     avg_order_value_eur
   FROM (
     SELECT
       industry,
       attribution_status,
       COUNT(*) AS orders_count,
       COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY industry) AS share_of_industry_orders_pct,
       100.0 * SUM(is_new) / COUNT(*) AS new_customer_order_pct,
       AVG(amount_eur) AS avg_order_value_eur
     FROM (
       SELECT
         t.industry AS industry,
         CASE WHEN EXISTS (
           SELECT 1 FROM attributions a WHERE a.order_id = o.id
         ) THEN 'attributed' ELSE 'unattributed' END AS attribution_status,
         CASE WHEN o.placed_at = (
           SELECT MIN(o3.placed_at)
           FROM orders o3
           WHERE o3.tenant_id = o.tenant_id
             AND o3.user_id = o.user_id
             AND o3.placed_at IS NOT NULL
         ) THEN 1 ELSE 0 END AS is_new,
         CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
              ELSE o.grand_total / (
                SELECT er.rate FROM exchange_rates er
                WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code
                  AND er.as_of = CAST(o.placed_at AS DATE)
              )
         END AS amount_eur
       FROM orders o
       JOIN tenants t ON t.id = o.tenant_id
       WHERE o.placed_at >= TIMESTAMP '2025-01-01'
         AND o.placed_at < TIMESTAMP '2026-01-01'
     ) base
     GROUP BY industry, attribution_status
   ) agg
   ORDER BY industry ASC NULLS LAST, attribution_status ASC NULLS LAST

   • Firebolt{ "errors": [ { "description": "Line 30, Column 31: 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": "9f62ff4a-89cf-40b8-b643-8795ab489341", "query_lab
   • DataFusionError during planning: Correlated scalar subquery must be aggregated to return at most one row
   • ClickHouseCode: 48. DB::Exception: Current query is not supported yet, because can't find correlated column '__table10.id' in current header: __table10.placed_at, __table10.currency_code, __table10.grand_total, __table11.industry. (NOT_IMPLEMENTED)

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

   Felix's whole session, 7 turns

   • The request, as Felix received it

     Report request from Sofie Nilsen (analyst for the Head of Growth Marketing):
     
     Title: Does the unattributed-orders blind spot differ by vertical or by new-vs-repeat customers?
     Question: Among orders with no linked marketing attribution, is there a pattern by vertical or by whether the buyer is a new or repeat customer that would tell us if this blind spot is worth chasing?
     Grain: one row per industry and attribution status ('attributed' vs 'unattributed')
     Columns:
     - industry - tenants.industry
     - attribution_status - 'attributed' if the order's id appears in attributions.order_id, else 'unattributed'
     - orders_count - count of orders placed_at in 2025 in that industry/status
     - share_of_industry_orders_pct - orders_count / total 2025 orders for that industry * 100, rounded to 1 decimal
     - new_customer_order_pct - share of orders in that cell that are the buyer's first order ever (placed_at equal to the minimum placed_at across all of that user's orders in the full dataset, not just 2025), *100, rounded to 1 decimal
     - avg_order_value_eur - average grand_total (EUR) of orders in that cell, rounded to 0 decimals
     Details: Source: orders joined to tenants (industry) and left-joined to attributions (match on order_id, any row = attributed) for 2025 orders, all tenants. 'First order ever' is computed per user_id (scoped to tenant since users are tenant-scoped) across their full order history including pre-2025 orders, to correctly flag new vs repeat. Convert grand_total to EUR using exchange_rates as of placed_at (EUR=rate 1). Order by industry ascending, then attribution_status ascending ('attributed' before 'unattributed'). Expect roughly 9-10 industries x 2 rows.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Felix calls dry_run

     SELECT
       industry,
       attribution_status,
       orders_count,
       share_of_industry_orders_pct,
       new_customer_order_pct,
       avg_order_value_eur
     FROM (
       SELECT
         industry,
         attribution_status,
         COUNT(*) AS orders_count,
         ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY industry), 1) AS share_of_industry_orders_pct,
         ROUND(100.0 * SUM(is_new) / COUNT(*), 1) AS new_customer_order_pct,
         ROUND(AVG(amount_eur), 0) AS avg_order_value_eur
       FROM (
         SELECT
           t.industry AS industry,
           CASE WHEN EXISTS (
             SELECT 1 FROM attributions a WHERE a.order_id = o.id
           ) THEN 'attributed' ELSE 'unattributed' END AS attribution_status,
           CASE WHEN o.placed_at = (
             SELECT MIN(o3.placed_at)
             FROM orders o3
             WHERE o3.tenant_id = o.tenant_id
               AND o3.user_id = o.user_id
               AND o3.placed_at IS NOT NULL
           ) THEN 1 ELSE 0 END AS is_new,
           CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
                ELSE o.grand_total / (
                  SELECT er.rate FROM exchange_rates er
                  WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code
                    AND er.as_of = CAST(o.placed_at AS DATE)
                )
           END AS amount_eur
         FROM orders o
         JOIN tenants t ON t.id = o.tenant_id
         WHERE o.placed_at >= TIMESTAMP '2025-01-01'
           AND o.placed_at < TIMESTAMP '2026-01-01'
       ) base
       GROUP BY industry, attribution_status
     ) agg
     ORDER BY industry ASC, attribution_status ASC

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: industry, attribution_status, orders_count, share_of_industry_orders_pct, new_customer_order_pct, avg_order_value_eur

   • FelixQuery compiles and returns the expected columns. Submitting.
   • Felix calls submit

     SELECT
       industry,
       attribution_status,
       orders_count,
       share_of_industry_orders_pct,
       new_customer_order_pct,
       avg_order_value_eur
     FROM (
       SELECT
         industry,
         attribution_status,
         COUNT(*) AS orders_count,
         ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY industry), 1) AS share_of_industry_orders_pct,
         ROUND(100.0 * SUM(is_new) / COUNT(*), 1) AS new_customer_order_pct,
         ROUND(AVG(amount_eur), 0) AS avg_order_value_eur
       FROM (
         SELECT
           t.industry AS industry,
           CASE WHEN EXISTS (
             SELECT 1 FROM attributions a WHERE a.order_id = o.id
           ) THEN 'attributed' ELSE 'unattributed' END AS attribution_status,
           CASE WHEN o.placed_at = (
             SELECT MIN(o3.placed_at)
             FROM orders o3
             WHERE o3.tenant_id = o.tenant_id
               AND o3.user_id = o.user_id
               AND o3.placed_at IS NOT NULL
           ) THEN 1 ELSE 0 END AS is_new,
           CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
                ELSE o.grand_total / (
                  SELECT er.rate FROM exchange_rates er
                  WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code
                    AND er.as_of = CAST(o.placed_at AS DATE)
                )
           END AS amount_eur
         FROM orders o
         JOIN tenants t ON t.id = o.tenant_id
         WHERE o.placed_at >= TIMESTAMP '2025-01-01'
           AND o.placed_at < TIMESTAMP '2026-01-01'
       ) base
       GROUP BY industry, attribution_status
     ) agg
     ORDER BY industry ASC, attribution_status ASC

   • The warehouse answers

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

   • FelixSubmitted. The report groups 2025 orders by tenant industry and attribution status (via an EXISTS check against `attributions.order_id`), showing order counts, each status's share within its industry, the percentage of orders that were the buyer's first-ever order (based on minimum placed_at per tenant-scoped user), and average order value converted to EUR using the daily exchange rate as of each order's placed_at date. One caveat flagged to the analyst: `industry` is a free-text field on `tenants` rather than a fixed enum, so the actual row count/labels may not perfectly match the "9-10 verticals" expectation if tenants have entered it inconsistently.

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

   Felix's transcript (271 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)