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: Legacy s_code.jsからAppMeasurement.…

    こんにちは、エクスチュアCTOの権です。今まで個人ブログで技術メモ…

  2. Google BigQuery

    GoogleNext 2019レポート:3日目

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

  3. Adobe Analytics

    Adobe Analytics:セグメントの落とし穴:滞在時間がおかしくなる

    Adobe Analyticsの持つ機能の中でも特に強力で便利な機能の…

  4. Adobe Analytics

    Adobe Analyticsと「どこどこJP」のAPIを連携する

    こんにちは、エクスチュアCTOの権です。弊社Webサイトでは「どこ…

  5. Adobe Analytics

    Azure DatabricksでAdobe AnalyticsのDatafeedを分析する

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

  6. Adobe Analytics

    Adobe Analytics:Report Builderの小技:リクエストのあるシートを丸ごとコ…

    エクセルでの定期レポートを作る際に、一度データブロックを作っておけば以…

最近の記事

  1. Databricks: Delta Lakeを使ってみる
  2. Adobe Analytics:計算指標でevents変数を…
  3. Databricks: Spark DataFramesをJ…
  4. Databricks: Spark RDDで使う主なメソッド…
  5. GCPのBQMLを使ってKaggleコンペに挑んでみた(その…
  1. Adobe Analytics

    Looker: Sankey Diagramを使ってサイト内フローを可視化する
  2. Mouseflow

    Mouseflow:計測対象からの除外
  3. Adobe Analytics

    Adobe AnaltyicsとGoogle Analytics の「生Webビ…
  4. Adobe Target

    Adobe Target: スマホアプリ上でABテストをする
  5. Salesforce

    Zapier(ザピエル)経由でSansanで取り込んだ名刺をSalesforce…
PAGE TOP