本記事はGoogleAnalytics Advent Calendar 2019 ３日目の記事になります。
複数のWEBサイトを運営していて、「全サイト合算で、どれくらい集客出来ているか知りたい」といった要望を上長やクライアントから受けたことはないでしょうか？個別にデータをダウンロードして集計しても良いのですが、提出頻度が多いほど手間が掛かります。今回はGoogleスプレッドシートで利用出来るGoogleAnalyticsアドオンを使い、簡単に日毎の合算レポートを作成する手法をまとめました。
課題
GoogleAnalyticsの複数のビューに存在するページビューなどの指標を１つに合算して、日毎の集計を出したい。
概要
Google Analyticsアドオンを使い、スプレッドシートへGAの値を集約し、スプレッドシート上でレポート化します。アドオンは設定した期間で自動更新可能なため、動的に更新されるレポートを作成することが出来ます。
手順
1) GoogleAnalyticsアドオンに複数のレポート取り込みを設定する
複数のビューからデータを取得出来るように、スプレッドシートに設定を入力します。
B列以降、各列毎に設定したデータが、それぞれ別のシートに分かれて保存されます。
今回取得する内容は下記のように設定しています。
- 「昨日」から「過去62日分」の期間 (2ヶ月分)
- 取得するデータは「セッション」「ページビュー」「収益」「トランザクション数」の４項目
- ディメンションは「日付」と「デバイス」
2) 合算シートを作成する
アドオンを実行すると、各シート毎に画像のようにデータが保存されます。
次にシート毎へ分かれたデータを集約するためのシートを作成します。
QUERY関数で日付の取得
シートを作成したら、1行目にヘッダーを追加し、2行目にQUERY関数を追加します。
QUERY関数は条件に合ったデータをまとめて取得するための関数です。
先ずはA列のデータを取得するためにA2セルへ下記のように入力します。
シートの分だけQUERY('シート名'!A列のデータ範囲,"where A is not null")をセミコロンで繋ぎます。
={QUERY('シート名'!A列のデータ範囲,"where A is not null";'シート名'!A列の範囲,"where A is not null")}
これでA列の指定範囲内に値が入っているものを全て拾います。2行目以降に関数を入れる必要はありません。
同じ作業を列ごとに繰り返します。その際、Whereの「A」の部分は列ごとに変更します(B列であれば「B」、C列であれば「C」です)
3) レポート用のシートを作成する
最後にレポート用のシートを作成し、SUMIFS関数で数値をレポートにまとめます。
UNIQUE関数で日付の取得
まずはじめにUNIQUE関数で重複を除外した日付の一覧を作成します。
日付を入力したい一番上のセルへ下記のように入力します。
=UNIQUE(シート名!範囲)
こうすることで、複数のシートにある重複する日付の中から重複しない値だけが並ぶようになります。
UNIQUE関数もQUERY関数のように2行目へ関数を入力する必要はありません。
SUMIFS関数で日毎の数値を合算
最後にSUMIFS関数を使い、A列の日付をキーにして合算値を出します。
=SUMIFS(合算シートのシート名!合計したい列の範囲,合算シートのシート名!日付を含む列の範囲,同じ行にある日付のセル)
同じ作業を各列ごとに繰り返せば、レポートの完成です。
今回は割愛していますが、元データはデバイスごとにデータを分けていますので、同じレポートをPC、モバイルで分けることも可能です。
GoogleAnalyticsアドオンは指定した周期で自動的にデータを取得するため、一度作成してしまえば、あとはスプレッドシートを開くだけで最新のレポートを確認することが出来ます。