What it does
Targets customers whose most recent completed RO falls in the
Gift and referral amounts cascade: shop-level config checked first, falls back to family-level, then hardcoded $20 default. Both amounts are formatted as dollar strings in the output.
The
fromDate to toDate window (a 4-day span ending nDays calendar days ago). The status flag controls whether the visit count check uses >= (true) or = (false) against afterVisit.Gift and referral amounts cascade: shop-level config checked first, falls back to family-level, then hardcoded $20 default. Both amounts are formatted as dollar strings in the output.
The
internal_completed_at dual condition handles the August 2025 data gap on repair_orders.
CTEs
giftAmntShop / referAmntShop
Shop-level referral amounts
From config_referrals_client_location for the target shopID
giftAmntFamily / referAmntFamily
Family-level fallback amounts
From config_referrals_client for the familyID. Used when shop-level is null.
rewardsBalance
Current balance per customer
credits − debits, familyID scoped, deleted_at excluded
mainQuery
Candidate selection with dedup
Anchors on repair_orders. having applies visit count logic and date window. Amount cascade resolved in select.
Output fields
candidateID
companyID
company
"Name"
"FirstName"
last_name
"FullName"
"Email"
"CustPhone"
"RewardsCard"
"RewardsCardPIN"
"RewardsBalance"
"LastService"
"Visits"
"GiftAmnt"
"ReferAmnt"