How to build lead import and validation

What matters here

• Validate before writing anything. A partial import is far harder to clean up than a rejected one.
• Quarantine bad rows with the reason and the original values. A count of failures is not something anybody can act on.
• Dedupe against the CRM, not just within the file. The duplicate that matters is the one already sitting in Salesforce.
• Keep the source file and the row number on every lead. Six weeks later somebody will ask where a record came from.
• Normalise on the way in: casing, country codes, phone formats. Every downstream rule depends on fields being shaped consistently.

Who this is for

You run marketing operations or revenue operations. Lists arrive from events, webinars, agencies and partners, in whatever shape the sender felt like, and each one becomes somebody's afternoon. What you want is an import that validates, explains its rejections, and does not create duplicates.

How it works in practice

Everything that sits between a spreadsheet landing and leads appearing in the CRM.

1. 1

   The file lands and the columns are mapped

   Against a saved mapping per source, because the same agency sends the same shape every time and nobody should re-map it.

2. 2

   Every row is validated before anything is written

   Required fields, email syntax, country codes and phone shapes. Nothing reaches the CRM until the whole file has been checked.

3. 3

   Failures are quarantined with a reason

   The row, the reason and the original values, in a place somebody can correct and resubmit.

4. 4

   Valid rows are deduped against the CRM

   Not just within the file. The duplicate that costs you is the one already in Salesforce with an owner.

5. 5

   Fields are normalised on the way in

   Casing, country to ISO codes, phone to E.164, so downstream routing and matching have something consistent to work with.

6. 6

   Every lead keeps its source

   File name, row number and import run, so the origin of a record is answerable months later.

Paste these into Claude Code or Codex with the Tray Headless plugin installed. Each stage runs on its own. The systems named in them are the worked example rather than a requirement, and every prompt says so.

Once per project, run /tray-workflows:set-workspace to pick the workspace these build in. Point it at a sandbox first.

1. 1

   Set up and confirm the target schema

   Import failures are usually schema surprises, so start there.

   Headless skills build-workflow

   Copy

   Use build-workflow. The systems in play are Salesforce, or whatever we
   run in those seats.
   
   Give me the real Lead fields with their types, which are required, which
   are picklists and what values those picklists accept. Picklists are where
   imports fail silently, so I want the accepted values before we map
   anything.

2. 2

   Map columns per source, and save the mapping

   Because the same sender sends the same shape every time.

   Headless skills build-workflow

   Copy

   Use build-workflow. The trigger is a file arriving in a watched folder.
   
   Build column mapping that is saved per source, so an agency that sends
   the same layout monthly is mapped once. Match on header name, tolerate
   case and whitespace differences, and flag any column in the file that is
   not in the mapping instead of ignoring it silently.
   
   If a saved mapping does not exist for a source, stop and ask me to map it.
   Do not guess at column meaning.

3. 3

   Validate the whole file before writing anything

   A partial import is much harder to clean up than a rejected one.

   Copy

   Validate every row before a single record is created:
   
     Required fields present: email, last name, company
     Email syntax valid, and reject obvious role addresses if I ask for it
     Country resolvable to an ISO 3166 code
     Phone parseable to E.164, or left empty instead of half-formatted
     Picklist values matching what the CRM actually accepts
   
   Run this across the whole file first and report the summary before
   writing. If more than a threshold I set fails, stop entirely and tell me,
   because that usually means the mapping is wrong, not the data.

   The threshold matters. A file where 40% of rows fail is not a data quality problem, it is a column mapped to the wrong field.

4. 4

   Quarantine what fails, do not drop it

   The difference between a report and something somebody can act on.

   Copy

   Failed rows go to a quarantine, never to a log line.
   
   Keep the original row exactly as it arrived, the row number, every reason
   it failed, and the source file. Make it correctable in place and
   resubmittable without re-running the whole file.
   
   Then report quarantine reasons grouped by frequency. One row with a bad
   phone number is a typo. Two hundred rows with bad phone numbers is a
   column in the wrong format, and that is a mapping fix rather than two
   hundred corrections.

5. 5

   Dedupe against the CRM, then normalise and write

   The duplicate that costs you is the one already in the CRM.

   Copy

   Before writing, dedupe in this order:
   
     1. Within the file itself, on lower(email)
     2. Against existing Leads on lower(email)
     3. Against existing Contacts on lower(email)
     4. A softer pass on email plus company for near matches
   
   An existing record is updated instead of duplicated, and never has its
   owner overwritten by an import. Stamp Source_File__c, Source_Row__c and
   Import_Run__c on everything written.
   
   Normalise as you write: title case names, ISO country codes, E.164
   phones. Every downstream routing and matching rule depends on these
   being shaped consistently.

6. 6

   Check it end to end, then hand it to the team that runs imports

   So a new list format is not an engineering request.

   Copy

   Run the per-step schema checks and the whole-workflow audit before this
   touches production.
   
   Then open the same workflow in Tray Build so marketing operations can add
   a source mapping or change a validation rule in the visual canvas. New
   list formats arrive constantly, and each one should not be a ticket.

What it connects to

Import reaches into wherever files arrive and writes into the system of record.

Named systems are the ones most teams run, not the only ones that work. Each is an authentication in your Tray workspace, referenced by name, so the workflow never holds a credential. Where we have a connector page, the name links to it.

Questions people ask

Further reading

Background on the same subject, for the case rather than the build.

• How to automate lead list uploads
• Automating lead list upload for faster lead follow-up
• Open Source RevOps: 1st steps of the lead processing pipeline
• Intro to the lead processing pipeline on the Tray platform

Related guides