How to Do Data Analysis in SEO

Data analysis in SEO is done by merging each page's crawl, traffic, search visibility, backlink and social share data into one table and filtering it. In SEO projects, whether the site is big or small, building strategies on data ties decisions to measurement instead of guesswork. So which data is actually useful in SEO? How do we collect it? Where and how do we read and interpret it?

First, crawl the whole site and collect every URL along with its page-level attributes. I would suggest Screaming Frog for the crawl. We then merge each page's summary with data from Google Analytics, Search Console, Screaming Frog, social networks, Ahrefs and Moz. The result is a set of tables that are ready to read. Excel or Google Sheets is fine for holding them.

Let's look at what each of these 6 tools gives us, in order:

1. With Screaming Frog we crawl the entire site and collect attributes such as title, description, H tags and text ratio for every page.

2. With Google Analytics we list the pages that received organic traffic in the last three months. For these pages we collect total traffic, page speed and bounce rate. Time on page, number of conversions and conversion rate come from the same tool.

Update (October 2026): Universal Analytics stopped processing data on 1 July 2023 and Google Analytics 4 (GA4) replaced it. The GA4 metric list has no page speed metric, average engagement time stands in for time on page, and conversions are now called key events. To query the full Search Console data set with SQL, see my post on exporting Search Console data to BigQuery. You can take page speed data from the Core Web Vitals report in Search Console.

3. With Search Console we list the pages that received organic traffic over the same period. I deliberately use the same date range as Analytics so the two sets line up. Here we collect clicks, impressions, average position and click-through rate (CTR).

4. With Ahrefs we list the values of every URL that appears across the data above, using Ahrefs' Batch Analysis tool. Batch Analysis gives us number of backlinks, number of keywords the page is visible for and total traffic.

5. With Moz we list the Page Authority of every page. You can substitute another tool's score here (Majestic, for instance).

6. Finally we list our pages' visibility on social networks. SharedCount does the job: it tells us how many times each page was shared on Facebook and Pinterest.

Merging everything we collected from those six tools into a single sheet produces a table like the one below.

Tip: on large sites (an e-commerce store, say) splitting the data into per-category sheets instead of one giant table makes it easier to read and interpret.

Tip 2: to merge large data sets in Excel, look up the VLOOKUP formula.

Tip 3: to take this beyond Excel formulas, ZEO's article on SEO analysis with Python is worth reading.

Now the enjoyable part: how do we make sense of all this data?

From the collected data we can derive new improvements across technical, content and backlinks. With some imagination and sensible filtering, new strategies fall out of the sheet.

Here are 7 filter examples to get you started:

1. List the pages with high organic traffic and a high bounce rate. Does their content answer the user's query? Does it meet the user's intent? There is a content strategy hiding in that list.
2. List the pages with high organic traffic and poor page speed, and work on their performance.
3. List the pages ranking between positions 5 and 15 with no backlinks at all. If they rank there with no links, link building can lift their visibility.
4. List the pages in the top 3 positions with a low click-through rate. There is work to do on titles, descriptions or structured data.
5. List the pages shared heavily on social but receiving no organic traffic. If people like them yet they get no visits, they may be targeting irrelevant keywords. That calls for a content fix.
6. List the pages with a high conversion rate but low organic traffic and low backlink value.
7. Add Google Ads (formerly AdWords) data to the same sheet and list the pages with a high conversion rate but low organic traffic, then work on bringing more organic traffic to them.

Filtering several metrics at once like this is where imagination pays off.

Finally, I am sharing the spreadsheet shown in the screenshots above via Google Sheets. Anyone who wants to inspect or use it can grab it there.