Synced objects: accounts, loans, users, campaign membership
The query below, on a recurring schedule
One row per recipient, LO and rate attached
Every email arrives from the recipient’s own LO
A custom junction object: this person, in this campaign, owned by this LO. Everything else hangs off it.
Scopes rows to Campaign_Key__c = 'REFINANCE_OPPORTUNITIES' — one query pattern, reusable across campaigns.
The recipient: name, email, and role flags (borrower, co-borrower, realtor).
The owning LO’s name and email for the sender profile, chained to their Contact record for the NMLS ID compliance requires.
A custom per-LO, per-campaign opt-in switch. Opted_In__c = 'true' means marketing never sends on an LO’s behalf without their say-so.
ROW_NUMBER() partitioned by account, ordered by close date, keeps only each client’s most recent closed loan — and the LEFT keeps co-borrowers without their own loan record in the audience.
SELECT
a.PersonContactId AS 'ContactId',
a.FirstName,
a.PersonEmail,
a.Name,
u.Id AS 'OwnerId',
u.Name AS 'Reply Name', -- ④ feeds the dynamic sender profile
u.Email AS 'Reply Email', -- ④
ct.NMLS_ID__c AS 'NMLS',
a.Is_Borrower__c,
a.Is_Co_Borrower__c,
a.Is_Realtor__c,
l.Loan_Rate__c -- ③ rate, for segmentation
FROM Marketing_Cloud_Campaign_Member__c_Salesforce m
JOIN Marketing_Cloud_Campaign__c_Salesforce c
ON m.Marketing_Cloud_Campaign__c = c.Id
JOIN Account_Salesforce a
ON m.Person_Account__c = a.Id
LEFT JOIN User_Salesforce u
ON m.Owning_LO__c = u.Id
LEFT JOIN Contact_Salesforce ct
ON u.ContactId = ct.Id
JOIN MC_Campaign_Toggle__c_Salesforce t -- ① LO opt-in gate
ON t.User__c = m.Owning_LO__c
AND t.Campaign__c = m.Marketing_Cloud_Campaign__c
LEFT JOIN ( -- ② latest closed loan only
SELECT
Account__c,
Loan_Rate__c,
ROW_NUMBER() OVER (PARTITION BY Account__c
ORDER BY Loan_Closed__c DESC) AS rn
FROM Loan__c_Salesforce
WHERE Loan_Closed__c IS NOT NULL
) l
ON l.Account__c = a.Id
AND l.rn = 1
WHERE c.Campaign_Key__c = 'REFINANCE_OPPORTUNITIES'
AND m.Audience_Active__c = 'true' -- ⑤ eligibility flags
AND m.Send_Eligible__c = 'true' -- ⑤
AND t.Opted_In__c = 'true' -- ①Opted_In__c means every send is opt-in at the Loan Originator level — the audience assembles itself only for LOs who raised their hand for this campaign.Loan__c row) in the send with a null rate rather than silently dropping them.Audience_Active__c and Send_Eligible__c are maintained upstream on the membership object, keeping suppression logic (active-loan status, recency windows) out of every campaign query instead of duplicated across them. Earlier iterations also gated entry until a loan cleared its ~6-month seasoning period; that logic later moved upstream with the rest.From Name: %%Reply Name%%
From Email: %%=Concat(Substring(AttributeValue('Reply Email'), 1,
Subtract(IndexOf(AttributeValue('Reply Email'), '@'), 1)),
'@email.umortgage.com')=%%
Reply-To: %%Reply Name%% <%%Reply Email%%>
It takes the localpart of the LO’s real address and rebuilds it on @email.umortgage.com — so it aligns with the Sender Authentication Package’s authenticated domain (SPF/DKIM pass), mimics the LO’s real address (gmays@umortgage.com renders as gmays@email.umortgage.com), and keeps the corporate @umortgage.com domain’s reputation insulated from bulk sending. Replies still route to the LO’s real inbox.