Plan · 2026-05-19

SFMC Bounce-Writeback Rollup

SFMC is already auto-holding hard-bouncers — sender reputation is safe. But 19,388 non-sendable subscribers (11.9% of the renewal count) still sit on the bill. One DLRS rollup + one formula change stops the bleed and shrinks the count.

Build-ready Zero new code Under 30 min build DLRS · declarative
19,388
Non-sendable subscribers in SFMC
11.9% of 163,303 — held + bounced + unsub
10,620
Hard-bouncing patients in Salesforce
Drop these from sync — day-1 cleanup
~30 min
Declarative build
No Apex, no test classes

§1The gap, in one diagram

SFMC has been writing bounce events into Salesforce for years. The patient Contact record was supposed to receive an aggregated signal — that wiring was never built.

SFMC → Salesforce IER (works)
43,433
Bounce records in et4ae5__IndividualEmailResult__c
→
IER → Contact (missing)
0
Contacts with SFMCEmailBounceDate__c populated

Production SOQL run by Kyle 2026-05-19. The destination fields exist on Contact already (with the description "set via flow based on IER data"). Nobody ever built the populator. The 2026-05-19 audit of the entire et4ae5 managed-package namespace (13 flows + 14 Apex triggers) confirmed no built-in mechanism does this work — nothing existing to step on.

§2Why fix this now

The real story isn't deliverability — SFMC already protects sender reputation by auto-holding hard-bouncers. The real story is billing.

SFMC _Subscribers snapshot (run 2026-05-19)

Subscriber state Count % of total Billable? Sendable?
active 143,915 88.1% Yes ✓ Yes
held (SFMC auto-paused) 7,541 4.6% Yes ✗ No
bounced (recent, pre-held) 9,304 5.7% Yes ✗ No
unsubscribed 2,543 1.6% Yes ✗ No
Total non-sendable 19,388 11.9% Yes — bill applies —

Source: SFMC Query Activity run 2026-05-19 against the _Subscribers system view. The 19,388 non-sendable subscribers are counted in renewal pricing but generate zero send value. This is the value-prop — not deliverability rescue.

💰
Billing-count reduction (PRIMARY)
SFMC pricing scales with subscriber count. 19,388 of the 163,303 subscribers in the renewal count are non-sendable — SFMC won't even attempt to deliver. The rollup + sync-filter update lets us drop hard-bouncing patients from the SF→SFMC pipeline so the count shrinks at the source.
🚰
Stop the inflow
Without a SF-side bounce gate, every newly-bouncing patient still flows: Contact syncs → SFMC sends → SFMC marks held → AdvancedRx keeps paying. The held population grows monotonically. Gating sync on the bounce rollup closes that loop.
🧑‍⚕️
Patient experience
A patient whose email bounced misses receipts, refill reminders, and welcome-series content. And they can't use the patient portal — portal access depends on a working email. Surfacing the bounce on Contact lets Customer Service catch the broken email on the next call-in.
What about sender reputation? Earlier drafts of this plan led with sender-reputation hygiene ("we're sending to dead addresses, hurting deliverability for the patients we CAN reach"). The 2026-05-19 _Subscribers check resolved that concern — SFMC's auto-hold is doing its job: 7,541 subscribers in held are not being sent to. So reputation isn't actively eroding from our side. The fix here is operational/financial, not deliverability rescue.

§3How the rollup works

DLRS counts each hard bounce up to its parent Contact and stamps the most-recent bounce date onto a custom Contact field. Mirrors the only existing rollup on the same parent object (Contact_Most_Recent_TC_Sent).

Source
IER record
et4ae5__HardBounce__c = TRUE
43K records, growing daily
→
Mechanism
DLRS managed package
Max Realtime
same pattern as the existing TC-Sent rollup
→
Target
Contact
SFMCEmailBounceDate__c
IsSFMCEmailBounced__c

The two Contact destination fields already exist

SFMCEmailBounceDate__c
Date/Time · custom
The DLRS rollup target. Field description reads "set via flow based on IER data" — the AdvancedRx architect built this intending a populator. The populator was never built. This plan builds it.
IsSFMCEmailBounced__c
Checkbox · custom (→ redefine as Formula)
Same description on the field. Plan redefines as Formula(Checkbox) that compares bounce-date to a new Email_Last_Updated_Date_Time__c field. Lifecycle-aware — self-clears when Customer Service updates the email on the next call-in. See §5.3.

Note: the standard Contact.EmailBouncedDate field is reserved for SF-platform-native bounce tracking (on SF-sent email), not SFMC bounces. Don't repoint there — these AdvancedRx custom fields exist specifically to keep the two channels separate.

§4The rollup spec — Sarah's build sheet

One new rollup record. Sarah opens the DLRS Lightning app (App Launcher → "Lookup Rollup" → "Manage Lookup Rollup Summaries" → New), gets a form with the fields below, types the values from the right column. The form is what the team uses for all 45 existing rollups — same pattern. Each row is one form field.

Field on dlrs__LookupRollupSummary2__mdt Value
Label / MasterLabel Contact Most Recent Hard Bounce
DeveloperName Contact_Most_Recent_Hard_Bounce
dlrs__ParentObject__c Contact
dlrs__ChildObject__c et4ae5__IndividualEmailResult__c
dlrs__RelationshipField__c et4ae5__Contact__c
dlrs__FieldToAggregate__c et4ae5__DateBounced__c
dlrs__AggregateOperation__c Max
dlrs__AggregateResultField__c SFMCEmailBounceDate__c
dlrs__RelationshipCriteria__c et4ae5__HardBounce__c = TRUE
dlrs__RelationshipCriteriaFields__c et4ae5__HardBounce__c
dlrs__CalculationMode__c Realtime
dlrs__Active__c checked

§5Scope decisions

Three design questions surfaced. All resolved to the simpler option for v1; each can be revisited later.

5.1 — Date cutoff: aggregate all 43K, or mirror the existing rollup?
The sibling rollup Contact_Most_Recent_TC_Sent uses a hardcoded DateSent > 2024-09-08T17:30 lower bound. Resolved 2026-05-19 by reading the destination field's description more carefully: TC = Terms & Conditions, and 2024-09-08T17:30 is the timestamp the 2024 T&C version went out. The cutoff is a business-policy boundary ("which patients have seen the current T&C version"), NOT a data-quality boundary. Pre-cutoff bounces are still factually true and should count.
✓ Chosen
Aggregate all 43K bounces
No date filter. A 3-year-old hard-bounce on a never-recovered email is still a dead-email signal. Excluding it would under-flag patients whose only bounce predates 2024-09-08.
Rejected
Mirror the T&C cutoff
Earlier draft proposed this for "consistency" but the existing cutoff is T&C-policy-specific, not a data-quality signal. Mirroring would under-flag without operational benefit.
5.2 — Bounce types: HardBounce only, or both hard and soft?
HardBounce = unrecoverable (mailbox doesn't exist, domain dead). SoftBounce = transient (mailbox full, server timeout); some SoftBounces self-resolve on next send. For staleness flagging, HardBounce is the canonical "give up" signal — every email platform agrees on it.
✓ Chosen
HardBounce only (v1)
Track HardBounce__c = TRUE. False negatives are easier to revisit than false positives — better to under-flag than drop a real patient from communications.
v2
SoftBounce-aware logic
Either OR the criteria or build a soft-bounce-count rollup with an N-in-30-days threshold. Revisit if sales/CS reports patients with months of soft bounces still getting sends.
5.3 — Email-change lifecycle: how does the bounce flag clear when Customer Service updates the patient's email on a call-in?
The DLRS rollup takes Max over ALL HardBounce IER records for a Contact — including ones tied to an old email address. If we gate the cleanup formula on SFMCEmailBounceDate__c != null directly, a patient whose old@email.com bounced 6 months ago stays dropped from sync forever — even after Customer Service updates them to a working new@email.com. The flag has no way to forget.
✓ Chosen
Track email-update timestamp + lifecycle-aware Boolean
Add Contact.Email_Last_Updated_Date_Time__c populated by a small flow on ISCHANGED(Email). Redefine IsSFMCEmailBounced__c as Formula(Checkbox) comparing bounce-date to email-update-date. Cleanup formula gates on the Boolean. Self-correcting if the new email also bounces.
Rejected
Modify managed-package IER records
Add a Superseded__c custom field to et4ae5__IndividualEmailResult__c + a flow that updates child IERs when Contact.Email changes. Works but adds DML on managed-package child records + governor-limit risk for high-bounce patients.
The new Boolean formula:
AND( NOT(ISBLANK(SFMCEmailBounceDate__c)), SFMCEmailBounceDate__c >= NULLVALUE( Email_Last_Updated_Date_Time__c, DATETIMEVALUE("2020-01-01 00:00:00") ) ) The NULLVALUE default to a very-old date means existing bouncers (whose email was never explicitly updated post-bounce) still flag correctly on day 1. As soon as Customer Service updates a patient's email on the next call-in, the formula transitions to comparing against the actual update timestamp.

§5bLifecycle walkthrough — how the boolean self-corrects

Three steps trace a hard-bouncing patient through the lifecycle. Each row is a state snapshot.

Step SFMCEmailBounceDate__c Email_Last_Updated_Date_Time__c IsSFMCEmailBounced__c
1. Patient's bob@old.com bounces 2026-04-10 2026-04-10 null (or older) TRUE
2. Patient calls in for refill, CS gets new email, updates to bob@new.com 2026-05-19 2026-04-10 (unchanged — factual) 2026-05-19 14:00 FALSE
3. New email also bounces 2026-06-01 (worst case) 2026-06-01 2026-05-19 14:00 TRUE

The cleanup formula gates on NOT(IsSFMCEmailBounced__c), so the patient drops out of sync at step 1, comes back at step 2, drops again at step 3. The Date field stays factually true throughout — useful for audit / history but not the right field to gate behavior on.

§6Build steps — under 30 minutes

Declarative. No source files to author by hand unless Sarah prefers metadata-source-based deploy.

✓ Sync filter verified — target field is Sync_to_Marketing_Cloud__c
Kyle verified in Contact Builder 2026-05-19. MC Connect's filter expression is Sync_to_Marketing_Cloud__c equal (=) True, poll schedule every 15 minutes, 177,044 records currently synced. Note the field name is "to" not "with" — earlier plan drafts targeted the wrong field. See §4.4.
Create Email_Last_Updated_Date_Time__c on Contact
New DateTime field. Read-only for everyone except sysadmin (it's flow-maintained). Add to page layouts.
Backfill from ContactHistory
Without this, day-1 every existing bouncer flags TRUE — including patients whose email was corrected pre-deploy. Backfill Email_Last_Updated_Date_Time__c from ContactHistory.CreatedDate where Field = 'Email', per-Contact MAX. Closes most of the false-positive risk within Salesforce's 18–24 month history retention.
Extend Contact_Insert_Update_Same_Record (BeforeSave)
Same-record stamps belong in the canonical BeforeSave flow, not an AfterSave self-update (recursion risk). Add a decision branch on ISCHANGED(Email), assign $Record.Email_Last_Updated_Date_Time__c = {!$Flow.CurrentDateTime}. No fault connector needed — no DML to fail.
Redefine IsSFMCEmailBounced__c as Formula(Checkbox)
Object Manager → Contact → field → Edit → change type. Formula compares bounce-date to email-update-date (see §5.3). Destructive field-type change but field is empty in prod so no actual loss.
Create the rollup in the DLRS Lightning app
App Launcher → "Lookup Rollup" → "Manage Lookup Rollup Summaries" → New. Field values per §4 above. Same form the team uses for all 45 existing rollups.
Deploy ClaudeTest → seeded sandbox test
Create test Contact + IER records in sandbox. Test the basic rollup behavior + the email-change lifecycle (§5b table). Full test sequence in plan.md §6.1.
Deploy to production · run Calculate
Normal change-set / package process. Schedule Calculate for off-hours. DLRS scans all 1.6M IER records against the criterion before deciding which 43K match — expect a meaningful async-job run, possibly 30+ minutes.
Convert Sync_to_Marketing_Cloud__c to Formula(Checkbox)
Currently a stored Checkbox maintained by a flow. Convert to Formula(Checkbox) with the §8.1 formula — NOT(IsSFMCEmailBounced__c) + deceased + business-approved staleness criteria. Destructive field-type change but no logic lost. Remove the now-redundant flow decision in Contact_Insert_Update_Same_Record.
Verify Contact_Salesforce drop in SFMC
Next MC Connect sync cycle removes newly-excluded Contacts. Run SFMC Contact Delete after; the 14-day soft-delete window means billing-count drop lags by ~2 weeks.

§7Downstream — update the cleanup formula

Once the rollup populates, Contact.Sync_to_Marketing_Cloud__c (the field MC Connect actually filters on — verified 2026-05-19, see §4.4) gets converted from a stored Checkbox to a Formula(Checkbox) with all the gates baked in: 

AND(
  Email <> NULL,                                  // existing behavior preserved
  NOT(IsSFMCEmailBounced__c),                     // NEW — drops 10,620 hard-bouncing patients
  ISBLANK(DeceasedDate),                          // defensive — currently 0 in prod
  ISBLANK(HealthCloudGA__DeceasedDate__c),        // defensive — currently 0 in prod
  ISBLANK(EmailBouncedDate)                       // defensive — currently 0 in prod
)

Production sizing (Kyle ran Developer Console SOQL 2026-05-19): 177,051 Contacts currently sync. 10,620 of them are hard-bouncing. 0 are deceased per SF fields. 0 have SF-native bounces. Post-cleanup: ~166,431 (6% reduction). The defensive ISBLANK gates have zero impact today but guard the future case where those fields start being populated.

No staleness gate in v1 — Kyle decision 2026-05-19. Staleness signals are fuzzy and carry real false-positive risk. Bounces are unambiguous. The 6% drop is meaningful and defensible without business politics around "how stale is too stale."

Gates on the lifecycle-aware Boolean, not the raw Date field. The Boolean self-clears when Customer Service updates a patient's email (per §5.3 + §5b) — so a patient whose old email bounced and got a new working address comes back into sync automatically. If we gated on the Date directly, the patient would stay dropped forever.

§8Verification basis

The numbers throughout this plan are live production data + live schema, not sandbox inference.

Schema: verified via FieldDefinition SOQL on ClaudeTest 2026-05-19. Both custom Contact fields exist with the "set via flow based on IER data" descriptions. The single existing DLRS rollup on et4ae5__IndividualEmailResult__c (Contact_Most_Recent_TC_Sent) verified to exclude bounces via HardBounce__c = FALSE AND SoftBounce__c = FALSE.

Production population: Kyle ran in production Developer Console SQL Workbench 2026-05-19:
  • et4ae5__IndividualEmailResult__c total → 1,635,895
  • HardBounce__c = TRUE OR SoftBounce__c = TRUE → 43,433
  • DateSent__c >= LAST_N_DAYS:30 → 75,429
  • Contact WHERE SFMCEmailBounceDate__c != null → 0
  • Contact WHERE IsSFMCEmailBounced__c = true → 0
Managed-package audit: Tooling API queries against NamespacePrefix = 'et4ae5' on 2026-05-19 surfaced 13 managed flows + 14 managed Apex triggers. All 13 flows fit a housekeeping/lifecycle pattern (record-internal status updates, SendDefinition state, deprecated WFR migrations). The suspect MCC_TrackingAsOfIER is named after the et4ae5__Tracking_As_Of__c field on IER itself — a per-record helper, not a Contact-side aggregator. None of the managed artifacts aggregate to Contact bounce fields.

SFMC subscriber state: Kyle ran a Query Activity against _Subscribers on 2026-05-19 — output DE Subscriber_Status_Check_Output. Query: SELECT Status, COUNT(*) AS cnt FROM _Subscribers WHERE SubscriberKey IS NOT NULL GROUP BY Status. Result: 143,915 active / 7,541 held / 9,304 bounced / 2,543 unsubscribed (163,303 total). Resolved the open question about whether SFMC was auto-holding (it is) and pivoted §2 framing from sender-reputation to billing-count.