山田良太・チーフテクノロジーマネージャーエンジニアリングはじめに2019年夏頃にローンチされたGoogleアナリティクスの新バージョンである「Googleアナリティクス4プロパティ」では、無料版利用ユーザーであっても、BigQueryへのエクスポート機能が利用可能となっています(別途、従量課金制でBigQueryのストレージコスト・クエリコストが発生)。BigQueryは、1つの列の中に、ネストしたテーブル(厳密には、レコードと表現)を保持することができる仕組みとなっており、Googleアナリティクス4プロパティがエクスポートするテーブルも、ネストしたデータ構造となっています。このように、ネストしたデータ構造のデータベースに対するSQLクエリは、少し癖があり、標準的なSQLに慣れ親しんだ人であっても、最初は戸惑うことが多いと思います。本記事では、そのような、Googleアナリティクス4プロパティでエクスポートしたBigQueryスキーマで利用可能なサンプル用クエリを紹介します。Googleアナリティクス4プロパティのBigQueryスキーマについてGoogleアナリティクス4プロパティのBigQueryデータは、基本的に1つのテーブルの中に全てのイベントデータを保有する形となっています。全部で68の列を持つテーブルとなっており、この中に全てのGoogleアナリティクスのデータが収められています。このテーブルの1行は、Googleアナリティクス4プロパティにおける1イベント(ヒット)の情報となっており、複数行のデータをまとめることで、セッション・ユーザーの行動データとなります。Googleアナリティクス360がエクスポートするBigQueryとは、1行がセッション単位になっていた点が異なります。実装内容によっては、他にも取得したい項目などあるかもしれませんが、ここでは基本的なページビューデータを取得する例を紹介します。ページビューデータとはいえ、その中には様々なデータが含まれており、データポータルの数式と組み合わせることで、「セッション数」や「ユーザー数」などの指標を取得することも可能です。{{ここをデータセット名に置き換える}}の箇所を自身のBigQueryの情報に置き換えて利用してください。サイトで利用している除外したいクエリパラメーターが存在する場合、「他に除外したいクエリパラメーターが存在する場合は、スペース区切りで続けて指定する」の箇所に追記してください。下記のクエリでは、2020年1月のデータを取得しています。取得期間を変更する際はクエリサイズ・課金金額に注意しながら、「_TABLE_SUFFIX」の絞り込み条件を変更してください。URLから指定したクエリパラメーターを除去したURLを返す。クエリパラメーターは、スペース区切りで複数指定することが可能。))データポータルでの利用データポータルでは、データソースとして、BigQueryコネクタの「カスタムクエリ」として、上記のSQLクエリを指定します。今回のSQLクエリの中では、「セッション数」や「ユーザー数」の指標は含めていません。これらの値は、利用するディメンションによって個別に再計算を行う必要があるため、データポータルにおいて「計算フィールド」を用いることを想定しています。「セッション数」や「ユーザー数」はそれぞれ、「clientIdAndSessionId」「clientId」の「個別件数」をカウントすることで解決します。ページ滞在時間」「セッション平均時間」などの指標についてもデータポータルで計算フィールドを利用する必要があります。まとめ最近、Googleアナリティクス4プロパティの新機能ローンチが相次いでおり、Google内でも、旧GoogleアナリティクスをGoogleアナリティクス4プロパティに置き換えるための動きが活発になってきていることが読み取れます。このGoogleアナリティクス4プロパティをフル活用するためには、無料版にも解放されたBigQuery連携を使いこなすことが求められてきます。Googleアナリティクス4プロパティのBigQueryに関する情報はまだまだ少ないので、ぜひ参考にしてみてください。デジタルマーケティング戦略、Web解析、SEO、リスティング広告、Facebook広告、Linkedin広告、Tableauでのデータビジュアライズなどなど、何か弊社でお役に立てそうなことがございましたら、こちらよりお気軽にご相談ください。ご相談は無料で承っております。プリンシプルでは業界最高レベルの専門家として一緒にご活躍いただける方を募集しています。詳しくは採用情報のページをご覧ください。AnalyticsSQL山田良太テクノロジー開発室長。チーフテクノロジーマネージャー。