LoginSignup
1
1

More than 5 years have passed since last update.

@doryokujin(Takahiro Inoue)

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

Posted at

はじめに

本シリーズは，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

要旨

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

前月に基づく Period Time Segment

また，1日のアクセスの中でも，どの時間帯の PV が多いのか，時間帯での内訳を見ることでユーザーを分類します。ただ，時間帯の区分の仕方というのはサイトの性質や分析の目的によって様々に異なるものですから，多分に多様性があります。
この時間帯のセグメント分けの方法でまず考えられるのは， '時間（24区分）' ごとに分けた上でいくつかの集合に統合することです。一般的には [00時, 06時)，[06時, 12時),[12時, 18時),[18時, 24時) のようにどの区分の中にも等しい数の '時間' が入るべきですが，今回は応用例として以下の様にユーザーの行動時間帯で区分することにします。

  • 「朝〜出勤（morning）」: 06時〜10時
  • 「会社(working)」: 10時〜17時
  • 「帰宅〜夜(night)」: 17時〜24時
  • 「就寝(sleeping)」: 24時〜06時

この分類の上で，以下のルールによりセグメントを作ります。

  • ルール1. 頻度および PV の意味で 50% 以上をその時間帯に閉める1組 → その時間帯に特化
  • ルール2. 頻度および PV の意味で 30% 以上をその時間帯に閉める2組 → その2組の時間帯に特化

ルールはいくらでも複雑にできますが，それに増してクエリの記述料が増えますので気をつけて下さい。
今回は以下の様な集計結果が得られます。後ほど詳説していきます。

target_month segment cnt
2018/10/01 working 5987
2018/10/01 sleeping 2409
2018/10/01 night 2367
2018/10/01 morning 1591
2018/10/01 sleeping,morning 38
2018/10/01 morning,working 19
2018/10/01 sleeping,working 18
2018/10/01 alltime 14
2018/10/01 working,night 7
2018/10/01 sleeping,night 5
2018/10/01 morning,night 4

4. Period Time Segment （時間帯）

まず初めに，普通の時間帯の集計方法であれば，数ステップの集計を踏むことになります。

  1. まず，ユーザー当たり '時間(24区分)' ごとの集計を行い，テーブルに格納する。このテーブルは今までのユーザーセグメントの結果と違って， 1ユーザー当たり1レコードでは無く24レコードになっている事に注意してください。
  2. 各時間がどの時間帯に属するかの24行のマスタテーブルを作成する。
  3. 1.の結果をインプットとして2.のマスタテーブルを充てた上で時間帯ごとの集計などを行う。

しかし，今回は以上のような数ステップを踏まずに，1つのクエリ内でセグメント分けを行う，前回までの手順を無視した方法で行います。理由として，

  • 上記のような数ステップで行う方が作業コストが高い
  • 上のようなステップでやっても汎用性がうまく確保できない

があります。これは実際にやってみないと感じることでは無いのですが，私はそう感じています。

さて，今回の長く，そして汎用性の無いクエリを以下に紹介します。ただ，このクエリを理解しさえすれば，うまく書き換えてすぐにセグメントが出せる便利なものになっています。

/* TD_SCHEDULED_TIME() = '2018-11-23 11:11:00' */
/*
SELECT target_month, segment, COUNT(1) AS cnt
FROM
(
*/
  SELECT target_month, 
    CASE 
      WHEN 50<=ratio_freq_sleeping OR 50<=ratio_pv_sleeping THEN 'sleeping'
      WHEN 50<=ratio_freq_morning  OR 50<=ratio_pv_morning  THEN 'morning'
      WHEN 50<=ratio_freq_working  OR 50<=ratio_pv_working  THEN 'working'
      WHEN 50<=ratio_freq_night    OR 50<=ratio_pv_night    THEN 'night'
      WHEN 
        (30<=ratio_freq_sleeping OR 30<=ratio_pv_sleeping) AND (30<=ratio_freq_morning OR 30<=ratio_pv_morning)
        THEN 'sleeping,morning'
      WHEN 
        (30<=ratio_freq_sleeping OR 30<=ratio_pv_sleeping) AND (30<=ratio_freq_working OR 30<=ratio_pv_working)
        THEN 'sleeping,working'
      WHEN 
        (30<=ratio_freq_sleeping OR 30<=ratio_pv_sleeping) AND (30<=ratio_freq_night OR 30<=ratio_pv_night)
        THEN 'sleeping,night'
      WHEN 
        (30<=ratio_freq_morning OR 30<=ratio_pv_morning) AND (30<=ratio_freq_working OR 30<=ratio_pv_working)
        THEN 'morning,working'
      WHEN 
        (30<=ratio_freq_morning OR 30<=ratio_pv_morning) AND (30<=ratio_freq_night OR 30<=ratio_pv_night)
        THEN 'morning,night'
      WHEN 
        (30<=ratio_freq_working OR 30<=ratio_pv_working) AND (30<=ratio_freq_night OR 30<=ratio_pv_night)
        THEN 'working,night'
      WHEN freq IS NULL THEN 'non_active'
      ELSE 'alltime'
    END AS segment,
    freq, freq_sleeping, freq_morning, freq_working, freq_night, 
    ratio_freq_sleeping, ratio_freq_morning, ratio_freq_working, ratio_freq_night,
    pv, pv_sleeping, pv_morning, pv_working, pv_night, 
    ratio_pv_sleeping, ratio_pv_morning, ratio_pv_working, ratio_pv_night
  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, freq_sleeping, freq_morning, freq_working, freq_night, 
      ROUND(1.0*freq_sleeping/freq,2)*100 AS ratio_freq_sleeping,
      ROUND(1.0*freq_morning /freq,2)*100 AS ratio_freq_morning,
      ROUND(1.0*freq_working /freq,2)*100 AS ratio_freq_working,
      ROUND(1.0*freq_night   /freq,2)*100 AS ratio_freq_night,

      pv, pv_sleeping, pv_morning, pv_working, pv_night, 
      ROUND(1.0*pv_sleeping/pv,2)*100 AS ratio_pv_sleeping,
      ROUND(1.0*pv_morning /pv,2)*100 AS ratio_pv_morning,
      ROUND(1.0*pv_working /pv,2)*100 AS ratio_pv_working,
      ROUND(1.0*pv_night   /pv,2)*100 AS ratio_pv_night,

      min_time, max_time
    FROM
    (
      SELECT
        td_client_id,
        SUM(IF(hour IN ('00','01','02','03','04','05'),            1, 0)) AS  freq_sleeping,
        SUM(IF(hour IN ('06','07','08','09'),                      1, 0)) AS  freq_morning,
        SUM(IF(hour IN ('10','11','12','13','14','15','16','17'),  1, 0)) AS  freq_working,
        SUM(IF(hour IN ('18','19','20','21','22','23'),            1, 0)) AS  freq_night,
        COUNT(1) AS freq,
        SUM(IF(hour IN ('00','01','02','03','04','05'),           pv, 0)) AS  pv_sleeping,
        SUM(IF(hour IN ('06','07','08','09'),                     pv, 0)) AS  pv_morning,
        SUM(IF(hour IN ('10','11','12','13','14','15','16','17'), pv, 0)) AS  pv_working,
        SUM(IF(hour IN ('18','19','20','21','22','23'),           pv, 0)) AS  pv_night,
        SUM(pv) AS pv,
        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,'HH','JST') AS hour, 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,'HH','JST')
      ) 
      GROUP BY td_client_id
    ) 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, segment HAVING segment != 'non_active'
ORDER BY cnt DESC
*/

クエリのポイント

1. '時間' ごとの集計

SELECT td_client_id, TD_DATE_TRUNC('day',time,'JST') AS access_day, TD_TIME_FORMAT(time,'HH','JST') AS hour, 
  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,'HH','JST')

上のクエリで， 日毎かつ時間毎の集計を行っています。 TD_TIME_FORMAT(time,'HH','JST') は， 与えられた time における時間： '00'〜'23' までの文字列を返す関数です。いきなり時間毎の集計を期間内全日で行っても良いですが，日毎の指標値を得たい応用の場合などを考え，日毎かつ時間毎にするのをお薦めします。

2. SUM(IF) による， Pivot 化

SUM(IF(hour IN ('00','01','02','03','04','05'),            1, 0)) AS  freq_sleeping,
SUM(IF(hour IN ('06','07','08','09'),                      1, 0)) AS  freq_morning,
SUM(IF(hour IN ('10','11','12','13','14','15','16','17'),  1, 0)) AS  freq_working,
SUM(IF(hour IN ('18','19','20','21','22','23'),            1, 0)) AS  freq_night,
  1. の時間毎の集計で得られたレコードは，同じユーザーでも各時間によって複数のレコードに分かれています。（これを「縦になっている」と呼びます。）これから時間帯ごとの比率を計算するのに当たって，この「縦になっている」状態は結構大変です。ですので上のクエリはこのユーザー当たり縦のレコードを「横にする（異なるカラムに分ける）」作業を上で行っています。

3. 時間帯ごとの（全体に対する）割合の計算

ROUND(1.0*freq_sleeping/freq,2)*100 AS ratio_freq_sleeping,
ROUND(1.0*freq_morning /freq,2)*100 AS ratio_freq_morning,
ROUND(1.0*freq_working /freq,2)*100 AS ratio_freq_working,
ROUND(1.0*freq_night   /freq,2)*100 AS ratio_freq_night,

今， freq にはユーザーの全時間帯の合計頻度が入っているとします。（ここでの freq は，1日で各時間帯でアクセスが1回でもある場合に1となりますから，1日でも最高4の値をとります。）一方， freq_* は各々の時間帯での頻度です。上では， 全体に対してそれぞれの時間帯での比率を求め，四捨五入した上で % での値を出しています。故にこの4つの比率の和を取ると 100% になります。pv の方でも同じ事をやっています。

4. 前月存在しなかったユーザーを出す必要が無ければパフォーマンスを上げられる

ここでも前回までの慣例に従って，前月登場しなかったユーザーも登場させるようにしています。（もちろん全ての freq の値が NULL となり，ここではあまり意味がありません。）前月までの全ての過去にアクセスする以下のサブクエリを取り除き，

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

かつ，上の方にある

IF(past_month.td_client_id IS NOT NULL, past_month.td_client_id, past_month_ago.td_client_id ) AS td_client_id,

この記述を純粋に td_client_id に書き換えれば，かなりのパフォーマンスアップを図る事ができます。

結果（ユーザー毎）

time.png

segment ratio_freq_sleeping ratio_freq_morning ratio_freq_working ratio_freq_night ratio_pv_sleeping ratio_pv_morning ratio_pv_working ratio_pv_night
sleeping,working 38 % 19 % 25 % 19 % 28 % 17 % 39 % 16 %
working 21 % 20 % 36 % 23 % 15 % 13 % 54 % 17 %
working 21 % 23 % 46 % 10 % 14 % 31 % 50 % 5 %
sleeping 59 % 32 % 9 % 0 % 34 % 31 % 35 % 0 %
working 18 % 0 % 76 % 6 % 17 % 0 % 80 % 3 %
working 0 % 6 % 82 % 12 % 0 % 14 % 80 % 6 %
morning 18 % 64 % 18 % 0 % 13 % 40 % 47 % 0 %
sleeping 82 % 18 % 0 % 0 % 82 % 18 % 0 % 0 %
working 0 % 0 % 90 % 10 % 0 % 0 % 97 % 3 %
working 0 % 0 % 78 % 22 % 0 % 0 % 97 % 3 %
sleeping 50 % 38 % 13 % 0 % 35 % 45 % 20 % 0 %
working 0 % 0 % 33 % 67 % 0 % 0 % 50 % 50 %
working 0 % 0 % 100 % 0 % 0 % 0 % 100 % 0 %
night 17 % 17 % 0 % 67 % 15 % 31 % 0 % 54 %
night 0 % 0 % 40 % 60 % 0 % 0 % 22 % 78 %
working 0 % 25 % 50 % 25 % 0 % 29 % 43 % 29 %
working 25 % 0 % 75 % 0 % 8 % 0 % 92 % 0 %
working 25 % 0 % 50 % 25 % 20 % 0 % 60 % 20 %
working 0 % 0 % 75 % 25 % 0 % 0 % 86 % 14 %
working 0 % 0 % 100 % 0 % 0 % 0 % 100 % 0 %
working 33 % 33 % 33 % 0 % 25 % 25 % 50 % 0 %
working 0 % 0 % 67 % 33 % 0 % 0 % 86 % 14 %
sleeping,working 33 % 0 % 33 % 33 % 43 % 0 % 29 % 29 %
working 0 % 0 % 33 % 67 % 0 % 0 % 50 % 50 %

結果で見るべきは上の9カラムで，先頭を除く前半4つが freq における割合， 後半4つが pv における割合を表しており，それぞれで「横」を見て傾向がないかを見ていきます。

今， freq の意味または pv の意味でどれかの時間帯の割合が 50% 以上であれば，十分にこの時間対に特化していると言うことができます。また，そうなっていないもので， どれか2つで 30% 以上の割合を占めていればその2つの時間帯に特化しているとここでは言うことにします。

結果（集計値）

クエリの上下のコメントを外して集計した結果が以下になります。

target_month segment cnt
2018/10/01 working 5987
2018/10/01 sleeping 2409
2018/10/01 night 2367
2018/10/01 morning 1591
2018/10/01 sleeping,morning 38
2018/10/01 morning,working 19
2018/10/01 sleeping,working 18
2018/10/01 alltime 14
2018/10/01 working,night 7
2018/10/01 sleeping,night 5
2018/10/01 morning,night 4

今回は 50% 以上の割合でその時間帯に特化としましたが，これを 60% や 75% にすれば， 2つの時間に特化したセグメントの数がそこから移動してくると思います。この当たりは各自の判断で適切な数値設定をして頂ければと思います。

本日は以上です。

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1