DACエンジニアブログ：アドテクゑびす界

DACのエンジニアやマーケター、アナリストが執筆するアドテクの技術系ブログです。

【入門編】TreasureDataでサイトのアクセス解析をしてみた~第2弾!~

HiveQL Treasure Data アクセス解析 ビッグデータ

今回もやります、集計クエリ解説シリーズ第2弾！！

前回は、Webログからセッション単位のデータを作成するだけでした。 第2弾では作成したテーブルを元に、より実践的なアクセス解析、サイト分析で使えるHiveQLについて、実際に使用したクエリとともに解説していきたいと思います。

今回やったこと

利用したアクセス解析用データテーブル

前回作成した、WEBログから作成したセッション単位のデータを利用しました。 このデータからサイトのアクセス解析レポートを生成する集計クエリをかいてみます。 ちなみに、DACサイトの1週間分のWEBログが元データになっています。

カラム名 内容
session_start_time セッションの開始日時
session_end_time セッションの終了日時
session_id セッションID
user_id ユーザーID
device デバイス区分
referer 流入元ページ
lp ランディングページ
exit_page 離脱ページ
views 閲覧回数
ga_utm_source GoogleAnalyticsのパラメータ
bounce_flg 直帰フラグ
duration 滞在時間

 

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

実行したクエリと結果

流入経路別のuu数/セッション数/pv数

サイトへ流入してきたユーザーがどこから来たのか、流入チャネル別に把握することもできます。 以下ではFacebook, Twitter, Google検索, Yahoo!検索, 直接流入別の分析用クエリを書いてみました。

[sql] SELECT -- リファラURLをパースしてホスト名を取得、CASE文を利用して流入元を判定 CASE WHEN parse_url( referer, 'HOST' ) LIKE '%www.facebook.com%' THEN 'Facebook' WHEN parse_url( referer, 'HOST' ) LIKE '%www.twitter.com%' THEN 'Twitter' WHEN parse_url( referer, 'HOST' ) LIKE '%www.google.co.jp%' THEN 'Search_google' WHEN parse_url( referer, 'HOST' ) LIKE '%search.yahoo.co.jp%' THEN 'Search_Yahoo' WHEN (referer = '' OR referer IS NULL) THEN 'Direct' ELSE 'Other' END AS inflow_source, COUNT(DISTINCT user_id) AS uu, COUNT(session_id) AS sessions, SUM(Views) AS pv FROM demo_www_sessions GROUP BY CASE WHEN parse_url( referer, 'HOST' ) LIKE '%www.facebook.com%' THEN 'Facebook' WHEN parse_url( referer, 'HOST' ) LIKE '%www.twitter.com%' THEN 'Twitter' WHEN parse_url( referer, 'HOST' ) LIKE '%www.google.co.jp%' THEN 'Search_google' WHEN parse_url( referer, 'HOST' ) LIKE '%search.yahoo.co.jp%' THEN 'Search_Yahoo' WHEN (referer = '' OR referer IS NULL) THEN 'Direct' ELSE 'Other' END [/sql]

流入元分析

曜日・時間帯別のセッション数

曜日・時間帯を軸に分析をしてみました。 これで、自社のサイトへのアクセス数がどの曜日のどの時間帯に集中しているのか等も把握できます。

[sql] SELECT -- タイムスタンプを曜日の文字列に変換 TD_TIME_FORMAT(TD_TIME_PARSE(session_start_time),'EEEE','JST') AS Day_in_week, -- タイムスタンプを時間帯の文字列に変換 TD_TIME_FORMAT(TD_TIME_PARSE(session_start_time),'HH','JST') AS Hour_in_day, COUNT(session_id) AS sessions FROM demo_www_sessions GROUP BY TD_TIME_FORMAT(TD_TIME_PARSE(session_start_time),'EEEE','JST'), TD_TIME_FORMAT(TD_TIME_PARSE(session_start_time),'HH','JST') [/sql]

曜日時間帯分析

 

ついでに、結果をダウンロードしてヒートマップを作ってみました。 DACサイトのアクセスは火曜日の深夜に集中してますね。。 ヒートマップ

フリークエンシー毎のuu数/pv数

サイトの接触頻度（回数）を軸に分析してみました。 例えば、これを応用して最適な広告表示回数を分析することもできます。

[sql] SELECT -- セッション数毎のユーザ数、PV数を集計 sessions, COUNT(user_id) AS uu, SUM(pv) AS pv FROM ( SELECT user_id, -- ユーザ毎にセッション数をカウント COUNT(session_id) AS sessions, SUM(Views) AS pv FROM demo_www_sessions GROUP BY user_id ) t0 GROUP BY sessions [/sql]

フリークエンシー分析

流入経路別の平均滞在時間

最初の流入経路別の分析を利用し、平均滞在時間も出してみました。 まずは比較対象として、流入経路関係なくサイトに訪れた人の平均滞在時間を集計してみます。 なお、今回直帰の場合は滞在時間を0秒としているため、直帰フラグのたっているデータは集計対象外にしました。

 

★サイト訪問者の平均滞在時間集計クエリ★

[sql] SELECT -- 合計滞在時間をセッション数で割って平均滞在時間を算出 SUM(duration)/ COUNT(session_id) AS arg_duration FROM -- 直帰とみなされたユーザーのデータは除外 demo_www_sessions WHERE bounce_flg = 0 [/sql]

サイト平均滞在時間

 

★流入経路別 サイト訪問者の平均滞在時間集計クエリ★

[sql] SELECT -- リファラURLのドメインを取得し流入元を判別 CASE WHEN parse_url( referer, 'HOST' ) LIKE '%www.facebook.com%' THEN 'Facebook' WHEN parse_url( referer, 'HOST' ) LIKE '%www.twitter.com%' THEN 'Twitter' WHEN parse_url( referer, 'HOST' ) LIKE '%www.google.co.jp%' THEN 'Search_google' WHEN parse_url( referer, 'HOST' ) LIKE '%search.yahoo.co.jp%' THEN 'Search_Yahoo' WHEN ( referer = '' OR referer IS NULL ) THEN 'Direct' WHEN parse_url( referer, 'HOST' )!= '' THEN 'Others' ELSE 'Others' END ref, 　-- 合計滞在時間をセッション数で割って平均滞在時間を算出 SUM(duration)/ COUNT(session_id) AS arg_duration FROM demo_www_sessions WHERE 　-- 直帰とみなされたユーザーのデータは除外 bounce_flg = 0 GROUP BY CASE WHEN parse_url( referer, 'HOST' ) LIKE '%www.facebook.com%' THEN 'Facebook' WHEN parse_url( referer, 'HOST' ) LIKE '%www.twitter.com%' THEN 'Twitter' WHEN parse_url( referer, 'HOST' ) LIKE '%www.google.co.jp%' THEN 'Search_google' WHEN parse_url( referer, 'HOST' ) LIKE '%search.yahoo.co.jp%' THEN 'Search_Yahoo' WHEN ( referer = '' OR referer IS NULL ) THEN 'Direct' WHEN parse_url( referer, 'HOST' )!= '' THEN 'Others' ELSE 'Others' END [/sql]

流入別平均滞在時間

流入経路関係なくDACサイトに訪れた人の平均滞在時間が約5分だったのに対し、 直接流入については約1分、FacebookとYahoo!検索からのDACサイト訪問者の平均滞在時間は30秒以上上回ることがわかりました！

クエリ解説

それでは、実際のクエリで使っている関数やTreasureDataのUDF(User Defined Functions)について解説していきたいと思います。

CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END

ドキュメント: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

 

if 文と同じように処理を分岐するために使用されるものです。 式が条件aに一致（TRUEと評価）したら値bを戻し、条件aに一致せず条件cに一致したら値dを戻し、、、 このように式と条件が一致する最初の値が戻されます。 どの条件とも一致しなかった場合は、ELSE句に指定したデフォルト値eを戻します。 ただし、ELSE句（デフォルト値）は省略可能で、省略されたときはNULL値が戻されます。

今回何に使ったかというと、 parse_urlで取得したホスト名がwww.facebook.comと部分一致に一致したら名称Facebookを戻しています。 ソーシャル(Facebook,Twitter)と検索(Google/Yahoo!)、リファラURLがないものについては直接流入(Direct)の判別をしてみました。

TD_TIME_FORMAT

公式ドキュメント: https://docs.treasuredata.com/articles/udfs#tdtimeformat

 

timestamp を特定フォーマットの文字列に変換する関数です。 2つ目の引数に時間のフォーマットを指定する事で、1つ目の引数の timestamp を文字列変換します。 また、3つ目の引数に "JST" を指定すれば（デフォルトは UTC）unixtimestamp を日本時間の日付に変換してくれます。

今回は曜日毎、時間帯毎に集計したかったので、2つ目の引数に曜日のフォーマット "EEEE" と時間帯のフォーマット "HH" を指定しました。 時間のフォーマット一覧はこちらにあります。

TD_TIME_PARSE

公式ドキュメント: https://docs.treasuredata.com/articles/udfs#tdtimeparse

 

TD_TIME_FORMATの逆です。 今回の場合は、参照テーブルのsession_start_timeのフォーマットが"yyyy-MM-dd HH:mm:ss"になっており、 曜日の判定ができなかったため文字列をunixtimestampに変換しました。

最後にひとこと

今回は主にuu数やセッション数、pv数を出してみました。 第3弾では、、、 セッション内でのページ遷移や直帰フラグ別、新規/リピーターごとの分析等をTreasure Dataでやってみたいと思います！

以上、TreasureDataでサイトのアクセス解析をしてみたでした！