Adobe Analytics

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

 

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

おまけ

以下は過去に書いた記事です。

events変数集計用SQL

フォールアウト用SQL

パスレポート(フローレポート)用SQL

ファーストタッチ・ラストタッチ・パーティシペーション(アトリビューション)用SQL

 

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

ブログへの記事リクエストはこちらまで

関連記事

  1. Adobe Analytics

    Adobe Analytics: ランドスケイプの「企業ログ解析データベースAPI」と連携する

    こんにちは、エクスチュアCTOの権です。今回はランドスケイプの…

  2. Adobe Analytics

    Adobe Analytics: Webサイト内で発生したJavascriptエラーの内容をAdob…

    こんにちは、エクスチュアCTOの権です。今日は、Webサイト内で発…

  3. Google BigQuery

    GoogleNext 2019レポート:3日目

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

  4. Adobe Analytics

    AA + GA : SafariのITP2.1に備えてphpでクッキーを永続化する

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

  5. Adobe Analytics

    BigQuery: Adobe Datafeed: event_listカラムの手軽な扱い方

    こんにちは、エクスチュアCTOの権です。今回は、AdobeAn…

  6. Google Analytics

    Google Analytics StandardのデータをBigQueryで分析するための力技

    こんにちは、エクスチュアの権です。先月「Google Clou…

最近の記事

  1. 続・SafariのITP2.x対策として別サーバー使ってクッ…
  2. Big QueryでWindow関数を用いて、累積和を計算す…
  3. Adobe Analytics: DatafeedをGoog…
  4. Adobe DTMからAdobe Launchに一発アップグ…
  5. Tableau:KPI達成状況を把握出来るグラフを作る
  1. Amazon Web Services

    Databricks Community Editionを使ってApache S…
  2. Adobe Analytics

    Looker: エンジニアがBIで分析ダッシュボードを作る
  3. Google Cloud Platform

    Looker: LookerbotでSlackにグラフ画像をスケジュール投稿する…
  4. Google Analytics

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

    Adobe Analytics: データフィードをGoogle BigQuery…
PAGE TOP