Googleアルゴリズム変更で影響を受けたページを分析する方法（Excel編）

前回はGoogleAnalyticsのみでアルゴリズム変更による変化量を調べましたが、もっと高速に様々なページを測定したい場合に便利なExcelを使った測定方法をご紹介します。

１．ランディングページのレポートから余分なページを排除する

まず、測定対象のデータを抽出する作業です。

前回と同じようにGoogleAnalyticsで先週の同じ曜日と比較するようにし「コンテンツ」→「ランディングページ」を表示させます。

次に、セカンダリディメンションで「キーワード」を選び、URLとキーワードの両方が表示されるようにしましょう。

Excelにデータを書き出す前に「アドバンス」フィルターを使って調査に余分なデータをここで排除しておきましょう。　「アドバンス」フィルターはものすごく便利な機能なのでぜひマスターしてください。

私のブログの例では、ブログの単体ページを調査対象に絞り込みたかったので、URLが全て「/develop/年/月/」で始まっているものを抽出するために「正規表現一致」で「^/develop/d+/d+/.+$」を設定しました。

ここまで複雑じゃなくても、普通に「含む」にしておいてURLの一部を設定して絞り込みするだけでもずいぶん絞り込まれると思いますので、ぜひやってみてください。

また、上記の条件に加えて「(not provided)＝googleのSSL検索からのアクセス」や「(not set)＝検索エンジン経由でないアクセス」は検索キーワードの調べようがないので、ここで排除しておきます。

次にCSVデータの書き出しですが、デフォルトでは10件書き出ししかしてくれないので、私のブログページ（919件）を書き出すにはちょっとしたコツが要ります。

まず、上記のように件数指定で25件（10件以外であればどれでもOK）を選択します。

すると、URL欄に「rowCount%3D25…」というように、表示される件数がURLに含まれるので、書き出したい希望の件数（私のブログの例だと919）を指定してアクセスします。

これで少々時間がかかりますがCSVで書き出す件数を自由に指定することができます。
ただし、5,000件を超えるように大量の件数を一度に書き出そうとすると時間がかかり過ぎてスクリプトエラーになる場合があるので注意しましょう。

CSVの書き出しは「エクスポート」から「CSV」で書きだされます。

ただ、これをExcelでいきなり開くと文字化けが起こってしまいますので、TeraPadなどの文字コード変換保存機能を持ったテキストエディタで一度開き、文字コードを「Shift_JIS」、改行コードを「CR+LF」にして保存しなおしてください。

あとはExcelでレポートファイルを開き、余分な行や列を削除して見やすくします。　今回は「ランディングページ」「キーワード」「訪問数」だけを残して他は削除してあります。

過去と比較したレポートでは、1つ下の行が過去のデータになっているので、「訪問数」の次の列に、1つ下の訪問数が表示されるようにします。

この計算式を全ての行にコピーし、１行に今週と先週のデータが横並びになるようにしましょう。

ここで、URLとキーワードが重複している行（＝先週のデータ）は余分なのでフィルターで非表示にします。

あとは今週と先週の列の差分を「増減数」として新たな列に表示されるようにします。

これで必要なデータが揃ったので、全て選択にし、新たなワークシートを追加して内容をコピーしましょう。
内容をコピーする理由は、フィルター設定や計算式を無くした純粋なデータとして利用するためです。

あとは、各列を「オートフィルタ」でソートしますが、できればURLごとにどのようなキーワードで増減があったかを変化量で見てみたいですよね。

この場合はソートの順番を以下のようにします。

これで各ページごとにどのようなキーワードでどれくらいアクセスの変化があるのかが一覧で見られるようになりました。

2012-07-21