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

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • PROMO_STACK_OPTIMIZATION_PATTERN

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • PROMOTION_CANNIBALIZATION

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • SEASONAL_ARBITRAGE_PATTERN

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • HIGH_DISCOUNT_DEPENDENCY_RATIO

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • FILLER_RETURN_MANIPULATION

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • SYNTHETIC_REFERRAL_PATTERN

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No

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.

FunctionReturnsBehaviourForm
MARGIN_DESTRUCTIVE_CUSTOMERlist of signalsCustomers whose revenue, less cost and discounts, is negative over several ordersMARGIN_DESTRUCTIVE_CUSTOMER("ACCOUNT_ID", "ROLLING_90_DAYS")
PROMO_STACK_OPTIMIZATION_PATTERNlist of signalsOrders where combined discounts took a very large share of revenuePROMO_STACK_OPTIMIZATION_PATTERN("ACCOUNT_ID", "ROLLING_30_DAYS")
PROMOTION_CANNIBALIZATIONlist of signalsStretches where more discount spend brought less extra revenue than the discount costPROMOTION_CANNIBALIZATION("ACCOUNT_ID", "ROLLING_365_DAYS")
SEASONAL_ARBITRAGE_PATTERNlist of signalsCustomers who buy heavily on promotion and then refundSEASONAL_ARBITRAGE_PATTERN("ACCOUNT_ID", "ROLLING_180_DAYS")
HIGH_DISCOUNT_DEPENDENCY_RATIOlist of signalsCustomers whose orders almost all carry a discountHIGH_DISCOUNT_DEPENDENCY_RATIO("ACCOUNT_ID", "ROLLING_90_DAYS")
FILLER_RETURN_MANIPULATIONlist of signalsCustomers who pad orders to reach a promotion and then return the filler itemsFILLER_RETURN_MANIPULATION("ACCOUNT_ID", "ROLLING_180_DAYS")
SYNTHETIC_REFERRAL_PATTERNlist of signalsPlaces where many new accounts appear that place one order and never returnSYNTHETIC_REFERRAL_PATTERN("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
MARGIN_DESTRUCTIVE_CUSTOMERCUSTOMERorderCount, totalRevenue, totalDiscount and contributionMargin
PROMO_STACK_OPTIMIZATION_PATTERNCUSTOMERorderId, orderRevenue, orderDiscount and discountRatio
PROMOTION_CANNIBALIZATIONPERIODperiodStartLabel, currentRevenue, currentDiscount, prevRevenue, prevDiscount, revenueDelta, discountDelta and revenuePerDiscountDollar
SEASONAL_ARBITRAGE_PATTERNCUSTOMERdiscountedOrderCount, refundCount, totalDiscount, totalRefundAmount and combinedImpact
HIGH_DISCOUNT_DEPENDENCY_RATIOCUSTOMERtotalOrderCount, discountedOrderCount, dependencyRatio and totalDiscount
FILLER_RETURN_MANIPULATIONCUSTOMERqualifiedOrderCount, totalRefundAmount and avgOrderLineCount
SYNTHETIC_REFERRAL_PATTERNGEO_CLUSTERclusterCountry, 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
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
  • FILLER_RETURN_MANIPULATION and SEASONAL_ARBITRAGE_PATTERN work 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_CANNIBALIZATION compares 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_CANNIBALIZATION before you renew a promotion, and HIGH_DISCOUNT_DEPENDENCY_RATIO before you send a coupon.
  • A customer in both MARGIN_DESTRUCTIVE_CUSTOMER and HIGH_DISCOUNT_DEPENDENCY_RATIO is a clear case to review.
  • Treat the signals as a worklist for a person, not a block list.