Google Analytics4(GA4)のBigqueryでの集計

概要

社内システムの計測にGoogle Analytics 4(GA4)を利用することになった。
GA4では有料プラン(360)でなくても、BigQueryにデータを出力可能であるため、早速、BigQueryで集計してみた。

集計サンプル

1. ページビュー数

ページビュー数を集計する。

GA4のデータは日別にテーブルが分かれますが、_TABLE_SUFFIXを活用することで、対象テーブル指定することができます。
_TABLE_SUFFIXはFROM句のテーブル名の一部を*とすることで、該当箇所がWHERE句で抽出することが可能です。

SELECT
  DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS hitdate,
  COUNT(1) AS pageviews
FROM
   `tablename.analytics_252000944.events_intraday_*` 
WHERE
  _TABLE_SUFFIX BETWEEN  '20201101' AND '20201123' 
  AND 
  event_name = 'page_view'
GROUP BY 1
ORDER BY 1

2. ページビューの生データの確認

ページビューの生データを確認する。

SELECT
  Date(timestamp_micros(event_timestamp), "Asia/Tokyo") AS hitdate,
  timestamp_micros(event_timestamp) AS Timestamp,
  FORMAT_TIMESTAMP("%H", timestamp_micros(event_timestamp)) as hour,
  FORMAT_TIMESTAMP("%M", timestamp_micros(event_timestamp)) as min,
  FORMAT_TIMESTAMP("%S", timestamp_micros(event_timestamp)) as sec
FROM
   `tablename.analytics_252000944.events_intraday_*` 
WHERE
  _TABLE_SUFFIX BETWEEN  '20201101' AND '20201123' 
  AND 
  event_name = 'page_view'

3. セッション数

セッション数を集計する。

WITH
  t1 AS (
  SELECT
    DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS hitdate,
    CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING)) AS sid,
  FROM
    `tablename.analytics_252000944.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20201101'
    AND '20201123'
    AND event_name = 'session_start' )
SELECT
  hitdate,
  COUNT(DISTINCT sid) AS sessions
FROM t1
GROUP BY 1
ORDER BY 1

データポータルのカスタムクエリ

データポータルでBigqueryのクエリを表示するための方法。
分単位でグラフを表示したいという要件があったため、作成した。
URLパラメータのname別に集計する。

データポータルでは日付までは標準のフィルタ機能で可能であるが、時間以下はフィルタできないため、
時間の列を作成し、その列でフィルタを掛ける必要がある。

SELECT 
  count(event_timestamp) as PV,
  DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS event_date,
　 　FORMAT_TIMESTAMP("%Y", timestamp_micros(event_timestamp),  'Asia/Tokyo') as event_year,
　 　FORMAT_TIMESTAMP("%m", timestamp_micros(event_timestamp),  'Asia/Tokyo') as event_month,
　 　FORMAT_TIMESTAMP("%H", timestamp_micros(event_timestamp),  'Asia/Tokyo') as event_hour,
　 　FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:00", timestamp_micros(event_timestamp),  'Asia/Tokyo') as event_ymdhm,
  REGEXP_EXTRACT(event_params.value.string_value, r'.*name=(.*)') as name
 FROM
 　`robust-index-303904.analytics_261143180.events_*`,
 UNNEST(event_params) event_params
where
  _TABLE_SUFFIX BETWEEN  @DS_START_DATE AND @DS_END_DATE  and
event_name = 'page_view'and
event_params.key = 'page_location' and 
(event_params.value.string_value = 'https://www.hoge.jp/?name=top' OR
event_params.value.string_value like '%www.hoge.jp/?name=%')
group by 
event_date, event_year, event_month, event_hour, event_ymdhm, name

SELECT 
  count(event_timestamp) as PV,
  DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS event_date,
  EXTRACT(HOUR FROM timestamp_micros(event_timestamp)) as event_hour,
  EXTRACT(MINUTE FROM timestamp_micros(event_timestamp)) as event_minute
 FROM
 　`robust-index-303904.analytics_261143180.events_*`,
 UNNEST(event_params) event_params
where
  _TABLE_SUFFIX BETWEEN  '20210319' AND '20210320' and
event_name = 'page_view'and
event_params.key = 'page_location' and 
event_params.value.string_value = 'https://www.hogehoge.com/'
group by 
event_date, event_hour, event_minute
LIMIT 10

時間別にPVを計測する。

SELECT 
  DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo')  AS event_date,
 timestamp_micros(event_timestamp)  AS event_timestamp,
 FORMAT_TIMESTAMP("%Y-%m-%d %H%M", timestamp_micros(event_timestamp),  'Asia/Tokyo') as event_dhm,
 FORMAT_TIMESTAMP("%H%M", timestamp_micros(event_timestamp), 'Asia/Tokyo') as event_hm,
  EXTRACT(HOUR FROM timestamp_micros(event_timestamp)) as event_hour,
  EXTRACT(MINUTE FROM timestamp_micros(event_timestamp)) as event_minute
 FROM
 　`robust-index-303904.analytics_261143180.events_*`,
 UNNEST(event_params) event_params
where
  _TABLE_SUFFIX BETWEEN '20210319' AND '20210319' and
event_name = 'page_view'and
event_params.key = 'page_location' and 
event_params.value.string_value = 'https://www.hoge.com/'
LIMIT 10

データポータルから

終わりに

無料プランでもBigQueryに出力できるのは良いですね