Reconciling three systems with no shared customer identifier

Context

Supporter data sat in three unconnected systems. Ticketing held subscriptions, single match tickets and parking. The cashless catering platform held on-site spend. The CRM and email platform held contact records and campaign engagement. Each system issued its own customer identifier and none of them recognised the others, so the same supporter existed three times over with no way to connect them. Commercial, communications, matchday operations and finance all reported off these sources, and each department was reading a different version of the same season.

Constraint

There was no shared customer identifier and no realistic prospect of retrofitting one across three vendor platforms mid-season. Matching had to be inferred from partial and inconsistent attributes, and every matching rule carried two-sided risk: merge two distinct supporters and the numbers understate the base, split one supporter into three and every per-customer metric collapses. Attribute quality made this worse. Team manager allocations and fan shop walk-ups had been entered against a placeholder date of birth, which pushed thousands of records into the wrong age band, and between 24% and 28% of single-ticket buyers carried no recorded gender at all.

Approach

1. 01Audited each source independently and quantified where and by how much the three disagreed, rather than assuming one system was correct.
2. 02Mapped the attributes each system actually captured reliably, and separated those from fields that existed but were populated with placeholder or default values.
3. 03Built matching rules against the reliable attributes and tested them on a manually verified sample before applying them at scale.
4. 04Set a confidence threshold for automatic matching and routed everything below it to a manual review queue rather than forcing a decision.
5. 05Rebuilt the reporting layer on the reconciled dataset and re-derived the season figures every department was already using.

Outcome

• The three systems were brought into agreement, and for the first time the club could follow a single supporter across the full commercial journey: what they bought, what they spent at the ground, and whether they came back.
• That opened three things that had not been possible before. Season ticket growth could be split into paid and comped, which changed what the headline growth number actually meant. Yield could be measured per stand rather than blended across the stadium, which located where the revenue was and was not coming from. And renewal behaviour could be tied to individual supporters rather than inferred from net counts, which is the precondition for any renewal-likelihood model.

Reflection

The reconciliation was worth doing but it treats the symptom. The durable fix is identifier governance at the point of capture: one customer ID issued once and enforced across ticketing, cashless and CRM, so the linkage never has to be inferred. If I were starting again I would spend the first month on the capture layer and the data dictionary rather than on the matching logic, because every hour of matching work is an hour spent compensating for a decision made upstream.

Stack

• Roboticket
• Weezevent
• ArenaMetrix
• Brevo
• SQL
• Power BI
• Excel