Queries · q303 · Head of Data · week 2, night 2
“Do any marketing segments or analytics cohorts already encode a definition like 'customers who purchased recently' or 'all customers', so we can certify a customer count the business already uses rather than inventing a new one?”
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
Before the board pack goes out, Data must certify the key metrics. Do revenue, orders, customers, refunds and payouts agree wherever they are recorded, and where they do not, why not and by how much?
The manager, at the start of week 2
Subject: Good work — a few things I need pinned down before we certify
Yuki,
This is exactly the kind of digging I wanted — especially catching that invoices weren't a real fifth number, and that your first "20-40% gap" read was wrong and you went back to check it. That's the instinct I want on this team.
Three things surprised me and need explaining before I can tell Tobias we're ready:
Questions for this week:
Keep the FX-adjusted view for USD/NOK on your list — that one's solid and the board will ask about it.
Thanks for the pace you're keeping on this.
Rosa
The analyst · week 2, night 2, request 2 of 3
Three things from last night change my plan. (1) The age-bucket report is reassuring but not a clean monotonic ramp: match rate is 100% at 0-7 days, dips to 83.4% at 8-14 days, climbs through the 90s, then settles around 98.9-100.1% from 91 days onward - so the true permanent "never collected" floor looks like just ~1-2%, but that mid-range dip needs an explanation before I tell Rosa this is purely timing; my best guess is a payment method with inherent capture lag (bank transfer/wire), which I want to test directly. (2) The refund-concentration report confirms Rosa's worry: the top 4 merchants (Cragsley Outfitters, Ampfeld Elektronik, Marrowby & Vane, Studio Vrij) account for 70.8% of all late-processed refunds in 2025, with Cragsley alone at 36.2% and still growing (+36.2% H1-to-H2) - this is genuinely concentrated, not spread thin, and it sits on our single biggest merchant. (3) The industry-revenue report was the big surprise: B2B wholesale (Office Supplies + Industrial Supplies) is 38.9% of 2025 collected revenue across just 3 tenants - far too material to leave out of the board pack, which changes the stakes on getting that receivables number right. But it also only found 3 B2B tenants by industry label, while Daniel Kim's team (last week's channel) referenced 5 B2B wholesale merchants - that gap is worth resolving properly now, by identifying B2B tenants operationally (via actual CRM deal activity) rather than by industry-text label, and recomputing their receivables on the corrected set. I'm also starting on Rosa's customer-definition-usage question tonight by surveying what audience/segment definitions already exist and are actively used elsewhere in the business.
The engineer · 1 dry run on the empty schema, then the submission
SELECT 'segment' AS source, t.name AS tenant_name, s.name AS name, s.is_dynamic AS is_dynamic, s.member_count AS member_count, substring(cast(s.rules AS varchar), 1, 500) AS rules_definition, s.last_refreshed_at AS last_updated FROM segments s JOIN tenants t ON t.id = s.tenant_id UNION ALL SELECT 'cohort' AS source, t.name AS tenant_name, c.name AS name, c.is_dynamic AS is_dynamic, c.member_count AS member_count, substring(cast(c.definition AS varchar), 1, 500) AS rules_definition, c.computed_at AS last_updated FROM cohorts c JOIN tenants t ON t.id = c.tenant_id ORDER BY member_count DESC, tenant_name ASC, name ASC LIMIT 50
“Query compiles cleanly. Submitting.”
“Submitted. The report pulls all segments and cohorts rows, tagged by source, joined to tenants for the name, with the rules/definition JSON cast to text and truncated to 500 characters, ordered by member_count descending and capped at the top 50 — matching the requested grain and columns exactly.”
As Yuki Tanaka saw it, from the small dataset the agents worked on
source | tenant_name | name | is_dynamic | member_count | rules_definition | last_updated
--------+------------------------+-------------------------------------+------------+--------------+------------------------------------------+--------------------
segment | Cragsley Outfitters | All subscribers | true | 849 | {"all":[{"field":"marketing_opt_in","op" | 2025-12-31 02:17:00
segment | Cragsley Outfitters | Lapsed 90 days | true | 400 | {"all":[{"field":"last_order_days_ago"," | 2025-12-31 02:17:00
cohort | Cragsley Outfitters | Customers before the 2023 migration | false | 348 | {"signed_up_before":"2023-01-01"} | 2023-01-01 04:00:00
segment | Cragsley Outfitters | VIP customers | true | 343 | {"all":[{"field":"orders_count","op":"gt | 2025-12-31 02:17:00
segment | Marrowby & Vane | All subscribers | true | 316 | {"all":[{"field":"marketing_opt_in","op" | 2025-12-31 02:17:00
segment | Brennquell Kaffee | All subscribers | true | 179 | {"all":[{"field":"marketing_opt_in","op" | 2025-12-31 02:17:00
cohort | Brennquell Kaffee | Customers before the 2023 migration | false | 175 | {"signed_up_before":"2023-01-01"} | 2023-01-01 04:00:00
segment | Marrowby & Vane | Lapsed 90 days | true | 155 | {"all":[{"field":"last_order_days_ago"," | 2025-12-31 02:17:00
cohort | Marrowby & Vane | Customers before the 2023 migration | false | 154 | {"signed_up_before":"2023-01-01"} | 2023-01-01 04:00:00
segment | Ampfeld Elektronik | All subscribers | true | 136 | {"all":[{"field":"marketing_opt_in","op" | 2025-12-31 02:17:00
segment | Cragsley Outfitters | New customers (30 days) | true | 132 | {"all":[{"field":"first_order_days_ago", | 2025-12-31 02:17:00
segment | Studio Vrij | All subscribers | true | 129 | {"all":[{"field":"marketing_opt_in","op" | 2025-12-31 02:17:00
segment | Marrowby & Vane | VIP customers | true | 126 | {"all":[{"field":"orders_count","op":"gt | 2025-12-31 02:17:00
segment | Brennquell Kaffee | VIP customers | true | 111 | {"all":[{"field":"orders_count","op":"gt | 2025-12-31 02:17:00
cohort | Cragsley Outfitters | Signed up 2024-12 | false | 109 | {"signed_up_from":"2024-12-01","signed_u | 2025-01-01 04:00:00
segment | Petalcourt Beauty | All subscribers | true | 101 | {"all":[{"field":"marketing_opt_in","op" | 2025-12-31 02:17:00
segment | Cinder Peak Sports Co. | All subscribers | true | 98 | {"all":[{"field":"marketing_opt_in","op" | 2025-12-31 02:17:00
cohort | Cragsley Outfitters | Signed up 2023-11 | false | 97 | {"signed_up_from":"2023-11-01","signed_u | 2023-12-01 04:00:00
cohort | Cragsley Outfitters | Signed up 2025-11 | false | 95 | {"signed_up_from":"2025-11-01","signed_u | 2025-12-01 04:00:00
cohort | Cragsley Outfitters | Signed up 2024-11 | false | 93 | {"signed_up_from":"2024-11-01","signed_u | 2024-12-01 04:00:00
segment | Fennel & Flint | All subscribers | true | 93 | {"all":[{"field":"marketing_opt_in","op" | 2025-12-31 02:17:00
cohort | Cragsley Outfitters | Signed up 2025-10 | false | 83 | {"signed_up_from":"2025-10-01","signed_u | 2025-11-01 04:00:00
segment | Brennquell Kaffee | Lapsed 90 days | true | 80 | {"all":[{"field":"last_order_days_ago"," | 2025-12-31 02:17:00
cohort | Studio Vrij | Customers before the 2023 migration | false | 77 | {"signed_up_before":"2023-01-01"} | 2023-01-01 04:00:00
cohort | Cragsley Outfitters | Signed up 2023-12 | false | 74 | {"signed_up_from":"2023-12-01","signed_u | 2024-01-01 04:00:00
cohort | Cragsley Outfitters | Signed up 2024-05 | false | 72 | {"signed_up_from":"2024-05-01","signed_u | 2024-06-01 04:00:00
cohort | Cragsley Outfitters | Signed up 2025-05 | false | 71 | {"signed_up_from":"2025-05-01","signed_u | 2025-06-01 04:00:00
cohort | Ampfeld Elektronik | Customers before the 2023 migration | false | 70 | {"signed_up_before":"2023-01-01"} | 2023-01-01 04:00:00
segment | Ampfeld Elektronik | Lapsed 90 days | true | 70 | {"all":[{"field":"last_order_days_ago"," | 2025-12-31 02:17:00
segment | Studio Vrij | Lapsed 90 days | true | 69 | {"all":[{"field":"last_order_days_ago"," | 2025-12-31 02:17:00
cohort | Cragsley Outfitters | Signed up 2024-10 | false | 68 | {"signed_up_from":"2024-10-01","signed_u | 2024-11-01 04:00:00
cohort | Cragsley Outfitters | Signed up 2025-07 | false | 68 | {"signed_up_from":"2025-07-01","signed_u | 2025-08-01 04:00:00
segment | Linden & Loom | All subscribers | true | 67 | {"all":[{"field":"marketing_opt_in","op" | 2025-12-31 02:17:00
cohort | Cragsley Outfitters | Signed up 2024-04 | false | 63 | {"signed_up_from":"2024-04-01","signed_u | 2024-05-01 04:00:00
cohort | Cragsley Outfitters | Signed up 2025-04 | false | 63 | {"signed_up_from":"2025-04-01","signed_u | 2025-05-01 04:00:00
cohort | Cragsley Outfitters | Signed up 2025-09 | false | 61 | {"signed_up_from":"2025-09-01","signed_u | 2025-10-01 04:00:00
segment | Gratlinie Outdoor | All subscribers | true | 60 | {"all":[{"field":"marketing_opt_in","op" | 2025-12-31 02:17:00
cohort | Cragsley Outfitters | Signed up 2024-07 | false | 59 | {"signed_up_from":"2024-07-01","signed_u | 2024-08-01 04:00:00
segment | Wiesgrund Wohnen | All subscribers | true | 59 | {"all":[{"field":"marketing_opt_in","op" | 2025-12-31 02:17:00
segment | Marrowby & Vane | New customers (30 days) | true | 58 | {"all":[{"field":"first_order_days_ago", | 2025-12-31 02:17:00
cohort | Cragsley Outfitters | Signed up 2024-03 | false | 57 | {"signed_up_from":"2024-03-01","signed_u | 2024-04-01 04:00:00
cohort | Cragsley Outfitters | Signed up 2025-03 | false | 57 | {"signed_up_from":"2025-03-01","signed_u | 2025-04-01 04:00:00
cohort | Cragsley Outfitters | Signed up 2025-06 | false | 56 | {"signed_up_from":"2025-06-01","signed_u | 2025-07-01 04:00:00
cohort | Cragsley Outfitters | Signed up 2025-08 | false | 56 | {"signed_up_from":"2025-08-01","signed_u | 2025-09-01 04:00:00
cohort | Studio Vrij | Signed up 2025-05 | false | 56 | {"signed_up_from":"2025-05-01","signed_u | 2025-06-01 04:00:00
cohort | Cragsley Outfitters | Signed up 2023-04 | false | 55 | {"signed_up_from":"2023-04-01","signed_u | 2023-05-01 04:00:00
segment | Cinder Peak Sports Co. | Lapsed 90 days | true | 54 | {"all":[{"field":"last_order_days_ago"," | 2025-12-31 02:17:00
cohort | Cragsley Outfitters | Signed up 2023-03 | false | 54 | {"signed_up_from":"2023-03-01","signed_u | 2023-04-01 04:00:00
cohort | Cragsley Outfitters | Signed up 2024-06 | false | 54 | {"signed_up_from":"2024-06-01","signed_u | 2024-07-01 04:00:00
cohort | Cragsley Outfitters | Signed up 2023-10 | false | 53 | {"signed_up_from":"2023-10-01","signed_u | 2023-11-01 04:00:00
(50 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 'segment' AS source, t.name AS tenant_name, s.name AS name, s.is_dynamic AS is_dynamic, s.member_count AS member_count, substring(cast(s.rules AS varchar), 1, 500) AS rules_definition, s.last_refreshed_at AS last_updatedFROM segments sJOIN tenants t ON t.id = s.tenant_idUNION ALLSELECT 'cohort' AS source, t.name AS tenant_name, c.name AS name, c.is_dynamic AS is_dynamic, c.member_count AS member_count, substring(cast(c.definition AS varchar), 1, 500) AS rules_definition, c.computed_at AS last_updatedFROM cohorts cJOIN tenants t ON t.id = c.tenant_idORDER BY member_count DESC, tenant_name ASC, name ASCLIMIT 50ORDER BY member_count DESC NULLS FIRST, tenant_name ASC NULLS LAST, name ASC NULLS LAST, source NULLS LAST, is_dynamic NULLS LAST, rules_definition NULLS LAST, last_updated NULLS LAST{{fetch_first(50)}}
SELECT 'segment' AS source, t.name AS tenant_name, s.name AS name, s.is_dynamic AS is_dynamic, s.member_count AS member_count, substring(cast(s.rules AS varchar), 1, 500) AS rules_definition, s.last_refreshed_at AS last_updated FROM segments s JOIN tenants t ON t.id = s.tenant_id UNION ALL SELECT 'cohort' AS source, t.name AS tenant_name, c.name AS name, c.is_dynamic AS is_dynamic, c.member_count AS member_count, substring(cast(c.definition AS varchar), 1, 500) AS rules_definition, c.computed_at AS last_updated FROM cohorts c JOIN tenants t ON t.id = c.tenant_id ORDER BY member_count DESC NULLS FIRST, tenant_name ASC NULLS LAST, name ASC NULLS LAST, source NULLS LAST, is_dynamic NULLS LAST, rules_definition NULLS LAST, last_updated NULLS LAST FETCH FIRST 50 ROWS ONLY
SELECT 'segment' AS source, t.name AS tenant_name, s.name AS name, s.is_dynamic AS is_dynamic, s.member_count AS member_count, substring(cast(s.rules AS varchar), 1, 500) AS rules_definition, s.last_refreshed_at AS last_updated FROM segments s JOIN tenants t ON t.id = s.tenant_id UNION ALL SELECT 'cohort' AS source, t.name AS tenant_name, c.name AS name, c.is_dynamic AS is_dynamic, c.member_count AS member_count, substring(cast(c.definition AS varchar), 1, 500) AS rules_definition, c.computed_at AS last_updated FROM cohorts c JOIN tenants t ON t.id = c.tenant_id ORDER BY member_count DESC NULLS FIRST, tenant_name ASC NULLS LAST, name ASC NULLS LAST, source NULLS LAST, is_dynamic NULLS LAST, rules_definition NULLS LAST, last_updated NULLS LAST LIMIT 50
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 Yuki Tanaka (analyst for the Head of Data): Title: Existing customer/audience definitions already in use Question: Do any marketing segments or analytics cohorts already encode a definition like 'customers who purchased recently' or 'all customers', so we can certify a customer count the business already uses rather than inventing a new one? Grain: named audience definition (one row per segment or cohort) Columns: - source - tenant_name - name - is_dynamic - member_count - rules_definition - last_updated Details: Population: all rows from the 'segments' table (marketing module) UNION ALL all rows from the 'cohorts' table (analytics module), across all tenants. source = literal 'segment' or 'cohort' depending on origin table. tenant_name = joined from tenants. name = segments.name or cohorts.name. is_dynamic = the respective is_dynamic boolean column. member_count = the respective member_count column. rules_definition = the rules (segments) or definition (cohorts) json column, cast to text (truncate to a reasonable length, e.g. 500 characters, if needed for display). last_updated = segments.last_refreshed_at or cohorts.computed_at. Order by member_count descending, limited to the top 50 rows by member_count. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT 'segment' AS source, t.name AS tenant_name, s.name AS name, s.is_dynamic AS is_dynamic, s.member_count AS member_count, substring(cast(s.rules AS varchar), 1, 500) AS rules_definition, s.last_refreshed_at AS last_updated FROM segments s JOIN tenants t ON t.id = s.tenant_id UNION ALL SELECT 'cohort' AS source, t.name AS tenant_name, c.name AS name, c.is_dynamic AS is_dynamic, c.member_count AS member_count, substring(cast(c.definition AS varchar), 1, 500) AS rules_definition, c.computed_at AS last_updated FROM cohorts c JOIN tenants t ON t.id = c.tenant_id ORDER BY member_count DESC, tenant_name ASC, name ASC LIMIT 50
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: source, tenant_name, name, is_dynamic, member_count, rules_definition, last_updated
submitSELECT 'segment' AS source, t.name AS tenant_name, s.name AS name, s.is_dynamic AS is_dynamic, s.member_count AS member_count, substring(cast(s.rules AS varchar), 1, 500) AS rules_definition, s.last_refreshed_at AS last_updated FROM segments s JOIN tenants t ON t.id = s.tenant_id UNION ALL SELECT 'cohort' AS source, t.name AS tenant_name, c.name AS name, c.is_dynamic AS is_dynamic, c.member_count AS member_count, substring(cast(c.definition AS varchar), 1, 500) AS rules_definition, c.computed_at AS last_updated FROM cohorts c JOIN tenants t ON t.id = c.tenant_id ORDER BY member_count DESC, tenant_name ASC, name ASC LIMIT 50
Submitted to tonight's batch. Your work on this request is done.