Cleaning contract data

An error in the data does not stay where it is. A single payment term of 450 days instead of 45 moves a median. A duplicate row doubles a contract in a total. A euro amount in a dollar column adds the wrong number to a sum. Each error then travels into every chart and every report built on the data. Cleaning is the cheapest place to stop them.

• Dates. 03/04/2025 may be 3 April or 4 March. Use one format that cannot be misread, such as 2025-04-03. Check that an end date is not before its start date.
• Units and currencies. Dollars and euros, days and weeks, thousands and ones. A column must hold one unit, stated in its definition. If currencies are mixed, record the currency and convert at a stated rate on a stated date.
• Names. “Fenmoor Packaging Ltd”, “FENMOOR PACKAGING” and “Fenmoor Packaging Limited” are one party. Add a standard name column and keep the original.
• Duplicates. The same contract entered twice, often from two sources. Remove one, and log it.
• Blanks and zeros. Decide what a blank means (not recorded) and what a zero means (a real zero), and write it down.
• Impossible or implausible values. A term of minus two years, a payment term of 450 days. Check them against the source. Do not guess.

1. Never overwrite the original. Keep the raw value, add a cleaned value, and log what changed and why.
2. Go back to the contract when a fix is a judgement. Swapping two dates might be right, or might not.
3. Mark what you cannot fix as unknown, with a reason. An honest blank beats an invented number.
4. Count what you changed. “We corrected 14 of 600 records” tells the reader how much to trust the rest.

Before you count or chart anything, run through: Are the dates in one format and in order? Is there one unit and one currency per column? Are names standardised? Are there duplicates? What do blanks and zeros mean? Are there any extreme values, and have they been checked? Five minutes on this saves a correction later.