Python SEO Analyzer: Build an SEO Audit Tool

We've reached the end of the first stage.

And so far we've learned a lot:

But if we stopped here, you might keep seeing them as separate lessons.

You know how to write a Loop. You know how to write a Function. You know how to read a CSV. And you know how to filter with Pandas.

But the goal from the start wasn't to memorize Python. It was to use Python in SEO work.

That's why this lesson has almost no new library, and we won't try to cram in new commands.

We'll build a complete program from start to finish.

The program will take a crawl of a site, analyze the data, produce a set of reports, and save each report in its own file.

We'll call it SEO Audit Analyzer.

But before we write any code, we'll apply what we learned in the last lesson.

What should the program do?

We have a crawl file called crawl.csv with information about the site's pages.

We want the program to give us:

And then save the important reports in separate CSV files.

We're not saying each of these results is an SEO problem that must be fixed. We're building an Analyzer that helps us find the pages that need review.

After that, it's the SEO's job to judge each case.

The project file

Make a file called crawl.csv and put this data in it:

And before we analyze the file, let's look at it with an SEO's eye.

We have 12 URLs, with 200, 301 and 404 pages among them. There are Indexable pages and Non-Indexable pages. There are titles. There are word counts. And there's at least one URL with a query parameter: ?page=2.

So we have enough data to apply most of what we've learned.

First, we write pseudocode

Before Python, we write the program in our own words.

That's the plan. Only now do we start translating it into Python.

Step one: reading the file

We'll use Pandas.

                                  URL  Status      Title   Indexability  Words
0                https://example.com/     200       Home      Indexable    900
1          https://example.com/about/     200   About Us      Indexable    650
2       https://example.com/blog/seo/     200  SEO Guide      Indexable   2200
3    https://example.com/blog/python/     200        NaN      Indexable   1800
4  https://example.com/product/phone/     200      Phone  Non-Indexable    320

The first thing we confirmed is that the file was read. We also noticed the empty title became NaN, which we know from the Pandas lesson.

Inspecting the data before analyzing it

Don't jump straight to the filters. We do:

Index(['URL', 'Status', 'Title', 'Indexability', 'Words'], dtype='str')

So we do have the columns we expect: URL, Status, Title, Indexability and Words. Then:

(12, 5)

So 12 rows and 5 columns, the same number of URLs we expect. Then:

URL               str
Status          int64
Title             str
Indexability      str
Words           int64
dtype: object

What matters here is that Status is a number, and Words is a number. So when we write df["Status"] == 404, we use 404 as a number, not "404".

Finding the size of the data

Total URLs: 12

It's a simple value, but having it matters. Now we know the main input has 12 URLs, and any analysis after this has to make sense against that number.

Starting with status codes

Before we pull out the 404s or 301s, let's understand how all the status codes are spread.

Status
200    9
404    2
301    1
Name: count, dtype: int64

So we have 9 URLs at 200, 2 URLs at 404, and one URL at 301. And if we add them up, 9 + 2 + 1 = 12, which matches the total.

That's our first Sanity Check.

Report one: 404 pages

The problem is clear. We want Status = 404. The code:

                             URL  Status     Title   Indexability  Words
7   https://example.com/missing/     404       NaN  Non-Indexable      0
8  https://example.com/blog/old/     404  Old Blog  Non-Indexable      0
404 Pages: 2

So we have two pages, the same number that showed up in value_counts(). The analysis makes sense.

Does every 404 need a redirect?

No. And this matters a lot.

The program here is saying: you have two pages with status 404. It's not saying: redirect both pages.

A URL might need a redirect. It might be a normal 404. You might have a wrong internal link that needs fixing. Or it might be a deleted page with no replacement.

So the 404 Pages report is Detection, not an Automatic Fix.

Report two: 301 pages

Same idea. The condition is Status = 301. We write:

                                URL  Status        Title   Indexability  Words
6  https://example.com/old-product/     301  Old Product  Non-Indexable     20
301 Pages: 1

We have one redirect. But again, a 301 doesn't automatically mean we have a problem.

Later we'd need to know, for example:

• Where does the redirect go?
• Is there a redirect chain?
• Do internal links still point to the redirect?
• Is the redirect intentional?

But the current data doesn't have that information. So we don't invent an analysis that isn't in the data.

Report three: 200 pages that aren't indexable

Here there are two conditions: first Status = 200, and second Indexability = Non-Indexable. So:

                                   URL  Status  ...   Indexability Words
4   https://example.com/product/phone/     200  ...  Non-Indexable   320
11        https://example.com/private/     200  ...  Non-Indexable   150

[2 rows x 5 columns]
200 Non-Indexable: 2

But are both of them problems? I can't say. For example:

/private/ might be a page that's not supposed to be in Google at all.

So once again, the program gives you Pages to review, not Pages that must be indexed.

Report four: 200 pages with no title

We have Status = 200 and, at the same time, Title = Missing. So:

                                URL  Status Title Indexability  Words
3  https://example.com/blog/python/     200   NaN    Indexable   1800
Missing Titles: 1

Now let's review the result itself. We have https://example.com/blog/python/. Status: 200. Title: NaN. So the filter really did what we asked.

Report five: 200 pages under 100 words

We want Status = 200 and Words < 100. We write:

                                    URL  Status    Title Indexability  Words
5  https://example.com/category/phones/     200   Phones    Indexable     80
9          https://example.com/contact/     200  Contact    Indexable     70
Pages under 100 words: 2

Do both pages have thin content? Not necessarily. It's completely normal for the /contact/ page to be short.

So a better name for this report is Low Word Count Pages, not Thin Content Errors. The difference in naming matters, because it reflects the right way of thinking.

Report six: blog pages

Here we're not looking for an issue. We're splitting the site by URL type. We want the URLs that contain /blog/. We use:

                                URL  Status      Title   Indexability  Words
2     https://example.com/blog/seo/     200  SEO Guide      Indexable   2200
3  https://example.com/blog/python/     200        NaN      Indexable   1800
8     https://example.com/blog/old/     404   Old Blog  Non-Indexable      0

We have three blog URLs. But notice something important: one of them is a 404.

If we ask: how many blog URLs are in the crawl? The answer is 3.

But if the question is: how many blog pages work with status 200? We need to add a condition.

                                URL  Status      Title Indexability  Words
2     https://example.com/blog/seo/     200  SEO Guide    Indexable   2200
3  https://example.com/blog/python/     200        NaN    Indexable   1800

The result is now two pages. And that's an excellent example of why the question itself has to be clear.

Of the blog pages, which have a missing title?

We can carry on the analysis on a smaller DataFrame. We have blog_200_pages, which holds only the blog pages with status 200. We do:

                                URL  Status Title Indexability  Words
3  https://example.com/blog/python/     200   NaN    Indexable   1800

Here we see the power of building one analysis on top of another.

Now let's review the URL parameters

In lesson 7 we learned urlparse(). And our data has https://example.com/shop/?page=2. We want the URLs with query parameters.

We can use what we already learned. We import:

Then make an empty list:

And go through the URL column:

['https://example.com/shop/?page=2']

Let's understand what happened.

We have for url in df["URL"]:, meaning: go through every URL in the URL column. Then parsed_url = urlparse(url) took the URL apart. And then if parsed_url.query:: if there's a query, add the URL to parameter_urls.

That's an important example, because we used Pandas, a loop, a list, a condition and urlparse all in one piece.

Does a parameter mean a problem?

No. ?page=2 on its own isn't enough to say Crawl Trap or Duplicate Content. You need to know:

• How many URLs are generated?
• Is Google crawling them?
• Is the canonical set correctly?
• Can the parameters generate an unlimited number of URLs?
• Are the pages useful?
• Are there filters?
• Does the sitemap include them?

So the program here says: you have URLs with parameters. Not: you have a Crawl Trap.

Let's print a summary of the audit

Instead of looking at scattered results, we make a summary. We write:

SEO Audit Summary
Total URLs: 12
404 Pages: 2
301 Pages: 1
200 Non-Indexable: 2
Missing Titles: 1
Low Word Count: 2
Blog URLs: 3
URLs with Parameters: 1

Now the program has really started to become an analyzer.

Before saving, pick the useful columns

We could save broken_pages with every column. But is that always best? It depends on the report.

For example, in the 404 report we might need URL, Status and Title. So we write:

                             URL  Status     Title
7   https://example.com/missing/     404       NaN
8  https://example.com/blog/old/     404  Old Blog

For the missing titles report, we might need every column, so we could keep them all. The idea is that the output itself is part of the program's design.

Saving the reports

We start with the 404 pages:

The 301 pages:

The non-indexable 200 pages:

Missing titles:

Low word count:

Blog pages:

After running the program, we'll have:

So we took one file and turned it into a set of reports.

Do we save the parameter URLs?

Right now we have parameter_urls, which is a list, not a DataFrame. We can save it the way we learned in the files lesson.

https://example.com/shop/?page=2

See how we came back to the files lesson again here.

The first full version of the program

Now we put all the pieces together.

And in the folder you'll find the crawl file, the reports, and the parameters file.

That's the first full version of the analyzer.

Is the program done?

In the sense that it does what's needed, yes. But we can organize it better. And this is where we come back to functions.

But notice our order. We built it first and got it working. Only then do we start organizing it.

We didn't start, in the first minute, by writing ten functions while we didn't yet know what the program would do.

A first function: inspecting the data

We have this part:

We might need it every time we analyze a file. Let's make it a function:

First Rows:
                                  URL  Status      Title   Indexability  Words
0                https://example.com/     200       Home      Indexable    900
1          https://example.com/about/     200   About Us      Indexable    650
2       https://example.com/blog/seo/     200  SEO Guide      Indexable   2200
3    https://example.com/blog/python/     200        NaN      Indexable   1800
4  https://example.com/product/phone/     200      Phone  Non-Indexable    320

Columns:
Index(['URL', 'Status', 'Title', 'Indexability', 'Words'], dtype='str')

Shape:
(12, 5)

Data Types:
URL               str
Status          int64
Title             str
Indexability      str
Words           int64
dtype: object

The advantage of the function is that we gathered one clear job, Inspect Data, in one place.

A function for the 404 pages

                             URL  Status     Title   Indexability  Words
7   https://example.com/missing/     404       NaN  Non-Indexable      0
8  https://example.com/blog/old/     404  Old Blog  Non-Indexable      0

The function's name here is clear. get_broken_pages(df) means: get the broken pages from this DataFrame.

A function for the redirects

                                URL  Status        Title   Indexability  Words
6  https://example.com/old-product/     301  Old Product  Non-Indexable     20

A function for the missing titles

                                URL  Status Title Indexability  Words
3  https://example.com/blog/python/     200   NaN    Indexable   1800

A function for the low-word pages

Here there's something we can improve. Right now we have Words < 100. But what if next time you want Words < 200?

Instead of changing the code inside the function, we can make the number a parameter. We write:

                                    URL  Status    Title Indexability  Words
5  https://example.com/category/phones/     200   Phones    Indexable     80
9          https://example.com/contact/     200  Contact    Indexable     70

If you want under 500:

The result changes because we changed limit, without editing the function itself. This is where we start to see why functions are really useful.

A function for saving a report

We keep repeating:

more than once. We could do:

And use:

Now one function is in charge of saving CSV reports.

Does everything have to be a function?

Again, no. You could make a function for every line in the program and end up with code that's harder than before.

We use a function when:

• The operation has a clear meaning.
• The operation might repeat.
• Grouping it makes the program easier to read.
• We need to pass it different values.

Not just because we learned functions.

An organized version of the project

Now we can write the program like this:

First Rows:
                                  URL  Status      Title   Indexability  Words
0                https://example.com/     200       Home      Indexable    900
1          https://example.com/about/     200   About Us      Indexable    650
2       https://example.com/blog/seo/     200  SEO Guide      Indexable   2200
3    https://example.com/blog/python/     200        NaN      Indexable   1800
4  https://example.com/product/phone/     200      Phone  Non-Indexable    320

Columns:
Index(['URL', 'Status', 'Title', 'Indexability', 'Words'], dtype='str')

Shape:
(12, 5)

Data Types:
URL               str
Status          int64
Title             str
Indexability      str
Words           int64
dtype: object

Status Codes:
Status
200    9
404    2
301    1
Name: count, dtype: int64

SEO Audit Summary
Total URLs: 12
404 Pages: 2
301 Pages: 1
200 Non-Indexable: 2
Missing Titles: 1
Low Word Count: 2
URLs with Parameters: 1

But now the program is better organized.

Look at what we actually used

This project brought together almost the whole first stage.

• Variable: df = pd.read_csv("crawl.csv")
• Lists: parameter_urls = []
• Conditions: if parsed_url.query:
• Loops: for url in df["URL"]:
• Functions: def get_broken_pages(df):
• Files: with open(...)
• URLs: urlparse(url)
• CSV: crawl.csv
• Pandas: pd.read_csv()
• Filtering: df[df["Status"] == 404]
• Missing values: df["Title"].isna()
• Export: to_csv()

So the tools that were separate at the start of the stage have become parts of one workflow.

The most important part of the project isn't the code

You could copy and paste the final code and run it. But if that's all you did, you missed the most important thing about the project.

What matters is being able to answer:

Because the data is tabular.

Because we go through the URLs and take each one apart with urlparse().

Because there's more than one Pandas condition that has to be met.

Because the missing title became a missing value.

To make the threshold changeable.

To check that the result makes sense.

If you understand these answers, you've got what this stage was for.

If you run it on a real export

The analyzer we built uses columns called URL, Status, Title, Indexability and Words. But as we saw in the Pandas lesson, a real export from Screaming Frog names its columns differently.

You don't need to change any function. All you need is one line after reading the file:

['https://example.com/old-page/']

And that's exactly the difference between building code on clear names and tying it to one particular file's shape. The functions themselves don't need to know where the file came from.

And if you want to take the analyzer one step further: merge a Search Console file into it with merge(), and pull out the 404 pages that still get clicks. You'll find that report very useful in any real audit.

This project is an analyzer, not a crawler

This is a very important point before we start the next stage.

Our current program takes crawl.csv, so the data already exists. It doesn't visit the site and collect the data itself.

The current path:

But in the next stage, we'll start building the part that comes before that.

So instead of telling Python: take this data and analyze it.

We'll start saying: go to the URL, send a request, see what the server returned, read the HTML, and pull out the data yourself.

And that's a very big step in the course.

Before we close the stage, here's a challenge

I won't give you the full code for this part, because the point here is for you to start building on your own. Use the same crawl.csv.

I want you to extend the analyzer so it produces a new report called blog-review.csv.

The report contains only the blog pages that have:

And, at the same time, at least one of these two problems:

or:

Watch the logic. We want:

Before you write the code, write it in plain English or pseudocode.

Challenge two

Write a function called get_blog_pages() that receives df and returns only the blog pages whose status is 200.

The result should be https://example.com/blog/seo/ and https://example.com/blog/python/, not https://example.com/blog/old/, because that one's a 404.

Challenge three

Write a function called print_summary() that receives the results you pulled out and prints a summary like this:

Think first: which parameters does the function need? There doesn't have to be only one solution.

Challenge four

Change the low word count from 100 to 200 without editing the code inside get_low_word_pages(). You should change only the value you send to the function. See what the result changes to.

Challenge five

Once the program works, try a deliberately wrong analysis. For example, instead of:

write:

And see the difference in the results.

The goal here is to see for yourself that code that runs doesn't mean code that answers the right question.

The final test of the first stage

You have a new file called final-audit.csv, containing:

You need to build an analyzer yourself. Before the code, write pseudocode.

Then the program needs to do the following:

1. Read the file.
2. Print the first 5 rows.
3. Print the column names.
4. Print the number of rows and columns.
5. Print the count for each status code.
6. Pull out the 404 pages.
7. Pull out the 301 pages.
8. Pull out the 200 pages that aren't indexable.
9. Pull out the 200 pages with a missing title.
10. Pull out the 200 pages under 100 words.
11. Pull out the blog pages whose status is 200.
12. Pull out the URLs with query parameters.
13. Use urlparse() to find each URL's hostname.
14. Split the URLs into Internal and External, taking the main site to be site.com.
15. Print a summary with the counts.
16. Save the reports in separate files.
17. Use at least one function with a changeable parameter.
18. Use a function to save CSV reports instead of repeating the same code.
19. Review a sample of each report before saving.
20. Check that the status codes add up to the total number of URLs.

The goal isn't the shortest possible program. And it isn't to write everything in one line.

The goal is that you can build the solution, understand every step, and go back to any step if the result comes out wrong.

If you managed this project yourself, even if you went back to earlier lessons along the way, you've reached the real goal of the first stage.

You're not expected to memorize Python. You now know how to start from an SEO problem, define the data, turn the problem into steps, pick the right tool, write the code, check the result, and save output you can use in your work.

And that's exactly the foundation we need before we go into the second stage and have Python start auditing the site itself, instead of receiving a ready-made crawl.