Googleアナリティクス4プロパティのBigQuery用サンプルSQLクエリ

    /*
  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 BETWEEN '20200101' AND '20200131'
      AND event_name = 'page_view'
  )
)