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.
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
| Step | What the sheet does | Default |
|---|---|---|
| CTR change | CTR in the later period divided by CTR in the earlier period, minus one. | Flag at a fall of 25% or more |
| Steady position | Average position in the later period minus the earlier period. | Steady if it moved by 1.0 or less |
| Enough data | Ignores queries with too few impressions in the later period. | At least 100 impressions |
| Expected CTR | Your 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 check | You search each flagged query and record Yes, No or Not checked, with the date. | By hand |
| Diagnosis | Combines the flag and your check into one line per query, and adds up lost clicks by group on the Summary tab. | No setting |
How to use it
- 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.
- 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.
- 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.
- 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.
| Group | Queries | Clicks lost |
|---|---|---|
| CTR fell at a steady position, AI Overview shown | 3 | 357 |
| CTR fell at a steady position, no AI Overview | 1 | 56 |
| CTR fell at a steady position, not checked yet | 2 | 27 |
| CTR fell and position moved | 1 | 32 |
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.
Related
- SEO report template with AI visibility
- GEO checklist, graded by evidence
- How long does it take a new website to get indexed and ranked in Google?
- All templates
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
| Date | Change |
|---|---|
| 4 November 2026 | Template published (version 1). |
| 4 November 2026 | Version 1 archived on Zenodo (DOI 10.5281/zenodo.23960477). |