Webコンテンツ監査のためにGoogleアナリティクスとScreaming FrogのデータをExcelで簡単に統合する方法

SEOのためのサイト内コンテンツをチェック（監査）するために、Screaming Frog SEO Spider（以下、Screaming Frog）のすばらしい各ウェブページに関するデータと、Googleアナリティクスのアクセス解析データをExcelで簡単に統合し、両方のデータを紐付けて活用する方法を紹介する。

GoogleアナリティクスとScreaming Frogのデータを組み合わせれば、SEOやサイト改善のために、数え切れないほどのメリットが生じる。

ウェブサイトの全体像をとらえ、人気ページで問題があればくまなく洗い出せる。

ウェブサイト内のまったく閲覧者のいないページや、いたとしても非常に少ないページがわかる。おそらく、そうしたページは、双方のデータを組み合わせればすぐ明らかになるような問題を抱えているのだろう。

Screaming Frogは、私のお気に入りのツールだ。日常的に使うSEOツールのなかで間違いなくナンバーワンだ（Mozに他意はないからね）。自分のもほかの人のも、ウェブサイトのデータを信じられないほどたくさん手に入れることができる。

たとえば、リンク切れを見つけられるし、カスタム検索を使えば、すべてのページでGoogleアナリティクスのトラッキングコードが正しくHTML内に存在するかを含めて、あらゆるコードの状況を確認できる。

すべてのリダイレクトをたどり、ウェブサイト内のリダイレクトパスに関する情報を得ることもできる。

しかしScreaming Frogは、ページの人気を教えてはくれない（そう、今のところはまだ）。人気を調べたければ何か分析用のパッケージが必要になる。

そこで、このパッケージとしてGoogleアナリティクスを用いようと思う。なんといっても、Googleアナリティクスはおそらく最も有名で最も使われている分析サービスだからだ。

両者のデータを1つにまとめ、ウェブサイト上にあるコンテンツの全概要とその人気の両方を把握できるようにしようというわけだ。

データの取得

ステップ1：Screaming Frog

取り組んでいるウェブサイトの必要情報をScreaming Frogで収集しよう。ボックスにURLを入力して「Start」をクリックすると、ウェブサイトからすべてのデータを取得してきてくれる（残念ながら無料版では500 URL分しかクロールできないが）。

Screaming Frogでクロールが完了したら、フィルタを「HTML」に設定して、「Export」をクリックする。これで、データをエクスポートできる。

ステップ2：Googleアナリティクス

次にGoogleアナリティクスでそのサイトのレポートを開き、［行動］＞［サイト コンテンツ］＞［すべてのページ］を選択する。

意味のあるデータが得られるように、データ範囲を数2～3か月程度の適切な期間に設定する（トラフィックが少ないサイトの場合は特に配慮しよう）。また、データ表の下にある［表示する行数］を5000にして、できるだけ多くのデータを取得できるようにする。

よし。そうしたら、データをExcelファイル形式でエクスポートしよう。

データ統合の前準備

これでExcel形式のデータが2セットできた。続いて、この2つを1つにまとめる。

まず、両方のファイルを開き、1つのExcelファイルの別々のシートに貼り付ける。どのツールからのデータなのかがわかるように、シート名をつけておこう。

screaming frogのデータを入れたExcelのワークシート

Googleアナリティクスのデータを入れたExcelのワークシート

次に、まとめたデータを入れる3つ目のシートを作成する。ここでは「統合データ」としよう。この時点で、次のような3つのシートができている。

もう1つ、やらなければいけないことがある。2つのデータを統合するには、どの行がどのURLに関するものかを判断できるようにする必要があるしかし、Screaming FrogのデータでURLを示す「Address」はドメイン名を含んだ全URLだが、GoogleアナリティクスのデータでURLを示す「ページ」はドメイン名を含んでいないパスだけだ。

そのため、検索置換を使って、Screaming Frogのデータから、最初のスラッシュの前にあるドメイン名を削除しておく必要がある。

Screaming Frogのシートを、この状態のデータから

こうしておく

これで、2つのデータソースにあるURLの形式が同じになったはずだ。

ここまではいいかな？ よし。それでは、この2つのデータセットを組み合わせる魔法のテクニックを紹介しよう。

データの統合

よし。まずScreaming Frogのシートですべてのデータを選択し、「数式タブ」の「名前の定義」をクリックする。識別しやすい名前をつけよう（僕はシート名と同じにしているが、定義する名前にはスペースを使えないことに注意）。

「名前の定義」を選択し、新しい名前を指定

同じことをGoogleアナリティクスのデータでも繰り返す。つまり、すべてのデータを選択し、「数式」タブの「名前の定義」を選択し、シート名と同じ名前をつける。

どちらも名前を定義できたかな？ いいぞ。いよいよ2つのデータを1つにする。

その前に、ここでファイルを保存しておこう。

さてここからが魔法の出番だ、3つ目のシート「統合データ」シートに切り替える。

「データ」タブの「その他のデータソース」から、「Microsoft Query」を選択する。

すると、データソースを選択するよう求められるので、選択肢から「Excel Files*」を選び、［OK］をクリックする。するとファイルを選ぶダイアログボックスが表示されるので、保存したExcelファイル（つまり、作業しているファイルそのものだ）を見つけて選択する。

すると、Excelの「名前の定義」で名前をつけた範囲が選択肢として表示されるので、それぞれクリックして選び［>］ボタンをクリックして右の「クエリの列」に追加する。

「次へ」をクリックするとエラーメッセージのようなものが表示されるはずだ。しかし、エラーではないので、［OK］をクリックして閉じればいい。

するとMicrosoft Queryのウィンドウが表示されるので、表の上部にあるボックスで、Googleアナリティクスデータの「ページ」をScreaming Frogデータの「Address」にドラッグする。次のようになるはずだ。

これで、2つのデータソースの全データが、同じURLのデータは同じ行になるように、自動的に再編されているはずだ。

メニューの［ファイル］＞［Microsoft Excelにデータを返す］をクリックする。

Excelに戻り、ダイアログボックスが表示されるが、そのまま［OK］をクリックすればいい。

これで出来上がりだ。

1つのシートにScreaming FrogとGoogleアナリティクスのデータを組み合わせたデータができているはずだ。あとはこれを好きなようにに活用しよう。

このささやかなチュートリアルで伝えたかったことが、みんなに届いて役に立つことを願っている。

最後まで読んでくれてありがとう！