見出し画像

【WEBログ分析の関数編】 TreasureData独自関数が覚えられない！TD利用歴2年半のエンジニアがよく使うものだけをまとめてみた！

株式会社Legoliss

はじめに

お客様のサポートをする中で、「TreasureDataの独自関数の使い方が分からない」という声をよく聞きます。TreasureDataは、BigQueryやRedshiftとは違い、便利な独自の関数（User Defined Functions）をたくさん用意しているのですが、慣れていなうちは使い方に苦労されている印象を受けます。
この記事では、TDタグで取得できるWEBログを、分析用途に活用しやすい形式に変更できる関数をご紹介したいと思います。


WEBログについて前提確認

TDタグのデフォルトタグで取得できるデータは以下になります。
他のデータを取得したい場合は、タグの中でjavascriptを記述することがで取得することができます。

$$
\begin{array}{|c|l|l|} \hline
\text{No} & \text{項目} & \text{説明} \\ \hline
\text{1} & \text{time} & \text{アクセス日時} \\ \hline
\text{2} & \text{td\_client\_id} & \text{1stPartyCookie} \\ \hline
\text{3} & \text{td\_global\_id} & \text{3rdPatyCookie} \\ \hline
\text{4} & \text{td\_host} & \text{ホスト} \\ \hline
\text{5} & \text{td\_path} & \text{パス} \\ \hline
\text{6} & \text{td\_url} & \text{ページURL} \\ \hline
\text{7} & \text{td\_title} & \text{ページタイトル} \\ \hline
\text{8} & \text{td\_referrer} & \text{リファラURL} \\ \hline
\text{9} & \text{td\_ip} & \text{IP} \\ \hline
\text{10} & \text{td\_user\_agent} & \text{ユーザーエージェント} \\ \hline
\text{11} & \text{td\_os} & \text{OS} \\ \hline
\text{12} & \text{td\_os\_version} & \text{OSバージョン} \\ \hline
\text{13} & \text{td\_platform} & \text{プラットフォーム} \\ \hline
\text{14} & \text{td\_browser} & \text{ブラウザ} \\ \hline
\text{15} & \text{td\_browser\_version} & \text{ブラウザバージョン} \\ \hline
\text{16} & \text{td\_screen} & \text{スクリーンサイズ} \\ \hline
\text{17} & \text{td\_viewpoint} & \text{ビューポイントサイズ} \\ \hline
\text{18} & \text{td\_color} & \text{色深度} \\ \hline
\text{19} & \text{td\_language} & \text{言語} \\ \hline
\text{20} & \text{td\_charset} & \text{文字コード} \\ \hline
\text{21} & \text{td\_version} & \text{JSバージョン} \\ \hline
\text{22} & \text{td\_description} & \text{ディスクリプション} \\ \hline
\end{array}
$$

次以降でこのタグで取得できるデータをもとに、利用できる関数を紹介していきます。

TreasureData関数紹介

① セッションIDを付与：TD_SESSIONIZE_WINDOW

TD_SESSIONIZE_WINDOW(time, 1800) OVER(PARTITION BY td_client_id ORDER BY time)
-- td_client_idをユニークIDとして、30分間ページ上で何も行動がない場合にセッションが切れる定義

この関数を利用することで、GoogleAnalyticsでよく分析指標で用いられる「セッション数」を算出することができ、ユーザー行動を分析しやすくなります。同一セッションと判断したアクセスに対して、同一のUUIDを振り分けてくれます。

例えば、1つのtd_client_idに対して、セッションIDを付与するSQLを実行してみます。

SELECT
  TD_SESSIONIZE_WINDOW(time, 1800) OVER(PARTITION BY td_client_id ORDER BY time) AS session_id
  , TD_TIME_FORMAT(time, 'yyyy-MM-dd HH:mm:ss', 'JST') AS day
  , td_client_id
FROM
  corporate_log
WHERE
  td_client_id = '30bf9355-917d-472f-85bb-fb98dbbd31ce'
ORDER BY
  day

結果として同一ユーザーIDに対して、セッションIDを付与することができました。（30分間ページ上で何も行動がない場合にセッションが切れる定義）

注意点としてはセッションIDは毎回ユニークに振られるので、集計を実施するたびに別のUUIDが割り振られてしまいます。

② IPアドレスから場所情報を付与：TD_IP_TO_××

TD_IP_TO_COUNTRY_NAME(td_ip) -- 国
TD_IP_TO_CITY_NAME(td_ip) -- 都道府県

IPアドレスから国や都道府県の情報を取得することができる関数です。
例えば、td_ipに対してSQLを実行してみます。

SELECT
  TD_IP_TO_COUNTRY_NAME(td_ip) AS country_name
  , TD_IP_TO_CITY_NAME(td_ip) AS city_name
FROM
  corporate_log

国の情報と都市情報を取得することができます。
国と都市情報以外にも緯度経度、郵便番号なども取得できるようです。

私自身はそれ以外を使用することはあまりありませんが、他の関数の詳細についてはTreasureDataのドキュメントを参照ください。

ドキュメントに記載がありますが、都市を判別するのに利用する元データは「maxmind」から取ってきているようです。

③ user_agentからブラウザなどの情報を付与

TD_PARSE_USER_AGENT(td_user_agent)

user_agentの情報をJSONで取得できます。アクセスしてきたブラウザやOSの種類を判別することができます。

オプションで下記を選択すると、全ての情報ではなく一部の情報を取得できます。私はよく「ua_family」を利用します。

os, os_family, os_major, os_minor, ua, ua_family, ua_major, ua_minor, device
SELECT DISTINCT
  td_user_agent
  , TD_PARSE_USER_AGENT(td_user_agent)
  , TD_PARSE_USER_AGENT(td_user_agent, 'ua_family')
FROM
  corporate_log

利用しているデバイスが、iosかandroidかなどの分類ができるので、デバイスの傾向分析などにも応用できそうです。

活用例

①：ブラウザ別のセッション辺りの平均滞在時間は？

→UIやサイト導線などを考える材料にしたい

WITH tmp_session AS (
  SELECT
    TD_SESSIONIZE_WINDOW(time, 1800) OVER(PARTITION BY td_client_id ORDER BY time) AS session_id
    , time
    , TD_PARSE_USER_AGENT(td_user_agent, 'ua_family') AS browser
  FROM
    corporate_log
) 
, tmp_min_max AS (
  SELECT
    session_id
    , browser
    , MAX(time) - MIN(time) AS stay_time
  FROM
    tmp_session
  GROUP BY
    session_id
    , browser
)

SELECT
  browser
  , SUM(stay_time) / COUNT(DISTINCT session_id) AS average_stay_time
FROM
  tmp_min_max
GROUP BY
  browser
ORDER BY
  browser


②：都市別のセッション数は？

→CMやチラシを都市別に出した際の効果測定を行いたい。

WITH tmp_session AS (
  SELECT
    TD_SESSIONIZE_WINDOW(time, 1800) OVER(PARTITION BY td_client_id ORDER BY time) AS session_id
    , TD_IP_TO_CITY_NAME(td_ip) AS city_name
  FROM
    corporate_log
) 

SELECT
  city_name
  , COUNT(DISTINCT session_id) AS session_cnt
FROM
  tmp_session
GROUP BY
  city_name
ORDER BY
  session_cnt DESC

まとめ

TD関数は非常に便利でログの分析に向いているものが多いですね。これらの関数を利用しながら、分析の質を高めていきたいです。

関連note


Legolissに関するお問い合わせはこちらへ！