みなさん、こんにちは！
引き続きウェイトトレーニングに励んでますタカハシ(@ntakahashi0505)です。
今回はウェイトといっても統計のお話です。
以前こちらの記事でGoogleアナリティクスのデータからカテゴリ別ページビューを集計する方法をお伝えしました。
これを読まれた皆さんはページビューだけでなく、カテゴリ別の平均ページ滞在時間や直帰率なども知りたくて知りたくてウズウズされていることと思います。
これらの計算もGoogleアナリティクスからのデータをエクセルにペタっと貼るだけでできるようにしていきたいと思います。
実はけっこうそこまでの道のりは長くてですね、まず前準備として、カテゴリ別の平均ページ滞在時間と直帰率の算出には、いわゆる一般的な平均が使えないということと、実際に算出する方法としての加重平均についてお伝えをしていきます。
どうぞお付き合いをよろしくお願いします。
ページ滞在時間や直帰率はふつうに平均とっちゃダメ
普通、平均の算出といったら全部足し算したもの（総和）を標本数で割るという方法で算出しますよね。
100円のリンゴと200円のリンゴの平均価格は300円÷2個で150円。
15才の兄と10才の姉と5才の弟の平均年齢は30才÷3人で10才。
当たり前ですよね。
カテゴリ別の平均ページビュー数はこのやり方で
カテゴリ内各記事のページビュー数の総和÷記事数
で簡単に算出できます。エクセル関数ではAVERAGE関数ですね。
平均ページ滞在時間や直帰率も平均なんだから同じだろ？ということで
カテゴリ内各記事の平均ページ滞在時間の総和÷記事数
カテゴリ内各記事の直帰率の総和÷記事数
で出せるなどと思っていませんか？
全然ダメです。
これが全然ダメなんですよ。
なぜ全然ダメなのか、ということを簡単な例で説明しますね。
普通の平均ではダメということを直感的に理解する例
Excel・VBAカテゴリには記事が2つあるとします。
「1,200万人の事務員たちよ、～」は人気の記事で
- ページビュー：10,000PV
- 平均ページ滞在時間：1,000秒と長く読まれる傾向
- 直帰率：100％と全然よくない
です。一方でもう一つの記事「【エクセルVBA入門】Do While～」は
- ページビュー：残念ながらたったの1PV
- 平均ページ滞在時間：たったの1秒
- 直帰率：0%
でした。
ではこのExcel・VBAカテゴリの色々な平均を算出していきましょう。
まず、平均ページビューを出してみますと
計算式は
=AVERAGE(B2:B3)
で、2つの記事の平均は5000.5。これはOKですね。
では同じように平均ページ滞在時間について平均をとってみます。
計算式は
=AVERAGE(C2:C3)
で、結果は501…。
明らかにおかしいのがわかりますか？
1,000秒かけて記事を読んでいる人が10,000人もいて、1秒しかかけない人はたった1人…それにも関わらず、Excel・VBAカテゴリの平均ページ滞在時間はかなりたった1人に影響をされてしまっています。
ほとんどの人が1,000秒滞在しているわけですから、Excel・VBAカテゴリの平均ページ滞在時間は限りなく1,000秒に近い値になるはず、というのが感覚的に理解いただけると思います。
直帰率に関しても同様なことが言えます。10,000人が100％なのに、1人が0％だったからといって、Excel・VBAカテゴリの直帰率が50％となるわけはありません。もっと、限りなく100％に近い値になるはずです。
平均にも色々あるんです…算術平均と加重平均
私たちが一般的に「平均」と呼ぶ、総和をとって標本数で割るという平均の取り方を別名で算術平均（または相加平均）と言います。
例で示した通り、カテゴリ別平均ページビューはこの算術平均でとることができます。
平均ページ滞在時間や直帰率をこの算術平均で計算するとなぜおかしくなるのか？
ということなのですが、例でいうとこの2つの記事をイコールで扱ってはいけないということなんです。
「1,200万人の事務員たちよ、～」の記事のほうが比率として圧倒的に読まれているわけですから、こちらの記事の値に重みをつけて平均をとらないといけません。
このように重みを考慮した平均を加重平均と言います。
この加重平均の算出方法を説明していきます。
加重平均の考え方と算出方法
まず各行について平均ページ滞在時間に重み（＝ページビュー数）を掛けます。
10,000人が1,000秒ずつ記事を読んでいるわけですから、「1,200万人の事務員たちよ、～」の記事を読んだ人の滞在時間は全部で1,000万秒になるわけです。
同様に「【エクセルVBA入門】Do While～」は1人が1秒読みましたので、この記事全体の滞在時間は1秒となります。
次に二つの記事の滞在時間を足し算します。これでExcel・VBAカテゴリ全体の滞在時間が出ますね。
重みを掛けたものの総和は10,000,001秒となります。
最後にExcel・VBAカテゴリ全体の滞在時間を重みの総和で割り算します。
ここでいう重みの総和はページビュー数の総和になりますね。
Excel・VBAを読んだ人は10,001人で、それらの滞在時間の合計が10,000,001秒ですから
10,000,001÷10,001＝999.9
これがExcel・VBAカテゴリの平均ページ滞在時間となります。
直帰率に関しても同様に加重平均を出しますと99.99％となります。
まとめ
ここまでで、カテゴリ別平均ページ滞在時間と直帰率には、いわゆる一般の平均すなわち算術平均を使わずに加重平均を使うべきということ、またその算出方法についてお伝えをしてきました。
平均には算術平均ではなくて、他の種類もあるということをまず心に留めておいていただけると嬉しいです。
今回紹介した加重平均のほかにも、いくつかあります。機会があればご紹介しますね。
また、平均ページ滞在時間や直帰率に関わらず、データを取り扱う際には重みを加味した加重平均をとったほうが良い場合がありますので、その点も覚えておいて頂きたいです。
実際にGoogleアナリティクスのデータを貼るだけで、カテゴリ別の平均ページ滞在時間と直帰率がパッと出るようになるまでには、まだいくつかのステップがあります。
次回は加重平均の算出も含めて使えるようになると便利なエクセル関数SUMPRODUCTについて紹介をします。
どうぞお楽しみにっ！
