Data migration in a utility: from the old CRM and billing to the new one

Phases, quality KPIs, reconciliations and handling of rejected records to move customers, contracts and invoices without losing trust in the data.

MethodologyutilityCRMmigrazione datiquadraturequalità del dato19 November 2026 · 6 min read

The first trial load ends with thousands of rejected records. That is normal: migration is not a one-off load but a series of trial runs that refine the mapping and the rules before the final one. What matters is the KPIs per entity, the reconciliations, and who answers for each rejected record.

Why migration is the main risk

An energy retailer manages long relationships: contracts that last for years, corrected invoices, cases with the distributor, complaints. The new system starts empty and its credibility depends on what is transferred into it. A mapping error can produce wrong invoices, wrong communications, supply points with no contract, or duplicate cases sent to the SII (the Italian central market data hub for electricity and gas).

The phases

  1. Data model analysis and mapping. The entities of the source and target systems are described and the transformation rules are written: from which field to which field, with which decodings, which default values, which filters. The mapping is a living document, with an owner and a version log.
  2. Extraction and transformation. The procedures are built, often in several steps: extraction, cleansing, enrichment, loading.
  3. Trial runs. Each run is a full or partial migration on a test environment, followed by quality checks. A serious project plans several cycles (three or four are not unusual) with growing goals: the first to find the mapping problems, the last to rehearse timing and automation.
  4. Dry run. The final migration is simulated with the real timing, people and steps of cutover day.
  5. Final migration. Execution in the planned window, with checkpoints and a rollback criterion defined in advance.
  6. Post-migration. Final reconciliations, corrections, handling of the remaining rejected records.

The entities of a utility

EntityWhat it holdsTypical points of attention
Customer / master dataIndividuals and companies, contacts, consentsDuplicates, privacy consents, tax codes and VAT numbers that are formally invalid
Supply pointElectricity or gas delivery point, technical dataConsistency with the distributor's data, status in the SII
ContractTerms, product, start and end datesDiscontinued products, inconsistent dates
Billing plan / payment methodFrequency, delivery channel, collection methodDirect debit mandates, IBANs, data that cannot be migrated in clear text
Invoices and open itemsIssued documents, due dates, payments, receivablesAccounting reconciliation per customer, instalment plans
Readings and measuresConsumption, self-readings, distributor measuresData volume, history needed for final-balance billing
CasesRequests and complaints in progress, historySee the article on in-flight cases
DocumentsPDF invoices, communications, signed contractsLink with the document archive

Quality KPIs per entity

For each entity a set of indicators is defined and calculated at every run:

  • completeness: percentage of records migrated against the extracted universe;
  • rejections: number and percentage of rejected records, by cause;
  • mapping correctness: percentage of fields in a sample that match the expected value;
  • referential integrity: no contract without a customer, no supply point without an active contract;
  • consistency between entities: for example, the number of active supply points matches the number of billed supplies;
  • timing: duration of extraction, transformation and loading.

Thresholds and tolerances are a project choice: they must be agreed with the business and tied to risk (a 0% threshold applies to accounting amounts, while descriptive fields may allow a different one).

Reconciliations

A quantitative reconciliation compares counts and sums between source and target: number of customers, supply points and contracts by status, sum of open items, balance per customer. It is done by aggregates and, for monetary values, also customer by customer. A qualitative reconciliation checks that the values are semantically right: a sample of records is compared field by field with the source system, paying attention to decodings, dates, formats and rounding. Sampling can be random, stratified (by segment, product, status) or targeted at known edge cases.

Handling rejected records

A rejected record must not disappear. You need a register with: entity, identifier, cause, severity, owner, status and resolution date. Typical causes are missing mandatory data, out-of-domain values, non-existent references, duplicates. The actions can be: fix at the source and rerun, fix with a transformation rule, fix by hand in the new system, exclude with a documented reason. Before go-live you need to know how many rejections remain, for which customers and with what impact.

Archiving and time cut-offs

Not everything has to be migrated. For each entity you define a time cut-off: for example, invoices of the latest periods and open items go into the operational system, the rest into a searchable historical archive. Take into account:

  • accounting and tax retention obligations (see the references);
  • the customer care team's operational need to see the history;
  • the need to calculate final balances and corrections on past periods;
  • data subject rights and the maximum retention time for personal data.

Personal data and trial environments

Every trial run with real data is a processing of personal data. The options are to run it in environments with the same security measures as production, or to mask the data as it leaves the extraction. Quality checks can be run by tools that print only outcomes and technical values, so that personal values do not circulate in the reports.

Common mistakes

  • Treating as "migrated" what is only "loaded": the data must also be usable by the processes.
  • Reconciling only counts and not amounts.
  • Discovering at the last run that codes (statuses, contract types) have different decodings from the ones assumed.
  • Forgetting the data that the target system generates by itself, creating inconsistencies between the two.
  • Not comparing migrated data with what has already been sent to external systems.

Frequently asked questions

How many runs are needed? There is no fixed number: as many as it takes to get stable KPIs in two consecutive runs.

Can you migrate incrementally? Yes, with a delta migration after an initial snapshot; see the article on cutover.

Who approves quality? The business, on the proposal of the migration team, with evidence.

Should customers be informed? It depends on the impact: if channels, portals or payment methods change, yes.

Regulatory references

  • Regulation (EU) 2016/679 (GDPR), in particular the principles of minimisation, storage limitation and security. https://eur-lex.europa.eu
  • Legislative Decree 196/2003 (Italian Privacy Code), as amended by Legislative Decree 101/2018. https://www.normattiva.it
  • Italian Civil Code, art. 2220: retention of accounting records for ten years. https://www.normattiva.it
  • Presidential Decree 633 of 26 October 1972, art. 39: keeping and retention of VAT registers and documents. https://www.normattiva.it/uri-res/N2Ls?urn:nir:stato:decreto.presidente.repubblica:1972-10-26;633
  • Legislative Decree 82/2005 (Digital Administration Code) and AgID technical rules on the preservation of electronic documents. https://www.agid.gov.it
This article is an OIAR skill

What you have just read is the content of a skill from the OIAR catalogue, written for people. Knowledge like this, but richer and deeper, written for the agents, drives the interviews, the analysis and the report inside the platform. Skills are used in OIAR: to try this one on a project of yours you need an account.

data-migration-methodMethod for migrating data from the old to the new CRM and billing system of a utility: trial runs, quality KPIs per entity, quantitative and qualitative reconciliations, classification and assignment of rejected records, final migration. Use it to define or review the migration plan of a CRM and billing project.