Knowledge & Column

SQLTableau

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

こんにちは。
Legoliss データアナリストの音嶋健斗です。
このブログでは、データマーケティングにまつわる様々な情報をお届けします。


■はじめに

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


■本記事の内容

・TDタグで取得できるデータの前提確認
・TreasureData関数紹介
・まとめ


■WEBログについて前提確認

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

#

項目

説明

1

time

アクセス日時

2

td_client_id

1stPartyCookie

3

td_global_id

3rdPatyCookie

4

td_host

ホスト

5

td_path

パス

6

td_url

ページURL

7

td_title

ページタイトル

8

td_referrer

リファラURL

9

td_ip

IP

10

td_user_agent

ユーザーエージェント

11

td_os

OS

12

td_os_version

OSバージョン

13

td_platform

プラットフォーム

14

td_browser

ブラウザ

15

td_browser_version

ブラウザバージョン

16

td_screen

スクリーンサイズ

17

td_viewpoint

ビューポイントサイズ

18

td_color

色深度

19

td_language

言語

20

td_charset

文字コード

21

td_version

JSバージョン

22

td_description

ディスクリプション

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


■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

session_id

day

td_client_id

e09add3b-62b1-46a5-b8d1-72eed81f1df0

2020-07-10 13:08:02

30bf9355-917d-472f-85bb-fb98dbbd31ce

e09add3b-62b1-46a5-b8d1-72eed81f1df0

2020-07-10 13:12:41

30bf9355-917d-472f-85bb-fb98dbbd31ce

c0742a0f-edec-4bb0-b2bf-0c1d92634ce9

2020-07-10 13:46:32

30bf9355-917d-472f-85bb-fb98dbbd31ce

b7cf19d6-ed3c-42cc-9033-c872516b6b9e

2020-07-10 15:05:29

30bf9355-917d-472f-85bb-fb98dbbd31ce

b7cf19d6-ed3c-42cc-9033-c872516b6b9e

2020-07-10 15:16:38

30bf9355-917d-472f-85bb-fb98dbbd31ce

b7cf19d6-ed3c-42cc-9033-c872516b6b9e

2020-07-10 15:26:08

30bf9355-917d-472f-85bb-fb98dbbd31ce

b7cf19d6-ed3c-42cc-9033-c872516b6b9e

2020-07-10 15:49:55

30bf9355-917d-472f-85bb-fb98dbbd31ce

3952179c-e4bf-45b1-92b8-658952b14003

2020-07-11 08:34:00

30bf9355-917d-472f-85bb-fb98dbbd31ce

eeddecd5-1f7c-4aa1-85da-e55f33f92714

2020-07-12 10:43:08

30bf9355-917d-472f-85bb-fb98dbbd31ce

結果として同一ユーザー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

td_ip

country_name

city_name

60.114.232.31

Japan

Shibuya

210.254.147.3

Japan

Yokohama

210.190.125.10

Japan

Saga

150.249.198.36

Japan

Nishiazabu

106.180.37.46

Japan

Suginami-ku

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

私自身はそれ以外を使用することはあまりありませんが、他の関数の詳細については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

td_user_agent

all

ua_family

Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.87 Safari/537.36

{"user_agent": {"family": "Chrome", "major": "80", "minor": "0", "patch": "3987"}, "os": {"family": "Mac OS X", "major": "10", "minor": "13", "patch": "6", "patch_minor": ""}, "device": {"family": "Other"}}

Chrome

Mozilla/5.0 (iPad; CPU OS 13_3_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148 Safari Line/10.3.0

{"user_agent": {"family": "LINE", "major": "10", "minor": "3", "patch": "0"}, "os": {"family": "iOS", "major": "13", "minor": "3", "patch": "1", "patch_minor": ""}, "device": {"family": "iPad"}}

LINE

Mozilla/5.0 (iPhone; CPU iPhone OS 13_3_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/13.0.5 Mobile/15E148 Safari/604.1

{"user_agent": {"family": "Mobile Safari", "major": "13", "minor": "0", "patch": "5"}, "os": {"family": "iOS", "major": "13", "minor": "3", "patch": "1", "patch_minor": ""}, "device": {"family": "iPhone"}}

Mobile Safari

利用しているデバイスが、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

browser

average_stay_time

Firefox

127

IE

90

LINE

126


②：都市別のセッション数は？
→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

city_name

session_cnt

Shibuya

1000

Yokohama

900

Shinjuku

800


■まとめ

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


＜このブログの執筆者＞
株式会社Legoliss
データアナリスト　音嶋健斗

 

〔関連記事〕

【Athena】「簡単！安価！」にSQLで分析できる環境を、AWS-Athenaを利用して構築する！概要から実際の手順をまとめてみた！

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

 

, , , , ,

関連記事