How to make SEO report in Excel

Downsides of the Google Search Console interface

  • limited history
  • lack of page or keywords groupings
  • difficult to really understand SEO performance
  • limited metrics
  • limited to just top 1000 keywords or 1000 pages
  • understood only by the SEO experts

Solution

Firstly, extract all of your Search Console data using Search Console API (or by using Google Sheets extension if you want to avoid any coding). Then use to extracted data to build powerful, flexible, and very insightful set of SEO reports using Excel (or alternatively in Data Studio). Search Console is a very powerful source of data, but it is really difficult to draw any meaningful conclusions using the interface. In fact it is very easy to make the wrong conclusions (sometimes drop of rankings may actually be a good thing!). Also, howe are you supposed to measure your coverage using the interface? Extracting the Search Console data, and building custom keyword and page groupings is a key to great SEO Analytics.

Why to build Search Console report outside of the interface

  • over time accumulate long history of data and have a better understanding of your Year on Year growth and Seasonalities
  • get a full set of pages and keywords (instead of just top 1000)
  • add additional metrics for measuring SEO Coverage (Number of Pages with Impressions, Number of Keywords with Impressions
  • build Custom Content Grouping (Page Categories, Keyword Categories, Regions)
  • see data daily, weekly, monthly or monthly
  • easily share your reports across teams or clients (Excel or Data Studio)
  • boost your GSC report by combining it with your Google Analytics data to identify high value pages or keywords
  • use your rich Search data to understand market demands and to expand and superpower your Paid Search campaigns.

Excel or Data Studio

Excel

For websites with many thousands of pages or queries, we typically advice to use Excel for this report. PowerPivot in Excel can handle many millions of rows, and allows to build even more complex data models. Once it is built, it is very easy to use and requires less than 5 minutes to update with the latest data. The downside however is that PowerPivot is currently only supported on Windows, and Mac users would not be able to fully benefit from the report (unless using some sort of a workaround, such as Bootcamp assistant).

Data Studio

The advantage of using Data Studio over Excel would be the fact that once setup, it requires absolutely no maintenance (data gets auto-refreshed). The downside however is that Data Studio would not work well with many thousands of pages or keywords, especially if you create a lot of custom content groups (eg. page groups, query groups etc). So it is a quick and very cost effective solution, but less scalable than Excel is.Download free Excel template

SEO Brand

If you want to keep things simple, the most crucial split that you should apply to your SEO is splitting it into SEO Brand and SEO Generic. More information about why it is important to isolate your SEO Brand data from generic queries can be found here.

More info on SEO analytics and a FREE Excel template

SEO Report template in Excel can be downloaded from here.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store