BigQueryでGoogleアナリティクスのセッションの間隔をだす

Googleアナリティクス上ではユーザーあたりのセッション数はでていますが、セッション間の間隔は集計されていませんのでそれを出したいと思います。

　select
　round(avg(interval_session),2) as avg_interval_session
　from
　(select
　fullvisitorid,
　parse_date('%Y%m%d', date) as date,
　lead(parse_date('%Y%m%d', date))
　over(
　partition by fullvisitorid
　order by date desc) as date2,
　date_diff(parse_date('%Y%m%d', date),
　lead(parse_date('%Y%m%d', date))
　over(
　partition by fullvisitorid
　order by date desc),day) as interval_session
　from `{bigqueryプロジェクトID}.{データセット}.{ga_sessions_*}`
　where _table_suffix between 'yyyymmdd' and 'yyyymmdd')
　;

 Googleアナリティクスがら送られたdateは文字列になっているので、PARSE_DATE関数を使ってdateを文字列からDATE型に変換しています。

セッション日の間隔を取るためにはLEAD関数を使い、日付を降順にして前のセッションの日付を取得しdate2というカラムに格納しています。

そして、DATE_DIFF関数でdateとdate2の差を取っています。ここまでのクエリを実行するとこのような表になります。（一般公開のGoogleアナリティクスデータを使用して、スプレッドシートに書き出したものです）

最上部のピンク色のユーザーは7/3と7/1に訪問があり、LEAD関数によってdate2に7/1が格納されています。そしてDATE_DIFF関数でその差分の2が計算できています。青のユーザーはセッション日が同じでしたので差は0です。塗りつぶしていないユーザーは1セッションのみでしたのでdate2はnullになっています。

そして、interval_sessionの平均を計算すればセッションの間隔の平均を求めることができます。AVG関数はnullを無視しますので、問題なく平均を求めることができます。