--- title: SFMC bounce-writeback rollup description: Aggregate the 43K SFMC bounce records sitting in et4ae5__IndividualEmailResult__c up to Contact.SFMCEmailBounceDate__c via a single new DLRS rollup, so reporting and cleanup formulas finally see bouncing patients. status: Build-ready owner: Kyle updated: 2026-05-19 last_verified: 2026-05-19 verification_type: soql+tooling-api+sfmc-sql verification_ttl_days: 30 --- # SFMC bounce-writeback rollup > **Owner:** Kyle. > **Builder:** Sarah (developer — already has DLRS-on-managed-package experience from the Elite Prescriber rollup). > **Status:** build-ready. Verification complete; no remaining unknowns. > **Build effort:** under 30 minutes of declarative point-and-click setup. One new DLRS rollup record + one optional field-type swap. ## 1. Goal Populate `Contact.SFMCEmailBounceDate__c` (and optionally `Contact.IsSFMCEmailBounced__c`) on every patient whose email has hard-bounced in SFMC. Today both fields are zero everywhere in production despite 43,433 bounce records sitting in `et4ae5__IndividualEmailResult__c` (IER). One DLRS rollup closes the gap. **Why this matters operationally:** - **Billing-count reduction for the SFMC contract renewal (PRIMARY).** SFMC pricing scales with subscriber count. The current `_Subscribers` view (verified 2026-05-19 via SFMC SQL Query Activity — see §2 below) shows **163,303 total subscribers**, of which **19,388 (11.9%) are non-sendable** (7,541 held + 9,304 bounced + 2,543 unsubscribed). That's a sizable chunk of the renewal bill paying for subscribers SFMC will not even attempt to send to. The stale-contact cleanup plan (sibling repo: [`AdvancedRxPharmacy/advancedrx-marketing-cloud/docs/plans/salesforce-contract-renewal/stale-contact-cleanup-opportunity.md`](https://github.com/AdvancedRxPharmacy/advancedrx-marketing-cloud/blob/main/docs/plans/salesforce-contract-renewal/stale-contact-cleanup-opportunity.md)) wants to filter out these dead-email patients from the active subscriber count. Bouncing emails are one of the cleanest signals — but only if it's queryable on Contact. Today it isn't. - **Stop the inflow of new dead subscribers.** Even with SFMC auto-holding hard-bouncers (see next bullet), every newly-bouncing patient currently still flows: Contact syncs to SFMC → SFMC attempts a send → SFMC marks the subscriber held → AdvancedRx keeps paying for that held subscriber. Without a SF-side bounce gate on the sync filter, the held population grows monotonically. The bounce rollup + sync-filter update closes that loop: a patient with a hard-bounce IER on Contact gets dropped from `Contact_Salesforce`, never imported again, and SFMC eventually drops the corresponding subscriber. - **Sender-reputation hygiene (already handled by SFMC; not the lead value-prop).** Concern through earlier drafts was that we were continuously sending to dead addresses. The 2026-05-19 SFMC `_Subscribers` check confirmed **SFMC IS auto-holding** — 7,541 subscribers in the `held` state are not being sent to. So reputation isn't actively eroding from our side. The fix here is operational/financial, not deliverability rescue. - **Patient experience.** A patient whose email bounced doesn't get the receipt, doesn't get the refill reminder, doesn't get the welcome series. AND they can't use the patient portal for new Rx orders or refills — portal access depends on a working email. Surfacing the bounce on Contact lets Customer Service see who's stranded and capture an updated email on the patient's next call-in. **Day-1 result:** the rollup backfills `SFMCEmailBounceDate__c` on **10,620 distinct Contacts** in production (verified via Developer Console SOQL 2026-05-19 — 43,433 IER bounce records total, but many bouncers have multiple bounce events; 10,620 is the distinct-Contact count). After the cleanup formula update in §8.1, the MC Connect Synchronized Data Source row count drops from 177,051 to ~166,431 — a 6% reduction, all provably-dead-email patients. ## 2. Schema state — what already exists **Verification basis** (2026-05-19): schema verified in ClaudeTest sandbox via `FieldDefinition` SOQL. Production population counts run by Kyle in the Developer Console SQL Workbench the same day. Managed-package mechanism audit via Tooling API (Flow, ApexTrigger, FlowDefinition queries on `NamespacePrefix='et4ae5'`). | Object | Field / artifact | Type | Status | | ---------------------------------- | ----------------------------- | --------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `Contact` | `SFMCEmailBounceDate__c` | Date/Time | **Exists.** Field description: `"set via flow based on IER data"`. Currently 0 Contacts populated in production. | | `Contact` | `IsSFMCEmailBounced__c` | Checkbox | **Exists.** Same description, also 0 in production. | | `Contact` | `EmailBouncedDate` (standard) | Date/Time | **Exists, reserved for SF-native bounce tracking** (Salesforce-platform-sent email). NOT the right target for SFMC bounces — don't repoint. | | `et4ae5__IndividualEmailResult__c` | `et4ae5__DateBounced__c` | Date/Time | **Exists** in the managed package. Populated when SFMC writes a bounce event. | | `et4ae5__IndividualEmailResult__c` | `et4ae5__HardBounce__c` | Checkbox | **Exists.** TRUE for unrecoverable bounces (mailbox doesn't exist, etc.). | | `et4ae5__IndividualEmailResult__c` | `et4ae5__SoftBounce__c` | Checkbox | **Exists.** TRUE for transient bounces (full mailbox, server timeout). | | `et4ae5__IndividualEmailResult__c` | `et4ae5__Contact__c` | Lookup(Contact) | **Exists.** The relationship field the new rollup parents on. | | `dlrs__LookupRollupSummary2__mdt` | (managed CMDT) | — | **Already installed and active.** 45 active rollups in the org. Sibling rollup on the same parent: `Contact_Most_Recent_TC_Sent` (Realtime Max on `et4ae5__DateSent__c` — same shape we're replicating). | **Production population counts** (Kyle ran in prod Developer Console 2026-05-19): | Query | Result | | ------------------------------------------------------------------------------------------------------------------------- | ------------: | | `SELECT COUNT() FROM et4ae5__IndividualEmailResult__c` | **1,635,895** | | `SELECT COUNT() FROM et4ae5__IndividualEmailResult__c WHERE et4ae5__HardBounce__c = TRUE OR et4ae5__SoftBounce__c = TRUE` | **43,433** | | `SELECT COUNT() FROM et4ae5__IndividualEmailResult__c WHERE et4ae5__DateSent__c >= LAST_N_DAYS:30` | **75,429** | | `SELECT COUNT() FROM Contact WHERE SFMCEmailBounceDate__c != null` | **0** | | `SELECT COUNT() FROM Contact WHERE IsSFMCEmailBounced__c = true` | **0** | **SFMC `_Subscribers` status breakdown** (Kyle ran via SFMC Query Activity → DE `Subscriber_Status_Check_Output` 2026-05-19): ```sql SELECT Status, COUNT(*) AS cnt FROM _Subscribers WHERE SubscriberKey IS NOT NULL GROUP BY Status ``` | Status | Count | % of total | Sendable? | | -------------- | ----------: | ---------: | :-------: | | `active` | **143,915** | 88.1% | ✓ | | `held` | **7,541** | 4.6% | ✗ | | `bounced` | **9,304** | 5.7% | ✗ | | `unsubscribed` | **2,543** | 1.6% | ✗ | | **Total** | **163,303** | 100% | — | **Key findings from this query:** 1. **SFMC IS auto-holding hard-bouncers.** 7,541 subscribers in the `held` state — SFMC's auto-pause kicks in after repeated hard bounces (typically 2–3). Plus 9,304 currently in the transitional `bounced` state. This resolves the open question from earlier drafts: sender reputation is not actively eroding from our side because SFMC stops sending to these addresses. 2. **The renewal-billing exposure is 19,388 subscribers (11.9%).** Held + bounced + unsubscribed are all counted in the per-subscriber pricing but generate zero send value. This is the primary value-prop of the cleanup, not deliverability rescue. 3. **Bounce volume per held contact ≈ 4–5.** 43,433 total bounce IER records ÷ 10,620 distinct hard-bouncing Contacts ≈ 4.1 bounces per Contact — consistent with SFMC's "N hard bounces before auto-hold" threshold. The IER record count being higher than the held subscriber count is expected, not a discrepancy. 4. **`active` 143,915 < SF-side synced 177,051** by ~34K. The gap likely reflects SF-side records that have synced but SFMC has not yet processed into the `_Subscribers` view, plus email-key subscribers created via SOAP paths (§4.5) that don't map 1:1 to Contact records. Order-of-magnitude is fine for renewal-cleanup planning; precise reconciliation is out of scope for v1. **Managed-package mechanism audit — no existing SFMC-side aggregator handles this** (2026-05-19): queried the entire `et4ae5` namespace via Tooling API. - 13 et4ae5-managed flows. The suspect was `MCC_TrackingAsOfIER` (Active, AutoLaunchedFlow). It's named after the `et4ae5__Tracking_As_Of__c` field on the IER object itself — a per-record housekeeping helper that updates the IER's own status timestamp. The sibling `MCC_TrackingAsOfSD` does the same on SendDefinition. The remaining 11 et4ae5 flows fit the same housekeeping/lifecycle pattern (`MCC_SendComplete`, `MCC_SendFailed`, `MCC_ScheduledSend`, `MCC_SendStatusDateTime`, the 5 deprecated workflow-rule migrations, `MCC_NewSupportRequest`). **None aggregate to Contact.** - 14 et4ae5-managed Apex triggers. 3 are on IER (`cascadeDeleteILDs`, `updateCampaignMemberStatusForIER`, `LinkIERActivityLog`) — handle delete cascade, Campaign Member status, and activity-log linkage. **None touch Contact bounce fields.** **Conclusion:** the "set via flow based on IER data" description on the two custom fields was aspirational. Someone created the fields with the intent to wire up an AdvancedRx flow, the flow was never built. The fix is purely additive — there is nothing existing to step on. **Why not just point at `Contact.EmailBouncedDate`?** That's the standard Salesforce field reserved for the platform's own bounce tracking on SF-sent email (e.g., emails sent via Workflow Rule, Apex Messaging, or Activity Send Email). SFMC sends from a different infrastructure entirely. Co-mingling SFMC bounces into the standard field would corrupt any future SF-native email tracking and confuse Salesforce admins. The AdvancedRx-owned custom fields exist precisely to keep the two channels separate. ## 3. Design DLRS Realtime Max rollup, scoped to hard-bounce IER records. Mirrors the only existing rollup on the same parent object (`Contact_Most_Recent_TC_Sent`). ### 3.1 The DLRS rollup — field values DLRS rollups are stored as `dlrs__LookupRollupSummary2__mdt` Custom Metadata Type records. **Author this via the DLRS Lightning app** (App Launcher → search "Lookup Rollup" → "Manage Lookup Rollup Summaries" → New). The app validates field references at save-time + surfaces the Calculate / Schedule buttons on the saved record — raw CMDT editing bypasses both safety rails. See `business-context/dlrs-rollups.md` "Authoring workflow" section + `CORRECTIONS.md` row 27. | 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 | After deploy, trigger the historical backfill via the **Calculate button on the rollup record's detail page in the DLRS Lightning app**. Sandbox Calculate is moot — there are no IER records there. ### 3.2 Why DLRS, not a record-triggered flow The field descriptions on `SFMCEmailBounceDate__c` literally say "set via flow based on IER data" — that was the original architect's intent. But DLRS is the better choice in 2026: - **Matches the established org pattern.** 45 active rollups; 1 already on the same parent (`et4ae5__IndividualEmailResult__c`) with the same shape. Adding a sibling rollup is the lowest-cognitive-load change for whoever audits this later. - **Declarative + idempotent.** DLRS handles the 43K-record backfill at deploy time via the Calculate button. A flow would need a custom backfill batch job. - **No new trigger surface on IER.** The DLRS package owns its own triggers. A record-triggered flow on `et4ae5__IndividualEmailResult__c` would add to AdvancedRx's flow inventory + need fault-connector wiring per `.claude/rules/flow.md`. - **Native handling of re-parenting / delete.** If MC Connect ever updates an IER's `et4ae5__Contact__c` (re-attribution to a different patient) or hard-deletes records, DLRS recalculates both old and new parents automatically. The original "set via flow" intent was correct architectural shape (something needs to aggregate from IER → Contact) but DLRS satisfies the requirement more cleanly than a hand-built flow. ## 4. Scope decisions — resolved Two open questions surfaced during design. Both resolved to the simpler option for v1; either can be revisited later as a follow-up. ### 4.1 Date cutoff — NO cutoff (aggregate all 43K) — REVISED 2026-05-19 **Question:** the existing `Contact_Most_Recent_TC_Sent` rollup uses a hardcoded `et4ae5__DateSent__c > 2024-09-08T17:30:00.000-04:00` lower bound. An earlier draft of this plan proposed mirroring that cutoff for consistency. Should the bounce rollup mirror it, or skip it? **Decision: NO cutoff. Aggregate all 43,433 historical bounce records** regardless of `et4ae5__DateSent__c`. **Rationale (revised after schema-description check on ClaudeTest 2026-05-19):** the cutoff in the existing rollup is **NOT a data-quality boundary** — it's a **business-policy boundary**. The destination field `Contact.Most_Recent_TC_Sent_Date__c` carries the description (verified via `FieldDefinition` SOQL): > _"The date a contact last received our updated Terms & Conditions (last updated 2024) -- set via DLRS."_ So **TC = Terms & Conditions**, and `2024-09-08T17:30:00.000-04:00` is the timestamp the updated 2024 T&C version went out. The existing rollup tracks "which patients have received the CURRENT T&C version." Pre-cutoff sends were the OLD T&C and correctly don't count for that question. The rollup's `Name LIKE '%Conditions%'` clause confirms the policy intent (and `%Receipt%` / `%Delivered%` / `%Shipment%` are in there because those transactional emails carry the current T&C in their footers). **For bounce tracking the cutoff is irrelevant.** A 3-year-old hard-bounce on an email address that never recovered is still a valid "this email is dead" signal. Excluding pre-cutoff bounces would under-flag patients whose only bounce predates the T&C update — there's no operational reason to under-flag them. **This reverses the prior draft's "mirror it" decision.** The prior draft hypothesized the cutoff was an MC Connect reinstall / schema migration. Neither is true. Mystery resolved purely by reading the destination field's `Description` more carefully. ### 4.2 HardBounce only (v1); SoftBounce-aware logic deferred to v2 **Question:** the 43,433 bounce records include both hard and soft bounces. Soft bounces are recoverable (mailbox full, server timeout); hard bounces aren't (mailbox doesn't exist, domain dead). Should the rollup track HardBounce only, both, or a derived "this address is effectively dead" signal that escalates N soft bounces to a hard equivalent? **Decision:** v1 tracks `HardBounce__c = TRUE` only. **Rationale:** HardBounce is the canonical "give up on this address" signal — every email platform agrees on it semantically. SoftBounce is noisier and platform-specific; some SoftBounces self-resolve on next send. For the cleanup use case (filter out dead-email patients from the renewal-cleanup formula), HardBounce is the conservative cut. False negatives (a SoftBounce-only patient we should have flagged) are easier to revisit later than false positives (a real patient flagged dead and dropped from communications). **Future delta if reversed:** depending on MC Connect's internal Soft→Hard escalation behavior (which we haven't documented), some portion of the 43K may already be flagged HardBounce after enough Soft retries — meaning v1 already captures them. If sales/CS reports patients with months of soft bounces but no hard flag, scope a v2 that either ORs the two criteria or builds a soft-bounce-count rollup with a threshold. ### 4.3 Email-change lifecycle — track email-update timestamp + lifecycle-aware boolean **Question (the design issue Kyle surfaced 2026-05-19):** the DLRS rollup always returns the Max date over ALL HardBounce IER records for a Contact — including ones tied to an old email address. If the patient's email is updated to a working one, the DLRS rollup doesn't forget the old bounce. The Contact stays flagged as bouncing forever, and any cleanup formula that drops bouncers drops a now-good patient. Why the obvious fixes don't work: - **DLRS criteria can't reference parent fields**, so we can't write `IER.email = Contact.email` in the criterion. - **`et4ae5__Email__c` is a Formula(Text) on IER that resolves to the current Contact email, NOT a snapshot of email-at-send.** Verified via Tooling API on ClaudeTest 2026-05-19 — the formula body is literally `IF(!ISBLANK(et4ae5__Contact__r.Email), et4ae5__Contact__r.Email, et4ae5__Lead__r.Email)`. So when `Contact.Email` is updated, every historical IER record for that Contact starts showing the NEW email in `et4ae5__Email__c` — the original email-at-send is unrecoverable from anywhere on the IER record. This rules out any "filter the rollup by IER-email = Contact-email" approach (that comparison would always be true). The lifecycle architecture below isn't a hedge; it's provably the only path. - **Clearing `SFMCEmailBounceDate__c` via a Contact-side flow doesn't stick** — DLRS Realtime fires on any IER child change and re-aggregates from the old HardBounce records, resurrecting the field. **Decision: track email-update timestamp on Contact + redefine `IsSFMCEmailBounced__c` as a lifecycle-aware formula.** Three new pieces: 1. **New field: `Contact.Email_Last_Updated_Date_Time__c`** (DateTime). Maintained by an extension to the existing **BeforeSave** canonical `Contact_Insert_Update_Same_Record` flow — set to `{!$Flow.CurrentDateTime}` whenever `ISCHANGED(Email)` OR (`ISNEW()` AND email is non-null). BeforeSave is correct here because we're stamping a value on the triggering record itself; an AfterSave self-update would be unnecessary recursion risk. 2. **Redefine `Contact.IsSFMCEmailBounced__c` as Formula(Checkbox):** ``` AND( NOT(ISBLANK(SFMCEmailBounceDate__c)), SFMCEmailBounceDate__c >= NULLVALUE(Email_Last_Updated_Date_Time__c, DATETIMEVALUE("2020-01-01 00:00:00")) ) ``` `NULLVALUE` defaults to a very-old date if `Email_Last_Updated_Date_Time__c` was never populated — so existing bounces on patients whose email hasn't changed (the historical 43K) still flag correctly on day 1. As soon as Customer Service updates a patient's email on a call-in, the formula transitions to comparing against the actual update timestamp. 3. **`SFMCEmailBounceDate__c` stays as the factual DLRS rollup target.** Don't try to clear it from the Contact side — it should stay truthful about "when was the most recent bounce we ever saw." The Boolean is what drives behavior. **How the lifecycle works end-to-end:** - Patient X's `bob@old.com` bounces → DLRS populates `SFMCEmailBounceDate__c = 2026-04-10`. `Email_Last_Updated_Date_Time__c` is whatever it was (likely null or the Contact's CreatedDate-area). Boolean → TRUE. - Patient calls in for a refill, Customer Service catches the bounce flag in their workflow, gets the new email → updates `Contact.Email` to `bob@new.com`. The extended Contact BeforeSave flow fires, sets `Email_Last_Updated_Date_Time__c = 2026-05-19 14:00`. Boolean recomputes: `2026-04-10 >= 2026-05-19`? FALSE → Boolean → FALSE. Patient back in cleanup-formula good standing. - If `bob@new.com` later bounces too → new HardBounce IER arrives → DLRS recalcs `SFMCEmailBounceDate__c = 2026-06-01`. Boolean recomputes: `2026-06-01 >= 2026-05-19`? TRUE → Boolean → TRUE. Patient correctly re-flagged. Self-correcting. **Why this is better than alternatives:** - **No managed-package data modification needed** (vs adding a `Superseded__c` field on `et4ae5__IndividualEmailResult__c` and updating child records on parent change — adds DML + governor-limit risk + custom field on a managed object). - **No information loss** — `SFMCEmailBounceDate__c` stays factually true about historical bounces; the Boolean does the "is it still relevant" reasoning. - **One extra field + one small flow** — both AdvancedRx-owned, both reversible if a better pattern emerges. ### 4.4 The cleanup target field is `Sync_to_Marketing_Cloud__c`, NOT `Sync_with_Marketing_Cloud__c` (correction 2026-05-19) **Earlier drafts of this plan had the wrong field name.** The Contact object has TWO similarly-named custom fields that both express "should this Contact sync to SFMC": | Field | Created | Type | Maintained by | Is it the MC Connect filter? | | ------------------------------ | ------------------- | ------------------- | --------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `Sync_with_Marketing_Cloud__c` | Kyle, 2023-01-25 | Formula(Checkbox) | Just the formula `Email <> NULL` | **No.** Layout dependencies only; nothing in flow / Apex / report references it. Effectively dead. | | `Sync_to_Marketing_Cloud__c` | Marissa, 2023-03-07 | **Stored Checkbox** | The `Contact_Insert_Update_Same_Record` BeforeSave flow (sets TRUE when `Email != null`, FALSE otherwise) | **Yes — this is the real MC Connect Synchronized Data Source filter.** Verified by Kyle in Contact Builder 2026-05-19: filter expression literally `Sync_to_Marketing_Cloud__c equal (=) True`; poll schedule every 15 minutes; current synced count 177,044. | Marissa created the second field 6 weeks after the first, almost certainly because MC Connect's Synchronized Data Source filter needs a stored Boolean — not a Formula. Same business logic, different field type. **This changes the cleanup-half strategy:** You can't "edit a formula" because `Sync_to_Marketing_Cloud__c` doesn't have one. It's a stored Checkbox maintained by the BeforeSave flow. Two options to layer the new bounce/deceased/staleness gates on top: **Option A — Convert `Sync_to_Marketing_Cloud__c` to a Formula(Checkbox).** Replace the stored field with a formula that incorporates all the gates. Cleanest end-state — single source of truth, MC Connect always reads the current evaluation on every 15-min poll, no flow-vs-formula sync gap. Destructive field-type change but the field's behavior is unchanged (it still equals what `Email != null` derives, just with additional ANDs). **Option B — Extend the BeforeSave flow.** Add the additional ANDs inline in the flow's existing decision that sets `Sync_to_Marketing_Cloud__c`. Keeps the field as a stored Checkbox. **Correctness gap:** when DLRS recalculates `IsSFMCEmailBounced__c` (via an IER child event), the Contact's stored `Sync_to_Marketing_Cloud__c` doesn't auto-update — DLRS rolls up to the formula-derived Boolean, but the BeforeSave flow doesn't fire on a DLRS-driven Contact write. Result: `Sync_to_*` stays stale until the next Contact-side save. **Not recommended.** **Decision: Option A.** Same field-type-swap pattern as `IsSFMCEmailBounced__c` (§4.3). Production currently has 177,044 records with `Sync_to_Marketing_Cloud__c = True` — the new formula will evaluate the same TRUE for the subset that passes ALL the criteria, and FALSE for the rest. MC Connect's next 15-min sync drops the false ones from `Contact_Salesforce`. **Bonus cleanup post-deploy:** the existing flow decision that sets `Sync_to_Marketing_Cloud__c` becomes a no-op (formulas can't be set in flows). Remove that decision branch from `Contact_Insert_Update_Same_Record` once Option A is live. The redundant `Sync_with_Marketing_Cloud__c` formula field is also now confirmed dead (no references) and can be retired in a separate cleanup PR — out of scope for this plan. ### 4.5 Side caveat — email-key subscribers are a rounding error (RESOLVED 2026-05-20) Not all SFMC subscribers come from the Contact sync. AdvancedRx has Apex paths that create subscribers directly via SOAP API with **email-address SubscriberKeys** (not Contact-ID keys): - `HandleMCSubscriber` — retrieves/creates SOAP `Subscriber` records, CustomerKey + EmailAddress both set to the passed subscriber key (often an email address) - `PrescribersToMarketingCloud` — creates subscribers into list `906` The SFMC retrieve includes a "No AT Symbol" / "Filter out bad subscriber keys" data filter that explicitly filters `SubscriberKey NotContains "@"` from `Master_Subscriber_DE` — strong evidence the org already knows email-key subscribers exist alongside Contact-ID ones. **Sized 2026-05-20 via SFMC Query Activity** (output DE `Subscriber_KeyType_Status_Output`): ```sql SELECT CASE WHEN SubscriberKey LIKE '003%' THEN 'Contact-ID' ELSE 'Email-key' END AS source, Status, COUNT(*) AS cnt FROM _Subscribers WHERE SubscriberKey IS NOT NULL GROUP BY CASE WHEN SubscriberKey LIKE '003%' THEN 'Contact-ID' ELSE 'Email-key' END, Status ``` | Source | active | held | bounced | unsubscribed | Total | | -------------- | ------: | ----: | ------: | -----------: | ----------: | | **Contact-ID** | 143,898 | 7,540 | 9,287 | 2,543 | **163,268** | | **Email-key** | 33 | 1 | 1 | 0 | **35** | | **Combined** | 143,931 | 7,541 | 9,288 | 2,543 | **163,303** | **Resolution: the §4.5 hedge was over-cautious.** Email-key subscribers are **35 of 163,303 — 0.02% of the SFMC subscriber population**. The Contact-side cleanup formula reaches **99.98% of SFMC subscribers**. Of the 19,388 non-sendable subscribers, only 2 are email-key (1 held + 1 bounced) — everything else gets cleaned up by the Contact-side gate. The renewal-savings story doesn't need a parallel cleanup path for the SOAP-created subscribers; they're a rounding error. **The 33 active email-key subscribers are almost certainly `PrescribersToMarketingCloud` list-906 inserts.** Easy verification someday (spot-check 33 email keys in SFMC against SF Contact records, or grep Apex for prescriber-push call paths) but not material to the renewal decision. **Incidental finding:** the 1 held + 1 bounced email-key subscriber confirms SFMC's auto-hold mechanism applies to email-key subscribers too, not just Contact-ID ones. Auto-hold is a global SFMC behavior, not Contact-sync-specific. No action needed. ## 5. Pre-deploy validation gate (CLAUDE.md §8) We're shipping declarative metadata only — one DLRS rollup CMDT record, plus (if Option A in §4.3) one field-type swap. The gate is mostly a no-op but we walk it because every deploy goes through it. 1. Confirm target org = `ClaudeTest` via `mcp__Salesforce_DX__get_username`. **Never deploy outside `ClaudeTest`** — production refresh is the source of truth (CLAUDE.md §1). 2. Code Analyzer runs against the change set. Expected clean — no logic-bearing files. 3. `sf project deploy validate --target-org ClaudeTest --source-dir --test-level RunLocalTests`. RunLocalTests confirms the new rollup CMDT record (and any field-type change) doesn't break existing tests. **We do not author new tests for this change.** 4. No hardcoded user / profile / group / RT IDs in the new metadata. The criterion references only `et4ae5__HardBounce__c` (Boolean). Conforms to `.claude/rules/flow.md` §3.11. ## 6. Test plan Sandbox has no transactional IER records (per CLAUDE.md §1). Two-step verification. ### 6.1 Sandbox seeded test After deploy: 1. Create one Contact in sandbox (any RT). 2. Create three `et4ae5__IndividualEmailResult__c` records under that Contact (`et4ae5__Contact__c = contact.Id`): - **IER A:** `et4ae5__HardBounce__c = TRUE`, `et4ae5__DateBounced__c = 2026-05-10T10:00:00`, `et4ae5__DateSent__c = 2026-05-10T09:55:00`. - **IER B:** `et4ae5__HardBounce__c = TRUE`, `et4ae5__DateBounced__c = 2026-05-15T14:00:00`, `et4ae5__DateSent__c = 2026-05-15T13:55:00` (newer; should win the Max). - **IER C:** `et4ae5__HardBounce__c = FALSE`, `et4ae5__SoftBounce__c = TRUE`, `et4ae5__DateSent__c = 2026-05-18T08:00:00` (must NOT count — soft bounce only). 3. Verify `contact.SFMCEmailBounceDate__c == 2026-05-15T14:00:00` (IER B wins the Max). 4. If Option A on §4.3 was chosen: verify `contact.IsSFMCEmailBounced__c == TRUE` (formula derives from non-null Date). 5. Update IER B's `et4ae5__HardBounce__c` to FALSE. Verify the rollup drops to IER A's date (`2026-05-10T10:00:00`). 6. Set IER A's `et4ae5__HardBounce__c` to FALSE too. Verify `contact.SFMCEmailBounceDate__c` returns to null. If Option A: verify `IsSFMCEmailBounced__c` returns to FALSE. 7. **Re-flip IER B's `et4ae5__HardBounce__c` back to TRUE** (otherwise step 7 tests nothing — a non-bounce IER reparented to a new Contact doesn't trigger the rollup). Then re-parent IER B to a different Contact (`et4ae5__Contact__c = otherContact.Id`). Verify the original Contact's `SFMCEmailBounceDate__c` is now null (only IER A is left and we set its HardBounce=FALSE in step 6) AND the new Contact's `SFMCEmailBounceDate__c` gets IER B's date (`2026-05-15T14:00:00`). This confirms DLRS handles re-parenting correctly (old-parent decrement + new-parent increment). 8. **Historical-bounce test (no date cutoff per §4.1):** create IER D with `et4ae5__HardBounce__c = TRUE`, `et4ae5__DateBounced__c = 2023-05-01T10:00:00`, `et4ae5__DateSent__c = 2023-05-01T09:55:00`. Verify the rollup DOES pick it up — no cutoff in the criterion, so historical bounces count. The Contact's `SFMCEmailBounceDate__c` should now reflect this older date if it's the most recent for the Contact. (Confirms the §4.1 decision that the bounce rollup doesn't inherit the T&C-rollup's policy cutoff.) 9. **Email-change lifecycle test (§4.3 verification).** Re-set IER A's `et4ae5__HardBounce__c = TRUE` so the Contact has an active bounce again. Verify `contact.SFMCEmailBounceDate__c` is populated AND `contact.IsSFMCEmailBounced__c = TRUE`. Now update `contact.Email` to a new value. The extended `Contact_Insert_Update_Same_Record` BeforeSave flow fires → `Email_Last_Updated_Date_Time__c` set to NOW in the same DML. The Boolean formula re-evaluates: bounce-date is now older than email-update-date → `IsSFMCEmailBounced__c` flips to FALSE. `SFMCEmailBounceDate__c` stays populated (factual). Then create IER E with `HardBounce = TRUE` and `DateBounced = NOW`. DLRS recalcs `SFMCEmailBounceDate__c` to IER E's date (newer). Now bounce-date > email-update-date → `IsSFMCEmailBounced__c` flips back to TRUE. Self-correcting. Tests 1–3 confirm the basic Max + HardBounce filter. Test 4 confirms the Boolean formula. Tests 5–6 confirm Realtime fires on update. Test 7 confirms re-parenting (DLRS handles old-parent decrement + new-parent increment). Test 8 confirms the date-cutoff criterion fires correctly. **Test 9 confirms the email-change lifecycle that closes the cleanup-formula recovery gap.** ### 6.2 Production smoke test (post-cutover) After deploying to production: 1. Click **Calculate** on the rollup record's detail page in the DLRS Lightning app to trigger the historical backfill. **Schedule for off-hours.** Calculate scans every record on the CHILD object (1.6M IER rows in production), not just the 43K matching the criterion — that's the result, not the scan size. Expect a meaningful async-job run, possibly 30+ minutes depending on org Apex pressure. Watch the DLRS app's job-status indicator + Setup → Apex Jobs for the underlying batch. 2. Spot-check 3 Contacts known to have bouncing emails (sales / Customer Service picks them, or pull them via the SOQL below). Verify `SFMCEmailBounceDate__c` matches the most-recent HardBounce IER record on each Contact: ```sql SELECT et4ae5__Contact__c, MAX(et4ae5__DateBounced__c) maxBounce FROM et4ae5__IndividualEmailResult__c WHERE et4ae5__HardBounce__c = true GROUP BY et4ae5__Contact__c LIMIT 5 ``` Then for each of those 5 Contact Ids, `SELECT Id, SFMCEmailBounceDate__c FROM Contact WHERE Id = ''`. Should match `maxBounce`. 3. Run aggregate sanity: ```sql SELECT COUNT() FROM Contact WHERE SFMCEmailBounceDate__c != null ``` Pre-rollup this was **0**. Post-rollup-Calculate this should jump to the count of distinct `et4ae5__Contact__c` values in IER records meeting the criteria. Order-of-magnitude check: somewhere between a few hundred and tens of thousands depending on the contact-uniqueness of the 43K bounce records (one patient can have many bounces). If significantly off (e.g., still 0, or wildly higher than expected): investigate the DLRS trigger before declaring done. 4. If Option A on §4.3 was chosen, also run: ```sql SELECT COUNT() FROM Contact WHERE IsSFMCEmailBounced__c = true ``` Should match the §6.2.3 count exactly (formula derives 1:1 from the Date field). ## 7. Build steps Mostly declarative. Order matters — bounce-half pieces (1-6) can ship standalone; cleanup-half pieces (7-9) are blocked on the sync-filter proof in step 0. 0. ~~**PREREQUISITE — Prove the MC Connect sync filter.**~~ **RESOLVED 2026-05-19** by Kyle's Contact Builder check. The MC Connect Synchronized Data Source for Contact uses filter expression **`Sync_to_Marketing_Cloud__c equal (=) True`** (poll schedule: every 15 minutes; current synced count 177,044). Note this is a DIFFERENT field from `Sync_with_Marketing_Cloud__c` — see §4.4 below for the field-name correction. The cleanup half of the plan is unblocked, but its target field changes and its mechanism changes (the actual filter field is a stored Checkbox maintained by a flow, not a formula — see §4.4). 1. **Create `Contact.Email_Last_Updated_Date_Time__c`** (DateTime, custom). Object Manager → Contact → New field → Date/Time. API name `Email_Last_Updated_Date_Time__c`. FLS Read-Only for everyone except System Administrator (it's flow-maintained; users shouldn't edit directly). Add to relevant page layouts. 2. **Backfill `Email_Last_Updated_Date_Time__c` from `ContactHistory`.** Without this, day-1 every existing bouncer has `Email_Last_Updated = null` → formula falls back to the 2020-01-01 NULLVALUE → every old bounce flags `IsSFMCEmailBounced = TRUE`, INCLUDING patients whose email was corrected pre-deploy. To minimize that false-positive set, run a one-time backfill: query `ContactHistory` for `Field = 'Email'` records (Salesforce retains 18-24 months by default), group by `ContactId`, set `Contact.Email_Last_Updated_Date_Time__c = MAX(ContactHistory.CreatedDate)`. Patients with no `ContactHistory.Email` change in retention keep the null default (worst-case false-positive, acceptable). Run via Data Loader update + a one-off SOQL helper, or via an Apex anonymous script. **Verify Email field history is being tracked** before relying on this: `SELECT IsFieldHistoryTracked FROM EntityDefinition WHERE QualifiedApiName = 'Contact'` + check Object Manager → Contact → Fields → Set History Tracking. ClaudeTest verified Email is history-tracked 2026-05-19; confirm in prod too. 3. **Extend the existing `Contact_Insert_Update_Same_Record` BeforeSave flow.** Add a decision branch with criteria `ISCHANGED($Record.Email) OR (ISNEW() AND NOT(ISBLANK($Record.Email)))`. On the TRUE branch, do an Assignment that sets `$Record.Email_Last_Updated_Date_Time__c = {!$Flow.CurrentDateTime}`. **No fault connector** — BeforeSave Assignments to the triggering record don't need one (no DML to fail). Don't author a separate AfterSave flow; that would create unnecessary recursion risk on a same-record stamp. The canonical Same_Record file lives at `force-app/main/default/flows/Contact_Insert_Update_Same_Record.flow-meta.xml` — extend it; do not branch. 4. **Redefine `Contact.IsSFMCEmailBounced__c` as Formula(Checkbox).** Object Manager → Contact → Fields & Relationships → `IsSFMCEmailBounced__c` → Edit. Change Field Type from Checkbox to Formula. Returns Type: Checkbox. Formula: ``` AND( NOT(ISBLANK(SFMCEmailBounceDate__c)), SFMCEmailBounceDate__c >= NULLVALUE(Email_Last_Updated_Date_Time__c, DATETIMEVALUE("2020-01-01 00:00:00")) ) ``` **This is a destructive field-type change; Salesforce will warn that existing data will be lost.** Production data is currently 0 for this field, so no actual loss. Confirm the warning. Save. 5. **Create the DLRS rollup CMDT record via the DLRS Lightning app.** App Launcher → search "Lookup Rollup" → "Manage Lookup Rollup Summaries" → New. Field values per §3.1 above. Do not use the raw Custom Metadata Types editor in Setup — it bypasses the DLRS app's save-time field-reference validation + skips the Calculate/Schedule buttons on the saved record. See `business-context/dlrs-rollups.md` "Authoring workflow." 6. **Pre-deploy gate** (§5). Mostly a confirmation step since there's no code. 7. **Deploy ClaudeTest → run sandbox seeded test** (§6.1). Sandbox has no IER records so you'll create test IERs by hand. Make sure to also test the email-change lifecycle: bounce on email X, then change Contact.Email, verify Boolean flips false. 8. **Pause for Kyle review** of sandbox results. 9. **Deploy to production** via the normal change-set / package process. (This plan does not authorize prod deploy — Kyle's normal prod-deploy path applies.) 10. **Trigger the historical backfill** via DLRS's Calculate operation on the new rollup. **Schedule for off-hours.** DLRS Calculate scans every record on the CHILD object against the criterion before deciding whether it counts — that's 1.6M `et4ae5__IndividualEmailResult__c` records to evaluate, not 43K. The 43K bounce subset is the result, not the scan size. Expect a meaningful async-job run (could be 30+ minutes depending on org-level Apex governor pressure). Watch the DLRS app's job-status indicator + Apex Jobs in Setup. After Calculate, run the §6.2 smoke test. 11. **Convert `Contact.Sync_to_Marketing_Cloud__c` from Checkbox to Formula(Checkbox)** per §4.4 Option A. Object Manager → Contact → Fields & Relationships → `Sync_to_Marketing_Cloud__c` → Edit. Change Field Type from Checkbox to Formula. Returns Type: Checkbox. Formula per §8.1. Destructive metadata change; Salesforce will warn that existing data will be lost. The existing flow-derived value (`Email != null`) is now part of the new formula, so no logic is actually lost. 12. **Remove the redundant decision from `Contact_Insert_Update_Same_Record`** that previously set `Sync_to_Marketing_Cloud__c`. Now that the field is a formula, the flow's old decision is a no-op (formula fields can't be set in flows). Clean up to avoid future confusion. 13. **Verify `Contact_Salesforce` row-count drop in SFMC** — the next MC Connect sync cycle (every 15 min per the synchronized-source poll schedule) should remove the newly-excluded Contacts. Pre-cleanup count: 177,051 (Kyle verified 2026-05-19). Post-cleanup expectation: **~166,431** (177,051 − 10,620 hard-bouncing patients). Then run SFMC Contact Delete + wait for the 14-day soft-delete window to clear before declaring the billing-count drop final. **Note** (per §4.5): email-key subscribers created via `HandleMCSubscriber` + `PrescribersToMarketingCloud` SOAP paths are NOT affected by this cleanup — they'll need a separate path if material to the renewal-savings target. ## 8. Future direction — downstream consumers Once `Contact.SFMCEmailBounceDate__c` is populated, two natural consumers should be updated to take advantage: ### 8.1 The new `Contact.Sync_to_Marketing_Cloud__c` formula `Sync_to_Marketing_Cloud__c` is the field MC Connect's Synchronized Data Source filters on (verified 2026-05-19 — see §4.4). Per §4.4 Option A, we're converting it from a stored Checkbox to a Formula(Checkbox). The new formula keeps the existing `Email != null` logic + adds a hard-bounce gate + defensive deceased/native-bounce gates. **Staleness gate is deliberately out of scope for v1** (Kyle decision 2026-05-19). Sizing the staleness drop with Developer Console SOQL produced fuzzy thresholds and unclear false-positive risk; better to ship the unambiguous bounce-only cleanup and defer activity-criteria choice to v2 if/when the renewal pricing makes it worth the additional analysis. **Final v1 formula:** ``` AND( Email <> NULL, // existing behavior preserved NOT(IsSFMCEmailBounced__c), // NEW — drop hard-bouncing patients; auto-recovers via §4.3 lifecycle ISBLANK(DeceasedDate), // defensive — currently 0 patients in prod, but guards future deceased flagging ISBLANK(HealthCloudGA__DeceasedDate__c), // defensive — currently 0 patients in prod ISBLANK(EmailBouncedDate) // defensive — SF-native bounces; currently 0 in prod ) ``` **Production sizing of each gate** (Kyle ran Developer Console SOQL 2026-05-19): | Gate | Drops | | ----------------------------------------------------- | -------------------------------------------------: | | `NOT(IsSFMCEmailBounced__c)` — hard-bouncing patients | **10,620** | | `ISBLANK(DeceasedDate)` | 0 (field not currently populated) | | `ISBLANK(HealthCloudGA__DeceasedDate__c)` | 0 (field not currently populated) | | `ISBLANK(EmailBouncedDate)` | 0 (SF-native field, not populated by SFMC bounces) | | **Total drop** | **10,620** | Pre-cleanup count: **177,051** synced Contacts. Post-cleanup expectation: **~166,431** (6% reduction). Every dropped Contact is provably-dead-email — zero false positives. **Why three defensive gates with 0 current impact:** they cost nothing in formula performance, and they guard the future case where AdvancedRx starts populating `DeceasedDate` or where SF-native email sends produce bounces. Cheap insurance. **Why the Boolean (`IsSFMCEmailBounced__c`) and not the raw Date field (`SFMCEmailBounceDate__c`):** because `SFMCEmailBounceDate__c` is the factual rollup result (most recent bounce we ever saw) — it doesn't know about email updates. If we gated cleanup on `ISBLANK(SFMCEmailBounceDate__c)`, a patient whose `old@email.com` bounced 6 months ago would STAY dropped from sync forever, even after Customer Service updated them to a working `new@email.com`. The Boolean's lifecycle-aware formula (§4.3) compares bounce-date to `Email_Last_Updated_Date_Time__c` and correctly recovers patients whose email has been refreshed since the bounce. See §4.3 for the full lifecycle walkthrough. ### 8.2 Staleness gate — deferred to v2 Kyle's call 2026-05-19: don't add a staleness gate in v1. Reasoning: - The bounce gate is **unambiguous** — patient's email is dead, can't reach them. Zero false-positive risk. - A staleness gate (e.g., "no fill in 24+ months") is **fuzzy** — activity signals can be incomplete (no Task logged for a phone interaction), and the false-positive cost is real (dropping a patient who DID interact but whose interaction wasn't captured in SF). - The 6% drop from bounces alone is meaningful and defensible without business politics around "how stale is too stale." **If renewal pricing makes staleness worth revisiting**, the analysis path is clear: the production SOQL battery in commit `[bdd7d91]` (`docs/plans/sfmc-bounce-rollup/plan.md` §7 historical) sized the drop at three cutoffs (12 / 18 / 24 months). Pick a cutoff, finalize the staleness gate, layer it on the v1 formula. The patient-action-signal trap (don't use `Last_Refill_Reminder_Sent__c` — that reflects what WE pushed, not what the patient did) is documented in commit history for future reference. ### 8.3 SoftBounce-aware logic (v2) If sales/CS reports patients with months of soft bounces still receiving communications, scope a v2 that either: - ORs the criterion to `(et4ae5__HardBounce__c = TRUE OR et4ae5__SoftBounce__c = TRUE)` — simplest, but treats every soft bounce as terminal. - Adds a sibling Count rollup of recent SoftBounce records with a threshold gate — closer to email-platform standard "3 soft bounces in 30 days = treat as hard." Out of v1 scope. Revisit if needed. ### 8.4 The 2024-09-08T17:30 cutoff mystery — RESOLVED 2026-05-19 The existing `Contact_Most_Recent_TC_Sent` rollup's hardcoded `> 2024-09-08T17:30` lower bound was an open question through earlier drafts of this plan + the sibling-repo `emails.md` Phase 5B finding. **Resolved by reading the destination field's description more carefully:** the field `Contact.Most_Recent_TC_Sent_Date__c` carries the description _"The date a contact last received our updated Terms & Conditions (last updated 2024) -- set via DLRS."_ So **TC = Terms & Conditions** (not "transactional content"), and `2024-09-08T17:30` is the timestamp the 2024 T&C version went out. The rollup tracks "has the patient seen the current T&C version." Pre-cutoff sends were the OLD T&C and correctly don't count. That clarification flipped this plan's §4.1 decision from "mirror the cutoff" to "no cutoff" — see §4.1 for the full reasoning. No further investigation needed. ## 9. Audit artifacts pending cleanup Created during the 2026-05-19/20 audit to verify the framing-pivot evidence + §4.5 hedge sizing. **All disposable; delete at end of audit, not before** — Kyle's preference to batch the deletion so we don't lose the source data mid-investigation. | Artifact | Type | Location | Created | Purpose | | ---------------------------------- | -------------- | -------------------------------------------- | ---------- | ------------------------------------------------------------------------------------------------ | | `Subscriber_Status_Check` | Query Activity | Automation Studio → Activities → Query | 2026-05-19 | Generated the `_Subscribers` Status breakdown that proved SFMC IS auto-holding (§2). | | `Subscriber_Status_Check_Output` | Data Extension | Email Studio → Subscribers → Data Extensions | 2026-05-19 | Target DE for the status breakdown query. | | `Subscriber_KeyType_Status_Check` | Query Activity | Automation Studio → Activities → Query | 2026-05-20 | Generated the Contact-ID vs email-key subscriber split that resolved §4.5. | | `Subscriber_KeyType_Status_Output` | Data Extension | Email Studio → Subscribers → Data Extensions | 2026-05-20 | Target DE for the key-type query. Description in SFMC reads "One time audit Ok to delete later." | **Cleanup checklist** (run at audit close): 1. Automation Studio → Activities → Query → delete `Subscriber_Status_Check` + `Subscriber_KeyType_Status_Check`. 2. Email Studio → Subscribers → Data Extensions → delete `Subscriber_Status_Check_Output` + `Subscriber_KeyType_Status_Output`. 3. Update this section to note the cleanup date and remove the table. ## 10. References - Sibling repo (SFMC-side audit): [`advancedrx-marketing-cloud/business-context/emails.md`](https://github.com/AdvancedRxPharmacy/advancedrx-marketing-cloud/blob/main/business-context/emails.md) §"Email tracking writeback gap — full diagnosis" — the canonical evidence chain for why this rollup is the right fix. - `business-context/dlrs-rollups.md` — DLRS authoring workflow + the existing 45-rollup catalog including `Contact_Most_Recent_TC_Sent`. - `business-context/integrations/sfmc.md` Hazards #7 — SF-side documentation of the bounce-writeback gap with cross-references to the sibling repo. - Sibling repo: [`advancedrx-marketing-cloud/docs/plans/salesforce-contract-renewal/stale-contact-cleanup-opportunity.md`](https://github.com/AdvancedRxPharmacy/advancedrx-marketing-cloud/blob/main/docs/plans/salesforce-contract-renewal/stale-contact-cleanup-opportunity.md) — the downstream consumer that benefits from this fix. (Note: the contract-renewal plan lives in the SFMC sibling repo because it was authored as part of the May 2026 SFMC repo build, not in the main SF repo. Both repos are stakeholder-shared.) - `docs/plans/archive/elite-prescriber-rollup/plan.md` — pattern-template for this plan; same shape (DLRS rollup on a managed-package object). Archived 2026-05-19 after production ship.