こんにちは、エクスチュアの權泳東(権泳東/コン・ヨンドン)です。
前回のブログで、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で可視化する
ブログへの記事リクエストはこちらまで
