Optimizing a web site for natural search rankings entails figuring out the best-performing pages, missed alternatives, and areas of enchancment. The method sometimes requires aggregating web page and question knowledge on a number of spreadsheets.
Costly instruments will help create these spreadsheets, however few small and medium-sized companies can afford them. Right here’s how one can create an in depth search engine marketing spreadsheet without spending a dime.
1. Export from Search Console
Begin with Google Search Console’s “Performance” 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 embrace:
- Natural clicks for every web page inside a specified interval,
- Common place of your web site for natural queries,
- Natural click-through price for every key phrase.
A freemium Google Workspace app known as “Search Analytics for Sheets” facilitates custom-made exports from Search Console, resembling consolidating and exporting two key sections, “queries” and “pages”:
- Greatest performing URL,
- The search question driving probably 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.
Chances are you’ll 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 Knowledge > Knowledge cleanup > Take away duplicates.
To take away duplicate URLs in Google Sheets, choose the column itemizing the URLs after which go to Knowledge > Knowledge cleanup > Take away duplicates. Click on picture to enlarge.
Now you can type and filter the spreadsheet to see underperforming queries, lower-ranking queries that also ship clicks, best- or worst-performing pages and how one can enhance them, and extra.
For instance, use conditional formatting to focus on queries that rank 3 or decrease. This may instantly determine fast alternatives.
From that preliminary export, listed here are further instruments so as to add critical SEO data 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.tools to export this without spending a dime for as much as 100 URLs. The net interface exhibits 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 SEO. You’ll find lacking components so as to add or different areas of enchancment — e.g., descriptive headings, better titles — to enhance your web site’s natural search efficiency. And it prices nothing!
