What it does
Uses
Only customers with 2+ visits in the epoch window qualify — single-visit customers don't have enough history to project from.
lag() over each customer's repair order history to compute average days between visits, then projects a next visit date. The nextVisit CTE filters to customers whose projected return lands in a 5-day window starting at shouldBeIn.Only customers with 2+ visits in the epoch window qualify — single-visit customers don't have enough history to project from.
CTEs
rewardsBalance
Current balance per customer
credits − debits, familyID scoped, deleted_at excluded
visitIntervals
Days between consecutive visits
lag() over customer_id ordered by updated_at. Excludes company accounts.
nextVisit
Projected return date
avg gap added to last visit. having filters to shouldBeIn window, requires count > 1.
mainQuery
Candidate selection with dedup
Joins repair_orders → customers → reward_cards → rewardsBalance → nextVisit.
Output fields
candidateID
companyID
company
"Name"
"FirstName"
last_name
"FullName"
"Email"
"CustPhone"
"RewardsCard"
"RewardsCardPIN"
"RewardsBalance"
"LastService"
"NextVisit"