アナリティクス(GA)とスプレッドシートを使って記事ごとの分析を自動化する方法
こんにちわ。ディレクターの清水です。
今回は、｢アナリティクスのデータをスプレッドシートに自動抽出してKPIを効率的に計る方法｣を紹介したいと思います!
今回はAPIやGAS(googleAppScript)に関する知識が全くない方でも簡単に自動で抽出できる方法をご紹介するので、ディレクターの方々の参考になれば幸いです。
(2015/11/22 【使用ツール】 : スプレッドシート(spread sheet), アナリティクス(Google Analytics) )
【かわいい＆長持ち＆絶対喜ぶ】1歳の誕生日におすすめプレゼント25選 | mamanoko（ままのこ）
最近の僕達のSEO施策
最近弊社ではサイトのリニューアルを行ったのですが、さらにユーザービリティ向上に何かできなかなと思っておりました。そこで、記事内にリンクを貼ることで、ユーザー様のニーズにあった記事をレコメンドしてユーザビリティを上げたいと考えました。今回はKPI(Key Paformance Indicator)として、直帰率の下げ幅を設定しました。
検索上位でトラフィックの多い50記事に的を絞って、リンクを手動で追加して変動を見る、みたいなことを行いました。ただ、リンクを追加した記事を毎回アナリティクスでみて、直帰率の変動を見るみたいな単純作業は少し効率が悪いと感じ、じゃあ自動化しようとこれから紹介するようなことを行いました。 Spreadsheetにアドオンの取り込みから、アナリティクスのデータの取り出し方法、取り出したデータをKPIとして観測しやすいようにシートにまとめる、といった順番で最初から説明したいと思います。アドオンの使用方法を知っている方は、手順5からご覧いただければ良いかと思います。
取ってくる情報
アナリティクスの記事ごとのデータ(記事IDで絞込み)
ユーザー数, 直帰率, 離脱率
手順1. アドオンの導入
まずはスプレッドシートを開き、ツールバーの上部からアドオンを導入する。(無料)
｢アナリティクス｣などで検索をして、下(画像で◯の付いている方)を選択する。
その後承認を求めてくるので、｢承認｣を押して下さい。
ダウンロードが完了したら、下記の画像のようにアドオンを起動。
手順2. アナリティクスのデータをスプレッドシートに抽出する。
create new reportを押したら、下図のようにシートの右側にエディット画面が出るので、
name には適当に名前を入力(あとで自動的に記事のURLになるようにします。)
Matrics には Pageviews(PV数) % Exit(直帰率) Bounce Rate(離脱率)を選択し、
Dimensions には Date を選択すると、下記のようになる。
もちろん他の指標も抽出できるので、コンバージョンなどを設定している場合はそれも指標にいれると良いかもしれないです。
これで、Create Reportをクリックします。すると新しいシートが作成され、下記の｢Report Configuration｣ページが表示されます。このシートを｢シートno.1｣とします。
ちなみにこのままデータを抽出すると、すべてのページの合計のPV数や直帰率を図れるのでKPIの観察に便利です。(アナリティクスでいうと、｢ユーザーサマリー｣の情報が抽出できます。)
手順3. 記事ごとのデータに絞っていく。
今回では、サイト全体のデータではなく、記事ごとのデータが欲しいので、ここからさらに検索の範囲を絞っていきます。
｢Filters｣と書かれてある列に、ga:pagepath==記事ページのユニークID を入力します。例えばmamanokoの場合だと、記事URLは、https://mamanoko.jp/articles/◯◯という形をとっているので、/articles/◯◯の部分がページのユニークIDです。
これでこの記事のデータを取ってくることが可能になりました。ちなみに、Filtersの部分には記事IDではなく、ga:sourceMedium=@organicと入力すると日毎の｢オーガニック検索｣流入数が、ga:sourceMedium!@organicと入力すれば、検索以外での流入数(ソーシャル、リファラル、ダイレクト)を計ることもできます。このfilterの使い方に関しては、下記の記事が参考になるかと思います。この記事はアドオンの使用方法の基礎をしっかり書いてくれているので、かなりおすすめです。
Googleアナリティクスの分析はスプレッドシートのアドオンで全自動化しよう
手順4 抽出
再び上のツールバーのアドオンより、アナリティクスを選択し、Run reportを押すと、ついにフィルターに掛けた記事に関するデータが表示されます。
直帰率と離脱率は、少数で表示されているので、列を選択して左上の%を選択すれば、アナリティクスと同様に32%などと表示されます。
残念ながらアドオンの機能では、同時に複数の記事のデータを表示することはできません。しかし、複数のデータの変化を測れないとスプレッドシートで管理する意味がないので、手順5からが大事になってきます。
手順5 複数の記事のデータを同時に取得する方法
冒頭で紹介したように、今回我々は流入数の多い50記事にリンクを追加しました。そこで50記事すべての直帰率･離脱率の変化を計る必要があります。さらには、今後も他の記事にリンクを追加していくので、今ある記事だけではなく今後追加されるであろう記事も自動でデータを抽出できるようにしたいと思います。結論から言うと、すべての記事に関して手順1から手順4をやる必要があるのですが、ここを自動化してみます。
KPIの施策からReportシートへの自動追加のポイント
弊社ではこのような形で、KPIの施策を行なう度にどんな変更をしたかを記述しております。このシートを｢シートno.2｣とします。
このシートno.2のA列に記事URLが追加される度に、シートno.1に自動追加されるようにします。(ちなみにシートno.2のB列は空列、C列には変更日、D列には｢変更日前1週間の直帰率の平均｣(の自動で追加)、E列には｢変更日後1週間の直帰率の平均｣、F列には｢変更内容｣を記述するようにしました。最初に入力が必要なのでは、A･C･F列です。)
さて自動抽出のポイントは4つあります。
①必要なデータは、URL全体ではなく、｢/articles/◯◯｣の部分だけであること
②シートno.2には縦にデータを追加していくが、シートno.1では横向きにデータを追加していかないといけないこと
③取ってきた記事IDを受けて、それに応じたfilterとその他の項目も自動的に設定されるようにすること
④設定されたデータをRun reportをしなくても自動でアナリティクスのデータを取得すること
の4つです。それぞれの順に解説します。
①入力されたURLの/articles/◯◯の部分だけ取得する
シートno.2の空白のB列に下記のように関数を入力します。
midは、指定したセルから部分的に文字を抜き出す関数です。=mid(A6,20,15)ですと20文字目から15文字を抜き出すという意味です。同じ列全部にコピーして完了です。これでA列に記事URLを添付したら、B列に自動的に記事のIDが抽出されるようになりました。ちなみに、最初の列に=ArrayFormula(mid(A6:A100,20,15))などと入力すれば、下のセルにはコピーしなくても自動で記事IDを取得してくれます。Arrayformulaはかなり汎用性の高い関数なので、かなり便利です。詳しくはこの記事を参考に。
ARRAYFORMULA - ドキュメント エディタ ヘルプ
②縦のデータを横にする
シートno.1の18行目に、①で取得した/articles/◯◯を抽出します。まずは18行目以降のセルが結合されているのでこの結合を解除しましょう。その後に、下図のように関数を入力します。
transposeは配列の行と列を入れ替える関数です。=transpose('シートno.2'!B6:B100)とすることで、シートno.2の列Bの6行目から100行目を、シートno.1の18行目に取り出すことができました。(シートno.2に変更が加わったら、この値もきちんと変更されます。)ちなみにシートno.1の17行目の文字とURLはテンプレで表示されているだけなので、消しても問題ないので、削除しましょう。
③取り出した記事IDに応じたデータから、自動的に他の値も入力されるようにする
手順2ではcreate new reportsからデータを抽出しましたが、エディタを経由しないでシートに直接入力してもでアナリティクスのデータを抽出できます。
ただコピペではReport NameとFiltersを毎回書き換える必要があるので、以下の関数を入力する。
ここで、ifで指定しているのは、｢シートno.1の18行目に何かしらの値があるときにはデータを表示し、何も書かれていない場合には何も表示しない｣ということである。これを指定するのは、新しい記事URLを追加したときにも自動的にレポートのデータが入力されるようにし、何もないときは空白にしておく必要があるからです。3行目から9行目の値は一緒なので、ifを使わずそのままコピペでも良いように思えますが、シートno.2に記事URLを追加した時にまたコピペする手間が増えますし、逆に全部の列にコピペをしておくと Run Reportsをする際にReport Nameがないのでエラーが発生します。そのため記事IDがないときは、他の行も空白でなければなりません。ifで指定したら、同じように右にデータをコピペします。arrayformulaを使用すれば他に入力する必要はありませんが、もちろんこのif関数をコピペしても問題ありません。
これですべての記事のデータを取得する準備ができたので、早速Run Reportsを押してデータを取得。(データが多いと取得にすこし時間がかかります。50記事で約30秒程です。) ここでエラーがでる場合や、記事のデータが取得できていない場合は、スペルミスの可能性が高いので、もう一度チェックしてみてください。特に空白が入っているなどのミスは多いので気をつけましょう！
④設定したすべてのデータを毎日自動で取得する
毎回Run Reportsをするのは手間なので、Schedule Reportsより毎日自動で更新されるように設定します。ただし1日に一回更新なので、すぐにデータが欲しい場合はRun Reportsを押してください。
ここまでで、｢手順 6 KPIの施策からReportシートへの自動追加関数｣は完了です。ここからは、アナリティクスから抽出した記事ごとのデータをシートno.2に表示させます。
手順7. 抽出したデータをシートno.2に取り出す。
いままではLast N Daysの部分に30と入力し取得日から過去30日のデータを取得していた。毎日のKPIを計るにはこれでも良いのだが、今回は変更日を基準として変更前1週間と変更後1週間のデータが欲しいので、手順6のシートを下記のように変更します。19行目にシートno.2の変更日の情報をtransposeで取り出し、それを受けて変更日の1週間前をデータの開始日時に、変更日の1週間後を終了日時に指定する。(こうすることで、表示データの日付が固定されるので、あとでデータを参照する際にセルの位置情報を指定すれば、変更日の前後の情報を取得できるので汎用性が高いです。)ちなみに1週間の平均を出そうとしているのは、1日だけを選択してしまうと数値にばらつきが生じ、正確なデータがとれないからだ。
これでもう一度Run Reportsをした後に、シートno.2のD列とE列には下記のように入力します。これで変更前1週間前の平均(=average(indirect(B6&"!c16:c22")))と、変更後1週間後の平均(=average(indirect(B6&"!c24:c30")))が取得できます。
indirectは、文字列で指定したセル参照を返せる関数です。
しかしindirectとarrayformulaの相性が悪いので、今回はオートフィル(連続データのコピー)をします。
これで、ついにすべての記事の直帰率を計ることに成功しました!
手順8. 最後直帰率が下がっている記事には色をつけます
E列を選択して、｢条件付き書式｣を選択する。
一週間前の直帰率から1週間後の直帰率が下がっているものには、色をつけるように、下記のカスタム数式を設定します。条件式をカスタムするこで色んなデータを場合分けして色付けできるので、便利機能です。
☆完成☆感想
以上、｢アナリティクスのデータをスプレッドシートに自動抽出してKPIを効率的に計る方法｣でした！長いこと、書いてしまいましたが、わかりづらいことあったらすみません!!スプレッドシートやアナリティクスは奥が深いので、とても楽しいですね!
アドオンで取得できる情報は他にもたくさんあるので、応用すれば色々なデータが自動で抽出&分析ができると思います。今回は直帰率のデータを取得しただけなので簡単でしたが、今後もさらに発展させて複雑なデータ分析をおこなっていきたいと思います。
読んでいただきありがとうございます。同じような境遇の方達の参考になれば幸いです。 清水
