Skip to content
ENNO
← Back to blog
·4 min read·

When Two Tools Disagree, Show Both Numbers

Analytics and Search Console never agree, and the dashboard I built for an agency shows both instead of picking one. The first time someone checked it against the ad platform, the gap turned out to be partly my own join bug.

When Two Tools Disagree, Show Both Numbers

A marketing agency I work with used to report 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, nobody could say why, 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, so it misses anyone blocking it, anyone who left before it loaded and anyone who said no to the cookie banner. They measure different things, and the gap between them is information. Averaging them or quietly picking one throws it away.

All the arithmetic lives in the warehouse

Everything lands in BigQuery now, in three layers per client: raw exports as they arrive, staging tables that clean them up, and marts, the modelled tables the dashboard reads. The page never does arithmetic of its own.

I didn't expect that rule to matter as much as it did. Half the old disagreements came from the charts themselves. When each chart computed its own totals from raw exports, two charts on one page could disagree about the same month because one of them filtered dates differently. With the definition in one place, a difference on the page means the sources differ, not that two bits of front end code rounded differently.

There's one rule in the marts I'd pass on to anyone doing this. Cost per call for a group of campaigns is total cost over total calls, never the average of each campaign's cost per call. The second one looks reasonable and gives a different answer every time.

sql
-- per bucket: a ratio of sums SELECT channel, SUM(cost) / NULLIF(SUM(calls), 0) AS cost_per_call FROM paid_daily -- simplified GROUP BY channel -- not AVG(cost / calls), which weights a campaign with 2 calls -- the same as one with 200

When the dashboard was the one that was wrong

Showing both numbers only works if you go and check the gap when someone asks about it. The first proper test came when someone at the agency compared the dashboard against the ad platform for one campaign. The ad platform said 55 conversions and the dashboard said 31.

Reconciling one campaign. The ad platform's conversions column showed 62.7, of which 7.7 were phone calls imported back from analytics, so the comparable figure was 55. The dashboard showed 31. The missing 24 were calls placed from the ad's call button, tagged by the call tracker under a campaign name that differed slightly from the real one, which the dashboard's exact-name join dropped. 31 plus 24 is 55.
Two of the three gaps were reading the source differently. The third was my join.

Two things were going on. The ad platform's own column was 62.7, and 7.7 of that was phone calls imported back from analytics, so they were counted twice there. The person checking already knew that and had taken them out, which is how they got 55.

The other 24 were mine. Calls placed straight from the ad's call button got tagged by the call tracker with a campaign name that wasn't quite the real one, and the mart joined on the exact name, so those calls never reached the campaign at all. That was a real bug, and nobody would have found it if the dashboard had shown one tidy number instead of putting it next to something checkable.

Both numbers, side by side

Where two sources really count different things, the dashboard shows both next to each other and says plainly what each one is: clicks from search, sessions recorded by analytics. They never get averaged into a third number nobody measured.

The people reading these reports are marketers, and 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 as a third source

The agency also wanted to show clients how often AI assistants were fetching their sites. Analytics can't see that, since those fetchers don't run JavaScript, but the web server's access log does. So it goes into the warehouse too, beside the others and never blended in. I wrote about what the log can and can't tell you separately.

Where the history stops

Backfills are where reports tend to lie without meaning to. Every source only goes back so far, and here no two of them stopped in the same place.

Timeline from 2023 to October 2026 of how far back each source goes. Analytics from April 2023, backfilled through the reporting API until May 2026 and from the native export after. Search Console from February 2025, the API's 16-month limit. Social from January 2025, twenty months of monthly exports. Server logs from July 2026, because the host keeps 30 days.
No two sources start in the same month.

Analytics goes back to April 2023 through the reporting API, then switches to the native export from May 2026. I checked the two eras against each other on a month where both existed: conversions matched exactly and sessions came out at 11,720 against 11,661. Close enough to join, but the channel split before the switch is scoped differently, so I say that whenever someone compares years.

Search Console stops at its 16-month API limit. Social is twenty months of exports, loaded one month at a time. The server logs only start in July 2026, because the host keeps 30 days and nothing older exists anywhere.

The charts start each source where its data really 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. The figures still differ from each other, but for reasons people can see, and I think that was most of what they wanted.

If your reporting has the same problem, this is the kind of platform I build.

#data platform#BigQuery#reporting#analytics#Search Console