Optimizing a site for organic search rankings involves identifying the best-performing pages, missed opportunities, and areas of improvement. The process typically requires aggregating page and query data on multiple spreadsheets.
Expensive tools can help create these spreadsheets, but few small and medium-sized businesses can afford them. Here’s how to create a detailed SEO spreadsheet for free.
1. Export from Search Console
Start with Google Search Console’s “Performance” section to export the best- and worst-performing pages into a Google Sheet or Excel. Search Console is free and provides detailed reports that include:
- Organic clicks for each page within a specified period,
- Average position of your site for organic queries,
- Organic click-through rate for each keyword.
A freemium Google Workspace app called “Search Analytics for Sheets” facilitates customized exports from Search Console, such as consolidating and exporting two key sections, “queries” and “pages”:
- Best performing URL,
- The search query driving the most clicks to that page,
- The page’s average position for each search query,
- Number of clicks that URL generates from all queries.
You may end up with a spreadsheet with URLs listed multiple times. To remove in Google Sheets, select the column listing the URLs and then go to Data > Data cleanup > Remove duplicates.
To remove duplicate URLs in Google Sheets, select the column listing the URLs and then go to Data > Data cleanup > Remove duplicates. Click image to enlarge.
You can now sort and filter the spreadsheet to see underperforming queries, lower-ranking queries that still send clicks, best- or worst-performing pages and how to improve them, and more.
For example, use conditional formatting to highlight queries that rank 3 or lower. This will immediately identify quick opportunities.
From that initial export, here are additional tools to add critical SEO data to the spreadsheet.
2. Add Search Volume
Add search volume to the spreadsheet via SearchVolume.io, which pulls the average monthly volume for up to 800 keywords in bulk. The tool requires no registration. To avoid the complexity of merging two spreadsheets:
- Sort both spreadsheets by queries (A > Z),
- Paste the spreadsheet with search volumes into the initial master sheet,
- Ensure the two columns with queries are the same,
- Remove the duplicate column with queries.
3. Add Titles, Meta Descriptions, Headings
For more ranking opportunities, pull each page’s title tag, meta description, and H1 to H6 headings.
Use Serp.tools to export this for free for up to 100 URLs. The web interface shows the complete list of headings. The Excel export places them all in a single cell.
Serp.tools pulls title tags, meta descriptions, and H1 to H6 headings for each page. Click image to enlarge.
Merge the two spreadsheets by sorting both by the URL column (like the search volume queries above).
The consolidated spreadsheet is a roadmap for on-page SEO. You can find missing elements to add or other areas of improvement — e.g., descriptive headings, better titles — to improve your site’s organic search performance. And it costs nothing!
