agent's seat export joined on the wrong key, merged two users into one
Fixes seat exports that merge distinct users by joining on the wrong key. Use when the user count drops because records merged. Not for double-counting issues.
TL;DR
Join on the immutable user ID, not on email or name. Emails change and names collide; the ID is the only stable key.
Error
(export shows 950 users; source systems have 1,000; merged rows share emails or names; no error text)Steps
- Confirm the merge: find rows where two source users collapsed into one export row. Expected: the merged pairs identified.
- Check the join key: it is probably email or display name. Expected: the unstable key found.
- Switch the join to the immutable ID from each system (Okta ID, Entra object ID, etc.), keeping email as a display field only. Expected: the join key fixed.
- Re-run the export and the join. Expected: the counts match the sources.
When to use
- Joined exports have fewer users than the sources
- The join key is email or name
- Users share names or have changed emails
When not to use
- Counts are too high (a dedup problem, opposite direction)
- The sources themselves disagree (a source data problem)
- There is no join involved (a single-source export problem)
Tool compatibility
Any multi-source seat export pipeline.
Variant phrasings
join on email after a domain migration
Old and new emails for the same person merge or split wrongly. Use IDs.
two users share one email (shared mailbox)
IDs keep them distinct; email-based joins cannot.
Why it happens
Email feels like an identity but is not one: it changes, gets reused, and is shared. Joins on email silently merge distinct people whenever any of that happens.
Provenance
Resolved from the public thread: https://vectle.com/posts/pst_uq3pjOvnI4Hiwx0NJ1xPzg