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

はじめに

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

• 『優しく学ぶ Treasure Data』その① 〜日付関数（DATE UDF）を理解しよう〜
• 『Treasure Data でアクセスログ分析の限界に挑む』その① 〜「日次」「週次」「月次」の集計を正しく理解する〜
• 『Treasure Data でアクセスログ分析の限界に挑む』その② 〜アクセスに基づいたユーザーセグメントの作成 月次編（アクティビティ）〜
• 『Treasure Data でアクセスログ分析の限界に挑む』その② 〜アクセスに基づいたユーザーセグメントの作成 月次編（頻度）〜

今回のテーマ

今回は『ユーザーがアクセスしたか否か』，この一点のみに着目してそこから導き出される様々な指標を導出します。
そして，これらが今後有益なユーザー『セグメント』として機能して行くことをお見せします。

• これから数回に渡って「日次」「週次」「月次」それぞれについて，「アクセスの有無」に基づいた様々な指標を求めるクエリを紹介します。
• セグメント対象となるユーザーは，特定の範囲内のユーザーではなく，常にデータベース上の全ユーザーとなります。
• 今回は「月次編」の「曜日毎のアクセスタイプ」に着目します。

前提とするデータ定義

本シリーズでは以下の様な 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
/**/

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

• 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 %

もう少し説明します。 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	休眠