![見出し画像](https://assets.st-note.com/production/uploads/images/128560452/rectangle_large_type_2_3935d1c6005cfc253de9e44f7387ee2f.png?width=1200)
【WEBログ分析の関数編】 TreasureData独自関数が覚えられない!TD利用歴2年半のエンジニアがよく使うものだけをまとめてみた!
はじめに
お客様のサポートをする中で、「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
![](https://assets.st-note.com/img/1704968708193-JTvfmqDc8Y.png?width=1200)
結果として同一ユーザー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
![](https://assets.st-note.com/img/1704968658173-i8aMyGprzB.png?width=1200)
国の情報と都市情報を取得することができます。
国と都市情報以外にも緯度経度、郵便番号なども取得できるようです。
私自身はそれ以外を使用することはあまりありませんが、他の関数の詳細については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
![](https://assets.st-note.com/img/1704968637879-SfyRsliYZ2.png?width=1200)
利用しているデバイスが、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
![](https://assets.st-note.com/img/1704968766773-acO4dqykjI.png)
②:都市別のセッション数は?
→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
![](https://assets.st-note.com/img/1704968793974-OvZHlOwTVv.png)
まとめ
TD関数は非常に便利でログの分析に向いているものが多いですね。これらの関数を利用しながら、分析の質を高めていきたいです。
関連note
Legolissに関するお問い合わせはこちらへ!
![](https://assets.st-note.com/img/1704968892590-TVaRwNCiQu.png?width=1200)