Queries · q058 · Head of Growth Marketing · week 1, night 4
“Within the same industry, do loyalty-program members actually order more or spend more than non-members, or does the platform-wide 'members look worse' result just reflect which verticals have more loyalty members enrolled?”
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 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.
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é
The analyst · week 1, night 4, request 1 of 3
Last night's industry breakdown overturned my earlier 'discounted orders are healthier' read: that was Simpson's-paradox-driven by B2B contract pricing (huge baskets, 40+ items/order) and near-free subscription trial offers in Food & Coffee/Pet Supplies (avg order value ~6-8 EUR). Within actual consumer retail verticals (apparel, beauty, electronics, home, sports), discounted orders have equal-or-lower AOV and meaningfully HIGHER return rates than full-price ones — the opposite of the pooled story. That's resolved and ready for the memo, no further query needed there. The loyalty report is the one open thread: members show lower order frequency and AOV than non-members, which is counterintuitive, and I suspect the same composition trap (loyalty concentrated in low-AOV subscription verticals) — worth one check before I tell Chloé 'loyalty doesn't move the needle.' Email engagement (29.9% open, 2.4% click, healthy bounce/unsubscribe) looked like a normal, non-alarming baseline with only one channel actually used, so I'm not spending a slot chasing it further. With this being the last night, I'm using the remaining two slots to close out Chloé's still-open questions: the referral half of Q4, and a first look at Q5 (A/B tests), both as compact baseline reports for the Friday memo.
The engineer · 1 dry run on the empty schema, then the submission
SELECT t.industry AS industry, COUNT(DISTINCT CASE WHEN dla.user_id IS NOT NULL THEN o.user_id END) AS members_count, COUNT(DISTINCT CASE WHEN dla.user_id IS NULL THEN o.user_id END) AS nonmembers_count, ROUND(COUNT(CASE WHEN dla.user_id IS NOT NULL THEN o.id END)::numeric / NULLIF(COUNT(DISTINCT CASE WHEN dla.user_id IS NOT NULL THEN o.user_id END), 0), 2) AS avg_orders_per_member, ROUND(COUNT(CASE WHEN dla.user_id IS NULL THEN o.id END)::numeric / NULLIF(COUNT(DISTINCT CASE WHEN dla.user_id IS NULL THEN o.user_id END), 0), 2) AS avg_orders_per_nonmember, ROUND(AVG(CASE WHEN dla.user_id IS NOT NULL THEN o.grand_total / (CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END) END), 0) AS avg_order_value_member_eur, ROUND(AVG(CASE WHEN dla.user_id IS NULL THEN o.grand_total / (CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END) END), 0) AS avg_order_value_nonmember_eur FROM orders o JOIN tenants t ON t.id = o.tenant_id LEFT JOIN (SELECT DISTINCT tenant_id, user_id FROM loyalty_accounts) dla ON dla.tenant_id = o.tenant_id AND dla.user_id = o.user_id LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE) WHERE o.placed_at >= TIMESTAMP '2025-01-01' AND o.placed_at < TIMESTAMP '2026-01-01' AND t.industry IN ( SELECT DISTINCT t2.industry FROM tenants t2 JOIN loyalty_programs lp ON lp.tenant_id = t2.id WHERE lp.is_active = true ) GROUP BY t.industry ORDER BY t.industry
“Query compiles and returns the 7 requested columns at the industry grain. Submitting.”
“Submitted. The report groups 2025 orders by tenant industry (restricted to industries with at least one tenant running an active loyalty program), splitting each order's placing user into member/non-member based on whether they have any loyalty_accounts row at that tenant, and reports counts, average orders per user, and average EUR order value for each group — letting the Head of Growth Marketing see whether members out-order/out-spend non-members within the same industry.”
As Sofie Nilsen saw it, from the small dataset the agents worked on
industry | members_count | nonmembers_count | avg_orders_per_member | avg_orders_per_nonmember | avg_order_value_member_eur | avg_order_value_nonmember_eur ----------------------+---------------+------------------+-----------------------+--------------------------+----------------------------+------------------------------ Apparel & Footwear | 619 | 2140 | 2.53 | 2.62 | 165.0 | 189.0 Beauty | 45 | 447 | 2.84 | 3.22 | 102.0 | 96.0 Books & Stationery | 8 | 47 | 3.13 | 2.85 | 69.0 | 48.0 Consumer Electronics | 25 | 250 | 2.28 | 2.08 | 446.0 | 1114.0 Food & Coffee | 7 | 831 | 7.71 | 8.64 | 61.0 | 30.0 Home & Living | 26 | 710 | 2.23 | 2.26 | 144.0 | 149.0 Jewelry & Accessories | 0 | 29 | NULL | 1.72 | NULL | 217.0 Pet Supplies | 4 | 229 | 4.0 | 6.28 | 49.0 | 37.0 Sports & Outdoor | 128 | 330 | 2.55 | 2.09 | 355.0 | 372.0 (9 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
SELECT t.industry AS industry, COUNT(DISTINCT CASE WHEN dla.user_id IS NOT NULL THEN o.user_id END) AS members_count, COUNT(DISTINCT CASE WHEN dla.user_id IS NULL THEN o.user_id END) AS nonmembers_count, ROUND(COUNT(CASE WHEN dla.user_id IS NOT NULL THEN o.id END)::numeric / NULLIF(COUNT(DISTINCT CASE WHEN dla.user_id IS NOT NULL THEN o.user_id END), 0), 2) AS avg_orders_per_member, ROUND(COUNT(CASE WHEN dla.user_id IS NULL THEN o.id END)::numeric / NULLIF(COUNT(DISTINCT CASE WHEN dla.user_id IS NULL THEN o.user_id END), 0), 2) AS avg_orders_per_nonmember, ROUND(AVG(CASE WHEN dla.user_id IS NOT NULL THEN o.grand_total / (CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END) END), 0) AS avg_order_value_member_eur, ROUND(AVG(CASE WHEN dla.user_id IS NULL THEN o.grand_total / (CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END) END), 0) AS avg_order_value_nonmember_eur CAST(COUNT(CASE WHEN dla.user_id IS NOT NULL THEN o.id END) AS numeric(18,6)) / NULLIF(COUNT(DISTINCT CASE WHEN dla.user_id IS NOT NULL THEN o.user_id END), 0) AS avg_orders_per_member, CAST(COUNT(CASE WHEN dla.user_id IS NULL THEN o.id END) AS numeric(18,6)) / NULLIF(COUNT(DISTINCT CASE WHEN dla.user_id IS NULL THEN o.user_id END), 0) AS avg_orders_per_nonmember, AVG(CASE WHEN dla.user_id IS NOT NULL THEN o.grand_total / (CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END) END) AS avg_order_value_member_eur, AVG(CASE WHEN dla.user_id IS NULL THEN o.grand_total / (CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END) END) AS avg_order_value_nonmember_eurFROM orders oJOIN tenants t ON t.id = o.tenant_idLEFT JOIN (SELECT DISTINCT tenant_id, user_id FROM loyalty_accounts) dla ON dla.tenant_id = o.tenant_id AND dla.user_id = o.user_idLEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE)WHERE o.placed_at >= TIMESTAMP '2025-01-01' AND o.placed_at < TIMESTAMP '2026-01-01' AND t.industry IN ( SELECT DISTINCT t2.industry SELECT DISTINCT t2.industry AS industry FROM tenants t2 JOIN loyalty_programs lp ON lp.tenant_id = t2.id WHERE lp.is_active = true )GROUP BY t.industryORDER BY t.industryORDER BY t.industry NULLS LAST
SELECT t.industry AS industry, COUNT(DISTINCT CASE WHEN dla.user_id IS NOT NULL THEN o.user_id END) AS members_count, COUNT(DISTINCT CASE WHEN dla.user_id IS NULL THEN o.user_id END) AS nonmembers_count, CAST(COUNT(CASE WHEN dla.user_id IS NOT NULL THEN o.id END) AS numeric(18,6)) / NULLIF(COUNT(DISTINCT CASE WHEN dla.user_id IS NOT NULL THEN o.user_id END), 0) AS avg_orders_per_member, CAST(COUNT(CASE WHEN dla.user_id IS NULL THEN o.id END) AS numeric(18,6)) / NULLIF(COUNT(DISTINCT CASE WHEN dla.user_id IS NULL THEN o.user_id END), 0) AS avg_orders_per_nonmember, AVG(CASE WHEN dla.user_id IS NOT NULL THEN o.grand_total / (CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END) END) AS avg_order_value_member_eur, AVG(CASE WHEN dla.user_id IS NULL THEN o.grand_total / (CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END) END) AS avg_order_value_nonmember_eur FROM orders o JOIN tenants t ON t.id = o.tenant_id LEFT JOIN (SELECT DISTINCT tenant_id, user_id FROM loyalty_accounts) dla ON dla.tenant_id = o.tenant_id AND dla.user_id = o.user_id LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE) WHERE o.placed_at >= TIMESTAMP '2025-01-01' AND o.placed_at < TIMESTAMP '2026-01-01' AND t.industry IN ( SELECT DISTINCT t2.industry AS industry FROM tenants t2 JOIN loyalty_programs lp ON lp.tenant_id = t2.id WHERE lp.is_active = true ) GROUP BY t.industry ORDER BY t.industry 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: Is loyalty members' lower spend a within-industry effect or a merchant-mix effect? Question: Within the same industry, do loyalty-program members actually order more or spend more than non-members, or does the platform-wide 'members look worse' result just reflect which verticals have more loyalty members enrolled? Grain: one row per industry that has at least one tenant with an active loyalty program Columns: - industry - tenants.industry - members_count - distinct users with a loyalty_accounts row who placed >=1 order with placed_at in 2025 at a tenant in this industry - nonmembers_count - distinct users without a loyalty_accounts row who placed >=1 order with placed_at in 2025 at a tenant in this industry - avg_orders_per_member - that industry's members' total 2025 orders / members_count, rounded to 2 decimals - avg_orders_per_nonmember - same calc for non-members, rounded to 2 decimals - avg_order_value_member_eur - average grand_total (EUR) of members' 2025 orders in this industry, rounded to 0 decimals - avg_order_value_nonmember_eur - average grand_total (EUR) of non-members' 2025 orders in this industry, rounded to 0 decimals Details: Source: orders joined to tenants (for industry) and loyalty_accounts (match on user_id and tenant_id) to classify each ordering user as member/non-member, same definitions as last night's loyalty report. Restrict to industries where tenants.id appears in loyalty_programs with is_active = true (i.e. only verticals where loyalty is actually offered). Convert grand_total to EUR the same way as prior money reports (exchange_rates, base_code='EUR', quote_code=order currency, as_of=placed_at date, EUR=rate 1). Order by industry ascending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT t.industry AS industry, COUNT(DISTINCT CASE WHEN dla.user_id IS NOT NULL THEN o.user_id END) AS members_count, COUNT(DISTINCT CASE WHEN dla.user_id IS NULL THEN o.user_id END) AS nonmembers_count, ROUND(COUNT(CASE WHEN dla.user_id IS NOT NULL THEN o.id END)::numeric / NULLIF(COUNT(DISTINCT CASE WHEN dla.user_id IS NOT NULL THEN o.user_id END), 0), 2) AS avg_orders_per_member, ROUND(COUNT(CASE WHEN dla.user_id IS NULL THEN o.id END)::numeric / NULLIF(COUNT(DISTINCT CASE WHEN dla.user_id IS NULL THEN o.user_id END), 0), 2) AS avg_orders_per_nonmember, ROUND(AVG(CASE WHEN dla.user_id IS NOT NULL THEN o.grand_total / (CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END) END), 0) AS avg_order_value_member_eur, ROUND(AVG(CASE WHEN dla.user_id IS NULL THEN o.grand_total / (CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END) END), 0) AS avg_order_value_nonmember_eur FROM orders o JOIN tenants t ON t.id = o.tenant_id LEFT JOIN (SELECT DISTINCT tenant_id, user_id FROM loyalty_accounts) dla ON dla.tenant_id = o.tenant_id AND dla.user_id = o.user_id LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE) WHERE o.placed_at >= TIMESTAMP '2025-01-01' AND o.placed_at < TIMESTAMP '2026-01-01' AND t.industry IN ( SELECT DISTINCT t2.industry FROM tenants t2 JOIN loyalty_programs lp ON lp.tenant_id = t2.id WHERE lp.is_active = true ) GROUP BY t.industry ORDER BY t.industry
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: industry, members_count, nonmembers_count, avg_orders_per_member, avg_orders_per_nonmember, avg_order_value_member_eur, avg_order_value_nonmember_eur
submitSELECT t.industry AS industry, COUNT(DISTINCT CASE WHEN dla.user_id IS NOT NULL THEN o.user_id END) AS members_count, COUNT(DISTINCT CASE WHEN dla.user_id IS NULL THEN o.user_id END) AS nonmembers_count, ROUND(COUNT(CASE WHEN dla.user_id IS NOT NULL THEN o.id END)::numeric / NULLIF(COUNT(DISTINCT CASE WHEN dla.user_id IS NOT NULL THEN o.user_id END), 0), 2) AS avg_orders_per_member, ROUND(COUNT(CASE WHEN dla.user_id IS NULL THEN o.id END)::numeric / NULLIF(COUNT(DISTINCT CASE WHEN dla.user_id IS NULL THEN o.user_id END), 0), 2) AS avg_orders_per_nonmember, ROUND(AVG(CASE WHEN dla.user_id IS NOT NULL THEN o.grand_total / (CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END) END), 0) AS avg_order_value_member_eur, ROUND(AVG(CASE WHEN dla.user_id IS NULL THEN o.grand_total / (CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END) END), 0) AS avg_order_value_nonmember_eur FROM orders o JOIN tenants t ON t.id = o.tenant_id LEFT JOIN (SELECT DISTINCT tenant_id, user_id FROM loyalty_accounts) dla ON dla.tenant_id = o.tenant_id AND dla.user_id = o.user_id LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE) WHERE o.placed_at >= TIMESTAMP '2025-01-01' AND o.placed_at < TIMESTAMP '2026-01-01' AND t.industry IN ( SELECT DISTINCT t2.industry FROM tenants t2 JOIN loyalty_programs lp ON lp.tenant_id = t2.id WHERE lp.is_active = true ) GROUP BY t.industry ORDER BY t.industry
Submitted to tonight's batch. Your work on this request is done.