【入門編】TreasureDataでWEBログ分析をしてみた

Tweet

この記事は Treasure Data Advent Calendar 2015 – Qiita の24日目の記事です。

こんにちは。

今回はWEBログの集計や解析をする際によく使うHiveQLのクエリと、UDF(User Defined Functions)について実際の集計クエリを使って解説していきたいと思います。
これからTreasureDataを使う人やTreasureDataを使い始めたばかりの人向けの内容になっています。

今回やったこと

利用したWEBログテーブル

以下のような一般的なWEBログを利用しました。
このログを元に標準的なアクセス解析用データを生成する集計クエリを書いてみます。

実際のテーブルは以下のようになっています。

実行したクエリ

具体的には、WEBログからセッション単位のデータを作成しています。

[sql]
SELECT
TD_TIME_FORMAT(min(time), ‘yyyy-MM-dd HH:mm:ss’, ‘JST’) as session_start_time,
TD_TIME_FORMAT(max(time), ‘yyyy-MM-dd HH:mm:ss’, ‘JST’) as session_end_time,
session_id,
user_id,
— TD_PARSE_USER_AGENTを利用してUAからOSの文字列を取得し、PC/SDを判定
CASE
WHEN TD_PARSE_USER_AGENT(
TD_LAST(
useragent,
time
), ‘os_family’
) IN(
‘Android’,
‘iOS’,
‘Windows Phone’,
‘Windows RT’
) THEN ‘sd’
ELSE ‘pc’
END device,
— セッションの一番最初のリファラURL（流入元）を取得
TD_FIRST(referer, time) as referer,
— セッションの一番最初のURL（ランディングページ）を取得
TD_FIRST(url, time) as lp,
— セッションの一番最後のURL（離脱ページ）を取得
TD_LAST(url, time) as exit_page,
COUNT(1) as views,
— ランディングページのGoogleAnalyticsのパラメータを取得
parse_url(
TD_FIRST(url, time),
‘QUERY’,
‘utm_source’
) as ga_utm_source,
— PV数が1のものに直帰フラグを設定
CASE
WHEN COUNT(1) = 1 THEN 1 ELSE 0
END bounce_flg,
— セッションの終了と開始時間から滞在時間を計算
MAX(time) – MIN(time) as duration
FROM (
— user_idをキーに30分でセッションIDを生成
SELECT
time,
ip,
user_id,
url,
referer,
useragent,
TD_SESSIONIZE(
time,
1800,
user_id
) AS session_id
FROM
(
— user_idが空の場合にUA+IPをキーに擬似user_idを生成。セッションIDを生成するため、user_id、timeでソート
SELECT
time,
ip,
COALESCE(user_id, TD_MD5(concat(useragent,ip))) as user_id,
url,
referer,
useragent
FROM
demo_www_access
DISTRIBUTE BY COALESCE(user_id, TD_MD5(concat(useragent,ip))) SORT BY COALESCE(user_id, TD_MD5(concat(useragent,ip))), time
) t0
) t1
GROUP BY session_id, user_id
[/sql]

結果

クエリの実行結果は以下のようなものになります。

クエリ解説

それでは、実際のクエリで使っているUDFについて解説していきたいと思います。

TD_MD5

文字列から、md5のハッシュ値を生成する関数です。

今回何に使ったかというと、利用したWebログで利用しているユーザIDが3rdPartyCookieであるため、iOS Safariなどのブラウザではブランクになってしまっています。
ユーザIDがないとセッション単位の集計もできません。除外してしまってもいいのですが、ある程度の分析は行いたいので、擬似的にユーザIDを生成しました。

具体的にはユーザーエージェントとIPアドレスを結合した文字列からmd5でハッシュを生成し、擬似ユーザIDとしています。

TD_SESSIONIZE

今回のWebログをセッション単位にデータにする上で、一番重要な関数です。

time値でソートし、タイムアウトを30分、セッションID生成の単位をユーザIDとしています。
ポイントとしては、事前に「distribute by」、「sort by 」を使って、ユーザID単位にtimeカラムでソートしておくことです。

TD_PARSE_USER_AGENT

これも非常に便利な関数で、Webログの解析ではよく利用します。ユーザーエージェントを元にOSやブラウザの判定をしてくれます。

TD_FIRST

アクセスログからセッション単位にサマリした際の一番最初（time値が一番小さい）のリファラとURLを取得しています。
つまり、流入元（リファラ）とランディングページ（進入ページ）が取得できます。

TD_LAST

TD_FIRSTの逆です。
今回の場合はセッションの最後にアクセスしたページ、つまり離脱ページを取得することができます。

parse_url

TDのUDFではなくhiveの関数です。
URLをパースしてホスト名やパス、指定したクエリパラメータの値を取得することができます。
これを使うことで、GoogleAnalyticsのパラメータを取得することができます。

最後にひとこと

実際の作業でよく使うHiveQLのクエリとUDFについて紹介させていただきました！

私自身、まだまだ知らないUDFもあり日々勉強しております。また次回、実例と一緒に学んだことを投稿できればと思います。

以上、TreasureDataでWEBログ分析をしてみたでした！

Tweet

  関連記事