今さら人に聞けないWebの仕組み

サイト構成表の作り方～Excel＆Googleアナリティクスと成績表の作り方【図解つき】～

サイト構成表を0から作る方法を、ExcelとGoogleアナリティクスの2パターンで紹介します。さらにサイトの成績表を作って、サイト改善のヒントにしてみましょう。【第11回】

石井研二（MILS） 2020/11/25 8:00 Web担当者／仕事 | 解説／ノウハウ

今回のお話では、サイト構成表をゼロから作る方法を解説します。サイト構成表は、ページの管理をしたり、SEOのためのコンテンツを考えたり、Webサイト運営に大切なものです。前回の「サイト構成表を作るとサイトの成績表がわかる？入手方法も紹介【第10回】」でサイト構成表の大切さについて解説しているので、参考にしてみてください。

サイト構成表をゼロから作成する

本記事では、2つのサイト構成表の作り方を紹介します。解説する作り方は次の2つです。

Excelで作成する
Googleアナリティクスから作成する

Excelで作る方法を体験しておくと、Googleアナリティクスから作る方法も理解しやすくなり、今後のサイト運営に大いに役立ちます。まずは、Excelで作る方法から解説していきます。一緒に手順を追ってやってみましょう。

Excelでサイト構成表を作成する

Excelでサイト構成表を作成する方法は大まかに以下のとおりです。

手順① サイト構造を整理する
手順② タイトルやメタでページの内容を採録する

それぞれの手順について、順番に説明していきます。

手順① サイト構造を整理する

Excelを立ち上げて、新規のワークシートを1枚開いてください。一番左上隅のセル、1行目A列（以下、Excelのセルは1-Aといった書き方をします）にタイトルを書きます。

続いて、2-AにトップページのURLを記載しましょう。Excelが勝手にリンクのついた状態にしてくれますから、これをクリックするとトップページが確認できます。

次に、ページに通し番号をつけます。4-Bセルに「No.」と見出しを書き、その下の5-Bに「1」、6-Bに「2」と入れてください。B列全体を選んでセンタリングすると見やすくなります。

ここで、数字を入れた5-B、6-Bのセルを両方選んで、6-Bの右下隅にカーソルを合わせ、下にドラッグすると、数字がカウントアップされます。「だいたいうちのサイトは100ページくらいあるかな」と思ったら最初に1から100まで数字を書いてしまうと作業がしやすいです。

次はページ内容を記述していきます。まず、No.と書いた右隣の4-Cに「内容」、さらにその隣の4-Dに「URL」と見出しを書きます。

その下には、「トップ」「https://mils.co.jp/（自社サイトのトップページURL）^※」と入れます。このURLは次の図のように、ブラウザで実際にページを開いてアドレス欄からコピーしましょう。

※ここでは筆者の自社サイトを例にしています。

さて、ブラウザで改めてトップページを見てください。サンプルにしているサイトでは、下図のようにページの上部に「グローバルナビゲーション」があります。今のウェブではこのグローバルナビゲーションがほぼ全ページにあって、どこからでも重要ページに移動できます。このグローバルナビゲーションはサイト内のコーナー分けを示すもので、サイトを見に来た人はこれを見てサイトの全体構造をつかみます。

訪問者はグローバルナビゲーションが横書きの場合は左から右へ、上下に並んでいるサイトでは上から下へと読みます。基本的には最初に見る項目が多くクリックされやすいです。

サイト構成図で書き表すと、以下のようになります。訪問者側がこの順番、この構造でサイトを認識しているので、サイト構成表も基本的にはこの順番で記載し、この構造が見えるように書き進めましょう。

グローバルナビゲーションを順番にクリックして、出てきたページを順番に表へ移した状態が次の図です。これで、大切なコーナートップが全部採録されたことになります。これが自社のサイトの基本構造です。

【メモ】

基本構造を書き終わった段階で客観的に眺めて、「企業情報が先の方が良いのになあ」とか、「あれ？採用情報ってどこだろう？」と感じたらそれをメモしておきましょう。次のサイトをつくるときに役立ちます。

それから、トップページのメニューに入れている「サイトマップ（サイトの案内図）」や「English（言語設定）」は後回しにしましょう。

手順② タイトルやメタでページの内容を採録する

大体のサイト構造がわかったら、次は内容を記録していきます。ブラウザでトップページに戻り、ページ上のどこかで右クリックをして「ページのソースを表示」を選択します。

すると、別タブでページのHTMLソースが表示されます。アルファベットだらけのところはいったん無視して、前の方に出てくる日本語を探してください。下図の赤で囲ったような部分です。探す場所はソースの先頭から数行のところにある<head>の中にある、<title>と</title>の間です。

<title>●●タイトル●●</title>
<meta name="description" content="●●メタディスクリプション●●" />

この赤枠部分を「タイトル」「メタディスクリプション」と呼びます。「タイトル」はそのページの内容を端的に表す要素です。また、検索エンジンで検索結果に表示される場合は、大体このタイトルが見出しとして利用されます。

このタイトルがページの内容から外れていると、そもそも検索エンジンが紹介しなかったり、紹介されてもユーザーが選んでくれなかったりするので、とても大切な文章です。普段は目立ちませんが実は多くの仕事をしています。

そして「メタディスクリプション（以下、メタ）」とは、ブラウザや検索エンジンなど「HTMLを読んで理解できるプログラム」に情報を渡す役割を持ったタグです。ディスクリプションとは「説明」という意味で、文字通りページの内容を説明するための文章です。検索エンジンはよくここの文章を、検索結果の説明文として使うので、これも大切な要素です。

このように大切な役割を担っている「タイトル」や「メタ」は、そのページが何を重視しているかを表すもので、サイト管理者にとっても重要です。そこで、サイト構成表にもこれを加えてみましょう。

【メモ】「キーワード」はとっておこう

メタにはディスクリプション以外にも「キーワード」などがあります。キーワードの書式は、

<meta name="keywords" content="●●●,●●●●,●●,●●" />

と、いくつかのキーワードをカンマで区切って入れます。検索エンジンがキーワードを紹介順位の判定に使わなくなったので、今ではキーワードを入れていないサイトも増えていますが、あなたのサイトには入っているかもしれません。しかし、そのページで何を伝えたいかを示すものですから、大事にしてください。

このキーワードにはいろいろの使い道があって、そのページに登場している商品の品番を入れておくと、商品が廃番になった時、どのページを変更しなければならないかが制作ソフトの検索機能で一発で見つけられるので非常に便利です。

ここまでで、各コーナーの内容をサイト構成表に反映できました。あとは、第一階層の情報をピックアップし、第二階層以降をサイト構成表に反映させていきましょう。

ただ、情報を入れていくと見づらくなっていきますから、表示や背景、罫線などを使って、少し見た目を整理しておきましょう。

URLのところを見てみてください。

https://mils.co.jp/analytics_mils_report/
https://mils.co.jp/analytics_mils_report/feature/

上記のように、リンクに従って先に進むと「/」が1つずつ増えているのが分かります。これが「ウェブの階層構造」を反映しています。「/」が多いほど階層が深い、と考えてください。このURL「https://mils.co.jp/analytics_mils_report/feature/」とは、「mils.co.jp」というフォルダの中に「analytics_mils_report」というフォルダがあり、その中にまた「feature」というフォルダが入っているのです。

この構造を反映する形で、内容の方も、Excelの列を挿入して、下記のように段がずれるように見やすく記載しています。

No.	内容
8	サービス
9	ミルズレポート
10	ミルズレポートの特長

ここまでのように、ひと通り情報が入ってくれば、いったん完成と言っても良いでしょう。これでどんなページがどれだけあるかがひと目で分かり、タイトルやメタの情報をもとにオートフィルター機能などを使えば、どのページにどんなキーワードが含まれているかを洗い出すこともできます。リンクをクリックすれば実際にブラウザでページを表示して確認できますから、とても便利です。

【メモ】サイト構成表はのんびり作ろう

この地道な方式は、やっているうちに自社サイトへの理解が相当進むということが最大のメリットです。「うちのサイトってこんなにページがあったのか！ニュースのページどれだけあるんだ？」と意外に感じられることもあるでしょう。Webが生まれて20年。小さいと思っている企業サイトも、時間とともに成長しているのです。意外なところに意外なコンテンツが隠れていることもあります。リンクを見落とさないように、じっくり進んでください。

サイト構成表を地道につくる方法のデメリットは「しんどい」ことです。忙しいWeb担当者がこれをやるのは正直大変です。一気につくってしまおうとすると肩がこりますから、ちょっとずつ、「今日は2015年のニュースを取ってしまおうか」ぐらいの感じでゆっくり取り組んでください。

サイト構成表をもっと役立てる3つの方法

サイト構成表を作っただけれは十分ではありません。役立てるために、下記の3つの作業をしておきましょう。

作業① フォームの動きを再現する
作業② 階層構造を再現する
作業③ Googleアナリティクスのデータを取り込めるようにする

それぞれについて説明します。

作業① フォームの動きを再現する

先程作ったサイト構成表では、「資料請求・お問い合わせ」があります。ここのフォームに記入して送信すると、「確認画面」→「完了画面」が出てきて、お問い合わせが完了します。プログラムの動きなので省略しがちなのですが、サイト構成表に入れておきましょう。実際にフォームを記入して、テスト送信し、途中のURLを確認していきます。

当然、フォーム送信に伴う自動送信メールなどは飛んでしまいますから、受信担当者には声をかけてから実施してください。お客様相談室がメールを受けている会社などでお問い合わせを適当にテスト送信すると、要らぬ手間ができて迷惑をかけてしまうことがあります。

しかし、こうして画面の遷移やURLを採録すると、「ここがゴールなんだ」と再認識でき、「訪問者をみんなここへ誘導するのがミッションなんだな」と考えられます。Googleアナリティクスで目標設定を行う場合にも役立ちますから、しっかりURLを取得してください。

実は、多くのWeb担当者がフォーム遷移のURLを把握していません。これは一度とればずっと使える情報ですから、早めに把握しておきましょう。

作業② 階層構造を再現する

URLの「/」が増えていくと、階層が深まっていきます。サイト構成表を作ったときにブラウザのアドレス欄からコピペしただけのURLでは、その階層の関係が分かりにくくなっています。そして、いちいち全部URLを全部読み込むのはページが多くなると大変です。

そこで、「/」で区切ってExcel上に記載していくことでさらに階層の構造を明らかにしていくのが次の作業です。

まず、今、URLが書かれているのはG列ですから、これを丸ごと選択してコピーし、一番後ろのJ列（キーワード）の右に丸ごとペーストしてください。G列がそのままK列になりました。つぎに、K列を選んで、Excelの「データ → 区切り位置」の機能を使い、「/」で分けましょう。きれいに階層ごとに列を分けてくれます。

「データ → 区切り位置」の機能をクリックし、「カンマやタブなどの区切り文字によって～」を選択し、「次へ」

さらに、ドメイン部分を削除した状態が次の図です。

このままだと、どれがフォルダで、どれがファイルか分かりません。また、トップページのところはただの空欄になってしまって寂しいので「index.html」という本当の名前を書き加えましょう。こうして整理したのが次の図です。

これができたら、今のK列からM列（第2階層以降を整理した部分）までを丸ごとカットして、H列に挿入しましょう。これで見やすくなります。

作業③ Googleアナリティクスのデータを取り込めるようにする

さて、最後にサイト構成表に沿ってGoogleアナリティクスからデータを取り込むことができれば非常に便利ですよね。

これは、Googleアナリティクスからとってきたデータをサイト構成表につき合わせられるようにするだけです。Googleアナリティクスのデータの方を加工しても良いのですが、それだとデータをとるたびに加工しなければならないので、サイト構成表の方をGoogleアナリティクスに合わせておきましょう。

記事の中で例にあげているサイトでは、Googleアナリティクスの「行動 > サイトコンテンツ > すべてのページ」のページ欄に表示されるURLは下図のような形式です。ドメインがなく、「/」から始まっていますね。この形式に合わせてやれば、アナリティクスからダウンロードしたデータがすぐに取り込めるようになります。

階層構造の再現でやったのと同じように、サイト構成表のG列「URL」を丸ごとコピーして、今度はすぐ右のH列に挿入します。同じものが2列隣り合わせたかっこうです。

この挿入したH列を選択して、検索置換で「http://mils.co.jp」という部分を取り除き、ついでにリンクを解除すれば、次の図のように、Googleアナリティクスと同じ表記になりました。

これでサイト構成表は晴れて完成です。お疲れさまでした！

リンクになったURLと、Googleアナリティクスに合わせたURLの欄は、普段見なくても大丈夫なので、横幅を狭めておいて一覧性を良くしましょう。

この表があれば、次は「どのページにどんなキーワードでどれだけの人を連れてくるのか？」「そこからどうやって製品特長のページを見せるのか？」「どこからどれだけの人をお問い合わせに誘導するか？」ということを設計できます。

Googleアナリティクスからサイト構成表を作成する

次は、Googleアナリティクスからサイト構成表を作る方法です。

手順① 全ページをエクスポート
手順② 「ページタイトル」ごとのデータをエクスポート
手順③ データを並べ替える
手順④ 不要なURLを削除
手順⑤ ページの並べ替え

手順① 全ページをエクスポート

まず、Googleアナリティクスの「行動 > サイトコンテンツ > すべてのページ」を開き、全ページのデータをエクスポートします。

手順② 「ページタイトル」ごとのデータをエクスポート

次に、同じ画面で、表の上にある「セカンダリディメンション」というボタンを押します。入力画面が出るので、「タイトル」と入力し「ページタイトル」を選択します。

すると、下図のようにURLとページタイトルがセットになった表ができますので、これもエクスポートします。

ページタイトルの入った表から、URLとページタイトルの列をすべてコピーして、サイト構成表のエクセルの右側の余白にペーストします。下図のように、今ペーストした部分をページのURLの昇順に並べ替えたら準備OKです。

サイト構成表の方に次のようにVLOOKUP関数の式を書き入れます。

=VLOOKUP(G5,J$1:K$63,2,FALSE)

G5はサイト構成表でトップページのURLが記載されたセルの番号です。このセルの内容を右の表から探せ、という意味です。探す範囲を「J$1:K$63」で指定しています。J1セルからK63まで、全ページ分の63行を選択するという仕組みです。

次の「2」は、指定したJ列とK列のうち、取ってきたいページタイトルは2番目のK列に書かれていますから、それを取ってこいという指令です。最後の「FALSE」は、見つけた内容を完全一致で表示するように指定する呪文です。そのまま「FALSE」と記入してください。

これでURLを突き合わせて合致するページタイトルを、サイト構成表のH列に移すことができました。あとは同じ関数をコピーして全ページのH列の欄にペーストするだけです。

取り込み元の表が消えてしまうと困るので、この表のB列を全部選択してコピーし、同じB列に文字列としてペーストします。これで、関数は消え、手順②の表とは関係なく処理できるようになります。

なぜわざわざ関数を消すかというと、手順①のページタイトルが表示されている表は、様々な事情で手順②の表と数字が違うことが多いからです。「分析期間の途中でページタイトルが変わった」という事情で、大きく数字が変わることもあります。せっかくGoogleアナリティクスからデータを保ったままサイト構成表を作るのに、こうした事情で数字が違うのではもったいないため、ここは慎重にいきます。

手順③ データを並べ替える

データを、ページURLの昇順で並べ替えます。トップが「/」だった場合は、だいたいトップページが先頭に来ると思います。

手順④ 不要なURLを削除

不要なURLを削除します。パラメータがついているだけで、他と同じページだと言えるURLがたくさんあるからです。ページタイトルで同じURLが並んだいたら、どれかは不要です。「?id=」のような形式のしっぽがついたURLはかなり削除できます。

手順⑤ ページの並べ替え

ページのブロックをカット＆ペースト（挿入）で動かし、見やすい順番に並べ替えれましょう。そして、ページに通し番号を打ったら完成です。

あとは、URLを階層整理したり、ページへのリンクをつけたり、他の表からデータを取り込むなどして、役立つものにしていきまそう。

こうして作っていけば、少なくともページタイトルは入った形で、しかも成績表のついたサイト構成表がつくれます。あとは、メタの情報などを少しずつ入れていけば、十分実用に耐えるサイト構成表ができあがります。

この方法の注意点は、2つあります。

アクセスゼロのページは表示されない
この方法でサイト構成表をつくった場合も、一度ゆっくりサイトを巡回して、アクセスゼロのページがどこかに存在しないか、確認した方が良いです。
タグが入っていないと表に反映されない
人気コーナーでもタグが入っていないと表に現れません。意外に大きなコーナーが「ない！」ということがけっこうあるので、注意してください。それが見つかったら、急いで計測を開始しましょう。

アクセス解析結果をサイト構成表に合わせてページの成績表を作る

最後に、Googleアナリティクスから取ってきたデータと、Excelで作ったサイト構成表を合わせて、ページの成績表を作成してみましょう。

方法はいろいろとありますが、Excelでサイト構成表にGoogleアナリティクスのデータを入れて成績表を作ると、非常に役に立ちます。

Googleアナリティクスの「すべてのページ」の表はページビューの“多い順”になっています。しかし、“多い順”に見ても大切なことはわかりません。多くのWeb担当者がGoogleアナリティクスを「どう見て良いのかわからない」と感じるのは、この“多い順”のせいです。たとえば、以下のようなページビュー数があったとします。

ページタイトル	訪問数
トップページ	10,000
企業情報	9,000
製品情報	8,500
会社概要	7,000
事業所一覧	4,000
製品B	3,500

このように、多い順に見ても、「トップの次に企業情報と製品情報が多く見られています」としか言いようがありません。「それで？」となりますよね。

同じような情報でも、サイト構成にしたがっていれば、見えてくるものが変わります。次の表を見てください。

ページタイトル（第1階層）	訪問数
トップページ	10,000
企業情報	9,000
社長メッセージ	1,000
会社概要	7,000
沿革	1,500
事業所一覧	4,000
ページタイトル（第2階層）	訪問数
製品情報	8,500
製品A	1,000
製品B	3,500
製品C	800

こうして並べると、誰の目にも、「社長メッセージが他のページと比べて見られていません」「歴史の長いのが自慢の会社なのに、沿革が見られていません」ということがわかります。「製品ではBばかり見られて、AやCがだめですね。新製品のCがもっと見られるようにしなければ」と、次にすべき作業まで見えるのです。

Googleアナリティクスからのデータの取り込み

ここからは、できあがった構成表をもとに実際の作業をやってみましょう。今回解説するのは、以下の3つです。

ページの成績表を作る
検索集客施策を考える
お問い合わせへの誘導を計画する

実際にやってみましょう。まずは成績表作りから解説します。

① ページの成績表を作る

まずGoogleアナリティクスで、「行動 > サイトコンテンツ > すべてのページ」を開きます。

最初は1位から10位までしか表示されていませんが、表の下を見ると、「1-10/425」と書かれています。この数字は、このWebサイトには全部で425ページあり、そのうち1～10ページを表示しているということを意味します。

表示するページ数はプルダウンから選ぶことができ、最大で5000行まで表示できます。この例のサイトは425ページですので、「500」を選択すれば全ページ表示できます。

しかし、1つ注意しなければならない点があります。ページが存在してもゼロページビューだと、Googleアナリティクスに記録されないので、ここには表示されません。ニュースやブログなどが膨大にあるサイトの場合、ゼロページビューもけっこう発生するので、注意が必要です。その場合、先程Excelで作成した全ページのサイト構成表があれば、どのページがアクセスゼロだったかを特定することができます。

次は、成績表を作るために、GoogleアナリティクスのデータをExcelに出力してみましょう。ページの右上部「エクスポート」というボタンがあります。これを押すと、「PDF」、「Googleスプレッドシート」、「Excel」、「CSV」を選択できます。

「Excel（XLSX）」を選択しましょう。表紙、アクセス解析、日ごとのアクセス数推移がわかるシートが出力されます。

ダウンロードしたデータを見てみましょう。「データセット1」というワークシートが全ページのデータになっているので、これを開き、データをコピーします。ここの例では「ページの価値」は設定していないため、「ページ」から「離脱率」までをコピーします。

先程Excelで作成したサイト構成表の右側にペーストしてみると、下図のようになります。F列で「表示→ウィンドウの固定」をしているので、サイト構成表はほとんど見えない状態です。

次は、O列にGoogleアナリティクスから取ったデータの「ページ別訪問数」を入れます。まず、O列の3行目にデータを取ってきた日付を入れましょう。そして、4行目に「ページ別訪問数」と見出しを入れます。そして、5-OのセルにVLOOKUP関数を書き込みます。

このVLOOKUP関数は、「Googleアナリティクスと同じ形式のURL（H列）と、Googleアナリティクスから取り込んだURL（Q列）をマッチングして、合致したS列のデータをここに書き込みなさい」という命令です。

Q列のURLを昇順に並べなおし、5-Oセルに

=VLOOKUP($H5,$Q$1:$W$425,3,FALSE)

と入力します。H5セルにあるデータを探して、Q1～W425の範囲にあるデータの左から3列目のデータをとってこい、という命令です。ややこしい「$」が入れてあるのは、このあとコピペするので、データ比較がずれないようにするためです。

これで、各ページが何人に見られたかがわかります。下図では右側の6-Oのように、「1,928人」と表示されています。あとは何ページあってもこれをコピーして、ずらっと下にペーストしていけば、完成です。

5-Oセルに「=VLOOKUP($H5,$Q$1:$W$425,3,FALSE)」と入力

また、閲覧開始数の合計はセッション数と等しいので、合計をとって各ページの閲覧開始数と割り算すれば、訪問全体に占めるページごとの集客力も計算できます。

閲覧開始数の合計＝セッション数

閲覧開始数の合計÷各ページの閲覧開始数＝ページの集客力

また、Googleアナリティクスの「セグメント機能」を使って「コンバージョンが達成されたセッション」のデータを見るようにし、その状態で「すべてのページ」を見れば、どのページが目標到達に深く関与しているかも分かります。

さらに、そのデータをExcelでダウンロードして、VLOOKUP関数を入れれば、ページ別訪問数とそのページの「コンバージョンしたセッション数」とを並べることができます。ここで割り算をすれば、各ページの目標関与率を計算することができます！

サイト構成表に、ExcelのVLOOKUP関数とGoogleアナリティクスの「セグメント」を組み合わせれば、実にパワフルなソリューションになります。

② 検索集客施策を考える

検索集客施策を考えるのは、実は①の「成績表を作る」とまったく同じです。Googleアナリティクスのセグメントで「自然検索トラフィック」を指定して「行動 > サイトコンテンツ > ランディングページ」のデータをとり、サイト構成表に入れた「閲覧開始数」の横に入れます。ランディングページのデータなら、そのページを入口にして訪れた人の目標到達回数もとれるので、以下のことが全てわかります。

どのページから閲覧開始した人が多いか
どのページから閲覧開始した人の目標到達が多いか
どのページに自然検索で訪れた人の割合が高いか
どのページに自然検索で訪れた人の目標到達率が高いか

Googleアナリティクスでは、いつも「ページビューの多い順」に並んでいます。ページビューの上位のページを見ていても、問題点を感じることができず、改善点を見つけ出すことはできません。しかし、改めて「ページの順番」にデータを並べ直して見ると、これまでとはぜんぜん違う風景が見えてきます。

たとえば、下図のページNo.12、13のデータを見て下さい。同じコーナーの1番目のページが訪問数36しかないのに、2番目のページは訪問数540もあります。「どうしてかな？」と考えると、「Googleアナリティクスでは月次でデータをとるのが大変」という問題に対して、多くのユーザーが悩んでいることがわかります。

このように、サイト構成表にデータを入れることで、訪問数の多い順に見ていたアクセス解析とは違う知見を得ることができます。