One number, two histories

Situation

This problem arrives wearing several different costumes, and it took me a few rounds to notice they were the same problem.

Sometimes it is a merger: two companies, two warehouses, two reporting stacks. Sometimes there is no merger at all, marketing and finance simply built separate platforms over several years because neither could wait for the other. Sometimes it is a migration, where the new warehouse was supposed to be a technical lift-and-shift and turned out to carry two incompatible definitional histories across with it. Occasionally it is an acquisition of a product rather than a company, which is the same thing at smaller scale with less political cover.

The surface stories are different. The invariant is not: two groups arrive with their own accumulated history of how numbers came to be defined, leadership wants one set of numbers, and what exists is two sets that disagree plus a third assembled by hand in spreadsheets whenever somebody needs an answer spanning both.

Everything below applies to all of these. Where the merger case differs is mainly in the politics: a merger supplies an obvious deadline and an obvious mandate, whereas departmental silos supply neither, which makes the silo version harder to start and easier to abandon.

The disagreements are rarely dramatic, and that is what makes them dangerous. Both sides report active customers. Both are correct by their own definition. One counts an account, the other counts a paying entity, and a customer with three subsidiaries is one number on the left and three on the right. Nobody is wrong. There is simply no such thing as the active customer count until somebody decides there is.

Two patterns show up almost every time. First, the divergence is older than anyone still working there, definitions were set by people who have since moved on, for reasons that were sound at the time and are now undocumented. Second, the spreadsheet layer is load-bearing. Someone in finance has been manually reconciling the two sides every month for long enough that the reconciliation is the number leadership sees. That spreadsheet is the real system of record, and no migration plan acknowledges it.

Constraint

Three constraints shape this class of problem:

• Reporting continues throughout. There is no freeze window. The business does not stop needing numbers because the data team is busy.
• The deadline, where there is one, comes from the financial calendar rather than from engineering. A first combined quarter-end is a fixed date and it arrives whether or not the platform is ready.
• The team doing the consolidation is the same team already running everything else. This work is additive. Nothing gets paused to make room for it.

The calendar binds hardest, and in an awkward way: it sets a date for the output while saying nothing about the definitional work that has to precede it. That asymmetry is what pushes teams toward lift-and-shift.

Worth separating the two political situations, because the constraint differs. With a merger, the mandate is handed to you and the deadline is real; the difficulty is speed. With departmental silos there is no deadline and no mandate Each department is functioning adequately by its own lights, and consolidation is something you want and they have not asked for. That version needs a demonstrated win before anyone will fund the rest, and choosing which department to start with matters more than any architectural decision in the project.

Decision

The core call: treat definition reconciliation as the actual project, and the pipeline work as the supporting task, rather than the reverse, which is the intuitive ordering and the wrong one.

In practice that means getting finance, growth, and analysts from both sides to agree on a single definition per metric before anyone writes a model. Not a mapping document. Not a spreadsheet of “brand A calls this X, brand B calls it Y.” An actual decision, with a named owner, about what the combined organization means by the word.

Structurally, what works:

1. Inventory by usage, not by existence. Rank every metric by how often it appears in decisions people actually make. The long tail does not need reconciling on day one, and treating it as though it does is how these projects stall.
2. One named decider per metric. Not a committee. Committees produce definitions with conditional clauses, which are definitions that have not been agreed.
3. Write the decision down before the model. A short definition record (what counts, what is excluded, why, who decided, when) takes twenty minutes and settles the argument permanently. Skipping it means having the same conversation every quarter with worse recall each time.
4. Rebuild the reconciled metrics; leave the rest alone deliberately. Not everything needs to move. Explicitly deciding to leave a legacy report running until it ages out is a legitimate architectural choice, and it is cheaper than migrating something nobody will read next year.

The available evidence supports the ordering. Analyses of post-merger integration consistently attribute a large share of lost deal value to slow or ineffective IT and data integration, figures in the 30–50% range are commonly cited. The failure is rarely that the pipelines did not run. It is that the combined organization could not agree on what the pipelines should produce, so the synergy case was never provable either way.

The tradeoff I accepted

This is the section worth being specific about, because it is the one experienced readers look for.

Front-loading definition work pushes the first visible deliverable out by weeks compared to a lift-and-shift. For a meaningful set of core metrics, expect the reconciliation alone to run six to ten weeks before a single model is written, and during that period what the business sees from the data team is meetings. That is a genuinely uncomfortable position to hold, particularly when a parallel workstream elsewhere in the integration is shipping something visible every week.

I took the slower start in exchange for not having to relitigate every number afterwards. The failure mode I was avoiding is common and expensive: the migration technically completes, the dashboards render, and nobody believes the output, so the spreadsheets never actually go away. At that point you have paid the full cost of the migration and retained the full cost of the manual reconciliation it was meant to eliminate.

The honest cost of this approach: the definitional debate surfaces real organizational disagreement that was previously hidden by the two sides simply not talking to each other. Some of those arguments are not really about data and cannot be settled by a data team. Deciding whose customer count wins is occasionally a decision about whose function matters more, and it escalates. Budget for that escalation. It is not a sign the process is failing. It is the process working as intended and surfacing something that needed deciding.

Outcome

The result worth aiming for is not a faster close, though that usually follows. It is that the reconciliation spreadsheet gets deleted and stays deleted.

Signals that it worked:

• Close shortens by several days, because the manual reconciliation step is gone rather than automated.
• Disagreements about a number become disagreements about the definition, which have a written answer and a named owner, and therefore end.
• New metrics get defined before they get built, because the pattern is now established and the cost of the alternative is fresh in everyone’s memory.

The last one is the durable win. The migration is finite; the habit outlives it.

What I’d do differently

Name the decider earlier, and more bluntly. The instinct to build consensus is the right instinct for adoption and the wrong one for speed. I would start by asking sponsors to assign a single decider per contested metric, in writing, in the first fortnight, before the debates begin rather than after they stall.

Treat the spreadsheet layer as a discovery task, not a cleanup task. The manual reconciliations people maintain privately are the best available documentation of where definitions actually diverge. Finding them early compresses the inventory phase considerably. They surface late because nobody volunteers that their spreadsheet is load-bearing.

Publish the definition records to everyone, not just the working group. The people most likely to relitigate a number are the ones who were not in the room. A definition record nobody outside the project can find is a decision that will be made again.