こんにちは！非エンジニアでウェブマーケターの木庭です @koba_tokyodays ！
みなさんはGoogle Analyticsに設定した目標クリック数を毎日アナリティクス上で見られてると思いますが、前日や週次、月次推移の比較ってしづらくありません？
でもアナリティクスとスプレッドシートを連携するだけで簡単に日週月次の推移を見ることができるんです！
今回の記事ではその方法をご紹介します！
結論を先にまとめると
- PV、user、sessionの日週月データをスプレッドシートで毎日見れるようになる
- さらにGoogle Analyticsで設定した目標をスプレッドシートに連携して
- どのページが送客数多いかスプレッドシートで見れるようになる
いますぐ使いたいというかたは目次の最後をタップしてください！
タップできる目次
スプレッドシートにGoogle公式アドオンをインストールする
- スプレッドシート>アドオン>アドオンを取得。
- Google Analyticsを選択もしくは検索してインストールしてください。
Create new reportを選択
スプレッドシート>アドオン>Google Analytics>Create new reportを選択します。
選択後、以下の内容を入力及び選択してください。
- Name > pagepath_goalpath とします
- Account
- Property
- View
Metrics、Dimensions、Segmentsは一旦スルーしてCreate Reportを選択してください。
各項目を設定
作成されたReport内の項目を設定します。
コピペ用各項目
| 項目 | コピペ用/記述方式など |
| Start Date | yyyy/mm/dd |
| End Date | yyyy/mm/dd or yesterday |
| Metrics | ga:goalxCompletions |
| Dimensions | ga:yearMonth,ga:isoWeek,ga:date,ga:goalCompletionLocation |
各項目の内容は以下です。
- Start Date：データ取得開始日
- End Date：データ取得終了日
- ga:goalxCompletions：目標完了数。xはアナリティクス上で設定した目標IDの数字を入力
- ga:yearMonth：yyyymm表記の年月
- ga:isoWeek：ISO基準の週番号
- ga:date：yyyy-mm-dd表記の年月日
- ga:goalCompletionLocation：目標完了のURLパス
設定が完了したらアドオン>Google Analytics>Run reportsを選択してください。成功すると以下のようなシートが作られます。
ピボットテーブルを作ってパスごとのデータを見る
行に
- GoalCompletion Location
- Month of Year
- ISO WEEK of the Year
値に
- ga:goalxCompletions
を設定します。
すると週次の各ページパスごとの目標クリック数を取得することができます！
毎日更新して最新のデータを反映する設定
アドオン>Google Analytics>Schedule Reportsを選択。
Enable Reports to run automaticallyにチェックをいれ、好きな時間に設定してください。
自分は一日一度、am4時に更新するようにしてます。
PageViewやUser数も取得し、PVあたり送客数を割り出す場合
別のReportを作成し関数でつなぎこむことで可能になります。
作業工程
- 別のシート pagepath_goalpath_keyを作り、pagepath_goalpath シートを読み込み、接続key列を作る
- PageView用のpagepath_goalepath_PV Reportを作成
- 別のシート pagepath_goalepath_PV_key を作り↑のReportを読み込み、接続key列を作る
- combile シートを作り1と3を関数でつなぐ
- ピボットテーブルを作る
1.別のシート pagepath_goalpath_keyを作り、pagepath_goalpath シートを読み込み、接続key列を作る
pagepath_goalpathシートをpagepath_goalpath_keyシートで読み込みます。
=query(pagepath_goalpath!A15:E)関数もしくはimportrange関数を利用します。
F列をkeyとして使用します。keyはCとD列を&でつないだものです。
2.PageView用のpagepath_goalepath_PV Reportを作成
Dimensionsにga:pagePathを設定した pagepath_goalepath_PV Reportを作成します。
※ga:goalCompletionLocationとga:pagePathは共存できません。
コピペ用
- Metrics: ga:users,ga:sessions,ga:pageviews
- Dimensions: ga:pagePath,ga:pageTitle,ga:yearMonth,ga:isoWeek,ga:date
3.別のシート pagepath_goalepath_PV_key を作りpagepath_goalepath_PVシートを読み込み、接続key列を作る
pagepath_goalepath_PV_key シートを作りpagepath_goalepath_PVシートを1同様query関数で読み込みます。
G列のSessionsはそのままだと数字表記のため、I列で =time("","",Gxxx)関数を使い hh:mm:ss表記に変更します。
keyはJ列で作成します。
4.combile シートを作り1と3を関数でつなぐ
合体用のcombineシートを作り、情報量が多いpagepath_goalpath_PV_keyシートを先にquery関数でA1に読み込みます。
次の工程で利用するので
- K1にkey2
- L1にga:goalxCompletions
と記載しておきます。
IとJにそれぞれ関数をいれる
- K列：=match(Jx,pagepath_goalpath_key!F:F,0)
- L列：=index(pagepath_goalpath_key!E:E,$Kx)
※xは適宜変更
そして上下にコピペすると以下の画像のようにkey列ごとの目標クリック数とPV、ユーザー数が1つのデータにまとまりました。
ここまでめちゃ大変w
5.ピボットテーブルを作る
ピボットテーブルで以下行や値を設定します。
▼行
- Page
- Month of Year
- ISO Week of the Year
▼値
- USers
- Pageviews
- sessions/秒
- ga:goal4Completions
以上設定で完了です！あとはよしなにフィルタリングしてください！
不要なパラメーターや不要なパスを取り除く方法
wordpressを利用している場合、プレビュー状態やcustomize時のURLは/?p=xxxや/?customize_xxxと表記され、そのときに目標をクリックすると不要なデータが↑のシートに掲載されてしまいます。
そこでデータの上流で不要なパスをはぶく設定をしたいと思います。
今回の記事で言うと pagepath_goalpath_PV_keyシート を作成しpagepath_goalpath_PVをquery関数で読み込んだときに
=QUERY(任意のシート名!範囲始点:範囲終点,"where not A contains '/?p=' and not A contains '/?customize_'",true)
とすればOKです！
A列に含まれている'/?P='と'/?customize_'の列を除外して表示、かつ1行目を見出しとして使用する。
という関数です。
すぐに使えるスプレッドシートテンプレです！
ここまでお読み頂きありがとうございます！書いてみてこれは伝わらないなと思いましたw
なのでみなさんにすぐ使っていただけるようにテンプレのスプレッドシートを用意しました！是非使ってください！
このブログの状況が丸裸ですw
また執筆時点で当ブログで目標設定してなかったので、以下画像に目標設定しました！その他の記事でも表示するように設定したので押して見て頂くと翌日には↑のシートに反映されます！
おわりに
文字で書くととにかく長く、なんか全然わかんないな…となるんですがテンプレ用スプレッドシートを使って組み立てて貰えれば、今後ページごとのPVや実績、修正したときの推移などが見える化でき、メディア運営方針の策定やLPブラッシュアップの参考になるはずです！
というわけでGoogle Analytics&スプレッドシートでPVと送客数を見える化しようでした！
わからないことや、ここ違うんじゃない？などあればいつでも以下のコメントか、twitterから質問してください！
→のツイッターアカウントでも色々つぶやいてるので是非フォローしてください☆@koba_tokyodays