Google Analyticsのデータを半自動で社内レポートに落とし込む方法【サンプル付き】
WEBサービスを運営している会社だと、自社サービスの問合せ数やUU、CVRなどをレポートにして提出する機会が多いと思います。
WEBサイト数値のみのレポートであればGoogle AnalyticsカスタムレポートやGoogleデータポータルなどでレポート作成して報告できるのですが、サービスの問合せ実数や粗利といったWEBサイト以外の数字を出そうとすると、ExcelやPowerPointなどを使ってデータフォーマットを合わせないといけない…という方もいらっしゃるのではないでしょうか？
毎週の進捗レポートや毎月の報告レポートなどたくさん出す資料があるのに、Excelに転記する作業や数字の確認が発生していたら面倒ですよね。
この面倒な作業をGoogleスプレッドシートで簡単な設定をするだけで半自動化できます。
また、Google Analyticsデータを自動レポートする記事は世の中にたくさんあるのですが、ある瞬間ある瞬間で一定期間のレポートを出力している物が多いです。この方法だとログが溜まっていかないため、前年同月比や前週比・前月比・過去1年間のトレンドなどを見ようと思っても、その都度設定が必要になると思います。
そこで今回は、運用時に1回のコピペ作業をするだけで数字をログに残して、前年同月比や推移トレンドのレポートなどを簡単に出す方法をお伝えします。
レポートサンプル
サンプルはこちらです。ファイル＞コピーを作成 にてコピーしてご活用ください。（2021年11月にSocailも追加しました）
※基本的に「【手動入力欄】テストサイト」「Report Configuration」シート以外は、触らないように作っています。
誤って式を壊してしまわないようにシートに保護をかけていますので、編集の際は保護を解除して編集してください。
Google Analyticsアドオンのインストール
まずはGoogleスプレッドシートにGoogle Analyticsのデータを取り込むため、アドオンをインストールします。
①Google Chromeの右上メニューから「新しいシークレット ウィンドウ」を開きます。
②計測したいWEBサイトのGoogle Analyticsを閲覧できるGoogleアカウントでGoogleにログインをします。
③Googleドライブを開き、新規でGoogleスプレッドシートを開きます。
④メニューから アドオン＞アドオンを取得 をクリックします。
⑤「Google Analytics」を選択して、「+無料」をクリックしてインストールします。
出力したいレポートの設定
次にGoogle Analyticsの内容を自動取得するために、レポート設定を作成します。
コピーしたサンプルを使って、自社WEBサイトのレポートを作りたい方は、下部のサンプル活用方法までお進みください。
①メニューから アドオン＞Google Analytics＞Create new report を選択します。
②レポート作成画面が右側に出現するので、以下の設定をしていきます。
Name your report
レポートの名前になります。作成されたレポートはシートに追加されるため、分かりやすくて使いやすい名前にしましょう。
今回は「【自動出力欄】サイト数値」という名前にします。
Select a view
計測したいWEBサイトのアカウント（Account）、プロパティ（Property）、ビュー（View）を設定します。
Choose configuration option
ここが英語表記なので一番の難関になると思います。抽出したいデータの分析軸と指標を設定します。
「Metrics」は指標を意味しており、ある分析軸に対しての数値になります。Google Analyticsではセッション数、直帰率、コンバージョン数など、表の右側に出力されるデータです。
「Dimension」は分析軸を意味しており、ある数値を切り分けたい項目になります。Google Analyticsではデフォルトチャネルグループ、ランディングページ、日付など、表の左側に出力される項目です。
「Segments」はGoogle Analyticsではページ上部で設定しているセグメントのことです。
今回は日別×デバイス別×デフォルトチャネルグループ別で、セッション数とコンバージョン数の推移を見ていきたいと思いますので、
Metricsには「Sessions」「Goal XX Completions」を設定して、Dimensionsには「Date」「Device Category」「Default Channel Grouping」を設定します。
| ユニークユーザー数（UU）でカウントしたい方へ | ユニークユーザー数は計測期間中に初めてアクセスしてきた人をカウントします。そのため、Dailyでユニークユーザー数を出力したものを合算して月の数字を出すと、Monthlyでユニークユーザー数を出力したものよりも数が多くなることがあります。特に1人のユーザーが月に何回も訪問するようなサービスだと、その差が大きく出てしまうためご注意ください。セッション数はGoogle Analyticsではその日の24時でセッションを切るため、Dailyで出力したものを合算しても問題ありません。 |
③「Create Report」をクリックしてレポート出力します。
※この際に「ScriptError: その操作を実行するには承認が必要です。」と出た場合は、シークレットウィンドウでお試しください。
④デフォルトでは計測対象日などが過去30日になっているので、スプレッドシートから各数値を修正します。
「Start Date」は計測開始日を示す項目です。
関数で表示もできるため、
=TODAY()-14
などで設定すると、レポート出力日から2週間前を指定できます。
大型連休で運用予定日に対応できなくても後日対応できるように、毎週レポートを提出しているような人は14日前、毎月運用している人は42日前を設定しておくと良いと思います。
「End Date」は計測開始日を示す項目です。
デフォルトの「yesterday」でも良いのですが、Start Dateと表記を合わせるため、
=TODAY()-1
などで設定すると、レポート出力日の前日を指定できます。
レポートを作る最初の段階では過去の推移も出したいと思うので、「Start Date」を変更することで過去の数字を含めて一気に出力することができます。
その際、Google Analyticsでは選択した期間でプロパティ単位のセッション数が50万件以上の場合に、サンプリングの処理が入ります。
サンプリングの処理が入ると精緻な値とズレてしまうので、セッション数が多いWEBサイトでは日付を短めに設定すると良いです。
また、Google AnalyticsではEU圏の「一般データ保護規則」（GDPR）を遵守するために、データの保持期間を26ヶ月にしています。
26ヶ月より前のデータが取得できなかった場合は、Google Analyticsの
管理＞プロパティ＞トラキング情報＞データ収集 から「ユーザーデータとイベントデータの保持」
を設定できるので、今後のために設定変更をしておくことをおすすめします。
※グローバルに展開されている会社など、EU圏からWEBサイトへ来訪されている場合はどう対応すべきか自社内でご確認ください。
⑤Metricsで指定しているコンバージョンについて、修正します。
ga:goalXXCompletions
と出力されてしまっている場合、目標の番号が分からないため計測ができません。計測したいコンバージョンの番号をGoogle Analyticsの管理画面で調べます。調べて分かった番号をXXの部分に書き込みます。
ga:goal1Completions
⑥レポートが出力される際の並び順を指定します。
「Order」が並び順を示す項目なので、今回は日別でソートするために
ga:date
と設定します。
⑦レポート出力される行数の制限を無くします。
「Limit」は出力される行数の制限をする項目なので、設定してしまうと最新日までの情報を取れなくなる可能性があります。
この項目を空にしておくことで制限がなくなるので、「1000」という記述を削除します。
⑧レポート出力されるデータの精度を上げます。
Google Analyticsにはサンプリングという概念があり、「処理速度を優先するか」「データ精度を優先するか」を設定できます。
そのサンプリングの設定をしていくのですが、サンプリングの項目はデフォルトでは非表示の行に隠れてしまっています。 そのためまずは非表示の行を表示させ、「Sampling Level」という行を表示させてください。
「Sampling Level」はデータの精度を示す項目なので、データ精度を優先するために
HIGHER_PRECISION
と記載します。
⑨メニューから アドオン＞Google Analytics＞Run reports を選択します。
※この際に「ScriptError: その操作を実行するには承認が必要です。」と出た場合は、シークレットウィンドウでお試しください。
⑩Name your reportで設定した名称（【自動出力欄】サイト数値）で、シートが追加されているので内容を確認します。
出力された値が同じ条件で絞り込んだ際のGoogle Analyticsと同じであれば成功です。
| サンプル活用方法 |
①分析したいWEBサイトのGoogle Analyticsに入り、 管理＞ビュー＞ビューの設定 から「ビュー ID」に記載されている数字列をコピーします。  
|
レポートの自動出力設定
無事にレポートが出力されたら、次は自動でGoogle Analyticsの数字を取りに行くように設定します。
①メニューから アドオン＞Google Analytics＞Schedule reports を選択します。
②「Enable reports to run automatically.」にチェックを付けます。
③「Schedule reports to run」は更新頻度です。毎日更新する場合は「every day」、毎週更新する場合は「every week」を設定します。
月初しか運用しないなどの特別な理由が無ければ「every day」での更新で良いと思います。
「between」は運用する時間に合わせて設定で良いかと思います。Google Analyticsは精緻なデータ反映に最大24時間かかるため、精緻な前日数字を取りたい方は夕方の16時台（4 p.m. – 5 p.m.）に設定して、レポート作成は夕方の運用にされると良いかなと思います。
朝の9時以降なら軽微なズレはあっても大幅なズレはない事が多いので、僕は朝の9時台（9 a.m. – 10 a.m.）にデータ更新するように設定しています。
※精緻なデータ反映に最大24時間かかるのはGoogle Analyticsの仕様のため、スプレッドシートではなくGoogle Analyticsを見に行っても朝の数字と夕方の数字で違うことがあります。
④設定に問題がなければ「Save」ボタンをクリックして、レポート自動出力設定は完了です。
Googleスプレッドシートのデータを形成する
ここからは出力されたレポートをもとにデータを整形していきます。
この記事の冒頭にも書きましたが、上記の自動出力のみでは「Start Date」と「End Date」で設定した計測期間でしか、レポートの出力ができません。
しかし、WEBサイトのレポートをする際に前年同月比や推移トレンドを出してほしいと言われることは多いかと思います。
そこでログを溜めるために手動入力欄を作って、そこに溜まったログからデータ分解してレポート作成を行っています。
月別や週別ではなく日別でデータ取得しているのは、手動入力する項目を減らしたかったのと、異常値が出た際に分析しやすくするためです。
ここのレポート出力プロセスは解説すると長くなってしまうため、ページ上部で案内したサンプルをベースに紹介します。シートの詳細については需要がありそうなら別の記事で書こうと思います。
「【自動出力欄】サイト数値」「【手動入力欄】サイト数値」シート
「【自動出力欄】サイト数値」シートに出力された内容を、「【手動入力欄】サイト数値」シートに転記します。
「【手動入力欄】サイト数値」シートを見るとサンプルでは2018年1月からのデータが入っていますが、自社WEBサイトのレポートを出したい方は3行目以降を削除します。
「【自動出力欄】サイト数値」シートに出力された内容を3行目以降に貼り付け、以降は運用のタイミングで前回運用で貼り付けた日付以降のデータを追記していきます。
上記で「【レポート】サイト数値_Monthly推移」「【レポート】サイト数値_Weekly推移」シートの出力まで終わってレポート作業は完了なのですが、他のシートがどのような役割をしているか簡単に記載したいと思います。
「【計算欄】ディメンション分解」シート
「【手動入力欄】サイト数値」シートに入力されたサイト数値を、デバイス別×チャネル別×日別で分解したシートです。
日別の流入数で0件になってしまうチャネルがあると、自動出力のレポートでは出力されないので、後々のレポート集計に影響を与えてしまいます。
0件のチャネルは0件だったと分かるように、IF関数とVLOOKUP関数を使って件数が記録されている日にはその件数を、件数が記録されている日には0を出力するようにしています。
またMonthly集計やWeekly集計をするために、MonthlyフラグやWeeklyフラグを確認して期間中のセッション数・CV数を合算させています。
「【計算欄】Monthly抽出」「【計算欄】Weekly抽出」シート
「【計算欄】ディメンション分解」シートで分解・合算された数値を抽出するシートです。
レポート出力ではサイト推移を表示するので、時間軸を横にした表の方が見やすいです。
「【計算欄】ディメンション分解」シートの時間軸が縦なので、そこからチャネル別推移レポートを時間軸を横にして出力するのが少し手間です。
その前段階の処理としてこのシートを作成しています。
「【レポート】サイト数値_Monthly推移」「【レポート】サイト数値_Weekly推移」シート
「【計算欄】Monthly抽出」「【計算欄】Weekly抽出」シートに出力された内容を、縦横転置して出力しています。
また、デバイス合算値もこのシートで算出して、各指標の前年同月比や前月同週比も算出しています。
それ以外に前月比や前週比を出したい場合はカスタマイズして作って貰えればと思います。
まとめ
後半駆け足となってしまいましたが、いかがでしたでしょうか？
月別や週別の数値が上記のような形で算出できていれば、問合せ実数や粗利といったWEBサイト以外の数字を管理しているExcelに簡単に貼り付けすることができるため、WEBサイト数値を報告するためにGoogle Analyticsを見て転記する必要はなくなります。
問合せ実数や粗利もGoogleスプレッドシートで管理できる会社であれば、IMPORTRANGE関数を使うことで簡単にスプレッドシートの値を外部参照させることができるので、結合して半自動的にレポート作成ができる状態を作り上げることもできます。
また、ExcelとPowerPointでレポート作成している会社であれば、Googleスライドを使ってGoogleスプレッドシートのグラフや表を参照することで、PowerPointで作るような報告レポートも半自動化することができます。
レポートのグラフや表に関する作業は自動化させて、その分の時間を分析に割り当てて改善PDCAを回していきたいですね。
