BigQueryでよく使うクエリたち（GAログ）

#GAログをBigQueryに転送したあとに集計するクエリ集

##ページビュー集計

SELECT
  DATE(TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)), 'Asia/Tokyo') AS hitdate
  ,COUNT(1) AS pageviews
FROM
  `ga_sessions_*`, 
  UNNEST(hits) AS hits
WHERE
  _TABLE_SUFFIX between  '20210101' AND '20211231'
  AND hits.type = 'PAGE'
GROUP BY 1
ORDER BY 1

##セッション数集計

SELECT
  DATE(TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)), 'Asia/Tokyo') AS hitdate
  ,COUNT(DISTINCT CONCAT(fullVisitorId, SAFE_CAST(visitNumber AS string))) AS sessions
FROM
  `ga_sessions_*`, 
  UNNEST(hits) AS hits
WHERE
  _TABLE_SUFFIX between  '20210101' AND '20211231'
GROUP BY 1
ORDER BY 1

##ユーザー数集計

SELECT
  DATE(TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)), 'Asia/Tokyo') AS hitdate
  ,COUNT(DISTINCT fullVisitorId) AS users
FROM
  `ga_sessions_*`, 
  UNNEST(hits) AS hits
WHERE
  _TABLE_SUFFIX between  '20210101' AND '20211231'
GROUP BY 1
ORDER BY 1

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

WITH t1 AS (
  SELECT
    hits.page.pagePath AS pagePath,
    CASE WHEN isEntrance THEN 1 ELSE 0
    END AS is_ent,
    CASE WHEN isExit THEN 1 ELSE 0
    END AS is_exit,
    CASE WHEN totals.bounces = 1 THEN 1 ELSE 0
    END AS is_bounce
    FROM
  `ga_sessions_*`, 
      UNNEST(hits) AS hits
  WHERE
    _TABLE_SUFFIX between  '20210101' AND '20211231'
)
SELECT
  t1.pagePath,
  SUM(t1.is_ent) AS entrance,
  SUM(t1.is_exit) AS exit,
  SUM(t1.is_bounce) AS bounce
FROM t1
GROUP BY 1
ORDER BY 2 DESC

##ページ別滞在時間

WITH t1 AS (
  SELECT
    TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)) AS hit_timestamp,
    CONCAT(fullVisitorId, SAFE_CAST(visitNumber AS string)) as sid,
    hits.page.pagePath AS pagePath
    FROM
  `ga_sessions_*`, 
      UNNEST(hits) AS hits
  WHERE
    _TABLE_SUFFIX between  '20210401' AND '20210408'
    AND hits.isInteraction = true
),
t2 AS (
    SELECT
      hit_timestamp,
      sid,
      pagePath,
      CASE WHEN timestamp_diff(LEAD(hit_timestamp) OVER(PARTITION BY sid ORDER BY hit_timestamp), hit_timestamp, second) > 1800 THEN NULL
      ELSE LEAD(hit_timestamp) OVER(PARTITION BY sid ORDER BY hit_timestamp) END AS next_hit
    FROM t1
)
SELECT
  t2.pagePath,
  COUNT(DISTINCT t2.sid) AS visits,
  SUM(IFNULL(TIMESTAMP_DIFF(t2.next_hit, t2.hit_timestamp, second), 0)) AS totaltimespent,
  ROUND(SAFE_DIVIDE(SUM(IFNULL(TIMESTAMP_DIFF(t2.next_hit, t2.hit_timestamp, second), 0)), COUNT(DISTINCT t2.sid)), 2) AS avgtimespent
FROM t2
WHERE t2.pagePath IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC

##カスタムディメンションの集計

SELECT
  (SELECT value FROM UNNEST(t.customDimensions) WHERE index = 4) AS dimension4, 
  (SELECT value FROM UNNEST(hits.customDimensions) WHERE index = 5) AS dimension5, 
  COUNT(DISTINCT CONCAT(fullVisitorId, SAFE_CAST(visitNumber AS string))) AS sessions
FROM
  `ga_sessions_*`  as t, 
  UNNEST(hits) AS hits
WHERE
    _TABLE_SUFFIX between  '20210401' AND '20210408'
GROUP BY 1, 2
ORDER BY 3 DESC

##流入元（UTMパラメータごと）の集計

WITH t1 AS (
  SELECT
    hits.page.pagePath AS pagePath,
    trafficSource.source source,
    trafficSource.medium medium,
    CASE WHEN isEntrance THEN 1 ELSE 0
    END AS is_ent,
    CASE WHEN isExit THEN 1 ELSE 0
    END AS is_exit,
    CASE WHEN totals.bounces = 1 THEN 1 ELSE 0
    END AS is_bounce
    FROM
  `ga_sessions_*`, 
      UNNEST(hits) AS hits
  WHERE
    _TABLE_SUFFIX between  '20210401' AND '20210408'
)
SELECT
  t1.pagePath,
  concat(t1.source," / ", t1.medium ) as source_medium
  SUM(t1.is_ent) AS entrance,
  SUM(t1.is_exit) AS exit,
  SUM(t1.is_bounce) AS bounce
FROM t1
GROUP BY 1
ORDER BY 2 DESC