Moz - SEOとインバウンドマーケティングの実践情報

[ステップ3] 両方のデータをExcelに入れる

[ステップ3] 両方のデータをExcelに入れる

次に、手に入れた2つのデータをExcelに入れる。データを全部同じシートに入れるのが便利だと思うけど、絶対にそうしなくちゃいけないというわけではない。ここでは、ウェブマスターツールから入手したURLのリンク数のデータを左側に、Google Analyticsから入手したアクセス数のデータを右側に並べてみた。

※Web担編注 Google Analyticsのエクスポートデータは、前半のグラフ用のデータ部分を削除して表部分だけにし、さらに各行の先頭にドメイン名(http://~)を追加して、ウェブマスターツールと同じスタイルにしておく必要がある。また、Excelに取り込んだあと、「閲覧開始ページ」と「セッション」の列だけを使うようにする(他の列は必要ない)。

[ステップ4] VLOOKUP関数を使う

さあ、ここでVLOOKUP関数の登場だ。VLOOKUP関数はこうしたデータセットのために作られたようなもので、2つデータセットの数値を容易に比較できる。両方のデータセットに1回ずつ、計2回VLOOKUPを実行することをお勧めする。結果は次のようになる。

注意 - サイト上にあるコンテンツの古さによっては、データが失われている部分があるかもしれない(これについてはおそらく、単独のテーマとして新たに記事を書く余地がありそうだ)。したがって、「#N/A」となっているところは「0」に書き換えておくべきだろう。

※Web担編注 Excelに以下の形でデータが入っているとすると、

A列ウェブマスターツールの「ページ」
B列ウェブマスターツールの「リンク」
C列VLOOKUPで「セッション」を抽出(詳しくは後述)
D列空き
E列Google Analyticsの「閲覧開始ページ」
F列Google Analyticsの「セッション」
G列VLOOKUPで「リンク」を抽出(詳しくは後述)

C2セルに次のように入れ、そのセルをコピーしてC列の他の行に貼り付ける。

=IF(ISNA(VLOOKUP(A2,E:F,2,FALSE)),0,VLOOKUP(A2,E:F,2,FALSE))

G2セルに次のように入れ、そのセルをコピーしてG列の他の行に貼り付ける。

=IF(ISNA(VLOOKUP(E2,A:B,2,FALSE)),0,VLOOKUP(E2,A:B,2,FALSE))

上記「ISNA」によって、原文にある「#N/Aを0に置き換える」処理が不要になる。

また、VLOOKUPのセルを埋めたら、このあとにシートを変更しても影響がないように、いちどC列とG列について、それぞれ列全体をコピーして[形式を選択して貼り付け]をし、[値]で貼り付けておくといいだろう。

[ステップ5] URLを分類する

さて、今回の記事では、URLを個別にたどって調べることよりも、全体でどういった現象が起きているのかという高度な分析を目指しているので、その目的にかなうようURLを分類していきたい。このステップで細かい所まで分類すればするほど、最終結果の精度が高くなるだろう。

そこで、カテゴリをURLに割り当てるルールをExcelで記述してみよう。これは、フォルダ構造に合わせただけのものから、クエリ文字列に基づくより複雑なものまで、さまざまな可能性がある。実際のところ、どんな分類が最適なのかは、サイト構造がどうなっているかによるので、残念ながらここで誰にでも最適なルールを書くのは不可能だ。それでも、各自工夫して実行すると、次のような表ができるだろう。

URLを分類する
※Web担編注 Excel上でA列のうしろに1列挿入して、各行についてB列に「カテゴリ」を入力していく。同様に、Google Analyticsの「閲覧開始ページ」(最初はE列、前述の「カテゴリ」を挿入した時点でF列)のうしろに1列挿入してカテゴリを入力していく。この図はウェブマスターツールの部分についてのカテゴリ分類が終わった状態を示している。

ページ分類ルール決めで悩んでいて、サイト構造が標準的な「サイト名.com/カテゴリ/サブカテゴリ/製品名」という形式になっている場合は、URLの「/」の数で分類するのが簡単だ。この場合、そのURLがどのカテゴリに属するかはわからないが、少なくともページが何階層目にあるかという基本的な分類はできる。

Excelの学習とURLの分類の2つは、努力して取り組んでみる価値があると本当に思うよ。この段階で追加するデータの質が高ければ高いほど、結果も良くなるだろう。

※Web担編注 コンテンツ管理システムを使っていて、CMS上にはカテゴリ分類の情報がある場合、それをまた別途Excelにインポートして、URLからカテゴリを自動表示させることも可能だろう。
次のページへ
この記事が役に立ったらシェア!
みんなが読んでるWeb担メルマガで、あなたも最新情報をチェック
  • SEOやデジタルマーケの最新情報をゲット
  • 事例やインタビューも見逃さない
  • 要チェックのセミナー情報も届く
みんなが読んでるWeb担メルマガで、あなたも最新情報をチェック
  • SEOやデジタルマーケの最新情報をゲット
  • 事例やインタビューも見逃さない
  • 要チェックのセミナー情報も届く

Web業界の転職情報

もっと見る
Sponsored by

今日の用語

Google爆弾
Googleに限らず、検索エンジンでネガティブな言葉を入力すると特定のホームペー ...→用語集へ

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

インフォメーション

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

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

サイトマップ
RSSフィード


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