Definitions

The definitions the agent and Ask both run on. Each has an owner, a version, SQL and tests. Edits make a new version.

Onboarded merchant

onboarded_merchant unit: companies no canonical variant: Ask returns all 3

When onboarding is finished. Growth closes it when the menu is mapped, Ops when the first order arrives, Finance when the first invoice goes out. All three count merchants that have not churned, as of the time asked.

growthCGO · growth v1

The merchant's menu is mapped and its branches are configured (onboarded_at is set).

latest change: First version.

pass count_betweenobserved 1746, expected between 1500.0 and 1850.0
pass uniqueobserved {'duplicates': 0}, expected merchant_id unique

SQL, rendered for now

SELECT m.id AS merchant_id, m.name, mk.code AS market, m.segment, m.contract_signed_at, m.onboarded_at,
       m.first_order_at
FROM merchants m
LEFT JOIN markets mk ON mk.id = m.market_id
WHERE m.onboarded_at <= TIMESTAMP 'Fri 23:00:00'
  AND (m.churned_at IS NULL OR m.churned_at > TIMESTAMP 'Fri 23:00:00')
ORDER BY m.id

opsCOO · ops v1

The merchant's first order has arrived; a mapped menu with no orders is still onboarding.

latest change: First version.

pass count_betweenobserved 1646, expected between 1400.0 and 1800.0
pass uniqueobserved {'duplicates': 0}, expected merchant_id unique

SQL, rendered for now

SELECT m.id AS merchant_id, m.name, mk.code AS market, m.segment, m.onboarded_at, m.first_order_at
FROM merchants m
LEFT JOIN markets mk ON mk.id = m.market_id
WHERE m.first_order_at <= TIMESTAMP 'Fri 23:00:00'
  AND (m.churned_at IS NULL OR m.churned_at > TIMESTAMP 'Fri 23:00:00')
ORDER BY m.id

financeCFO · finance v1

The merchant has been sent its first invoice for a positive amount.

latest change: First version.

pass count_betweenobserved 1637, expected between 1300.0 and 1850.0
pass uniqueobserved {'duplicates': 0}, expected merchant_id unique

SQL, rendered for now

WITH first_invoice AS (
  SELECT merchant_id, min(issued_at) AS first_invoiced_at
  FROM invoices
  WHERE amount > 0 AND issued_at <= TIMESTAMP 'Fri 23:00:00'
  GROUP BY merchant_id
)
SELECT m.id AS merchant_id, m.name, mk.code AS market, m.segment, m.onboarded_at, f.first_invoiced_at
FROM merchants m
JOIN first_invoice f ON f.merchant_id = m.id
LEFT JOIN markets mk ON mk.id = m.market_id
WHERE m.churned_at IS NULL OR m.churned_at > TIMESTAMP 'Fri 23:00:00'
ORDER BY m.id

History

append-only; nothing is edited in place

definitionCFO finance v1 First version.
definitionCGO growth v1 First version.
definitionCOO ops v1 First version.