How to make SEO report in Excel
If you are serious about growing and understanding your SEO traffic, then your should really pay close attention to your Search Console data. In this article, you will learn how to make the most of your data and gain a really deep understanding of your SEO Performance and what is (or is not) driving your growth. You can get an Excel template from here.
We recommend taking out all of your data outside of the Search Console interface, and building powerful and very insightful SEO reports that should be a must for any business serious about organic growth. And do not forget that your SEO data may actually be very powerful also in helping your Paid Search campaigns. The ways you can use your SEO data are limitless (you just need to be a bit more open minded).
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.
For support contact us on: contact@analyticshacker.com.
We offer FREE Skype or Zoom consultations which you can book on our website (AnalyticsHacker.com)
