HLT-DC-MCN-001 — Executive Progress Report

Timeline

SOW Phase Gantt — 8 Weeks

Phase

W1
16–23 Apr

W2
23–30 Apr

W3
30 Apr–7 May

W4
7–14 May

W5
14–21 May

W6
21–28 May

W7
28 May–4 Jun

W8
4–11 Jun

1 — Discovery & Audit

✓ DONE

2 — DC Remediation

START

PLANNED

3 — Audience Building

PLANNED

4 — MCN Configuration

EARLY

PLANNED

5 — Validation & Handover

PLANNED

Phase 1 — Discovery & Audit

Closed · Architecture Decided

Overall Phase Progress 100%

Week 2 deliverables (24–30 April)

30 Apr · Snowflake audit: 121 Zero Copy share objects + 779 medallion tables across 8 databases inventoried. Reusable scripts committed for re-runs.

30 Apr · Operating-model deck (#142): 14-slide framework for the post-architecture working model — data flow, team workflow, Data Graph lifecycle, consent mapping. In review.

30 Apr · All Subscribers migration spec drafted (#82): SFMC truth verified at 362K current global unsubs. 3-phase hybrid approach documented. Open questions raised for legal scope.

30 Apr · MC stream cleanup audit (#160): Verified Jessica's deletion of 31 sfmc-connector streams against all 47 CIs and 60 segments — zero downstream dependencies confirmed. Safe to proceed with reconnection.

29 Apr · Architecture decision (#96): Hybrid model agreed — Snowflake delivers harmonised Gold layer; Data Cloud handles Identity Resolution, Consent (Approach B for consent only), and activation. Campus pilot is the first proof point.

29 Apr · SFMC migration-readiness classification: 14 Pillar query families classified across 4 buckets (Ready / Needs CI/DG / Cannot migrate / Out of scope). 12 Gold-layer fields prioritised for build.

28 Apr · SFMC Classic full instance audit (#143): 362 automations, 1,534 journeys, 1,959 query activities, 41 CloudPages catalogued.

28 Apr · Polly + Simon Pillar journey walkthrough: Full SFMC automation stack for PG/UG nurture documented end-to-end (entry, splits, suppression, personalisation).

28 Apr · BI architecture session #4: Credit-cost analysis Approach A vs B (~60M credits/year for full-DC IR ruled out). Approach decision teed up for following day.

27 Apr · Salesforce AgentForce + Data Cloud workshop: Strategic agentic-readiness assessment. Snowflake ML + Zero Copy confirmed as preferred lead-scoring architecture. External FAQ agent identified as highest-yield starter.

Week 1 deliverables (16–23 April)

23 Apr · BI architecture session #3: Approach A vs B framing. Identity Resolution cost insight (~60M credits/year for full-DC scenario).

23 Apr · DLO/DMO/DSO inventory (#129): 137 DMOs, 203 DLOs, 224 DSOs, 3,454 field-level mappings. Zero coverage gap. DBML lineage diagrams published.

23 Apr · Calculated Insights inventory (#126): 47 CIs documented with SQL, schedule, status, DMO lineage.

23 Apr · Segments inventory (#130): 60 Data Cloud segments audited with criteria, runtime stats, and health checks.

22 Apr · V3 CRM Data Kit field maps (#112): 963 DLO→DMO field mappings retrieved and indexed.

21 Apr · BI sessions #1 + #2: Bronze/Silver/Gold architecture confirmed; Zero Copy strategy; dbt adoption; ML model inputs; field-mapping strategy.

21 Apr · Simon marketing call: MCN migration priorities, consent complexity, parallel-operation period planning.

17 Apr · Data Stream baseline (#76): All 176 streams audited. All healthy, 344M+ records. Dual SFMC architecture documented.

17 Apr · CI write-back diagnosis (#74): Root cause identified — source stream at 0 records since Feb 2026. 409 stale residuals.

17 Apr · Opportunity 3-stream audit (#75): Architecture confirmed intentional. Custom Field History is audit log.

16 Apr · Repo scaffold (#77): Spec-driven workflow, issue templates, board, folder structure live.

16 Apr · Access provisioning (#72): Salesforce prod, sandbox, JIRA, GitHub, Snowflake, Marketing Cloud — all confirmed.

16 Apr · Kick-off + GitHub board: Stakeholder map confirmed. 21-issue board created with Jira refs and SOW scope notes.

Detailed Timeline

What Happened — Week by Week

Week 1
16–23 Apr

Kick-off, Discovery, BI Deep Dive & Full Audit

Kick-off with full team. BI sessions #1–3: Bronze/Silver/Gold confirmed, Zero Copy strategy, IR cost analysis, field-mapping strategy. 176 Data Streams baselined — all healthy. V3 CRM Data Kit retrieved (963 field maps). Full DLO/DMO/DSO inventory — zero coverage gap. 47 CIs and 60 segments inventoried. GitHub board created with 21 issues. 7 closed in Week 1.

Complete · 1 week ahead of plan

Week 2
24–30 Apr

Architecture Decision · SFMC Audit · Operating-Model Deck

Salesforce AgentForce + Data Cloud workshop (3h, full team). BI session #4 — credit cost analysis Approach A vs B (~60M credits/year for full-DC IR ruled out). Polly + Simon SFMC Pillar walkthrough — full automation stack documented. Architecture decision 29 Apr: Hybrid model — Snowflake gold + DC for IR, Consent (Approach B), and activation. Campus pilot first. SFMC Classic full audit: 362 automations, 1,534 journeys, 41 CloudPages, 14 query families classified. Snowflake medallion + Zero Copy share audited (779 tables · 121 share objects). MC reconnection cleanup begun by Jessica (31 streams deleted, audit confirmed safe). Operating-model deck (#142) delivered — 14 slides, in review. All Subscribers migration spec (#82) drafted with corrected baseline.

Complete · architecture decided · SOW checkpoint

Week 3
1–7 May

Campus Pilot Kick-off · MC Reconnection Production · Phase 2 Remediation Begins

Campus canonical pilot (per architecture decision) — first end-to-end build through the operating model. MC connection recreation in production (Jessica), DMO unmap → delete → recreate sequence. BDT viability spike (3 representative SFMC SQL queries classified). Phase 2 remediation begins (#85 BigQuery DLO workaround, #92 Lead Source dedup, #88 stream error monitoring on the surviving streams).

Starts Fri 1 May

Weeks 4–6
8–28 May

MCN Configuration · CI Build · Consent & All Subscribers

Calculated Insights build (#81 — PipeAge, Suppression Flags, Footer CTAs, Application Link). Consent architecture implementation (#79). All Subscribers migration execute (#82) once legal scope confirmed. Programme footer CTAs (#87). End-to-end Pillar journey on MCN.

Planned

Weeks 7–8
29 May – 11 Jun

Validation & Handover

QA of all configurations. Team enablement sessions (MAT, BI, Tech) per #93. Documentation handover via GitHub. Final sprint review and SOW closure.

Planned

Critical Findings

Status as of End-of-Week 2

Architecture Decision — Resolved 29 April

Hybrid model confirmed: Snowflake delivers harmonised Gold layer (no IR); Data Cloud handles Identity Resolution natively, Consent (Approach B for consent only — never originates from Snowflake), and activation. ML stays in Snowflake. Campus canonical model is the agreed first proof point. Operating model deck (#142) currently in review with Hristo + Simon for sign-off.

MC Connection Cleanup — Underway (#80)

Per SF Support's recommended fix (unmap → delete streams → delete connection → recreate), Jessica has deleted 31 sfmc-connector streams (lowercase sfmc_*). 22 alive streams remain. Cross-checked all 47 CIs and 60 segments — zero usage of the deleted set. EmailEngagement DMO is the only consumer of any unmap-affected DMO; consumers are 2 Last7D rolling-window CIs (tolerate gap during reconnect).

All Subscribers Migration — Baseline Corrected (#82)

Initial assumption (HULT.DATA_IMPORTS.UNSUBSCRIBER = 2.14M) was wrong — that table is the _Unsubscribe event log. SFMC truth from direct query: 362,348 current global unsubs. Three new questions block execute: legal scope (current 362K vs 1.5M historical superset?), Snowflake current-state master availability, DC subscriber DLO numeric status mapping.

Data Architecture

Data Cloud Object Model — Interactive Diagram

Full DLO → DMO lineage covering all 137 DMOs, 203 DLOs, and 224 DSOs across both Enrollment and MyStudents orgs. Interactive — zoom, pan, and click to explore relationships.

Open full diagram in dbdiagram.io →

Password protected · request access if needed

Issue Board

Full Workstream Status

#	Issue	Phase	Status
72	Access provisioning (sandbox, JIRA, GitHub, MC)	1	Done
74	CI write-back diagnosis — CC_TopChoiceCampus__c	1	Done
75	Opportunity 3-stream split audit	1	Done
76	Data Stream baseline — 176 streams	1	Done
77	Repo scaffold + spec-driven workflow	1	Done
112	V3 CRM Data Kit field maps (963 mappings)	1	Done
143	SFMC Classic full audit (362 autos · 1,534 journeys · 14 query families)	1	Done
96	BI architecture review — Hybrid decided 29 Apr	1	Decided
80	MC connection recreation — 31 streams cleaned, recreate next	4	In Progress (Jessica)
142	Operating-model deck — 14 slides, sign-off pending	4	In Review
82	All Subscribers migration — spec drafted	4	Spec drafted
160	Verify MC segments not in use — audit complete	2	Audit done
73	Two-org Data Space/Data Share architecture	1	Backlog
79	Consent architecture — MCN consent solution	4	Backlog
81	Calculated Insights build — PipeAge, Suppression, CTAs	4	Backlog
83	Sender profiles + Reply Mail Management	4	Backlog
84	WhatsApp / Unified Messaging — 5 bugs	4	Backlog
85	BigQuery DLO — batch data transform workaround	2	Backlog
86	WhatsApp bulk send — MCN setup	4	Backlog
87	Programme-specific footers + application link CTAs	4	Backlog
88	Stream error monitoring — 27 streams	2	Backlog
92	Lead Source deduplication	2	Backlog
93	Team enablement sessions	5	Backlog
149	Campus canonical pilot — first proof point	2	Backlog

Financial

Budget & Utilisation — End of Week 2

Weeks Completed

2 of 8

Budget Used (capped)

€5,000 of €20,000

Invoice status: Invoice for the first two weeks (€5,000 = 2 × €2,500 weekly cap) sent today. Fortnightly invoicing, NET 30.

Cap rule honoured: Weekly cap is fixed at €2,500 regardless of actual hours worked. Wk 2 logged 32.5h actual (12.5h above the 20h cap-equivalent); Wk 2 close logged a further ~9.5h on the final day to ship the Snowflake audits, operating-model deck and All Subscribers spec. Total of ~22 extra hours absorbed beyond contracted capacity to maintain delivery pace through Phase 1 closure, the architecture decision, and the Phase 4 spec / deck work that surfaced ahead of schedule. None of this overage is invoiced — the contracted SOW total of €20,000 will not be exceeded.

Pace: 25% of budget used at 25% of timeline = on-budget pace, with Phase 1 fully closed and Phase 4 prep already underway (operating-model deck delivered).

Looking Ahead

Next Steps — Week 3 (starts Fri 1 May, per SOW Phases 2 + 4)

Operating-model deck sign-off (#142) — James + Simon review the 14-slide deck, give green light or request revisions. Unblocks first-pilot kick-off.

Campus canonical pilot kick-off (#149) — first proof point per architecture decision. Standardise BOSS / LON / SF naming, build canonical layer in Snowflake, integrate with Data Cloud.

MC connection recreation in production (#80) — continue sequence: unmap remaining DMOs → delete connection → recreate (UMA packages auto-install) → remap. Requires low-traffic window.

Consent architecture (#79) — design Comm Sub Consent + Comm Sub + Channel Type DMO build pattern. Maps SFMC Publication Lists → MCN topics. Must land before #82 execute.

All Subscribers migration spec close-out (#82) — pending legal scope decision on opt-out historical depth. Then execute backfill.

Phase 2 remediation continues — #85 (BigQuery DLO workaround), #92 (Lead Source dedup), #88 (stream error monitoring on the surviving streams).

Phase 4 prep extended — #81 (CI build: PipeAge / Suppression / CTAs), #87 (programme-specific footers), #83 (sender profiles + reply mail mgmt), #86 (WhatsApp bulk send).

Decisions Pending

Open Questions for Leadership

Q	Question	Owner	Blocks
Q1	Operating-model deck (#142) — sign-off?	James + Simon	Campus pilot kick-off
Q2	Legal scope for All Subscribers migration — migrate just current global unsubscribes, or the broader historical population (subscribers who unsubed at any point in Classic, even if since re-subscribed)?	Ku / Simon (legal)	#82 execute
Q3	Data Graph naming convention — agree the per-journey-family pattern (Pillar PG/UG, BAU Lead/Inquiry, Announcement, Fair, Accepted) before first build?	Marketing (Simon · Polly · Ash)	First Data Graph build
Q4	Campus canonical pilot — confirm Campus as the first proof point (per architecture decision), or pivot to a different family for first end-to-end run?	James + Simon	Pilot kick-off scope

SOW Checkpoint

Health Check at End of Week 2 (per SOW §6)

Schedule

Phase 1 closed on time. Phase 2 starting (MC reconnection cleanup begun). Phase 4 spec work already in flight (operating-model deck + #82 spec) — running ahead on documentation deliverables.

Budget

On budget — 25% used at 25% of timeline. Cap rule applied (no overage billed despite +22h dedicated above contracted hours).

Scope

No scope expansion. SOW §5 boundaries respected (Automation Studio migration, CloudPages rebuild, CRM Flow / Apex changes — all out of scope, surfaced as Change Order candidates only).

Risk

One risk open: 4 open questions above need answers in Wk 3 to keep the Phase 4 build window intact and the Campus pilot on track.

Net read

Engagement is on schedule and on budget at the Week-2 checkpoint. Architecture decision landed cleanly (Hybrid). Phase 1 deliverables are over-spec (Snowflake medallion audit, SFMC Classic full audit, operating-model deck were not in the SOW but proved necessary to unblock Phase 4). The next 6 weeks need fast turnaround on the 4 open questions to land MCN configuration and validation in Phases 4–5. Weekly checkpoint cadence to be agreed with James for the back half.