How to improve data quality before automation or AI

Your company just invested in automation or an AI tool — and within weeks, the results are wrong, the processes break, and nobody trusts the output. Nine times out of ten, the technology is not the problem. The data feeding it is. This article walks you through exactly how to find the data quality issues that will sabotage your automation or AI project, and how to fix them before they do.

Why does bad data break automation and AI so reliably?

Automation and AI do not tolerate ambiguity the way humans do. A sales rep sees "Acme Corp" and "ACME Corporation" and immediately knows they are the same customer. An automated invoice matching process does not — it creates two accounts, splits the purchase history, and sends a payment reminder to a customer who already paid.

AI models are even less forgiving. A demand forecasting model trained on order data where 15% of product codes are inconsistent will confidently produce forecasts that are systematically wrong. The model is not broken — it learned exactly what it was taught. Garbage in, garbage out is not a cliché; it is the first law of applied AI.

The five data quality problems most likely to derail your project are:

1. Duplicates — the same customer, product, or order exists multiple times under slightly different names or IDs
2. Incomplete records — key fields like delivery address, VAT number, or unit of measure are missing
3. Inconsistent formats — dates as DD/MM/YYYY in one system and MM-DD-YY in another; phone numbers with and without country codes
4. Outdated data — customers who moved, products that were discontinued, price lists that expired two years ago
5. Unstructured fields — free-text notes fields that contain information (payment terms, special instructions, product variants) that should live in structured, queryable columns

How do you actually find these problems?

Most organisations discover data quality issues the hard way — after the automation has already failed. A smarter approach is a structured data audit before you build anything.

Step 1: Profile your data

Data profiling means running systematic statistics over your datasets to understand what is actually in them — not what you assume is in them. For each key table (customers, orders, products), measure:

• Completeness: what percentage of records have a value in each field? A customer table where 30% of records have no email address is a problem for any marketing automation.
• Uniqueness: how many duplicate records exist, and on which keys? Run a simple group-by on name + postcode, or name + VAT number, and count how many groups have more than one record.
• Consistency: do values follow expected patterns? A regex check on phone numbers, postal codes, or IBAN fields will surface format chaos immediately.
• Validity: are values within expected ranges and reference lists? An order line with a quantity of -1 or a product category of "??" is invalid data that will propagate into every downstream report and model.
• Freshness: when was each record last updated? A CRM with 4,000 accounts where 60% were last touched more than three years ago likely contains a significant proportion of dead data.

You do not need expensive tooling for this. A well-structured SQL query, a FileMaker script, or even a pivot table in Excel will surface the worst problems in an afternoon.

Step 2: Map data flows between systems

Data quality problems often originate at system boundaries. An order gets entered into FileMaker, and then re-typed by hand into Exact Online — every single order, every single day. Each manual re-entry is an opportunity for a typo, a format change, or a missing field. Before you can fix the data, you need to know where it comes from and where it goes. Draw a simple map: which systems hold which data, in which direction does it move, and which steps are manual?

This map will immediately show you where quality breaks down. If the product master lives in your ERP but product descriptions are re-entered manually into your webshop CMS, that is where your inconsistencies are born.

Step 3: Prioritise by impact, not by volume

Not every data quality problem is worth fixing immediately. Prioritise based on which fields and records your automation or AI will actually use first. If your first automation is purchase order matching, focus on supplier data quality and product codes — not on whether customer middle names are filled in.

A useful prioritisation matrix:

Field / dataset	Used by automation?	Error rate	Fix priority
Customer VAT number	Yes	18% missing	High
Product EAN code	Yes	7% inconsistent	High
Contact person title	No	40% missing	Low
Order delivery date	Yes	3% blank	Medium

How do you fix data quality problems?

Fixing duplicates

Duplicate resolution has two parts: finding duplicates and merging or suppressing them.

For finding: use fuzzy matching on name + address or name + VAT number. Tools like OpenRefine (free) or a custom database script can cluster near-matches and flag them for review. Never auto-merge without a human check — the cost of incorrectly merging two legitimate different companies is higher than leaving one duplicate in place.

For merging: decide on a "golden record" strategy. Which record is the master — usually the most recently updated, or the one with the most complete fields? Merge the others into it and retire their IDs, making sure any foreign key references (orders, invoices, contacts) point to the surviving record.

Fixing incomplete records

For critical missing fields, you have three options:

1. Backfill from another source — if VAT numbers are missing, look them up in the Chamber of Commerce register or your accounting system and import them.
2. Ask the customer — a targeted email campaign to re-confirm contact details works well for B2B customer bases once every two years.
3. Mark and quarantine — if you cannot fill the gap, at least flag the record so the automation skips it rather than processing it incorrectly. A process that skips 5% of records cleanly is far better than one that processes 100% of records with errors.

Fixing inconsistent formats

Format normalisation is usually scriptable. Write a transformation rule for each field:

• Phone numbers → strip all non-numeric characters, add country code prefix, store as +31612345678
• Dates → convert all variants to ISO 8601 (YYYY-MM-DD) at the point of entry and in the database
• Country names → replace all free-text variants ("Netherlands", "The Netherlands", "NL", "Holland") with the ISO 3166-1 alpha-2 code
• Product codes → uppercase, strip spaces, enforce fixed length with leading zeros

Do this transformation once during the cleanse, and then enforce it with validation rules at every future entry point — not just in the database, but in every form, API endpoint, and import routine that feeds data in.

Fixing outdated data

Outdated data is the hardest problem because it looks clean — the record is complete and consistently formatted, it just no longer reflects reality. A few practical approaches:

• Decay rules: any customer not active (no order, no contact) in 24 months gets flagged as "needs verification" and excluded from automated sends until confirmed.
• Event-driven updates: when an email bounces, when a payment fails, when a delivery is returned — feed that signal back into the record immediately. Do not wait for a quarterly cleanse.
• Enrichment services: third-party data enrichment APIs (like Clearbit for company data or PostcodeAPI for address validation) can verify and update records at scale.

Fixing unstructured fields

This is often the most impactful fix and the most overlooked. A notes field that contains "payment in 60 days, attention: Finance dept, ref: PO-2021-0044" is useless to any automation. That information needs to be in three separate structured fields: payment_terms, contact_department, and purchase_order_reference.

The fix process:

1. Extract a random sample of 200–300 records from the free-text field
2. Manually categorise what types of information appear in it
3. Create the proper structured fields
4. Write a parsing script for the most common patterns ("payment in X days" → payment_terms field)
5. Manually review and fill in what the script cannot parse
6. Remove or archive the free-text field from active use and replace it with structured inputs going forward

Data quality checklist before you automate or go live with AI

Use this checklist before switching on any automation or feeding data to an AI model:

• Duplicate check run on all key entity tables (customers, products, suppliers)
• Completeness rate measured for every field used by the automation
• Format normalisation applied and validated for dates, phone numbers, codes, and countries
• Stale records flagged or quarantined based on last-activity date
• Unstructured free-text fields parsed into structured columns
• Data flow map created showing all manual re-entry steps
• Validation rules enforced at all entry points (forms, APIs, imports)
• A test run of the automation on a 10% sample with results manually verified
• Ownership assigned: who is responsible for ongoing data quality per dataset?

How do you prevent quality problems from coming back?

Cleansing your data once is not enough. Without governance, it will degrade again within months. The practical minimum for sustained data quality:

Validation at the point of entry is the single highest-leverage action. A postal code field that only accepts valid formats, a product code field that looks up the master list before saving — these prevent bad data from entering the system in the first place. Fixing errors at the source costs a fraction of cleaning them downstream.

Data stewardship means assigning a named person — not a team, a person — as responsible for the quality of each critical dataset. They own the rules, they review flagged records, and they have the authority to reject imports that do not meet standards.

Regular automated monitoring means running your profiling queries on a schedule and alerting when error rates cross a threshold. If your customer VAT number completeness drops below 95%, you want to know immediately — not at the next quarterly review.

Frequently asked questions

How long does a data quality project take before we can start automating? For a mid-sized company with one or two core systems, a focused cleanse of the highest-priority datasets typically takes two to six weeks. You do not need to fix everything before you start — fix what the first automation actually needs, then build governance so it stays clean.

Do we need special tools, or can we do this in our existing systems? In most cases, your existing database platform — FileMaker, SQL Server, or your ERP — has everything you need for the audit and cleanse. Specialised MDM (Master Data Management) tools are useful at enterprise scale but overkill for most SME projects. Start simple.

What is the difference between data cleansing and data governance? Cleansing is a one-time (or periodic) fix of existing bad data. Governance is the ongoing set of rules, ownership, and processes that prevents bad data from forming in the first place. You need both: cleansing to start clean, governance to stay clean.

Can AI fix our data quality problems automatically? AI and machine learning can assist with specific tasks — fuzzy matching for duplicates, named entity recognition for parsing free-text fields, anomaly detection for outlier values. But AI cannot replace human judgment about what the correct data should be. Use it as a tool in the cleansing process, not as a substitute for it.

We have data in multiple systems that don't talk to each other. Where do we start? Start with the system that feeds the automation you are building first. Trace the data backward from the output you want — the AI result, the automated email, the matched invoice — and clean the sources that contribute to that output. Trying to fix everything at once is how data quality projects stall.

---

If your data is scattered across systems that were never designed to work together, cleaning it manually is only a temporary fix — the problems will return the moment the next person enters a free-text note or re-types an order. Loggix helps businesses map their data flows, build validation and governance directly into custom FileMaker solutions, and connect systems through API integrations so that data enters once and flows correctly from there. If you are preparing for automation or AI and want to be sure your data foundation will hold, that is exactly the kind of challenge we work through with clients every day.