アドバンスセグメントで擬似加重ソート(Excel のTable機能)

Google Analyticsだけに限らないTIPSですが、簡単でそれなりに実用的に使える方法だとおもうので、紹介します。

2010年の秋にGoogle Analyticsの加重並び替えが導入されました。Wikiの方の紹介。新機能として紹介されたに似た感じのものを、アドバンスセグメントでやろうとするものです。

ちょっとズルですが、APIでデータを取得するのが前提です。

APIでのデータの取得は、実は簡単で、

    1. Date Feed Explorerを使う(日本語だと止まるので、IDを直接入れる必要がある。)
    2. http://excellentanalytics.com/ を使う。
    3. //abc-analytics.com/data-feeds-query-explorer-in-windows-applicationを使う。

で出来ます。他にも、色々なツールがあります。

今回は、Cの自作ツールを使ってデータを取得しておきます。

閲覧開始ページ、キーワード、開始数、直帰数を取ります。

WS000033

で、タイトルの話のエクセルの貼り付けます。ここから、本題です。

コピペしたあとは、テーブルにします。

  • テーブル名を英語にします。大事です。
  • 一行目に数字を入れるので、空けておきます。

WS000034

もう少し、下ごしらえが続きます。

  • 直帰数になっているので、直帰率をいれます。
  • 一行目に集計値を出すようにします。
    • ここで、テーブル機能が行きます。

entrancesの集計値は、下図のようにススメます。

(テーブル名が英語だと補完が効いて、マウス無しで^^です)

WS000035 WS000036 WS000037

式は、=SUBTOTAL(9,table1[entrances]) になります。

同じようにbounces(直帰数)も計算します。=subtotal(9,table1[bounces]) ですね。

bounceRate(直帰率)は、この2つを割り算します。

ここで、もう一回、画像。

WS000038

ここから、本当の本題であった、加重ソートを入れます。

前提として、100回以上セッションがあった、キーワード+ページは、そのまま。ソレ以下のものを、全体の平均値と按分する方針です。

B1に 分かれ目の数字、100を入れておいて、TrueBounceRateの列を作りましょう。

WS000039

上の数式を説明します。

entrancesが100以上なら、その列のBounceRateのまま。なので [@BounceRate]

以下なら、全体の平均値(E1)と[@BounceRate]を 全体のEntrances(C1)と列のentrances([@enttances])で按分する。

([@bounces]/$B$1 * [@BounceRate] )   +   (1 – ([@bounces]/$B$1)) * $E$1

Googleの加重ソートは、たぶん似たような感じだと思う。

加重ソートはいろいろ本を読んだけど、理論的背景はよく理解できなかった。

2次元の正規分布の場合に、なんやからすると、上記のような単純な式でもOKという話だったと思うけど、よく理解できなかったので、公開レクチャーしれくれる人がいたらお願いします。@phar

で、ここまでは単に計算しただけど、ここからエクセルのテーブル機能が生きて来る。

ランディングページ単位のキーワードでの加重ソート、ある単語が含まれるキーワードデータの加重ソートとかが簡単にできる。

最初の画面で、

WS000040

landingを/api ディレクトリ以下のものに絞る。

WS000041

その後、TrueBouncecRateを昇順に。(自動で順列にならない、、、フィルターするたびに、並べ替えの必要がある。ここは、イケテナイ。)

WS000042

まあ、それでもそれっぽいソートが出来上がる。landingページを絞った上での、加重ソート。

今回は、ディメンジョンがキーワード、閲覧開始ページという組み合わせだけど、ソレは自分でデータを持ってくるときに好きに選べばいい。

また、加重平均の按分の中心になる平均値(直帰率)も、テーブルでフィルタリングすると、subtotalでそのフィルタリングされたデータの平均値で計算し直されるので、都合が良い。

あまり、データ数が少なくなるとだめだけど、そのデータ全体での平均値を適用して計算しなすのは、フィルタリング前のデータの平均値を持ち出すより適切なはずだ。

まとめ

google analyticsには、加重ソート機能がありますが、似たような事をエクセルでしました。

エクセルのテーブル機能を使うことにより、簡単に特定ディメンジョンの加重並び替え(条件は複数でもOK => アドバンスセグメント)ができることを図示しました。 これは、たぶん、今のレポート画面ではできないことだと思います。 ただし、並び替えのアルゴリズムは違うのでしょう。

冒頭のリンクにも 今回のようなことをやった記録があって(加重ソートが出始めたころに書いたやつ) 、実際の GAでの順番と比較したグラフがありますが、そんなにズレはないと思います。

そんなにってどんだけ? 主観です^^。前やったとき、順位相関とか計算してみたけど、それを用いるのが正しいのかさっぱり分からなかったし、数値も直感的に理解できなかったので、、、単純な加重ソートもGAのソートも、結果としてはそんなに変わらないと思いました。

確か、加重ソートをアドバンスセグメントでという話は、結構要望であったと思うので、擬似ですが、それなりに役に立つ作業工程の紹介だと思います。試してみてください。