When Two Tools Disagree, Show Both Numbers
Analytics and Search Console never agree, and picking one quietly throws information away. How a marketing report stopped producing arguments.
A marketing agency I work with reported on client performance by pulling exports from half a dozen tools and reconciling them in spreadsheets. The figures disagreed depending on which tool they came from, and nobody could say why. So every report carried a small risk of being wrong, and every month somebody picked a number and moved on.
Most of the time neither number was wrong. Search Console counts clicks from Google's results. Analytics counts the sessions its script managed to record, which misses anyone blocking it, anyone who left before it loaded and anyone who declined the cookie banner. They measure different things, and the gap between them tells you something. Averaging them, or quietly picking one, throws that away.
Do the arithmetic once, in the warehouse
The first fix was structural. Everything lands in BigQuery, and every figure the dashboard shows comes from a mart, a modelled table built in the warehouse. The page never does arithmetic of its own.
That sounds like a small rule. It's what had been causing half the disagreements. When each chart computed its own totals from raw exports, two charts on the same page could disagree about the same month because one filtered a date range differently from the other. Once the definition lives in one place, a difference on the page means the sources differ, not that two bits of front end code rounded differently.
Put the sources next to each other
Where two sources genuinely count different things, the dashboard shows both side by side and says plainly what each one is. Clicks from search. Sessions recorded by analytics. They are never averaged into a third number that nobody measured.
The people reading these reports are marketers. They know analytics undercounts. What they don't want is one confident figure that later turns out to be the lower of two.
Server logs are a third opinion
The agency also wanted to show clients how often AI assistants were fetching their sites. None of the usual tools see that, because those fetchers don't run JavaScript. The web server's own access log does, so it goes into the warehouse as a third source. I wrote about what the log can and can't tell you separately. It sits beside the others as well, it doesn't get blended in.
Say where the history stops
Backfills are where reports lie without meaning to. Every API only lets you go so far back. I got twenty months of social history and three years of analytics, and not a day more. The charts start each source where its data actually starts, and the limits are written down, so nobody reads the first month of a series as sudden growth.
Reporting that used to take a day in spreadsheets now comes off one page, and the figures reconcile against the source systems. The numbers still differ from each other. They just differ for reasons everyone can see, which turns out to be most of what people wanted.
If your reporting has the same problem, this is the kind of platform I build.