Adobe Analytics

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

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

前回のブログで、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. Google BigQuery

    BigQuery BI Engine解説

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

  2. GA 360 Suite

    GoogleDataStudio:複数のデータソースにフィルターを適用する

    こんにちは。エクスチュアの渡部です。今回はDataStudio(デ…

  3. Adobe Analytics

    Adobe AEP SDKでReactNativeアプリを計測する (Android編)

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

  4. Adobe Analytics

    AdobeAnalyticsでReactNativeアプリを計測する

    この記事は2018年12月7日現在の情報を元にしているのと、レガシーA…

  5. Ad Hoc Analysis

    Adobe Analytics: Ad Hoc Analysisでセグメントの条件を一気に追加する方…

    Adobe Analyticsでセグメントを作るとき、「A または B…

最近の記事

  1. ELB (ALB・NLB・CLB) をサクッと学ぶ
  2. Direct Connect vs Site to Site…
  3. パブリックサブネット vs プライベートサブネット
  4. セキュリティグループ vs ネットワークACL
  5. IAM (Identity and Access Manag…
  1. Adobe Analytics

    Adobe Analytics: レガシーs_code.jsのリミッターを解除し…
  2. Tableau

    【TC19ブログ】Tableau Conference 2019 2日目レポート…
  3. Adobe Dynamic Tag Manager

    Adobe DTMからAdobe Launchに一発アップグレード
  4. GA 360 Suite

    GoogleDataStudio:複数のデータソースにフィルターを適用する
  5. Amazon Web Services

    パブリックサブネット vs プライベートサブネット
PAGE TOP