Saturday Night Data Party: SEO Metrics Analysis

Hey, it's another Saturday night data party is Saturday the 18th of April. Today looking at some SEO data. So one of the things that I'm curious about is if we can glean from data like with the stuff you get out of uh service like RFs, uh can we get a sense of what causes an article to rank? Um specifically I want to look into marketing technology. We're gonna look at this year's data.

Hey parry, how you doing? Uh so we're gonna look at only live data in the English language. And we care about traffic. We like traffic. Uh let's see, I'll go turn on just my stream viewer here so I can see what I'm doing.

Good. So the question to ask is um can we infer anything? Whoops, I don't want Spanish, I want English. Um can we infer anything about what's likely to get an article to do well on the topic of marketing technology? So to do this, I'm gonna need a bunch of data.

Let's get started. All right, and for this do we want to use the database for this? Yes, let's use the database for this. Let's do let's call it RF's data frame. And actually, we want to write the query first.

We've got to do a select. What are the fields in our database that we care about? Actually, you know what? Let's do it for the data file first. I think that's actually a good starting place.

So let's go. Oop, did I specify? I need to specify as content URLs. And we'll call this SEO. Alright, so we have an initial look at 38,000 variables.

Let's do some cleaning. We want to make sure we get all the data in the correct types. And make it distinct. Let's see what's in here. We get.

Hi Christine. Glad nice to see you. Got article titles. We got the URL, the title, got authors, domain ratings, referring domains, traffic, the value of the traffic in dollar amounts, Twitter, Facebook, and Pinterest shares, date, number of words in the article, HTTP code, and republished date. Alright, let's get rid of, let's do a little bit of cleaning here.

We don't need HTTP code because all these should be working. If they're not working, there's really no point in looking at this. We can get rid of that for sure. Let's also fix that date field because those are both POSIX dates and uh you know, it's fine. Let's leave them.

We need to get rid of the number field. That's just the number of rows that's pointless. It's not going to be helpful. And what else do we want to get anything else we want to get rid of here? Okay, so let's do a little bit of cleanup here.

You'll notice that we've knocked out a few of the fields there. Now, some things that we might want to consider. When you're looking at a URL, um, and when you're looking at content titles, what are the things that are worth paying attention to here? Uh something like the domain might be a really helpful thing to see. Like, do certain domains just rank better and and therefore get better traffic.

So we're gonna use we're gonna add in the URL tools library and let's call this root domain equals domain of the URL. Oh help if I actually loaded the library. Alright. Oh, content URL. Helps if I get the field name correct.

So now we see root domain, we've got internal.com, smart insights.com, stuff like that. Let's look at this a little more closely. You can see that we're now starting to break these down into individual domains to see if the domain is going to be relevant. And then let's also add in. What else do we want to do?

Well, you know, let's let's see if we can figure out a couple of useful keywords that we might want to tag in here. To do this, we're gonna want to do uh what are called co-locations. So we're gonna go back up to the top here. We're gonna add in the quantity library for text mining. And let's see.

For this. How do I want to just I want to understand what are in what's in the headlines first before we do anything else? So let's do do. Let's turn the this into. So they call text DF.

But I only want the article titles. Content title. And then let's do a bit of cleaning. We should get rid of stop words, because stop words are bad. All right, so let's go through.

Didn't load quantity. That's stupid. What'd I screw up? Duh. Content as content title.

Would help if I had a stop words file handy, wouldn't it? Where's my stopwards file? Much better. All right. Let's look at our co-locations now.

So what we're doing, a co-location is when words appear next to each other. So in this example, marketing technology, the category, you're gonna see marketing and technology appear next to each other a lot. And one of the ways to to digest a piece of text very quickly is to identify the co-locations. Um determine and then what we're going to do is start counting them. So let's do this.

Let's do if one colook equals true. This can consume a lot of time. Yes, terminated. All right. Let's go back into our project, which literally just exploded.

And start over. This is why we copy and paste things. Alright, so we should also specify run collook in this case today, right now we want it to be true. Let's just double check our code. Transmute it, and then go through and tokenize it.

And then go. Oh, I see what I did wrong. At no point have I made this. Actually, that's true. CS it should be distinct.

Let's double check. I didn't make it distinct on the second pass through after I removed HCB code and row number. Well, that'll teach me. Blow that up, start over. This, by the way, is what data science looks like most of the time behind the scenes.

You never see the complete and total abysmal failures of things just blowing up constantly. So let's see here's where the error is. You can see up until this point, we've got 36,000 rows. Let's make it distinct. This is gonna sh shrink that data frame down really small.

If you look in the clean data set, it's only 7,000 rows. And anytime we're running a really big text mining operation, the smaller you can make your data set, the happier you're gonna be and the less time it's gonna take. Look at that. Just by shrinking that down and removing duplicate titles, we're done in in seconds. Okay, so let's look at the text stat.

So the co-location, I've got some press releases, that's not helpful. Digital marketing, marketing trends, marketing technology, SEO news, marketing services, customer experience. Alright, so we've got a few things here that are kind of useful, right? These are there's some diagrams that might be useful. So let's do this.

Let's go now back into our table here. And with this information that we have. Let's do digital marketing as a keyword, marketing trans marketing technology. Digital marketing count equals string count. And we're going to do this on content title.

The pattern is digital marketing. And what this is doing is it's going to create counters for the number of times specific keywords occur within the title. There is a way to do this programmatically, but as you can see, it takes some doing to do that. If you just ran it, you're going to get a lot of uh garbage the first couple times through. So I find that it's helpful to do it manually, at least for investigations like this.

Marking technology. And we'll make this lowercase as well in case that is happening there. Actually, do we want to do all these to be case insensitive? If so. I think we do.

So for that, we're going to need to transform this to a regex on this term. And then ignore case equals true. And then add that. Oops. So we're going to want to do this for all these.

Which means we don't need the second version of that. Okay, let's do what else is in our biograms? SEO B2B and customer experience. I think it would be good ones to do. Okay.

That's a good blend. And what we're going to see is we'll now see in our frame here. We've got our title, got a URL, author, domain rating, referring domains, traffic, traffic value, Twitter, Facebook, Pinterest. And then we can have our our counts of topics. You can see there's there's a few in there, not many.

So next what we want to do is from the things that are numeric uh alone, is there enough data to warrant uh further investigation? So let's do this. Let's do. Let's load our core plot library. And for this, let's do a spearman.

We should get the numeric data fields out first. So from here. This will create a data frame of only fields that have numbers in them because you can't do correlations on things that aren't numbers. Just doesn't work. And now anything that is an NA, we want to turn to a zero in this particular case.

And now do we think we have any fields that are there are zero value fields? If so, let's get rid of them. Because again, you can't have you can't do a correlation on fields that are just straight zeros. Just doesn't work. That's not good.

And now let's see. What this looks like. Helps if I load the whole thing. So what do we got? Referring domains trainer.

Well this did that wasn't helpful. All right, let's kill off the carrot stuff, because that clearly had did not in any way do what I wanted to do. And run it this way. X must be numeric. Oh duh.

Let's see what we got. So our interest is in traffic value, traffic. So traffic value and traffic are highly correlated. Makes sense. Traffic and referring domains is somewhat correlated.

Traffic in Facebook shares is moderately correlated. And then nothing else. Hmm. Okay. So at least within this set of metrics, you can see all the different counts.

We're looking by these different topics. Like for example, marketing trends and digital marketing co-occur a lot, right? There's a relationship there, but in this line here, the traffic line, the answer is guess what? Those topics, articles about those topics, at least with that in the headline, the frequency of that term doesn't matter. It doesn't have a relationship.

The relationship really is on traffic value, which I would in this case actually say traffic value is a trailing uh a lagging indicator, so that's not really useful. Referring domains is useful because it tells us that the number of referring domains to an article is related to traffic, and then Facebook shares for this industry for this niche of marketing technology. Now that's only 7,000 articles. These are the articles that have been written this year. Um sharing it on Facebook seems to be the way to go for this particular thing.

Now, if I want uh if I want to increase something else like referring domains, uh actually sharing on Facebook seems to matter there, Pinterest less so, number of words pretty weak. Um the next thing to do on if we were going to be doing this um for a client or a customer would be do the same word analysis, but instead of doing it on just the titles of the articles alone, getting the actual article text and doing the exact same analysis in the article text to see if if the writing that is in the article itself has importance, has relevance to the outcome. The other thing we can do is you notice that we didn't really do anything with those domain, those URLs and domains. Actually, you know what? Let's do that.

That would be kind of entertaining. So let's take our clean data frame here and just write it out. Dump that out there. Let's go to Watson Studio. And let's go to C Plex test.

Let's do an auto AI experiment. Just go call this SEO test createet's fish out our data file. And our the column we care about again, we're caring about traffic today. Regression type RMSE. Let's see.

Do we want to look at the settings? Nah. We're good. Let's go ahead and run the experiment. Now what Watson's going to do is it's going to take the massive amount of data.

And it's not massive, it's like 7,000 rows. But it's going to try to take a look at each of the individual columns and say, is this column relevant to the result, not only by itself, but in combination. So one of the things that we did not do is when you look at this result set, we're looking at variables on a one-to-one basis. So does is there a correlation between Facebook and traffic? There may be multiplicative effects.

Like something that does well Facebook and Pinterest together may do better than just Facebook or Pinterest individually. But you don't know that in this type of analysis. You have to use a more sophisticated platform that can get you those multiple combinations. It's called multiple regression subset analysis. In this case, it's using XG uh extreme gradient boosting, XGP of regressors, and it's going to start building pipelines.

And the pipelines it's going to look at is what has the lowest error rate. So it's already built pipeline one, which is the default, just straight up, which I actually find is the most useful for doing variable importance. Pipeline two, we'll do hyperparameter optimization, which is how much how fast do you run the experiment. Let's go ahead and dig into pipeline one, because I think there's might be some good things in there. Let's go to feature importance, traffic value, of course.

Number of words. So this is interesting. In the gradient boosting regressor, it came out with number words as being slightly more important. This is still not great. This is none of again, like Pearson and Spearman correlations, the same general numbers apply.5 is uh moderate correlation.

Uh 0.5 enough is is strong. In this case, traffic variable traffic value. We already uh agreed it was a um a lagging indicator, so that's not super helpful. And then everything else kind of sucks. So even here, this tells me that we don't have enough data to draw a conclusion.

We don't have any of the article text, we don't have any other things, but things like you know, author and stuff didn't make it in here as as being important to this pipeline, important to this this model to try and understand this data set. So hope you had fun and watching this. This is how you do this kind of analysis for uh for complex data sets to see what's in there to try and unpack them and make uh sense of them. You should do this with your marketing data, and these techniques apply to all marketing data, not just SEO, which is what we're playing with tonight, but things like marketing automation, lead scoring, especially super important for. Thanks for watching.

Hope you had fun. Hope you enjoy the rest of your evening. Take care. Want help solving your company's data analytics and digital marketing problems? Visit trustinsights.ai today and let us know how we can help you.