Clean Up Badge-Scan Exports
Turns a messy badge-scan or lead-retrieval export into a clean, deduped, CRM-ready list, with every merged or removed row explained and risky contacts flagged for review.
- Leads & follow-up
- easy
- CSV
- Excel
- Google Sheets
- HubSpot
- Salesforce
Example prompts
Once the skill is added, start a chat with one of these and attach your file if you have one.
Here's the badge-scan CSV from our booth at the show. Clean it up, dedupe it and tell me what you removed.
Merge these three lead exports (two scanner apps and a sign-up sheet) into one list without duplicates.
Flag anyone in this booth list who's a competitor, a student or another exhibitor, and anyone in the EU who didn't opt in.
What this skill does
Every show ends with an export that has shouting caps, full names in one column, three spellings of the same company and the same person scanned twice by two reps. This skill fixes casing, splits names, normalizes titles, countries and company names, adds seniority and department, and merges duplicate badge scans while keeping every rep's notes.
It doesn't silently delete anything. Competitors, other exhibitors, students, your own staff, personal emails and EU or UK contacts without opt-in get review flags, and a change log lists every merge and removal with the reason, so you can defend the numbers and import with confidence.
When to use it
- You just got the lead retrieval export from a trade show or conference
- You need to merge scans from several scanner apps or a paper sign-up sheet
- Sales asked for the booth leads and the file isn't fit to send
- You're about to import event leads into your CRM and want to avoid duplicates
- You need to find EU or UK contacts who didn't opt in before adding anyone to marketing lists
How it works
- 1IntakeReads your event context file if you have one, then asks once for your company domain, competitor list and target CRM.
- 2Map columnsFinds the real header row in each export and maps its columns to one standard lead layout, showing you any guesses.
- 3Clean fieldsFixes names, emails, companies, titles, countries, phones and scan times, and adds seniority and department.
- 4DedupeMerges exact duplicates by email, keeps the earliest scan and all notes, and flags likely duplicates instead of guessing.
- 5Flag and check consentAdds review flags for competitors, exhibitors, students, staff, personal emails and EU or UK contacts without opt-in.
- 6ValidateReconciles rows in against rows out and hands over the clean file, a change log and a short summary.
What you'll need
- Badge-scan or lead-retrieval export (CSV, XLSX or pasted table), one or several
- Your company's email domain
- Optional: competitor and partner domains
- Optional: target CRM (HubSpot, Salesforce or other) to shape the import columns
What you get
A clean lead file with standard columns (name, email, company, domain, title, seniority, department, country, scan time, scan count, reps, notes, qualifying answers, consent status and review flags), a change log with every merge, removal and flag and the reason for it, and a summary that shows rows in, rows out and the arithmetic in between. Optionally, a second version with columns renamed for a Salesforce or HubSpot import.
Tips
- Give it your own email domain so staff test scans get flagged instead of landing in the sales list.
- Add a competitor domain list once in an event-context.md file and every lead skill will reuse it.
- Keep the flags column when you import. It's how sales knows a record needs a second look.
- Run it the same day the export lands. Clean data is what makes 24 to 48 hour follow-up possible.
- Hand the clean file straight to the event lead scoring skill to get A, B and C tiers.
What's in the download
badge-scan-cleanup/SKILL.md10.4 KBbadge-scan-cleanup/assets/sample-badge-scans.csv5.2 KBbadge-scan-cleanup/references/cleaning-rules.md4.2 KBbadge-scan-cleanup/references/crm-columns.md1.9 KBbadge-scan-cleanup/references/shared-terms.md6.6 KBbadge-scan-cleanup/scripts/clean_scans.py19.1 KB
Read the SKILL.md
---
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).
FAQ
How do I clean badge scan data before importing to Salesforce?
Does the skill delete leads it thinks are junk?
How does it handle duplicate badge scans?
What does it do with EU and UK contacts?
Do I need a paid tool or a CRM connector to use it?
More leads & follow-up skills
advanced
Map Event Data Into Your CRM
Designs the event campaign and campaign member statuses, maps registration and lead exports to CRM fields, matches existing records and builds import-ready files with a checklist.
intermediate
Score and Route Event Leads
Sorts event leads into A, B and C tiers from booth notes, qualifying answers and ICP fit, then gives each lead an owner, a next step and a due date that sales can act on.
easy
Turn Booth Notes Into Lead Records
Turns reps' Slack messages, voice memos and business card photos from the show floor into structured lead notes with intent level and next step, matched to your badge-scan list.
easy
Win Back Registrants Who Didn't Show
Segments event and webinar no-shows by engagement and account value, then writes recovery emails with the replay, a recap, a meeting offer or a re-invite, timed for the first 24 to 48 hours.
easy
Write Tiered Event Follow-Up Emails
Drafts post-event follow-up emails by lead tier, with a personalized first line from each lead's booth notes and send dates timed for the 24 to 48 hour window.