photo credit: Calculation via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
これまで何回かの記事に渡って、Googleアナリティクスのアクセスデータからカテゴリ別の成績を見る方法についてお伝えしています。
前回の記事でカテゴリ別の平均ページ滞在時間や直帰率を知るための前準備として、条件付きのSUMPRODUCT関数の使い方についてお伝えしました。
今回でいよいよ本丸である、Googleアナリティクスのデータを貼り付けるだけで、平均ページビュー数、平均ページ滞在時間、直帰率を瞬時に算出する方法についてお伝えしたいと思います。
前回までのおさらい
まずGoogleアナリティクスからのデータの取得方法、そしてカテゴリ別ページビューの出し方については以下の２つの記事をご覧ください。
まずこちらの記事ではGoogleアナリティクスからデータを出力する方法、そしてそのデータを貼り付けるだけでカテゴリ別のページビュー数を算出する方法についてお伝えしています。
こちらではGoogleアナリティクスのアドバンス機能を使って、不要なデータを除外する方法についてお伝えしています。
その状態での、Googleアナリティクスからのデータはこちら。
そしてカテゴリ別のページビューを算出するところまで進めたエクセルシートがこちらです。
ここをスタートとして進めていきますね。
よろしくお願いします！
AVERAGEIF関数でカテゴリ別平均ページビュー数
まず、カテゴリ別の平均ページビューを出していきます。
この場合の平均はいわゆる一般的な算術平均で、
カテゴリ別のページビュー数÷記事数
でOKですね。
ですが、エクセルには便利な関数が用意されていまして、AVERAGEIF関数を使いますよ。記述の仕方は
AVARAGEIF(範囲,検索条件,平均対象範囲)
指定された範囲の中で、検索条件に該当しているデータについて、平均対象範囲のデータを合計する
SUMIF関数に準じているので覚えやすいですね。
実際の式は
=AVERAGEIF(データセット1!A:A,”/”&カテゴリ!B2&”*”,データセット1!C:C)
で、全てのカテゴリ分をコピーすれば完成です。
COUNTIF関数でカテゴリ別記事数
ついでにカテゴリ別の記事数も出しておきましょうか。
その場合はCOUNTIF関数ですね。
COUNTIF(範囲,検索条件)
指定された範囲の中で、検索条件に該当しているデータの個数をカウントする
SUMIF関数、AVERAGE関数の対象範囲の要素がない書き方です。簡単ですね。
実際の式は
=COUNTIF(データセット1!A:A,”/”&カテゴリ!B2&”*”)
となります。
SUMPRODUCT関数でカテゴリ別平均ページ滞在時間と直帰率
次にカテゴリ別の平均ページ滞在時間と直帰率を出していきます。
この場合は、ページビュー数を重みとした加重平均を使う必要がありました。
また、加重平均を計算するためにはSUMPRODUCT関数を使うと簡単です。
さらに条件付きのSUMPRODUCT関数の使い方はこちらの記事で紹介しています。
…結構長い道のりでしたね。
まず、カテゴリ別の平均ページ滞在時間を出していきます。
式を先に出してしまいますと
=SUMPRODUCT((ISNUMBER(FIND(B2,データセット1!A:A))*データセット1!C:C),(データセット1!E:E))/C2
となりますが、解説をしていきますね。
データセット1の
- A列（ページ）にカテゴリURLが含まれているデータのみを対象にする
- C列（ページビュー）を加重平均の重み
- E列（平均ページ滞在時間）
をSUMPRODUCTして、カテゴリの合計ページビュー数（C2）で割るという計算になります。
ただ一点注意点がありまして、SUMPRODUCT関数内で掛け算が入っている場合は、対象範囲に見出し行があるとエラーになりますからデータセット1の見出し行は削除してください。
上記計算式でカテゴリ別の平均ページ滞在時間を算出できます。
カテゴリ別の直帰率も同様の方法で
=SUMPRODUCT((ISNUMBER(FIND(B2,データセット1!A:A))*データセット1!C:C),(データセット1!G:G))/C2
この式で算出することができます。
まとめ
ようやくといった感じですが、Googleアナリティクスのデータを貼るだけでカテゴリ別の
- ページビュー
- 記事数
- 平均ページビュー数
- 平均ページ滞在時間
- 直帰率
を算出できるシートが完成しました。
少し覚えることや手順はあったかも知れませんが、一度作ってしまえばあとはGoogleアナリティクスのデータを貼るだけで算出ができるようになりますから、今後の分析においてはかなりの効率化になると思いますよ。
次回ですが、せっかくここまで来ましたので、算出したデータから自動でグラフも作成するようにして、視覚的に分析しやくすくしていきたいと思います。
どうぞ合わせてご覧ください！
