【入門編】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エンジニア採用情報

  関連記事

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

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

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

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

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

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

toadstool
【Hivemall入門】RandomForestで毒キノコ推定モデルを作る

こんにちは。俺やで。 今回も前回から間が空いてしましたが、ビッグデータに対応したHiveで使える機械学習ライブラリ、 Hivemallの使い方について、書かせていただければと思います。 なお今回はQiitaのTreasure Data / Advent Calender 2015の12/3日分として …

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

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

no image
Treasure Dataで長期間の集計

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

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

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

gasserverless
GoogleAppsScriptとTreasureData REST APIを使ってサーバレスにTwitterのデータを取得

またまたTreasureDataネタです。 ただ、今回はクエリ系のネタではなく、GoogleAppsScriptとTreasureDataのREST APIを使ってTwitterのデータをTreasureDataに入れてみたので、その方法を紹介したいと思います。 はじめに ログデータだけではなく、公 …

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

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

chain
PyStanによるはじめてのマルコフ連鎖モンテカルロ法

はじめに こんにちは。システム開発部の中村です。 社内で行っている『データ解析のための統計モデリング入門』(所謂緑本)の輪読会に参加した所、 大変わかりやすい本だったものの、Macユーザには悲しい事に実装サンプルがWinBUGSだったため、 9章の一般化線形モデルのベイズ推定によるアプローチをPyt …