What it does
Calculates each customer's projected next visit by averaging the gaps between their past visits using a
Only customers with a balance ≥
lag() window function — then filters to customers whose projected return date lands in a 5-day window starting at shouldBeIn (4 weeks out).Only customers with a balance ≥
minBalance and an active rewards card qualify. The reminder gives them a reason to think about their next visit before it's due.
The
visitIntervals CTE replaced the old self-join on repair_orders. The lag() approach computes the same result in a fraction of the time.CTEs
rewardsBalance
Current balance per customer
credits − debits from cash_ledgers, familyID scoped, deleted_at excluded
visitIntervals
Days between each consecutive visit
lag() over customer_id ordered by updated_at. Excludes company accounts.
nextVisit
Projected return date per customer
avg of visit gaps added to last visit date. having filters to shouldBeIn window and requires >1 visit.
mainQuery
Candidate selection with dedup
Joins repair_orders → customers → reward_cards → rewardsBalance → nextVisit. Applies internal_completed_at logic.
Output fields
candidateID
companyID
company
"Name"
"FirstName"
last_name
"FullName"
"Email"
"CustPhone"
"RewardsCard"
"RewardsCardPIN"
"RewardsBalance"
"LastService"
"NextVisit"