[For non -engineers] Examples of analysis for utilizing Python and Excel for SEO measures in an easy -to -understand manner

In SEO measures, it is very important to determine the content and direction of the measure after confirming the data.But I think there are several steps to be able to judge something with data.

That means "collecting data."

I think that many of those who are reading this article are performing the "collection of data" with power play (collected manually).In conclusion, using the topical programming language "Python" and "Excel" can automate and analyze data.

If this trend is possible, there is no doubt that you can do a higher -grade analysis work than the marketer next to you.This time, for those who are engaged in SEO measures, we will introduce analysis examples for SEO measures that combine Python and Excel in an easy -to -understand manner.

I often hear Python, how can I use it for SEO measures?

Python is one of the programming languages that is good at natural language processing.

Natural language processing is simply a technology that uses a computer on a computer.In recent years, many people think that the AI boom seems to be the use of Python = AI, but it is one of the reasons that it was used for AI because I was good at natural language processing.

In addition to Python, there are many programming languages, but in fact, they are not good at it.For example, "C language" has the feature that the processing speed is very fast, so it is often used for those that require the reaction speed of games.

Recommended for how to use Python is "Collection of information" and "morphological analysis"

From now on, we will introduce how to actually use Python for SEO measures.There are two ways to use it for SEO measures.

"Scraping" is to automatically collect information from the web page.For example, you can check if the site you are always checking is regularly checked, and all the titles and contents of all articles posted on a specific website can be exhaled to CSV.

"Practical analysis" refers to dividing sentences for each word.Of course, if you want to raise the ranking with the keywords you aimed at in SEO, it is a rule of thumb to include the keywords searched with keywords in the content text.

However, in the process of writing the article, it is difficult to always be aware of "which keywords have been used".In such a case, if you use a morphological analysis, you can see how many words are included in the sentence in the text.

This time, I will introduce how to use these two with "Python".It is for non -engineers, so please read it with confidence.One point is that in order to use Python, you have to set the development environment to your computer.

However, if you use a tool called Colaboratory (hereinafter referred to as Colab collaboration) provided by Google, you can run Python on the browser, so you do not need to have a troublesome initial setting.The program created for this article is published in my Colab.

You can copy the published Colab and move it immediately in your own environment, so if you are not familiar with programming, please try it.

Python Use Example ① Let's get the latest trend of "SEO measures"

Let's introduce the method of "information collection" that grabs the trend using Python.

One of the ways to grasp trends is "the behavior of highly reliable people" and "watch the trend of websites".This time, as an example, I will collect the title of the article posted in the SEO category of "Web Staff Forum" in Python.

The list of the collected titles is made in Python for morphological analysis, and finally a pivot table of Excel.

Collect the titles of the page that exists in the SEO category page of the web person forum.If you want to do it manually, it is a very broken bone.Aside from the details of the detailed programs, let's look at the results of actually moving the program.

Here, it is defined as "trends = a lot of themes on the web", so the top words are keywords related to "trends".Naturally, the keyword list includes non -trendy information such as seminars.

For example, keywords such as "content", "Google" and "site" are ranked high.Isn't it a convincing result that "content" is coming to the top?Since the panda update in 2011, "Content SEO" to improve the quality of pages is one of the SEO measures that must be performed with a mast.

In other words, "content SEO" has been in the top of the trend for about 10 years, so it can be excluded as a "trend that has recently been attracting attention".Regarding "Google" and "Site", it is hard to say trends for the same reason, so I will exclude it.As a result, we analyzed that the hottest topic in the SEO area (as of April 2021) is "local SEO".In addition, many keywords called "My Business" were scattered.

Examples of use in actual articles

Google My Business Tactics by industry (Part 2) Distance and quality, emphasis snippet, offline strategy

"Google My Business" has been searching for search volumes year by year.The figure below is a graph that displays the trends in the last five years of "Google My Business" and "Local SEO" in the Google Trend.

It can be said that the need for companies and stores to attract customers using Google is increasing.I think this analysis is not a wrong result.

Next, download the data collected in Python with CSV and analyze further with Excel.When you move the sample program, [Result.You can download a file called CSV.

When you download it, the information you have scraped this time includes information on "Date, Article title, author, KWD, number of appearances".

This is analyzed by the author using the Excel pivot table function.

If it is a pivot table, you can analyze only the author you are paying attention to, and you can easily analyze it in terms of year and month.

In this way, Python does "information gathering" and "morphological analysis" that is difficult by Excel alone, spitting the results to CSV, and conducting an analysis work that is one step further than others by performing an Excel pivot table.You will be able to do it.

Of course, Python alone can carry out data shaping and analysis, but the program will be longer and the difficulty will increase.Instead of suddenly completing with python, downloading the collected data in CSV format and then analyzing with Excel, it is intuitive and easy to understand at first.

However, I want to know the analysis that can be completed with Python alone without using Excel!I think some people may think.For this reason, in this article, I will introduce an analysis method of articles using only python.

Python Use Example ② Let's analyze the contents of the article only with python

From here on, I will scrape the text of the article, perform morphological analysis, and analyze the appearance of the word appearance and the combination.The program is long because everything is done in Python, but there is of course no need to understand everything.

I hope you can feel, "Oh, can you do this with Python?"

In the first analysis, we performed morphological analysis for the list of headings that have been scraped.Here, let's scrape the "text of the article", perform "morphological analysis" and analyze the characteristics of the article.

Click here for the program. Let's compare the number of words and the number of combinations.

This time, the URL for the analysis is an article on the theme of "2021 SEO measures".Let's compare the words used on the two pages.

The entire graph is listed on the link, so please take a look if you have time.This is the result of the URL of ① and the URL of the green graph, and the URL of ② is scraped.The number of words used is drawn on the horizontal bar graph.

Looking at the results, the URL of ① uses words such as "core web vital", "Google my business" and "experience".

On the other hand, the URL of ② shows that the word "users", "content", "communication", "building", and "audience" are often used.

In the URL of (1), you can see that the content SEO and technical SEO are comprehensively handled.On the other hand, the URL of ② mainly compiles user needs, so the use of keywords does not match.However, it may not be a good idea to look at this, so I will continue to analyze it.

Next, if you look at the analysis results of the "words used in the set", you will understand the content written a little more on the page.

"Words used in the set" are technical terms called "N-GRAM", and simply put together a "combination of adjacent words".

If you count only by words, it will be difficult to understand the "context" of what intentions are used, but by conducting N-GRAM, you can understand the intention of the use of words.

Let's analyze the combination of words used in the set

I was able to understand what kind of words are used.Next, let's analyze the number of words combinations.It is described as a combination, but in short, what is the word used in the set.

It is quite troublesome to aggregate "two combinations" and "three combinations" with Excel, but with Python, you can easily tabulate them.Let's take a look at the analysis results of "combinations of words".

Looking at the combination of two words (n-gram = 2), the characteristics of the article may be easier to grasp.

The keyword "page experience", "core web vital page", "structured", "customer reaction", and "crawl budget" are used on the page of (1).On the other hand, on the page of (2), many tool names and writer information were collected, and the number of quoted from the content of the content was small.

In this way, Python alone can perform "information collection -aggregation -graph".The theme of the two pages mentioned this time was similar, but it may not have been suitable for comparison.

The original usage is that if you analyze the first and second -place pages of the search results of a certain keyword, you can analyze what words are used on each page, and to investigate multiple keywords.You will be able to see the next measure.

By the way, if you change the URL part of the sample code, you can do the same analysis on other pages, so please use it.

Inevitably, the more frequently appearing words on the top pages, the more "words that are interested in users".If the ranking of the target keyword is bad, the user needs are not well captured, but this sample code uses, "What kind of words are used in the top page.Isn't it an opportunity to analyze and think about what's missing your page? "

summary

What did you think.This time, we introduced two points: "Using Python and Excel to grab the SEO trend from the entry of the web" and "analyze the combination of words used in the text only in Python".

In the second half, only Python was used, but it can be analyzed by combining Excel pivot tables and Python.As the word "execel" says, you may think that it is better not to use Excel, but of course Excel has the advantage.

In particular, pivot tables are very powerful tools in analysis operations.Conversely, Python does not perform intuitive operation like a pivot table.There is a disadvantage that you cannot use it without writing a program, but there is an advantage that Excel cannot be done with "information collection" or "morphological analysis".

By using Python and Excel, you will be able to go up the ranks of other marketers.I would be grateful if you could use this article as a foothold, use Python and study.