How to clean an applicant tracking system export

From the field · Recruiting operations analyst

Clean candidate history before measuring time-to-hire

A candidate applies twice with different email casing and later changes phone number. Stage labels include Phone Screen, Recruiter Call, and Initial Screen. One requisition closes and reopens under the same title.

Columns used in the example

candidate_id · application_id · requisition_id · email · phone · stage · stage_timestamp · application_status

Follow it with your own file

1. Preserve application rows and match candidate identities using reviewed contact evidence.
2. Map synonymous stage labels while retaining raw labels.
3. Order events and flag impossible or duplicated transitions.
4. Calculate time-to-hire per hired application using approved start and end events.

Check: Candidate, application, and hired counts must each reconcile at their own grain. Review every identity merge, protect personal fields, and disclose applications excluded from duration metrics.

Where to look for exceptions

• Candidate and application counts are identical despite repeat applicants.
• The same recruiting stage has several labels.
• Time-to-hire becomes negative or includes reopened requisitions incorrectly.

The decision hiding in the data

One candidate can apply to several jobs and move through several stage rows. Counting rows as candidates inflates volume, while deduplicating by email can merge shared addresses or miss changed contact details.

Build the result step by step

1. Separate entities: Identify candidate, application, requisition, and stage-event keys and their relationships.
2. Resolve duplicate candidates: Use controlled identity evidence and retain a crosswalk rather than deleting rows.
3. make consistent stage history: Map labels to an approved funnel while preserving timestamps and skipped stages.
4. Calculate eligible metrics: Define start, end, withdrawals, holds, and reopened roles before measuring duration or conversion.

Before touching the file

• Identify candidate, application, requisition, and stage-event keys and their relationships.
• Use controlled identity evidence and retain a crosswalk rather than deleting rows.
• Map labels to an approved funnel while preserving timestamps and skipped stages.
• Define start, end, withdrawals, holds, and reopened roles before measuring duration or conversion.

Choose the approach that matches the evidence and repetition

Recruiting data contains several valid grains

Candidate, application, requisition, interview, and stage-event measures answer different questions. A useful recruiting report names the grain in every metric instead of removing repeated rows until the table looks unique.

Where this usually goes wrong

Do not merge candidates solely because names match. Shared names and recycled contact information create real privacy and decision risks; uncertain pairs belong in review.

Before the result leaves your desk

• Entity keys identified
• Identity merges reviewed
• Stage map approved
• Durations use valid events
• Applicant PII protected

Doing these steps in Prepyr

Prepyr accepts CSV files only. Export the worksheet or table you need as a UTF-8 CSV first; workbook formulas, formatting, macros, charts, and other sheets will not be included. Describe the result you need: Clean an ATS export for recruiting funnel and time-to-hire analysis. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.

Prepyr steps used here: Sensitive Column protection · Data profiling · Verified analysis · Report generation

Questions readers usually ask next

Should repeat applications be removed?

No. They are legitimate application records. Resolve the candidate identity but preserve each application and requisition relationship.

How is time-to-hire usually defined?

It commonly measures from an approved application or requisition event to accepted offer or start, but the organization must document its exact endpoints.