BigQueryで相関係数を出す

BigQueryにはいくつか統計集計関数がサポートされていますが、CORR関数を使って相関係数をだしたいと思います。

「BigQueryでサンプルデータを使う」のエントリーで紹介したGoogleアナリティクスのサンプルデータを使って、CVRと直帰率の相関係数を出してみます。

　select
　corr(cvr,bounce_rate) as correlation
　from
　(select
　date,
　sum(totals.transactions) / sum(totals.visits) as cvr,
　sum(totals.bounces) / sum(totals.visits) as bounce_rate,
　from `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`
　group by 1)
　;

 サブクエリ内にCVRと直帰率を日別に集計します。一般公開のGoogleアナリティクスのテーブルをクエリするとFROM句のプロジェクトIDとデータセットは上のようになっていると思います。期間は2017年7月1日から7月31日までを範囲にしています。「201707*」のようにワイルドカード記号を使うと、2017年7月すべてのテーブルを指定してくれるので便利です。2017年中のすべてのテーブルを対象にしたいなら「2017*」となります。

上のクエリを実行するとこのような結果を返してくれます。

-0.84で負の相関が強いということが分かります。一方の数字が大きければ、もう一方の数字が小さいことを意味していますが、この場合では直帰率が低いほどCVRが高いということを意味しています。

ただ、相関係数と言われても。。という場合は散布図を書くと関係性がよりイメージしやすくなります。データポータルを使えば、BigQueryのテーブルを参照できる上に、散布図も容易に作成できます。

この図を見れば、直帰率が低いとCVRは高く、直帰率が高いとCVRは低い傾向にあることがより分かりやすくなります。お試しください。

注１：データポータルでBigQueryのテーブルを参照すると、その度にクエリを回すことになり課金対象となります。