Anomaly and economic signal functions
Find the customers, orders and months that do not make economic sense, and the records that sit far from the rest.
Syntax
Write the function's name and its arguments. For the six economic signals, the period is written one of three ways: a date window name such as ROLLING_90_DAYS (the date window functions), a start date and an end date such as "2026-01-01", "2026-03-31", or an accounting period id. They also accept a location at the end.
LEAKAGE_SCORING and VECTOR_ANOMALIES take their own arguments, listed below.
LEAKAGE_SCORING(input)
VECTOR_ANOMALIES(entityType, k)
NEGATIVE_CONTRIBUTION_CUSTOMER(accountId, period, [periodEnd], [locationId])
DISCOUNT_TO_REVENUE_RATIO_OUTLIER(accountId, period, [periodEnd], [locationId])
REFUND_RATE_DEVIATION(accountId, period, [periodEnd], [locationId])
HIGH_SUPPORT_COST_PER_REVENUE(accountId, period, [periodEnd], [locationId])
LTV_BELOW_CAC_THRESHOLD(accountId, period, [periodEnd], [locationId])
CONTRIBUTION_MARGIN_COLLAPSE(accountId, period, [periodEnd], [locationId])Parameters and arguments
Arguments are written in brackets, separated by commas. An argument in square brackets is optional.
LEAKAGE_SCORING
Required: YesinputVECTOR_ANOMALIES
Required: YesentityType
Required: YeskNEGATIVE_CONTRIBUTION_CUSTOMER
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdDISCOUNT_TO_REVENUE_RATIO_OUTLIER
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdREFUND_RATE_DEVIATION
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdHIGH_SUPPORT_COST_PER_REVENUE
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdLTV_BELOW_CAC_THRESHOLD
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdCONTRIBUTION_MARGIN_COLLAPSE
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationId
Examples
Up to 100 orders whose margin is under 5 per cent, worst first.
Return type and behaviour
Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
LEAKAGE_SCORING | record | Up to 100 orders whose margin is below a threshold, lowest margin first | LEAKAGE_SCORING('{"referenceId": "weekly-review"}') |
VECTOR_ANOMALIES | list of records | The records furthest from the average of their kind | VECTOR_ANOMALIES("product", 10) |
NEGATIVE_CONTRIBUTION_CUSTOMER | list of signals | Customers whose lifetime margin after discounts and refunds is negative | NEGATIVE_CONTRIBUTION_CUSTOMER("ACCOUNT_ID", "ROLLING_90_DAYS") |
DISCOUNT_TO_REVENUE_RATIO_OUTLIER | list of signals | Customers whose discount to revenue ratio is far above the account's top tenth | DISCOUNT_TO_REVENUE_RATIO_OUTLIER("ACCOUNT_ID", "ROLLING_90_DAYS") |
REFUND_RATE_DEVIATION | list of signals | Customers whose refund rate is statistically far above the account's average | REFUND_RATE_DEVIATION("ACCOUNT_ID", "ROLLING_90_DAYS") |
HIGH_SUPPORT_COST_PER_REVENUE | list of signals | Customers whose refunds and chargebacks are a large share of their revenue | HIGH_SUPPORT_COST_PER_REVENUE("ACCOUNT_ID", "ROLLING_90_DAYS") |
LTV_BELOW_CAC_THRESHOLD | list of signals | Customers acquired in the period whose lifetime value is below what it cost to acquire a customer | LTV_BELOW_CAC_THRESHOLD("ACCOUNT_ID", "ROLLING_90_DAYS") |
CONTRIBUTION_MARGIN_COLLAPSE | list of signals | Months where contribution margin fell sharply against the month before | CONTRIBUTION_MARGIN_COLLAPSE("ACCOUNT_ID", "ROLLING_365_DAYS") |
LEAKAGE_SCORING answers
| Field | Type | Meaning |
|---|---|---|
ok | true or false | True when the call worked |
tenantId | text | Your account |
leakyOrders | list of records | orderId, revenue, cogs, gatewayFees, refunds, netProfit (money) and marginPercent (a percent number), lowest margin first, at most 100 |
threshold | percent number | The margin limit used |
count | whole number | How many orders were listed |
referenceId | text | The reference you passed |
VECTOR_ANOMALIES answers a list, furthest first
| Field | Type | Meaning |
|---|---|---|
gid | text | The record's id |
entityType | text | The kind of record |
label | text | The record's label |
anomalyScore | number | Its distance from the average of its kind. Larger means more unusual |
Every signal function answers a list of signal rows, strongest first
| Field | Type | Meaning |
|---|---|---|
entityId | text | The customer, staff member, geographic cluster, period or account the signal is about |
entityType | text | CUSTOMER, STAFF, GEO_CLUSTER, PERIOD or TENANT |
signalType | text | The signal's name in words, such as HighRefundVelocity |
severity | text | LOW, MEDIUM, HIGH or CRITICAL, graded from the size of the exposure |
confidenceScore | text | A decimal between 0 and 1 written as text. It grows with the amount of evidence behind the signal |
impactAmount | money | The money exposed by the signal, with its currency |
detectedAt | text | When the signal was found |
evidence | record | The facts the signal rests on. The fields differ for each function and are listed below |
Evidence for each economic signal
| Function | Entity | Evidence fields |
|---|---|---|
NEGATIVE_CONTRIBUTION_CUSTOMER | CUSTOMER | orderCount, grossMargin, totalRefund and netMargin |
DISCOUNT_TO_REVENUE_RATIO_OUTLIER | CUSTOMER | totalRevenue, totalDiscount, discountRatio, tenantP90Ratio and multiple, how many times the account's tenth percentile the customer's ratio is |
REFUND_RATE_DEVIATION | CUSTOMER | orderCount, refundCount, refundRate, tenantMeanRate, tenantStddevRate, zScore (how many standard deviations above the mean) and totalRefund |
HIGH_SUPPORT_COST_PER_REVENUE | CUSTOMER | totalRevenue, totalRefund, totalChargeback, supportCost and supportCostRatio |
LTV_BELOW_CAC_THRESHOLD | CUSTOMER | lifetimeValue and tenantCac, the average cost to acquire a customer from your campaign spend in the period |
CONTRIBUTION_MARGIN_COLLAPSE | PERIOD | periodLabel, currentMargin, prevMargin, declineAmount and declinePct |
The limits that decide when a signal fires are tuned by the platform and are not part of the contract. An empty list means nothing in the window met the signal's test.
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
Limitations
LEAKAGE_SCORINGalways returns at most 100 orders.VECTOR_ANOMALIEScompares the stored embeddings of one kind of record, so it answers an empty list for a kind with none.- The economic signals read monthly summaries, so a window is widened to whole months.
LTV_BELOW_CAC_THRESHOLDestimates the acquisition cost from campaign spend in the same period, so it is only as good as the campaign costs you record.
Permissions and security
These functions read per order profit, customer and refund summaries, campaign costs and stored embeddings. The caller needs read access to the records named, and nothing is written.
They are governed by the same read access as the tables they read, so no separate grant is needed beyond it.
Best practices
- Treat a signal as a prompt to look, not a verdict: read
evidenceandconfidenceScorefirst. - Run the economic signals on a window of at least a quarter so a month's noise does not dominate.
- Keep
referenceIdmeaningful: it is the only label on a stored review.