Googleアナリティクスのデータを貼り付けるだけで瞬時にカテゴリ別分析

★気に入ったらシェアをお願いします!


計算するおじさん

photo credit: Calculation via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

これまで何回かの記事に渡って、Googleアナリティクスのアクセスデータからカテゴリ別の成績を見る方法についてお伝えしています。

前回の記事でカテゴリ別の平均ページ滞在時間や直帰率を知るための前準備として、条件付きのSUMPRODUCT関数の使い方についてお伝えしました。

条件付きSUMPRODUCT関数の使い方とよく出くわすエラーの回避方法
SUMPRODUCT関数は条件に合ったデータのみを計算するという発展をさせることができますので、今回はその基本的な方法とともにいくつか出くわすであろう罠とその回避方法についてお伝えしていきます。

今回でいよいよ本丸である、Googleアナリティクスのデータを貼り付けるだけで、平均ページビュー数、平均ページ滞在時間、直帰率を瞬時に算出する方法についてお伝えしたいと思います。

前回までのおさらい

まずGoogleアナリティクスからのデータの取得方法、そしてカテゴリ別ページビューの出し方については以下の2つの記事をご覧ください。

まずこちらの記事ではGoogleアナリティクスからデータを出力する方法、そしてそのデータを貼り付けるだけでカテゴリ別のページビュー数を算出する方法についてお伝えしています。

ペタっと貼るだけでGoogleアナリティクスのデータからカテゴリ別集計
Googleアナリティクスはカテゴリ情報を持っていないので、人気カテゴリを正確に知るには一工夫が必要です。今回はエクセルにデータをペタっと貼るだけでカテゴリ別PVを集計してグラフ化までする方法をお伝えします。

こちらではGoogleアナリティクスのアドバンス機能を使って、不要なデータを除外する方法についてお伝えしています。

Googleアナリティクスのデータを特定条件で絞り込むアドバンスの使い方
Googleアナリティクスからのデータに含まれているプレビューや見つかりませんページなどの余計なデータを、特定条件でデータを絞り込むアドバンスセグメントを使って除外する方法についてお伝えします。

その状態での、Googleアナリティクスからのデータはこちら。

Googleアナリティクスからの出力データ

そしてカテゴリ別のページビューを算出するところまで進めたエクセルシートがこちらです。

カテゴリ別ページビュー

ここをスタートとして進めていきますね。
よろしくお願いします!

AVERAGEIF関数でカテゴリ別平均ページビュー数

まず、カテゴリ別の平均ページビューを出していきます。

この場合の平均はいわゆる一般的な算術平均で、

カテゴリ別のページビュー数÷記事数

でOKですね。

ですが、エクセルには便利な関数が用意されていまして、AVERAGEIF関数を使いますよ。記述の仕方は

AVARAGEIF(範囲,検索条件,平均対象範囲)
指定された範囲の中で、検索条件に該当しているデータについて、平均対象範囲のデータを合計する

SUMIF関数に準じているので覚えやすいですね。

実際の式は

=AVERAGEIF(データセット1!A:A,”/”&カテゴリ!B2&”*”,データセット1!C:C)

で、全てのカテゴリ分をコピーすれば完成です。

AVERAGEIF関数でカテゴリ別平均ページビューを計算

COUNTIF関数でカテゴリ別記事数

ついでにカテゴリ別の記事数も出しておきましょうか。

その場合はCOUNTIF関数ですね。

COUNTIF(範囲,検索条件)
指定された範囲の中で、検索条件に該当しているデータの個数をカウントする

SUMIF関数、AVERAGE関数の対象範囲の要素がない書き方です。簡単ですね。

実際の式は

=COUNTIF(データセット1!A:A,”/”&カテゴリ!B2&”*”)

となります。

COUNTIF関数でカテゴリ別記事数を算出

SUMPRODUCT関数でカテゴリ別平均ページ滞在時間と直帰率

次にカテゴリ別の平均ページ滞在時間と直帰率を出していきます。

この場合は、ページビュー数を重みとした加重平均を使う必要がありました。

平均って種類があるの…?!重みがある場合の加重平均とその算出方法
平均にはいくつか種類があるのはご存知ですか?カテゴリ別の平均ページ滞在時間と直帰率の算出には、いわゆる一般的な平均が使えないということと、実際に算出する方法としての加重平均についてお伝えをしていきます。

また、加重平均を計算するためにはSUMPRODUCT関数を使うと簡単です。

エクセルデータ分析の強い味方!初心者向けSUMPRODUCT関数の使い方
エクセルでデータの集計や分析をするときに大変重宝する万能的な関数SUMPRODUCT関数について、その記述法や考え方についてなど、はじめの一歩について具体的な例を出しながら解説をしたいと思います。

さらに条件付きの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関数でカテゴリ別平均ページ滞在時間を算出

カテゴリ別の直帰率も同様の方法で

=SUMPRODUCT((ISNUMBER(FIND(B2,データセット1!A:A))*データセット1!C:C),(データセット1!G:G))/C2

この式で算出することができます。

SUMPRODUCT関数でカテゴリ別直帰率を算出

まとめ

ようやくといった感じですが、Googleアナリティクスのデータを貼るだけでカテゴリ別の

  • ページビュー
  • 記事数
  • 平均ページビュー数
  • 平均ページ滞在時間
  • 直帰率

を算出できるシートが完成しました。

少し覚えることや手順はあったかも知れませんが、一度作ってしまえばあとはGoogleアナリティクスのデータを貼るだけで算出ができるようになりますから、今後の分析においてはかなりの効率化になると思いますよ。

次回ですが、せっかくここまで来ましたので、算出したデータから自動でグラフも作成するようにして、視覚的に分析しやくすくしていきたいと思います。

Googleアナリティクスデータを貼るだけでカテゴリ別分析結果のグラフも出す
Googleアナリティクスで取得したブログのアクセスデータ元に算出した平均ページビュー数、平均ページ滞在時間、直帰率などの指標について、バブルグラフ化する方法についてお伝えしたいと思います。

どうぞ合わせてご覧ください!

連載目次:Googleアナリティクスのデータをペタっと貼るだけの集計分析

  1. ペタっと貼るだけでGoogleアナリティクスのデータからカテゴリ別集計
  2. Googleアナリティクスのデータを特定条件で絞り込むアドバンスの使い方
  3. 平均って種類があるの…?!重みがある場合の加重平均とその算出方法
  4. エクセルデータ分析の強い味方!初心者向けSUMPRODUCT関数の使い方
  5. 条件付きSUMPRODUCT関数の使い方とよく出くわすエラーの回避方法
  6. Googleアナリティクスのデータを貼り付けるだけで瞬時にカテゴリ別分析
  7. Googleアナリティクスデータを貼るだけでカテゴリ別分析結果のグラフも出す
  8. ビジネスマン必携!あらゆる場面で活用できるパレートの法則とパレート図の作り方