Trigger Query Portal/Welcome — Email
Email

Welcome to the Loyalty Program

Fires when a customer's loyalty card was activated exactly nDays open business days ago. Roadside assistance and rewards URL fields are dynamically included based on card type and shop configuration.

Key Parameters
messageTypeEmail
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
Targets customers whose loyalty card activation date (pulled from cash_ledgers desc codes 57109 or 57110) falls on the open business day that is nDays back from today.

The roadAssist flag controls which customers receive the message: onlyRSA sends only to customers with roadside assistance, noRSA excludes them, both sends to all. The gotRSA and gotRewardsURL fields are dynamically built from sms_tags and the card's road_assist flag — empty string if not applicable.

Dedup is by customer_id (SMS) or email (Email) via row_number(). The final filter "FirstName" <> last_name removes malformed records.
CTEs
shopSchedule
Open/closed by day of week
From config_client_location_hours for the target shop
dateSteps
Recursive backward date walk
Counts only open days stepping back from today
collectDate
Resolved target activation date
The open business day nDays back — matched against cash_ledger created_at
rewardsBalance
Current balance per customer
credits − debits, familyID scoped, deleted_at excluded
rewardsURL / rsaPhone
Shop-level dynamic content
Pulled from sms_tags by tag_name. Used to build gotRewardsURL and gotRSA fields.
mainQuery
Candidate selection with dedup
Anchors on cash_ledgers activation date. roadAssist filter applied in where clause.
Output fields
candidateID
companyID
"Company"
"Name"
"FirstName"
last_name
"FullName"
"Email"
"CustPhone"
"RewardsCard"
"RewardsBalance"
"RewardsPercentage"
"RSA"
"AwardName"
"gotRSA"
"gotRewardsURL"
"ActivationDate"