Google Analytics 4 + BigQueryでよく使う基本的なSQL例

こんにちは、エクスチュアの權泳東(権泳東/コン・ヨンドン)です。

Google Analytics 4 (以下GA4)がリリースされましたね。
というわけでGA4のBigQueryエクスポート機能を使って早速データを抽出してみましょう！

以前、GA360+BigQueryで私がよく使う基本的なSQL例を紹介しましたが、今回はそれのGA4バージョンです。

1. ページビュー数

GA4の日別ページビューを出してみます。

タイムゾーンを任意で変更したいので、event_dateカラムではなくevent_timestampを「Asia/Tokyo」にして使ってます。
それがメンドクサイならevent_dateでもOK。
event_name = ‘page_view’ に絞るとページビューになる。
※アプリのスクリーンビューは’screen_view’を使う

SELECT
  DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS hitdate,
  COUNT(1) AS pageviews
FROM
  `analytics_12345678.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20201001'
  AND '20201031'
  AND event_name = 'page_view'
GROUP BY 1
ORDER BY 1

結果はこうなります。

日別ページビュー数

なお、ページURL別にページビューを出すならこうです。

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
  COUNT(1) AS pageviews
FROM
  `analytics_12345678.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20201001' and '20201031'
  AND event_name = 'page_view'
GROUP BY 1
ORDER BY 2 DESC

ページURLは、event_params.key が「page_location」になっている行のstring_value列に入ってます。
event_paramsはネストされたRECORD型のカラムになってるので、UNNEST関数でフラットにしてあげる必要があります。
以前書いた、「GoogleAnalytics Apps+Web プロパティをBigQueryと連携したついでにフラット化する」の記事を参考。

結果ははこうなります。

ページ別PV数

2. セッション数

user_pseudo_id と event_params.key=ga_session_idのint_valueを文字列にしたものをCONCATして、ユニーク数をカウントしたのがセッション数。
これも日別で出します。

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
    `analytics_12345678.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20201001'
    AND '20201031'
    AND event_name = 'session_start' )
SELECT
  hitdate,
  COUNT(DISTINCT sid) AS sessions
FROM t1
GROUP BY 1
ORDER BY 1

結果はこうなります。

日別セッション数

なお、Webとアプリ両方でUserIDを使ってる場合は user_pseudo_id の代わりに user_id を使えばクロスデバイスで同じユーザーとして扱えます。

3. ユーザー数

user_pseudo_id のユニーク数をカウントするとユーザー数。
下記はhitdateでGROUP BYしてるので、日別のユーザー数を出してます。

SELECT
  DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS hitdate,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM
  `analytics_12345678.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20201001'
  AND '20201031'
  AND event_name = 'session_start'
GROUP BY 1
ORDER BY 1

結果はこうなります。

日別ユーザー数

4. ページ別ランディング数・離脱数・直帰数

先程出したセッションIDを使います。
これをevent_timestampを昇順ソートして、一番目のページがランディングページ。
逆にevent_timestampを降順ソートしたら、一番目のページが離脱ページ。
そしてセッション中に見たページの数が「1」なら直帰。
event_name = page_viewで絞ってます。

WITH
  t1 AS (
  SELECT
    event_timestamp,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
    CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING)) AS sid
  FROM                     
    `analytics_12345678.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20201001' and '20201031'
  AND event_name = 'page_view'
  )
  ,t2 AS (
  SELECT
    page_location,
    CASE
      WHEN ROW_NUMBER() OVER(PARTITION BY sid ORDER BY  event_timestamp) = 1 THEN 1
      ELSE 0
    END AS is_entry,
    CASE
      WHEN ROW_NUMBER() OVER(PARTITION BY sid ORDER BY  event_timestamp DESC) = 1 THEN 1
      ELSE 0
    END AS is_exit,
    CASE
      WHEN COUNT(1) OVER(PARTITION BY sid) = 1 THEN 1
      ELSE 0
    END AS is_single
  FROM
    t1
  )
SELECT
  t2.page_location AS page_location,
  SUM(t2.is_entry) AS entry,
  SUM(t2.is_exit) AS exit,
  SUM(t2.is_single) AS singlevisit
FROM t2
GROUP BY 1
ORDER BY 2 DESC

結果はこうなります。

ランディング数・離脱数・直帰数

ただし、この書き方だと集計期間を跨いでる訪問の場合には集計期間中の最初に見たページがランディングページになるけどキニシナイ

5. ページ別合計滞在時間・平均滞在時間

これもセッションIDを使う。
ページ別に次ページとのタイムスタンプの差分を合算した秒数をページ別トータル滞在時間として抽出。
次ページとのタイムスタンプ差が1800秒以上あるものはセッションが切れてるものとして除外する。
滞在時間の合計を、ページのセッション数で割れば平均滞在時間になる。
event_name = page_viewに絞ってないので、スクロールイベントや離脱クリックなどのエンゲージイベントも滞在時間に含まれます。

WITH t1 AS (
  SELECT
    timestamp_micros(event_timestamp) AS event_timestamp,
    CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING)) AS sid,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location
  FROM                     
    `analytics_12345678.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20201001' and '20201031'
)
,t2 AS (
  SELECT
    event_timestamp,
    sid,
    page_location,
    CASE
      WHEN timestamp_diff(LEAD(event_timestamp) OVER(PARTITION BY sid ORDER BY event_timestamp), event_timestamp, second) > 1800 THEN NULL
      ELSE LEAD(event_timestamp) OVER(PARTITION BY sid ORDER BY event_timestamp) END AS next_hit
  FROM t1
)
SELECT
  t2.page_location,
  COUNT(DISTINCT t2.sid) AS sessions,
  SUM(IFNULL(TIMESTAMP_DIFF(t2.next_hit, t2.event_timestamp, second), 0)) AS total_time,
  ROUND(SAFE_DIVIDE(SUM(IFNULL(TIMESTAMP_DIFF(t2.next_hit, t2.event_timestamp, second), 0)), COUNT(DISTINCT t2.sid)), 2) AS avg_time
FROM t2
WHERE t2.page_location IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC

結果はこうなります。
滞在時間の単位は「秒」です。

合計滞在時間・平均滞在時間

6. イベントパラメータの集計

サイト内検索ワードをイベントパラメータで計測してるという前提です。
view_search_resultsイベントが発生した時に、検索ワードがevent_params.key = search_term の string_value列に入るので、それを集計します。

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'search_term') AS int_search_term,
  COUNT(1) AS int_search
FROM
  `analytics_12345678.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20201001' and '20201031'
  AND event_name = 'view_search_results'
GROUP BY 1
ORDER BY 2 DESC

結果はこうなります。

サイト内検索ワード

event_name と event_paramsのキー名はイベント内容に合わせて書き換えてください。

7. ユーザープロパティの集計

ユーザープロパティは event_params と同じく縦持ちのレコードなのでUNNEST関数でフラットにする必要があります。
ユーザープロパティの値別にユーザー数を出してみます。

SELECT
  (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'weather') AS weather,
  COUNT(distinct user_pseudo_id) AS users
FROM
  `analytics_12345678.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20201001' and '20201031'
  AND event_name = 'user_engagement'
GROUP BY 1
ORDER BY 2 DESC

結果はこうなります。