The Only World-Standard SEO Software

Download Now
SEO PowerSuite
SEO PowerSuite Hot-new version
Supported OS

Missed SEO opportunities (Part 2): Run SEO Analysis like a Pro

| Posted in category Analytics Crawling duplicate content Google Internet Marketing Off Page SEO On Page SEO Search Engine Optimization Search Engines SEO Tools


If your website is stuck at the bottom of search results, and you can't find the reason for its ranking stagnation, run a comprehensive analysis of your SEO strategies. It can be that your SEO techniques are missing on some essentially important components, which is holding back ranking growth.

In the first series we learnt how to run such analysis for small websites. This part will tell you how to find and fix SEO issues for sites of a large size.



From this post you will learn how to:

  • conveniently organize big amounts of SEO data;
  • run comprehensive SEO analysis of big websites;
  • research SEO data with the help of Excel;
  • spot underperforming pages and find out the reasons of their poor performance;
  • make the necessary adjustments to improve your current SEO strategies.

In a word, this guide will tell you how to run professional in-depth SEO analysis quickly and easily.

Ok, let it roll.

Stage 1: Migrating all the collected data to a spread sheet.  











The first thing you need to do is to import all the data you previously collected from different sources (see the first of the series for more detail).

We have created a customized Excel template that will let you bring together all the data you need for all-rounded website analysis.

You can download the file here:

Share this post to unlock the file download
Facebook Share

Just do one share, and the download link will be shown below

Please save a backup copy of the file so that you can return to the starting point if something goes wrong.

When you open the file, you will see that it has 4 default sheets:

  • Site Structure;
  • Google Analytics;
  • Backlinks;

(here you can import all the collected data)

  • Website Analysis

(this spread sheet will let you have a quick overview of all website problematic areas).   


Importing data to the Site Structure sheet  

First, you need to copy all the data you collected with Website Auditor to the Excel template. Before you do that, make sure that all the following columns in your project are populated with data:

  •   Page
  •   Title
  •   Meta description
  •   HTTP status code
  •   Robots Instructions
  •   Canonical URL
  •   Total Links
  •   Internal Links to Page

Next, select the entries from the Website tab, right-click on the selection and choose the Copy to Clipboard option from the context menu that opens. Alternatively, you can use the Ctrl+C keyboard shortcut (for Windows). Then open the Excel file -> go to Site Structure Sheet -> put a cursor in A1 cell and click Paste (or Ctrl+V).

Please keep in mind, that if some of the above mentioned columns aren't shown in Website Auditor working area, you won't be able to export its contents to any external file! Check if you have all the columns added and updated.

When done, the header of your Excel file should look like:

NB! You can also use data from any other source (not only Website Auditor). Just export the analogue data to a .csv file and make sure that:

  • the columns have exactly the same names as said above;
  • delete http://, www., https:// and a domain name from the URLs in the Page column. E.g.

the URL should look like /category1/page1.html .

Otherwise, the trick won’t work.


And finally, one more little but important tweak:

  • scroll down to the bottom of the Site Structure tab to check the number of rows you have in the table;
  • switch to the Website Analysis sheet;
  • click on the Table Tools option and choose the Resize Table option (see the screen-shot below);

  • replace the last number (it is 3 on the screen-shot) with the number of rows you have in the Site Structure;
  • click OK to apply the new condition.

As you see, Website Analysis sheet is gradually getting populated with data.


Importing data to the Google Analytics sheet

The second step is importing data from your Google Analytics account.

Go to Google Analytics and jump to the Traffic Sources -> All Traffic -> Primary Dimension module. There, switch to the Landing Page –> Pivot –> Medium mode. Here you can see traffic stats for each page and the sources where your site traffic is coming from.

Set Show Rows to Maximum and export the table to a CSV file.

For further analysis we will only need the columns Landing Page, Total, (none) – it’s your direct traffic, Referral and Organic. So, delete all the other columns in the exported .csv file and copy only the traffic stats from the remained columns to your Excel document (Google Analytics sheet -> A2).

NB! Make sure you don't copy the header of the column in the file you exported from Google Analytics and don’t edit the header of the Google Analytics sheet in the Excel template.

Done? Let’s jump to the last step.


Importing data to the Backlinks tab

Copy the data you collected with SEO SpyGlass to the Backlinks sheet.

The procedure is basically the same as with Website Auditor (see above).

Next, select all the links from the first column, switch to the Data tab -> Data Tools and select the Remove Duplicates option. Make sure you check the Domain и Anchor URL boxes and click OK.  Thus, you will sort out all links that have the unique Domain - Anchor URL match.

NB!  Please check the number of columns you have in the Backlinks sheet doesn't exceed 40. Otherwise, the formulas we use for the integrated analysis won't work.

Also, if you copy backlink data from some other source, make sure that the columns that contain the target URLs and domains that link to your site are named Anchor URL и Domain.


Now the Website Analysis tab is fully populated with the results.


Stage 2: Running Integrated SEO Analysis  











Now, when all the data is collected and imported to the Excel template, you can easily spot which pages show poor SEO performance.

That’s as easy as ABC.

Switch to the Website Analysis sheet. The page ranking factors that are highlighted with red or orange need your urgent attention!

If the color of a cell is green, the page is doing fine.

That’s basically it.


Stage 3: Drawing conclusions and making adjustments to your SEO campaign 

After you have spotted your site's underperforming pages and figured out which factors are hampering their ranking progress, make corresponding changes in your SEO strategies.

Perhaps you need to:  

  •   manage pages that are found in your Google Analytics account but aren’t found in your website structure;  
  •  manage pages that have links from other sites but aren't found on your website;
  •  check your pages crawlability and set the correct indexation instructions to search engines bots;
  • verify your site existing backlinks;
  • fix internal system of website links;
  • etc.

For the complete onpage and offpage SEO checklists, refer to the first part of this series.

* * * * *


Now you know all little-known SEO aspects that may be the reason for your website poor ranking performance. Conduct the analysis described in both part of this article, implement the corresponding adjustments to your SEO campaign and you will see that it will enhance your website ranking performance manifold.   

Good luck on your way to Google's top!

P.S. By the way, if you want to learn the other ways to use Excel for SEO research, this article will be of great help.  

Image credits: doug8888 (via Flickr), windowstoweb (via Flickr).


back to SEO blog