Bigqueryで集計したGoogleアナリティクスのセッション数とGAのUI上のセッション数が合わない

BigqueryでGoogleアナリティクスのトラフィック分析だ！

自らのスキルアップのためにもSQLを使えるようになってBigqueryで分析するぞ！と思い、オンライン講座でSQLの基本的なことを学びBigqueryを使い始めました。

当然、使う上でいろいろ分からないことがでてくるわけですが、意外とBigqueryを使ったGoogleアナリティクスのトラフィック分析事例やSQLの書き方についての情報が少なく、書き留めようと思った次第です。

で、早速壁にあたりました。

Bigqueryで集計したGoogleアナリティクスのセッション数とGAのUI上のセッション数が合わない・・・

手始めにセッション数を出そうと思い、書いたSQLがこちら

　select
　count(visitId)
　from `bigqueryプロジェクトID.データセット.ga_sessions_日付`
　;

 visitid はBigQuery Export のスキーマによると「セッションのID」とあるので、これをカウントすればセッション数になると考えていました。

が、出した数字がGAの数字と違うのです。なんで。。。

いろいろ考えた挙句、スキーマで見たtotals.visitsをsumすることにしました。

　select
　sum(totals.visits)
　from `bigqueryプロジェクトID.データセット.ga_sessions_日付`
　;

すると、ビンゴでした！

やった！！と思ったのですが、ということはvisitidにはセッション数に含まれていないidが存在していることになります。

そこで、今後はvisitidをcountしてtotals.visitsでgroup byしてみました。

 見てみると、1行目の数字がGAのUI上のセッション数と合致していたのです。先程の仮設通り、visitidにはセッションに含まれないものが存在していると思われます。

GAのイベントを多用しているので、イベントだけ発生した？visitidがあるのかもしれません。

まっ、早い話、セッション数を集計するにはtotals.visitsをsumすればよかったのですが、visitidを使うならこのような書き方になると思います。

　select
　count(visitId)
　from `bigqueryプロジェクトID.データセット.ga_sessions_日付`
　where totals.visits is not null
　;

 遠回りしましたが、visitidの扱いにも今後気を付けないといけないことが分かりました。。

お読みいただきありがとうございました。ただ、私はマーケターですので、SQLは初心者です。誤っているかもしれませんので、十分検証はお願い致します。

ちなみに、SQLはudemy の「BigQueryで学ぶ非エンジニアのための SQL データ分析入門」で覚えました。