Optimizing a web site for natural search rankings includes 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 can assist create these spreadsheets, however few small and medium-sized companies can afford them. Right here’s the way to create an in depth search engine marketing spreadsheet without cost.
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 gives detailed studies that embody:
- Natural clicks for every web page inside a specified interval,
- Common place of your web site for natural queries,
- Natural click-through fee for every key phrase.
A freemium Google Workspace app known as “Search Analytics for Sheets” facilitates custom-made exports from Search Console, comparable to consolidating and exporting two key sections, “queries” and “pages”:
- Finest 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 the way to 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 below are extra instruments so as to add crucial search engine marketing knowledge to the spreadsheet.
2. Add Search Quantity
Add search quantity to the spreadsheet by way of SearchVolume.io, which pulls the typical 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 without cost for as much as 100 URLs. The online interface reveals the whole record 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 search engine marketing. You could find lacking parts so as to add or different areas of enchancment — e.g., descriptive headings, higher titles — to enhance your web site’s natural search efficiency. And it prices nothing!
