BLOG ブログ

Googleアナリティクス「App + Webプロパティ」のBigQuery用サンプルSQLクエリ

はじめに

2019年夏頃にローンチされたGoogleアナリティクスの新バージョンである「App + Webプロパティ」では、無料版利用ユーザーであっても、BigQueryへのエクスポート機能が利用可能となっています(別途、従量課金制でBigQueryのストレージコスト・クエリコストが発生)。

BigQueryは、1つの列の中に、ネストしたテーブル(厳密には、レコードと表現)を保持することができる仕組みとなっており、App + Webプロパティがエクスポートするテーブルも、ネストしたデータ構造となっています。このように、ネストしたデータ構造のデータベースに対するSQLクエリは、少し癖があり、標準的なSQLに慣れ親しんだ人であっても、最初は戸惑うことが多いと思います。

本記事では、そのような、App + WebプロパティでエクスポートしたBigQueryスキーマで利用可能なサンプル用クエリを紹介します。

App + WebプロパティのBigQueryスキーマについて

App + WebプロパティのBigQueryデータは、基本的に1つのテーブルの中に全てのイベントデータを保有する形となっています。全部で68の列を持つテーブルとなっており、この中に全てのGoogleアナリティクスのデータが収められています。このテーブルの1行は、App + Webプロパティにおける1イベント(ヒット)の情報となっており、複数行のデータをまとめることで、セッション・ユーザーの行動データとなります。Googleアナリティクス360がエクスポートするBigQueryとは、1行がセッション単位になっていた点が異なります。

基本的なページビューデータを取得する例

実装内容によっては、他にも取得したい項目などあるかもしれませんが、ここでは基本的なページビューデータを取得する例を紹介します。ページビューデータとはいえ、その中には様々なデータが含まれており、データポータルの数式と組み合わせることで、「セッション数」や「ユーザー数」などの指標を取得することも可能です。

サンプルのSQLクエリ

使い方:

  1. 下記のSQLクエリの中から、最後の方にある{{ここをGCPプロジェクト名に置き換える}} {{ここをデータセット名に置き換える}}の箇所を自身のBigQueryの情報に置き換えて利用してください。
  2. サイトで利用している除外したいクエリパラメーターが存在する場合、「他に除外したいクエリパラメーターが存在する場合は、スペース区切りで続けて指定する」の箇所に追記してください。
  3. 下記のクエリでは、2020年1月のデータを取得しています。取得期間を変更する際はクエリサイズ・課金金額に注意しながら、「_TABLE_SUFFIX」の絞り込み条件を変更してください。

サンプルクエリ:


    /*
  URLから指定したクエリパラメーターを除去したURLを返す。クエリパラメーターは、スペース区切りで複数指定することが可能。
  */
CREATE TEMPORARY FUNCTION stripUrlParams(url STRING, queries STRING) RETURNS STRING LANGUAGE js as """
  if (url.indexOf('?') == -1) {
    return url;
  }
  var params = url.split('?')[1].split('&');
  params = params.filter(function(param) {
    var k = param.split('=')[0];
    return queries.split(' ').filter(function(x) {
      return x == k;
    }).length == 0;
  });
  if (params.length == 0) {
    return url.split('?')[0];
  }
  return url.split('?')[0] + '?' + params.join('&');
""";

SELECT
  *,
  CASE
    WHEN pageDuration IS NULL THEN 1
    ELSE 0
  END as exits,
  CASE
    WHEN pageDuration IS NULL AND entrances = 1 THEN 1
    ELSE 0
  END as bounces
FROM (
  SELECT
    *,
    TIMESTAMP_DIFF(LAG(timestamp) OVER (
      PARTITION BY clientId, sessionId
      ORDER BY timestamp DESC
    ), timestamp, SECOND) as pageDuration,
  FROM (
    SELECT
      PARSE_DATE('%Y%m%d', event_date) as date,
      TIMESTAMP_MICROS(event_timestamp) as timestamp,
      TIMESTAMP_MICROS(user_first_touch_timestamp) as firstVisitTimestamp,
      user_pseudo_id as clientId,
      (SELECT value.int_value FROM events.event_params WHERE key = 'ga_session_id') as sessionId,
      (SELECT CONCAT(user_pseudo_id, '_', SAFE_CAST(value.int_value as STRING)) FROM events.event_params WHERE key = 'ga_session_id') as clientIdAndSessionId,
      device.category as deviceCategory,
      (SELECT CASE WHEN value.int_value = 1 THEN 'New Visitor' ELSE 'Returning Visitor' END FROM events.event_params WHERE key = 'ga_session_number') as visitorType,
      geo.country as country,
      traffic_source.source as source,
      traffic_source.medium as medium,
      traffic_source.name as campaign,
      (SELECT
        -- 他に除外したいクエリパラメーターが存在する場合は、スペース区切りで続けて指定する
        stripUrlParams(value.string_value, 'fbclid utm_source utm_medium utm_campaign utm_content utm_term utm_id utm_referrer gclid yclid')
      FROM
        events.event_params
      WHERE
        key = 'page_location'
      ) as url,
      (SELECT value.string_value FROM events.event_params WHERE key = 'page_title') as title,
      IFNULL((SELECT value.int_value FROM events.event_params WHERE key = 'entrances'), 0) as entrances,
      1 as pageviews
    FROM
      `{{ここをGCPプロジェクト名に置き換える}}.{{ここをデータセット名に置き換える}}.events_*` as events
    WHERE
      _TABLE_SUFFIX BETWEN '20200101' AND '20200131'
      AND event_name = 'page_view'
  )
)

データポータルでの利用

データポータルでは、データソースとして、BigQueryコネクタの「カスタムクエリ」として、上記のSQLクエリを指定します。

今回のSQLクエリの中では、「セッション数」や「ユーザー数」の指標は含めていません。これらの値は、利用するディメンションによって個別に再計算を行う必要があるため、データポータルにおいて「計算フィールド」を用いることを想定しています。「セッション数」や「ユーザー数」はそれぞれ、「clientIdAndSessionId」「clientId」の「個別件数」をカウントすることで解決します。

同様に、「ページビュー / セッション」や「直帰率」「離脱率」「ページ滞在時間」「セッション平均時間」などの指標についてもデータポータルで計算フィールドを利用する必要があります。

まとめ

最近、App + Webプロパティの新機能ローンチが相次いでおり、Google内でも、旧GoogleアナリティクスをApp + Webプロパティに置き換えるための動きが活発になってきていることが読み取れます。
このApp + Webプロパティをフル活用するためには、無料版にも解放されたBigQuery連携を使いこなすことが求められてきます。App + WebプロパティのBigQueryに関する情報はまだまだ少ないので、ぜひ参考にしてみてください。

デジタルマーケティング戦略、Web解析、SEO、リスティング広告、Facebook広告、Linkedin広告、Tableauでのデータビジュアライズなどなど、何か弊社でお役に立てそうなことがございましたら、こちらよりお気軽にご相談ください。ご相談は無料で承っております。

プリンシプルでは業界最高レベルの専門家として一緒にご活躍いただける方を募集しています。詳しくは採用情報のページをご覧ください。

山田良太

テクノロジー開発室長。チーフテクノロジーマネージャー。10年以上のプログラミング経験を活かして、Webマーケティングのテクノロジー領域(APIを使ったシステム開発や、タグ実装など)を中心に取り組む。

ご意見・ご相談、料金のお見積もりなど、お気軽にお問合わせください

お問合せ メルマガ登録 ダウンロード資料一覧

TOP