Optimizing a website for natural search rankings entails figuring out the best-performing pages, missed alternatives, and areas of enchancment. The method usually requires aggregating web page and question information on a number of spreadsheets.
Costly instruments may also help create these spreadsheets, however few small and medium-sized companies can afford them. Right here’s the way to create an in depth web optimization spreadsheet free of charge.
1. Export from Search Console
Begin with Google Search Console’s “Efficiency” part to export the best- and worst-performing pages right into a Google Sheet or Excel. Search Console is free and supplies detailed studies that embody:
- Natural clicks for every web page inside a specified interval,
- Common place of your website for natural queries,
- Natural click-through fee for every key phrase.
A freemium Google Workspace app referred to as “Search Analytics for Sheets” facilitates custom-made exports from Search Console, resembling consolidating and exporting two key sections, “queries” and “pages”:
- Finest performing URL,
- The search question driving essentially the most clicks to that web page,
- The web page’s common place for every search question,
- Variety of clicks that URL generates from all queries.
You could find yourself with a spreadsheet with URLs listed a number of instances. To take away in Google Sheets, choose the column itemizing the URLs after which go to Information > Information cleanup > Take away duplicates.
To take away duplicate URLs in Google Sheets, choose the column itemizing the URLs after which go to Information > Information cleanup > Take away duplicates. Click on picture to enlarge.
Now you can kind and filter the spreadsheet to see underperforming queries, lower-ranking queries that also ship clicks, best- or worst-performing pages and the way to enhance them, and extra.
For instance, use conditional formatting to spotlight queries that rank 3 or decrease. It will instantly determine fast alternatives.
From that preliminary export, listed below are further instruments so as to add vital web optimization information to the spreadsheet.
2. Add Search Quantity
Add search quantity to the spreadsheet by way of SearchVolume.io, which pulls the common month-to-month quantity for as much as 800 key phrases in bulk. The instrument requires no registration. To keep away from the complexity of merging two spreadsheets:
- Kind each spreadsheets by queries (A > Z),
- Paste the spreadsheet with search volumes into the preliminary grasp sheet,
- Guarantee the 2 columns with queries are the identical,
- Take away the duplicate column with queries.
3. Add Titles, Meta Descriptions, Headings
For extra rating alternatives, pull every web page’s title tag, meta description, and H1 to H6 headings.
Use Serp.instruments to export this free of charge for as much as 100 URLs. The online interface reveals the entire listing of headings. The Excel export locations all of them in a single cell.
Serp.instruments pulls title tags, meta descriptions, and H1 to H6 headings for every web page. Click on picture to enlarge.
Merge the 2 spreadsheets by sorting each by the URL column (just like the search quantity queries above).
The consolidated spreadsheet is a roadmap for on-page web optimization. You will discover lacking parts so as to add or different areas of enchancment — e.g., descriptive headings, higher titles — to enhance your website’s natural search efficiency. And it prices nothing!
