Three systems, three member lists, all wrong in different places

Three lists, three counts, and a meeting about which one is right. Skip the meeting. Export each list on the same day within the same hour, reconcile them on a key that exists in all three, and sort every mismatch into 4 classes. The class tells you the cause; the totals tell you nothing except that a problem exists.

This is a diagnosable defect, not evidence that the platform is wrong. Almost every club running a booking tool, a payment platform and a spreadsheet has this, including clubs whose software is working exactly as designed, because the 3 systems were never told which of them decides what a member is.

The check: same day, same hour, one key

1. Export all 3 lists within the same hour, with every status field included. A club with 400 members and 30 joins a month changes about 1 record per working day, so exports taken a week apart differ by real events before any defect is involved.
2. Choose the join key. In order of preference: a shared member id, then a normalised mobile number, then email, then name plus date of birth. Write down which one you used, because the answer changes what the reconciliation can prove.
3. Normalise before joining. Lowercase emails, strip spaces and country-code variants from phone numbers, trim trailing whitespace. A meaningful share of apparent mismatches disappear at this step alone.
4. Join, then bucket every row into: present in all 3 and agreeing, present in all 3 but disagreeing on status, present in some but not others, or duplicated within a single list.
5. Count the buckets and record them before forming a theory. In a large export, a theory formed first will find evidence for itself.
6. Take 10 rows from each bucket and follow them back to a person. Every reconciliation exercise that skipped this step has ended in an argument about the spreadsheet rather than about the members.

Classify the mismatches, do not total them

The causes, ordered by how cheaply you can rule each one out

The word active is the argument, not the count

Most reconciliations end here. Nothing is broken: 3 systems are each answering a different question correctly, and only the label on the answer is shared.

Fix this by writing one status vocabulary and mapping each system's native states onto it, rather than by trying to make 3 products agree. The vocabulary needs a defensible boundary for lapsing — the question answered in what counts as a lapsed member — and a rule for freezes, which behave differently in billing and booking by design: what a membership freeze does to the billing schedule.

Three systems disagreeing is not three bugs. It is usually one missing decision about who owns the word member, repeated three times.

The decision: a reconciliation job, or a declared source of truth

1. Mismatches dominated by status disagreement? Write the status vocabulary. This is a definition exercise and needs no new software.
2. Dominated by direction — joins arriving, leaves not? Build a scheduled reconciliation job that reports differences, and fix the sync path it exposes.
3. Dominated by duplicates? Do the identity work first: merge, then add a uniqueness rule at the point of creation. Deduplicating without closing the door means doing it again next term.
4. Every list wrong in both directions, and nobody can name the owner? Declare a system of record per object and enforce the write path. This is the only case that justifies structural change.
5. The paper register is the only list matching reality? Then the register is your real system, and the software is a reporting layer with a bad reputation — the situation described in whatever the coach writes on the clipboard.

What declaring a source of truth actually commits you to

The declaration is cheap and the enforcement is not. A system of record is the system that owns the write path: new members are created there and nowhere else, status changes originate there, and every other system receives rather than decides. If a member can still be created in the booking tool by a coach on a Saturday, the declaration is decorative.

• Choose per object, not per product. The billing platform is usually the right record for money and mandates; the club system is usually right for people, squads and consents. One winner for everything is how you end up with a payment tool holding safeguarding data.
• Name the write path for each object and remove the others, including the friendly workarounds. This is a process change, and it is the part that gets skipped.
• Keep the reconciliation job running afterwards. Declaring a record does not stop drift; it only makes drift attributable, which is what turns a quarterly argument into a weekly report.
• The same question recurs wherever a second system also holds an authoritative copy — device data is the clearest case, where the phone's health store and a vendor cloud can both claim to be right: phone health store or vendor cloud as source of truth.

One thing this exercise is not: a reason to migrate. Disagreeing lists follow you to a new platform, and they arrive worse, because import turns a duplicate into a permanent record. Clean first, then decide — the identity order in preparing a roster and member file that will import, and the threshold in most clubs should not build custom software. If a move is genuinely happening mid-term, the sequencing constraint is money already collected against sessions not yet delivered: a three-site academy changing platform in week 6.

The reconciliation job itself — 3 scheduled exports, a normalising step, a differences table and an alert when a bucket grows — is a few days of work rather than a project, and it is exactly the kind of thing we build as internal tools and operations work. The rest of this silo sits under build, buy, extend or migrate, inside our sports, fitness and athletics practice.

• data reconciliation
• source of truth
• member records
• club operations