How to analyze logs in detail with Excel tables

Abstract: Webmaster are aware of the importance of Web site log analysis, but when the site log, the face of a line of code, see the dazzling but can not do. Today I would like to introduce a detailed analysis of the log with the Excel table method, I hope that the big

Webmaster are aware of the importance of Web site log analysis, but when the site log, the face of a line of code, see the dazzling but can not do. Today, I would like to introduce a detailed analysis of the log with the Excel table method, I hope to help.

First we create a new Excel file and import the log file into an Excel table.

In the data-> Import external Data-> Import data, open the selection data source box, the following figure

Then locate the site log directory, and at the bottom of the file type (T), select all the files so that your log files are displayed.

Select the Web log you want to view and open the Text Import Wizard directly. The first step here is to select the "separator symbol" in the appropriate file type, as shown below

After selecting the next step directly to the Text Wizard, you need to note that the division of the symbol to remove all the check.

The next step is to finish.

After the log is successfully imported, the rest of the work is analyzed.

For example, we want to see how many pages Baidu spiders crawl. We select the data-> filter-> AutoFilter in the Excel table. This will appear in the first row of the table with a small arrow with a Drop-down box, click the small arrow, select "Custom", open "custom AutoFilter", in the first row of the left box to select "Equals", the right fill "* Baiduspder* ", OK on it.

So what we see is Baidu spider crawl all content.

For some slightly larger site, Baidu Spiders crawl content is also a lot, it seems inconvenient, how to do? Oh, we can continue to screen ah. Copy the results of the filter above to another table (such as Sheet2). Take the same approach to continue filtering. For example, your website has a news column/news/, you want to see/news/This column has been crawled by Baidu how many times. Method Still, select data-> filter-> AutoFilter, select "Custom" in the first row Drop-down box, select "equals" in the left box of "Custom AutoFilter", and the right type "*/news/*", so that you can filter out columns/news/ The number of pages crawled and crawled. If you have many columns, you can filter them sequentially. In this way you can see the content and number of times each column is crawled at a glance. If some of the columns are significantly less fetching, you need to be targeted to the optimization of these columns. This is the purpose of our analysis of the log. When some of the columns are optimized after a period of time (at least one months), we still take this approach to see if the optimization of these column crawl volume increases. This is also our test optimization effect is very practical method.

Of course, we can also filter out many of the data we need through Excel. For example, you can filter out an IP address of the number of users to browse a total of pages, browse the pages. This is important for site layout and page design. Another example can see a search engine of a spider to crawl how many pages, grab the pages, crawl frequency waiting. Long time observation, you will find a lot of spiders crawl the law, and even can reverse the spider's crawl rules. More data also please according to their own needs to analyze, here do not repeat. In short, we can easily use the Excel table to filter out the content we need from the website log.