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


要旨

今回は,月次のユーザーセグメントを求める方法を紹介する第1回です。ちなみに,次回以降紹介するクエリは,全て今回のものがベースですので,やや難しめの今回を乗り切れば,次回以降は楽に読んでいけます。

さて,今回は連続する過去2ヶ月間におけるユーザーのアクティビティを取得し,時系列でのアクティビティの推移を加味した以下のセグメントを作成し,クエリによってこのセグメントを識別した上で集計できるようにします。

activity_past_1
activity_past_2
segment_name

active
active
継続

active
non_active
休眠→復活

active
welcome
新規→継続

non_active
active
継続→休眠

non_active
non_active
休眠

non_active
welcome
新規→休眠

welcome

新規

image (3).png

また,最後にはこれに「コンバージョン」の概念も含めてより細分化された応用も紹介します。


1. Activity Segment (「アクティブ」「非アクティブ」「新規」)

データベースに存在する全ユーザーに関して,ある月のアクセスの有無を鑑みることで


  • 「アクティブ」ユーザー: 『(月次の範囲内で)1回でもアクセスした非新規ユーザー』

  • 「新規」ユーザー: 『その月に初めてアクセスしたユーザー』

  • 「非アクティブ」ユーザー: 『それ以外のユーザー(以前はアクセスのあったユーザー)』

を,1つのクエリで求めます。この内,「アクティブ」ユーザーのみは月次の範囲内のレコードだけで導出できますが,それ以外のユーザーは月次より過去のレコードを参照することになります。


A. 前月に基づく Activity Segment

単月の Activity Segment を得るためのクエリは以下の様になります。このクエリの中で,

WHERE TD_INTERVAL(time, '-10y/-1M', 'JST') /* 1ヶ月前より過去 */

がありますが,この '-10y/-1M' は,「(TRUNCされた)1ヶ月前の10年分」の取得範囲を意味し, '-10y' にデータの保存期間より長い期間を指定することで「1ヶ月前の過去全て」を代替しています。

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

/*
SELECT target_month, 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(this.td_client_id IS NOT NULL, this.td_client_id, all_past.td_client_id) AS td_client_id,
CASE
WHEN this.td_client_id IS NOT NULL AND all_past.td_client_id IS NOT NULL THEN 'active'
WHEN this.td_client_id IS NULL AND all_past.td_client_id IS NOT NULL THEN 'non_active'
WHEN this.td_client_id IS NOT NULL AND all_past.td_client_id IS NULL THEN 'new'
END AS segment,
TD_TIME_FORMAT(min_time, 'yyyy-MM-dd', 'JST') AS min_time,
TD_TIME_FORMAT(max_time, 'yyyy-MM-dd', 'JST') AS max_time
FROM
(
SELECT td_client_id
FROM pageviews
WHERE TD_INTERVAL(time, '-10y/-1M', 'JST') /* 1ヶ月前より過去,厳密には以下を用いる */
/* WHERE TD_TIME_RANGE( time, NULL, TD_DATE_TRUNC('month', TD_DATE_TRUNC('month', TD_SCHEDULED_TIME(),'JST')-1, 'JST') ) */
GROUP BY td_client_id
) all_past
FULL OUTER JOIN
(
SELECT td_client_id, MIN(time) AS min_time, MAX(time) AS max_time
FROM pageviews
WHERE TD_INTERVAL(time, '-1M', 'JST')
GROUP BY td_client_id
) this
ON all_past.td_client_id = this.td_client_id
/*
)
GROUP BY target_month, segment
ORDER BY cnt DESC
*/

segment_monthly_activity.png

セグメントごとの集計値を比較するためには,クエリの上下のコメントアウトを外して実行します。

segment_monthly_activity_agg.png

このセグメント集計値を毎月実行するようにして, 'segent_monthly_activity_single' テーブルに append する設定にしておくことにしましょう。仮に 2018年01月から10月までの集計値がこのテーブルに溜まっていたといます。また, 本記事のアクセスログは 2018-01-01 から取得開始されたという前提としており,故に01月のユーザーセグメントは全て 'new' となっています。これは正しい挙動です。

table.png

上のように単純に SELECT で選んで来るだけでは,月毎の各々のセグメント数の推移などを把握することができません。そこで,以下の Pivot クエリでトレジャーデータ上で Pivot テーブルを作成します。

SELECT

target_month,
kv['active'] AS active,
kv['non_active'] AS non_active,
kv['new'] AS new,
kv['active']+kv['non_active']+kv['new'] AS total
FROM (
SELECT target_month, map_agg(segment, cnt) kv
FROM segment_monthly_acctivity
GROUP BY target_month
)
ORDER BY target_month

上の Pivot クエリは, segment カラム内の内訳を知っていないと書けないという意味で完全に汎用的なものでは無いことに注意してください。また, total までは求める必要はありません。

pivot.png

active、non_active、new、total.png

上の図は先ほど得られた結果を 'active','new' のユーザー数を左軸に, 'non_active', 'total' のユーザー数を右軸に据えた折れ線グラフの例となっています。月を追う毎に 'active' と 'new' のユーザー数が減り, 故に 'non_active' ユーザーが 'total' に接近している危機的な情報が見て取れますね。


B. 前月と前々月に基づく Activity Segment

ただ,先ほどの Activity では, 'non_active' だった人が,前から 'non_active' だったのか,直近までは 'active' だったのか,などの背景が読み取れません。それをするには,格納されたテーブルから過去の月とを比較するクエリを書かないといけません。そこで,ここでは1つのクエリで先々月のそれと同時に見る細分化セグメントを考えて行きます。ここからは,並びの都合上 'new' を 'welcome' に変えている事にご了承ください。

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

/*
SELECT target_month, activity_past_1, activity_past_2, COUNT(1) AS cnt,
MIN(min_time_past_1) AS min_time_past_1, MAX(max_time_past_1) AS max_time_past_1,
MIN(min_time_past_2) AS min_time_past_2, MAX(max_time_past_2) AS max_time_past_2
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,
past_1month.td_client_id,
past_1month.segment AS activity_past_1,
past_2month.segment AS activity_past_2,
past_1month.min_time AS min_time_past_1, past_1month.max_time AS max_time_past_1,
past_2month.min_time AS min_time_past_2, past_2month.max_time AS max_time_past_2
FROM
(
SELECT
IF(past_month.td_client_id IS NOT NULL, past_month.td_client_id, past_month_ago.td_client_id) AS td_client_id,
CASE
WHEN past_month.td_client_id IS NOT NULL AND past_month_ago.td_client_id IS NOT NULL THEN 'active'
WHEN past_month.td_client_id IS NULL AND past_month_ago.td_client_id IS NOT NULL THEN 'non_active'
WHEN past_month.td_client_id IS NOT NULL AND past_month_ago.td_client_id IS NULL THEN 'welcome'
END AS segment,
TD_TIME_FORMAT(past_month.min_time, 'yyyy-MM-dd', 'JST') AS min_time,
TD_TIME_FORMAT(past_month.max_time, 'yyyy-MM-dd', 'JST') AS max_time
FROM
(
SELECT td_client_id
FROM pageviews
WHERE TD_INTERVAL(time, '-10y/-1M', 'JST') /* 1ヶ月前より過去 */
/* WHERE TD_TIME_RANGE( time, NULL, TD_DATE_TRUNC('month', TD_DATE_TRUNC('month', TD_SCHEDULED_TIME(),'JST')-1, 'JST') ) */
GROUP BY td_client_id
) past_month_ago
FULL OUTER JOIN
(
SELECT td_client_id, MIN(time) AS min_time, MAX(time) AS max_time
FROM pageviews
WHERE TD_INTERVAL(time, '-1M', 'JST') /* 前月1ヶ月間 */
GROUP BY td_client_id
) past_month
ON past_month_ago.td_client_id = past_month.td_client_id
) past_1month
LEFT OUTER JOIN /* 集合 past_1month は past_2month を内包する */
(
SELECT
IF(past_month.td_client_id IS NOT NULL, past_month.td_client_id, past_month_ago.td_client_id) AS td_client_id,
CASE
WHEN past_month.td_client_id IS NOT NULL AND past_month_ago.td_client_id IS NOT NULL THEN 'active'
WHEN past_month.td_client_id IS NULL AND past_month_ago.td_client_id IS NOT NULL THEN 'non_active'
WHEN past_month.td_client_id IS NOT NULL AND past_month_ago.td_client_id IS NULL THEN 'welcome'
END AS segment,
TD_TIME_FORMAT(past_month.min_time, 'yyyy-MM-dd', 'JST') AS min_time,
TD_TIME_FORMAT(past_month.max_time, 'yyyy-MM-dd', 'JST') AS max_time
FROM
(
SELECT td_client_id
FROM pageviews
WHERE TD_INTERVAL(time, '-10y/-2M', 'JST') /* 1ヶ月前より過去 */
/* WHERE TD_TIME_RANGE( time, NULL, TD_DATE_TRUNC('month', TD_DATE_TRUNC('month', TD_DATE_TRUNC('month', TD_SCHEDULED_TIME(),'JST')-1, 'JST')-1, 'JST' )) */
GROUP BY td_client_id
) past_month_ago
FULL OUTER JOIN
(
SELECT td_client_id, MIN(time) AS min_time, MAX(time) AS max_time
FROM pageviews
WHERE TD_INTERVAL(time, '-1M/-1M', 'JST') /* 前々月1ヶ月間 */
GROUP BY td_client_id
) past_month
ON past_month_ago.td_client_id = past_month.td_client_id
) past_2month
ON past_1month.td_client_id = past_2month.td_client_id

/*
)
GROUP BY target_month, activity_past_1, activity_past_2
*/

ORDER BY activity_past_1, activity_past_2

shape.png

上のクエリにおいて, 1ヶ月前のアクティビティを求める サブクエリ: 'past_1month' 内では,上図の赤色とオレンジ色の領域(データ範囲)を使用し, 2ヶ月前のアクティビティを求める サブクエリ: 'past_2month' 内では,上図の紫色と緑色の領域(データ範囲)を使用して求めています。

1.png

上記クエリの 'min_time_past_1', 'max_time_past_1', 'min_time_past_2', 'max_time_past_2' は各々のサブクエリで想定の時間範囲内であるかを確認するもので, TD_SCHEDULED_TIME() = '2018-11-23 11:11:00' の想定では,


  • [ min_time_past_1, max_time_past_1 ] = [ 2018-10-01, 2018-10-31]

  • [ min_time_past_2, max_time_past_2 ] = [ 2018-09-01, 2018-09-30]

となっている事が確認できます。さて,上記クエリのコメント部分を外して,セグメントの集計をしましょう。この時,以下の組合せの集計結果が得られます。

target_month
activity_past_1
activity_past_2
cnt

2018-10-01
active
active
1219

2018-10-01
active
non_active
1047

2018-10-01
active
welcome
887

2018-10-01
non_active
active
2402

2018-10-01
non_active
non_active
454515

2018-10-01
non_active
welcome
11413

2018-10-01
welcome

9306

この組合せで注意したいのは,クエリの記述から考えられる組合せ 『'welcome', 'welcome'』 は存在せず,先月の 'welcome' は先々月では NULL が入ります。

この集計結果テーブルを 'monthly_segment_activity' として保存しておきます。

次に,このセグメントに分かりやすい名前を付けた以下のテーブルを 'monthly_segment_activity_master' として保存しておきます。

activity_past_1
activity_past_2
segment_name

active
active
継続

active
non_active
休眠→復活

active
welcome
新規→継続

non_active
active
継続→休眠

non_active
non_active
休眠

non_active
welcome
新規→休眠

welcome

新規

このようなセグメント分けによってマーケティングはターゲットを絞った施策が行いやすくなりますね。また,このような推移を表現するための有効な可視化手段として「2部グラフ」があります。

image (3).png

次に,先ほどと同様に 'monthly_segment_activity' を全ての月で集計結果を加えて,(全体として)この Activity Segment の推移を見てみましょう。

SELECT target_month, segment_name, cnt 

FROM segment_monthly_activity s
JOIN
( SELECT activity_past_1, activity_past_2, segment_name FROM segment_monthly_activity_master ) m
ON s.activity_past_1 = m.activity_past_1 AND s.activity_past_2 = m.activity_past_2
ORDER BY target_month

2.png

得られたテーブルを Pivot 化して可視化しましょう。Pivot 化は前述の通りトレジャーデータでできますが, Google Spreadsheet でやっちゃった方が楽ですね。

3.png

また,セグメントが多い分,折れ線の数がおおくなるので,カラー付きテーブルで各セグメントの推移を追っていくのも有効です。


さらに「コンバージョン」の有無で細分化

ここまで来るとさらに欲が出て,今度は 「active」, 「welcome」 の人の特徴をもっと知った上でセグメントを細分化したいと考えます。 個々で言う特徴とは例えば,


  • コンバージョンしたか否か:


    • 'active' → 'active' と 'active_cv'

    • 'welcome' → 'welcome' と 'welcome_cv'



  • 課金したか否か:


    • 'active' → 'active_free' と 'active_pay'

    • 'welcome' → 'welcome_free' と 'welcome_pay'



等です。課金については,別途課金テーブルを参照することになる(さらに課金額で細分化できる)ので,後ほどの「ECログ分析の限界に挑む」シリーズで紹介します。ここではコンバージョンを加味してセグメントを作ります。今回の例として,


  • td_title = 'お問い合わせ - Treasure Data'

にアクセスしたユーザーをコンバージョンしたユーザーとします。以下のクエリを前月および前々月のサブクエリ内に挿入しています。

LEFT OUTER JOIN

(
SELECT td_client_id
FROM pageviews
WHERE TD_INTERVAL(time, '-1M', 'JST') /* 前月1ヶ月間 */
AND td_title = 'お問い合わせ - Treasure Data'
GROUP BY td_client_id
) past_month_cv
ON past_month.td_client_id = past_month_cv.td_client_id

この past_month_cv 集合は完全に past_month 集合に含まれますので, LEFT OUTER JOIN で past_month.td_client_id = past_month_cv.td_client_id の一致でくっつけます。

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

/**/
SELECT target_month, activity_past_1, activity_past_2, COUNT(1) AS cnt,
MIN(min_time_past_1) AS min_time_past_1, MAX(max_time_past_1) AS max_time_past_1,
MIN(min_time_past_2) AS min_time_past_2, MAX(max_time_past_2) AS max_time_past_2
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,
CASE
WHEN past_1month.segment = 'active' AND past_1month.with_cv = 1 THEN 'active_cv'
WHEN past_1month.segment = 'active' AND past_1month.with_cv = 0 THEN 'active'
WHEN past_1month.segment = 'welcome' AND past_1month.with_cv = 1 THEN 'welcome_cv'
WHEN past_1month.segment = 'welcome' AND past_1month.with_cv = 0 THEN 'welcome'
ELSE past_1month.segment
END AS activity_past_1,
CASE
WHEN past_2month.segment = 'active' AND past_2month.with_cv = 1 THEN 'active_cv'
WHEN past_2month.segment = 'active' AND past_2month.with_cv = 0 THEN 'active'
WHEN past_2month.segment = 'welcome' AND past_2month.with_cv = 1 THEN 'welcome_cv'
WHEN past_2month.segment = 'welcome' AND past_2month.with_cv = 0 THEN 'welcome'
ELSE past_2month.segment
END AS activity_past_2,
past_1month.min_time AS min_time_past_1, past_1month.max_time AS max_time_past_1,
past_2month.min_time AS min_time_past_2, past_2month.max_time AS max_time_past_2
FROM
(
SELECT
IF(past_month.td_client_id IS NOT NULL, past_month.td_client_id, past_month_ago.td_client_id) AS td_client_id,
CASE
WHEN past_month.td_client_id IS NOT NULL AND past_month_ago.td_client_id IS NOT NULL THEN 'active'
WHEN past_month.td_client_id IS NULL AND past_month_ago.td_client_id IS NOT NULL THEN 'non_active'
WHEN past_month.td_client_id IS NOT NULL AND past_month_ago.td_client_id IS NULL THEN 'welcome'
END AS segment,
IF(past_month_cv.td_client_id IS NOT NULL,1,0) AS with_cv,
TD_TIME_FORMAT(past_month.min_time, 'yyyy-MM-dd', 'JST') AS min_time,
TD_TIME_FORMAT(past_month.max_time, 'yyyy-MM-dd', 'JST') AS max_time
FROM
(
SELECT td_client_id
FROM pageviews
WHERE TD_INTERVAL(time, '-10y/-1M', 'JST') /* 1ヶ月前より過去 */
GROUP BY td_client_id
) past_month_ago
FULL OUTER JOIN
(
SELECT td_client_id, MIN(time) AS min_time, MAX(time) AS max_time
FROM pageviews
WHERE TD_INTERVAL(time, '-1M', 'JST') /* 前月1ヶ月間 */
GROUP BY td_client_id
) past_month
ON past_month_ago.td_client_id = past_month.td_client_id
LEFT OUTER JOIN
(
SELECT td_client_id
FROM pageviews
WHERE TD_INTERVAL(time, '-1M', 'JST') /* 前月1ヶ月間 */
AND td_title = 'お問い合わせ - Treasure Data'
GROUP BY td_client_id
) past_month_cv
ON past_month.td_client_id = past_month_cv.td_client_id
) past_1month
LEFT OUTER JOIN /* 集合 past_1month は past_2month を内包する */
(
SELECT
IF(past_month.td_client_id IS NOT NULL, past_month.td_client_id, past_month_ago.td_client_id) AS td_client_id,
CASE
WHEN past_month.td_client_id IS NOT NULL AND past_month_ago.td_client_id IS NOT NULL THEN 'active'
WHEN past_month.td_client_id IS NULL AND past_month_ago.td_client_id IS NOT NULL THEN 'non_active'
WHEN past_month.td_client_id IS NOT NULL AND past_month_ago.td_client_id IS NULL THEN 'welcome'
END AS segment,
IF(past_month_cv.td_client_id IS NOT NULL,1,0) AS with_cv,
TD_TIME_FORMAT(past_month.min_time, 'yyyy-MM-dd', 'JST') AS min_time,
TD_TIME_FORMAT(past_month.max_time, 'yyyy-MM-dd', 'JST') AS max_time
FROM
(
SELECT td_client_id
FROM pageviews
WHERE TD_INTERVAL(time, '-10y/-2M', 'JST') /* 1ヶ月前より過去 */
/* WHERE TD_TIME_RANGE( time, NULL, TD_DATE_TRUNC('month', TD_DATE_TRUNC('month', TD_DATE_TRUNC('month', TD_SCHEDULED_TIME(),'JST')-1, 'JST')-1, 'JST' )) */
GROUP BY td_client_id
) past_month_ago
FULL OUTER JOIN
(
SELECT td_client_id, MIN(time) AS min_time, MAX(time) AS max_time
FROM pageviews
WHERE TD_INTERVAL(time, '-1M/-1M', 'JST') /* 前々月1ヶ月間 */
GROUP BY td_client_id
) past_month
ON past_month_ago.td_client_id = past_month.td_client_id
LEFT OUTER JOIN
(
SELECT td_client_id
FROM pageviews
WHERE TD_INTERVAL(time, '-1M/-1M', 'JST') /* 前々月1ヶ月間 */
AND td_title = 'お問い合わせ - Treasure Data'
GROUP BY td_client_id
) past_month_cv
ON past_month.td_client_id = past_month_cv.td_client_id
) past_2month
ON past_1month.td_client_id = past_2month.td_client_id

/**/
)
GROUP BY target_month, activity_past_1, activity_past_2
/**/
ORDER BY activity_past_1, activity_past_2

結果は以下の表のようになります。

target_month
activity_past_1
activity_past_2
cnt

2018-10-01
active
active
2491

2018-10-01
active
active_cv
29

2018-10-01
active
non_active
3608

2018-10-01
active
welcome
4546

2018-10-01
active
welcome_cv
38

2018-10-01
active_cv
active
25

2018-10-01
active_cv
active_cv
3

2018-10-01
active_cv
non_active
14

2018-10-01
active_cv
welcome
32

2018-10-01
active_cv
welcome_cv
4

2018-10-01
non_active
active
6906

2018-10-01
non_active
active_cv
38

2018-10-01
non_active
non_active
2019942

2018-10-01
non_active
welcome
66311

2018-10-01
non_active
welcome_cv
248

2018-10-01
welcome

73267

2018-10-01
welcome_cv

277

さて,このセグメントの組合せで先ほどと同じようにわかりやすい segment_name をつけた master テーブルを作りましょう。

activity_past_1
activity_past_2
segment_name
happy

active
active
継続
4

active
active_cv
CV→継続
4

active
non_active
休眠→復活
4

active
welcome
新規→継続
4

active
welcome_cv
新規CV→継続
3

active_cv
active
継続→継続CV
4

active_cv
active_cv
継続CV
5

active_cv
non_active
休眠→復活CV
5

active_cv
welcome
新規→継続CV
5

active_cv
welcome_cv
新規CV→継続CV
5

non_active
active
継続→休眠
2

non_active
active_cv
継続CV→休眠
2

non_active
non_active
休眠
1

non_active
welcome
新規→休眠
1

non_active
welcome_cv
新規CV→休眠
1

welcome

新規
3

welcome_cv

新規CV
5

今回は,そのセグメントがどれだけ嬉しいかを Happy セグメントして5段階評価してみました。

次回は単月の集計の戻って,月次の様々なセグメントを見ていきたいと思います。