How to Clean Transaction Data Before Analysis

The safe cleaning principle

Clean data preserves the original description while adding a normalized merchant, transaction class, category, and confidence note.

Cleaning should make transactions easier to compare without erasing the evidence needed to audit a decision.

Keep raw and normalized fields

Never overwrite the bank description. Add separate fields for normalized merchant, transaction class, spending category, and review note. “SQ *CAFE 00391” can become “Cafe” while the original remains available.

Resolve transactions in this order

Remove exact duplicate exports.
Exclude pending items when a posted version exists.
Pair internal transfers by amount and nearby date.
Match refunds to original spending.
Split mixed receipts only when you have evidence.
Normalize merchants after the accounting treatment is known.

Privacy note: local tools reduce exposure, but exported statements may still contain account fragments and sensitive descriptions. Store and delete them deliberately.

Continue from a verified file

After preserving the raw description, standardize genuine merchant variations with Merchant Name Normalization for Spending Analysis.

Before totaling debits, separate transfers from real purchases by following How to Categorize Spending for Better Decisions.

Upload the reconciled file and inspect its category pattern in the private spending analyzer.

Validate the cleaned file

Count source rows, duplicates removed, transfers matched, refunds paired, split rows created, and items left unresolved. The cleaned outflow total should be explainable from the raw export; a silent difference means the process changed more than labels.

Keep confidence visible

Use high confidence for documented or deterministic rules, medium for a stable pattern, and low for inference requiring review. Sample normalized merchants regularly, especially processor descriptions. A smaller accurate dataset is more useful than a complete-looking file built on guesses.

Create control totals before editing

Record the source filename, export date, row count, total credits, total debits, earliest date, and latest date before changing anything. After cleaning, prepare a bridge that shows duplicate rows removed, pending rows excluded, refunds paired, transfers matched, and split rows added. The bridge should explain every difference from the source totals.

Control example. A source export has 842 rows and $18,420 of debits. Two exact duplicates totaling $94 are removed and a $600 card payment is reclassified as a transfer. The cleaned file still preserves $18,326 of ledger debits, while the spending view excludes the $600 transfer. Row cleaning and spending classification are separate steps.

Use a review queue for uncertain records

Do not stop the entire analysis because ten transactions are ambiguous. Give them an unresolved class, a confidence level, and a review reason such as missing receipt, unclear processor, possible reimbursement, or unmatched transfer. Sort the queue by financial effect rather than reviewing every small item first.

Keep transaction identifiers so a later export can be matched safely.
Store split amounts that add back to the original transaction.
Never turn an inference into a permanent merchant rule without sampling it.
Re-run control totals after each bulk rule or import.

The clean file is finished when it is explainable, not when every row looks certain.

Transaction cleaning at a glance

A cleaned transaction keeps the bank's original evidence while adding a merchant label, transaction class, category, and confidence level in separate fields.

Fields that make the file auditable

Raw description, posted date, and signed amount
Normalized merchant and transaction class
Category, confidence, and a note for uncertain treatment

Audit conclusion

Reconcile row counts and totals. Review low-confidence items instead of hiding them in a broad miscellaneous category.