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

ホーム
Adobe Analytics, Google BigQuery
Adobe Analytics + Google BigQueryでよく使うSQL例 6選

Adobe Analytics

2019.02.12

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

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

今回は、私がよくBigQueryで使ってるAdobe Analytics用のSQL例を紹介します。
GA360編はこちら

もうBigQueryあるからAdobeのUIいらねーよってくらいBigQuery依存です。

以前、レガシーSQLの例を掲載しましたが、今回は標準SQLです。

前提条件

当然ながらAdobeAnalytics DatafeedをBigQueryにロードしてる事。

スキーマのテンプレート: All Columns Premium (August 2018)
原則 exclude_hit = 0の行を利用する
hit_time_gmt列(UTC)で日付分割パーティションを作ってるので、期間を絞る場合はタイムゾーンをAsia/Tokyoにしてを使う

1. ページビュー数

post_page_event = 0 がページビュー。
日別で出します。

SELECT
  DATE(hit_time_gmt, 'Asia/Tokyo') AS date,
  COUNT(1) AS pageviews
FROM
  `datafeed.hit_data`
WHERE
  DATE(hit_time_gmt, 'Asia/Tokyo') >= '2019-01-01'
  AND DATE(hit_time_gmt, 'Asia/Tokyo') < '2019-02-01'
  AND exclude_hit = 0
  AND post_page_event = 0
GROUP BY 1
ORDER BY 1

2. 訪問回数

post_visid_high + post_visid_low + visit_num を文字列にしたものをCONCATして、ユニーク数をカウントしたのが訪問回数。
これも日別で出します。

SELECT
  DATE(hit_time_gmt, 'Asia/Tokyo') AS date,
  COUNT(DISTINCT CONCAT(post_visid_high, post_visid_low, SAFE_CAST(visit_num AS string))) AS visits
FROM
  `datafeed.hit_data`
WHERE
  DATE(hit_time_gmt, 'Asia/Tokyo') >= '2019-01-01'
  AND DATE(hit_time_gmt, 'Asia/Tokyo') < '2019-02-01'
  AND exclude_hit = 0
GROUP BY 1
ORDER BY 1

3. 訪問者数

post_visid_high + post_visid_low をCONCATして、ユニーク数をカウントすると訪問者数。
下記はdateでGROUP BYしてるので、日別訪問者数を出してます。

SELECT
  DATE(hit_time_gmt, 'Asia/Tokyo') AS date,
  COUNT(DISTINCT CONCAT(post_visid_high, post_visid_low)) AS visitors
FROM
  `datafeed.hit_data`
WHERE
  DATE(hit_time_gmt, 'Asia/Tokyo') >= '2019-01-01'
  AND DATE(hit_time_gmt, 'Asia/Tokyo') < '2019-02-01'
  AND exclude_hit = 0
GROUP BY 1
ORDER BY 1

4. ページ別入口数・出口数(離脱数)・直帰数

post_visid_high + post_visid_low + visit_numをセッションIDとして使う。
これをhit_time_gmtを昇順ソートして、一番目のページが入口。
逆にhit_time_gmtを降順ソートしたら、一番目のページが離脱。
そして入口＆離脱になったページが直帰。
post_page_event = 0に絞ってるので「直帰数」になってますが、「バウンス数」を見たい場合はpost_page_event = 0を外せば良い。

WITH
  t1 AS (
  SELECT
    hit_time_gmt,
    post_pagename AS pagename,
    CONCAT(post_visid_high, post_visid_low, SAFE_CAST(visit_num AS string)) AS sid,
    CASE
      WHEN ROW_NUMBER() OVER(PARTITION BY CONCAT(post_visid_high, post_visid_low, SAFE_CAST(visit_num AS string))  ORDER BY  hit_time_gmt) = 1 THEN 1
      ELSE 0
    END AS is_lp,
    CASE
      WHEN ROW_NUMBER() OVER(PARTITION BY CONCAT(post_visid_high, post_visid_low, SAFE_CAST(visit_num AS string))  ORDER BY  hit_time_gmt DESC) = 1 THEN 1
      ELSE 0
    END AS is_exit,
    CASE
      WHEN COUNT(1) OVER(PARTITION BY CONCAT(post_visid_high, post_visid_low, SAFE_CAST(visit_num AS string))) = 1 THEN 1
      ELSE 0
    END AS is_singlevisit
  FROM
    `datafeed.hit_data`
  WHERE
    DATE(hit_time_gmt, 'Asia/Tokyo') >= '2019-01-01'
    AND DATE(hit_time_gmt, 'Asia/Tokyo') < '2019-02-01'
    AND exclude_hit = 0
    AND post_page_event = 0)
SELECT
  t1.pagename,
  SUM(t1.is_lp) AS entry,
  SUM(t1.is_exit) AS exit,
  SUM(t1.is_singlevisit) AS singlevisit
FROM t1
GROUP BY 1
ORDER BY 2 DESC

5. ページ平均滞在時間

セッションID別にLEAD関数で次のhit_time_gmtとの差分を合算した秒数をトータル滞在時間として抽出。
30分以上(1800秒以上)経過した場合はカウントしない。
滞在時間の合計を、セッション数で割れば平均滞在時間になる。

WITH
  t1 AS (
  SELECT
    hit_time_gmt,
    post_pagename AS pagename,
    CONCAT(post_visid_high, post_visid_low, SAFE_CAST(visit_num AS string)) AS sid,
    CASE WHEN timestamp_diff(LEAD(hit_time_gmt) OVER(PARTITION BY CONCAT(post_visid_high, post_visid_low, safe_cast(visit_num as string)) ORDER BY hit_time_gmt), hit_time_gmt, second) > 1800 THEN NULL
ELSE LEAD(hit_time_gmt) OVER(PARTITION BY CONCAT(post_visid_high, post_visid_low, safe_cast(visit_num as string)) ORDER BY hit_time_gmt) END AS next_hit
  FROM
    `datafeed.hit_data`
  WHERE
    DATE(hit_time_gmt, 'Asia/Tokyo') >= '2019-01-01'
    AND DATE(hit_time_gmt, 'Asia/Tokyo') < '2019-02-01'
    AND exclude_hit = 0)
SELECT
  t1.pagename,
  COUNT(DISTINCT t1.sid) AS visits,
  SUM(IFNULL(TIMESTAMP_DIFF(t1.next_hit, t1.hit_time_gmt, second), 0)) AS totaltimespent,
  ROUND(SAFE_DIVIDE(SUM(IFNULL(TIMESTAMP_DIFF(t1.next_hit, t1.hit_time_gmt, second), 0)), COUNT(DISTINCT t1.sid)), 2) AS avgtimespent
FROM t1
WHERE t1.pagename IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC

6. 購入回数(purchase) および各events数

purchaseイベントはpost_event_listに、「1」というIDで格納されてます。
このカラムはカンマ区切りで同時に発生したeventsが格納されてるので、post_event_listの前後にカンマを付けて「,1,」が含まれる数をカウントすればOK.
duplicate_purchase=0に絞れば、重複purchaseを除外。
なお、event1〜event100は 200〜299 というIDが付与され、event101〜event1000は 20100〜20999 になる。

SELECT
  DATE(hit_time_gmt, 'Asia/Tokyo') AS date,
  COUNT(1) AS purchases
FROM
  `datafeed.hit_data`
WHERE
  DATE(hit_time_gmt, 'Asia/Tokyo') >= '2019-01-01'
  AND DATE(hit_time_gmt, 'Asia/Tokyo') < '2019-02-01'
  AND exclude_hit = 0
  AND duplicate_purchase = 0
  AND CONCAT(',', post_event_list, ',') LIKE '%,1,%'
GROUP BY 1
ORDER BY 1

これさえ分かれば、応用して任意のカラム別(ディメンション別)に集計出来ますね。