Establish what the records mean
Inspect raw values and document currency, decimal separator, grouping separator and blank meaning. The lab accepts plain decimal USD money with up to two decimal places. It rejects formatted or ambiguous amounts rather than silently guessing their value.
A worked example
Fictional fixtures: 1200.50 is supported; $1,200.50 needs a known-format transformation; 1.200,50 uses a different convention and cannot be interpreted safely without context; blank means unknown for an optional cost; −25 as a plain negative payment can represent a documented adjustment. A quoted comma keeps CSV structure intact but does not establish numeric meaning.
Choose the next action
Create a separate normalized column and preserve the original. Remove symbols or separators only according to the documented source convention. Validate negative amounts by record type: a refund payment may be negative while a specified direct cost should not be. Do not use a blanket “strip everything except digits” rule because it can remove signs and decimal meaning.
Check whether it worked
Compare a small known total before and after normalization, including decimals, blanks and refunds. Reopen the CSV as text and inspect row width where commas appear. Keep unresolved values out of calculations until their meaning is established. Record the transformation so later exports use the same rule.
A repeatable review order
- Original amount and currency. Record the evidence and mark any missing answer as unknown.
- Decimal/grouping convention. Record the evidence and mark any missing answer as unknown.
- Normalized amount or unresolved. Record the evidence and mark any missing answer as unknown.
- Negative and blank meaning. Record the evidence and mark any missing answer as unknown.
- Known-total and reopened-file checks. Record the evidence and mark any missing answer as unknown.
Put it to work
A record you can use
- Original amount and currency
- Decimal/grouping convention
- Normalized amount or unresolved
- Negative and blank meaning
- Known-total and reopened-file checks
Plain text. No email required. Store completed records in your approved business systems.
Next useful step
Need help applying this to your marketing? A strategy call starts with your business and the evidence you have.
Talk through your next step →For a general editorial question, email Chris. Please leave out customer details. Questions are private, and no response time is promised.