Queries · q166 · Head of Lifecycle Marketing · week 1, night 3
“Among loyalty members' own orders, do the ones where loyalty points were actually redeemed show a higher refund rate than their other orders - i.e. is the elevated member refund rate concentrated in reward-redeeming purchases, suggesting rewards are being used without real purchase intent?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
Week 1; shared with Tobias Lindqvist, Chief Financial Officer
Is the loyalty and referral programme worth it? Do members and referred customers behave differently from others, and what does the programme cost?
The manager, at the start of week 1
Subject: Loyalty & referral programme — what I need for Friday's memo
Hi Ingrid,
Welcome aboard — thank you for jumping on this in week one. Tobias and I need to give the leadership team a joint, evidence-based view on whether the loyalty and referral programme is paying for itself. The deliverable is your Friday memo, which Tobias and I will use to decide whether we expand the programme, leave it as is, or rework it. Given the slot budget, let's prioritize breadth of the right questions over depth on any one — we can always follow up next week.
Here's what I'm trying to answer, in my own words:
Given we only have a few report slots a night, I'd rank these in order 3, 1, 2 as the must-haves for Friday; 4 and 5 are nice-to-haves if slots allow. Please loop in whatever Tobias's analyst is already pulling on the finance side so we're not duplicating report requests.
Thanks, Ingrid — happy to talk through any of this before you start drafting requests.
Best, Aisha
Subject: Finance angle on the loyalty/referral review
Hi Ingrid,
A few things I'd like us to cover as you scope this:
Happy to look at a first cut together before it goes wider.
Tobias
The analyst · week 1, night 3, request 1 of 3
Night 2 sharpened the picture. The tenure-controlled cut (Report 1) rules out the 'members are just newer accounts' explanation: at every tenure band members order less, spend less, and are refunded 2x more than non-members - and the refund gap actually widens with tenure (27% to 34% for members vs 20% down to 14% for non-members). So this isn't a cohort artifact; it's a persistent, worsening pattern. The before/after enrollment report (Report 2) came back with zero pre-enrollment orders for everyone - meaning enrollment essentially happens at or before a member's very first order (likely at signup), so there is no 'pre' baseline to diff against; that test was a dead end for isolating causality but confirms enrollment is immediate, not something people do after building a track record. Report 3 shows no smoking-gun concentration: redemption counts per referrer are almost all 1 (occasional 2), spread across dozens of tenants and referrers with normal-looking order histories (from near-zero to tens of thousands of EUR) - this looks like genuine light word-of-mouth, not gaming, though volume is tiny. For the last full night before the memo, I want to: (1) directly test Aisha's 'rewards redeemed without real repeat purchase' hypothesis by checking whether orders where points were actually redeemed show elevated refund rates versus other member orders - this could explain the mechanism behind the refund gap; (2) quantify participation and its trend for Aisha's #4; (3) close out Tobias's B2B payment-terms question, which is still untouched. I'm leaving one night in reserve for any necessary follow-up before Friday.
The engineer · 1 dry run on the empty schema, then the submission
WITH loyalty_members AS ( SELECT DISTINCT user_id FROM loyalty_accounts ), scoped_orders AS ( SELECT o.id, o.tenant_id, o.user_id, o.status, o.currency_code, o.grand_total, o.placed_at FROM orders o JOIN loyalty_members lm ON lm.user_id = o.user_id WHERE o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2026-01-01' ), redeem_agg AS ( SELECT order_id, SUM(ABS(points)) AS points_redeemed, COUNT(*) AS redeem_tx_count FROM loyalty_transactions WHERE direction <> 'earn' AND order_id IS NOT NULL GROUP BY order_id ), refund_payment_orders AS ( SELECT DISTINCT order_id FROM payments WHERE order_id IS NOT NULL AND amount_refunded > 0 ), refund_return_orders AS ( SELECT DISTINCT order_id FROM order_returns ), classified AS ( SELECT so.id, so.tenant_id, so.user_id, so.status, so.currency_code, so.grand_total, so.placed_at, CASE WHEN ra.order_id IS NOT NULL THEN 'points redeemed on this order' ELSE 'no points redeemed on this order' END AS order_type, COALESCE(ra.points_redeemed, 0) AS points_redeemed, CASE WHEN rpo.order_id IS NOT NULL OR rro.order_id IS NOT NULL THEN 1 ELSE 0 END AS is_refunded FROM scoped_orders so LEFT JOIN redeem_agg ra ON ra.order_id = so.id LEFT JOIN refund_payment_orders rpo ON rpo.order_id = so.id LEFT JOIN refund_return_orders rro ON rro.order_id = so.id ), valued AS ( SELECT c.*, CASE WHEN c.status <> 'cancelled' THEN c.grand_total / er.rate ELSE NULL END AS grand_total_eur FROM classified c LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = c.currency_code AND er.as_of = CAST(c.placed_at AS DATE) ) SELECT order_type, COUNT(*) AS order_count, COUNT(DISTINCT user_id) AS distinct_customers, ROUND(100.0 * SUM(is_refunded) / COUNT(*), 2) AS refund_rate_pct, ROUND(AVG(grand_total_eur), 2) AS avg_order_value_eur, ROUND(AVG(points_redeemed), 2) AS avg_points_redeemed_per_order FROM valued GROUP BY order_type ORDER BY CASE WHEN order_type = 'points redeemed on this order' THEN 0 ELSE 1 END, order_type
“Query compiles and matches the requested grain/columns. Submitting.”
“Submitted. Summary of what the query does:
- Scopes to orders placed by users who have any loyalty_accounts row, between 2023-01-01 and 2025-12-31 (by placed_at).
- Classifies each order as "points redeemed on this order" if a loyalty_transactions row exists for that order with direction <> 'earn', else "no points redeemed on this order".
- Flags refunds via payments.amount_refunded > 0 or any order_returns row for the order.
- Converts non-cancelled orders' grand_total to EUR using the EUR -> currency_code rate as of the order's placed date.
- Aggregates to one row per order type with order count, distinct customers, refund rate %, average EUR order value, and average redeemed points per order.
Noted in the submission: orders lacking a placed_at are excluded from the date-scoped population, and avg_order_value_eur only reflects non-cancelled orders with a matching FX rate, while the refund rate uses the full order population per the requested definition.”
As Ingrid Solberg saw it, from the small dataset the agents worked on
order_type | order_count | distinct_customers | refund_rate_pct | avg_order_value_eur | avg_points_redeemed_per_order ---------------------------------+-------------+--------------------+-----------------+---------------------+------------------------------ no points redeemed on this order | 4941 | 1330 | 32.36 | 207.88 | 0.0 (1 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 loyalty_members AS ( SELECT DISTINCT user_id FROM loyalty_accounts),scoped_orders AS ( SELECT o.id, o.tenant_id, o.user_id, o.status, o.currency_code, o.grand_total, o.placed_at SELECT o.id AS id, o.tenant_id AS tenant_id, o.user_id AS user_id, o.status AS status, o.currency_code AS currency_code, o.grand_total AS grand_total, o.placed_at AS placed_at FROM orders o JOIN loyalty_members lm ON lm.user_id = o.user_id WHERE o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2026-01-01'),redeem_agg AS ( SELECT order_id, SUM(ABS(points)) AS points_redeemed, COUNT(*) AS redeem_tx_count FROM loyalty_transactions WHERE direction <> 'earn' AND order_id IS NOT NULL GROUP BY order_id),refund_payment_orders AS ( SELECT DISTINCT order_id FROM payments WHERE order_id IS NOT NULL AND amount_refunded > 0),refund_return_orders AS ( SELECT DISTINCT order_id FROM order_returns),classified AS ( SELECT so.id, so.tenant_id, so.user_id, so.status, so.currency_code, so.grand_total, so.placed_at, so.id AS id, so.tenant_id AS tenant_id, so.user_id AS user_id, so.status AS status, so.currency_code AS currency_code, so.grand_total AS grand_total, so.placed_at AS placed_at, CASE WHEN ra.order_id IS NOT NULL THEN 'points redeemed on this order' ELSE 'no points redeemed on this order' END AS order_type, COALESCE(ra.points_redeemed, 0) AS points_redeemed, CASE WHEN rpo.order_id IS NOT NULL OR rro.order_id IS NOT NULL THEN 1 ELSE 0 END AS is_refunded FROM scoped_orders so LEFT JOIN redeem_agg ra ON ra.order_id = so.id LEFT JOIN refund_payment_orders rpo ON rpo.order_id = so.id LEFT JOIN refund_return_orders rro ON rro.order_id = so.id),valued AS ( SELECT c.*, CASE WHEN c.status <> 'cancelled' THEN c.grand_total / er.rate ELSE NULL END AS grand_total_eur CASE WHEN c.status <> 'cancelled' THEN CAST(c.grand_total / er.rate AS NUMERIC(18,6)) ELSE NULL END AS grand_total_eur FROM classified c LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = c.currency_code AND er.as_of = CAST(c.placed_at AS DATE))SELECT order_type, COUNT(*) AS order_count, COUNT(DISTINCT user_id) AS distinct_customers, ROUND(100.0 * SUM(is_refunded) / COUNT(*), 2) AS refund_rate_pct, ROUND(AVG(grand_total_eur), 2) AS avg_order_value_eur, ROUND(AVG(points_redeemed), 2) AS avg_points_redeemed_per_order 100.0 * SUM(is_refunded) / COUNT(*) AS refund_rate_pct, AVG(grand_total_eur) AS avg_order_value_eur, AVG(points_redeemed) AS avg_points_redeemed_per_orderFROM valuedGROUP BY order_typeORDER BY CASE WHEN order_type = 'points redeemed on this order' THEN 0 ELSE 1 END, order_typeORDER BY CASE WHEN order_type = 'points redeemed on this order' THEN 0 ELSE 1 END NULLS LAST, order_type NULLS LAST
WITH loyalty_members AS ( SELECT DISTINCT user_id FROM loyalty_accounts ), scoped_orders AS ( SELECT o.id AS id, o.tenant_id AS tenant_id, o.user_id AS user_id, o.status AS status, o.currency_code AS currency_code, o.grand_total AS grand_total, o.placed_at AS placed_at FROM orders o JOIN loyalty_members lm ON lm.user_id = o.user_id WHERE o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2026-01-01' ), redeem_agg AS ( SELECT order_id, SUM(ABS(points)) AS points_redeemed, COUNT(*) AS redeem_tx_count FROM loyalty_transactions WHERE direction <> 'earn' AND order_id IS NOT NULL GROUP BY order_id ), refund_payment_orders AS ( SELECT DISTINCT order_id FROM payments WHERE order_id IS NOT NULL AND amount_refunded > 0 ), refund_return_orders AS ( SELECT DISTINCT order_id FROM order_returns ), classified AS ( SELECT so.id AS id, so.tenant_id AS tenant_id, so.user_id AS user_id, so.status AS status, so.currency_code AS currency_code, so.grand_total AS grand_total, so.placed_at AS placed_at, CASE WHEN ra.order_id IS NOT NULL THEN 'points redeemed on this order' ELSE 'no points redeemed on this order' END AS order_type, COALESCE(ra.points_redeemed, 0) AS points_redeemed, CASE WHEN rpo.order_id IS NOT NULL OR rro.order_id IS NOT NULL THEN 1 ELSE 0 END AS is_refunded FROM scoped_orders so LEFT JOIN redeem_agg ra ON ra.order_id = so.id LEFT JOIN refund_payment_orders rpo ON rpo.order_id = so.id LEFT JOIN refund_return_orders rro ON rro.order_id = so.id ), valued AS ( SELECT c.*, CASE WHEN c.status <> 'cancelled' THEN CAST(c.grand_total / er.rate AS NUMERIC(18,6)) ELSE NULL END AS grand_total_eur FROM classified c LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = c.currency_code AND er.as_of = CAST(c.placed_at AS DATE) ) SELECT order_type, COUNT(*) AS order_count, COUNT(DISTINCT user_id) AS distinct_customers, 100.0 * SUM(is_refunded) / COUNT(*) AS refund_rate_pct, AVG(grand_total_eur) AS avg_order_value_eur, AVG(points_redeemed) AS avg_points_redeemed_per_order FROM valued GROUP BY order_type ORDER BY CASE WHEN order_type = 'points redeemed on this order' THEN 0 ELSE 1 END NULLS LAST, order_type 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 Ingrid Solberg (analyst for the Head of Lifecycle Marketing): Title: Refund rate on points-redeeming orders vs other member orders Question: Among loyalty members' own orders, do the ones where loyalty points were actually redeemed show a higher refund rate than their other orders - i.e. is the elevated member refund rate concentrated in reward-redeeming purchases, suggesting rewards are being used without real purchase intent? Grain: one row per order type among loyalty members' orders: 'points redeemed on this order' vs 'no points redeemed on this order' Columns: - order_type - order_count - distinct_customers - refund_rate_pct - avg_order_value_eur - avg_points_redeemed_per_order Details: Population: all orders (any status, 2023-01-01 to 2025-12-31) placed by users who have a row in loyalty_accounts. Classify an order as 'points redeemed on this order' if there exists a loyalty_transactions row with that order_id and a direction other than 'earn' (i.e. a burn/redeem row); otherwise 'no points redeemed on this order'. refund_rate_pct = share of orders in that group with payments.amount_refunded greater than 0 OR an order_returns row, consistent with prior reports' refund definition. avg_order_value_eur = average grand_total of non-cancelled orders in the group, converted to EUR via exchange_rates (order currency_code to EUR, rate as_of placed_at). avg_points_redeemed_per_order = average absolute points value of the matched redeem transaction(s) for the 'points redeemed' group (0 for the other group). Order rows: 'points redeemed on this order' first, then 'no points redeemed on this order'. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH loyalty_members AS ( SELECT DISTINCT user_id FROM loyalty_accounts ), scoped_orders AS ( SELECT o.id, o.tenant_id, o.user_id, o.status, o.currency_code, o.grand_total, o.placed_at FROM orders o JOIN loyalty_members lm ON lm.user_id = o.user_id WHERE o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2026-01-01' ), redeem_agg AS ( SELECT order_id, SUM(ABS(points)) AS points_redeemed, COUNT(*) AS redeem_tx_count FROM loyalty_transactions WHERE direction <> 'earn' AND order_id IS NOT NULL GROUP BY order_id ), refund_payment_orders AS ( SELECT DISTINCT order_id FROM payments WHERE order_id IS NOT NULL AND amount_refunded > 0 ), refund_return_orders AS ( SELECT DISTINCT order_id FROM order_returns ), classified AS ( SELECT so.id, so.tenant_id, so.user_id, so.status, so.currency_code, so.grand_total, so.placed_at, CASE WHEN ra.order_id IS NOT NULL THEN 'points redeemed on this order' ELSE 'no points redeemed on this order' END AS order_type, COALESCE(ra.points_redeemed, 0) AS points_redeemed, CASE WHEN rpo.order_id IS NOT NULL OR rro.order_id IS NOT NULL THEN 1 ELSE 0 END AS is_refunded FROM scoped_orders so LEFT JOIN redeem_agg ra ON ra.order_id = so.id LEFT JOIN refund_payment_orders rpo ON rpo.order_id = so.id LEFT JOIN refund_return_orders rro ON rro.order_id = so.id ), valued AS ( SELECT c.*, CASE WHEN c.status <> 'cancelled' THEN c.grand_total / er.rate ELSE NULL END AS grand_total_eur FROM classified c LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = c.currency_code AND er.as_of = CAST(c.placed_at AS DATE) ) SELECT order_type, COUNT(*) AS order_count, COUNT(DISTINCT user_id) AS distinct_customers, ROUND(100.0 * SUM(is_refunded) / COUNT(*), 2) AS refund_rate_pct, ROUND(AVG(grand_total_eur), 2) AS avg_order_value_eur, ROUND(AVG(points_redeemed), 2) AS avg_points_redeemed_per_order FROM valued GROUP BY order_type ORDER BY CASE WHEN order_type = 'points redeemed on this order' THEN 0 ELSE 1 END, order_type
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: order_type, order_count, distinct_customers, refund_rate_pct, avg_order_value_eur, avg_points_redeemed_per_order
submitWITH loyalty_members AS ( SELECT DISTINCT user_id FROM loyalty_accounts ), scoped_orders AS ( SELECT o.id, o.tenant_id, o.user_id, o.status, o.currency_code, o.grand_total, o.placed_at FROM orders o JOIN loyalty_members lm ON lm.user_id = o.user_id WHERE o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2026-01-01' ), redeem_agg AS ( SELECT order_id, SUM(ABS(points)) AS points_redeemed, COUNT(*) AS redeem_tx_count FROM loyalty_transactions WHERE direction <> 'earn' AND order_id IS NOT NULL GROUP BY order_id ), refund_payment_orders AS ( SELECT DISTINCT order_id FROM payments WHERE order_id IS NOT NULL AND amount_refunded > 0 ), refund_return_orders AS ( SELECT DISTINCT order_id FROM order_returns ), classified AS ( SELECT so.id, so.tenant_id, so.user_id, so.status, so.currency_code, so.grand_total, so.placed_at, CASE WHEN ra.order_id IS NOT NULL THEN 'points redeemed on this order' ELSE 'no points redeemed on this order' END AS order_type, COALESCE(ra.points_redeemed, 0) AS points_redeemed, CASE WHEN rpo.order_id IS NOT NULL OR rro.order_id IS NOT NULL THEN 1 ELSE 0 END AS is_refunded FROM scoped_orders so LEFT JOIN redeem_agg ra ON ra.order_id = so.id LEFT JOIN refund_payment_orders rpo ON rpo.order_id = so.id LEFT JOIN refund_return_orders rro ON rro.order_id = so.id ), valued AS ( SELECT c.*, CASE WHEN c.status <> 'cancelled' THEN c.grand_total / er.rate ELSE NULL END AS grand_total_eur FROM classified c LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = c.currency_code AND er.as_of = CAST(c.placed_at AS DATE) ) SELECT order_type, COUNT(*) AS order_count, COUNT(DISTINCT user_id) AS distinct_customers, ROUND(100.0 * SUM(is_refunded) / COUNT(*), 2) AS refund_rate_pct, ROUND(AVG(grand_total_eur), 2) AS avg_order_value_eur, ROUND(AVG(points_redeemed), 2) AS avg_points_redeemed_per_order FROM valued GROUP BY order_type ORDER BY CASE WHEN order_type = 'points redeemed on this order' THEN 0 ELSE 1 END, order_type
Submitted to tonight's batch. Your work on this request is done.