Promotion abuse signal functions
Find the customers and patterns that drain margin through promotions: shoppers who only buy on discount, orders that stack every offer, returns that keep the benefit, and accounts created to claim referral credit.
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.
MARGIN_DESTRUCTIVE_CUSTOMER(accountId, period, [periodEnd], [locationId])
PROMO_STACK_OPTIMIZATION_PATTERN(accountId, period, [periodEnd], [locationId])
PROMOTION_CANNIBALIZATION(accountId, period, [periodEnd], [locationId])
SEASONAL_ARBITRAGE_PATTERN(accountId, period, [periodEnd], [locationId])
HIGH_DISCOUNT_DEPENDENCY_RATIO(accountId, period, [periodEnd], [locationId])
FILLER_RETURN_MANIPULATION(accountId, period, [periodEnd], [locationId])
SYNTHETIC_REFERRAL_PATTERN(accountId, period, [periodEnd], [locationId])Parameters and arguments
Arguments are written in brackets, separated by commas. An argument in square brackets is optional.
MARGIN_DESTRUCTIVE_CUSTOMER
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdPROMO_STACK_OPTIMIZATION_PATTERN
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdPROMOTION_CANNIBALIZATION
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdSEASONAL_ARBITRAGE_PATTERN
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdHIGH_DISCOUNT_DEPENDENCY_RATIO
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdFILLER_RETURN_MANIPULATION
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdSYNTHETIC_REFERRAL_PATTERN
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationId
Examples
Customers whose orders almost all carried a discount 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 |
|---|---|---|---|
MARGIN_DESTRUCTIVE_CUSTOMER | list of signals | Customers whose revenue, less cost and discounts, is negative over several orders | MARGIN_DESTRUCTIVE_CUSTOMER("ACCOUNT_ID", "ROLLING_90_DAYS") |
PROMO_STACK_OPTIMIZATION_PATTERN | list of signals | Orders where combined discounts took a very large share of revenue | PROMO_STACK_OPTIMIZATION_PATTERN("ACCOUNT_ID", "ROLLING_30_DAYS") |
PROMOTION_CANNIBALIZATION | list of signals | Stretches where more discount spend brought less extra revenue than the discount cost | PROMOTION_CANNIBALIZATION("ACCOUNT_ID", "ROLLING_365_DAYS") |
SEASONAL_ARBITRAGE_PATTERN | list of signals | Customers who buy heavily on promotion and then refund | SEASONAL_ARBITRAGE_PATTERN("ACCOUNT_ID", "ROLLING_180_DAYS") |
HIGH_DISCOUNT_DEPENDENCY_RATIO | list of signals | Customers whose orders almost all carry a discount | HIGH_DISCOUNT_DEPENDENCY_RATIO("ACCOUNT_ID", "ROLLING_90_DAYS") |
FILLER_RETURN_MANIPULATION | list of signals | Customers who pad orders to reach a promotion and then return the filler items | FILLER_RETURN_MANIPULATION("ACCOUNT_ID", "ROLLING_180_DAYS") |
SYNTHETIC_REFERRAL_PATTERN | list of signals | Places where many new accounts appear that place one order and never return | SYNTHETIC_REFERRAL_PATTERN("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 |
|---|---|---|
MARGIN_DESTRUCTIVE_CUSTOMER | CUSTOMER | orderCount, totalRevenue, totalDiscount and contributionMargin |
PROMO_STACK_OPTIMIZATION_PATTERN | CUSTOMER | orderId, orderRevenue, orderDiscount and discountRatio |
PROMOTION_CANNIBALIZATION | PERIOD | periodStartLabel, currentRevenue, currentDiscount, prevRevenue, prevDiscount, revenueDelta, discountDelta and revenuePerDiscountDollar |
SEASONAL_ARBITRAGE_PATTERN | CUSTOMER | discountedOrderCount, refundCount, totalDiscount, totalRefundAmount and combinedImpact |
HIGH_DISCOUNT_DEPENDENCY_RATIO | CUSTOMER | totalOrderCount, discountedOrderCount, dependencyRatio and totalDiscount |
FILLER_RETURN_MANIPULATION | CUSTOMER | qualifiedOrderCount, totalRefundAmount and avgOrderLineCount |
SYNTHETIC_REFERRAL_PATTERN | GEO_CLUSTER | clusterCountry, clusterZip, newCustomerCount, avgLifetimeValue and avgTotalOrders |
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
FILLER_RETURN_MANIPULATIONandSEASONAL_ARBITRAGE_PATTERNwork from a proxy: orders with several lines and a partial refund, or heavily discounted orders followed by a refund. They show a pattern, not intent.PROMOTION_CANNIBALIZATIONcompares consecutive stretches, so it needs a window of at least two of them.- Summaries behind the customer signals are monthly, so a window is widened to whole months.
Permissions and security
These functions read customer discount, refund and order summaries, order lines and monthly period summaries. 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
- Read
PROMOTION_CANNIBALIZATIONbefore you renew a promotion, andHIGH_DISCOUNT_DEPENDENCY_RATIObefore you send a coupon. - A customer in both
MARGIN_DESTRUCTIVE_CUSTOMERandHIGH_DISCOUNT_DEPENDENCY_RATIOis a clear case to review. - Treat the signals as a worklist for a person, not a block list.