AI Overview CTR loss finder

A free Excel sheet for Search Console data that finds queries where click-through rate fell while ranking held, and separates AI Overview losses from title and snippet problems.

Last updated

On this page
  1. Why Search Console alone cannot answer this
  2. How it works
  3. How to use it
  4. Worked example
  5. What to do with each group
  6. Limits
  7. Related
  8. License and credit
  9. Update log

Short answer: a free Excel sheet for Search Console data. Paste your queries for two periods and it flags the ones where click-through rate fell by 25% or more while your average position moved by one place or less. You then check each flagged query in Google and record whether an AI Overview showed. The sheet splits the lost clicks into AI Overview queries, which a title rewrite is unlikely to fix, and queries with no AI Overview, which it might.

Free template · Excel · CC BY 4.0

AI Overview CTR loss finder

Four tabs: Summary, Queries, Settings and Start here. Room for 500 queries. Fictional example rows included. No signup.

Download (.xlsx)

Why Search Console alone cannot answer this

Google says sites that appear in AI Overviews and AI Mode are “included in the overall search traffic in Search Console” and reported “within the ‘Web’ search type” (Google, AI features and your website). That page does not describe a way to filter AI Overview traffic out. So Search Console can show that a query’s CTR fell. It cannot show that an AI Overview was on the page when it did.

The usual advice for low CTR is to rewrite the title and meta description. That still works when the cause is your snippet. When the cause is an AI answer above your result, a better title changes little, and the better question is whether the AI answer cites you.

How it works

StepWhat the sheet doesDefault
CTR changeCTR in the later period divided by CTR in the earlier period, minus one.Flag at a fall of 25% or more
Steady positionAverage position in the later period minus the earlier period.Steady if it moved by 1.0 or less
Enough dataIgnores queries with too few impressions in the later period.At least 100 impressions
Expected CTRYour own site’s CTR at that rounded position in the earlier period: clicks divided by impressions for every query at that position. Shown for context, not an industry benchmark.No setting
AI Overview checkYou search each flagged query and record Yes, No or Not checked, with the date.By hand
DiagnosisCombines the flag and your check into one line per query, and adds up lost clicks by group on the Summary tab.No setting
All three defaults can be changed on the Settings tab.

How to use it

  1. In Search Console’s Performance report, set the Date filter to Compare and choose two periods (Google’s help page: Advanced filtering and comparison). Google suggests weekly or monthly granularity to even out day-of-week effects.
  2. Copy the Queries table into the Queries tab: query, then clicks, impressions and average position for each period. If your export does not carry both periods, export each period on its own and paste them side by side.
  3. Sort or filter the Flag column. For each query marked “CTR fell at a steady position”, search it in Google and record whether an AI Overview showed, and the date.
  4. Read the Summary tab, then work through the Diagnosis column.

Worked example

The example has 12 fictional queries for a Dublin accounting firm. Six are flagged as CTR falling at a steady position, and one more fell because its position slipped from 7.8 to 9.9.

GroupQueriesClicks lost
CTR fell at a steady position, AI Overview shown3357
CTR fell at a steady position, no AI Overview156
CTR fell at a steady position, not checked yet227
CTR fell and position moved132
Clicks lost = earlier clicks minus later clicks. Fictional data, from the Summary tab of the download.

In this example, most of the lost clicks sit on three informational queries where an AI Overview showed. Rewriting those titles is unlikely to win the clicks back. The one query with no AI Overview, “how to file a ct1 return”, is where a title and snippet review comes first.

What to do with each group

  • AI Overview shown. Check whether the AI Overview cites your page. If it does not, look at what it does cite, and work through the GEO checklist. Measure AI citations over time with the SEO report template.
  • No AI Overview. Look at the live results page for ads, a featured snippet, video or other blocks above you, then rewrite the title and meta description to match what the searcher wants.
  • Position moved. This is a ranking change, not a CTR problem. Work out why the position dropped first.

Limits

  • AI Overviews do not show for every search, every person or every location. One check by hand is one sample: write the date, and look again before acting on a big query.
  • A fall in CTR at a steady position has other causes: new ads, a featured snippet or video block, a change in what searchers want, or seasonality. That is why the diagnosis says “may”.
  • Average position in Search Console is based on your topmost result, averaged across impressions, so a steady average can hide changes on individual searches.
  • Clicks lost are not adjusted for periods of different lengths. Compare periods of the same length.

License and credit

Free to use and adapt under CC BY 4.0, including for client work, with credit and a link: AI Visibility Census, “AI Overview CTR loss finder”, aivisibilitycensus.com/templates/ai-overview-ctr-loss/. The file that builds the sheet is on GitHub. Version 1 is archived on Zenodo with a permanent DOI: 10.5281/zenodo.23960477.

New templates and every Census test result, sent when they publish: get the Census by email. Free, and one click to leave.

Update log

DateChange
4 November 2026Template published (version 1).
4 November 2026Version 1 archived on Zenodo (DOI 10.5281/zenodo.23960477).