Edited at

『Treasure Data でアクセスログ分析の限界に挑む』その③ 〜アクセスに基づいたユーザーセグメントの作成 月次編(平日/土日)〜


はじめに

本シリーズは,Treasure Data,つまり基本SQLで実現可能な様々なアクセスログ分析事例を具体的なクエリと可視化を交えて紹介していくブログです。多くは当たり前のものですが,時には実に良く捻られたものと思って頂けるよう,私が知りうる限りの全ての事例を提供していく予定です。

今回の内容を理解するに当たって,参考になる記事を挙げておきます。


今回のテーマ

今回は『ユーザーがアクセスしたか否か』,この一点のみに着目してそこから導き出される様々な指標を導出します。

そして,これらが今後有益なユーザー『セグメント』として機能して行くことをお見せします。


  • これから数回に渡って「日次」「週次」「月次」それぞれについて,「アクセスの有無」に基づいた様々な指標を求めるクエリを紹介します。

  • セグメント対象となるユーザーは,特定の範囲内のユーザーではなく,常にデータベース上の全ユーザーとなります。

  • 今回は「月次編」の「曜日毎のアクセスタイプ」に着目します。


前提とするデータ定義

本シリーズでは以下の様な pageview テーブルを想定していきますが,多くの場合では time カラムと td_client_id カラム(または何らかのユーザーを識別するカラム)しか使いませんので,アクセスログに限らず様々なログで応用可能です。

time
td_client_id
td_title
td_ip

1542956154
c445a751288
Treasure Data
72.215.178.105

1542955952
6b5a0357b2f
Treasure Data
103.206.191.199

1542955625
344503ae8cb
td command line tool reference
185.3.22.17

1542954891
178e1a1d3e4
SQL & Performance Tuning Tips
103.250.170.50

1542954151
f556371264a
Installing and Updating...
39.110.208.102

1542953658
c31baa01dfe
Planning and Implementation...
203.132.82.75

1542953160
9894e8afc72
gtm-msr
66.102.8.8


要旨

今回は,月次のユーザーセグメントを求める方法を紹介する第3回目です。


前月に基づく Weekend Segment

前回は前月(および前々月)のアクセス頻度を求めました。この頻度は,さらに詳細である「何曜日」のアクセスが多いのかという情報を得ることはそれほど難しくなさそうです。ただ,曜日毎の内訳を見てもそれほど有用ではないので,「平日」「土日」「全日」に偏ったユーザーの3セグメントに区分する方法を紹介します。今回は単月のみの集計なので簡単です。今回の成果は次の表になります。

target_month
segment
cnt

2018-10-01
weekend
1476

2018-10-01
weekday
10804

2018-10-01
allday
179


3. Weekend Segment (平日/土日/全日)

まず,各ユーザーがアクセスのあった日の「曜日」を求めるには以下の週番号を取り出せる次の句を用います。

TD_TIME_FORMAT(time,'u','JST') # 1(月曜日)  7(日曜日)

また,下記のクエリは前月の土日の日数と平日の日数を集計し,それぞれのユーザーレコードに付与しています。

SELECT         

SUM(IF(week_num IN ('6','7'), 1,0)) AS freq_weekend_total,
SUM(IF(week_num NOT IN ('6','7'), 1,0)) AS freq_weekday_total
FROM
(
SELECT TD_DATE_TRUNC('day',time,'JST') AS access_day, TD_TIME_FORMAT(time,'u','JST') AS week_num
FROM pageviews
WHERE TD_INTERVAL(time, '-1M', 'JST')
GROUP BY TD_DATE_TRUNC('day',time,'JST'), TD_TIME_FORMAT(time,'u','JST')
)

freq_weekend_total
freq_weekday_total

8
23

10月ならば平日が23回,土日が8回あったことがわかります。これを各ユーザーの土日/平日のアクセス頻度に割れば,全土日/全平日に対するアクセス割合がわかり,この割合で双方を比較することができるようになります。

/* TD_SCHEDULED_TIME() = '2018-11-23 11:11:00' */

/**/
SELECT target_month,
CASE
WHEN 70 <= ratio_freq_weekend OR 70 <= ratio_pv_weekend THEN 'weekend'
WHEN ratio_freq_weekend <= 30 OR ratio_pv_weekend <= 30 THEN 'weekday'
WHEN ratio_freq_weekend IS NULL THEN 'non_active'
ELSE 'allday'
END AS segment,
COUNT(1) AS cnt
FROM
(
/**/
SELECT
TD_TIME_FORMAT(TD_DATE_TRUNC('month', TD_DATE_TRUNC('month', TD_SCHEDULED_TIME(),'JST')-1, 'JST'),'yyyy-MM-dd','JST') AS target_month,
IF(past_month.td_client_id IS NOT NULL, past_month.td_client_id, past_month_ago.td_client_id ) AS td_client_id,
freq_weekend, freq_weekend_total,
freq_weekday, freq_weekday_total,
ROUND(1.0*freq_weekend/freq_weekend_total,2)*100 AS freq_weekend_ratio,
ROUND(1.0*freq_weekday/freq_weekday_total,2)*100 AS freq_weekday_ratio,
ROUND(
( 1.0*freq_weekend/freq_weekend_total ) / ( 1.0*freq_weekend/freq_weekend_total + 1.0*freq_weekday/freq_weekday_total )
,2)*100 AS ratio_freq_weekend,
pv_weekend, pv_weekday,
ROUND(1.0*pv_weekend/freq_weekend_total,2)*100 AS pv_weekend_avg,
ROUND(1.0*pv_weekday/freq_weekday_total,2)*100 AS pv_weekday_avg,
ROUND(
( 1.0*pv_weekend/freq_weekend_total ) / ( 1.0*pv_weekend/freq_weekend_total + 1.0*pv_weekday/freq_weekday_total )
,2)*100 AS ratio_pv_weekend,
freq_weekend_total, freq_weekday_total,
min_time, max_time
FROM
(
SELECT td_client_id, freq_weekend_total, freq_weekday_total, freq_weekend, freq_weekday, pv_weekend, pv_weekday, min_time, max_time
FROM
(
SELECT
td_client_id,
SUM(IF(week_num IN ('6','7'), 1,0)) AS freq_weekend,
SUM(IF(week_num NOT IN ('6','7'), 1,0)) AS freq_weekday,
SUM(IF(week_num IN ('6','7'), pv,0)) AS pv_weekend,
SUM(IF(week_num NOT IN ('6','7'), pv,0)) AS pv_weekday,
TD_TIME_FORMAT(MIN(min_time),'yyyy-MM-dd','JST') AS min_time, TD_TIME_FORMAT(MAX(max_time),'yyyy-MM-dd','JST') AS max_time
FROM
(
SELECT td_client_id, TD_DATE_TRUNC('day',time,'JST') AS access_day, TD_TIME_FORMAT(time,'u','JST') AS week_num, COUNT(1) AS pv, MIN(time) AS min_time, MAX(time) AS max_time
FROM pageviews
WHERE TD_INTERVAL(time, '-1M', 'JST')
GROUP BY td_client_id, TD_DATE_TRUNC('day',time,'JST'), TD_TIME_FORMAT(time,'u','JST')
)
GROUP BY td_client_id
),
(
SELECT
SUM(IF(week_num IN ('6','7'), 1,0)) AS freq_weekend_total,
SUM(IF(week_num NOT IN ('6','7'), 1,0)) AS freq_weekday_total
FROM
(
SELECT TD_DATE_TRUNC('day',time,'JST') AS access_day, TD_TIME_FORMAT(time,'u','JST') AS week_num
FROM pageviews
WHERE TD_INTERVAL(time, '-1M', 'JST')
GROUP BY TD_DATE_TRUNC('day',time,'JST'), TD_TIME_FORMAT(time,'u','JST')
)
)
) past_month
FULL OUTER JOIN
(
SELECT td_client_id
FROM pageviews
WHERE TD_INTERVAL(time, '-10y/-1M', 'JST') /* 1ヶ月前より過去 */
GROUP BY td_client_id
) past_month_ago
ON past_month.td_client_id = past_month_ago.td_client_id
/* 前月分のみのユーザーのみ取得で良いのならば past_month_ago サブクエリは不要 */
/**/
)
GROUP BY target_month,
CASE
WHEN 70 <= ratio_freq_weekend OR 70 <= ratio_pv_weekend THEN 'weekend'
WHEN ratio_freq_weekend <= 30 OR ratio_pv_weekend <= 30 THEN 'weekday'
WHEN ratio_freq_weekend IS NULL THEN 'non_active'
ELSE 'allday'
END
/**/

7f7009b8b68a9ba3edeef1c1bde4ca9e.png

今回は多くの指標が求められていますので,いくつか説明します。これらの指標は今回は集計されてしまいますが,ユーザー毎のアクセス傾向を見るためのとても有用な指標です。


  • freq_weekend_ratio: 土日のアクセス比率 = (土日にアクセスした回数) / (全土日数) [%]

  • freq_weekday_ratio: 平日のアクセス比率 = (平日にアクセスした回数) / (全平日数) [%]

  • ratio_freq_weekend: 土日の(全日に対する)占有率 = (土日のアクセス比率) / (土日のアクセス比率 + 平日のアクセス比率) [%]

  • pv_weekend_avg: 土日の PV 比率 = (土日の総 PV) / (全土日数) [%]

  • pv_weekday_avg: 平日の PV 比率 = (平日の総 PV) / (全平日数) [%]

  • ratio_pv_weekend: 土日の(全日に対する) PV 占有率 = (土日の PV 比率) / (土日の PV 比率 + 平日の PV 比率) [%]

(ratio_freq_weekend と ratio_pv_weekend は比率の比率を求めているので,気持ち悪い指標ですが今回のセグメント分けに使う指標としてこれくらいしか思いつきませんでした。ご了承ください。)

freq_weekend
freq_weekend_total
freq_weekday
freq_weekday_total
freq_weekend_ratio
freq_weekday_ratio
ratio_freq_weekend
pv_weekend
pv_weekday
pv_weekend_avg
pv_weekday_avg
ratio_pv_weekend

3 日
8 日
5 日
23 日
38 %
22 %
63 %
39 回
113 回
488 回/日
491 回/日
50 %

2 日
8 日
4 日
23 日
25 %
17 %
59 %
5 回
10 回
63 回/日
43 回/日
59 %

0 日
8 日
5 日
23 日
0 %
22 %
0 %
0 回
43 回
0 回/日
187 回/日
0 %

0 日
8 日
4 日
23 日
0 %
17 %
0 %
0 回
15 回
0 回/日
65 回/日
0 %

1 日
8 日
0 日
23 日
13 %
0 %
100 %
1 回
0 回
13 回/日
0 回/日
100 %

0 日
8 日
2 日
23 日
0 %
9 %
0 %
0 回
31 回
0 回/日
135 回/日
0 %

1 日
8 日
2 日
23 日
13 %
9 %
59 %
12 回
38 回
150 回/日
165 回/日
48 %

2 日
8 日
9 日
23 日
25 %
39 %
39 %
56 回
98 回
700 回/日
426 回/日
62 %

0 日
8 日
5 日
23 日
0 %
22 %
0 %
0 回
128 回
0 回/日
557 回/日
0 %

0 日
8 日
1 日
23 日
0 %
4 %
0 %
0 回
1 回
0 回/日
4 回/日
0 %

0 日
8 日
20 日
23 日
0 %
87 %
0 %
0 回
150 回
0 回/日
652 回/日
0 %

1 日
8 日
0 日
23 日
13 %
0 %
100 %
2 回
0 回
25 回/日
0 回/日
100 %

2 日
8 日
10 日
23 日
25 %
43 %
37 %
19 回
55 回
238 回/日
239 回/日
50 %

image.png

もう少し説明します。 freq_weekend_ratio, freq_weekday_ratio は土日/平日,各々で独立して頻度の度合いを見ますので,両方とも比率が大きいユーザーもいれば,両方とも比率が小さいユーザーもいます。また,数値の大小はそもそものアクセス頻度にも依存します。アクセス頻度の少ないユーザーでも,土日/平日のどちらかのタイプかを見るには双方を比較する別の指標が必要になってきます。

また, pv_weekend_ratio, pv_weekday_ratio は全土日/全平日に対する平均 PV を各々出していますので,頻度が少ないユーザーでも PV がどちらかに極端に大きければこちらに傾向が現れてきます。

そこで,今回は非常にざっくりですが, ratio_freq_weekend または ratio_pv_weekend が 70% 以上のユーザーを「土日」ユーザー, 30%以下のユーザーを「平日」ユーザー,それ以外を「全日」ユーザーと区別することにします。上のクエリの上下のコメントアウトを外して集計実行しましょう。必要なら集計結果を 'monthly_segment_weektype' テーブルに格納しておきます。

target_month
segment
cnt

2018-10-01
weekend
1476

2018-10-01
weekday
10804

2018-10-01
allday
179

2018-10-01
non_active
2422201

この区分の方法では,以外に全日のユーザーが少ない事がわかります。尚,私のクエリでは non_active ユーザーも求めてしまっていますが,前月にアクティブであったユーザーのみを保持するのであれば, 1ヶ月より前の過去全てを見る past_month_ago サブクエリが無くなり,パフォーマンスが格段に向上します。

最後に,以下のマスタを 'monthly_segment_weektype_master' に格納しておきます。

segment
segment_name

weekend
土日

weekday
平日

allday
全日

non_active
休眠