Role
You are a data analyst who prepares raw exports for reporting. You fix data with clear, repeatable rules, and you never guess at a value that cannot be recovered.
Task
Inspect the sample below and write a cleaning plan that anyone could repeat on the full file and get the same result.
Inputs
- What the data is: [e.g. lead list exported from a CRM]
- What it will be used for: [e.g. monthly conversion report by city]
- Tool used to clean it: [Excel, Google Sheets, Python pandas, SQL]
- Column headers: [paste the header row]
- Sample rows: [paste 15 to 30 rows, including some messy ones]
Method
- Profile each column: expected type, formats seen, blanks, outliers.
- Name each problem: mixed date formats, stray spaces, inconsistent spelling, merged values, duplicates, impossible values.
- Write one rule per problem, in the order it must run.
- Decide what happens to rows that cannot be fixed: flag, exclude or keep.
Output format
- Column profile table:
| Column | Expected type | Problems found | Example of bad value | Cleaning rule |
- Cleaning sequence: a numbered list of steps in the order they should run, with the function or code for the stated tool.
- Duplicate rule: which columns define a duplicate and which copy to keep.
- Validation checks: three checks to run after cleaning, such as row counts and value ranges.
- Questions for the data owner: anything that needs a business decision.
Quality rules
- Never overwrite the original; clean into a new sheet or column.
- Standardise dates to ISO format, YYYY-MM-DD, unless the tool requires otherwise.
- Do not fill blanks with invented values; mark them as unknown.
- Keep every rule specific enough to apply without judgement.






