GA360のデータをBigQueryで再現する

はじめに

• デイリーでGA360からBigQueryにデータをエクスポートしている方向けの記事になります。
• 再現できているかどうかは、GA360の非サンプリングレポートを使い、数字が一致するかどうかで判定しております。
• 再現方法が分かり次第、随時更新していきます。
• もし他のメトリクスやディメンションの再現方法をご存じの方がいましたら、コメントいただけますと幸いです。

PV,UU,セッション数

WITH
  query_results AS (
    SELECT 
      fullVisitorId
      , visitStartTime
      , hits.page.pagePath
      , hits.type
      , totals.visits
      , MIN(hits.hitNumber) OVER(PARTITION BY fullVisitorId, visitStartTime) AS first_hitNumber
    FROM
      `project_name.dataset_name.ga_sessions_*`, UNNEST(hits) AS hits
    WHERE
      _TABLE_SUFFIX BETWEEN '20210801' 
      AND '20210807'
  )

SELECT 
  -- GA360でいうユーザー
  COUNT(DISTINCT fullVisitorId) AS unique_user

  -- GA360でいうページビュー数
  , COUNT(CASE WHEN type = 'PAGE' THEN pagePath ELSE NULL END) AS page_view

  -- GA360でいうセッション数
  , COUNT(DISTINCT CASE WHEN visits = 1 AND hitNumber = first_hitNumber THEN CONCAT(fullVisitorId, visitStartTime) ELSE NULL END) AS unique_session
FROM
  query_results

直帰数、直帰率

WITH
  query_results AS (
    SELECT 
      fullVisitorId
      , visitStartTime
      , totals.visits
      , totals.bounces
      , MIN(hits.hitNumber) OVER(PARTITION BY fullVisitorId, visitStartTime) AS first_hitNumber
      , MIN(IF(hits.isInteraction IS NOT NULL, hits.hitNumber, 0)) OVER(PARTITION BY fullVisitorId, visitStartTime) AS first_interactionHitNumber
    FROM
      `project_name.dataset_name.ga_sessions_*`, UNNEST(hits) AS hits
    WHERE
      _TABLE_SUFFIX BETWEEN '20210801' 
      AND '20210807'
  )

SELECT 
  -- GA360でいうセッション数
  COUNT(DISTINCT CASE WHEN visits = 1 AND hitNumber = first_hitNumber THEN CONCAT(fullVisitorId, visitStartTime) ELSE NULL END) AS unique_session

  -- GA360でいう直帰数
  , COUNT(DISTINCT CASE WHEN visits = 1 AND hitNumber = first_interactionHitNumber AND bounces = 1 THEN CONCAT(fullVisitorId, visitStartTime) ELSE NULL END) AS bounce 

  -- GA360でいう直帰率 = 直帰数 / セッション数
  , SAFE_DIVIDE(COUNT(DISTINCT CASE WHEN visits = 1 AND hitNumber = first_interactionHitNumber AND bounces = 1 THEN CONCAT(fullVisitorId, visitStartTime) ELSE NULL END), COUNT(DISTINCT CASE WHEN visits = 1 AND hitNumber = first_hitNumber THEN CONCAT(fullVisitorId, visitStartTime) ELSE NULL END)) AS bounce_rate 
FROM
  query_results

離脱数,離脱率

WITH
  query_results AS (
    SELECT 
      fullVisitorId
      , visitStartTime
      , hits.page.pagePath
      , hits.type
      , hits.isExit
    FROM
      `project_name.dataset_name.ga_sessions_*`, UNNEST(hits) AS hits
    WHERE
      _TABLE_SUFFIX BETWEEN '20210801' 
      AND '20210807'
  )

SELECT 
  -- GA360でいうページビュー数
  COUNT(CASE WHEN type = 'PAGE' THEN pagePath END) AS page_view

  -- GA360でいう離脱数
  , COUNT(CASE WHEN type = 'PAGE' AND isExit = TRUE THEN pagePath END) AS exit_page_view

  -- GA360でいう離脱率 = 離脱数 / ページビュー数
  , SAFE_DIVIDE(COUNT(CASE WHEN type = 'PAGE' AND isExit = TRUE THEN pagePath END), COUNT(CASE WHEN type = 'PAGE' THEN pagePath END)) AS exit_rate
FROM
  query_results

セッション開始日時,ヒット日時

• 実際は、日付や時間帯といったディメンションですが、扱いやすくするためにdatetimeで出力しています。

SELECT 
  DATETIME(TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS session_begin_datetime
  , DATETIME(TIMESTAMP_SECONDS(visitStartTime + SAFE_CAST(SAFE_DIVIDE(hits.time, 1000) AS INT64)), 'Asia/Tokyo') AS hit_datetime
FROM 
  `project_name.dataset_name.ga_sessions_*`, UNNEST(hits) AS hits
WHERE 
  _TABLE_SUFFIX BETWEEN '20210801' 
  AND '20210807'

初回閲覧ページ

• hits.hitNumber = 1にしてしまうと、場合によってはヒットタイプがPAGE以外ヒットデータが該当してしまうため、hits.isEntrance = TRUEで絞り込むようにしています。

SELECT 
  hits.page.pagePath
FROM 
  `project_name.dataset_name.ga_sessions_*`, UNNEST(hits) AS hits
WHERE 
  _TABLE_SUFFIX BETWEEN '20210801' 
  AND '20210807'
  AND hits.type = 'PAGE'
  AND hits.isEntrance = TRUE