さて今回は、
- （前半）複数ある Google Analytics データの一部を Googleスプレットシートで一括取得
- （後半）一括取得した Googleスプレットシートの特定シートデータをメール本文（HTML）と PDF にして添付したメール送信
について紹介したいと思います。
毎回ログインして調べるのはやってられない！
ということと、問い合わせや登録関係に Googleフォーム + Google スプレットシートを使い始めて、Google Script でゴニョゴニョしだしたので、やってみるか！というノリでやってみました。
ようするに、ウェブサイトのアクセスログが知りたい！っていう度に、Google Analytics にログインして調べるのはやってられない！ そこはまぁ簡単に実装できたけど、メール添付できたら更に楽だなぁというノリです。
（前半）複数ある Google Analytics データの一部を Googleスプレットシートで一括取得
やりかたはネットで調べたら結構でてきます。
が一番参考になりました。
その他の参考サイト
- Googleアナリティクスの面倒なレポート作成をGoogleスプレッドシートで解消！ （第1回）（Impress）
- Googleアナリティクスの自動デイリーレポート＆よく使うMetrics一覧（いつも隣にITのお仕事）
- Google AnalyticsからGoogleスプレッドシート上にレポートを出す方法（bambooshot TV）
今回、「2018年4月1日から一日前までの自動取得」を設定してみます。
STEP 1. データを取得するまで
- Google Analytics で１つ以上、自分が閲覧できるビューを持つこと
※View IDを登録していくことになります。 - Google スプレットシートに「Google Analytics」アドオンをインストール
- 新規 Google スプレットシートを作成し、アドオン「Google Analtics」より「Create new report」でレポートを作成
※ 説明の便宜上、Name：サンプル１、Matrix等の設定はなし（後から入れる）にしました。 - スプレットシートの「Report Confituration」シート（固定）に、初期設定が入ります。
※以下は、サンプル１とサンプル２（View IDは非表示にしてます）の２つをいれています。
- Create new report をする度に、1つ目 = B列、２つ目 = C列と追加されていきます。
※手動やコピー＆ペーストでいれても構いません（多くなってくるとこっちのほうが楽）。 - 各種設定（筆者の設定例）
- Start Date
2018-04-01 - End Date
=TODAY()-1 - Metrics
- ga:users,ga:pageviews,ga:newusers,ga:sessions,ga:bounceRate,ga:avgSessionDuration,ga:organicSearches
※このあたりは前述した参考サイトを参考に、欲しいデータのMetrixをいれてください。 - Dimensions（国別トップ10を自動表示させるため、出力を国別にします）
- ga:country
- Order（ソートはページビューが多い降順）
-ga:pageviews - 設定し終わると下図のようになります。
※この Report Name で「シート」が作成されるので注意！
- アドオン > Google Analytics > Run rerports を実行しましょう。
シート「サンプル１」と「サンプル２」が出来ます。
STEP 2. 纏めページの作成
- シート「TOTAL Summary」を追加します。
※Google Script も含めて利用しますので、このシート名を変更する場合には読み替えてください。 - シート「TOTAL Summary」を一番左に移動しておきます
※必須ではなく、利便性の考慮です。 - 下記を追加してみてください。
A1 : From
B1 : ='Report Configuration'!B4
C1 : To
D1 : ='Report Configuration'!B5
これは最初の１つ目の Start Date (B1)と End Date (D1) を取得して表示します。いつからいつまでのデータを取得しているのかパッとみてわかるようにします。 - 次にサイトURLを入れておきます（利便性）
※筆者の例は下記の通り（セルは折り返しにしておくといいでしょう）
B2 : https://kitaney-google.blogspot.jp
C2 : https://kitaney-wordpress.blogspot.jp - 以下、少し装飾をいれたりなんかして枠を作成します。
※行3は、空行として１つあげました。名称 = 行4です。
- 上記の空いているところに、次のデータを入れます。
- レポート名：='Report Configuration'!2:2
※B5, C5もレポート名は同じです。 - アクセスログ：＜B列をいれて、コピー＆ペーストすればいい＞
B列（サンプル１）: =INDIRECT(B5 & "!C12")
C列（サンプル２）: =INDIRECT(C5 & "!C12")
※サンプル１，２の Totals For All Results の Pageviews データを表示 - ユーザー：＜B列をいれて、コピー＆ペーストすればいい＞
B列（サンプル１）: =INDIRECT(B5 & "!B12")
C列（サンプル２）: =INDIRECT(C5 & "!B12")
※サンプル１，２の Totals For All Results の Users データを表示 - 国：＜B列をいれて、コピー＆ペーストすればいい＞
B列（サンプル１）: =COUNTA(INDIRECT(B5 & "!A16:A1000"))+IF(COUNTIF(INDIRECT(B5 & "!A16:A1000"),"(not set)"), -1,0)
C列（サンプル２）: =COUNTA(INDIRECT(C5 & "!A16:A1000"))+IF(COUNTIF(INDIRECT(C5 & "!A16:A1000"),"(not set)"), -1,0)
※16行目から1000行目（そんな国ないと思いますので適当に）までから文字（国名）が入っていたらカウントする。ただし (not set) が出てきたら、カウントを -1 する。 - 国（トップ10） B列（サンプル１）: =INDIRECT(B5 & "!A16:A25")
C列（サンプル２）: =INDIRECT(C5 & "!A16:A25")
※Results Breakdown の16行目からは、Pageviews の降順でソートされてます。ので16行目から10行分のデータを取得して表示
以上がうまくいけば、下図のように出てくるはず！
おおー、Google関連ブログは結構いろんな国からアクセスされてますねー。
おおー、Google関連ブログは結構いろんな国からアクセスされてますねー。
Step 3. 自動取得にしよう！
アドオン > Google Analytics > Run rerports を毎回するのは面倒なので、スケジュール化します。
- アドオン > Google Analytics > Shedule Reports を選択
- Enable reports to run automatically をチェック
- every day, 4a.m. - 5 a.m. を設定
- Save
これで毎日 4時 - 5時のタイミングで、自動実行されます
これで前半のやりたいことは終了です。
これを共有しておけば、「必要なときにみたら？」っていえますね！楽だ。
（後半）一括取得した Googleスプレットシートの特定シートデータをメール本文（HTML）と PDF にして添付したメール送信
ただ見てねっていっても、「URL どこだっけ？」「共有アイテムにあるよ」「共有アイテムってなに？」なんて面倒なやり取りがあるかもしれません。年に1、2回みるぐらいな場合も多いですしね！ なら、メールで定期的に送ってやればいいという発想です。
※ここでは、実験も兼ねて毎日送信する設定にしています。これは毎日アクセスログを眺めたい人以外はスパムになりかねないので注意してください。１ヶ月に１度とか、数ヶ月に１度程度にしておくのがいいかなと思います。
参考サイト
- GAS から HTMLメールを送信する方法（bmoo.net）
- スプレットシートに登録したメールアドレス宛に スプレットシートデータを GASからメール送信する（lifehacker）
- 【GAS】スプレッドシートをPDFにしてGmailで送付する方法（いつも隣にITのお仕事）
※これから紹介するコードは、上記の参考サイトのコードを元に改変してます。
またコードは、まぁ動けばいいやという程度です。
STEP 1. メールを送信するまで
※メールの差出人は、Googleスプレットシートを作成した Googleアカウントのメールアドレスになります。これは変更不可なのでご注意を！
- 「Send-Emails」シートを追加します。
1行目：メモ欄です（適当にメモやタイトル等をいれてOK）
2行目：タイトル（自由記載）
3行目：
A列 = 名前（自由記載）
B列 = メールアドレス（ここに送信される）
※サンプルコードでは、B3 - B100 までをチェックします。 - https://github.com/kimipooh/sendArticleCountEmails より、 sendArticleCountEmails.js のデータをダウンロードして開いたり、Rawデータを開いたりして、中身をすべて選択してコピーします。
- 前半に作成した スプレットシートから、ツール > スクリプトエディタを開きます。
- そこに最初から入っている functin myFunctions() .. をすべて削除して、「２」でコピーしたコードをペーストします。
- 変更点（変更しなくても動きますが、より高速化したい場合）
※それぞれの変更は、ソースのコメントをみてください。 - var dataRange = sheet.getRange("A3:B100");
- var dataRange = sheet.getRange("A1:Z19");
- スクリプトを保存します。
※以下の説明のために、プロジェクト名は「sendArticleCountEmails」として保存します。
- 動作確認します（上部「関数を選択」から「sendArticleCountEmails」を選択して、▶ ボタンを押して実行します。
- 最初の一度のみ、「承認が必要です」のメッセージがでるので「許可」してください。
初めてスクリプトを作成したのなら、下記のように警告されます。
とりあえず試すだけなら、「詳細」をクリックして「sendArticleCountEmails（安全ではないページ）に移動をして許可してください。
※もし警告を出したくないなら、下記を参考に申請してみてください。
https://support.google.com/cloud/answer/7454865
うまくいったなら、
下記のようなメールが届きます。
添付ファイルの中身は下記の感じ。
STEP 2. メール送信の自動化
- STEP 1. で作成したスクリプトに、Googleスプレットシートの ツール > スクリプトエディタからアクセスします。
- 編集 > 現在のプロジェクトのトリガー を選択
- トリガーを新規作成します。
- 次の設定にします。
実行：sendArticleCountEmails
イベント：時間主導型、日タイマー、午前8時〜9時
※毎日4時〜5時に、Google Analyticsのデータを自動取得する設定にしていましたので、念のため 6時以降に実行するのがよいと思います。朝早いとメールで起こされるかもしれませんので、起きている時間のほうがいいかもしれません。
※ここを月タイマー等にして、実際にどの間隔でメールするか設定してみてください。
これで保存した完了です。
これで朝の8時〜9時に、メールで新しい情報が送信されてくるかチェックしてみて、問題ないようなら、間隔を変更するとよいと思います。
以上、結構長くなりましたが、備忘録を兼ねてメモしてみました。
2018年4月6日 @kimipooh
0 件のコメント:
コメントを投稿