Queries · q372 · Head of Lifecycle Marketing · week 2, night 1
“Before retesting the rewards-redemption-without-purchase hypothesis, is order_id even populated on redemption-direction loyalty_transactions, or do we need a different way to connect a redemption to an order?”
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 2
Subject: Re: Loyalty & referral — thank you, and where to dig next
Ingrid, this is excellent — especially catching that the refund-rate gap was a vertical mix artifact. Good instinct to go back and break that out rather than let the scary headline stand.
What surprised me most, and what I want explained before we take this to Tobias as a joint recommendation: a 19.4% cost-to-member-revenue ratio is strikingly high, and members showing lower revenue and order counts than non-members is the opposite of what a loyalty programme is supposed to produce. I don't want to present "the programme loses money" to leadership until I understand whether that's a true causal effect or another mix artifact like the refund rate turned out to be — since 72% of members sit in one vertical, I'm wary of a second apples-to-oranges comparison hiding in there.
Questions for this week, in my own words:
The B2B extension question can wait — I agree we shouldn't open that door until we trust the member economics number. Let's keep that out of this week's slots unless there's room left over.
Thanks again, Ingrid — this is exactly the kind of rigor I want from this team.
Best, Aisha
The analyst · week 2, night 1, request 3 of 3
Aisha's push is right: with 72% of members in one vertical, the revenue/order gap could be a second Simpson's-paradox case just like refunds were. Tonight I'm prioritizing her questions 1-3 in order since she explicitly ranked the apparel-only comparison as the one she'll trust as a verdict, and flagged the liability trajectory and the redemption-linkage retest as the two things that could change the recommendation. Before rebuilding the gaming-hypothesis join (which returned zero matches last time), I'm first running a cheap diagnostic on whether order_id is ever populated on redemption-direction loyalty_transactions at all - if it's structurally never set, I need a different join strategy (e.g. time-proximity matching) rather than repeating the same failed approach, and I'd rather spend a slot finding that out now than waste a bigger request later. Holding question 4 (what better-performing members look like) and the B2B question for later nights, per Aisha's explicit deprioritization.
The engineer · 1 dry run on the empty schema, then the submission
select direction, count(*) as transaction_count, count(order_id) as transactions_with_order_id, round(100.0 * count(order_id) / nullif(count(*), 0), 2) as pct_with_order_id, count(distinct order_id) as distinct_order_ids_referenced from loyalty_transactions where occurred_at >= date '2023-01-01' and occurred_at < date '2026-01-01' group by direction order by transaction_count desc, direction
“Looks good — submitting.”
“Submitted. The report will show, per direction value (e.g. earn/redeem), how many loyalty_transactions rows exist, how many have a non-null order_id, the percentage with an order_id, and how many distinct orders are referenced — which directly answers whether order_id linkage is usable for the redemption-to-order hypothesis or whether a time-proximity matching approach is needed instead.”
As Ingrid Solberg saw it, from the small dataset the agents worked on
direction | transaction_count | transactions_with_order_id | pct_with_order_id | distinct_order_ids_referenced ----------+-------------------+----------------------------+-------------------+------------------------------ earn | 3931 | 3931 | 100.0 | 3931 burn | 343 | 0 | 0.0 | 0 (2 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 direction, count(*) as transaction_count, count(order_id) as transactions_with_order_id, round(100.0 * count(order_id) / nullif(count(*), 0), 2) as pct_with_order_id, 100.0 * count(order_id) / nullif(count(*), 0) as pct_with_order_id, count(distinct order_id) as distinct_order_ids_referencedfrom loyalty_transactionswhere occurred_at >= date '2023-01-01' and occurred_at < date '2026-01-01'group by directionorder by transaction_count desc, directionorder by transaction_count desc NULLS FIRST, direction NULLS LAST
select direction, count(*) as transaction_count, count(order_id) as transactions_with_order_id, 100.0 * count(order_id) / nullif(count(*), 0) as pct_with_order_id, count(distinct order_id) as distinct_order_ids_referenced from loyalty_transactions where occurred_at >= date '2023-01-01' and occurred_at < date '2026-01-01' group by direction order by transaction_count desc NULLS FIRST, direction 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: Loyalty transaction order-linkage diagnostic by direction Question: Before retesting the rewards-redemption-without-purchase hypothesis, is order_id even populated on redemption-direction loyalty_transactions, or do we need a different way to connect a redemption to an order? Grain: one row per distinct loyalty_transactions.direction value Columns: - direction - transaction_count - transactions_with_order_id - pct_with_order_id - distinct_order_ids_referenced Details: Source: loyalty_transactions across all tenants, 2023-01-01 to 2025-12-31. Group by direction. transaction_count = total rows for that direction. transactions_with_order_id = count where order_id is not null. pct_with_order_id = transactions_with_order_id divided by transaction_count as a percentage. distinct_order_ids_referenced = count of distinct non-null order_id values for that direction. This is a diagnostic to determine whether order_id linkage is usable at all for testing the redemption-to-refund hypothesis, or whether an alternative approach (e.g. matching by time proximity between a transaction and the customer's orders) will be needed instead. Order by transaction_count descending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runselect direction, count(*) as transaction_count, count(order_id) as transactions_with_order_id, round(100.0 * count(order_id) / nullif(count(*), 0), 2) as pct_with_order_id, count(distinct order_id) as distinct_order_ids_referenced from loyalty_transactions where occurred_at >= date '2023-01-01' and occurred_at < date '2026-01-01' group by direction order by transaction_count desc, direction
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: direction, transaction_count, transactions_with_order_id, pct_with_order_id, distinct_order_ids_referenced
submitselect direction, count(*) as transaction_count, count(order_id) as transactions_with_order_id, round(100.0 * count(order_id) / nullif(count(*), 0), 2) as pct_with_order_id, count(distinct order_id) as distinct_order_ids_referenced from loyalty_transactions where occurred_at >= date '2023-01-01' and occurred_at < date '2026-01-01' group by direction order by transaction_count desc, direction
Submitted to tonight's batch. Your work on this request is done.