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

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • GIFT_CARD_REFUND_MISMATCH

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • REFUND_AFTER_CONSUMPTION_WINDOW

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • LOYALTY_EXPLOIT_REFUND

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • MULTI_ACCOUNT_REFUND_PATTERN

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • REFUND_TO_DIFFERENT_INSTRUMENT

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • REFUND_BURST_WINDOW_ANOMALY

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • REFUND_AFTER_PROFILE_MODIFICATION

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • REFUND_APPROVAL_OUTLIER

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No

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.

FunctionReturnsBehaviourForm
HIGH_REFUND_VELOCITYlist of signalsCustomers whose refunds are a large share of their ordersHIGH_REFUND_VELOCITY("ACCOUNT_ID", "ROLLING_90_DAYS")
GIFT_CARD_REFUND_MISMATCHlist of signalsGift card orders refunded back as cashGIFT_CARD_REFUND_MISMATCH("ACCOUNT_ID", "ROLLING_90_DAYS")
REFUND_AFTER_CONSUMPTION_WINDOWlist of signalsRefunds issued long after the sale, past the usual return windowREFUND_AFTER_CONSUMPTION_WINDOW("ACCOUNT_ID", "ROLLING_90_DAYS")
LOYALTY_EXPLOIT_REFUNDlist of signalsRefunds to customers who already carry fraud warningsLOYALTY_EXPLOIT_REFUND("ACCOUNT_ID", "ROLLING_90_DAYS")
MULTI_ACCOUNT_REFUND_PATTERNlist of signalsPlaces where several accounts at the same address are all refundingMULTI_ACCOUNT_REFUND_PATTERN("ACCOUNT_ID", "ROLLING_90_DAYS")
REFUND_TO_DIFFERENT_INSTRUMENTlist of signalsRefunds routed to a different country or postcode than the orderREFUND_TO_DIFFERENT_INSTRUMENT("ACCOUNT_ID", "ROLLING_90_DAYS")
REFUND_BURST_WINDOW_ANOMALYlist of signalsDays when refunds ran far above the window's daily averageREFUND_BURST_WINDOW_ANOMALY("ACCOUNT_ID", "ROLLING_90_DAYS")
REFUND_AFTER_PROFILE_MODIFICATIONlist of signalsRefunds to customers whose account carries warnings, the shape of an account takeoverREFUND_AFTER_PROFILE_MODIFICATION("ACCOUNT_ID", "ROLLING_90_DAYS")
REFUND_APPROVAL_OUTLIERlist of signalsStaff whose orders are refunded far more often than the account's averageREFUND_APPROVAL_OUTLIER("ACCOUNT_ID", "ROLLING_90_DAYS")

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

What each function detects and its evidence

FunctionEntityEvidence fields
HIGH_REFUND_VELOCITYCUSTOMERrefundCount, orderCount, refundRate and totalRefundAmount
GIFT_CARD_REFUND_MISMATCHCUSTOMERorderId, refundId, refundAmount, gcLineTotal and gcRedemptionCount
REFUND_AFTER_CONSUMPTION_WINDOWCUSTOMERorderId, refundId, daysElapsed and refundAmount. The later the refund, the higher the confidence
LOYALTY_EXPLOIT_REFUNDCUSTOMERorderId, refundId, refundAmount, fraudWarnCount, totalOrders and lifetimeValue
MULTI_ACCOUNT_REFUND_PATTERNGEO_CLUSTERclusterCountry, clusterState, clusterZip, refundingCustomerCount, totalRefundAmount and totalOrderCount. entityId joins country, state and postcode with `
REFUND_TO_DIFFERENT_INSTRUMENTCUSTOMERorderId, refundId, orderCountry, orderZip, refundCountry, refundZip and refundAmount. A different country weighs more than a different postcode in the same country
REFUND_BURST_WINDOW_ANOMALYTENANTburstDate, dailyRefundCount, dailyRefundAmount, avgDailyRefundCount and burstMultiple
REFUND_AFTER_PROFILE_MODIFICATIONCUSTOMERorderId, refundId, fraudWarnCount and refundAmount. The account's warning count stands in for profile changes
REFUND_APPROVAL_OUTLIERSTAFForderCount, 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
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
  • 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_INSTRUMENT compares the geography of the refund with the geography of the order as a stand in.
  • REFUND_AFTER_PROFILE_MODIFICATION and LOYALTY_EXPLOIT_REFUND rely on the fraud warning count kept on the customer.
  • REFUND_APPROVAL_OUTLIER follows 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_ANOMALY to tell a campaign or a defect (many customers) from abuse (a few).
  • Pass a location when stores have different policies.