What it does
Looks up referrals in
When a customer made multiple referrals in the window,
client_reward_card_referral_logs updated within the last nDays calendar days. For each referring customer, pulls the individual amount, cumulative total, and the referred customer's formatted name.When a customer made multiple referrals in the window,
row_number() ordered by updated_at desc keeps the most recent.
CTEs
rewardsBalance
Current balance per customer
credits − debits, familyID scoped, deleted_at excluded
refAmount
Cumulative referral total per referrer
sum(amount) grouped by referred_from, scoped to shopID
refName
Formatted name of referred customer
Master name formatting applied to referred_to customer record
mainQuery
Candidate selection with dedup
Anchors on referral_logs. Dedup by most recent referral in window.
Output fields
candidateID
companyID
company
"Name"
"FirstName"
last_name
"FullName"
"Email"
"CustPhone"
"RewardsCard"
"RewardsCardPIN"
"RewardsBalance"
"ReferralAmount"
"CumulativeReferralAmount"
"ReferredName"
"ReferralDate"
"CusType"
"CusGrade"