らくがきスマートフォン用の表示で見るらくがき「のほほん」ってローマ字で書いたら、後ろから読んでも「のほほん」になることに気付いた時の感動といったら。この広告は、90日以上更新していないブログに表示しています。2016-09-27グーグルアナリティクスとエクセルを使って、ページ毎のアクセス数が比較できる一覧表の作り方こんにちは、とみたです。9月もまた勉強会やワークショップに参加する機会が多く、あっという間に月末です(泣)特に今月は、法人化前に経費を使おうとパソコンとか椅子の新調でショールームに出かけたりしていたので、あっという間に過ぎてしまいました。先月、とあるセミナーに参加した時に、「記事の修正はどのタイミングですればいいのですか?」という質問が出ていました。僕の考えだとその答えは1つではなくて、何のための修正かによると思います。以前紹介したこちらのデータ集計の方法(別タブ)の、特に「6.ページ毎分析」は、今あるアクセスをより多くしたり、今よりもっと成約や別記事へと誘導するための修正をするために、ページ毎に細かなデータを取っています。これはある程度アクセス数があるからこそ、『その流れを整える修正』が活きてくるのであって、アクセス数が少ない段階では、改善しても影響が小さく、効率がよくありません。ある程度狙ったキーワード、もしくは他ページから流入が出てきてからが修正のタイミングと考えています。アクセスが集まっていない段階でもやることはあります。狙っているキーワードの順位が長い間横ばいのままだったり、月単位で見て下がってくるようであれば、それは修正のタイミングと考えます。僕の場合は、1記事1記事狙うキーワードを決めて、その検索意図を考えて記事にするので、同じ内容で別記事を書くわけにもいきません。その記事が狙っているキーワードで上がってこないか、1度上がったものが落ちてくるようであれば、内容に手を加えるようにしています。それは言ってしまえば、GRCで各キーワードの順位を見ていれば分かる話ですが、他にも次のような目的で、記事ごとの検索流入数の一覧をまとめているのが、今回紹介する集計データです。・検索流入が増えない記事を把握するため・成約のための修正の優先順位を決めるため・メインワードの順位が変わらず、流入が増減している記事を把握するためなお、これは以前に「2.人気ページと不人気ページ」という内容で紹介したものを改良して、先月と比較できるようにしたもので、シンプルながら得られる情報は多いと思います。エクセルは最新の2016を使用して解説しており、特に2007より前のバージョンでは操作が違う可能性がありますが、ほとんど基本機能だけを使う方法なので、機能的には幅広く対応できるはずです。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スプレッドシートを選びます。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番上の「セルの値に基づいてすべてのセルを書式設定」を選択し、次の開設や画像を参考に設定してください。数値これは先月よりも5%以上流入が増えていたら上矢印を、5%以上流入が減っていたら下矢印を、その間なら横矢印を表示させるように設定しています。この値は自由に調整していただいてもかまいません。設定ができたらOKをクリックします。すると、次のようにアイコンが表示され、流入が増えている記事、減っている記事が一目で分かります。あとは見やすいように整えるだけです。1行目の見出しに名前を付けましょう。これは自由につけて頂いて構いませんが、例として次のように付けました。続けて、このままだと行の幅が一定で、データを表示しきれていない列があるので、それぞれの幅に合わせて調整します。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つです。今回は少々やぼった方法でしたが、難しい数式もほとんど使っていないので、やっていること自体はかんたんです。分かる人ならマクロ組んで自動処理してもいいでしょうし、スタッフさんに任せるのもいいかも。本来の目的である、修正や改善の時間を圧迫し過ぎない程度に、できる範囲で取り入れてみてください。08:40グーグルアナリティクスとエクセルを使って、ページ毎のアクセス数が比較できる一覧表の作り方Tweet広告を非表示にする関連記事2017-06-26サーチコンソールから考える3つの改善ポイントこんにちは、やらしいの人です。ブログの更新はしばらくぶりで...2016-07-08自分なりに思う、ホワイトハットで上手くいった3つの施策こんにちは、とみたです。あっという間に今年も半年が過ぎ、下...2016-06-16データ分析の考え方をちょっとばかり。こんにちは、とみたです。先日ねーやんにお誘い頂き、某勉強会...2016-05-25Googleアナリティクスやサーチコンソールを使ったサイト分析方法こんにちは、とみたです。...2016-05-02外注記事によるアフィリエイトサイトの年間収支こんにちは、とみたです。»と・みた大学受験の年にアフィリエイトを知り、かれこれ10年以上...そこから長いブランクを経たのちに去年の2月からまたアフィリにも挑戦中。今年4月に大台に乗りました。最新記事サーチコンソールから考える3つの改善ポイントデスクワークが多いひとり社長の作業環境(デスク・椅子・パソコンなど)運営開始から20か月、アフィリエイトで法人化に至るまでの軌跡グーグルアナリティクスとエクセルを使って、ページ毎のアクセス数が比較できる一覧表の作り方アフィリエイトの出口戦略?サイト売買の話。月別アーカイブはてなブログをはじめよう!sptomyさんは、はてなブログを使っています。ブログを報告するスターをつけました引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。