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

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

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

  2. Adobe Analytics

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

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

  3. Adobe Analytics

    Adobe Analytics: SegmentsAPIを使って大量のセグメント設定を作成・更新する…

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

  4. Adobe Analytics

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

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

  5. Google BigQuery

    Google Apps Scriptを使ってスプレッドシートからBigQueryのテーブルを更新する…

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

  6. Google BigQuery

    BigQueryのユーザー定義関数(UDF)をTableauで使う

    こんにちは、エクスチュアの渡部です。今回はTableauでstan…

最近の記事

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

    データアナリスト/データエンジニア向けNext19まとめ
  2. Adobe Audience Manager

    Adobe Audience Manager: REST APIを使ってTrai…
  3. Databricks

    ScalaでDatabricksのDataFrameを扱う
  4. Adobe Analytics

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

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