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

    input

    Required: Yes
  • VECTOR_ANOMALIES

    entityType

    Required: Yes
  • k

    Required: Yes
  • NEGATIVE_CONTRIBUTION_CUSTOMER

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • DISCOUNT_TO_REVENUE_RATIO_OUTLIER

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • REFUND_RATE_DEVIATION

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • HIGH_SUPPORT_COST_PER_REVENUE

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • LTV_BELOW_CAC_THRESHOLD

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • CONTRIBUTION_MARGIN_COLLAPSE

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No

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.

FunctionReturnsBehaviourForm
LEAKAGE_SCORINGrecordUp to 100 orders whose margin is below a threshold, lowest margin firstLEAKAGE_SCORING('{"referenceId": "weekly-review"}')
VECTOR_ANOMALIESlist of recordsThe records furthest from the average of their kindVECTOR_ANOMALIES("product", 10)
NEGATIVE_CONTRIBUTION_CUSTOMERlist of signalsCustomers whose lifetime margin after discounts and refunds is negativeNEGATIVE_CONTRIBUTION_CUSTOMER("ACCOUNT_ID", "ROLLING_90_DAYS")
DISCOUNT_TO_REVENUE_RATIO_OUTLIERlist of signalsCustomers whose discount to revenue ratio is far above the account's top tenthDISCOUNT_TO_REVENUE_RATIO_OUTLIER("ACCOUNT_ID", "ROLLING_90_DAYS")
REFUND_RATE_DEVIATIONlist of signalsCustomers whose refund rate is statistically far above the account's averageREFUND_RATE_DEVIATION("ACCOUNT_ID", "ROLLING_90_DAYS")
HIGH_SUPPORT_COST_PER_REVENUElist of signalsCustomers whose refunds and chargebacks are a large share of their revenueHIGH_SUPPORT_COST_PER_REVENUE("ACCOUNT_ID", "ROLLING_90_DAYS")
LTV_BELOW_CAC_THRESHOLDlist of signalsCustomers acquired in the period whose lifetime value is below what it cost to acquire a customerLTV_BELOW_CAC_THRESHOLD("ACCOUNT_ID", "ROLLING_90_DAYS")
CONTRIBUTION_MARGIN_COLLAPSElist of signalsMonths where contribution margin fell sharply against the month beforeCONTRIBUTION_MARGIN_COLLAPSE("ACCOUNT_ID", "ROLLING_365_DAYS")

LEAKAGE_SCORING answers

FieldTypeMeaning
oktrue or falseTrue when the call worked
tenantIdtextYour account
leakyOrderslist of recordsorderId, revenue, cogs, gatewayFees, refunds, netProfit (money) and marginPercent (a percent number), lowest margin first, at most 100
thresholdpercent numberThe margin limit used
countwhole numberHow many orders were listed
referenceIdtextThe reference you passed

VECTOR_ANOMALIES answers a list, furthest first

FieldTypeMeaning
gidtextThe record's id
entityTypetextThe kind of record
labeltextThe record's label
anomalyScorenumberIts distance from the average of its kind. Larger means more unusual

Every signal function answers a list of signal rows, strongest first

FieldTypeMeaning
entityIdtextThe customer, staff member, geographic cluster, period or account the signal is about
entityTypetextCUSTOMER, STAFF, GEO_CLUSTER, PERIOD or TENANT
signalTypetextThe signal's name in words, such as HighRefundVelocity
severitytextLOW, MEDIUM, HIGH or CRITICAL, graded from the size of the exposure
confidenceScoretextA decimal between 0 and 1 written as text. It grows with the amount of evidence behind the signal
impactAmountmoneyThe money exposed by the signal, with its currency
detectedAttextWhen the signal was found
evidencerecordThe facts the signal rests on. The fields differ for each function and are listed below

Evidence for each economic signal

FunctionEntityEvidence fields
NEGATIVE_CONTRIBUTION_CUSTOMERCUSTOMERorderCount, grossMargin, totalRefund and netMargin
DISCOUNT_TO_REVENUE_RATIO_OUTLIERCUSTOMERtotalRevenue, totalDiscount, discountRatio, tenantP90Ratio and multiple, how many times the account's tenth percentile the customer's ratio is
REFUND_RATE_DEVIATIONCUSTOMERorderCount, refundCount, refundRate, tenantMeanRate, tenantStddevRate, zScore (how many standard deviations above the mean) and totalRefund
HIGH_SUPPORT_COST_PER_REVENUECUSTOMERtotalRevenue, totalRefund, totalChargeback, supportCost and supportCostRatio
LTV_BELOW_CAC_THRESHOLDCUSTOMERlifetimeValue and tenantCac, the average cost to acquire a customer from your campaign spend in the period
CONTRIBUTION_MARGIN_COLLAPSEPERIODperiodLabel, 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
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
Limitations
  • LEAKAGE_SCORING always returns at most 100 orders.
  • VECTOR_ANOMALIES compares 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_THRESHOLD estimates 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 evidence and confidenceScore first.
  • Run the economic signals on a window of at least a quarter so a month's noise does not dominate.
  • Keep referenceId meaningful: it is the only label on a stored review.