BigQueryでGoogleアナリティクスの訪問回数ごとのユーザー分布を集計する

ユーザーが一定期間に何回訪問したかを集計し、訪問回数ごとのユーザー数の分布をだしてみた。

 Googleアナリティクスでは、ユーザー数や新規ユーザー数はわかりますが、訪問回数ごとのユーザー数を把握するにはやや手間がかかります。

今はユーザーエクスプローラにエクスポート機能がありますので、データをダウンロードの上、エクセルのピボットで訪問回数ごとにユーザー数をカウントする必要があります。

同じ事をBigQueryでやってしまおうということです。

①：BigQueryでユーザー単位でセッションをカウントする

まずは、ユーザー単位のセッション数を集計する必要があります。書き方はこうです。

　select
　fullvisitorid,
　visitId,
　count(visitId)　
　over(
　partition by fullvisitorid
　order by visitId
　rows between unbounded preceding and unbounded following) as session_count
　from `bigqueryプロジェクトID.データセット.ga_sessions_日付`
　order by 1,2
　;

fullvisitorid単位でvisitIdが何種類あるか集計するには、集計分析関数を使います。fullvisitoridでpartition（仕切りを作って）してその中のvisitIdの種類をカウントします。ウインドウフレーム句はfullvisitoridを一つの単位にしなければいけないので、上のように書く必要があります。

3行目のvisitIdは最終的になくてもいいのですが、この状態でクエリを回していただくと、session_countの列にはfullvisitorid単位でvisitIdが何種類あるか、その数が入っていることがわかります。

②：①で作った表をサブクエリとして使う

①のクエリを実行すると、このような表を返してくれます。

2行目と3行目は同じfullvisitoridで、2種類のvisitIdを持っていますのでsession_countが「2」となっており、意図通りの結果が得られています。（データは一般公開のGAサンプルデータ）

ただ、ここまでは、あるユーザーが何回訪問したかという結果をかえすだけですので、さらにそれを集計する必要があります。

①をサブクエリとして使い、fullvisitoridをカウントし、session_countでグループ化します。

　select
　session_count,
　count(distinct fullvisitorid) as users
　from
　(select
　fullvisitorid,
　visitId,
　count(visitId)　
　over(
　partition by fullvisitorid
　order by visitId
　rows between unbounded preceding and unbounded following) as session_count
　from `bigqueryプロジェクトID.データセット.ga_sessions_日付`
　where totals.visits is not null
　order by 1,2)
　group by 1
　order by 1
　;

 このクエリをまわすと、期間中に1セッションのユーザーがどれだけで、2セッションのユーザーはどれだけか・・・という表が取り出せます。

 ここにサイトのゴール（購買、申し込み等）を加えればさらに面白いことになるのですが、それはまた次回。