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

# はじめに

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

- [『優しく学ぶ Treasure Data』その① 〜日付関数（DATE UDF）を理解しよう〜](https://qiita.com/doryokujin/items/18a29916374ab67c7be2)
- [『Treasure Data でアクセスログ分析の限界に挑む』その① 〜「日次」「週次」「月次」の集計を正しく理解する〜](https://qiita.com/doryokujin/items/773f6008e420c7f3260d)

# 今回のテーマ

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

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

# 前提とするデータ定義

本シリーズでは以下の様な 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](https://qiita-image-store.s3.amazonaws.com/0/4752/05cccca3-d2b3-2fff-3ff3-713072a5e731.png)
また，最後にはこれに「コンバージョン」の概念も含めてより細分化された応用も紹介します。

# 1. Activity Segment （「アクティブ」「非アクティブ」「新規」）

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

- 「アクティブ」ユーザー: 『（月次の範囲内で）1回でもアクセスした非新規ユーザー』
- 「新規」ユーザー： 『その月に初めてアクセスしたユーザー』
- 「非アクティブ」ユーザー： 『それ以外のユーザー（以前はアクセスのあったユーザー）』

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

## A. 前月に基づく Activity Segment

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

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

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

```sql
/* 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](https://qiita-image-store.s3.amazonaws.com/0/4752/6c44f3f9-8b3a-9b1b-b466-591e193804db.png)
セグメントごとの集計値を比較するためには，クエリの上下のコメントアウトを外して実行します。
![segment_monthly_activity_agg.png](https://qiita-image-store.s3.amazonaws.com/0/4752/1603e1db-c349-b0d1-74d4-e186abbfbec0.png)
このセグメント集計値を毎月実行するようにして， 'segent_monthly_activity_single' テーブルに append する設定にしておくことにしましょう。仮に 2018年01月から10月までの集計値がこのテーブルに溜まっていたといます。また， 本記事のアクセスログは 2018-01-01 から取得開始されたという前提としており，故に01月のユーザーセグメントは全て 'new' となっています。これは正しい挙動です。
![table.png](https://qiita-image-store.s3.amazonaws.com/0/4752/2496086f-89fb-bb90-1289-6bc2a0d24c77.png)
上のように単純に SELECT で選んで来るだけでは，月毎の各々のセグメント数の推移などを把握することができません。そこで，以下の Pivot クエリでトレジャーデータ上で Pivot テーブルを作成します。

```sql
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](https://qiita-image-store.s3.amazonaws.com/0/4752/92923dc3-eda9-5e22-73cf-7a7a313e05d2.png)
![active、non_active、new、total.png](https://qiita-image-store.s3.amazonaws.com/0/4752/98e0c557-ccab-b9ae-48f4-ca372b71ef92.png)
上の図は先ほど得られた結果を 'active','new' のユーザー数を左軸に， 'non_active', 'total' のユーザー数を右軸に据えた折れ線グラフの例となっています。月を追う毎に 'active' と 'new' のユーザー数が減り， 故に 'non_active' ユーザーが 'total' に接近している危機的な情報が見て取れますね。

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

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

```sql
/* 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](https://qiita-image-store.s3.amazonaws.com/0/4752/6afeabd6-f41f-2ca4-29fb-cbdb845dc6e6.png)
上のクエリにおいて， 1ヶ月前のアクティビティを求める サブクエリ: 'past_1month' 内では，上図の赤色とオレンジ色の領域（データ範囲）を使用し， 2ヶ月前のアクティビティを求める サブクエリ: 'past_2month' 内では，上図の紫色と緑色の領域（データ範囲）を使用して求めています。
![1.png](https://qiita-image-store.s3.amazonaws.com/0/4752/d2256d3c-fb17-0532-72c1-372b60b49114.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](https://qiita-image-store.s3.amazonaws.com/0/4752/05cccca3-d2b3-2fff-3ff3-713072a5e731.png)
次に，先ほどと同様に  'monthly_segment_activity' を全ての月で集計結果を加えて，（全体として）この Activity Segment の推移を見てみましょう。

```sql
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](https://qiita-image-store.s3.amazonaws.com/0/4752/3b6a566f-68f6-4217-703e-c92bc355d0b1.png)
得られたテーブルを Pivot 化して可視化しましょう。Pivot 化は前述の通りトレジャーデータでできますが， Google Spreadsheet でやっちゃった方が楽ですね。

![3.png](https://qiita-image-store.s3.amazonaws.com/0/4752/dcea18ca-2f93-18f7-1896-d3083bbae730.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'

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

```sql
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 の一致でくっつけます。

```sql
/* 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段階評価してみました。

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