Googleアナリティクスの(not provided) をエクセルとWMTを使って調べる

何気に(not provided)が増えてきましたね、でもいちおう調べられるみたいです。

サイトによっては、(not provided)の割合が非常に多くなっている模様で、当ブログでも訪問キーワードの30%を越えました。というわけで、公式サイトに載っていた方法で試してみました。

(not provided) を調べる準備

公式Blogによりますと、(not provided)で匿秘されたキーワードでも、ウェブマスターツールを見るといいよと記されています。そこで、以下のように準備します。

1.ウェブマスターツールでキーワードをダウンロード
(表示件数が10件でも全件ダウンロードできます)

2.同一期間で、Googleアナリティクスでもキーワードをダウンロード
こちらの記事などを参考に全件ダウンロードします)

いずれもダウンロードファイルは、EXCELで文字化けします。予めテキストエディタなどで開き、文字コードをシフトJISに変更しておきましょう。

Excelにまとめ、MATCH関数で調べる

エクセルには、MATCHという比較の関数がありますので、これを使います。

=MATCH(検索値, 範囲, 0)

ウェブマスターツールのキーワードにはあるが、Googleアナリティクスのキーワードには無いものを探せばいいわけです。MATCH関数を使い、エラー値になったものがGA側で発見できなかったキーワードですので、それが(not provided)であると推測できます。

ポイントは、MATCH関数の最後の「照合の型」を0にするところです。処理は重くなるようですが(2000件程度数秒でした)、これを0にしておけば、「完全一致、事前並べ替え不要」で探索できるのです。

VLOOKUPもそうなのですが、EXCELの照合系関数は、予め並べ替えをしておかないと機能しないことが多いですよね。しかし、MATCH関数のこのオプション値は0にしておけば、ソート不要なのだそうです。

#N/Aとなっているキーワードが、Googleアナリティクスでは見られなかったキーワードとなります。

キーワードを調べる参考URL

しっかり検証したわけでは無いですが、まあ大体はこれでわかると思います。更に知りたいという方は、森野さんのランディングページから推察する方法を併用すれば良いでしょう。

Google ウェブマスター向け公式ブログ: SSL 検索における検索クエリ データの取得方法について
検索クエリ – ウェブマスター ツール ヘルプ
アナリティクス 日本版 公式ブログ: 検索をより安心してご利用いただくために : Google アナリティクスでの検索キーワードについて
ExcelのMATCH関数の使い方

あとはこれで、CVしたかどうかがわかるとイイんですけどねえ・・・。