members

members

スプレッドシートでGoogle Analytics(GA:アナリティクス)の分析作業を自動化(レポート化)する

Tweet

画像元

 

日常業務の小さな作業を1つ1つ効率化・自動化していくシリーズ第3弾。

今回はアクセス解析ツールの王道「Google Analytics(以下GA)」による定常的な分析作業を自動化する方法をご紹介します。

定常的な作業はどんどん効率化・自動化していきましょう。

 

完成イメージ

まずは完成したレポートのイメージですが、このようなものを作りたいと思います。
※サンプルの数字を入れております

メディア別に昨対比較をするようなレポートですね。
対象としては複数の流入元=ユーザーが入ってくる入り口を持つサイトを想定しています。
それでは作り方を見ていきましょう!

 

レポートの目的とそれに必要なデータを定義する

レポートを作る際はまず、どのような目的で何を見たいのかを明確にしましょう。
今回の場合は以下を想定して作成していきます。

目的

メディア別でセッション数とCV数・CVRを昨対で可視化。週次で改善すべきメディアを把握すること。
大枠の状態把握ができるレポートと思ってください。

これ自体で分析が完結するわけではなく、ここからどのメディアの効果が低いかが分かり、深掘りに繋がります。
状態把握のためのレポートだからこそ、効率的に出せるのが重要になります。

 

そして、今回の目的を踏まえてGAで出すデータを整理すると以下になります。

■指標(GAにおけるデータの数値)
セッション数、CV数、CVR

■ディメンション(GAにおけるデータの属性)
集計するべき主要なメディア
今回は、organicとcpc、(none)、referralを集計しましょう!

それでは本題。
ここからはスプレッドシートでのレポート作成方法をみていきましょう。

 

スプレッドシートのアドオンを設定する

アドオンを追加する

まずはスプレッドシートのメニューにある「アドオン」>「アドオンを取得」をクリックします。

 

検索で「google analytics」と入力すると出てくるのでこちらをクリックします。

「インストール」>「続行」で追加し、最後にアクセス許可を行います。

 

アドオンで新規レポートを作成する

次に、追加したアドオンを使ってレポートを作っていきます。
先程同様、「アドオン」>「Google Analytics」から「Create new report」をクリックします。

 

すると以下のような入力欄が右側に出てくるので、入力して「Create Report」をクリックします。
なお、ここで入力した内容は後ほど変更も可能なので必要最低限の入力で進みましょう。
今回はデモアカウントを使って解説していきたいと思います。

 

ディメンションと指標を入力する

スプレッドシートのアドオンでは入力方法が決まっています。
記述方法を解説しているサイトもありますのでブックマークしておきましょう。

今回は前述の通り、
ディメンション:メディア
指標:セッション、CV、CVR
でデータを出して昨対を見たいので以下のように設定します。

 

なお、ディメンションはメディアのほかにセカンダリディメンションとして「年」を持ってきています。
データ自体は今年と昨年をそれぞれ出しますが、出したデータが何年のデータかを可視化する意味でもディメンションとして設定しておきます。
※サンプルデータのためCVを示す「ga:goalXXCompletions」の箇所は「XX」としていますが、ここに目標の番号を入れてください。

 

さて、ここまでやると残る作業は以下3つです。頑張りましょう。

  1. 開始日と終了日を入力する
  2. わかりやすいように集計する
  3. 週1回データを取得・更新する

続いて、1つ目の作業を自動化する方法についてみていきましょう。

 

開始日と終了日が自動で決まるように関数を入力する

まず、今年分の開始日と終了日ですが以下のように設定します。

■今年
・開始日 [=eomonth(today(),-1)+1]
eomonth関数は、eomonth(開始日,月)で開始日から起算して何ヶ月前、あるいは後の月の最終日を算出します。
上記の場合、今日を起算日として1ヶ月前の月末最終日を算出して、1日追加するので当月1日となります。

・終了日 [yesterday]
こちらは素直に前日までを設定しましょう。
なお当日を設定してしまうと数値が時間帯によって変動してしまうので前日までをオススメします。

 

続いて、昨年分の開始日と終了日は若干ややこしいですが以下のように設定します。

■昨年
・開始日 [=date(year(B4)-1,month(B4),day(B4))]
B4=今年の開始日が入っているセル番号、をもとに年・月・日を算出します。
月・日は同じですが、昨年にしたいので年だけ-1します。

・終了日 [=date(year(B4)-1,month(B4),day(today()-1))]
開始日と同様に、B4セルをもとに年・月・日を算出します。
今回は日にちについても昨日までの日数で算出する必要があるので最後の部分が「day(today()-1)」となっています。

 

アドオンを走らせる

「アドオン」>「Google Analytics」から「Run reports」をクリックしてローデータを出します。

 

ここまでできたら、ローデータ出しは完了です。

ここまでの作業で以下のようなローデータシートが2つ生成されているかと思います。
(以下のシートはサンプルで数値を入れております)

続いて、これらの数値を集計する表を作りたいと思います。

 

集計する表を作る

メディア別に集計する表を作る

今回集計する4つのメディアそれぞれでセッション・CV・CVRを昨対比較できるテーブルを作成します。

併せて全体の数値を集計するテーブルも作成しておきましょう。

 

関数で集計を自動化する

続いて、作った表のセルに都度手打ちしていたら手間なのでここも関数で引っ張ってきます。

方法はいくつかありますが、今回はvlookup関数を使って引っ張ってきましょう。

 

以下をそれぞれ赤枠部分のC10セル、D10セルに貼り付けます。

・セッション数を引っ張ってくる関数 [=vlookup(B8,’testReport(昨年)’!$A$16:$D,3,)]

・CV数を引っ張ってくる関数 [=vlookup(B8,’testReport(昨年)’!$A$16:$D,4,)]

 

B8に入っている「cpc」という単語を「testReport(昨年)」という名前のシートで検索し、
該当する行のデータからそれぞれC列(セッション)・D列(CV)の値を持ってくるという関数になります。

これを2019年の行に入れ込んだ後、同じ関数を2020年の行にも貼り付け、
「昨年」を「今年」に置換してあげれば2020年分も数値を取得することができます。
あとはC10:D11セルをコピーして他のメディアのセルにも貼り付けてあげれば集計シートの完成です。

ちなみに、%の差分を出す場合、そのまま引き算をすると割合が出でてきてしまいます。
今回はポイントで差を表したいので[=TEXT((E5-E4)*100,”0.00″)&”pt”]のように差分の値を数値化し、末尾に「pt」を付与してあげます。

なお、集計の際には常に行うこととして、ローデータのシートと引っ張ってきた数値が合っているかは確認しておきましょう。

 

これで集計自体も自動化できましたね。
最後は更新したい頻度に合わせてトリガーを設定して終わりです。

 

更新頻度に合わせてトリガーを設定する

最後の作業として、レポートの更新頻度を合わせてトリガーを発火させる頻度を変えます。
週次分析で使うなら週1回で問題ありません。

方法としては「アドオン」>「Google Analytics」>「Schedule reports」をクリックします。

 

するとポップアップが出てくるので「Enable reports to run automatically.」の左のチェックボックスにチェックを入れ、
どの頻度で更新するか、いつ更新するかを指定して「Save」をクリックします。

以下の例では、毎週月曜日の午前8-9時の間にアドオンを自動で走らせる=更新するという設定になります。

 

これでデータ出しとレポートの整形まですべての作業を自動化できました。

なお、毎週シートを新しく作って週毎にまとめた方が
「先週までの数値ってどうだったんだろう?」という比較ができるので
週毎に新しいシートを作って更新することをオススメします。

 

まとめ

いかがでしたでしょうか。
今回は、定常的な解析作業を自動化する方法をご紹介しました。
今回ご紹介した作り方以外にも作り方はあるので、方法の1つとして参照頂ければと思います。

 

ラボ活動について

最後に、冒頭の「TECHラボ」について気になった方もいらっしゃるかと思うので簡単に説明しますと、メンバーズにはラボという「社員同士での自主的な学び/研究活動をする制度」があります。

その1つであるTECHラボでは、実業務の無駄な作業を効率化・自動化するために、普段業務では使う機会のないプログラミングを学びつつアウトプットをしています。

 

コラム執筆者

桑原佑司(くわはら ゆうじ)

第3ビジネスユニット アカウントサービス第17ユニット所属
2017年4月入社。主にWeb広告運用・Webコンサルに従事。
2017年のクリスマスに同期とプログラミング初心者しかいないTECHラボを立ち上げる。TECHラボ代表。
趣味はランニングと服を買うこと。ここ1年はJSでの業務効率化にはまってます。

 

執筆記事

スプレッドシート関数 IMPORTXMLでスクレイピングする方法

【コピペで簡単】Hangouts ChatでリマインドBotを作る

【動画解説付き】Googleアナリティクスを使っても 「何が問題か分からない」というディレクター必見の分析術

【動画解説付き】Googleアナリティクスを使っても 「何が問題か分からない」というディレクター必見の分析術

なぜ、Googleアナリティクス(GA)で「何が問題か」に辿りつけないのか、どんなデ ータが不足しているのかが分かり、ユーザテストとGoogleアナリティクスの組み合わせ方が分かります。

資料ダウンロード