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?

Which tools are used for SEO data analysis?

SEO data analysis in this workflow draws on six tools. Screaming Frog supplies crawl data, Google Analytics supplies traffic and Search Console supplies search visibility. Ahrefs and Moz cover backlinks, and SharedCount covers social shares. To add custom fields to a crawl, see my post on XPath for better crawls. Server log files are a separate data source that shows how Googlebot crawls the site, as my guide to reading server logs explains.

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.

How do you validate SEO data before analysis?

SEO data is validated by checking that each dataset measures what you expect before you merge it. Search Console and Analytics numbers do not match exactly, because Search Console counts clicks in Google Search while Analytics counts sessions on the site. Google's guide to using Search Console and Google Analytics data for SEO lists the causes. Five of them are below:

• Analytics depends on the tag, so a page with a missing tag sends no data.
• Users who opt out of tracking do not appear in Analytics.
• Search Console runs on Pacific Time, while Analytics uses the time zone you set.
• Search Console reports only canonical URLs, while Analytics reports every tagged URL.
• Analytics excludes known bots automatically, while Search Console may not filter them out.

The raw totals differ, but the trends in both tools should be similar. For crawl data, Screaming Frog can compare two crawls and flag changed titles, word counts and internal links. That comparison helps isolate the technical change behind a traffic drop. It works only in the paid version, in database storage mode.

How do you interpret SEO data?

SEO data is interpreted by filtering two or more metrics at once and tying each filtered list to one technical, content or backlink task.

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.

How do you turn SEO findings into a report?

SEO findings turn into a report when each filtered list gets a task, a metric to watch and a review period. The table below applies that format to three of the filters above:

Measure each change by comparing two periods of equal length before and after it. Google's guide includes a Looker Studio dashboard template that shows Search Console and Google Analytics data side by side. That dashboard makes the report easy to share with stakeholders.

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.