Prepare and clean analysis-ready tables

Before asking for insights, create a short inventory. Record the source, extraction time, owner, date range, row count, column count, and intended row unit. Then make a data dictionary with one entry per field:

• column name and plain-language meaning;
• expected type, such as date, category, integer, decimal, or Boolean;
• unit, currency, or timezone;
• allowed values or valid range;
• whether missing values are expected;
• whether the field is an identifier, measure, or dimension;
• known collection or transformation rules.

AI can draft this dictionary from headers and samples, but a sample cannot prove what a field means. A column named date could be an order date, shipment date, snapshot date, or local event date. Confirm definitions with the source owner or documentation.

Preserve the original file and profile it before cleaning. Useful checks include:

1. row and column counts;
2. data type inferred for each column;
3. missing count and percentage;
4. distinct count for categories and identifiers;
5. minimum, maximum, median, and selected percentiles for numeric fields;
6. earliest and latest dates;
7. duplicate counts for the expected key;
8. unexpected category spellings;
9. values outside sensible ranges.

Ask AI to propose profiling code or spreadsheet formulas, then save the output. Profiling after cleaning cannot tell you what the cleaning removed.

A safe prompt names the limits:

Profile this table without modifying it. One row should represent one
invoice line, uniquely identified by invoice_id + line_id. Return row
count, missingness, duplicate-key count, numeric ranges, date range,
and distinct status values. Flag anomalies; do not repair them yet.
Show the code or formulas used.

The request separates detection from repair and makes the expected key explicit.

Missing data is not one problem. A blank may mean unknown, not collected, not applicable, delayed, suppressed for privacy, or truly zero. Replacing every blank with zero creates false observations. Dropping every incomplete row may remove a particular group and bias the result.

For each important field, compare missingness across time, source, location, and segment. If one platform has far more missing conversion values, an overall conversion comparison may mostly measure tracking quality.

Choose and document a rule:

• preserve missing and exclude it only from calculations that require the field;
• use an explicit Unknown category when absence itself matters;
• impute a value only with a justified method and sensitivity check;
• repair from an authoritative source;
• exclude records under a named criterion;
• block the analysis if the missingness undermines the question.

Report how many rows each rule affects. Never let AI silently “fill gaps.”

Dates deserve special care. Parse them with a declared format, preserve the original field, and state the timezone. 03/04/26 can mean March 4 or April 3. Events near midnight may move between days when converted to UTC.

Numeric cleaning should remove formatting without erasing meaning. Parentheses may indicate negative accounting values. Percentages may be stored as 0.25, 25, or 25%. Currency conversion needs the exchange rate and effective date.

Category cleanup should use a mapping table. For example, UK, U.K., and United Kingdom may belong together, while Congo may be too ambiguous to map automatically. Keep the raw value, cleaned value, and mapping reason. Fuzzy matching can propose candidates, but a human should review uncertain merges.

Duplicate rows are not automatically mistakes. Exact duplicates may come from repeated exports. Repeated identifiers may represent updates, retries, installments, or one-to-many relationships.

Investigate duplicates in this order:

1. Confirm the intended unique key.
2. Count exact duplicate rows separately from duplicate keys.
3. Compare timestamps and status fields.
4. Identify whether later records replace or supplement earlier ones.
5. Define a deterministic retention or aggregation rule.

“Drop duplicates” is not a complete rule. “For each ticket ID, retain the record with the latest updated_at; if timestamps tie and values conflict, quarantine the ticket” is reproducible.

Upload only data you are authorized to share with the chosen AI tool. Names, email addresses, account numbers, free-text notes, precise locations, and rare combinations can identify people even when direct identifiers are removed. Use approved environments, minimize columns, aggregate when detail is unnecessary, and follow retention requirements.

Do not send ten columns when the question needs four. Data minimization improves both privacy and analytical clarity. Keep the mapping between identifiers and real people outside the analysis environment unless it is genuinely required.

Every transformation should leave a trace:

| Step | Rule | Rows affected | Reason | | --- | --- | ---: | --- | | 1 | Parse completed_at as ISO-8601 UTC | 8,420 | Standardize time | | 2 | Quarantine invalid dates | 17 | Cannot place in period | | 3 | Map approved region aliases | 63 | Consistent grouping | | 4 | Retain latest record per ticket | 104 | Source emits updates |

Add before-and-after row counts and a checksum or saved version when possible. Someone else should be able to reconstruct the analysis-ready table from the raw input and log.