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と「どこどこJP」のAPIを連携する

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

  2. Cloud Dataproc

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

    こんにちは、エクスチュアCTOの権です。今日も普段あまり使わな…

  3. Google Analytics

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

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

  4. Google BigQuery

    Treasure DataからBigQueryにデータを移動させる方法

    こんにちは、エクスチュアの酒井です。今回は、TreasureDat…

  5. Adobe Analytics

    Adobe Analytics: BigQuery+Lookerでアトリビューション分析

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

  6. Adobe Analytics

    Adobe Analytics: DatafeedのログからパスフローレポートをBigQueryで作…

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

最近の記事

  1. AA + GA : SafariのITP2.1に備えてphp…
  2. Tableau:分かりやすいLOD – EXCL…
  3. Tableau:スペース区切りで複数検索が出来る検索ボックス…
  4. Google Analytics 360 + BigQuer…
  5. Looker: 公開されているLookML Blockをクロ…
  1. Mouseflow

    Mouseflow:ヒートマップ表示の仕様
  2. Adobe Target

    Adobe Target: スマホアプリ上でABテストをする
  3. Google Cloud Platform

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

    Google Tag Manager上でGoogle Analyticsのcli…
  5. Adobe Analytics

    Adobe Analytics: SegmentsAPIを使って大量のセグメント…
PAGE TOP