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

panda-update-analytics-autofilter.gif

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

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

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

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

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

panda-update-analytics-landing-dimention-keywords.png

余分なデータはここで落とす

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

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

panda-update-analytics-landing-filter.png

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

(not provided)、(not set)も排除しておく

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

2.すべての件数を含めてレポートをCSVファイルに書き出し

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

panda-update-analytics-rowcount-prepare.gif

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

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

panda-update-analytics-rowcount.gif

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

CSV書き出し後の文字コード変換

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

panda-update-analytics-csv-export.gif

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

panda-update-analytics-csv-convert.gif

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

panda-update-analytics-csv.gif

3.先週のアクセスとの増減分を算出する

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

panda-update-analytics-num-selection.gif

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

panda-update-analytics-num-copy.gif

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

panda-update-analytics-ignore-duplicate.gif

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

panda-update-analytics-calc-offset.gif

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

4.レポートを見やすい形にソートする

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

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

  1. 「増減数」を降順にソート
  2. 「ランディングページ」を昇順にソート

panda-update-analytics-autofilter.gif

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

このページをシェアする

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

2012-07-21