Refund signal functions
Find refunds that look like abuse: customers who refund most of what they buy, refunds that turn gift cards into cash, refunds that arrive long after the sale or go somewhere new, groups of accounts refunding together, and the staff whose sales come back most.
Syntax
Write the function's name and its arguments. 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. An optional location at the end limits the answer to one location.
HIGH_REFUND_VELOCITY(accountId, period, [periodEnd], [locationId])
GIFT_CARD_REFUND_MISMATCH(accountId, period, [periodEnd], [locationId])
REFUND_AFTER_CONSUMPTION_WINDOW(accountId, period, [periodEnd], [locationId])
LOYALTY_EXPLOIT_REFUND(accountId, period, [periodEnd], [locationId])
MULTI_ACCOUNT_REFUND_PATTERN(accountId, period, [periodEnd], [locationId])
REFUND_TO_DIFFERENT_INSTRUMENT(accountId, period, [periodEnd], [locationId])
REFUND_BURST_WINDOW_ANOMALY(accountId, period, [periodEnd], [locationId])
REFUND_AFTER_PROFILE_MODIFICATION(accountId, period, [periodEnd], [locationId])
REFUND_APPROVAL_OUTLIER(accountId, period, [periodEnd], [locationId])Parameters and arguments
Arguments are written in brackets, separated by commas. An argument in square brackets is optional.
HIGH_REFUND_VELOCITY
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdGIFT_CARD_REFUND_MISMATCH
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdREFUND_AFTER_CONSUMPTION_WINDOW
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdLOYALTY_EXPLOIT_REFUND
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdMULTI_ACCOUNT_REFUND_PATTERN
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdREFUND_TO_DIFFERENT_INSTRUMENT
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdREFUND_BURST_WINDOW_ANOMALY
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdREFUND_AFTER_PROFILE_MODIFICATION
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdREFUND_APPROVAL_OUTLIER
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationId
Examples
Customers whose refunds are a large share of their orders, with at least a few refunds, over the last 90 days.
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 |
|---|---|---|---|
HIGH_REFUND_VELOCITY | list of signals | Customers whose refunds are a large share of their orders | HIGH_REFUND_VELOCITY("ACCOUNT_ID", "ROLLING_90_DAYS") |
GIFT_CARD_REFUND_MISMATCH | list of signals | Gift card orders refunded back as cash | GIFT_CARD_REFUND_MISMATCH("ACCOUNT_ID", "ROLLING_90_DAYS") |
REFUND_AFTER_CONSUMPTION_WINDOW | list of signals | Refunds issued long after the sale, past the usual return window | REFUND_AFTER_CONSUMPTION_WINDOW("ACCOUNT_ID", "ROLLING_90_DAYS") |
LOYALTY_EXPLOIT_REFUND | list of signals | Refunds to customers who already carry fraud warnings | LOYALTY_EXPLOIT_REFUND("ACCOUNT_ID", "ROLLING_90_DAYS") |
MULTI_ACCOUNT_REFUND_PATTERN | list of signals | Places where several accounts at the same address are all refunding | MULTI_ACCOUNT_REFUND_PATTERN("ACCOUNT_ID", "ROLLING_90_DAYS") |
REFUND_TO_DIFFERENT_INSTRUMENT | list of signals | Refunds routed to a different country or postcode than the order | REFUND_TO_DIFFERENT_INSTRUMENT("ACCOUNT_ID", "ROLLING_90_DAYS") |
REFUND_BURST_WINDOW_ANOMALY | list of signals | Days when refunds ran far above the window's daily average | REFUND_BURST_WINDOW_ANOMALY("ACCOUNT_ID", "ROLLING_90_DAYS") |
REFUND_AFTER_PROFILE_MODIFICATION | list of signals | Refunds to customers whose account carries warnings, the shape of an account takeover | REFUND_AFTER_PROFILE_MODIFICATION("ACCOUNT_ID", "ROLLING_90_DAYS") |
REFUND_APPROVAL_OUTLIER | list of signals | Staff whose orders are refunded far more often than the account's average | REFUND_APPROVAL_OUTLIER("ACCOUNT_ID", "ROLLING_90_DAYS") |
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 |
What each function detects and its evidence
| Function | Entity | Evidence fields |
|---|---|---|
HIGH_REFUND_VELOCITY | CUSTOMER | refundCount, orderCount, refundRate and totalRefundAmount |
GIFT_CARD_REFUND_MISMATCH | CUSTOMER | orderId, refundId, refundAmount, gcLineTotal and gcRedemptionCount |
REFUND_AFTER_CONSUMPTION_WINDOW | CUSTOMER | orderId, refundId, daysElapsed and refundAmount. The later the refund, the higher the confidence |
LOYALTY_EXPLOIT_REFUND | CUSTOMER | orderId, refundId, refundAmount, fraudWarnCount, totalOrders and lifetimeValue |
MULTI_ACCOUNT_REFUND_PATTERN | GEO_CLUSTER | clusterCountry, clusterState, clusterZip, refundingCustomerCount, totalRefundAmount and totalOrderCount. entityId joins country, state and postcode with ` |
REFUND_TO_DIFFERENT_INSTRUMENT | CUSTOMER | orderId, refundId, orderCountry, orderZip, refundCountry, refundZip and refundAmount. A different country weighs more than a different postcode in the same country |
REFUND_BURST_WINDOW_ANOMALY | TENANT | burstDate, dailyRefundCount, dailyRefundAmount, avgDailyRefundCount and burstMultiple |
REFUND_AFTER_PROFILE_MODIFICATION | CUSTOMER | orderId, refundId, fraudWarnCount and refundAmount. The account's warning count stands in for profile changes |
REFUND_APPROVAL_OUTLIER | STAFF | orderCount, refundCount, totalRefunded, staffRefundRate, tenantAvgRefundRate and rateMultiple |
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
- The refund and cluster summaries are monthly, so a window is widened to whole months for the functions that read them.
- Payment method details are not held, so
REFUND_TO_DIFFERENT_INSTRUMENTcompares the geography of the refund with the geography of the order as a stand in. REFUND_AFTER_PROFILE_MODIFICATIONandLOYALTY_EXPLOIT_REFUNDrely on the fraud warning count kept on the customer.REFUND_APPROVAL_OUTLIERfollows your fraud sensitivity setting, so the same data can flag more staff at a higher sensitivity.
Permissions and security
These functions read refund, customer, order, staff and daily summaries (the signal summaries built from them). 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
- Run the refund signals together for the same window and look for customers who appear in more than one.
- Use
REFUND_BURST_WINDOW_ANOMALYto tell a campaign or a defect (many customers) from abuse (a few). - Pass a location when stores have different policies.