グーグルアナリティクスとエクセルを使って、ページ毎のアクセス数が比較できる一覧表の作り方

こんにちは、とみたです。

9月もまた勉強会やワークショップに参加する機会が多く、あっという間に月末です（泣）
特に今月は、法人化前に経費を使おうとパソコンとか椅子の新調でショールームに出かけたりしていたので、あっという間に過ぎてしまいました。

そのレビューなんかもしたいんですが、最近分析関係のことも書いていないので、なんとか月一更新を続けるべく、かんたんな比較表の作り方を紹介します！

今回の比較表を作る目的

先月、とあるセミナーに参加した時に、「記事の修正はどのタイミングですればいいのですか？」という質問が出ていました。
僕の考えだとその答えは1つではなくて、何のための修正かによると思います。

以前紹介したこちらのデータ集計の方法（別タブ）の、特に「６．ページ毎分析」は、今あるアクセスをより多くしたり、今よりもっと成約や別記事へと誘導するための修正をするために、ページ毎に細かなデータを取っています。

これはある程度アクセス数があるからこそ、『その流れを整える修正』が活きてくるのであって、アクセス数が少ない段階では、改善しても影響が小さく、効率がよくありません。
ですから、ある程度狙ったキーワード、もしくは他ページから流入が出てきてからが修正のタイミングと考えています。

一方、アクセスが集まっていない段階でもやることはあります。

狙っているキーワードの順位が長い間横ばいのままだったり、月単位で見て下がってくるようであれば、それは修正のタイミングと考えます。
特に僕の場合は、1記事1記事狙うキーワードを決めて、その検索意図を考えて記事にするので、同じ内容で別記事を書くわけにもいきません。

なので、その記事が狙っているキーワードで上がってこないか、1度上がったものが落ちてくるようであれば、内容に手を加えるようにしています。

それは言ってしまえば、GRCで各キーワードの順位を見ていれば分かる話ですが、他にも次のような目的で、記事ごとの検索流入数の一覧をまとめているのが、今回紹介する集計データです。

・検索流入が増えない記事を把握するため
・成約のための修正の優先順位を決めるため
・メインワードの順位が変わらず、流入が増減している記事を把握するため

なお、これは以前に「２．人気ページと不人気ページ」という内容で紹介したものを改良して、先月と比較できるようにしたもので、シンプルながら得られる情報は多いと思います。

また、エクセルは最新の2016を使用して解説しており、特に2007より前のバージョンでは操作が違う可能性がありますが、ほとんど基本機能だけを使う方法なので、機能的には幅広く対応できるはずです。

はじめにカスタムレポートの準備

今回見るオーガニック検索による流入数は、グーグルアナリティクスの集客＞サマリー＞Organic Search＞ランディングページと進むと見ることができます。

これでも数字は同じですが、見たいデータが決まっていて、なおかつ毎月など頻繁に見るのであれば、カスタムレポートを作っておくと便利です。

以下その方法を紹介しますが、面倒であればアカウントにログインの上、こちらのURLにアクセスすれば、僕が使っているカスタムレポートをインポートできますので、よかったらどうぞ。

https://analytics.google.com/analytics/web/template?uid=ZyP4yS2OR1SaaKXdcuJyFg

まずはグーグルアナリティクスのどの画面からでもいいので、上部の「カスタム」をクリックし、カスタムレポートのサマリーを開きます。

次に画面上部の「+新しいカスタムレポート」をクリックします。

カスタムレポートの作成画面が開いたら、次のように入力＆選択してください。

タイトル：
オーガニック検索セッション

指標グループ：
セッション
直帰率
平均セッション時間

ディメンションの詳細：
ランディングページ

選択の際はたくさんの候補が出てきますが、それぞれ検索窓がついているので、そちらで絞り込むのが1番探しやすいです。

最後に「保存」をクリックすると、今作ったカスタムレポートが開きます。

ただ、これだとオーガニック検索以外のセッション数も含まれているので、セグメントを使います。

表の上、右側にある「+セグメント」をクリックし、セグメントの設定画面を開きます。

最初についている「すべてのユーザー」のチェックを外します。

続けて、その下にある「検索トラフィック」のチェックを入れます。

その後「適用」をクリックすると、検索トラフィック＝オーガニック検索によるセッションによる数字だけが表示されます。

これにより、例えばサイト公開やバズって一時的にSNSからの流入が増えた場合も、その増減に惑わされずに済みます。

設定自体はこれで終わりで、最初に紹介したリンクからインポートした場合も、ここまでの設定はできています。
ただ、表示件数がデフォルトの10件ではさすがに少ないので、右下の「表示する行数」のセレクトボックスをクリックし、表示件数を増やします。

どの程度がよいかはサイトの規模によりますが、ここでは250件にしました。
するとセッション数が少ないランディングページに、動的なURLが含まれていることが分かります。

含まれていなければ次の設定は不要ですが、含まれている場合は後々邪魔になるので、できる限り除外しておきます。

例えばうちのサイトであれば、「?」「=」が含まれていることから、これらを利用すればよさそうです。

まず、表の右上にある虫眼鏡マークの横、「アドバンス」をクリックします。

すると詳細検索の画面が開くので、左端のセレクトボックスは「除外」を選択し、右端のテキストボックスに「?」（※半角）と入力します。

さらに「ディメンションと指標を追加」をクリックし、ディメンション＞ランディングページを選びます。

検索の設定が1行追加されるので、同じく「除外」を選び、「=」（※半角）と入力します。

できたら「適用」をクリックします。
これで表示件数が減り、ある程度必要なデータだけを抽出できました。

このように不要なデータの共通点を考え、それを除外するのがコツです。

なお、さきほどのセグメントやこの除外検索は、カスタムレポートには反映されていません。
（カスタムレポートの設定でフィルタをかけていれば、除外の方は維持できます）

そこで、ショートカットに登録しておくと、毎回設定しなおす必要がなく、便利です。

画面の上部に「ショートカット」というボタンがあるので、それをクリックします。

するとポップアップが開くので、分かりやすい名前を入力します。
ここではカスタムレポートと同じ、「オーガニック検索セッション」にしました。

「OK」をクリックするとショートカットに追加されるので、次からはサイドバーのショートカット＞オーガニックセッション数をクリックすると、このページを開くことができます。

データを取得する

ではここから、データを取得していきます。

まず、さきほどのショートカットからカスタムレポートを開き、右上の期間が表示されているボックスをクリックします。

そして、今回は先月である8月と先々月7月を比較するため、まずは直近8月1日～8月31日を選択します。

続けてその下にある「比較」のチェックボックスをクリックし、7月1日～7月31日を選択します。
※今回はたまたま比較する前後の月が同じ31日間でしたが、そうでない場合は日数が違っても、ひと月で見ています。

そして適用をクリックすると、選択した期間のデータが表示されます。

ではこれを、一旦Googleスプレッドシートにエクスポートします。
画面上部にあるエクスポート＞Googleスプレッドシートを選びます。

Import documentという英語の画面が開いたら、「import the data」をクリックし、ブラウザ上にスプレッドシートが開きます。

不要なデータも含まれているので、A～F列を7行目から必要な分だけを選択します。
合計値は必要ないので、ここでは140ページ分、287行目までを選択しました。

そしてCtrl+cか右クリック＞コピーでコピーします。

ここでエクセルを開き、A1セルを選択したら、右クリックか上部メニューより、「形式を選択して貼り付け」をクリックします。

貼り付ける形式は「テキスト」を選び、OKをクリックします。

これで必要なデータを取り出すことができました。

わざわざスプレッドシートに出力してからコピーしたのは、直接エクセルシートに出力すると、セッション時間の数字が上手く表示されないからで、このデータが必要ないのであれば、CSV形式などでエクスポートした方が早いです。

データを整える

ここで貼り付けしたデータを見てみると、先月と先々月のデータが縦に並び、少し見づらくなっています。
そこで左側に先々月のデータを、右側に今月のデータを置くことで、前後の比較がしやすいよう整形していきます。

丁寧に解説していきますが、少し難しいかもしれません。
その場合は機械的にやって頂いても構わないので、とりあえず完成まで進めてみてください。

最初に、各項目の数字を横に並べるために、列を用意します。
「F」と書かれたところを左クリックしてF列を選択します。

そのままF列の上で右クリックし、「挿入」をクリックします。

すると先ほどF列だった列がG列に移り、新たなF列が挿入されます。

同じ要領で、E列を選択して挿入、D列を選択して挿入し、次のような形にします。

次に新しく挿入した行に、先々月、今回であれば7月のデータを表示します。
方法はいくつかありますが、今回は基本的な数式を使います。

まずはD2セルを左クリックで選択します。
この時、キーボードの「半角/全角」のキーを押し、半角入力にしておきましょう。

そしてそのまま選択した状態で「=」と入力し、続けてE3セルをクリックします。
次のような表示になれば合っています。

そのままEnterキーを押すと、E3セルのデータがD2セルに表示されます。

ここから少しややこしいので、注意しながら進めてください。

先ほどの数式入力で、D2には7月のデータが、E2に8月のデータが表示されています。
つまり、偶数行にだけ横並びのデータが表示されているので、これを1番下の行までコピーしていきましょう。

そのためにまず、D2セルをクリックし、そのままドラッグして、D2とD3セルを選択します。

次にオートフィルという機能を使って下まで数式をコピーしていくのですが、選択した2つのセルの右下に小さな四角があり、そこにカーソルを合わせると、白抜きの十字から黒い十字に変わるところがあるので、探してみてください。

見つけたら左クリックして、最終行までそのままドラッグします。
ここでは281行目までドラッグしました。

そこで左クリックを離すと各行に数式がコピーされます。
1行ごと、偶数行に数字が表示されていたらOKです。

ここで後の手順のために、データを変換しておきます。
現状ではD列のデータは、E列のデータを数式で表示しているだけなので、E列のデータが消えると、一緒に消えてしまいます。

そこで、オートフィル後のD列が選択された状態で、D列のどの行の上でもいいので、右クリック＞コピーをクリックします。

そして1番上までスクロールして、D2セル上で左クリックして選択し、右クリックしたら、貼り付けオプションの「値」を選択します。
バージョンによって貼り付けオプションという項目がない場合は、「形式を選択して貼り付け」から、「値」を選んでも同じことができます。
※また最新のバージョンでは、そのまま同じD列上で右クリック＞値を選択して貼り付けでも構いません。

すると、数式だったD列が数値に変わり、E列を消してもそのまま残るようになります。

続けて、F列にもG列の内容をコピーするため、F2セルに「=G3」と入力しましょう。

もしも画像のようにG3と違う数字が表示された場合は、単位がパーセントになっていないことが原因です。
F2セル上で右クリックして「セルの書式設定」をクリックするか、メニューバーのホームタブの書式＞セルの書式設定をクリックします。

書式設定のボックスが表示されるので、図のように分類を「パーセンテージ」に、小数点以下の桁数は「2」を選択し、OKをクリックします。

これで同じ表示になりました。

あとは先ほどと同じ要領で、F2とF3セルを選択して、最終行までオートフィルでコピーします。

そして、数式が入力されているF列をコピーして、同じセルに値として貼り付けます。

H列も同じで、H2セルに数式でI3のデータを表示し、H2とH3を選択して、下までコピーします。
そして数式のデータをコピーして、値として貼り付けましょう。

これですべての先月（8月）と先々月（7月）のデータが横並びになったので、不要な列と行を削除していきます。

まずはA列と1列の左上にある、何も書かれていないところを左クリックして、すべてのセルを選択してください。

それから、メニューバーのデータタブにあるフィルターをクリックします。

すると、1行目の各セルに下矢印のマークが現れるので、D1セルの下矢印をクリックし、フィルター設定のボックスを表示します。

そして、まずは1番上の「（すべてを選択）」のチェックボックスを外したら、1番下までスクロールし、「（空白セル）」のチェックボックスだけを選択して、OKをクリックします。

これで、D列に何も入力されていない（＝削除したい行）だけが表示されます。

あとは左端の「3」書かれたマスを左クリックし、そのまま最終行（ここでは281行目）までドラッグし、複数行を一括選択してください。

できたら選択した行の上で右クリックして、「行の削除」を選択します。

これで不要な行が削除できました。
ここでもう1度、D1セルの”ろうと”のボタン（元下矢印）をクリックしてください。

さきほどのフィルター設定画面が現れるので、「（すべて選択）」にチェックを入れ、OKをクリックします。

これで行の削除が終わったので、続けて不要な列も削除します。
B列とC列は必要ないので、まずはその2つの列を選択するため、B列を右クリックした後、そのままD列までドラッグします。

選択した列の上で右クリックし、「削除」をクリックして、2つの行を削除します。

これで必要なデータだけを表示することができました。

見やすく整形する

最後に見やすくなるように調整し、ちょっと工夫を加えます。

まずはC列の横に上昇か下落が分かりやすいようにアイコンを付けます。
（もしかすると、古いバージョンではこの機能がついていないかもしれませんが、エクセル2007までは確認済みです。）

「D」と書かれたところをクリックして列を選択したら、右クリックで「列の挿入」を選択します。

D2セルを選択し、次のように入力してください。
そのままコピーして貼り付けても問題ありません。

=IF(B2<>0,C2/B2,10)

一応説明しておくと、先々月（7月）のセッション数に対する先月（8月）のセッション数の割合を計算しています。
その際、先々月のセッション数が0の場合もあるので、その時は仮の値として「10」と表示するようにしています。

画像のように小数点が表示されていない場合は、ホームタブの「小数点以下の表示桁数を増やす」というボタンを2回クリックして、小数点第二位まで表示させておきましょう。

あとは同じ数式を各行にも適応するため、先ほどやったオートフィルを使って、数式をコピーします。
D2セルを選択した状態で、セル右下のカーソルが黒い十字になるところに合わせ、クリックしたまま最終行まで下にドラッグします。

数式をコピーできたら、D列を選択したままの状態で、ホームタブの条件付き書式＞アイコンセット＞その他のルールをクリックします。

「新しい書式ルール」というボックスが表示されたら、1番上の「セルの値に基づいてすべてのセルを書式設定」を選択し、次の開設や画像を参考に設定してください。

書式スタイル：アイコンセット
アイコンスタイル：↓→↑の矢印
アイコンのみ表示：チェック
↑の値と種類：>= / 1.05 / 数値
→の値と種類：>= / 0.95 / 数値

これは先月よりも5%以上流入が増えていたら上矢印を、5%以上流入が減っていたら下矢印を、その間なら横矢印を表示させるように設定しています。
この値は自由に調整していただいてもかまいません。

設定ができたらOKをクリックします。
すると、次のようにアイコンが表示され、流入が増えている記事、減っている記事が一目で分かります。

あとは見やすいように整えるだけです。

まずは1行目の見出しに名前を付けましょう。
これは自由につけて頂いて構いませんが、例として次のように付けました。

A1セル：ランディングページ（そのまま）
B1セル：7月セッション
C1セル：8月セッション
D1セル：増減
E1セル：7月直帰率
F1セル：8月直帰率
G1セル；7月滞在時間
H1セル：8月滞在時間

続けて、このままだと行の幅が一定で、データを表示しきれていない列があるので、それぞれの幅に合わせて調整します。

まずはAと書かれた列を左クリックし、H列までドラッグしてください。

そして、列と列との間にカーソルを合わせると、左右の矢印に変わるところがあるので、そこでダブルクリックします。

すると、各列に入力されている文字の最大幅に合わせて、列幅が自動的に調整されます。

あとは好みに応じて罫線を付けたり、見出しに色付けや中央揃えにしてもいいでしょう。

・罫線
メニューバーの「ホーム」より、罫線のボタンから選択できます。
特にこだわりがなければ、「格子」で升目の罫線が引けます。

・中央揃え
同じくメニューバーの「ホーム」より、「中央揃え」のボタンをクリックすると変更できます。
※見出しを中央揃えにする場合は必要な列幅が変わるので、もう1度自動調整してください

・色付け
メニューの塗りつぶしボタンをクリックすると、選択範囲が塗りつぶされます。

使い方の例

例えばフィルター機能を使って、アクセスが落ちているものだけを表示することができます。

「増減」と付けたD1セルの下三角をクリックし、色フィルター＞↓（下矢印）を選ぶだけです。

これで7月よりも検索流入が減っているページだけが表示され、これが続くようであれば、記事修正の対象になります。

同じ操作をすればもとに戻れるので、その上で今度は並び替えをしてみます。

「8月セッション」と付けているC1セルの下三角をクリックし、「昇順」をクリックします。

これにより、8月の流入数が少ない順に並び替えができました。

ここではすでに削除したページや、新しく追加したページ、カテゴリーページなどが上に並んでいるので問題視はしませんが、長い期間流入がなければ、これも修正や削除の候補になってきます。

他にも今回は7月と8月という2か月間で比較しましたが、2015年と2016年の8月どうしを比較するのもアリです。
また、検索流入・直帰率・滞在時間以外にも、検索以外の流入数、もしくは検索以外を含む全体のPVを見てみても、また違う発見ができると思います。

最後に

僕は普段からデータをエクセルに落として見るようにしています。

そうすることで、印刷したデータと画面を見比べながら、その数字が意味するところを探ることができたり、増減のアイコン表示、フィルターや並び替え機能が使えるなど、何かと便利です。
その上、もしもデータをさかのぼって見たくなったときも、毎月同じ形で残しておくことで、かんたんに呼び出してくることができます。

ただ、僕の場合は1つのサイトに集中して運営していることもあり、1か月ごとの集計でも変化が見られますが、アクセスが少ない場合はそうもいきません。

その場合は、1～3月と4～6月を比較するなど、期間を長めに取って比較してもいいと思います。
特にある程度安定期に入っていたり、運営サイトが多い場合は、1・4・7・10月はサイトA、2・5・8・11月はサイトBなど、決まったサイクルで見るのも1つです。

今回は少々やぼった方法でしたが、難しい数式もほとんど使っていないので、やっていること自体はかんたんです。
分かる人ならマクロ組んで自動処理してもいいでしょうし、スタッフさんに任せるのもいいかも。

本来の目的である、修正や改善の時間を圧迫し過ぎない程度に、できる範囲で取り入れてみてください。

それではまた！