記事一覧

スプレッドシートアドオンでGoogleアナリティクスのレポート作成が3分に! エクセルでデザインも自由自在!

2019.08.19

文/加藤春樹

アクセス解析結果を社内やクライアントと共有するために、Googleアナリティクス解析レポートの作成は不可欠な作業です。通常、レポート提出が1度で済むことは少なく、経過を確認するため、月1回など定期的に作成することが多いですよね。

さて、その面倒な定期レポート、あなたはどのように作成していますか?

私はGoogleアナリティクスが世に出た2005年から14年間、さまざまな業種のクライアントに対し、Googleアナリティクスを使った解析と提案を行ってきました。

以前の私は、Googleアナリティクスの画面をキャプチャーし、数値をコピペして、半日以上かけてレポートを作成していました。ようやく作業を終え、データを分析したり仮説を立てたりという本来の業務に取りかかる頃には、もうヘトヘトでした。

あるとき、半日もかかる作業部分をなんとか効率よくできないかと考え、「これは!」という方法にたどり着きました。その方法を使うことにより、それまで半日もかかっていたレポート作成作業が、わずか3分でできるようになったのです。

その結果、データを検証したり仮説を立てたりといった本来のアクセス解析業務に注力できるようになり、レポートの価値もぐんとアップしました。

今回は、私がいつも行っているそのオリジナル術をご紹介します。

具体的には、Googleスプレッドシートの解析データを、アドオンを利用して取得し、エクセルにVBAマクロでインポートするという方法です。

GoogleスプレッドシートもVBAマクロも使ったことがない、というあなたにもわかりやすく解説しました。絶対おすすめの方法ですので、ぜひ、ご一読ください。

また、今回はレポート作成用に設定したエクセルも無料で提供します。どうぞご自由にダウンロードしてください。

 

GoogleスプレッドシートアドオンとVBAスクリプトとエクセルで作る、これが最速レポート作成術!

Googleスプレッドシート? アドオン? VBAマクロ? 聞いただけで面倒そう、難しそう・・・。特にプログラミングの経験がない方がそう思うのは当然です。でも、ご安心ください。この記事では、手順に従うだけで誰もが再現できるよう、細かく解説していきます。
しかも、面倒な設定は最初の一度だけ。2回目以降はたった3分でレポートが作成できます。

そんな面倒な設定をしなくても、GoogleアナリティクスのPDFをダウンロードすればいいじゃないか、と思われるかもしれませんね。
しかし、GoogleアナリティクスのPDFには不要なものがたくさん掲載されていて、なにを見てどう解釈したらよいのか、非常にわかりにくくなっています(下図左参照)。
レポートには無駄な要素は掲載せず、自分の主張に沿ったグラフやデータのみを記載するのがセオリーです。だから、GoogleアナリティクスのPDFはおすすめしません。

参考 ウェブ分析レポーティング講座 小川卓著

●GoogleアナリティクスのPDFダウンロードgoogleanalytics-report-o1.png●この記事で作られたレポートgoogleanalytics-report-o2.png

また、レポート機能のついた専用のBIツールを導入すればいいじゃないか、と思われるかもしれません。しかし、高額であることや、使い方を習得し熟練する必要があることを考えると、専用のBIツールも、やはりおすすめできません。

無料で入手できる既存のツールを利用しながら、わずか3分でレポート作成が可能になるこの方法を、ぜひ、試してみてください。
さらに、設定済みの専用エクセルをご提供しますので、それを利用して好みの色や文字サイズでデザインすれば、解析者であるあなたの「仮説や改善案などのコメント」を、よりしっかり訴求することができるでしょう。

googleanalytics-report-o4.png

本記事を読むメリット
  • 半日かかっていたレポート作成作業が、わずか3分に!
  • 解析に注力できるので、レポートの価値が向上
  • VBAスクリプト初心者のあなたでも大丈夫! わかりやすい手順解説
  • ダウンロード可能なエクセルデータ付きで、デザインも自由自在!

事前準備:パソコン環境を確認し、ソフトを整える

まず、パソコン環境と必要なソフトについて確認します。以下の通りです。

OS: Microsoft Windows10
Microsoft Excel 2019(最新バージョン)
旧バージョンでも動く可能性は十分ありますが、検証はしていません。もし動作しない環境がありましたら、コメント欄からご連絡ください。
※Apple社Macでは検証していません。動かない場合、VBAスクリプトの微修正で動作する可能性があります。検証ご希望の方は、コメント欄からご連絡ください。
GoogleアナリティクスおよびGoogleサーチコンソールの導入
導入が済んでいなければ、下記の記事を参考に設定を行ってください。キャプチャー付きでGoogleアナリティクス・サーチコンソール導入のすべてのステップをわかりやすく解説しています。
Googleアナリティクス導入の徹底ガイド! 初心者も挫折しない全キャプチャー付き - Googleアナリティクス(Google Analytics・グーグルアナリティクス)
Googleドライブにアクセスし、Googleスプレッドシート新規作成の準備をする
解析データを取得するために必要です。ただし、GoogleドライブのアカウントがGoogleアナリティクスのアカウントと異なる場合は、GoogleアナリティクスのViewの設定で、当該アカウントに閲覧権を与えておく必要があります。
グーグルドライブ

参考
Googleドライブ
とは、Googleアカウントを持っている人が15GBまで無料で利用できるネット上のフォルダです。会員登録などしなくても、Googleドライブにアクセスするだけで使えるようになります。
PC上のファイルをGoogleドライブ画面上へドラッグ・アンド・ドロップするだけで、Googleドライブへアップロード保存が可能です。
ファイルには本人しかアクセスできないため、大切なデータを他人に見られる心配はありません。(ただし、許可することで他者と共有することは可能です。)
Googleスプレッドシートは、一言で表すならブラウザからエクセルを操作できるサービスです。シートを増やしたり、セル内で計算式を使えたりと、エクセルに慣れている人にはとても使いやすいツールです。
作成したGoogleスプレッドシートは、Googleドライブにのみ保存することが可能です。また本記事で紹介するように、エクセル形式で自分のPC上にダウンロードすることもできます。

事前の確認事項
  • Windows環境で、Excelは導入済みか?
  • Googleアナリティクスは導入できているか?
  • Googleサーチコンソールは導入できているか?
  • GoogleスプレッドシートはGoogleアナリティクスと同じアカウントで作られているか?

レポート出力の設定手順 (初回のみの作業)

それでは、実際のレポート出力までの手順をご紹介します。
VBAスクリプトで編集しなければならないのは、わずか1行です。ぜひ、がんばって最後までお読みください。

全体像

googleanalytics-report-o3.png

ステップ1  Googleアナリティクスのデータを、アドオンでGoogleスプレッドシートに取得する

Googleドライブにログインし、下記の処理を行ってください。

googleanalytics-report-s1.png

ネット上に新たにエクセルファイルを作るイメージです。

googleanalytics-report-s2.png

googleanalytics-report-s3.png

googleanalytics-report-s4.png

googleanalytics-report-s5.png

googleanalytics-report-s6.png

googleanalytics-report-s7.png

googleanalytics-report-s8.png

※ 例:この画面上では、「集客>キャンペーン>オーガニック検索キーワード」のデータを収集する設定です

Name: _集キーワード
この設定を実行したとき、このNameに設定された項目(_集キーワード)シートが生成されることになります。「_集」とは「集客に関する分析」の略、そのうちの「キーワード」についてデータを収集しようという設定例です。
Account: Googleアナリティクスのアカウントを選択
Property: 同様にプロパティを選択
View: 同様にビューを選択
Metrics: メトリックス(指標)を選択。今回は「Organic Searches(キーワード)」
Dimensions: ディメンションを選択。今回は「Keyword(キーワード)」と「Source(参照元)」

googleanalytics-report-s9.png

 

①「Start Date」と「End Date」は「yyyy-mm-dd」形式で入力
②もともとあった「シート1」は不要なので削除
③「View ID」はあなたのGoogleアナリティクスのビューIDです。メモしておきましょう。

googleanalytics-report-s10.png

残りの設定はコピペで行う

上記の工程で、「Configuration Options」シート上に1件だけ設定が行われました。その他の設定は下の図と設定表Aを参考に、コピー&ペーストで入力してください。

googleanalytics-report-s11.png

設定表A 赤枠部分を下記の内容に書き換えます。

Report Name _集キーワード _集チャンネル _集参照サイト _集ペイド _ユ推移 _ユ _ユ国 _ユ市区 _ユデバイス _ユ性別 _ユ年齢 _ユ曜日 _ユインタレスト _行ページ
View ID あなたのView ID =B3
※B3を参照の意
=B3 =B3 =B3 =B3 =B3 =B3 =B3 =B3 =B3 =B3 =B3 =B3
Start Date

2019-06-26

※計測開始日

=B4
※B4を参照の意
=B4 =B4 =B4 =B4 =B4 =B4 =B4 =B4 =B4 =B4 =B4 =B4
End Date 2019-06-30
※計測終了日
=B5
B4
※B5を参照の意
=B5 =B5 =B5 =B5 =B5 =B5 =B5 =B5 =B5 =B5 =B5 =B5
Metrics ga:organicSearches ga:users ga:users ga:users ga:users,ga:sessions,
ga:pageviews
ga:users,ga:sessions,ga:pageviews,
ga:bounceRate,ga:avgSessionDuration,
ga:sessionsPerUser,ga:pageviewsPerSession,
ga:goal1Completions,ga:goal2Completions
ga:users ga:users ga:users ga:users ga:users ga:sessions ga:users ga:pageviews,ga:sessions,
ga:avgTimeOnPage,ga:entrances,
ga:bounceRate,ga:exitRate
Dimensions ga:keyword,ga:source ga:channelGrouping ga:source ga:date ga:date   ga:country ga:city ga:deviceCategory ga:userGender ga:userAgeBracket ga:dayOfWeek,ga:hour ga:interestAffinityCategory ga:pagePath
Order -ga:organicSearches -ga:users -ga:users ga:date     -ga:users -ga:users -ga:users -ga:users ga:userAgeBracket   -ga:users -ga:pageviews
Filters               ga:country==Japan            
Segments     gaid::-8 gaid::-4                    
Limit 1000 1000 1000 1000 1000 5000                

※参考
Report Nameで先頭についている「_集」は、「集客に関する分析」を略したものです。同様に「_ユ」は「ユーザー(オーディエンス)に関する分析」、「_行」は「行動に関する分析」の略です。

設定した内容でGoogleアナリティクス アドオンを実行

googleanalytics-report-s12.png
googleanalytics-report-s13.png


ステップ2 Googleサーチコンソールのデータを、アドオンでGoogleスプレッドシートに取得する

上記と同じスプレッドシートで下記の手順を行います。

googleanalytics-report-c1.png
googleanalytics-report-c2.png

consoleで検索すると探しやすいです。

googleanalytics-report-c3.png

googleanalytics-report-c4.png

googleanalytics-report-c5.png

以上で、「Search Analyics for Sheets」アドオンがインストールされました。

googleanalytics-report-c6.png

赤枠部分に下記の通り記入します。
・Verified Siteあなたのサイトを指定してください。
・日付(DateRange)は取得したいデータの開始日と終了日に書き換えます。
・他は赤枠の通り入力します。

googleanalytics-report-c7.png

googleanalytics-report-c8.png

ステップ3 Googleスプレッドシートからエクセルファイルとしてダウンロードする

「形式を指定してダウンロード>Microsoft Excel(.xlsx)」をクリックします。
事前に「デスクトップ>レポート」フォルダを作成しておきダウンロードしてください。

googleanalytics-report-s14.png

googleanalytics-report-s15.png

ステップ4 レポート本体ファイル(report.xlsm)を「デスクトップ>レポート」フォルダにダウンロードする。

レポート作成用に私が設定したエクセルを無料で提供します。

report.xlsmファイルダウンロードはこちら

レポート本体ファイル(report.xlsm)をダウンロードし、「デスクトップ>レポート」フォルダにGoogleスプレッドシートを保存してください。

  • ※VBAスクリプトが含まれているため、拡張子がxlsmとなります。
  •  

googleanalytics-report-s16.png

以上で、Googleスプレッドシートでの作業をすべて終えました。

ステップ5 エクセルファイル(report.xlsm)での最終作業

エクセルファイル(report.xlsm)を開いて下記の作業をしてください。

VBAを実行するためには、コンテンツの有効化をクリックする必要があります。
この作業は初回のみ行えば、2回目以降は表示されません。

googleanalytics-report-x1.png

スペースシートは表紙となるページです。

googleanalytics-report-x2.png

5行目
dir = "C:\Users\kato\Desktop\レポート\"
この内容をあなたの作成したレポートフォルダの絶対パスに書き換えてください。
たとえば、Cドライブ直下にレポートフォルダを作成した場合は下記となります。
dir = "C:\レポート\"

googleanalytics-report-xm1.png

data.xlsxをプリント用のreport.xlsmファイルにVBAマクロでコピーする

このエクセルファイルでは、ロゴを差し替えたり、マージンや文字サイズなどをあなたの好きなデザインに変更できます。

googleanalytics-report-x2.png

①「レポートを更新する」をクリックすると、data.xlsxのデータがreport.xlsmファイルにコピーされます
②「データを表示」をクリックすると、コピーしたシート群が確認できます。

googleanalytics-report-x3.png

googleanalytics-report-x4.png

ステップ6 プリントする

「_console」などのGoogleスプレッドシートからのデータシート群を非表示にします。データシート群は単にグラフ描画のための元データで、印刷する必要はありません。必ず非表示にしましょう。

googleanalytics-report-xp1.png

①「ファイル」メニューをクリックします。
②「印刷」をクリックします。
③「プリンター」でAdobe PDFか搭載のプリンターを選択します。

  • ※Adobe PDFはAdobe Acrobatなどのインストールがないと選択できません。

④「ブック全体を印刷」に変更します。
⑤「印刷」ボタンをクリックすると印刷が始まります。

googleanalytics-report-xp2.png

以上で、レポートの設定およびプリントが完了しました。

3分でできる! 2回以降のレポート作成手順

B列の「Start Date」、「End Date」を書き換えると、C列、D列も自動的に書き換わることを確認します。

googleanalytics-report-r1.png

続いて、「Run reports」をクリックします。

googleanalytics-report-s12.png

googleanalytics-report-c6.png

赤枠部分を下記の通り設定し、「Request Data」をクリックしてデータを生成させます。

googleanalytics-report-c7.png

以上で、Googleアナリティクス、GoogleサーチコンソールのデータがGoogleスプレッドシートに更新されました。

デスクトップのレポートフォルダにダウンロードします。(report.xlsmファイルと同階層
ファイル名は、data.xlsxとなります。

googleanalytics-report-s14.png

googleanalytics-report-r2.png

「レポートを更新する」ボタンをクリックすると、データが書き換えられます。
データシート群は自動的に非表示になっています。
プリントする前に、念のため各シートを確認してください。プリントエリアのズレなどがあったら修正を行ってください。

googleanalytics-report-r3.png

①「ファイル」メニューをクリックします。
②「印刷」をクリックします。
③「プリンター」でAdobe PDFか搭載のプリンターを選択します。

  • ※Adobe PDFはAdobe Acrobatなどのインストールがないと選択できません。

④「ブック全体を印刷」に変更します。
⑤「印刷」ボタンをクリックすると印刷が始まります。

googleanalytics-report-xp2.png

以上で、レポートをプリントすることができました。

おすすめ:あなたの解析・改善案をプラスする

さて、ここまでの作業で、グラフや数値が入力されたレポートをほぼ自動的に作成することができました。
ここで、レポートの価値をさらに向上させるひと手間を加えることをおすすめします。解析者であるあなたの考えを加えるのです。

あなたのレポート作成は、“提出する”ことが目的になっていませんか?
そもそも、レポート作成の目的は、サイトの改善です。より多くのユーザーを呼び込み、より多くの問合せを得ることが本来の目的のはずです。解析者であるあなたの視点で、なにを行ったらレポートの数値を改善できるのか考え、具体的な改善施策や特筆すべき点を、できるだけ具体的にコメントとして書き加えましょう

googleanalytics-report-o2.pngコメントの参考例

レポート作成に慣れていないと、データのどこを見ればよいのか、どう読み解けばよいのか等、わからないことばかりかもしれません。
しかし、仮説検証を繰り返すことで、その精度が高まり、サイトに何らかの効果が現れるときが必ず来るはずです。それこそが、本来のレポート作成の目的なのです。

ですから、レポートにはぜひ、解析者であるあなたのコメントを加えてください。
そのコメントが、レポートの価値をきっと高めてくれるはずです。

まとめ

定期的なGoogleアナリティクスの解析レポートを最速で作成する方法として、GoogleスプレッドシートとVBAマクロでエクセル上に作成する方法をご紹介しました。

これにより、Googleアナリティクスの画面をキャプチャーし数値をコピペするという、無駄とも思えるような作業は一切なくなります。
初回の設定をしてしまえば、2回目以降はたった3分でレポート作成が可能です。しかも、使い慣れたエクセルファイルで、デザインも自由自在です。

浮いた時間を有効活用し、検証や仮説の立案といった本来のアクセス解析業務に注力し、レポートの価値をぐんとアップさせましょう。
解析データの読み解き方やコメントの書き方は、また別の記事で紹介する予定です。

最後までお読みいただきありがとうございました。

 

コメントをお残しください

ご感想やご意見など、ぜひコメントをお残しください。みなさまからのご意見をお待ちしております。質問に関しては必ずご返信させていただきます。

メールアドレス

コメントを送信する

この記事を書いたのは

  • image

加藤春樹

ディレクター
WEB制作に約20年たずさわり、ECサイトやファッションブランドサイトなどさまざまな業種のホームページを制作しました。”成果のでる”ホームページを目指し、日々努力を重ねています。
WEBマーケティング、WEB制作に関する記事を随時投稿する予定です。
もしご参考になりましたら、「いいね!」ボタン押していただけると、記事作成のやる気が出ます。

関連記事

オフィスアンツ メールマガジン

オフィスアンツからWEB制作や集客に興味のあるあなたに有用な情報を提供します。
 ・ 制作・マーケティングなどの意見交換を兼ねた交流会のご案内(過去年1回程度10~20人くらいで実施)
 ・ セミナー・勉強会のご案内
 ・ 有用な記事のご案内
あなたのメルマガ登録をお待ちしております。
オフィスアンツからの営業などは一切ありません。上記のメール配信の目的のみメールアドレスを使用します。
また、解除したいときには、メルマガにご返信いただければ、責任をもってメールマガジンを解除するとともに、メールアドレスを削除いたします。

メールマガジンに登録する

※当サイトに掲載されている画像等の無断転載・無断使用はご遠慮下さい。無断転載、無断使用した場合は損害賠償を請求します。