How to Audit 12 Months of Broken UTM Data Before Your QBR

Your quarterly business review is a week out, and the attribution slide does not reconcile. Paid social shows three different utm_source values, half your email is sitting in Unassigned, and the channel totals do not match what finance has. The data is not wrong about reality — it is fragmented, because twelve months of links were written twelve different ways.

Why twelve months of UTM data drifts#

Drift is not carelessness — it is the default behavior of any shared link process. Spreadsheets stop enforcing consistency once more than two people touch them, and a year is plenty of time for three contractors, a new hire, and an agency to each invent their own spelling for the same channel. SEMrush (2024) found that 67% of teams use UTMs but only 58% have a documented strategy, and 30% skip UTM markup in more than 30% of their campaigns. The gap between "we use UTMs" and "we use them the same way" is exactly where your QBR numbers go to die.

Two GA4 mechanics turn that gap into broken reports. First, GA4 is case-sensitive: email, Email, and EMAIL are three different mediums, so one channel quietly splits into three rows. Second, any hit GA4 cannot map to a channel lands in Unassigned — commonly 10–20% of sessions — which is where un-tagged and mis-tagged links pile up. When the data is this fragmented, as much as 26% of conversions can be credited to the wrong channel, which is the difference between renewing a budget and cutting it.

The six-step pre-QBR UTM audit#

This is the same sequence whether you do it by hand or with a UTM audit tool. The difference is only speed: the manual version is a spreadsheet weekend, the automated version is a scan plus a review. The steps do not change.

1. 1

   Export your source/medium report

   In GA4, open Reports → Acquisition → Traffic acquisition, set the date range to the last 12 months, add session_source / session_medium / session_campaign, and export to CSV. This is your raw evidence — every spelling that actually reached your property.

2. 2

   Grade it

   Paste or upload the CSV and get an A–F health grade with a one-sentence reason. The grade gives you a number for the QBR ("we started the quarter at a C, we are at a B now") and tells you how deep the cleanup goes before you spend any time on it.

3. 3

   Cluster the variants

   Review the issue clusters ranked by traffic impact. This is where the Anthropic Claude API groups fb, Facebook, facebook, and meta into one canonical channel and surfaces case duplicates and typos. The clustering is bounded and reviewable — it proposes, you decide.

4. 4

   Approve the canonical taxonomy

   Accept the suggested canonical values in one click — one lowercase token per source, medium, and campaign. Override anything that does not match how your team actually talks. Nothing is written to GA4 or your ad platforms; you are approving a map, not pushing changes.

5. 5

   Re-map the history

   GA4 hits are immutable, so you collapse the duplicates where you report. Take the variant→canonical map into Looker Studio, BigQuery, or your QBR spreadsheet so Facebook and facebook finally sum to one channel. New links go through a governed builder so the drift stops at the source instead of being re-cleaned next quarter.

6. 6

   Document and share

   Export the branded PDF health report and share the link before the meeting, so the channel owners see the same grade you do. Save the locked taxonomy as the team's reference. That document is what turns a one-time cleanup into a standard.

The grade is deliberately blunt because a QBR audience does not want a 40-tab workbook — they want to know whether the numbers can be trusted and what changed. A letter does that. Underneath it, the cluster list is where the actual GA4 UTM cleanup happens, and that is the next step to look at closely.

What the cluster step actually finds#

Here is what a year of drift looks like in the raw export. Every one of these reached GA4 as a separate row, and every one was meant to be the same two channels: paid Facebook and the monthly newsletter.

Once the map is applied, the four Facebook rows become one and the two newsletter rows merge — and your QBR channel totals finally match. The deeper mechanics of why a single capital letter forks a channel are covered in our note on a UTM naming convention your whole team will actually follow.

Days of spreadsheet work, compressed honestly#

It is fair to ask what you actually save. The honest answer: the scan runs synchronously for up to roughly 500 links and returns a grade in about a minute, and the first public scan has no signup wall. You still spend a few minutes reading the clusters and deciding the canonical names — that judgment is yours, not the tool's. What disappears is the part nobody enjoys: building pivot tables, eyeballing thousands of rows, and arguing about whether paid-social and cpc are the same thing.

Common mistakes that sink a pre-QBR cleanup#

• Trying to fix every row instead of the few clusters that move channel totals. Sort by traffic impact and stop when the curve flattens.
• Assuming GA4 will merge Facebook and facebook for you. It will not — case-sensitivity is a feature, and you must canonicalize them yourself.
• Cleaning the export but leaving the link process untouched, so the same variants reappear before the next QBR. Fix forward with a governed builder.
• Renaming sources in a way no teammate agreed to, then watching everyone ignore the new convention. Document the canonical map and share it.
• Forgetting Unassigned. Un-tagged links never show up in your UTM columns at all — you have to look at where those Unassigned sessions came from separately.
• Presenting a clean quarter with no plan to keep it clean. Pair the audit with automated drift detection so next quarter starts ahead.