Adobe Analytics

Adobe Analytics: データフィードをBigQueryで集計する

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

前回のブログで、AdobeのデータフィードファイルをBigQueryにロードするところまで書きました。
今回は、データフィードをロードしたテーブルに対してSQLクエリを実行して、データを集計してみます。

2019/02/12 備考
この記事のSQLはレガシーSQLを使っています。StandardSQLの例はこちらの記事で紹介してます。

訪問者別ページビュー数

まずは訪問者別のページビュー数の抽出です。

/* 訪問者別pv */
SELECT date(date_time) as date,
concat(string(post_visid_high),  '-', string(post_visid_low)) as visid, 
COUNT(*) as pv_by_visid
FROM [test:my_adobe_datafeed.online_users]
WHERE hit_source = 1
AND exclude_hit = 0
AND date(date_time) = '2017-05-17' 
GROUP BY date, visid;

クエリ結果はこうなります。

日付、訪問者ID、そして訪問者ID別のページビュー数が抽出されました。
BigQueryではtimestampがUTCで格納されるのですが、そもそもDatafeedファイルのdate_time列は、日本時間JSTでエクスポートしてるので、時差9時間は足してません。

訪問者の特定については、Adobeのヘルプに書いてある通り、post_visid_highとpost_visid_lowを繋げたものに対して、exclude_hitが0、かつhit_sourceが1だけの行に絞ってあります。

Identifying Visitors – ClickStream Data Feeds

訪問回数別ページビュー数

各訪問者の訪問回数別にページビュー数を抽出します。

/* 訪問回数別pv */
SELECT date(date_time) as date,
concat(string(post_visid_high), "-", string(post_visid_low), "-", string(visit_num)) as unq_visits,
count(*) as pv_by_unq_visits
FROM [test:my_adobe_datafeed.online_users]
WHERE hit_source = 1
AND exclude_hit = 0
AND date(date_time) = '2017-05-17'
GROUP BY date, unq_visits;

クエリ結果はこうなります。

訪問者IDの末尾に訪問回数(visit_num)を追加することで、セッションを特定するIDになります。
各訪問者の訪問セッションごとのページビュー数が抽出されました。

パスフロー

訪問レベルの遷移レポートであるパスフローを抽出してみます。
GROUP_CONCAT関数を使います。

/* パスフロー(フルパス) */
SELECT date(date_time) as date,
concat(string(post_visid_high), '-', string(post_visid_low), '-', string(visit_num)) AS unq_visit,
GROUP_CONCAT(post_pagename,  ' -> ') AS visit_level_path
FROM [test:my_adobe_datafeed.online_users]
WHERE hit_source = 1 
AND exclude_hit = 0
AND date(date_time) = '2017-05-17'
GROUP BY date, unq_visit;

クエリ結果はこうなります。

GROUP_CONCAT関数を使って、セパレータを「->」にして訪問セッション別に表示したページ名を繋げました。
訪問者レベルのパスフローを抽出したい場合は、visit_numを使わなければOKです。

なお、リロードした場合に同じページ名が入ってしまってます。
リロードページ名を重複表示しないようにするためには、クエリをもう少し考える必要がありますね。

今回はBigQueryを使ってデータフィードに対してSQLクエリを実行して集計を行う方法について説明しました。

次回は、Google DataStudioからBigQueryに接続して、データを可視化する方法について書いて見たいと思います。

続き: Adobe Analytics: BigQueryにロードしたデータフィードをDataStudioで可視化する

関連記事

  1. Adobe Analytics

    Adobe Analytics: Mobile SDK 4.x でアプリ計測する際の小技

    こんにちは、エクスチュアCTOの権です。今回も、かつて個人ブロ…

  2. Google Analytics

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

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

  3. Adobe Analytics

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

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

  4. Cloud Dataflow

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

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

  5. Adobe Analytics

    Adobe Analytics:セグメントの落とし穴:意図しないデータが混ざる①

    Adobe Analyticsの便利な機能のセグメント。便利なのですが…

  6. Adobe Analytics

    Adobe AnaltyicsとGoogle Analytics の「生Webビーコン」をBigQu…

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

最近の記事

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

    Adobe Analytics: DWHレポートの日付列をBigQueryのDa…
  2. Adobe Analytics

    Adobe Analytics:自動で分析してくれる貢献度分析(異常値検出)機能…
  3. Adobe Analytics

    Azure DatabricksでAdobe AnalyticsのDatafee…
  4. Adobe Analytics

    Adobe Analytics: データフィードをGoogle BigQuery…
  5. Cloud Dataproc

    BigQueryテーブルをAVRO形式でエクスポートしてHiveで扱う
PAGE TOP