Data Engineering · Dynamics 365
A UK/IE Microsoft partner had industry classification on only a small share of its customer accounts, so segmentation, campaign targeting and board reporting were all running on guesswork. I built a four-source enrichment pipeline that closed most of the gap, and wrote it so it can never confidently write the wrong answer.
Industry was an optional field in Dynamics 365, filled in inconsistently over a decade of sales activity. Most active customers had nothing in it, so any report slicing revenue, churn or opportunity by sector was quietly wrong, and marketing could not build a sector list without manually eyeballing account names.
The obvious fix — bulk-guessing from account names — is exactly the fix that poisons a CRM. The real requirement was coverage and trustworthiness: every value written had to be defensible, and anything uncertain had to land on a human's review list rather than in the database.
Treated as a waterfall rather than a single integration. Each source fills what the previous one could not, and each has its own write threshold.
Sole traders and trading names appear on no register at all. For those, owner review is the only honest path.
1. HubSpot → Dataverse, nightly. Marketing had already curated industry on a large slice of the company records in HubSpot. A .NET 8 isolated Azure Function, triggered by a Logic App overnight, pulls companies modified since a watermark held in blob storage, matches them into Dataverse, and patches industry and industry group together so the two never drift apart. Every run emails a summary, on success and on failure.
2. SIC codes already sitting in the CRM. A credit-referencing feed had been writing SIC codes onto accounts for years without anyone deriving anything from them. A Python pass mapped SIC divisions onto the industry option set, giving free coverage from data already in the building.
3. Companies House, weekly. A timer-triggered function takes a capped batch of blank customer accounts each week, normalises the name, searches the register, and writes industry, group, SIC and SIC description — but only on an exact normalised match. High-but-imperfect scores are logged as review candidates and never written.
4. CRO Ireland, monthly. Ireland has no equivalent live API, so the pipeline consumes the CRO's bulk open-data company file. Recent registrations carry a NACE Rev 2 code; older ones carry a principal-objects code on the Rev 1.1 scheme, numbered completely differently, needing its own mapping table. Running old codes through the modern lookup silently mislabels a decade of Irish accounts.
A Logic App fires the nightly sync over HTTP and receives a JSON summary back; the weekly Companies House pass runs on a timer trigger inside the same .NET 8 isolated Azure Function app. Secrets live in Azure Key Vault and are read via managed identity; the delta watermark sits in blob storage so each nightly run only pulls what changed since the last one. Every run — success or failure — ends in a summary email, so a silent failure cannot quietly stall enrichment.
Industry and industry group are always patched together in a single request, mapped through one lookup, so the two fields can never drift apart. Writes go through the Dataverse Web API as update-only PATCHes — the pipeline can amend an account but can never create one — and anything below the write threshold ends up in the run log as a review candidate instead of in the database.
A plain contains() lookup matched one company's name inside another's, where the shorter name happened to fall on a syllable boundary in the longer one, and wrote to the wrong account. The fix was a word-boundary check: the characters either side of the match must be non-alphanumeric. Fuzzy matching without a boundary rule is not fuzzy, it is wrong.
Promoting low-confidence candidates by corroborating the registered town happily matched a childcare provider onto an unrelated tyre centre: same town, same first word, a high similarity score, and the differing words were the entire business. A second weak signal does not add up to a strong one. Exact normalised match is the only grade good enough for an unattended write.
The ambiguous list turned out to be worth as much as the enrichment itself — it doubles as a duplicate-accounts report, surfacing years of accumulated CRM sprawl as a side effect.
$top is a total cap, not a page size. Paginate with Prefer: odata.maxpagesize and @odata.nextLink, or use it as a deliberate per-run cap.Industry and industry group are now populated across the large majority of active customers and maintained automatically — nightly from HubSpot, weekly from Companies House, monthly for new Irish registrations. Sector reporting and campaign segmentation run off real data. What is left is not a gap in the pipeline; it is the businesses that appear on no official register, and they sit on a named review list rather than being invented.
The matching rules and operational gotchas have since become the house pattern for other third-party-API-to-Dataverse integrations.