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

At what point in their order history do customers start referring?

“Is there a typical order-count threshold after which a customer becomes a referrer, which would tell us when to prompt the referral ask?”

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 1 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
   At what point in their order history do customers start referring?

   Question

   Is there a typical order-count threshold after which a customer becomes a referrer, which would tell us when to prompt the referral ask?

   One row per

   bucket of orders-placed-before-first-referral

   Columns

   • orders_before_first_referral_bucket - '0 (referred before ordering)', '1', '2', '3', '4-5', or '6+'
   • referrer_count - count of distinct (tenant_id, referrer_user_id) pairs falling in that bucket
   • share_of_referrers_pct - referrer_count / total referrers across all buckets * 100, rounded to 1 decimal
   • avg_total_2025_orders - average of each referrer's full count of 2025 orders (not just pre-referral), rounded to 2 decimals

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

   WITH first_referral AS (
     SELECT tenant_id, referrer_user_id, MIN(created_at) AS first_referral_at
     FROM referrals
     GROUP BY tenant_id, referrer_user_id
   ),
   referrer_orders AS (
     SELECT
       fr.tenant_id,
       fr.referrer_user_id,
       COUNT(CASE WHEN o.placed_at < fr.first_referral_at THEN 1 END) AS orders_before,
       COUNT(CASE WHEN o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' THEN 1 END) AS orders_2025
     FROM first_referral fr
     LEFT JOIN orders o
       ON o.tenant_id = fr.tenant_id AND o.user_id = fr.referrer_user_id
     GROUP BY fr.tenant_id, fr.referrer_user_id, fr.first_referral_at
   )
   SELECT
     bucket_label AS orders_before_first_referral_bucket,
     COUNT(*) AS referrer_count,
     ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS share_of_referrers_pct,
     ROUND(AVG(orders_2025), 2) AS avg_total_2025_orders
   FROM (
     SELECT
       ro.orders_before,
       ro.orders_2025,
       CASE
         WHEN ro.orders_before = 0 THEN 0
         WHEN ro.orders_before = 1 THEN 1
         WHEN ro.orders_before = 2 THEN 2
         WHEN ro.orders_before = 3 THEN 3
         WHEN ro.orders_before BETWEEN 4 AND 5 THEN 4
         ELSE 6
       END AS sort_key,
       CASE
         WHEN ro.orders_before = 0 THEN '0 (referred before ordering)'
         WHEN ro.orders_before = 1 THEN '1'
         WHEN ro.orders_before = 2 THEN '2'
         WHEN ro.orders_before = 3 THEN '3'
         WHEN ro.orders_before BETWEEN 4 AND 5 THEN '4-5'
         ELSE '6+'
       END AS bucket_label
     FROM referrer_orders ro
   ) b
   GROUP BY bucket_label, sort_key
   ORDER BY sort_key

   “Query compiles and returns exactly the requested 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 buckets each distinct (tenant_id, referrer_user_id) referrer by how many orders they had placed before their first referral (earliest referrals.created_at), with the fixed 6-bucket sequence, share-of-total percentage, and their average full-year-2025 order count for context on eventual purchase frequency.”

4. The report comes back overnight

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

   orders_before_first_referral_bucket | referrer_count | share_of_referrers_pct | avg_total_2025_orders
   ------------------------------------+----------------+------------------------+----------------------
   1                                   |             97 |                   17.5 |                  2.57
   2                                   |             77 |                   13.9 |                  3.14
   3                                   |             55 |                    9.9 |                  3.27
   4-5                                 |             86 |                   15.5 |                  4.27
   6+                                  |            239 |                   43.1 |                  9.78
   (5 rows)

5. What came of it

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

   It builds on:

   • q059Do referred or referring shoppers spend differently than everyone else?
   • q261Referral reach and value by vertical

6. It becomes benchmark query q262

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

   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
   • made explicita selected t.col gets AS col, its standard name made explicit (4×)
   • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results
   The benchmark text, against the submitted SQL

   WITH first_referral AS (  SELECT tenant_id, referrer_user_id, MIN(created_at) AS first_referral_at  FROM referrals  GROUP BY tenant_id, referrer_user_id),referrer_orders AS (  SELECT    fr.tenant_id,    fr.referrer_user_id,    fr.tenant_id AS tenant_id,    fr.referrer_user_id AS referrer_user_id,    COUNT(CASE WHEN o.placed_at < fr.first_referral_at THEN 1 END) AS orders_before,    COUNT(CASE WHEN o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' THEN 1 END) AS orders_2025  FROM first_referral fr  LEFT JOIN orders o    ON o.tenant_id = fr.tenant_id AND o.user_id = fr.referrer_user_id  GROUP BY fr.tenant_id, fr.referrer_user_id, fr.first_referral_at)SELECT  bucket_label AS orders_before_first_referral_bucket,  COUNT(*) AS referrer_count,  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS share_of_referrers_pct,  ROUND(AVG(orders_2025), 2) AS avg_total_2025_orders  COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS share_of_referrers_pct,  AVG(CAST(orders_2025 AS NUMERIC(18,6))) AS avg_total_2025_ordersFROM (  SELECT    ro.orders_before,    ro.orders_2025,    ro.orders_before AS orders_before,    ro.orders_2025 AS orders_2025,    CASE      WHEN ro.orders_before = 0 THEN 0      WHEN ro.orders_before = 1 THEN 1      WHEN ro.orders_before = 2 THEN 2      WHEN ro.orders_before = 3 THEN 3      WHEN ro.orders_before BETWEEN 4 AND 5 THEN 4      ELSE 6    END AS sort_key,    CASE      WHEN ro.orders_before = 0 THEN '0 (referred before ordering)'      WHEN ro.orders_before = 1 THEN '1'      WHEN ro.orders_before = 2 THEN '2'      WHEN ro.orders_before = 3 THEN '3'      WHEN ro.orders_before BETWEEN 4 AND 5 THEN '4-5'      ELSE '6+'    END AS bucket_label  FROM referrer_orders ro) bGROUP BY bucket_label, sort_keyORDER BY sort_keyORDER BY sort_key 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 first_referral AS (
     SELECT tenant_id, referrer_user_id, MIN(created_at) AS first_referral_at
     FROM referrals
     GROUP BY tenant_id, referrer_user_id
   ),
   referrer_orders AS (
     SELECT
       fr.tenant_id AS tenant_id,
       fr.referrer_user_id AS referrer_user_id,
       COUNT(CASE WHEN o.placed_at < fr.first_referral_at THEN 1 END) AS orders_before,
       COUNT(CASE WHEN o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' THEN 1 END) AS orders_2025
     FROM first_referral fr
     LEFT JOIN orders o
       ON o.tenant_id = fr.tenant_id AND o.user_id = fr.referrer_user_id
     GROUP BY fr.tenant_id, fr.referrer_user_id, fr.first_referral_at
   )
   SELECT
     bucket_label AS orders_before_first_referral_bucket,
     COUNT(*) AS referrer_count,
     COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS share_of_referrers_pct,
     AVG(CAST(orders_2025 AS NUMERIC(18,6))) AS avg_total_2025_orders
   FROM (
     SELECT
       ro.orders_before AS orders_before,
       ro.orders_2025 AS orders_2025,
       CASE
         WHEN ro.orders_before = 0 THEN 0
         WHEN ro.orders_before = 1 THEN 1
         WHEN ro.orders_before = 2 THEN 2
         WHEN ro.orders_before = 3 THEN 3
         WHEN ro.orders_before BETWEEN 4 AND 5 THEN 4
         ELSE 6
       END AS sort_key,
       CASE
         WHEN ro.orders_before = 0 THEN '0 (referred before ordering)'
         WHEN ro.orders_before = 1 THEN '1'
         WHEN ro.orders_before = 2 THEN '2'
         WHEN ro.orders_before = 3 THEN '3'
         WHEN ro.orders_before BETWEEN 4 AND 5 THEN '4-5'
         ELSE '6+'
       END AS bucket_label
     FROM referrer_orders ro
   ) b
   GROUP BY bucket_label, sort_key
   ORDER BY sort_key NULLS LAST

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

   Viktor's whole session, 7 turns

   • The request, as Viktor received it

     Report request from Sofie Nilsen (analyst for the Head of Growth Marketing):
     
     Title: At what point in their order history do customers start referring?
     Question: Is there a typical order-count threshold after which a customer becomes a referrer, which would tell us when to prompt the referral ask?
     Grain: one row per bucket of orders-placed-before-first-referral
     Columns:
     - orders_before_first_referral_bucket - '0 (referred before ordering)', '1', '2', '3', '4-5', or '6+'
     - referrer_count - count of distinct (tenant_id, referrer_user_id) pairs falling in that bucket
     - share_of_referrers_pct - referrer_count / total referrers across all buckets * 100, rounded to 1 decimal
     - avg_total_2025_orders - average of each referrer's full count of 2025 orders (not just pre-referral), rounded to 2 decimals
     Details: Source: referrals, deduplicated to one row per (tenant_id, referrer_user_id) by taking the earliest created_at as 'first_referral_at'. For each, count that user's orders (any status) with placed_at strictly before first_referral_at, bucket per the labels above. avg_total_2025_orders = average count of that user's orders with placed_at in 2025 (full year, for context on eventual frequency). Order rows in the fixed sequence 0, 1, 2, 3, 4-5, 6+ (use a CASE-based sort key, not alphabetical). Expect exactly 6 rows.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Viktor calls dry_run

     WITH first_referral AS (
       SELECT tenant_id, referrer_user_id, MIN(created_at) AS first_referral_at
       FROM referrals
       GROUP BY tenant_id, referrer_user_id
     ),
     referrer_orders AS (
       SELECT
         fr.tenant_id,
         fr.referrer_user_id,
         COUNT(CASE WHEN o.placed_at < fr.first_referral_at THEN 1 END) AS orders_before,
         COUNT(CASE WHEN o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' THEN 1 END) AS orders_2025
       FROM first_referral fr
       LEFT JOIN orders o
         ON o.tenant_id = fr.tenant_id AND o.user_id = fr.referrer_user_id
       GROUP BY fr.tenant_id, fr.referrer_user_id, fr.first_referral_at
     )
     SELECT
       bucket_label AS orders_before_first_referral_bucket,
       COUNT(*) AS referrer_count,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS share_of_referrers_pct,
       ROUND(AVG(orders_2025), 2) AS avg_total_2025_orders
     FROM (
       SELECT
         ro.orders_before,
         ro.orders_2025,
         CASE
           WHEN ro.orders_before = 0 THEN 0
           WHEN ro.orders_before = 1 THEN 1
           WHEN ro.orders_before = 2 THEN 2
           WHEN ro.orders_before = 3 THEN 3
           WHEN ro.orders_before BETWEEN 4 AND 5 THEN 4
           ELSE 6
         END AS sort_key,
         CASE
           WHEN ro.orders_before = 0 THEN '0 (referred before ordering)'
           WHEN ro.orders_before = 1 THEN '1'
           WHEN ro.orders_before = 2 THEN '2'
           WHEN ro.orders_before = 3 THEN '3'
           WHEN ro.orders_before BETWEEN 4 AND 5 THEN '4-5'
           ELSE '6+'
         END AS bucket_label
       FROM referrer_orders ro
     ) b
     GROUP BY bucket_label, sort_key
     ORDER BY sort_key

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: orders_before_first_referral_bucket, referrer_count, share_of_referrers_pct, avg_total_2025_orders

   • ViktorQuery compiles and returns exactly the requested columns. Submitting.
   • Viktor calls submit

     WITH first_referral AS (
       SELECT tenant_id, referrer_user_id, MIN(created_at) AS first_referral_at
       FROM referrals
       GROUP BY tenant_id, referrer_user_id
     ),
     referrer_orders AS (
       SELECT
         fr.tenant_id,
         fr.referrer_user_id,
         COUNT(CASE WHEN o.placed_at < fr.first_referral_at THEN 1 END) AS orders_before,
         COUNT(CASE WHEN o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' THEN 1 END) AS orders_2025
       FROM first_referral fr
       LEFT JOIN orders o
         ON o.tenant_id = fr.tenant_id AND o.user_id = fr.referrer_user_id
       GROUP BY fr.tenant_id, fr.referrer_user_id, fr.first_referral_at
     )
     SELECT
       bucket_label AS orders_before_first_referral_bucket,
       COUNT(*) AS referrer_count,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS share_of_referrers_pct,
       ROUND(AVG(orders_2025), 2) AS avg_total_2025_orders
     FROM (
       SELECT
         ro.orders_before,
         ro.orders_2025,
         CASE
           WHEN ro.orders_before = 0 THEN 0
           WHEN ro.orders_before = 1 THEN 1
           WHEN ro.orders_before = 2 THEN 2
           WHEN ro.orders_before = 3 THEN 3
           WHEN ro.orders_before BETWEEN 4 AND 5 THEN 4
           ELSE 6
         END AS sort_key,
         CASE
           WHEN ro.orders_before = 0 THEN '0 (referred before ordering)'
           WHEN ro.orders_before = 1 THEN '1'
           WHEN ro.orders_before = 2 THEN '2'
           WHEN ro.orders_before = 3 THEN '3'
           WHEN ro.orders_before BETWEEN 4 AND 5 THEN '4-5'
           ELSE '6+'
         END AS bucket_label
       FROM referrer_orders ro
     ) b
     GROUP BY bucket_label, sort_key
     ORDER BY sort_key

   • The warehouse answers

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

   • ViktorSubmitted. The report buckets each distinct (tenant_id, referrer_user_id) referrer by how many orders they had placed before their first referral (earliest `referrals.created_at`), with the fixed 6-bucket sequence, share-of-total percentage, and their average full-year-2025 order count for context on eventual purchase frequency.

   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 (269 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)