Missing Field Audit

You scan a client dataset against an agreed list of required fields and produce a record-level audit that names every row with missing, blank, or placeholder values in those fields. You own the completeness verdict: after this audit, the client knows exactly which records are unusable, which field is the biggest offender, and what has to be fixed before the data is used downstream.

When to use

• A client has handed over a dataset (CRM export, intake spreadsheet, billing extract) and you need to confirm it is complete before migration, analysis, or import.
• A downstream process is failing or rejecting records and you suspect blank required fields are the cause.
• You are onboarding a new client and must produce a data-quality baseline before work begins.
• A recurring feed needs a periodic completeness check against an agreed schema or SLA.
• Someone asks "how clean is this data?" and needs a number plus a fix list, not an impression.

Do not use this skill when:

• The question is about accuracy, duplication, or formatting validity (wrong email format, duplicate customer IDs, impossible dates). This skill only answers "is a value present?" — route those to a validation or dedup skill.
• No one has confirmed which fields are required. Guessing required fields produces an audit the client will reject. Stop and get the list first.

Inputs

Before starting you need:

1. The dataset. File path or table name, format (CSV, XLSX, JSON, SQL table), and the sheet/tab if it is a workbook. Note the row count you expect.
2. The required-field list. Explicit column names, ideally with conditions ("vat_number required only when country is in the EU"). Source: client schema, contract, import spec, or written client confirmation.
3. A record identifier. The column that uniquely names a row (client_id, account_number, email). Needed so the output is actionable.
4. The blank definition. Confirm which values count as missing beyond true nulls: empty string, whitespace-only, NULL as text, N/A, -, 0, Unknown, TBD, XXX.
5. Scope and cutoff. Whole file or a filtered subset (active records only, created after a date). Confirm whether archived or test records are excluded.

If any input is missing, ask for it before running anything. Ask in one batch, not one question at a time. If the required-field list is unavailable and the client is unreachable, you may proceed with a provisional list derived from columns that are populated in more than 95 percent of rows — but label every deliverable "PROVISIONAL — required fields not confirmed" and list your assumptions at the top. If no unique identifier exists, use the source row number and state that in the output.

Method

1. Load the dataset and reconcile the row count. Read all rows as text, not typed values, so 0, leading zeros, and empty strings survive. If the loaded count differs from the expected count by more than zero, stop and report the difference before auditing — a truncated read produces a false clean bill of health.
2. Reconcile column names against the required list. Compare after trimming whitespace and lowercasing. If a required field does not exist as a column, do not silently skip it: record it as a structural gap (100 percent missing) and flag it separately from record-level gaps, since the fix is different.
3. Fix the blank definition in writing. Apply this default unless the client overrode it: a value is missing if it is null, empty string, whitespace-only, or a case-insensitive match to n/a, na, none, null, unknown, tbd, -, --, ?. Treat 0 and false as present unless the client explicitly says otherwise, and note that decision in the output.
4. Apply conditional rules before scanning. For each conditional requirement, evaluate the condition per row first and mark the field as not-required where the condition fails. If the condition column is itself blank, mark the field as INDETERMINATE rather than missing, and count indeterminates in their own bucket.
5. Scan row by row and record each gap as its own line. Capture: record identifier, source row number, field name, and observed raw value in quotes (so " " is distinguishable from empty). Never collapse multiple missing fields on one record into a single entry — the fix list needs field-level granularity.
6. Aggregate by field. For each required field compute the missing count and missing percentage of in-scope rows. Sort descending. If one field accounts for more than half of all gaps, call it out as the primary driver in the summary.
7. Aggregate by record. Count how many records have at least one gap. Bucket them: records missing 1 field, 2 fields, 3 or more. Records in the 3-or-more bucket are usually a systemic import failure, not individual data entry errors — inspect a sample of five and say whether they cluster by date, source, or owner.
8. Look for patterns before writing conclusions. Check whether gaps cluster by creation date range, source system, record owner, or a contiguous block of row numbers. A contiguous block almost always means a failed export or a shifted column, not real missing data. Verify before reporting it as missing.
9. Classify each field gap by severity. BLOCKER — record cannot be used downstream at all. DEGRADED — usable but reduced function. COSMETIC — no functional impact. Use the client's import spec to assign these; if no spec exists, assign by best judgment and mark the column "unconfirmed severity."
10. Write the deliverable in the output format below. Lead with the headline completeness rate, then field-level table, then the record-level gap list. Keep the full gap list in an attached CSV if it exceeds 50 rows; put the first 20 inline and reference the file.
11. State next actions, not just findings. For each field with more than 5 percent missing, name who can supply the values and whether the fix is a re-export, a manual fill, or a schema change.

Rules

• Never impute, guess, or auto-fill a missing value. Your job is to report absence, not resolve it.
• Never modify the source dataset. Work on a copy; if you must write anything, write to a new file with a clear suffix.
• Never report a percentage without the underlying counts (12.4% (312 of 2,514)). Percentages alone hide small-denominator noise.
• Never expand scope into accuracy checks. If you notice bad values (malformed emails, future birth dates), list them in a short "Observed but out of scope" note and move on.
• Never audit against an unconfirmed required-field list without labelling the output PROVISIONAL.
• Treat 0, false, and 1900-01-01 as present unless the client says otherwise; state the choice explicitly rather than leaving it implicit.
• If a required column is entirely absent from the file, report it as a structural gap and do not include it in per-record gap counts.
• Do not reproduce sensitive field values (national ID, full card numbers, health notes) in the gap list. Report the field name and record ID only, and note the redaction.
• If more than 30 percent of in-scope rows have at least one gap, stop and raise it with the client before completing the full record list — that usually indicates a wrong file or wrong required-field list, and finishing the audit wastes the effort.
• Cap the inline gap list at 20 rows regardless of dataset size; the rest goes to the attached CSV.

Output format

# Missing Field Audit — [Client Name] / [Dataset Name]
Date run: [YYYY-MM-DD]
Source file: [filename + sheet/table]
Rows in file: [N]   Rows in scope: [N]   Scope filter: [description or "none"]
Required-field list source: [contract / import spec / client email dd-mm-yyyy / PROVISIONAL]
Blank definition: null, empty string, whitespace-only, and: [list of placeholder tokens]
Zero / false treated as: [present | missing]

## Summary
Complete records: [N] of [N] ([X]%)
Records with at least one gap: [N] ([X]%)
Total field-level gaps: [N]
Primary driver: [field name] — [N] gaps ([X]% of all gaps)
Verdict: [READY / READY WITH FIXES / NOT USABLE — reason in one line]

## Structural gaps (required fields absent from the file)
| Required field | Status |
|---|---|
| [field] | Column not present in source |

## Gaps by field
| Field | Missing | % of in-scope | Severity | Indeterminate |
|---|---|---|---|---|
| [field] | [N] | [X]% | BLOCKER/DEGRADED/COSMETIC | [N] |

## Gaps by record
| Fields missing per record | Record count |
|---|---|
| 1 | [N] |
| 2 | [N] |
| 3+ | [N] |

## Record-level gap list (first 20 — full list: [filename].csv)
| Record ID | Source row | Field | Observed value | Severity |
|---|---|---|---|---|
| [id] | [n] | [field] | "" / " " / "N/A" | [severity] |

## Patterns observed
- [e.g. 184 of 212 phone gaps fall in rows 1,340–1,551, a contiguous block — likely export failure, verify before treating as real]

## Observed but out of scope
- [e.g. 47 email values present but malformed — not counted as missing]

## Next actions
| Field | Owner | Fix type | Blocking? |
|---|---|---|---|
| [field] | [name/team] | re-export / manual fill / schema change | yes/no |

## Assumptions
- [any decision you made without client confirmation]

Failure modes

1. Silent truncation. The reader caps at 1,000 rows or stops at the first blank line, and you audit a fraction of the file while reporting a clean result. Check: compare the loaded row count to the file's own row count (line count, COUNT(*), or the client's stated figure) and print both in the output header before auditing anything.
2. Placeholder blindness. A field is 100 percent populated but with N/A, TBD, or . and you report it as complete. Check: for every required field reporting zero or near-zero gaps, list its five most frequent values. If a non-meaningful token appears in the top five, add it to the blank definition and re-run.
3. Wrong required-field list. You audit against your own assumption instead of the client's spec, so the report flags fields nobody needs and misses one that blocks the import. Check: before running, paste the required-field list back to the client and get a yes. If you cannot, stamp PROVISIONAL on every page and list the fields you assumed.
4. Column shift mistaken for missing data. A malformed CSV shifts columns from a certain row onward, so a whole block reads as blank. Check: if gaps cluster in contiguous row ranges, open five raw rows from inside the block and five from outside and compare column alignment before reporting anything as missing.

License

MIT