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


今回もやります、集計クエリ解説シリーズ第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でサイトのアクセス解析をしてみたでした!


DACエンジニア採用情報

  関連記事

気象予報士とビッグデータ解析の意外な関係

DACから気象予報士が誕生しました ビッグデータ解析部のMikeです。 2015年1月の気象予報士試験に合格し、めでたく4月からアドテク業界ただ一人(本当?)の気象予報士となりました 。 そんなわけで、今回は気象予報士とビッグデータ解析の関係についてお話したいと思います。 なぜ気象予報士を目指したか …

【超入門】Hivemallで機械学習 〜Treasure Dataでロジスティック回帰編〜

こんにちは。俺やで。 ビッグデータとかデータサイエンティストとかいう言葉が未だブームですね。 (「データサイエンティスト」は下火か。) ビッグデータ扱えるエンジニアも、 統計解析ができるアナリストも、 どっちもできるスーパーマンも世の中にはたくさんいますが、 ビッグデータも統計解析も扱えるインフラは …

no image
いま必要なのは「アナリティクスアプローチ」

こんにちは。 ビッグデータ解析部のakiです。 解析部で、Markezineでの連載をはじめましたのでご紹介です。 いま必要なのは「アナリティクスアプローチ」、ビッグデータ活用の課題とこれから (http://markezine.jp/article/detail/21293) マーケターのかた向け …

ディープラーニングで「顔が似ているAKB48のメンバーを教えてくれるbot」を構築

概要 こんにちは、システム開発部の中村です。 今回は、Facebook Messenger APIを利用して、 画像をアップロードすると、似ているAKB48のメンバーを教えてくれるbotを実装しました。 尚、ディープラーニングやTensorFlowそのものの解説というより、 「エンジンとしてディープ …

Treasure Dataで大規模なマスタデータを扱う際にはtimeカラムインデックスを活用しよう

DACではTreasure Dataを利用して各種データの蓄積や集計を行っています。Treasure Dataは時系列のデータを扱うのに特にすぐれたアーキテクチャなのですが、セグメントIDとユーザーIDの組み合わせといった大量のマスタデータを利用した計算にも利用することもできます。そのような場合にt …

D3.jsとその活用事例について

D3.jsとは? D3とは「Data Driven Document」の略で、データに基づいてドキュメントを操作するための JavaScript ライブラリです。 ご存知の方も多いと思いますが、ちょっとだけD3.jsの基本的な使い方、そして弊社プラットフォームでの利用についてご紹介したいと思います。 …

Google BigQuery / Tableauを使ってみた

TableauからGoogle BigQueryへ接続してみました。 弊社で利用しているTreasureDataからデータ出力してBigQueryへロード、Tableauから接続まで実際に行った手順について記載します。 TreasureDataからAmazonS3へデータ出力 まず、データが蓄積され …

HyperLoglogでcount distinctを速くする

こんにちは。俺やで。 HyperLoglogについて書きます。おもしろいです。名前が。 ■1. HyperLoglogとは? count distinctを速くするアルゴリズム 以前、Minhashについて書きました。 (Treasure Dataさんのブログにも載せていただきました。ありがとうござ …

トレジャーデータの新機能「Data Connector」でクライアントレスなビッグデータ連携を実現する

トレジャーデータは、スキーマレスな大量のデータ(ビッグデータ)をパブリッククラウド上に保管して集計や抽出をするためのサービスなのですが、他システムからの連携データをトレジャーデータのテーブルに格納するまでが一苦労でした。 他システムとの外部連携を行う場合、一般的にローカルサーバー内のストレージを外部 …

no image
Treasure Dataで長期間の集計

プラットフォーム・ワン T氏です。プラットフォーム・ワンでは、DSPのMarketOneとSSPのYIELD ONE提供しています。 MarketOneやYIELD ONEのログを調査する場合にTreasure Dataを使うことがあります。Treasure Dataでは大量のデータに対してHive …