HubSpot → Dynamics 365 CRM migration + automation

The trigger

The VP Sales had four sources of truth. HubSpot CRM was the official record. A Microsoft Access database held inherited records from a 2019 acquisition that nobody had ever fully merged. Eight reps maintained personal Excel spreadsheets that diverged from HubSpot whenever a deal got "interesting." And there was an old Salesforce export from 2022 that two senior reps still consulted because they remembered it had cleaner activity history.

The board mandate was Microsoft consolidation. The company had moved to Microsoft 365 a year earlier and was leaving HubSpot, Salesforce, and the Access database on the cleanup list. Dynamics 365 Sales was the destination. The mandate didn't account for the data-quality reality.

The trigger event was a missed follow-up that cost a $140K deal. Two reps had been working the same account, neither aware the other was active. The customer received conflicting emails from both reps in the same week, lost confidence, and chose a competitor. The VP Sales walked into the board meeting with that loss and the board approved the consolidation budget the same hour.

The week-one discovery

Week zero audit found the numbers. 9,212 contacts in HubSpot. About 7,500 in the Access database — a number we'd never actually confirmed because the database had no proper count query (it had been Access since 2012 with no schema documentation). Roughly 2,000 contacts in the rep Excel files with an estimated 800 overlap to HubSpot. 1,047 accounts total but 312 had duplicate spelling variants ("Acme Corp" vs "ACME Corporation" vs "Acme, Inc."). 40,000+ activity rows that hadn't been touched in 18 months but were still being preserved "in case we need them."

The HubSpot data-export API was the first surprise. Rate-limited at 100 contacts per minute. Full export at scale would have been a 90-minute job. We built the ETL to handle it in chunks with resume capability so a network blip wouldn't restart the whole export.

The Access database was the second surprise. We couldn't connect to it remotely — it was an MDB file on a shared drive that one of the older reps had been maintaining. We pulled a copy locally, opened it in Access for the first time, and discovered the schema was reasonably clean. The data inside was the problem: ~30% of rows had been hand-edited in ways that broke the field types (dates entered as free-text, phone numbers in the email column, that kind of thing).

The third discovery was 38 contacts with placeholder emails — "noreply@…", "donotuse@…", "test1234@…". Someone had been entering these for years as a workaround when a real email wasn't available. These would have polluted any marketing automation if we'd migrated them as-is.

The architecture decisions

**Dynamics 365 Sales Enterprise vs Salesforce Essentials.** The client's Microsoft commitment made this a one-option decision from a product perspective, but we did the comparison anyway for the architecture memo. Salesforce Essentials would have come in at ~$25/seat/month vs Dynamics at $95/seat/month, but the Microsoft 365 + Power Automate integration story closed the gap and then some at the client's scale. Sticking with the stated direction.

**Power Automate vs custom workflow engine.** Power Automate was already in their stack (used elsewhere for Teams notifications and SharePoint approvals). The 12 most-common manual sales-ops actions (lead routing, follow-up reminders, owner-reassignment rules, deal-stage notifications, etc.) could all be expressed as Power Automate flows. The ops team can edit them post-handoff without engineering involvement. We documented the upgrade path to Azure Logic Apps for the day the workflow needs more compute than Power Automate offers.

**Google Cloud Functions for ETL vs Azure Functions.** This decision raised an eyebrow internally — we're recommending Google in a Microsoft shop. The reason: the engineer doing the ETL build had 4 years of GCF production experience vs 6 months on Azure Functions. The ETL ran for a total of 11 hours over the migration period and was decommissioned at cutover, so the cross-cloud question was moot. We documented the decision so future audits don't flag it as "why is there a Google account on the invoice."

**Owner-reassignment rule.** The hardest decision. With 1,047 accounts and 312 dedup variants, we had to assign each consolidated account to ONE sales rep. The candidates: alphabetical fallback (clean but unfair), most-recent-activity (fair but volatile), current-deal-stage (fair AND stable). We debated for an hour with the VP Sales. Current-deal-stage won — whoever was furthest along in an active deal got the account. The 4 accounts where current-deal-stage was tied went to a manual-review queue.

The build

Weeks 2-3 ran daily shadow runs against HubSpot. Each morning the ETL pulled a fresh export, ran the normalization rules, and produced a "this is what Dynamics would look like" snapshot. The VP Sales reviewed the snapshot each afternoon. The first 3 days surfaced 12 normalization rules we'd missed — things like "if first name is empty, derive from the email handle" and "if phone number has a +1 prefix, strip it for US records but keep it for international."

The 40,000-row activity dataset was its own project. Most of the rows were preserved for historical reference but not migrated as activities — they were exported to a CSV archive and stored in SharePoint. The 4,500 most recent activity rows (last 12 months) were migrated as Dynamics activities with date-preserving timestamps.

The placeholder-email discovery: we found 38 contacts with "noreply@" or "donotuse@" addresses. These were quarantined to a "data hygiene review" board in Monday.com (the client already used Monday for cross-team tracking). The VP Sales walked through each one with the relevant rep over a single afternoon. 14 contacts had real emails available in old correspondence; the other 24 were stale records that got deleted.

The dedup pass had its own surprise: 4 contacts had been emailed by 3 different sales reps in the same week. Each rep believed they were the account owner. The current-deal-stage rule resolved 2 of them automatically; the other 2 went to manual review and the VP Sales made the calls.

The cutover

Cutover happened on a Friday night. The ETL had been rehearsed twice in shadow runs that week, so the actual cutover was a low-drama 4-hour execution: snapshot HubSpot at 6pm, run the migration ETL through 10pm, run validation checks until midnight, sign off, send the all-clear email to the sales team at 6am Saturday for a Monday-morning go-live.

By Monday 9am the sales team was on Dynamics 365. Power Automate workflows were live. Mailchimp was routing leads through to Dynamics via the new integration. The VP Sales spent the morning watching the support email queue, expecting complaints. None came.

"The migration was clean enough that nobody complained on Day 1" — the proof quote in the case file — was the VP Sales's actual sentence on the post-launch debrief call. A CRM swap that nobody notices on Monday morning is the test. Most fail it.

Ninety days later

75% manual-task reduction is real and measurable. Power Automate logs show 4,000+ automated actions per month — the 12 workflows now handle what previously consumed 75% of two sales-ops coordinators' time. The coordinators have been reallocated to higher-leverage work (campaign attribution analysis, lost-deal post-mortems, win-rate cohort tracking).

The Mailchimp + Dynamics integration cut the marketing-to-sales lead-handoff window from 3 days to 4 hours. Marketing-qualified leads now route to the right Dynamics rep within an hour of the form submission, with the campaign attribution preserved on the lead record. The reps know which campaign drove the lead before they make the first call.

Account ownership has been stable. The current-deal-stage rule worked. Three months in, only 6 accounts have been manually reassigned (vs an internal estimate that we'd see 30-50). The board has not had to mediate a single account-ownership dispute.

What we'd do differently

The 38 placeholder-email quarantine should have been a discovery-phase finding, not a build-phase surprise. We'd add a 5-minute SQL pre-flight to surface these on any future CRM migration: `SELECT COUNT(*) FROM contacts WHERE email LIKE '%noreply%' OR email LIKE '%donotuse%' OR email LIKE '%test1234%'`. Cheap to run, would have caught this on day one.

We'd build the data-hygiene-review board in Monday as a deliverable from the start, not as a mid-build response. Any CRM migration surfaces stale records that need human review; making that an explicit deliverable instead of an emergent task is the cleaner pattern.

The Google Cloud Functions decision in a Microsoft shop was the right pragmatic call (we used the engineer's strongest tool for a transient ETL) but it added a footnote to every architecture review afterwards. Next time we'd either justify it more clearly upfront in the SOW or just absorb the Azure-Functions ramp-up time.