---
name: badge-scan-cleanup
description: Cleans, standardizes and dedupes trade show and conference badge-scan or lead-retrieval exports (CSV, XLSX or pasted rows). Fixes name casing, splits full names, normalizes titles, countries and company names, merges duplicate badge scans, flags students, vendors, competitors, personal emails and EU/UK contacts without opt-in, and returns a CRM-ready file plus a change log. Use when someone has badge scans, booth leads, an event lead list or an organizer CSV to clean before a CRM import.
license: MIT
metadata:
  author: eventmarketingsoftware.com
  version: "1.0"
  category: leads-and-follow-up
  source: https://eventmarketingsoftware.com/skills/badge-scan-cleanup
---

# Badge scan cleanup

Turn a messy badge-scan or lead-retrieval export into one clean, deduped, CRM-ready lead list. Nothing gets dropped silently: every merged or removed row is explained in a change log, and anything uncertain gets a review flag instead of a delete.

This is the first step of the event lead workflow. Its output columns are the input for event-lead-scoring, event-follow-up-sequences and crm-event-import, so keep the column names in [references/shared-terms.md](references/shared-terms.md) exactly.

## Intake

1. If the user has an `event-context.md` (or `event-program-context.md`) file, read it first. Take competitor domains, partner domains, your own company domain, target CRM and booth qualifying questions from it.
2. Then ask only for what's still missing, in one message:
   - The file(s): CSV, XLSX or pasted rows. Several exports are fine (two scanner apps plus a sign-up sheet).
   - Event name and dates.
   - Your company's email domain(s), so staff scans can be flagged `internal`.
   - Competitor domains or names (optional) and partner domains to keep (optional).
   - Target CRM, if they want the columns shaped for it (optional; default is the standard columns).
3. If the user says "just do it", proceed with defaults and list every assumption at the top of the output.

## Connectors (optional)

- If a Google Sheets connector is available and the data is in a sheet, read it from there and offer to write the clean list to a new tab. Never overwrite the source tab.
- If a HubSpot or Salesforce connector is available, you may read contacts by email or domain to set `existing_customer` or flag CRM matches. Don't write anything to the CRM in this skill; that's crm-event-import's job.
- Otherwise work from the pasted table or uploaded file and return a downloadable CSV (or a Markdown table if the list is under about 30 rows or files can't be created).

## Method

Work through these steps in order. Keep a running tally for the change log.

### 1. Read and stack the inputs
- Detect the header row (exports often have a title line or blank rows above it). Drop fully blank rows and repeated header rows, and log them.
- Map each file's columns to the standard columns. Common variants: `First`, `FName`, `Given Name` > first_name; `Full Name`, `Name`, `Attendee` > split later; `Organization`, `Company Name`, `Account` > company; `Job Title`, `Position` > title; `Email Address`, `E-mail` > email; `Lead Notes`, `Comments` > notes; `Rep`, `Captured By`, `Scanned By`, `Staff` > scanned_by; `Date`, `Timestamp`, `Scan Time` > scan_time. Custom question columns become `qualifier_<short_name>`.
- Add `source` per file (Booth scan, Session scan, Sign-up sheet and so on) and `event_name`.
- Show the column mapping table to the user before transforming if any mapping is a guess.

### 2. Clean each field
- **Names**: trim, collapse spaces, proper case (`mcdonald` > `McDonald`, `o'neil` > `O'Neil`, keep particles like `van`, `de` lowercase when mid-name). Split a full name on the last space unless a comma format (`Last, First`) is used. Drop honorifics (Dr, Mr, Ms) and post-nominals (PhD, MBA); don't guess gender.
- **Email**: lowercase, trim, remove `mailto:` and trailing punctuation. Flag `invalid_email` if it lacks one `@` and a dot in the domain. Flag `missing_email` if empty.
- **Domain**: from the email. If the email is personal (see [references/cleaning-rules.md](references/cleaning-rules.md)), flag `personal_email` and leave domain blank unless a company website is given.
- **Company**: trim, fix ALL CAPS and all lowercase, remove legal suffixes (Inc, Inc., LLC, Ltd, GmbH, S.A., Corp, Co.) for matching, and use the most common spelling within the file for the same domain. Keep acronyms in caps (IBM-style names stay as given).
- **Title**: tidy casing and expand common abbreviations (`Sr` > `Senior`, `Mgr` > `Manager`, `Dir` > `Director`, `VP Mktg` > `VP Marketing`). Then set `seniority` and `department` with the rules in the reference file.
- **Country**: full English name (`US`, `USA`, `United States of America` > `United States`; `UK`, `GB`, `England` > `United Kingdom`; `Deutschland`, `DE` > `Germany`). If missing, infer only from a country-code email domain (`.de`, `.fr`) and say so in the log; never infer from phone format alone.
- **Phone**: keep digits and a leading `+`. Don't invent country codes.
- **Scan time**: convert to ISO 8601. If the time zone is unknown, keep local time and note it once.

### 3. Dedupe
- **Exact duplicates**: same email (after cleaning). Merge into one record.
- **Likely duplicates**: same first name, last name and company domain with different or missing email. Merge only when one row has no email; otherwise flag both `possible_duplicate` and don't merge.
- **Merge rules**: keep the earliest `scan_time`; keep the longest non-empty value for each field; join all `scanned_by` names with `;`; join different notes with ` / ` in time order; for qualifier columns, keep the most specific answer and log conflicts; set `scan_count` to the number of rows merged.
- Never merge across different emails just because the names match.

### 4. Flag (don't delete)
Apply the review flags from [references/shared-terms.md](references/shared-terms.md): competitor, exhibitor_or_vendor, student, internal, personal_email, eu_uk_no_optin, missing_email, invalid_email, possible_duplicate, test_record. Detection rules are in [references/cleaning-rules.md](references/cleaning-rules.md).

Only remove rows that are clearly junk (blank, repeated header, `test@test.com`-style entries) and log each one. Everything else stays with flags.

### 5. Consent
- Set `consent_status` from the export: an explicit opt-in field or checkbox > `opted_in`; explicit no > `not_opted_in`; nothing > `unknown`.
- Flag `eu_uk_no_optin` for any EU, EEA or UK country where consent_status isn't `opted_in`.
- A badge scan by itself doesn't mean marketing consent. Say this in the summary if many rows are `unknown`.

### 6. Shape for the CRM (optional)
If the user named a CRM, add a second file or tab with columns renamed to that CRM's import fields using [references/crm-columns.md](references/crm-columns.md). Keep the standard file too.

## Script (optional)

If you can run Python, `scripts/clean_scans.py` does steps 2 to 5 deterministically with the standard library:

```
python scripts/clean_scans.py input.csv [more.csv ...] --out clean.csv --log changes.csv \
  --own-domains yourco.com --competitors rival.com,other.com --personal-domains extra-webmail.com
```

It prints a summary and writes the clean file plus a row-level change log. Check its column mapping output, then review flags by hand. If you can't run code, do the same steps in the conversation; for files over about 500 rows, work in batches of 200 and keep a running dedupe key list (lowercased email) across batches.

To try the skill, use `assets/sample-badge-scans.csv` with `--own-domains ourco.example.com --competitors rivalsoft.example.com --personal-domains personal.example.org` (in the sample, `personal.example.org` stands in for webmail providers).

## Output

Return three things, in this order.

**1. Summary** (short):

```
Rows in: 112 (3 files)   Clean records out: 87   Merged duplicates: 19   Removed junk: 6
Flags: competitor 2, exhibitor_or_vendor 4, student 3, internal 5, personal_email 9,
       eu_uk_no_optin 11, missing_email 1, possible_duplicate 4
Assumptions: scan times kept in local show time; country inferred from .de domain for 2 rows
```

**2. Clean lead file** with the standard columns, in this order: record_id, first_name, last_name, email, company, domain, title, seniority, department, phone, country, state_region, event_name, source, scan_time, scan_count, scanned_by, notes, qualifier_* columns, consent_status, flags.

**3. Change log** with columns: `row_ref` (file and original row number), `action` (cleaned, merged, flagged, removed), `field`, `before`, `after`, `reason`, `record_id`. For large files, log every merge, removal and flag, and summarize routine casing fixes as counts.

Then suggest the next step: score and route the list (event-lead-scoring), or import it (crm-event-import).

## Validation checklist

Run before handing over, and show the result:

- [ ] Rows in = clean records out + rows merged away + rows removed. Show the arithmetic.
- [ ] Every removed or merged row appears in the change log with a reason.
- [ ] No two clean records share an email.
- [ ] Every record has an email or a `missing_email` flag.
- [ ] Every EU/UK record without `opted_in` has `eu_uk_no_optin`.
- [ ] Notes from merged rows are all preserved.
- [ ] No column was renamed away from the standard set.

## Approval gates

- Don't write to a CRM, a shared sheet or Slack from this skill without showing a preview and getting an explicit yes. Default to returning files.
- Never overwrite the user's original export.

## Privacy and consent

- Badge-scan data is personal data. Keep it in the user's own tools; don't paste it into other services or look people up on the web.
- Flag EU, EEA and UK contacts without opt-in (GDPR, and PECR in the UK for email marketing) so they're kept out of marketing lists until consent or another lawful basis is confirmed. One-to-one sales follow-up about the conversation is a different question; tell the user to check with their privacy lead rather than deciding for them.
- Don't enrich records with data from outside the user's files and connected tools.

## Pushback

- "Just delete the junk": remove only clear junk; keep flagged rows and explain that a flag is cheaper to undo than a delete.
- "Merge anyone with the same name": decline for different emails; mark `possible_duplicate` instead.

## Related skills

booth-notes-to-crm (attach rep notes captured outside the scanner), event-lead-scoring (lead tier A/B/C and routing), crm-event-import (campaign member status and import files).
