Queries · q267 · Head of Growth Marketing · week 2, night 4
“Books & Stationery, Food & Coffee, and Pet Supplies show real loyalty members and orders in one report but €0 member revenue in another — is this a currency-conversion gap, and does it change the loyalty cost conclusion?”
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 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:
My questions for this week, roughly in priority order:
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é
The analyst · week 2, night 4, request 3 of 3
Urgent discrepancy to resolve before Friday: last week I told the whole company "only 1 in 7 orders can be traced to a marketing channel" (based on an attributions-to-campaigns join), but tonight's report counted attributed orders a different way (any attributions row at all) and got ~75-83% coverage for 2025 — a 5x gap on a number leadership already saw. I need to find out whether that's because many attribution rows have no campaign_id (meaning there's a broader 'we know the source' population that isn't tied to a paid campaign, e.g. organic/referral touches) before I say anything more about Q4. That's my top slot tonight. Second slot characterizes whatever that gap turns out to be, so I can give Chloé (and correct the record for everyone else) one honest, specific number instead of two conflicting ones. Third slot resolves the loyalty data-quality flag from last night (member_revenue_eur reading as exactly €0 for three small verticals) so the Q2 recommendation isn't sitting on an unverified caveat going into the memo. Referral timing (report 1 tonight) is clear enough to use as-is — 43% of referrers only refer after 6+ orders, so no further query needed there.
The engineer · 1 dry run on the empty schema, then the submission
SELECT t.id AS tenant_id, t.name AS tenant_name, t.industry, t.default_currency_code, COUNT(DISTINCT m.user_id) AS members_count, COUNT(o.id) AS member_orders_count_2025, SUM(CASE WHEN o.currency_code = t.default_currency_code THEN o.grand_total ELSE 0 END) AS member_revenue_local_sum, ROUND(SUM(CASE WHEN er.rate IS NOT NULL THEN o.grand_total / er.rate ELSE 0 END), 0) AS member_revenue_eur_converted, COUNT(CASE WHEN er.rate IS NULL THEN 1 END) AS orders_missing_exchange_rate_count, CASE WHEN COUNT(DISTINCT o.currency_code) > 1 THEN true ELSE false END AS has_mixed_currency_orders FROM tenants t JOIN ( SELECT DISTINCT la.tenant_id, la.user_id FROM loyalty_accounts la JOIN orders o2 ON o2.user_id = la.user_id AND o2.tenant_id = la.tenant_id WHERE o2.placed_at >= DATE '2025-01-01' AND o2.placed_at < DATE '2026-01-01' ) m ON m.tenant_id = t.id JOIN orders o ON o.tenant_id = m.tenant_id AND o.user_id = m.user_id AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' 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 t.industry IN ('Books & Stationery','Food & Coffee','Pet Supplies') GROUP BY t.id, t.name, t.industry, t.default_currency_code ORDER BY t.name ASC, t.id ASC
“Query compiles cleanly and returns the requested grain plus one diagnostic column. Submitting.”
“Submitted. The report gives one row per qualifying tenant (Books & Stationery, Food & Coffee, Pet Supplies with ≥1 loyalty member) with member counts, 2025 order counts, local-currency revenue (matched to default currency only), EUR-converted revenue, a count of orders with no matching exchange rate, and a mixed-currency flag — which together should pinpoint whether the €0 in the other report comes from a currency mismatch, a missing FX rate row, or something else.”
As Sofie Nilsen saw it, from the small dataset the agents worked on
tenant_id | tenant_name | industry | default_currency_code | members_count | member_orders_count_2025 | member_revenue_local_sum | member_revenue_eur_converted | orders_missing_exchange_rate_count | has_mixed_currency_orders
----------+-----------------------+--------------------+-----------------------+---------------+--------------------------+--------------------------+------------------------------+------------------------------------+--------------------------
50 | Buchstabe | Books & Stationery | EUR | 8 | 25 | 1728.0000 | 0.0 | 25 | false
91 | Chocolaterie Mireille | Food & Coffee | EUR | 7 | 54 | 3298.6700 | 0.0 | 54 | false
31 | Hound Hamper | Pet Supplies | EUR | 4 | 16 | 777.0600 | 0.0 | 16 | false
(3 rows)The memo and the queries that followed; links found by title, so inferred
It builds on:
Changed only where the benchmark needs the same result on every system
SELECT t.id AS tenant_id, t.name AS tenant_name, t.industry, t.default_currency_code, t.industry AS industry, t.default_currency_code AS default_currency_code, COUNT(DISTINCT m.user_id) AS members_count, COUNT(o.id) AS member_orders_count_2025, SUM(CASE WHEN o.currency_code = t.default_currency_code THEN o.grand_total ELSE 0 END) AS member_revenue_local_sum, ROUND(SUM(CASE WHEN er.rate IS NOT NULL THEN o.grand_total / er.rate ELSE 0 END), 0) AS member_revenue_eur_converted, SUM(CASE WHEN er.rate IS NOT NULL THEN CAST(o.grand_total / er.rate AS NUMERIC(18,6)) ELSE 0 END) AS member_revenue_eur_converted, COUNT(CASE WHEN er.rate IS NULL THEN 1 END) AS orders_missing_exchange_rate_count, CASE WHEN COUNT(DISTINCT o.currency_code) > 1 THEN true ELSE false END AS has_mixed_currency_ordersFROM tenants tJOIN ( SELECT DISTINCT la.tenant_id, la.user_id SELECT DISTINCT la.tenant_id AS tenant_id, la.user_id AS user_id FROM loyalty_accounts la JOIN orders o2 ON o2.user_id = la.user_id AND o2.tenant_id = la.tenant_id WHERE o2.placed_at >= DATE '2025-01-01' AND o2.placed_at < DATE '2026-01-01') m ON m.tenant_id = t.idJOIN orders o ON o.tenant_id = m.tenant_id AND o.user_id = m.user_id AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'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 t.industry IN ('Books & Stationery','Food & Coffee','Pet Supplies')GROUP BY t.id, t.name, t.industry, t.default_currency_codeORDER BY t.name ASC, t.id ASCORDER BY t.name ASC NULLS LAST, t.id ASC NULLS LAST
SELECT t.id AS tenant_id, t.name AS tenant_name, t.industry AS industry, t.default_currency_code AS default_currency_code, COUNT(DISTINCT m.user_id) AS members_count, COUNT(o.id) AS member_orders_count_2025, SUM(CASE WHEN o.currency_code = t.default_currency_code THEN o.grand_total ELSE 0 END) AS member_revenue_local_sum, SUM(CASE WHEN er.rate IS NOT NULL THEN CAST(o.grand_total / er.rate AS NUMERIC(18,6)) ELSE 0 END) AS member_revenue_eur_converted, COUNT(CASE WHEN er.rate IS NULL THEN 1 END) AS orders_missing_exchange_rate_count, CASE WHEN COUNT(DISTINCT o.currency_code) > 1 THEN true ELSE false END AS has_mixed_currency_orders FROM tenants t JOIN ( SELECT DISTINCT la.tenant_id AS tenant_id, la.user_id AS user_id FROM loyalty_accounts la JOIN orders o2 ON o2.user_id = la.user_id AND o2.tenant_id = la.tenant_id WHERE o2.placed_at >= DATE '2025-01-01' AND o2.placed_at < DATE '2026-01-01' ) m ON m.tenant_id = t.id JOIN orders o ON o.tenant_id = m.tenant_id AND o.user_id = m.user_id AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' 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 t.industry IN ('Books & Stationery','Food & Coffee','Pet Supplies') GROUP BY t.id, t.name, t.industry, t.default_currency_code ORDER BY t.name ASC NULLS LAST, t.id ASC 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: Why does loyalty member revenue read as €0 for three small verticals?
Question: Books & Stationery, Food & Coffee, and Pet Supplies show real loyalty members and orders in one report but €0 member revenue in another — is this a currency-conversion gap, and does it change the loyalty cost conclusion?
Grain: one row per tenant, restricted to tenants in Books & Stationery, Food & Coffee, or Pet Supplies with at least one loyalty member
Columns:
- tenant_name - tenants.name
- industry - tenants.industry
- default_currency_code - tenants.default_currency_code
- members_count - distinct users with a loyalty_accounts row who placed >=1 order with placed_at in 2025 at this tenant
- member_orders_count_2025 - count of those members' orders placed_at in 2025
- member_revenue_local_sum - sum of grand_total in each order's own currency_code, NOT converted, labeled alongside default_currency_code for reference (if a tenant mixes currencies, sum only those matching default_currency_code and flag separately if easier)
- member_revenue_eur_converted - sum of grand_total converted to EUR via exchange_rates (as of placed_at date), rounded to 0 decimals
- orders_missing_exchange_rate_count - count of those members' orders whose currency_code/placed_at date has no matching row in exchange_rates (i.e. would convert to null)
Details: Source: orders joined to tenants (filter industry in ('Books & Stationery','Food & Coffee','Pet Supplies')) and loyalty_accounts (member match on user_id+tenant_id), placed_at in 2025. This is a diagnostic report to find where the EUR conversion is silently dropping revenue to null/zero in the 'Loyalty point economics by vertical' report from two nights ago. Order by tenant_name ascending.
Check your query with the warehouse's dry_run tool, then hand it in with submit.dry_runSELECT t.id AS tenant_id, t.name AS tenant_name, t.industry, t.default_currency_code, COUNT(DISTINCT m.user_id) AS members_count, COUNT(o.id) AS member_orders_count_2025, SUM(CASE WHEN o.currency_code = t.default_currency_code THEN o.grand_total ELSE 0 END) AS member_revenue_local_sum, ROUND(SUM(CASE WHEN er.rate IS NOT NULL THEN o.grand_total / er.rate ELSE 0 END), 0) AS member_revenue_eur_converted, COUNT(CASE WHEN er.rate IS NULL THEN 1 END) AS orders_missing_exchange_rate_count, CASE WHEN COUNT(DISTINCT o.currency_code) > 1 THEN true ELSE false END AS has_mixed_currency_orders FROM tenants t JOIN ( SELECT DISTINCT la.tenant_id, la.user_id FROM loyalty_accounts la JOIN orders o2 ON o2.user_id = la.user_id AND o2.tenant_id = la.tenant_id WHERE o2.placed_at >= DATE '2025-01-01' AND o2.placed_at < DATE '2026-01-01' ) m ON m.tenant_id = t.id JOIN orders o ON o.tenant_id = m.tenant_id AND o.user_id = m.user_id AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' 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 t.industry IN ('Books & Stationery','Food & Coffee','Pet Supplies') GROUP BY t.id, t.name, t.industry, t.default_currency_code ORDER BY t.name ASC, t.id ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_id, tenant_name, industry, default_currency_code, members_count, member_orders_count_2025, member_revenue_local_sum, member_revenue_eur_converted, orders_missing_exchange_rate_count, has_mixed_currency_orders
submitSELECT t.id AS tenant_id, t.name AS tenant_name, t.industry, t.default_currency_code, COUNT(DISTINCT m.user_id) AS members_count, COUNT(o.id) AS member_orders_count_2025, SUM(CASE WHEN o.currency_code = t.default_currency_code THEN o.grand_total ELSE 0 END) AS member_revenue_local_sum, ROUND(SUM(CASE WHEN er.rate IS NOT NULL THEN o.grand_total / er.rate ELSE 0 END), 0) AS member_revenue_eur_converted, COUNT(CASE WHEN er.rate IS NULL THEN 1 END) AS orders_missing_exchange_rate_count, CASE WHEN COUNT(DISTINCT o.currency_code) > 1 THEN true ELSE false END AS has_mixed_currency_orders FROM tenants t JOIN ( SELECT DISTINCT la.tenant_id, la.user_id FROM loyalty_accounts la JOIN orders o2 ON o2.user_id = la.user_id AND o2.tenant_id = la.tenant_id WHERE o2.placed_at >= DATE '2025-01-01' AND o2.placed_at < DATE '2026-01-01' ) m ON m.tenant_id = t.id JOIN orders o ON o.tenant_id = m.tenant_id AND o.user_id = m.user_id AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' 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 t.industry IN ('Books & Stationery','Food & Coffee','Pet Supplies') GROUP BY t.id, t.name, t.industry, t.default_currency_code ORDER BY t.name ASC, t.id ASC
Submitted to tonight's batch. Your work on this request is done.