How to compare Search Console data year over year in Google Sheets
By Jake Marsh · Updated
To compare Search Console data year over year in Google Sheets, install Queryledger, open Extensions > Queryledger > Open Queryledger and go to the Compare tab. Pick the property, choose Last 28 days vs the same 28 days a year ago, select Query or Page as the dimension, and click Compare into a new tab. You get one row per query or page with both periods, the change, and whether it is up, down, new or lost.
Before you start
- Comparisons read from the Search Console API, so the year-ago period must still be inside Google's 16 months. A 28-day year-over-year comparison fits easily.
- The year-over-year preset compares 364 days apart, so weekdays line up (a Monday is compared with a Monday).
Steps
-
Open the Compare tab in the Queryledger sidebar and choose the Property.
-
Choose what to compare:
Option Use it for Last 7 days vs the 7 days before Spotting a drop the week it happens Last 28 days vs the 28 days before Monthly reporting without calendar quirks Last calendar month vs the month before Client reports by month Last 28 days vs the same 28 days a year ago Seasonal sites and annual growth -
Choose Dimensions. Query shows which searches moved; Page shows which URLs moved. Tick the Page group option to compare whole sections of the site.
-
Set Min. impressions (10 by default) so one-off queries don't fill the tab.
-
Choose Sort by: Biggest click change, Biggest impression change, Biggest position change or Most clicks.
-
Click Compare into a new tab.
Reading the result
Each row shows clicks, impressions, CTR and position for both periods and the difference. The status column marks each query or page, and a summary line counts how many went up, down, new and lost:
- Up, Down or Flat: in both periods, with the change.
- New: in the recent period only, often a new page or a new ranking.
- Lost: in the earlier period only, worth checking for a removed page or a ranking that slipped away.
Sorting by Biggest click change puts the biggest losses and gains at the ends of the list, which is usually where the story is.
What it costs
Queryledger's free trial covers 30 uses or 14 days, whichever comes first, with no card needed. A use is one property in a pull, one comparison, or one backup's daily run that adds data, and the whole 16-month backfill counts as one. After that it's $49 a month or $492 a year per Google account, or $149 a month for a team plan that covers everyone at your company's domain. Install it from the Queryledger page.
Queryledger: Search Console Backups & Reports
Search Console data in Google Sheets, with daily backups that keep your history past 16 months $49 a month or $492 a year per Google account.
Questions
Why doesn't the year-over-year comparison use exactly 365 days?
It compares 364 days apart, a whole number of weeks, so weekdays line up. Weekend and weekday traffic differ on most sites, and lining up weekdays keeps that from skewing the change.
Can I compare two custom date ranges?
The Compare tab uses its four presets. For other ranges, pull each one on the Report tab into its own tab and compare them in Sheets.
Can I compare sections of my site instead of single pages?
Yes. Define groups with a regex on the Groups tab (for example Blog = /blog/), save them, and tick the Page group option on the Compare tab.
Does a comparison count against the trial?
Each comparison counts as one use, no matter how many rows it returns.
More Queryledger guides
- How to export more than 1,000 rows from Search Console
- How to keep Search Console data longer than 16 months
More Google guides
- How to create barcodes in Google Sheets (Rowcode)
- How to create a PDF for each row in Google Sheets (PickMerge)
- How to share only part of a Google Sheet (Rowsplit)
- How to print 5160 address labels from Google Sheets (Labelyard)