Google Analytics のデータをこんなふうにヒートマップにしてみたい
自分が投稿したQiita等の記事について、日ごとのPV数を一覧表で見れて、数値の大小に応じて色がついて見やすく表示されていると分かりやすいなあ、と思うのですが、Google Analytics の UI ではこのようなビューは提供されていないと思います。（私の記事のPV数は大したことは無いので、こういうのを作るほどの事はないのですが。。。）
ですが、Google AnalyticsはGoogle Spreadsheetと連携可能ですから、Google Spreadsheetで少し工夫すれば、こういうのが作れます。
この作成に必要な知識は、ピボットテーブル、条件付き書式くらいです。技術的に難しい部分はまったくありません。
なお一部の記事タイトルは2箇所に出現していますが、これはQiita側のページ表示の際に、なぜかページタイトルに微妙な揺らぎが生じることがあるようで、それが別々に集計されているようです。
また、この記事ではQiitaのPV数を対象に説明していますが、Google Analytics で集計されたデータならどんなサイトのデータでも同じようにヒートマップ表示できます。
必要なもの
- Google Analyticsのアカウントを作成し、Qiita記事へのアクセスを集計できるようにする
- Google Spreadsheetへの Google Analyticsアドオンの追加
- このようなヒートマップに使用するスプレッドシート
Qiitaの記事へのGoogle Analyticsの設定方法は、以下の記事で紹介されています。
https://qiita.com/Qiita/items/c7f704e3786df3aa7a11
ただしGoogle Analyticsでは自分自身のアクセスもカウントしてしまうと思います。これが問題となる場合はそれを除外する設定を行うべきです。(特定のIPアドレスを除外したり、特定のcookieを除外するなどの方法が一般的だと思います)
Google SpreadsheetへのGoogle Analyticsアドオンの追加は、ここらへんの記事が詳しいようです。
https://tonari-it.com/google-analytics-spreadsheet/
これらの準備が出来ていたら、続きをお読みください。
PV数のレポートを作成して、それをヒートマップ化する
Google Spreadsheet で Google Analytics レポートを作る
新規のレポートを作ります。
レポートでは、Metrics に Pageviews、Dimensions は Date と Page Title を設定する
生成されたレポートの End Date を現在時刻にするために "=today()" を指定する
レポートを作成する
こんな感じでレポートが生成されました。
ピボットテーブルで集計しよう
メニューからピボットテーブルを選択し、
行、列、値をこんな条件にすると、
こんな感じの集計ができあがります。
集計値でヒートマップにしたり、スパークラインをつけたり、その他のレイアウト調整をしよう
A-C列と1行目に空行を差し込みます。
A列、B列、C列、1行目に集計やスパークラインをつけます。
A列はスパークラインにします。A4セルに、こんな計算式を入れます。
=SPARKLINE(E4:AI4)
B列は直近30日分の集計を入れます。B4に、こんな計算式を入れます。
=SUM(E4:AS4)
C列は直近7日分の集計を入れることにします。C4には、こんな計算式を入れます。
=SUM(E4:K4)
ここまで入れたら、A4 - C4 にはスパークライン、30日分集計、7日分集計が出力されているはずですね。（この記事を読んで初めてGoogle Analyticsを設定した場合は、過去データがないので当日分しか表示できませんし、スパークラインは #NA 表示になっているはずです。）
そしたら、A4 - C4 をまとめて選択してクリップボードにコピーし、A5から下の行にペーストします。そうするとセルの参照設定が適切に書き換えられた計算式がペーストされているはずです。
さらに、各日付の全ページの合計PV数を1行目に入れることにします。E1セルにこんな計算式を入れます。
=SUM(E4:E1001)
そしたら、E1をコピーし、その右側の F1 - AI1 にペーストします。
これで、ページごとのPV数集計が7日分、30日分手に入り、過去30日分のスパークラインも表示されます。また、それぞれの日ごとの合計PV数も分かります。
これらの集計をピポットテーブルの集計で行っても良いのですけど、それだと集計は表の最下行や右端に出力されてしまいますので、数値を見るためにシートをスクロールせねばなりません。しかしダッシュボード的に数値を見たい場合は、スクロール無しに見たいので、そういう位置に集計値を表示したいのです。
ヒートマップをつけよう
メニューから「条件付き書式」を選びます。
カラースケールを選択し、スケールのパターンと範囲を適切に設定すると、各集計値や合計値の大小に応じてセルに色の濃淡が付いて比較が容易になります。
まずは、ピボットグリッドのページ単位、日毎の集計値に濃淡をつけます。
１行目につけた、日毎の集計値にも濃淡をつけます。
直近30日分のPV数合計値や、
直近7日分のPV数合計値にも設定しましょう。
このように、比較したい範囲毎に条件付き書式でカラースケールをつけていきます。
土日の日付は赤く表示しよう
その日付が週末なのかどうかをラクに区別したいですよね。これも条件付き書式で対応できます。=WEEKDAY(E3,2)>=6 という計算式を、範囲E3-AI3に適用するだけです。ワークシート関数WEEKDAYでは6=土曜日、7=日曜日が返されるので、戻り値が6以上の場合に色をつければよいわけです。
これに加えて以下の書式調整を行えば、さらに良い感じになると思います。
- 日付の書式をMM/ddにする
- 標準の文字色を黒、塗りつぶしは白にする
- セルの文字列を-90度回転する
見出しの行や列を固定しよう
固定しておけば、集計行や見出し行を常時表示できるで、大変便利ですね。
集計とヒートマップを１時間毎に更新しよう
Google Analyticsアドオンには Schedule reports の機能がありますので、これを設定しておけば常に最新の情報が得られます。
これで一応完成です。
応用：１時間単位の集計もヒートマップ化してみよう。
ここまでの内容をすこし応用すれば、こういう集計も作れます。
この例では、以下の作業を追加で行っています。
- レポート生成の条件やピポットテーブルの集計に Hour を追加
- ピポットテーブルの時刻表示部に、条件付き書式で昼と夜が明示的になるような条件で色をつける（夏と冬の時間差はとりあえず無視）
まとめ
誰でも知っているようなワークシート関数とピポットテーブル、条件付き書式を組み合わせるだけでこんなのが作れます。2種類のパラメータで集計可能なデータなら同様の集計ができるので、案外イロイロと応用できるかも。
私の場合は、iBeaconで位置追跡が行えるBeaconFenceのデータをプロトタイプ的に可視化する場合に、これと同じような手法を用いています。
https://qiita.com/kazinoue/items/55131d81ee75533bd1c4