スポンサーリンク

「はてなカウンター」から１年分の「リンク元」情報を抽出し，URLをExcelに記録するバッチ　（URLのフィルタリング機能付き）

excel WSH/JScript はてなカウンター

アクセス解析の作業において，下記の２つを自動化するバッチ。

  • （１）アクセス元URLの抽出と，Excelファイルへの記録。
  • （２）アクセス元URLの一括フィルタリング。検索エンジンなどの除外。

まず一つ目から。


アクセス解析ツール「はてなカウンター」には，「リンク元」という項目がある。

当ブログが，Web上のどこのURLからリンクされ，何回アクセスされたか？

を記録・集計してくれる機能だ。


この「リンク元」ページは，１画面内に50リンク表示される。

しかし，「１年間分のリンク元をぜんぶ集計したい」という場合，

ページ数が数百ページ以上にも及び，Excelに手動で貼り付けるのは面倒すぎる。


そして２番目。

これらの「リンク元」URLのうち，大半はGoogleやYahooなどの

検索エンジンからのもので，情報としてあまり価値のないものだ。


なぜなら，はてなカウンターには「検索語」という機能もあって，

どのような検索ワードでアクセスされたのかが解析できるから。


「リンク元」を見る場合，検索エンジンなどのサイトは，リンク元から除外したいのだ。

だから，URLを自動的にフィルタリングしたい。というわけ。


以下では，このようなバッチのソースコードを掲載する。

そして，当ブログに対して実行した結果も合わせて掲載する。

（１）リンク元をExcelに記録するバッチ


はてなに関する自分の設定事項や，対象期間をバッチ内に記入して，

あとはダブルクリックするだけ。

すると，IEが起動して，はてなカウンターからリンク元情報を抽出してくれる。

はてなにログインしておくのをお忘れなく。


リンク元のURLを収集.bat

@if(0)==(0) ECHO OFF

echo 処理開始時刻： %date% %time% > log.txt

rem WSHコードに，第一引数としてカレントフォルダを渡す
cscript.exe //nologo //E:JScript "%~f0" "%~dp0"
rem cscript.exe //nologo //E:JScript "%~f0" "%~dp0" > log.txt

rem 環境変数から「パス名」（フォルダ名）を取り出すには ％~pX とする。
rem http://orangeclover.hatenablog.com/entry/20101004/1286120668


echo 終了しました。

echo 処理終了時刻： %date% %time% >> log.txt
@pause

GOTO :EOF
@end


/*

	はてなカウンターから，対象年内のリンク元URLを抽出してExcelに記録するバッチ
	
	・該当はてなIDにログイン済みであること
	・バッチ内の設定事項を調整すること

*/



// ---- 設定事項


// 自分のはてな情報
var hatena_id = "XXXXXXXX"; // "language_and_engineering";
var counter_id = "1";

// 対象年
var target_year = "2013";

// はてなカウンターの基本的なURL。
// 表示情報のページングに関する情報は除外してある
var counter_url_base = "http://counter.hatena.ne.jp/" 
	+ hatena_id
	+ "/report?cid="
	+ counter_id
	+ "&date="
	+ target_year
	+ "-12-01&mode=summary&target=link&type=yearly&"
;
	// 実際にはこの後ろに page=4 などが付与される


// 情報を記録するExcelファイル名
var xls_filename = "link_report_"
	+ hatena_id
	+ "_"
	+ counter_id
	+ "_"
	+ target_year
	+ ".xls"
;

// このバッチでページングを行なう際の最高ページ，リミット
var max_page = 1000;

// リンク元からアクセスされた回数の下限として認める範囲
var min_cnt = 2;

// １ページあたりに表示されるリンクの上限
var links_num_in_page = 50;

// ページングの開始ページ
var first_page_num = 1; // 通常は1からだが，実験用に変更することも可能



function log(s){ WScript.Echo(s); }



// ---- カレントフォルダにExcelを新規生成


var curr_dir = WScript.Arguments.Unnamed(0);
var file_path = curr_dir + xls_filename;
var fso = WScript.CreateObject("Scripting.FileSystemObject");

// ファイルが存在するか
if( fso.FileExists( file_path ) )
{
	log( "既にファイルが存在します。実行停止");
	WScript.Quit();
}
else
{
	log( "記録対象：" + file_path );
}



// Excelèµ·å‹•
var excel = null;
try
{
	excel = WScript.CreateObject("ET.Application");
}
catch(e)
{
	excel = WScript.CreateObject("Excel.Application");
}
excel.Visible = true;


// 新規ブック
excel.Workbooks.Add();
var book = excel.Workbooks( excel.Workbooks.Count );
	// http://language-and-engineering.hatenablog.jp/entry/20090717/p1

// 新規ブックを保存
excel.DisplayAlerts = false;
book.SaveAs( file_path );
log("とりあえずブックを保存しました");

// 先頭のシートを情報の記録場所とする
var sheet = book.Worksheets(1);



// ---- IEではてなカウンターから情報を抽出


// IEがビジー状態の間待ちます
function ie_wait_while_busy( ie, _url )
{   
	var timeout_ms      = 45 * 1000;
	var step_ms         = 100;
	var total_waited_ms = 0;
	
	while( ( ie.Busy ) || ( ie.readystate != 4 ) )
	{
		WScript.Sleep( step_ms );
		
		// タイムアウトか？
		total_waited_ms += step_ms;
		if( total_waited_ms >= timeout_ms )
		{
			log(
				"警告：タイムアウトのため，リロードします。("
				+ ie.LocationURL
					// http://blog.livedoor.jp/programlog/archives/298228.html
				+ ")"
			);
			
			// どこかに移動中なら，そこへの移動を再試行
			if( _url )
			{
				log( _url + "への遷移を再試行");
				ie_goto_url( ie, _url );
			}
			else
			{
				log( "リロード中");
				
				// 移動先が明示されていなければリロード
				ie.document.location.reload( true );
				ie_wait_while_busy( ie );
			}
			
			break;
		}
	}

	WScript.Sleep( 1000 )
}
	// http://language-and-engineering.hatenablog.jp/entry/20100310/p1
	// http://language-and-engineering.hatenablog.jp/entry/20100403/p1


// ページを移動
function ie_goto_url( ie, url ){
	ie.Navigate( url );
	ie_wait_while_busy( ie, url );
}



// IEèµ·å‹•
var ie = WScript.CreateObject("InternetExplorer.Application")
ie.Visible = true;
ie_goto_url( ie, "http://www.google.co.jp/" );
log("ブラウザでのアクセスを開始します。");


// ページが存在する限り抽出を続行
var page_num = first_page_num;
var continue_flag = true;
while( continue_flag )
{
	var target_url = counter_url_base 
		+ "page="
		+ page_num
	;

	// IEで開く
	log("[" + page_num + " ページ目] " + target_url + " を開きます");
	ie_goto_url( ie, target_url );
	
	// tableを取得
	var table = ie.document
		.getElementById("hourlyreport")
		.getElementsByTagName("table")[0]
	;
	var trs = table.getElementsByTagName("tr");
	
	// trが51行あるので情報抽出。先頭のタイトル行はスキップ
	for( var i = 1; i < links_num_in_page + 1; i ++ )
	{
		// 行があるか？
		var tr = trs[ i ];
		if( tr )
		{
			var y = ( page_num - 1 ) * links_num_in_page + i;
			log( y + "番目の情報を抽出");
		
			var tds = tr.getElementsByTagName("td");
			
			// URLを認識
			var elem_as = tds[0].getElementsByTagName("a");
			if( elem_as.length > 0 )
			{
				var elem_a = elem_as[0];
				
				//var link_url = elem_a.getAttribute("href"); // エラーになる場合がある
				//var link_url = elem_a.href; // エラーになる場合がある
				
				var link_url = elem_a.getAttribute("href", 2);
					// http://might1976.doorblog.jp/archives/51159843.html
			}
			else
			{
				// 「不明」などの文言の場合もある
				var link_url = tds[0].innerText;
			}

			// アクセス回数
			var cnt = parseInt( tds[1].innerText.replace( /,/g, "" ), 10);
			if( cnt < min_cnt )
			{
				continue_flag = false;
				log( "アクセス回数が下限に達したので抽出を終了" );
			}
			else
			{
				log( i + " 行目からリンクを抽出：「" + link_url + "」, " + cnt );
				
				// 書き込み
				sheet.Cells( y, 1 ).Value = link_url;
				sheet.Cells( y, 2 ).Value = cnt;
			}
		}
		else
		{
			// 行が途切れたらそこで終わり
			continue_flag = false;
			
			// ページが終わる場合もtable自体と先頭行は表示され，
			// 下部に「アクセスが記録されておりませんでした。」と出る。
		}
	}
	
	
	// 次のページへ
	page_num ++;
	if( page_num > max_page )
	{
		continue_flag = false;
	}
}

log("全ページのリンク抽出が完了");


// IEの制御を破棄
ie.Quit();
ie = null;



// ---- 終了


// ブックを保存
excel.DisplayAlerts = false;
book.SaveAs( file_path );
log( "ブックを保存しました。" );


// Excelを閉じて終了
//excel.Quit();
//excel = null;

log("全処理が終了");

これを実行すると，バッチと同じフォルダ上にExcelファイルが生成され，

その中にリンク元URLとアクセス回数が記録される。

↓こんな感じで。

https://www.google.co.jp/	129672
ブックマーク他	89545
https://www.google.com/	7097
http://language-and-engineering.hatenablog.jp/entry/20130502/	5427
http://language-and-engineering.hatenablog.jp/entry/20130502/PatternsOfMSDOSorBAT	4247
https://www.google.co.jp	3545
http://language-and-engineering.hatenablog.jp/entry/20110904/p1	2194
http://language-and-engineering.hatenablog.jp/entry/20110717/p1	1864
http://d.hatena.ne.jp/language_and_engineering/	1561
http://www.google.co.jp/	1154
http://language-and-engineering.hatenablog.jp/entry/20110114/p1	1106
http://language-and-engineering.hatenablog.jp/entry/20081014/1223905380	980
http://language-and-engineering.hatenablog.jp/entry/20080912/1221297779	836
http://language-and-engineering.hatenablog.jp/entry/20081001/1222857265	703
http://www2u.biglobe.ne.jp/~motida/xmlhunsenki.html	656
http://language-and-engineering.hatenablog.jp/entry/20120330/p1	554
・・・

アクセス数が２以上あったURLを，１年間分抽出したら，２２５６９個あった。

上記は，その最上位の部分のコピペだ。


これだけのボリュームを記録するのは大変だよ。

一度に画面に表示されるのは50個までだから。

452回のページングと毎回のExcelへのコピペを，バッチが自動的に処理してくれた，ということ。


このバッチ処理にかかった時間は，log.txtに記録される。

処理開始時刻： 2014/01/xx 1:36:06.60
処理終了時刻： 2014/01/xx 4:57:49.92

３時間２０分。　その間，ずーっとIEが動きっぱなし。

つまり200分間かけて，400以上のページをスクレイピングしたので，

１分あたり平均して２〜３ページのオープンしかしてないから，

Webサイト側には負荷はかけてない。迷惑なし。


でも，60ページとか70ページ目あたりから，ブラウザのタイムアウトが時折起こるようになる。

その辺の情報になると，DBからのクエリがはてなのキャッシュサーバ内に保管されていないんだろう。


でも，たびたびブラウザがタイムアウトで動かなくなっても，

このバッチではちゃんとリトライ処理が働いて，

ページのリロードがかかるから，処理が中断されないですむ。


参考：

ブラウザの自動操作の最大の問題，「タイムアウト」を克服するには
http://language-and-engineering.hatenablog.jp/entry/20100403/p1


プロジェクト専用のDSLで，効率的にIEを自動操作する（WSH/JScript）
http://language-and-engineering.hatenablog.jp/entry/20100310/p1


ブラウザのビジー状態を判定するための，より良い方法　（WSHでIEを自動操作する際，COMのアプリケーションイベントを利用する）
http://language-and-engineering.hatenablog.jp/entry/20100410/p1


上の抽出結果を見ると，私の場合，サイト内からサイト内に遷移しているケースが非常に多い。

なぜなら，私が執筆する情報は，どれも互いに関連しており，

過去の参考情報に対してリンクをまめに挿入しているから。

そうすれば，書く側も，読む側も，労力が削減される。


…のはいいんだけど，「リンク元」としては，あまり価値がない。

外部サイトから，どのようなリンクを受けているか，という要因を知りたいから。

理由：

Google検索エンジンのアルゴリズム(hummingbird)を把握し，SEOのキーワード分析手法に役立てよう
http://computer-technology.hateblo.jp/entry/20140118/p2

  • サイトが高評価を得るためには，被リンクの「質」に注目。リンクがキーワードを含み，なおかつ多数の分散したIPアドレスからリンクされているか


SEO時，アクセス解析ツールの利用目的は，ユーザについての仮説を検証すること
http://computer-technology.hateblo.jp/entry/20140118/p1

  • なんとなく上位ランキングを眺めているだけではだめ。時間の浪費

だから，次のバッチが必要になる。

（２）リンク元URLをフィルタリングするバッチ

自分のサイトとかを除外した結果を得たいので，URLのリストをフィルタリングにかけよう。


前述のバッチで生成されたエクセルファイルを，下記のバッチにドラッグ・ドロップすれば良い。

ただし，２シート目が存在することを事前に確認しておくこと。


URLパターンの絞り込み.bat

@if(0)==(0) ECHO OFF
cscript.exe //nologo //E:JScript "%~f0" %* 
echo 終了しました。
@pause
GOTO :EOF
@end


// ---- 設定事項


// ホワイトリスト
var reg_patterns_white = [
	/^http/
];

// ブラックリスト（検索エンジンやマッシュアップ，URL加工系のWebサービスなど）
var reg_patterns_black = [
	// 自分のブログ内のリンク
	/^http:\/\/d\.hatena\.ne\.jp\/language_and_engineering\//,
	
	// 検索エンジン
	/^http(s)?:\/\/www\.google\./,
	/^http:\/\/(nl\.)?search\.yahoo\./,
	/^http:\/\/www\.bing\.com\/search/,
	/^http:\/\/jp\.ask\.com\//,
	/^http:\/\/jp\.hao123\.com\//,
	/^http:\/\/websearch\./,
	/^http:\/\/cgi\.search\./,
	/^http:\/\/nortonsafe\.search\.ask\.com\//,
	/^http:\/\/notify\.bluecoat\.com\/notify\-/,
	/^http:\/\/(s)?(ksearch)?\.luna\.tv/,
	/^http:\/\/wsearch\.ocn\.ne\.jp\//,
	/^http:\/\/www\.search\./,
	/^http:\/\/geo\-cafe\.starthome\.jp\//,
	/^http:\/\/green\.search\./,
	/^http:\/\/image\.search\./,
	/^http:\/\/kids\.goo\.ne\.jp\/search/,
	/^http:\/\/office\.microsoft\.com\/ja\-jp\/results/,
	/^http:\/\/sp\-search\.auone\.jp/,
	/^http:\/\/webcache\.googleusercontent\.com/,
	/^http:\/\/www\.aolsearch\./,
	/^http:\/\/www\.hatena\.ne\.jp\/o\/search/,
	/^http:\/\/www\.metasearch\./,
	/^http:\/\/www\.pointtown\.com\/ptu\/search/,
	/^http:\/\/www\.so\-net\.ne\.jp\/search/,
	/^http:\/\/ysearch\./,
	/^http:\/\/isearch/,
	/^http:\/\/hatenatunnel\.appspot\.com\/language_and_engineering\/searchdiary/,
	/^http:\/\/www\.amazon\.(co\.jp)?(com)?\/gp\/bit\/apps\/web\/SERP\/search/,
	/^http(s)?:\/\/encrypted\.google\.com/,
	/^http:\/\/www(.+)\.delta\-search\.com/,
	/^http:\/\/www\.searchgol\.com/,
	/^http:\/\/www\.mysearchresults\.com/,
	/^http:\/\/pex\.jp\/search/,
	/^http:\/\/livedoor\-search/,
	/^http:\/\/kaikatsu\.jword\.jp/,
	/^http:\/\/eonet\.excite\.co\.jp\/search/,

	// URL加工系
	/^http:\/\/d\.hatena\.ne\.jp\/notify\-/,
	/^http:\/\/t\.co\//,
	/^http:\/\/search\./,
	/^http:\/\/r\.duckduckgo\.com/,
	
	// Webクリップ系，その他サイト
	/^http:\/\/b\.hatena\.ne\.jp\//,
	/^http:\/\/hatebu\.net\/entry\/d\.hatena\.ne\.jp\/language_and_engineering/,
	/^http:\/\/ceron\.jp\//,
	/^http:\/\/getpocket\.com\//,
	/^http:\/\/reader\.livedoor\.com/,
	/^http:\/\/tophatenar\.com/,
	/^http:\/\/k\.hatena\.ne\.jp\/keywordblog/,
	/^http:\/\/hatebu\-graph\.com/,
	/^http:\/\/tweetbuzz\.jp/,
	/^http:\/\/translate\.google\./,
	/^http(s)?:\/\/m\.facebook/,
	/^http(s)?:\/\/www\.facebook/,
	
	// ほか，はじきたいもの
	/^http(s)?:\/\/[^/]+\/$/ // ドメインのトップページからリンクされている
];




function log(s){ WScript.Echo(s); }


// ---- 引数取得


// 引数があるか
if( WScript.Arguments.length == 0 )
{
	log("同一フォルダ上のExcelファイルをドロップしてください。");
	WScript.Quit();
}

// ファイルパスを構築
var filename = WScript.Arguments.Unnamed(0);
var filepath = filename;
var fso = WScript.CreateObject("Scripting.FileSystemObject");
var filedir  = fso.GetParentFolderName( filepath );

// ファイルが存在するか
if( ! fso.FileExists( filepath ) )
{
	log( filepath + " は無効なファイルパスです。");
	log("同一フォルダ上のExcelファイルをドロップしてください。");
	WScript.Quit();
}
else
{
	log( filepath + " は有効なファイルです。");
}



// ---- Excelèµ·å‹•


var excel = null;
try
{
	excel = WScript.CreateObject("ET.Application");
}
catch(e)
{
	excel = WScript.CreateObject("Excel.Application");
}
excel.Visible = true;


// 対象ブックを開く
excel.Workbooks.Open( filepath );
var book = excel.Workbooks( excel.Workbooks.Count );


// 最初のシートにURLの生データが列挙されているとする
var sheet_raw = book.Worksheets(1);

// 2番目のシートにURLを整理する
var sheet_urls = book.Worksheets(2);



// ---- URLの生データをフィルタリング


var continue_flag = true;
var y_raw   = 1;
var y_write = 1;
while( continue_flag )
{
	// 該当セルが空でなければ読み取りを継続
	if( sheet_raw.Cells( y_raw, 1 ).Value )
	{
		log( y_raw + "行目のURLを検査" );
		var url = sheet_raw.Cells( y_raw, 1 ).Value + "";
		var cnt = sheet_raw.Cells( y_raw, 2 ).Value + 0;
		var ok_flag = true;
		
		// ホワイトリストに通る？
		for( var i = 0; i < reg_patterns_white.length; i ++ )
		{
			if( ! url.match( reg_patterns_white[i] ) )
			{
				ok_flag = false;
			}
		}
		
		// ブラックリストにひっかからない？
		for( var i = 0; i < reg_patterns_black.length; i ++ )
		{
			if( url.match( reg_patterns_black[i] ) )
			{
				ok_flag = false;
			}
		}
		
		// フィルタを通過した？
		if( ok_flag )
		{
			log("OK");
			sheet_urls.Cells( y_write, 1 ).Value = url;
			sheet_urls.Cells( y_write, 2 ).Value = cnt;
			
			y_write ++;
		}
		else
		{
			log("NG");
		}
		
		// 次の行へ
		y_raw ++;
	}
	else
	{
		// 空セルに到達したので読み取りを中断
		continue_flag = false;
	}
}
log("全URLのフィルタリングが完了");



// ---- 終了


// ブックを保存
excel.DisplayAlerts = false;
book.SaveAs( filepath );


// Excelを閉じて終了
//excel.Quit();
//excel = null;

log("全処理が終了");

ホワイトリストと，ブラックリストの両方を正規表現で保持しておいて，

安全なURLだけを残せばよいのだ。


このバッチを実行すると，そんなに時間はかからず，数分で完了した。

フィルタリング後の結果は，２番目のシートに記録されている。


↓こんな感じ。

http://www2u.biglobe.ne.jp/~motida/xmlhunsenki.html	656
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1040371430	252
http://alohayou.com/2008/10/hawaiian_olelo/	184
http://archive.1topi.jp/1/d.hatena.ne.jp/language_and_engineering/20110916/p1	183
http://u2k772.blog95.fc2.com/blog-entry-246.html	168
http://d.hatena.ne.jp/bluerabbit/20090801/1249080708	157
http://commte.net/blog/archives/3605	151
http://blog.sen-i.jp/vba/excel-vba-common-module-file.html	135
http://wiki.clockahead.com/index.php?Coding%2fVBA%2fExcel%2f%a5%bd%a1%bc%a5%b9%a5%b3%a1%bc%a5%c9%a4%ce%b4%c9%cd%fd	135
http://makoto-watanabe.main.jp/htmlDOM.html	113
http://d.hatena.ne.jp/replication/20130622/1371883575	109
http://d.hatena.ne.jp/elwoodblues/20120404/1333474343	107
http://blog.goo.ne.jp/xmldtp/e/169a579bc8004099188976f4b2e4cc0d	87
http://mspec.jp/blog/archives/197	84
http://d.hatena.ne.jp/kk_Ataka/20130828/1377693786	76
http://3rd.geocities.jp/kaito_extra/Source/MouseCtrl.html	75
http://naonao.ktkr.net/?p=1703	67
http://gigazine.net/news/20121210-headline/	63
http://programmerbox.com/2013-06-14_web_site_load_testing_tools/	63
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q14106051914	60
・・・


先ほどはURLの個数が22000以上もあったが，このフィルタリングのおかげで，５０８個まで絞られた。

有益な情報だけを残してくれるので，なかなか便利だと思う。



本エントリの目的は，こういうバッチの作り方と，ソースコードを実際に掲載すること。

だから，ここで得られた実行結果についてコメントするのは，別の機会に譲ろう。

関連する記事：

はてなカウンターのアクセス解析を整理して，「人気記事のランキング」を自動生成するバッチ
http://language-and-engineering.hatenablog.jp/entry/20140112/GenerateAccessRa...


はてなダイアリーに執筆した記事一覧を，表形式に整理するブックマークレット （アーカイブページを，Excelに貼り付けやすく整形加工）
http://language-and-engineering.hatenablog.jp/entry/20140102/p1


はてブのマイページから，情報を一括して整形・抽出するブックマークレット
http://language-and-engineering.hatenablog.jp/entry/20131229/p1

　

language_and_engineering 読者になる

広告を非表示にする
スポンサーリンク
スポンサーリンク