読者です 読者をやめる 読者になる 読者になる

らくがき

「のほほん」ってローマ字で書いたら、後ろから読んでも「のほほん」になることに気付いた時の感動といったら。

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

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

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

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

f:id:sptomy:20160926150747p:plain


今回の比較表を作る目的

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

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

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


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

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

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

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

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


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

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


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

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

f:id:sptomy:20160926092223p:plain

 

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

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

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


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

f:id:sptomy:20160926092303p:plain

 

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

f:id:sptomy:20160926092323p:plain

 

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

f:id:sptomy:20160926092332p:plain

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

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

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

 

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

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

f:id:sptomy:20160926092438p:plain

 

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

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

f:id:sptomy:20160926092605p:plain

 

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

f:id:sptomy:20160926092617p:plain

 

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

f:id:sptomy:20160926092627p:plain

 

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

f:id:sptomy:20160926092636p:plain

 

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


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

f:id:sptomy:20160926092700p:plain

 

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

f:id:sptomy:20160926092714p:plain

 

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

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


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

f:id:sptomy:20160926092730p:plain

 

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

f:id:sptomy:20160926092740p:plain

 

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

f:id:sptomy:20160926092758p:plain

 

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

f:id:sptomy:20160926092808p:plain

 

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

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


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

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

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

f:id:sptomy:20160926092828p:plain

 

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

f:id:sptomy:20160926092840p:plain

 

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

f:id:sptomy:20160926092850p:plain

 


データを取得する

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


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

f:id:sptomy:20160926092917p:plain

 

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

f:id:sptomy:20160926092958p:plain

 

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

f:id:sptomy:20160926093014p:plain

 

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

f:id:sptomy:20160926093028p:plain


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

f:id:sptomy:20160926093135p:plain

 

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

f:id:sptomy:20160926093146p:plain

 

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

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

f:id:sptomy:20160926093211p:plain

 

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

f:id:sptomy:20160926093232p:plain

 

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

f:id:sptomy:20160926093240p:plain

 

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

f:id:sptomy:20160926093248p:plain

 

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


データを整える

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

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


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

f:id:sptomy:20160926093326p:plain

 

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

f:id:sptomy:20160926093348p:plain

 

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

f:id:sptomy:20160926093402p:plain

 

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

f:id:sptomy:20160926093412p:plain

 

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

 

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

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

f:id:sptomy:20160926093456p:plain

 

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

f:id:sptomy:20160926093512p:plain

 

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

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

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

f:id:sptomy:20160926093531p:plain

 

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

f:id:sptomy:20160926093638p:plain

 

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

f:id:sptomy:20160926093655p:plain

 

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

f:id:sptomy:20160926093708p:plain

 

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

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

f:id:sptomy:20160926093734p:plain

 

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

f:id:sptomy:20160926093829p:plain

 

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

f:id:sptomy:20160926093852p:plain

 

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

f:id:sptomy:20160926093904p:plain

 

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

f:id:sptomy:20160926093951p:plain

 

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

f:id:sptomy:20160926094006p:plain

 

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

f:id:sptomy:20160926094014p:plain

 

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

f:id:sptomy:20160926094025p:plain

 

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

f:id:sptomy:20160926094042p:plain

 

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

f:id:sptomy:20160926094058p:plain

 


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

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

f:id:sptomy:20160926094127p:plain

 

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

f:id:sptomy:20160926094218p:plain

 

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

f:id:sptomy:20160926094232p:plain

 

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

f:id:sptomy:20160926094315p:plain

 

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

f:id:sptomy:20160926094335p:plain

 

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

f:id:sptomy:20160926094350p:plain

 

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

f:id:sptomy:20160926094406p:plain

 

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

f:id:sptomy:20160926094438p:plain

 

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

f:id:sptomy:20160926094500p:plain

 

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

f:id:sptomy:20160926094517p:plain

 

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

f:id:sptomy:20160926094529p:plain

 

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

f:id:sptomy:20160926094540p:plain


見やすく整形する

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

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

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

f:id:sptomy:20160926094625p:plain

 

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

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

 

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

f:id:sptomy:20160926094810p:plain

 

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

f:id:sptomy:20160926094822p:plain

 

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

f:id:sptomy:20160926094833p:plain

 

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

f:id:sptomy:20160926094843p:plain

 

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

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

 

f:id:sptomy:20160926094935p:plain

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

 

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

f:id:sptomy:20160926095010p:plain

 

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

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

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

f:id:sptomy:20160926095043p:plain

 

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

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

f:id:sptomy:20160926095106p:plain

 

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

f:id:sptomy:20160926095119p:plain

 

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

f:id:sptomy:20160926095125p:plain

 

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

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

f:id:sptomy:20160926095145p:plain

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

f:id:sptomy:20160926095219p:plain

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

f:id:sptomy:20160926095257p:plain


使い方の例

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

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

f:id:sptomy:20160926095330p:plain

 

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

f:id:sptomy:20160926095341p:plain


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

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

f:id:sptomy:20160926095401p:plain

 

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

f:id:sptomy:20160926095413p:plain

 

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


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


最後に

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

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


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

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

 

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

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

それではまた!