ExcelでGoogle Analyticsのデータを使いユーザーレポート(行動履歴レポート)を作ってみた

Google Analytics(ユニバーサルアナリティクス)をカスタマイズして得られたデータから,各ユーザーのサイト内の行動確認できるユーザーレポートが作れます.この記事では,そのユーザーレポートをExcelの機能を利用して作ったものを紹介します.

 

1.はじめに

Google Analytics(ユニバーサルアナリティクス)でサイトを訪れた各ユーザー(クライアントID)のサイト内の行動を確認するには,ユーザーエクスプローラにあるユーザーレポートを使います.ですが,これがけっこう扱いにくいです.その代用となるようなものをデータポータルで作ったものを紹介したのが記事「データポータルで「GAのユーザーエクスプローラより便利な(?)ユーザーレポート」を作ってみた」でした.

このデータポータルで作ったユーザーレポートの問題点があるとすれば,ユーザー数が多いと処理が重くなる(また時間帯によっても処理が重くなる)などがあります.また,各ユーザーのページビューのテーブル(Table 4-1)とリンククリックとスクロールなどのイベント計測のテーブル(Table 4-2)が分かれているので,一つのテーブルでそれらを時系列順に見ることができません.現状このようなことを解消するには,データを出力してそれらを自分のローカル(PC)などで別ツールを使ってデータを結合するしかありません.今回は,そのようなことが実はExcelで比較的簡単にできることを紹介します.

 

2.外部ツールの活用

Webサイトのアクセス解析で有名な小川卓さんのセミナーに参加したことがありますが,Google Analyticsのユーザーエクスプローラ(ユーザーレポート)の活用の話もありました.そこで参加者からの質問に対する返答で,Tableauでデータを読み込んでユーザーレポートを作ったことがある話や,データポータルでもある程度は似たものが作れるというような話がありました.

具体的にそれらがどのようなものかはわかりませんが,データポータルならば記事「データポータルで「GAのユーザーエクスプローラより便利な(?)ユーザーレポート」を作ってみた」で紹介したようなものになるだろうというのが私の考えです.一方で,私はTableauを扱ったことがないのですが,ダッシュボードやレポート作成で強力なツールなのでより理想的なものを簡単に作れると思われます.ですが,Tableauは有料ツール(SaaS)で使い続けるにはかなりお金がかかります.

有料ツールですが多くの人に使われている(PCにインストールされている)のが,MicrosoftのExcelです.Excelでレポートというと,関数を多用してとか作るのが大変とか,更新作業が面倒だとか,自動化するにもVBAが扱えないと駄目だと思うかもしれません.ですが,Excel 2016から標準搭載されたPower Queryを使ってレポートを作ってしまえば,後は同じ形式(同じファイル名にするなどした一工夫で)データファイルを用意してそれを常に読み込むようにすることでファイルを開く度に最新のデータでレポートを表示できるようになります(Excel 2010や2013にでもPower Query for Excelアドインのインストールで,Power Queryが使えるようになるとのことです).

ただしこの記事では,Power Queryの使い方などは紹介しません(ユーザーレポートを作るためのヒントになるようなことは紹介します).なぜならば,使い方を紹介したサイトや動画などはすでに多く存在していているからです.ですから興味を持った方はぜひそのようなものを検索などで探してください.

 

3.データの取得

この記事ではなにを紹介するかと言えば,ExcelのPower Queryで作ったユーザーレポートそのものです.このレポートを作るのに用いるデータは,記事「データポータルで「GAのユーザーエクスプローラより便利な(?)ユーザーレポート」を作ってみた」で紹介したデータポータルのレポートの1ページ目(目標1を達成したユーザーを集めたページ)のTable 2, 3-1, 3-2, 4-1, 4-2と新たに作るGoogle Analyticsでカスタムレポートです.

データポータルのレポートを使わずGoogle Analyticsのカスタムレポート(1つのテーブルで使えるディメンションは最大5個)だけも同様なものが作れますが,データポータルのほうが1つのテーブルで多くのディメンション(最大10個)を使えるのでエクスポート(出力)して使用するファイル数が減らせます.

データポータルのテーブルは,右上にマウスのカーソルを持って行くと「・・・」が縦に並んだものが現れて,それをクリックすることで形式を選んでエクスポートできます(図1参照).なお,データポータルで作ったレポートを公開する場合には公開条件の調整が行えます.このとき付与された条件によっては,ユーザーがデータをエクスポートできない場合もあります.記事「データポータルで「GAのユーザーエクスプローラより便利な(?)ユーザーレポート」を作ってみた」で公開しているレポートは,閲覧のみ有効としているので,申し訳ありませんがデータの出力はできません.

 

図1.データポータルのテーブルのデータをエクスポートする

 

上述しましたがデータポータルのテーブルの他に,Google Analyticsのカスタムレポートを1つ新たに作ってそのデータも使うことにしました.作ったカスタムレポートは図2のような設定です(作るレポートは目標1を達成したユーザーが対象なので,目標1を達成したユーザーのセグメントで表示させたこのレポートのデータをエクスポートすることになります).なぜこのレポートを作ったのかと言うとディメンション「直接セッション」の値を使いたかったからです(データポータルとGoogle Analyticsを接続した環境にはディメンション「直接セッション」が見当たらなかったので,データポータルのレポートで使えませんでした).ディメンション「直接セッション」の説明は,記事「Google Analyticsの最近の注目すべき変化・その1」を参照してください.

 

図2.新たに作ったカスタムレポートの設定

 

私のローカルはWindows環境なので,データポータルからはWindows用のcsvファイル,Google AnalyticsからはExcel形式のファイルをエクスポートしました.これらファイルを,名前をわかりやすい簡単な名前に変え,ハードディスクに専用フォルダを作り,そこに格納します.ExcelのPower Queryでこれらデータを読み込んでレポートを作成します.次に新たなデータでレポートを作る場合は,新たにデータポータルとGoogle Analyticsからエクスポートしたファイルを専用フォルダ内の同じ内容のファイルと同じ名前に変えて,専用フォルダ内に移動させます(上書き保存させたくない場合は,前のファイルは別の場所に移動させてください).その後,作ったExcelのユーザーレポートのファイルを開く(ファイルを開く度にファイルを読み込んで更新する設定をしておく)ことで,最新のデータでレポートが表示されるようになります.

 

4Excelで作ったユーザーレポート

Excelで作ったユーザーレポートは, Excelで作ったGoogle Analyticsのユーザーレポート からダウンロードできます(このファイルはデータ接続を解除しているので,各シートのテーブルは単なるテキストや数値になっています).

比較対象となるデータポータルのユーザーレポートは以下のリンクから閲覧できます.

[閲覧用] GAのユーザーエクスプローラより便利なユーザーレポート

別記事ですでに説明していますが,データの期間は「2021年4月12日から2021年4月18日」をデフォルトにしています.このレポートの説明は,記事「データポータルで「GAのユーザーエクスプローラより便利な(?)ユーザーレポート」を作ってみた」を見てください.

Excelで作ったユーザーレポートのシート「Table2」は,データポータルのユーザーレポートの「クライアントID」が主軸のTable 2と同じです(図3参照).つまり,シート「Table2」のテーブルは「クライアントID」が主軸のレポートで,データ期間に目標1を達成したユーザー(クライアントID)の基本データが集められています.

 

図3.Excelで作ったユーザーレポートのシート「Table 2」の一部

 

シート「Table3+alpha」は, データポータルのTable 3-1にTable 2とTable 3-2と作成したGoogle Analyticsのカスタムレポートを結合したものです(図4参照).つまり,シート「Table3+alpha」のテーブルは「クライアントID&セッションID」が主軸のレポートで,データ期間に目標1を達成したことがあるユーザー(クライアントID)の各セッションの基本データが集められています(データ期間内に目標1を達成したことがあるユーザーなので,目標1を達成していないその期間内のセッションも含まれ,アクセス時間の昇順に並んでいます)

 

図4.Excelで作ったユーザーレポートのシート「Table3+alpha」の一部

 

シート「Table4+alpha」は, データポータルのTable 4-1にTable 2とTable 3-1とTable 4-2と追加作成したGoogle Analyticsのカスタムレポートを結合・追加したものです(図5参照).つまり,シート「Table4+alpha」のテーブルは「クライアントID&セッションID&アクセス時間」が主軸のレポートで,データ期間に目標1を達成したことがあるユーザー(クライアントID)のページビューとイベント計測の一覧となっています(データ期間内に目標1を達成したことがあるユーザーなので,期間内のそのユーザーの全てのセッションでのページビューとイベント計測の情報がアクセス時間の昇順に並んでいます).

 

図5.Excelで作ったユーザーレポートのシート「Table4+alpha」の一部

 

ExcelのユーザーレポートではExcelの機能(条件付き書式)を使い,目標1を達成した箇所に該当するシート「Table3+alpha」のテーブルのセッションIDのセルとシート「Table4+alpha」のテーブルのアクセス時間(JST)のセル内のフォントを赤色に変化させています.

データポータルのレポートでは,レポート上部にあるコントロールボタンを用いて(時間やデバイスなどで)ユーザーを絞り込むことができました.Excelではそのようなコントロールボタンがないので,デバイス別で絞り込めるようにするにはデバイスの列をテーブルに用意する必要があります(列のフィルタ機能を使って絞り込めるようにします).例えば,Excelのシート「Table3+alpha」や「Table4+alpha」のテーブルには「アクセス時間(JST)」が元のデータ同様に年月日時間がそれもそのまま記載されています.ですがこの「アクセス時間(JST)」の記載内容では,ある特定の日や曜日に絞ってデータを見たいと思っても不便です(できなくはないですが).そこでこのアクセス時間のデータを元に「年,月,日,曜日,時間」の列をそれぞれ作成しました(図6参照).このような新しい列の作成もPower Queryでは簡単に行えます(このような場合,エクセルではセルの書式設定などを通常用いると思いますが,Power Queryだけで処理が完結できます).

 

図6.アクセス時間のデータを元に作成した「年,月,日,曜日,時間」の列

 

データポータルのTable 4-2は各ユーザー(クライアントID)のイベントのデータを集めたものでした.そのテーブルには「イベントカテゴリ」という列があり,外部リンクをクリックした場合に「OutboundLink_Click」,内部リンクをクリックした場合に「InternalLink_Click」,そしてページの縦方向の移動を「ページスクロール」という情報が保存されています.Excelのシート「Table4+alpha」のテーブルでそれら情報があるのは「アクション」の列になります.そしてその「アクション」の列には「ページビュー」というものがあるのが確認できると思います(図7参照).この「ページビュー」とは,データポータルのTable 4-1のデータに該当するデータとなっています.ExcelのPower Queryを使った結合・追加などの作業にて,Table 4-1のデータに新たに「アクション」という列を追加し,その列すべのセルに「ページビュー」を与えました.一方で,Table 4-2のデータの「イベントカテゴリ」という列名を「アクション」に変えました.こうすることでクエリの結合・追加によって,Excelのシート「Table4+alpha」の各行がどのような行動のデータに該当するかをわかりやすくする列「アクション」を作りました

なお,リンククリックやスクロール率の情報(イベントラベル)の内容は,記事「Google Analytics(ユニバーサルアナリティクス)で個々のユーザーのサイト内の行動を追う」を参照してください.

 

図7.シート「Table4+alpha」のテーブルの列「アクション」

 

5.おわりに

ExcelのPower Queryを使った結合などでの注意としては,セッションIDなどを結合のキーで選ぶ場合に,セッションの途中で変化するような情報を使わないなどがあります.例えば,ユーザーがPCの環境で閲覧していた場合,その閲覧しているブラウザのサイズをGoogle Analyticsの同一セッションで変化させることはありえます.したがって,このようなユーザーレポートでは,Google Analyticsのディメンション「ブラウザのサイズ」などを使うのは避けるべきです.

また,このレポートで使用しているJavaScriptで作ったセッションIDは,Google Analyticsのセッションの設定と連動しているわけではありません.そのためGoogle Analyticsでセッションが切り替わったと判断されても,セッションIDの値は変化しないなどもあります.

これで紹介を終わりにしますが,Excelでユーザーレポートをどう作るかのイメージのヒントがつかめたでしょうか?