【入門編】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!検索, 直接流入別の分析用クエリを書いてみました。

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

流入元分析

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

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

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')

曜日時間帯分析

 

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

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

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

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

フリークエンシー分析

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

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

 

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

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

サイト平均滞在時間

 

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

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

流入別平均滞在時間

流入経路関係なく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エンジニア採用情報

  関連記事

logomono-tableau-software-mono
Tableau 9.2で郵便番号の特性を地図で可視化してみる

Tableau 9.2から郵便番号地図が表示可能に 弊社ではデータ分析ツールのTableauを利用しています。オーディエンスデータの重複を分析したり、デモグラフィック属性を表示したりするなどデータの可視化に役立ちますTableauでは9.2から日本の郵便番号を用いて地図を可視化できるようになりました …

Screen Shot 2014-11-17 at 9.33.19 PM
Amazon ElastiCache/Redisのパフォーマンス確認

はじめに こんにちは、AudienceOne開発部です。AudienceOne開発部ではいわゆるビッグデータと呼ばれる大量のデータをアドホックあるいは定常的に日々ETLだの集合演算だのをする一方で、様々な大規模データ処理ソリューションを継続的に検証しております。 本記事は、その中でもユーザが保持して …

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

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

Hivemall_Minhash_pic1_thum
HivemallでMinhash!〜似てる記事を探し出そう。〜

こんにちは。俺やで。 前回の投稿に続き(間が空きましたが)、 ビッグデータに対応したHiveで使える機械学習ライブラリ、 「Hivemall」の使い方、第2弾となります。 今回はMinhashという手法について書きたいと思います。 ※前回 【超入門】Hivemallで機械学習 〜Treasure D …

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

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

no image
Treasure Dataで長期間の集計

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

bigdata
HyperLoglogでcount distinctを速くする

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

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

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

11396380473_26f323b1e4_z
Google BigQuery / Tableauを使ってみた

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

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

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