Moz - SEOとインバウンドマーケティングの実践情報
Webコンテンツ監査のためにGoogleアナリティクスとScreaming FrogのデータをExcelで簡単に統合する方法

SEOのためのサイト内コンテンツをチェックする(監査する)ために、簡単に各種のデータを統合する方法を解説
Moz(旧SEOmoz) 2015/3/23(月) 7:00 tweet46このエントリーをはてなブックマークに追加 印刷用
この記事はもともとMozのYOUmozセクションに掲載したものですが、われわれのコミュニティにとって非常に興味深く、大きな価値をもたらしてくれるのでこちらのブログに格上げしました。筆者の意見は筆者独自のものであり、Mozの考え方を示すものではありません

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にして、できるだけ多くのデータを取得できるようにする。

※Web担編注 原文ではURLに細工をすることで、UIで選べる以上の「表示する行数」にする手法が解説されていたが、現在この方法は利用できなくなっている。

行数が5000件以上ある場合は、下記の手順を何回か繰り返しすべてのデータをエクスポートして1つのExcelファイルにまとめてほしい。

よし。そうしたら、データを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アナリティクスのデータを組み合わせたデータができているはずだ。あとはこれを好きなようにに活用しよう。

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

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

この記事が役に立ったらシェア!
tweet46このエントリーをはてなブックマークに追加
みんなが読んでるWeb担メルマガで、あなたも最新情報をチェック
SEOやアクセス解析のなどノウハウをゲット
事例やインタビューも見逃さない
要チェックのセミナー情報も届く
編集長コラムを一足先に読める
日本赤十字社 東日本大震災 義援金募集
みんなが読んでるWeb担メルマガで、あなたも最新情報をチェック
SEOやアクセス解析のなどノウハウをゲット
事例やインタビューも見逃さない
要チェックのセミナー情報も届く
編集長コラムを一足先に読める

今日の用語

robots.txt
ロボット型の検索エンジンが自分のページを登録しないようにするためにサイト管理者が ... →用語集へ

連載/特集コーナーから探す

インフォメーション

Web担のメルマガを購読しませんか?
Web担の記事がコンパクトに毎週届くメールマガジン「Web担ウィークリー」は、10万人が読んでいる人気メルマガ。忙しいあなたの情報収集力をアップさせる強い味方で、お得な情報もいち早く入手できます。

Web担に広告を掲載しませんか?
購読者数10万人のメールマガジン広告をはじめとする広告サービスで、御社の認知向上やセミナー集客を強力にお手伝いいたします。

サイトマップ
RSSフィード


Web担を応援して支えてくださっている企業さま [各サービス/製品の紹介はこちらから]

GOLD SPONSOR
さくらインターネット株式会社株式会社KDDI ウェブコミュニケーションズ株式会社日本レジストリサービスオープンテキスト株式会社トランスコスモス株式会社株式会社ハイパーボックスDomain Keeper
SPONSOR
株式会社キノトロープ株式会社アイレップ株式会社ニューズ・ツー・ユーシックス・アパート株式会社ウェブアンテナ株式会社サイバーエージェント富士通株式会社Sitecore