[1]セクションに掲載したものですが、われわれのコミュニティにとって非常に興味深く、大きな価値をもたらしてくれるのでこちらのブログに格上げしました。Frog)のすばらしい各ウェブページに関するデータと、Googleアナリティクスのアクセス解析データをExcelで簡単に統合し、両方のデータを紐付けて活用する方法を紹介する。Frogのデータを組み合わせれば、SEOやサイト改善のために、数え切れないほどのメリットが生じる。ウェブサイトの全体像をとらえ、人気ページで問題があればくまなく洗い出せる。ウェブサイト内のまったく閲覧者のいないページや、いたとしても非常に少ないページがわかる。したページは、双方のデータを組み合わせればすぐ明らかになるような問題を抱えているのだろう。Frogは、私のお気に入りのツールだ。日常的に使うSEOツールのなかで間違いなくナンバーワンだ(Mozに他意はないからね)。自分のもほかの人のも、ウェブサイトのデータを信じられないほどたくさん手に入れることができる。リンク切れを見つけられるし、カスタム検索を使えば、すべてのページでGoogleアナリティクスのトラッキングコードが正しくHTML内に存在するかを含めて、あらゆるコードの状況を確認できる。すべてのリダイレクトをたどり、ウェブサイト内のリダイレクトパスに関する情報を得ることもできる。Frogは、ページの人気を教えてはくれない(そう、今のところはまだ)。人気を調べたければ何か分析用のパッケージが必要になる。そこで、このパッケージとしてGoogleアナリティクスを用いようと思う。なんといっても、Googleアナリティクスはおそらく最も有名で最も使われている分析サービスだからだ。両者のデータを1つにまとめ、ウェブサイト上にあるコンテンツの全概要とその人気の両方を把握できるようにしようというわけだ。Frogで収集しよう。URL分しかクロールできないが)。Frogでクロールが完了したら、フィルタを「HTML」に設定して、「Export」をクリックする。これで、データをエクスポートできる。コンテンツ]>[すべてのページ]を選択する。意味のあるデータが得られるように、データ範囲を数2~3か月程度の適切な期間に設定する(トラフィックが少ないサイトの場合は特に配慮しよう)。データ表の下にある[表示する行数]を5000にして、できるだけ多くのデータを取得できるようにする。原文ではURLに細工をすることで、UIで選べる以上の「表示する行数」にする手法が解説されていたが、現在この方法は利用できなくなっている。行数が5000件以上ある場合は、下記の手順を何回か繰り返しすべてのデータをエクスポートして1つのExcelファイルにまとめてほしい。したら、データをExcelファイル形式でエクスポートしよう。データ統合の前準備これでExcel形式のデータが2セットできた。続いて、この2つを1つにまとめる。両方のファイルを開き、1つのExcelファイルの別々のシートに貼り付ける。どのツールからのデータなのかがわかるように、シート名をつけておこう。frogのデータを入れたExcelのワークシートGoogleアナリティクスのデータを入れたExcelのワークシート次に、まとめたデータを入れる3つ目のシートを作成する。ここでは「統合データ」としよう。この時点で、次のような3つのシートができている。1つ、やらなければいけないことがある。FrogのデータでURLを示す「Address」はドメイン名を含んだ全URLだが、GoogleアナリティクスのデータでURLを示す「ページ」はドメイン名を含んでいないパスだけだ。Frogのデータから、最初のスラッシュの前にあるドメイン名を削除しておく必要がある。Frogのシートを、この状態のデータからこうしておくこれで、2つのデータソースにあるURLの形式が同じになったはずだ。それでは、この2つのデータセットを組み合わせる魔法のテクニックを紹介しよう。Frogのシートですべてのデータを選択し、「数式タブ」の「名前の定義」をクリックする。識別しやすい名前をつけよう(僕はシート名と同じにしているが、定義する名前にはスペースを使えないことに注意)。同じことをGoogleアナリティクスのデータでも繰り返す。すべてのデータを選択し、「数式」タブの「名前の定義」を選択し、シート名と同じ名前をつける。2つのデータを1つにする。その前に、ここでファイルを保存しておこう。ここからが魔法の出番だ、3つ目のシート「統合データ」シートに切り替える。Query」を選択する。Files*」を選び、[OK]をクリックする。するとファイルを選ぶダイアログボックスが表示されるので、保存したExcelファイル(つまり、作業しているファイルそのものだ)を見つけて選択する。すると、Excelの「名前の定義」で名前をつけた範囲が選択肢として表示されるので、それぞれクリックして選び[>]ボタンをクリックして右の「クエリの列」に追加する。「次へ」をクリックするとエラーメッセージのようなものが表示されるはずだ。エラーではないので、[OK]をクリックして閉じればいい。Frogデータの「Address」にドラッグする。次のようになるはずだ。これで、2つのデータソースの全データが、同じURLのデータは同じ行になるように、自動的に再編されているはずだ。Excelにデータを返す]をクリックする。Excelに戻り、ダイアログボックスが表示されるが、そのまま[OK]をクリックすればいい。FrogとGoogleアナリティクスのデータを組み合わせたデータができているはずだ。あとはこれを好きなようにに活用しよう。このささやかなチュートリアルで伝えたかったことが、みんなに届いて役に立つことを願っている。http://b.hatena.ne.jp/entry/https%3A%2F%2Fwebtan.impress.co.jp%2Fe%2F2015%2F03%2F23%2F19608。