グロースエンジニアのブログ

Ruby on Rails エンジニアです!開発に当たって勉強したことをまとめていこうと思います!

【Googleアナリティクス API】取得したデータを月別に集計する

前回はデータ取得までやったので、これを日別に取得し、月別データでまとめるところまでやっていきます。

レポート作成は月ごとが多いと思うので今回はそれに合わせて作成しますが、週ごとなど報告形式に合わせて調整していただければと思います。

※ 今回の内容は以下にまとめてあります。

160517_ブログ_GAdata - Google スプレッドシート

 日別にデータを取得する

前回は直近7日間のデータを取得しました。
これは以下の図の青枠で指定しています。この数字は削除してください。 

次に赤枠内に取得したい期間の日付を指定してください。

以下では、このブログのデータ取得を始めたのが11月からなので【Strat Date】にそのように記入していますが、それぞれに合わせて記入してください。

【End Date】はいつまでのデータを取得するかという意味ですが、未来の日付も指定できます。今期のデータをまとめるという意味で「2017/03/31」を記入しています。

 

f:id:ikdtty0423:20160515120019p:plain

 

これで取得するデータの日程を設定できたので、メニュー【アドオン】→【Google Analytics】→【Run reports】とクリックしデータを取得しましょう。

 

f:id:ikdtty0423:20160424100950p:plain

 

すると指定した期間でのデータが取得できています。

f:id:ikdtty0423:20160515121330p:plain

月別にデータを集計する

次は日別のデータを月別でまとめていきます。

集計のための下準備

以下の図の【ga:date】の列を最後まで選択します。

選択した状態で【表示形式】→【数字】→【書式なしテキスト】を選択します。これはエクセルを使い慣れている人なら経験があると思いますがデータを検索するときに必要な処理となります。詳細は長くなるので興味がある方は調べてみてください。

 

f:id:ikdtty0423:20160515122007p:plain

 

表の作成

画面左下にある【+】ボタンから新しいシートを追加します。

f:id:ikdtty0423:20160515122457p:plain

今回は基本的な指標ということで、以下のようにデータをまとめることにしました。
※ 見た目は好みなので適宜修正してください。

f:id:ikdtty0423:20160515123113p:plain

B,C列には【年】と【月】を入れています。また、先ほど取得した指標をD列〜I列(緑)に入れるように表を作成し、J列〜L列(オレンジ)はデータ計算して率や平均時間を出すようにします。

月別データの集計

【Report Configuration】シートで【Report Name】を「はてなブログ」としたので、「はてなブログ」というシートに取得したデータが表示されていると思います。

f:id:ikdtty0423:20160515124513p:plain

【Report Name】を修正した方は、それに合わせて以下の数式を適宜修正してください。

最初に「PV」の値を集計します。D列3行目に以下の数式を入力してください。

=sumifs('はてなブログ'!B$4:B$839, 'はてなブログ'!$A$4:$A$839, B3&"-"&C3&"-*")

数式の詳細は調べていただきたいのですが、最初の【'はてなブログ'!B$4:B$839】でどのセルの値を合計するのかを指定しています。この場合はPVなので、「はてなブログ」シートのB列4〜839行目のデータを合計するという意味です。

行数はデータ取得のときに指定した日付で変わってくるので、それに合わせて修正してください。

次の【'はてなブログ'!$A$4:$A$839, B3&"-"&C3&"-*"】部分が集計する条件を指定しています。【'はてなブログ'!$A$4:$A$839】は「はてなブログ」シートのA列4〜839行目のデータ(日付)を表しており、その日付の中で【B3&"-"&C3&"-*"】にマッチするもの、つまり「B3」では年、「C3」では月を指定しており、「-*」は日付の部分は任意のものを取得しています。

それ以外の「セッション数、ユーザー数、新規ユーザー数、直帰数、滞在時間合計」も同様の考え方で3行目に以下の式を入力してください。

※ このファイルは後ほどまとめて公開しますのでお待ちください。

セッション数: =sumifs('はてなブログ'!C$4:C$839, 'はてなブログ'!$A$4:$A$839, B3&"-"&C3&"-*")
ユーザー数: =sumifs('はてなブログ'!D$4:D$839, 'はてなブログ'!$A$4:$A$839, B3&"-"&C3&"-*")
新規ユーザー数: =sumifs('はてなブログ'!E$4:E$839, 'はてなブログ'!$A$4:$A$839, B3&"-"&C3&"-*")
直帰数: =sumifs('はてなブログ'!F$4:F$839, 'はてなブログ'!$A$4:$A$839, B3&"-"&C3&"-*")
滞在時間合計: =sumifs('はてなブログ'!G$4:G$839, 'はてなブログ'!$A$4:$A$839, B3&"-"&C3&"-*")

4行目以降はも同じ用に入力すると月別の合計値を出すことができます。

データの計算

最後に「新規ユーザー率、直帰率、平均滞在時間」を計算します。

それぞれ取得した値を割り算するので、J〜L列3行目に以下の数式を入力してください。

新規ユーザー率: =G3/F3
直帰率=H3/E3
平均滞在時間: =I3/E3

作成月までのデータは取得・集計でき、それ以外は「0」「#DIV/0!」(割り算できない)となっていると思います。

※ 以下は2016年5月までのデータは取得でき(グレーの箇所)、それ以外はまだ取得できていないことになっています。

f:id:ikdtty0423:20160515131208p:plain

データ取得の自動化

このレポートですが、データ取得のために毎回【アドオン】→【Google Analytics】→【Run reports】を実行する必要があります。

f:id:ikdtty0423:20160515114428p:plain

 

これも手間なので、上記【Run reports】の下にある【Schedule reports】から自動で取得するようにしましょう。

【Schedule reports】をクリックすると以下の画面が現れますので、「Enable reports to run automatically.」にチェックを入れると「Schedule reports to run ...」部分が現れます。

ここはそのままにして、【Save】ボタンをクリックします。

 

f:id:ikdtty0423:20160515115039p:plain

 

これで毎朝午前4時〜5時の間に自動でデータ取得を行ってくれるようになります。

 

長くなりましたね...

ひとまず、何も修正せず、この通りにやってみてから自分に合わせて修正するのがいいかと思います。

次回はこの集計データを元にグラフ化を行っていきます。

1. モニタリングレポートとは?
【Googleアナリティクス】モニタリングレポートと作成自動化の流れ

2. データの自動取得
【Googleアナリティクス】無料で使えるレポート作成ツールでレポートに必要なデータを取得する

3. データの集計 ← 今回

4. グラフ化
【Googleアナリティクス API】グラフを作成する

5. 目標値の取得(CV)

【Googleアナリティクス API】目標値(CV)の取得

6. 記事ごと、ディレクトリごとのデータ取得

【Googleアナリティクス API】記事ごと、ディレクトリごとにデータを取得する

7. 流入元ごと、セグメントごとのデータ取得

【Googleアナリティクス API】流入元ごと、セグメントごとにデータを取得する