Overwrite the guess
Every fifteen minutes, find recent contacts where the hand-typed source disagrees with the tracking source, and overwrite the hand-typed one. The automatic value wins, always.
Case study · Attribution
Getting revenue back to the channel that earned it
The source field on every contact was typed by a person, and people guess. I replaced it with the tracking data, then built the join from closed revenue back to first touch, with the limits printed on the report rather than hidden.
15 minbetween repair runs
2fields fixed: source and lifecycle stage
365 daysof closed revenue in the standard join
3limits stated on the output, not buried
Problem
The field that decides budget was filled in by hand.
Every contact had a lead source dropdown that a rep picked from. A contact who arrived through organic search would get tagged as paid, because the form they filled in had the word "ads" in its name. Nobody was careless. The field just asks a person to remember something the system already knows.
Meanwhile the CRM was quietly recording the real source through its own tracking cookie. Two fields, one right and one guessed, and the reporting was reading the guessed one.
Two jobs, running all day
Fix the stage too
The same job corrects lifecycle stage from the deals a contact is attached to. An open deal makes them an opportunity. A won deal makes them a customer. Neither should depend on someone remembering to change a dropdown.
Preview before it writes
Both jobs have a dry run that prints every change it would make. Anything that rewrites CRM fields at scale needs a way to look before it moves.
Log every run
The jobs write a log, and a dashboard shows a green dot while they are running and a red one when they stop. A silent repair job is worse than none.
The join
Deal to contact to first touch.
With the source fields trustworthy, revenue can be traced back. For every deal won in the window:
- Use the deal's own source if it names a real marketing channel.
- Most of the time it does not, because a rep created the deal by hand. So fall back to the contact attached to it, which carries the true origin.
- If neither resolves to a marketing channel, put it in a bucket called sales and offline.
That third step is the one that matters. The easy version spreads unresolved deals across the channels and every channel looks better than it is. Giving them their own bucket keeps the numbers honest, even when the bucket is embarrassingly large.
What it proves
Print the caveats on the report.
The output says three things about itself every time it runs. It is first touch only. Long sales cycles mean this quarter's revenue traces to touches from earlier quarters. Deals with no contact attached cannot be traced at all, and are counted separately so you can see how many there are.
An attribution number without its limits gets quoted in a board deck as though it were a measurement. Attaching the caveats to the number is the difference between a report people can act on and one that quietly misleads them for a year.
Revenue figures, channel splits, account identifiers, and contact data are withheld. The repair logic and the join are the transferable parts.