Trigger Query Portal/Welcome — SMS
SMS

Welcome to the Loyalty Program

Same query as the Email version — change messageType to 'SMS'. Fires when a customer's loyalty card was activated nDays open business days ago. RSA and rewards URL fields dynamically built from shop config.

Key Parameters
messageTypeSMS
familyIDclient family
shopIDlocation
nDays2 open days back
roadAssistonlyRSA / noRSA / both
Tables
cash_ledgers customers customer_locations reward_cards config_client_award_levels sms_tags config_client_location_hours
Purpose
Orient the new card holder. Establish program value from day one.
Send Time
10:12 AM
What it does
Anchors on the cash_ledger activation record (desc codes 57109 or 57110) to find customers whose card was activated on the open business day that is nDays back from today.

The roadAssist flag filters the candidate set by RSA status. The gotRSA field is an empty string for customers without RSA, so the message template works without branching. Same for gotRewardsURL when the shop has no rewards URL configured in sms_tags.
CTEs
shopSchedule
Open/closed by day of week
From config_client_location_hours
dateSteps / collectDate
Backward date walk to activation target
Counts open days only. collectDate is the resolved match date for cash_ledger created_at.
rewardsBalance
Current balance per customer
credits − debits, familyID scoped, deleted_at excluded
rewardsURL / rsaPhone / warrantyPhone
Shop-level dynamic content tags
Pulled from sms_tags by tag_name. Used to build gotRewardsURL and gotRSA output fields.
mainQuery
Candidate selection with dedup
Anchors on cash_ledgers. roadAssist case applied in where. Dedup by customer_id (SMS) or email (Email).
Output fields
candidateID
companyID
"Company"
"Name"
"FirstName"
last_name
"FullName"
"Email"
"CustPhone"
"RewardsCard"
"RewardsBalance"
"RewardsPercentage"
"RSA"
"AwardName"
"gotRSA"
"gotRewardsURL"
"ActivationDate"