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. 最速で理解したい人のためのIT用語集

関連記事

  1. Adobe Analytics

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

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

  2. Adobe Analytics

    サイトセクションとは-Adobe Analyticsの指標説明

    今回はサイトセクションについて説明いたします。サイトセクションとは…

  3. Adobe Analytics

    Adobe AEP SDKをTypeScriptで開発したReactNativeアプリに実装する

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

  4. Adobe Analytics

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

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

  5. Adobe Analytics

    AdobeAnalytics: Adobe I/OのAPIを使ってSAINT分類データを取得する

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

  6. Adobe Analytics

    Report&Analytics グラフについて

    こんにちは!インターン生の藤本です。私は、最近ACEの試験(Repo…

カテゴリ

最近の記事

  1. コンポーザブルCDPにおけるSnowflakeのマルチモーダ…
  2. boxMCPサーバーを使ってみた
  3. #ai-datacloud勉強会でマルチモーダルに触れた日
  4. Matillion ETLを安全に使いたい人へ送る、SSL対…
  5. LangGraphのソースコードから見る今更聞けないAIエー…
  1. Google Analytics

    Google Tag Manager上でGoogle Analyticsのcli…
  2. Tableau

    Tableau:2つの値の比較に便利な「ダンベルチャート」を作る
  3. ObservePoint

    Webサイトのプライバシー検証 (6/6): 知らない国やドメインにデータを送っ…
  4. IT用語集

    エンタープライズサーチ(Enterprise Search)って何?
  5. IT用語集

    キッティング(Kitting)って何?
PAGE TOP