Optimizing your site for organic search rankings involves identifying your best performing pages, missed opportunities, and areas for improvement. This process typically requires aggregating page and query data into multiple spreadsheets.
Expensive tools can help create these spreadsheets, but few small 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 “performance” Use sections to export your best and worst performing pages to Google Sheets or Excel. Search Console is free and provides detailed reports, including:
- number of organic clicks on each page within a specified time period;
- your site’s average position for organic queries;
- Organic click-through rate for each keyword.
A freemium Google Workspace app called “Spreadsheet Search Analytics” It facilitates customized exports from Search Console, including consolidating and exporting two main sections: “Queries” and “Pages.”
- Best performing URL,
- the search queries that cause the most clicks to that page,
- The average position of the page for each search query,
- The number of clicks the URL generated from all queries.
You may end up with a spreadsheet with URLs listed multiple times. To delete in Google Sheets, select the column with the URL listed, then select Data > Data Cleanup > Remove Duplicates.
To remove duplicate URLs in Google Sheets, select the column that lists the URL, and then Data > Data Cleanup > Remove Duplicates. Click on the image to enlarge.
You can now sort and filter your spreadsheet to see which queries are performing poorly, bottom queries that are still sending clicks, which pages are performing best or worst and how to improve them.
For example, use conditional formatting to highlight queries with rank 3 or below. This allows you to quickly find opportunities.
From the first export, here are the tools to add: Important SEO data into a spreadsheet.
2. Add search volume
To add search volume to your spreadsheet, search volume.ioget the monthly average volume of up to 800 keywords at once. This tool does not require registration. To avoid the complexity of merging two spreadsheets:
- Sort both spreadsheets by query (A > Z).
- paste the spreadsheet search volume In the first master sheet,
- Verify that the two columns containing the query are the same.
- Remove duplicate columns in a query.
3. Add a title, meta description, and heading
For more ranking opportunities, pull the title tag of each page. meta descriptionH1-H6 headings.
use tharp tools You can export this to up to 100 URLs for free. The web interface displays a complete list of headings. The Excel export places all of this in one cell.
Serp.tools retrieves the title tag, meta description, and H1 to H6 headings for each page. Click on the image to enlarge.
Combine the two spreadsheets by sorting them by the URL column (similar to the search volume query above).
The integrated spreadsheet is On-page SEO. You can find missing additions and other areas for improvement. for example, descriptive heading, better title — Improve your site’s organic search performance. And it doesn’t cost anything!
