Saturday Night Data Party: Fixing Broken Outbound Links

Alright, it is Saturday Night Data Party. Tonight, we are fixing up the blog, my blog. One of the things that, from an SEO perspective, that's important is to make sure that your site is kept up to date and that your content is relevant and fresh and such. And nothing tells the search engine that you're incompetent at this, like having outbound links that don't go anywhere, that are broken. Now for a young website, you know, less than a year old, uh it's not as big a deal, right?

You know, there's there aren't uh a ton. My site is 13 years old. So uh there's gonna be a lot of stuff. Hey, Shauna. Uh, we're gonna be using a bunch of different uh tools.

The first step in this is really important, is to have a tool that can identify the links. So the SEO tool of your choice. I actually use uh a local app called Scrutiny. Uh it is by Peacock Media, it's a Mac app uh and it will it goes through and identifies all the different uh links. You can use uh RFs, SEMrush, uh any of the major SEO tools will all go through and crawl uh your site and tell you uh here's all the crap that you haven't fixed up.

So uh that's the first step. The second step is going to be is gonna be based on the CMS that your system is using. So let's put away RFs because that's just embarrassing. Um I use WordPress. So in WordPress, if you look in the back end database, you're gonna be able to see uh all of the content on your website.

You can see, like you know, there's actual URLs here in this WordPress content. This is the database. This is MySQL. Which means that we can programmatically update everything all at once, which is really cool. We're going to need a 404 page.

We're going to need a URL that we're going to change links to. Here's my lovely 404 page. And what we're going to do is we're going to essentially take every bad link that's on the site and replace it with a link to the 404 page. Now, when somebody clicks on that link, you're going to get traffic to this page instead of sending them somewhere. They're like, oops, yeah, sorry.

Hence the mildly amusing animation. How are we going to do this? We have to write code that will essentially update our database for us. So let's dig into that because this is going to be uh mildly entertaining. Let's go ahead and get the bad link fixer up and running here.

Uh let's see. Yeah, you know, Shauna, it if you don't have an app, I would definitely say look at I like scrutiny. Uh Screaming Frog is also really good if you want to crawl your website. If you want it to be an app that you use on your desktop, screaming frog or scrutiny, I think, are terrific choices. Um if you want to use a web-based app, I like RFs personally.

I think they're their crawler is fantastic. Let's go ahead and start ourselves a new. Okay, we're not gonna need the SQL database. Our data folder is gonna be called data, and what I've done is I've taken the 2,000 uh bad links uh and put them into uh my data folder. Actually, no, there's there's close to 8,000.

Let's take a look at the results here. We have 404. We have the link text, which don't I really care about, and we have the bad URL, the URL that that uh went off uh and went missing. So let's go ahead and clean this up here and take our data frame. First things first, we want to I only really care about two things.

I want care about the error code, and I care about the URL itself. So we're gonna get rid of everything else. Let's do that. Looks good. Next, I only care about 404s.

I really four or fours are the most important to me. So let's do a filter URL equals 404 not found. Let's go ahead and look at this. Oh, status dir. That's stupid of me.

This is why you never overwrite your source data frame. 5300 404s, and now let's make sure that they're unique. Okay, 415 unique. So there's a bunch of URLs in this list that are uh on many pages. They've gone bad on many, many pages, right?

So we need to go through and fix this. Now I am seeing things like um some things that look like they could be flickr uh links, some things that could be graphic links and stuff like that. Those are potentially images within my site that uh may have gone bad and I don't necessarily want to I I should not replace an image link with a regular web link so what we're gonna set up a um a counter to just identify anything that's an image link and just delete it because we're not going to tackle fixing those right now we're gonna do image equals string count we're gonna look at the URL and the pattern is going to be fng dot jpeg and this should give us a list of let's see if we did that right yep is that an image can see if it's it's a it's not zero if it's an image. So now filter image equals zero so we're only gonna have image based fixed bad links okay so now we're down to 370 uh links. So we've gotten rid of the image ones which is terrific now we're ready to start going through and clean this up so next let's take this we're now only really care about the URLs.

We've validated this works okay so now I have a list of bad URLs, URLs that have gone bad. Here's what we need to do next. We need to. If you recall, I was saying that in SQL in this SQL database, I can create an update table in here that will essentially tell this system to change a link. And the query format for that in SQL looks like this.

Update your table name, set your post content. This is this is specific to WordPress, keep that in mind, specific to WordPress. Replace the post content with the bad thing with what the good thing is going to be where you're looking in the string itself. If the post content, the bad thing uh is greater than zero. So I'm gonna copy this right now because we're gonna need this in our R environment to create essentially 317 of these.

You could do this in Excel. 317 is not unmanageable. Um I'm gonna do it programmatically because I want to have a piece of software I can run again and again. So let's take a look at this. We're gonna have our uh our links are gonna be the bad URLs.

Our replace URL is and this is where I go and get my 404 page link, right? Okay, that's the the good URL. And now I'm going to take my links table. Actually, I'm gonna links fix. We'll do a paste zero here.

And the first half of that SQL statement that we were just working on. Like so. And now we're going to put in our where we could. This is the URL. And we're going to put in.

I need the single quotes here. Otherwise, this is going to blow up badly. And then our replace URL. There's our in string. And we want our single quote there.

This is the bad URL again. Equals zero. Now let's see. Clean that up a little bit. Let's see if we manage to make this work.

There we go. So now we have should have a table with the links and then the SQL statements. Now we don't need the SQL the URLs anymore. So we're just going to transmute down to our SQL statement. Let's take a look at this real quick here.

Okay. Let's turn this into a comma-separated value file. And write it out. It is easy as Pi, because Pi is really difficult to make. So what we should have is a whole file of these SQL statements.

Now, one thing I don't love about this, I don't love that there are question marks for each of the lines. See if I can remember how to do get rid of those. Quote escape should be none. I still don't I'm not getting rid of the quotes on those lines. Well, you know what?

We'll fix this the old fashioned way. If it's a new line with quotes, starting it, just make it a regular new line. Good. And if it's ends in a new line, quote, again, make it blank. Now let's go and test this.

We're going to take one of these lines and move it into a SQL statement. Paste this here. So far there's nothing glaring wrong. We're going to simulate the query. That's the bad one.

Okay. That looks good. It did not blow up. So now we take that entire file, paste it in here. Just one more time.

Important point here. SQL queries run against a database are not undoable. Once you hit go, if this damages your database, there is no way to undo it. So make sure that you're absolutely positively sure this is something that you want to do to your database. Run this on the copy and make sure that it didn't blow up first.

I, however, live dangerously. So we're gonna hit go. Well, let me clarify that. I live dangerously on my own personal website. I do not do this with the corporate website because that would just be bad.

And so what we've done now is taken all those bad links away. When Google comes by and crawls that content, it's going to look, it's going to follow those links, and it's going to follow all the links that were bad, and now they're all going to go to the 404 page, and Google's going to say, huh, okay, well, that's working. And instead of going uh, you know, and seeing just all these these crap links, it's going to just go to the 404 page and say, that was cool. I see that this is not working. And the link's not working, but it's not going to give the HTTP 404 status, which is the technical way to say that link is broken.

And so that will help to improve the internal linking and the reputation of the site. Is it going to make the world better overnight for my website? Probably not. But does it create a better user experience? Yes, because you're going to click on a link and it's going to clearly go to someplace that's that says, hey, this thing has gone missing.

Sorry about that. That is what we want. We want a better user experience, and in turn, Google rewards sites that have better user experiences. That's the process. Using like scrutiny or uh screaming frog or whatever the tool is.

The processing the data and then updating the database is we did it in 15 minutes. Should you do that to a live database? Not without testing, right? Um so that's it. Keep an eye then.

One of the things that's really nice about some of these tools, like the the RFs tool, is that once you do something like this, this is a crawl that's running. I would recommend very strongly that you rerun a crawl on your site and go back and look for more bad stuff, right? And and validate that it went through and it is no longer finding the crap. Right. You can see that this this crawl that I kicked off about uh 45 minutes ago has been finding crap pretty consistently, right?

So now once this one is done, uh actually I might just hit stop and say just run a new run. New, just start a new one, and then go through and have a check to make sure that the work we did was valid. Give it a shot. Be careful, please be very, very careful. Um play it safe when you're doing this sort of thing.

If you have follow-up questions on stuff like this, join the analytics for marketers community. It's over on Slack. If you go to trustinsights.ai slash analytics for marketers, you can uh ask questions about stuff that you've seen in these Saturday night data parties. This is in my Sunday evening newsletter as well. Uh feel free to just hit reply and ask questions.

But uh give it a shot. This will help your reputation as a website because it shows that you're trying to maintain it. And I would recommend you do this process once a quarter. If you've got a lot of content that you're putting up, you know, a blog post a day, maybe do it once a month. Um, I have not done this in a really long time because it's my personal website.

I don't take care of it as well as I should, um, the corporate website. But give that a shot. Thanks for watching. Hope you had a good time and uh enjoyed some uh some refreshments while we were doing this, and I'll talk to you soon. Take care.