GA 360 Suite

Google Analytics 360 + BigQueryでよく使うSQL例 6選

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

今回は、私がよくBigQueryで使ってるGoogle Analytics 360用のSQL例を紹介します。
以前書いたAdobe Analytics編のGA360版です。

前提条件

  • 当然ながらGA360のデータをBigQueryにエクスポートしてる事。

ここでは、パブリックデータセットの bigquery-public-data:google_analytics_sample と google.com:analytics-bigquery:LondonCycleHelmet を使って説明します。

1. ページビュー数

日別で出します。
タイムゾーンを任意で変更したいので、dateカラムではなくヒットタイムスタンプをAsia/Tokyoにする。
hits.type = ‘PAGE’ に絞るとページビューになる。
hitsはネストされたRECORD型のカラムになってるので、UNNEST関数でフラットにしてあげる必要があります。
以前書いた、BigQuery: Google Analytics 360のネストされたデータをフラット変換するSQLを参考。

SELECT
  DATE(TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)), 'Asia/Tokyo') AS hitdate,
  COUNT(1) AS pageviews
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`, 
  UNNEST(hits) AS hits
WHERE
  DATE(TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)), 'Asia/Tokyo')  >= '2017-07-01'
  AND DATE(TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)), 'Asia/Tokyo') < '2017-08-01'
  AND hits.type = 'PAGE'
GROUP BY 1
ORDER BY 1

なお、サイト全体のPVならtotals.pageviewsの数をSUM関数で合算する方法もあります。
しかしページ別に出したりするならばhitsレコードをUNNESTする事になるので、私は上記SQLをテンプレにしてます。
 

2. セッション数

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

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
  `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`, 
  UNNEST(hits) AS hits
WHERE
  DATE(TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)), 'Asia/Tokyo')  >= '2017-07-01'
  AND DATE(TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)), 'Asia/Tokyo') < '2017-08-01'
GROUP BY 1
ORDER BY 1

 

3. ユーザー数

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

SELECT
  DATE(TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)), 'Asia/Tokyo') AS hitdate,
  COUNT(DISTINCT fullVisitorId) AS users
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`, 
  UNNEST(hits) AS hits
WHERE
  DATE(TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)), 'Asia/Tokyo')  >= '2017-07-01'
  AND DATE(TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)), 'Asia/Tokyo') < '2017-08-01'
GROUP BY 1
ORDER BY 1

 

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

hits.page.pagePathがページ。
hits.isEntranceが1になってるページがランディング。
hits.isExitが1になってるページが離脱。
totals.bouncesが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
     `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
      UNNEST(hits) AS hits
  WHERE
  DATE(TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)), 'Asia/Tokyo')  >= '2017-07-01'
  AND DATE(TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)), 'Asia/Tokyo') < '2017-08-01'
)
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

 

5. ページ平均滞在時間

fullVisitorId + visitNumberをセッションIDとして使う。
ページ別に次ページとのタイムスタンプの差分を合算した秒数をページ別トータル滞在時間として抽出。
次ページのタイムスタンプ差が1800秒以上あるものはセッションが切れてるので除外。
hits.isInteraction = trueで、非インタラクションヒットは除外。
滞在時間の合計を、ページのセッション数で割れば平均滞在時間になる。

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
     `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
      UNNEST(hits) AS hits
  WHERE
  DATE(TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)), 'Asia/Tokyo')  >= '2017-07-01'
  AND DATE(TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)), 'Asia/Tokyo') < '2017-08-01'
    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

 

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

カスタムディメンションは縦持ちのレコードなので、横持ちに変換すると使いやすいです。
これまた、以前書いた記事に書いてます。

カスタムディメンション1 x カスタムディメンション2 をクロス集計してセッション数を出します。

SELECT
  (SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customDimensions)) AS dimension1, 
  (SELECT MAX(IF(index=2, value, NULL)) FROM UNNEST(hits.customDimensions)) AS dimension2, 
  COUNT(DISTINCT CONCAT(fullVisitorId, SAFE_CAST(visitNumber AS string))) AS sessions
FROM
  `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_*`, 
  UNNEST(hits) AS hits
WHERE
  DATE(TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)), 'Asia/Tokyo')  >= '2013-09-01'
  AND DATE(TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)), 'Asia/Tokyo') < '2013-10-01'
GROUP BY 1, 2
ORDER BY 3 DESC

 

SQLでWeb解析ログを分析すると、Adobe Analytics や Google Analytics で集めたデータを自由にドリルダウン出来るのがメリットです。

弊社では、Google Cloud認定データエンジニア資格を保持した技術者達によるデータ分析基盤構築業務を承っております。
お問い合わせはこちらからどうぞ

関連記事

  1. Google Analytics

    Google Analytics: アプリSDKのclientIDを取得する

    こんにちは、エクスチュアCTOの権です。以前、Google T…

  2. Adobe Analytics

    Adobe Analyticsに入り切らないデータをBigQueryに投入する

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

  3. Adobe Analytics

    Adobe Analytics: BigQueryにロードしたデータフィードをDataStudioで…

    こんにちは、エクスチュアCTOの権です。前回のブログで、Big…

  4. Cloud Dataflow

    データアナリスト/データエンジニア向けNext19まとめ

    こんにちは、エクスチュア渡部です。2019/4/9-4/11に行わ…

  5. Google BigQuery

    Google Apps Scriptを使ってスプレッドシートからBigQueryのテーブルを更新する…

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

  6. Adobe Analytics

    Adobe Analytics: DatafeedのログからフォールアウトレポートをBigQuery…

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

最近の記事

  1. Azure DatabricksでAdobe Analyti…
  2. GoogleDataStudio:複数のデータソースにフィル…
  3. Node.js+GAE: 日本語自然文を形態素解析してネガポ…
  4. Tableauで半円型のゲージを作る方法
  5. Looker: サンバーストグラフを使って階層データを可視化…
  1. Tableau

    Tableau:分かりやすいLOD – EXCLUDE編
  2. Adobe Analytics

    Adobe Analytics: BigQuery+Lookerでアトリビューシ…
  3. Google Tag Manager

    Google Tag Manager: 離脱リンクのクリックをトリガーにする
  4. Tableau

    Tableau:抽出を含んだワークブックを10.4以下にダウングレードする
  5. Tableau

    Tableau Viz紹介 : 10.4新機能を使ってダッシュボードを作ってみた…
PAGE TOP