■■■小さな分析を積み重ねるアドホック分析■■■
- データと対話し、全体から部分へ分析を進める
- データを分けるのは「時間ごと」「グループごと」
■■■ファクトデータを活かす時系列分析■■■
■時系列分析■
目的
- 今後の動きを予測する
- 異常値の原因を突き止める
ファクトデータ：実験 / 観測データを集めたデータ
■利用データ■
利用ファクトデータ：購買履歴テーブル（購買履歴で日付ごとに購買額を合計し、それに７日間の移動平均の算出値を付加して表示。
・item_purchase_anlyticsテーブル
term_name|item_category|action_date |place |user_id|price|
---------|-------------|----------|-------|-------|-----|
サバ |food |2019-03-29|store_A|UID0001| 280|
しょう油 |food |2019-03-29|store_A|UID0001| 280|
しょう油 |food |2019-03-30|store_A|UID0001| 200|
サバ |food |2019-03-30|store_A|UID0001| 350|
卵 |food |2019-03-29|store_A|UID0001| 200|
サバ |food |2019-03-29|store_A|UID0001| 350|
鶏肉 |food |2019-04-01|store_A|UID0001| 450|
鶏肉 |food |2019-04-02|store_A|UID0001| 450|
深皿 |zakka |2019-04-03|store_A|UID0002| 980|
卵 |food |2019-04-04|store_A|UID0001| 280|
しょう油 |food |2019-04-04|store_A|UID0003| 280|
卵 |food |2019-04-05|store_A|UID0001| 280|
卵 |food |2019-04-06|store_A|UID0001| 280|
サバ |food |2019-04-07|store_A|UID0001| 280|
サバ |food |2019-04-08|store_A|UID0001| 280|
卵 |food |2019-04-09|store_A|UID0001| 280|
しょう油 |food |2019-04-10|store_A|UID0001| 280|
卵 |food |2019-04-10|store_A|UID0001| 280|
サバ |food |2019-04-10|store_A|UID0004| 280|
卵 |food |2019-04-12|store_A|UID0001| 280|
卵 |food |2019-04-12|store_A|UID0001| 280|
サバ |food |2019-04-13|store_A|UID0001| 280|
しょう油 |food |2019-04-13|store_A|UID0001| 280|
深皿 |zakka |2019-04-14|store_A|UID0003| 980|
深皿 |zakka |2019-04-14|store_A|UID0003| 780|
・ウィンドウ関数で、日付売上と７日間移動平均の売上を求めるSQL
SELECT
action_date,
SUM(price) AS ampunt_price,
# 日付毎にグループ化されたデータに対して一週間分の合計を出しその平均を求める
# グループ化されているので、対象列データを集約関数でまとめてからAVG()する。
AVG(SUM(price)) OVER(
ORDER BY
action_date
ROWS
BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_average
FROM
item_purchase_anlytics
GROUP BY
action_date
ORDER BY
action_date;
・結果
action_date|ampunt_price|moving_average|
-----------|------------|--------------|
2019-03-29| 1110| 1110.0000|
2019-03-30| 550| 830.0000|
2019-04-01| 450| 703.3333|
2019-04-02| 450| 640.0000|
2019-04-03| 980| 708.0000|
2019-04-04| 560| 683.3333|
2019-04-05| 280| 625.7143|
2019-04-06| 280| 507.1429|
2019-04-07| 280| 468.5714|
2019-04-08| 280| 444.2857|
2019-04-09| 280| 420.0000|
2019-04-10| 840| 400.0000|
2019-04-12| 560| 400.0000|
2019-04-13| 560| 440.0000|
2019-04-14| 1760| 651.4286|
■■■クロス集計■■■
例）性別、エリア毎でユーザー数をクロス集計するSQLを分解して理解する。
・master_customerテーブル
user_id|age|sex|user_place|
-------|---|---|----------|
UID0001| 45|M |Kanagawa |
UID0002| 31|M |Osaka |
UID0003| 22|M |Tokyo |
UID0004| 25|W |Tokyo |
UID0005| 36|W |Tokyo |
UID0006| 40|W |Kanagawa |
UID0007| 34|M |Kanagawa |
UID0008| 24|W |Tokyo |
UID0009| 30|M |Tokyo |
UID0010| 25|W |Osaka |
UID0011| 20|W |Tokyo |
UID0012| 31|W |Osaka |
① まず、性別・地域ごとのユーザー数を集計する。（これが１つめのサブクエリになる）
・SQL
WITH count_tmp AS(
SELECT
sex,
user_place,
COUNT(user_id) AS user_count
FROM
master_customer
GROUP BY
user_place,
sex
ORDER BY
user_place DESC
),
・結果
sex|user_place|user_count|
---|----------|----------|
M |Tokyo | 2|
W |Tokyo | 4|
M |Osaka | 1|
W |Osaka | 2|
M |Kanagawa | 2|
W |Kanagawa | 1|
性別・エリア別のデータ数が導かれた。
② ①のサブクエリを用いて男女で共通になるように、地域に連番を付ける
・SQL
WITH count_tmp AS(
SELECT
sex, user_place, COUNT(user_id) AS user_count
FROM
master_customer
GROUP BY
user_place, sex
ORDER BY
user_place DESC
)
SELECT
*,
ROW_NUMBER() OVER( PARTITION BY sex) AS sequence
FROM
count_tmp;
・結果
sex|user_place|user_count|sequence|
---|----------|----------|--------|
M |Tokyo | 2| 1|
M |Osaka | 1| 2|
M |Kanagawa | 2| 3|
W |Tokyo | 4| 1|
W |Osaka | 2| 2|
W |Kanagawa | 1| 3|
③ ①と②のサブクエリ結果を用いてseqence列の番号ごとにユーザー数の集計値を表示させる。
・最終SQL
WITH count_tmp AS(
SELECT
sex, user_place, COUNT(user_id) AS user_count
FROM
master_customer
GROUP BY
user_place, sex
ORDER BY
user_place DESC
),
tmp AS(
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY sex ) AS sequence
FROM
count_tmp
)
SELECT
sex,
MAX(CASE sequence WHEN 1 THEN user_count ELSE NULL END) AS Tokyo,
MAX(CASE sequence WHEN 2 THEN user_count ELSE NULL END) AS Osaka,
MAX(CASE sequence WHEN 3 THEN user_count ELSE NULL END) AS Kanagawa
FROM
tmp
GROUP BY
sex;
・最終結果
sex|Tokyo|Osaka|Kanagawa|
---|-----|-----|--------|
M | 2| 1| 2|
W | 4| 2| 1|
■■■アドホック分析１〜全体から部分へ分析を進める■■■
・item_purchase_anlyticsテーブル
term_name|item_category|action_date |place |user_id|price|
---------|-------------|----------|-------|-------|-----|
サバ |food |2019-03-29|store_A|UID0001| 280|
しょう油 |food |2019-03-29|store_A|UID0001| 280|
しょう油 |food |2019-03-30|store_A|UID0001| 200|
サバ |food |2019-03-30|store_A|UID0001| 350|
卵 |food |2019-03-29|store_A|UID0001| 200|
サバ |food |2019-03-29|store_A|UID0001| 350|
鶏肉 |food |2019-04-01|store_A|UID0001| 450|
鶏肉 |food |2019-04-02|store_A|UID0001| 450|
深皿 |zakka |2019-04-03|store_A|UID0002| 980|
卵 |food |2019-04-04|store_A|UID0001| 280|
しょう油 |food |2019-04-04|store_A|UID0003| 280|
卵 |food |2019-04-05|store_A|UID0001| 280|
卵 |food |2019-04-06|store_A|UID0001| 280|
サバ |food |2019-04-07|store_A|UID0001| 280|
サバ |food |2019-04-08|store_A|UID0001| 280|
卵 |food |2019-04-09|store_A|UID0001| 280|
しょう油 |food |2019-04-10|store_A|UID0001| 280|
卵 |food |2019-04-10|store_A|UID0001| 280|
サバ |food |2019-04-10|store_A|UID0004| 280|
卵 |food |2019-04-12|store_A|UID0001| 280|
卵 |food |2019-04-12|store_A|UID0001| 280|
サバ |food |2019-04-13|store_A|UID0001| 280|
しょう油 |food |2019-04-13|store_A|UID0001| 280|
深皿 |zakka |2019-04-14|store_A|UID0003| 980|
深皿 |zakka |2019-04-14|store_A|UID0003| 780|
例）上記テーブルを用いて、直近購買日と購買頻度から顧客をランク付けし、顧客のステージを、優良顧客、旧優良顧客、新規顧客、離脱に分類する。
顧客のランク付けは直近購買日、購買頻度それぞれで３段階にランク付けする。
1) 全体像の把握、limit句を付けてテーブルを確認（pandasのhead()みたいな感じで確認。）
・SQL
SELECT
*
FROM
item_purchase_anlytics
LIMIT 10;
・結果
term_name|item_category|action_date|place |user_id|price|
---------|-------------|-----------|-------|-------|-----|
サバ |food | 2019-03-29|store_A|UID0001| 280|
しょう油 |food | 2019-03-29|store_A|UID0001| 280|
しょう油 |food | 2019-03-30|store_A|UID0001| 200|
サバ |food | 2019-03-30|store_A|UID0001| 350|
卵 |food | 2019-03-29|store_A|UID0001| 200|
サバ |food | 2019-03-29|store_A|UID0001| 350|
鶏肉 |food | 2019-04-01|store_A|UID0001| 450|
鶏肉 |food | 2019-04-02|store_A|UID0001| 450|
深皿 |zakka | 2019-04-03|store_A|UID0002| 980|
卵 |food | 2019-04-04|store_A|UID0001| 280|
2) 全体のテーブルの行数の確認
・SQL
SELECT
COUNT(action_date) AS 日付カウント,
SUM(price) AS 料金合計
FROM
item_purchase_anlytics;
・結果
日付カウント|料金合計|
------|----|
25|9220|
3) ユーザー毎の購買状況を確認するため、ユーザー毎の購買頻度と直近の購買日を求める。
・SQL
SELECT
user_id,
MAX(action_date),
COUNT(action_date) AS frequency
FROM
item_purchase_anlytics
GROUP BY
user_id ;
・結果
user_id|MAX(action_date)|frequency|
-------|----------------|---------|
UID0001| 2019-04-13| 20|
UID0002| 2019-04-03| 1|
UID0003| 2019-04-14| 3|
UID0004| 2019-04-10| 1|
4) 顧客がサービスから遠ざかっているかを確認するため、基準日を'2019-05-01'として、基準日から最新購入日までの日数を求める。その日数が大きいほどサービスから遠ざかっている客と判断できる。
・SQL（recency列が、基準日からの日数）
SELECT
user_id,
'2019-05-01' AS benchmark_date,
MAX(action_date) AS recent_date,
DATEDIFF('2019-05-01', MAX(action_date)) AS recency,
COUNT(action_date) AS frequency
FROM
item_purchase_anlytics
GROUP BY
user_id ;
・結果
user_id|benchmark_date|recent_date|recency|frequency|
-------|--------------|-----------|-------|---------|
UID0001|2019-05-01 | 2019-04-13| 18| 20|
UID0002|2019-05-01 | 2019-04-03| 28| 1|
UID0003|2019-05-01 | 2019-04-14| 17| 3|
UID0004|2019-05-01 | 2019-04-10| 21| 1|
5--1) recencyとfrequencyに基づくユーザーのランク付け１
- recency(新・旧ユーザーのランク)と、frequency(購入頻度のランク)を、NTILE関数でランク付けするSQL
SELECT
user_id,
recency,
frequency,
NTILE(3) OVER(ORDER BY recency DESC) AS recency_rank,
NTILE(3) OVER(ORDER BY frequency) AS frequency_rank
FROM(
SELECT
user_id,
'2019-05-01' AS benchmark_date,
MAX(action_date) AS recent_date,
DATEDIFF('2019-05-01', MAX(action_date)) AS recency,
COUNT(action_date) AS frequency
FROM
item_purchase_anlytics
GROUP BY
user_id
) AS user_rfm
ORDER BY
user_id ;
・結果
user_id |recency|frequency|recency_rank|frequency_rank|
------- |------- |--------- |------------ |-------------- |
UID0001| 18| 20| 2| 3|
UID0002| 28| 1| 1| 1|
UID0003| 17| 3| 3| 2|
UID0004| 21| 1| 1| 1|
・NTILE関数の構文
NTILE (<分けるグループの数>) OVER(
[ PARTITION BY <カラムリスト> ]
[ ORDER BY <ソート用カラムリスト> ]
[ <フレーム句> ] )
※自動的に引数の数にランク付けされるが、データが思わぬ偏り方をしてしまう場合があるので注意。
5-2) ランク付けをCASE式でやる場合の方法。(NTILEを使わない方法）
・SQL
SELECT
user_id,
recency,
frequency,
CASE
WHEN recency < 19 THEN 3
WHEN recency < 26 THEN 2
ELSE 1
END AS recency_rank,
CASE
WHEN frequency >= 10 THEN 3
WHEN frequency >= 5 THEN 2
WHEN frequency >= 1 THEN 1
END AS frequency_rank
FROM (
SELECT
user_id,
'2019-05-01' AS benchmark_date,
MAX(action_date) AS recent_date,
DATEDIFF('2019-05-01', MAX(action_date)) AS recency,
COUNT(action_date) AS frequency
FROM
item_purchase_anlytics
GROUP BY
user_id
) AS user_rf
ORDER BY
user_id;
・結果
user_id|recency|frequency|recency_rank|frequency_rank|
-------|-------|---------|------------|--------------|
UID0001| 18| 20| 3| 3|
UID0002| 28| 1| 1| 1|
UID0003| 17| 3| 3| 1|
UID0004| 21| 1| 2| 1|
CASE式を使うことで、rankのしきい値を自分で設定できる。
6) クロス集計で5の結果を表に整える
ー最終的に知りたいのは、直近購入日と購買頻度による集計結果なので、直近購入日のランクと購買頻度のランクでクロス集計をかける。
・クロス集計内容
・インデックス：直近購入日のランク(r_1, r_2, r_3)
・カラム：購買頻度のランク(f_1, f_2, f_3)
・集計値：該当するユーザー数
・SQL
WITH user_rank_table AS(
SELECT
user_id,
recency,
frequency,
CASE
WHEN recency < 19 THEN 3
WHEN recency < 26 THEN 2
ELSE 1
END AS recency_rank,
CASE
WHEN frequency >= 10 THEN 3
WHEN frequency >= 5 THEN 2
WHEN frequency >= 1 THEN 1
END AS frequency_rank
FROM(
SELECT
user_id,
'2019-05-01' AS benchmark_date,
MAX(action_date) AS recent_date,
DATEDIFF('2019-05-01', MAX(action_date)) AS recency,
COUNT(action_date) AS frequency
FROM
item_purchase_anlytics
GROUP BY
user_id
) AS user_rf
ORDER BY user_id
)
SELECT
concat('r_', recency_rank) AS ranks,
COUNT(
CASE
WHEN frequency_rank = 3 THEN 1
END
) AS f_3,
COUNT(
CASE
WHEN frequency_rank = 2 THEN 1
END
) AS f_2,
COUNT(
CASE
WHEN frequency_rank = 1 THEN 1
END
) AS f_1
FROM
user_rank_table
GROUP BY
recency_rank
ORDER BY
ranks DESC;
・結果
ranks|f_3|f_2|f_1|
----- |--- |--- |--- |
r_3 | 1| 0| 1|
r_2 | 0| 0| 1|
r_1 | 0| 0| 1|
■■■アドホック分析２〜集計と深堀り■■■
指標を追加し、３指標のランクを求める。上記では、直近購入日と購買頻度の２つの指標でランクを求めた。ここでは、さらに価格を加えた３つの指標で分析をする。
1) 料金を追加し全体を把握する。
・SQL
SELECT
DATEDIFF('2019-05-01', MAX(action_date)) AS recency,
COUNT(action_date) AS frequency,
SUM(price) AS monetary
FROM
item_purchase_anlytics;
・結果
recency|frequency|monetary|
-------|---------|--------|
17| 25| 9220|
ユーザー全体の購入金額は9220円。次に、ユーザーごとの購入金額を確認
・SQL
SELECT
user_id,
DATEDIFF('2019-05-01', MAX(action_date)) AS recency,
COUNT(action_date) AS frequency,
SUM(price) AS monetary
FROM
item_purchase_anlytics
GROUP BY
user_id ;
・結果
user_id|recency|frequency|monetary|
-------|-------|---------|--------|
UID0001| 18| 20| 5920|
UID0002| 28| 1| 980|
UID0003| 17| 3| 2040|
UID0004| 21| 1| 280|
ユーザーごとでは、購入金額に大きな違いがある。
2) ３指標でランクを求める（新しい指標は、ユーザーごとの購入金額でのランク付け、
列名：m_rank）
・SQL
SELECT
user_id,
recency,
frequency,
monetary,
CASE
WHEN recency < 19 THEN 3
WHEN recency < 26 THEN 2
ELSE 1
END AS r_rank,
CASE
WHEN frequency >= 10 THEN 3
WHEN frequency >= 5 THEN 2
WHEN frequency >= 1 THEN 1
END AS f_rank,
CASE
WHEN monetary >= 50 THEN 3
WHEN monetary >= 10 THEN 2
ELSE 1
END AS m_rank
FROM(
SELECT
user_id,
DATEDIFF('2019-05-01', MAX(action_date)) AS recency,
COUNT(action_date) AS frequency,
SUM(price) AS monetary
FROM
item_purchase_anlytics
GROUP BY
user_id
) AS user_rfm;
・結果
user_id|recency|frequency|monetary|r_rank|f_rank|m_rank|
-------|-------|---------|--------|------|------|------|
UID0001| 18| 20| 5920| 3| 3| 3|
UID0002| 28| 1| 980| 1| 1| 3|
UID0003| 17| 3| 2040| 3| 1| 3|
UID0004| 21| 1| 280| 2| 1| 3|
m_rankの値がすべて３になってしまったので、しきい値を以下のように調整する。
CASE
WHEN monetary >= 5000 THEN 3
WHEN monetary >= 1000 THEN 2
ELSE 1
END AS m_rank
・結果２
user_id|recency|frequency|monetary|r_rank|f_rank|m_rank|
-------|-------|---------|--------|------|------|------|
UID0001| 18| 20| 5920| 3| 3| 3|
UID0002| 28| 1| 980| 1| 1| 1|
UID0003| 17| 3| 2040| 3| 1| 2|
UID0004| 21| 1| 280| 2| 1| 1|
これで、ユーザーそれぞれの、recency / frequency / monetary のランクが求められた。
最後に、ランクごとにユーザー数がどの程度いるかを３指標のクロス集計で調べる。
3) ランク(1~3)を示す連番テーブルの作成
・SQL
SELECT
1 AS rfm_index
UNION ALL
SELECT
2 AS rfm_index
UNION ALL
SELECT
3 AS rfm_index ;
・結果
rfm_index|
---------|
1|
2|
3|
このrfm_indexの1~3は、r_rank, f_rank, m_rankの各ランクを示している。
次に、顧客ごとのランクの結果テーブルと上記の連番テーブルを用いてクロスジョインして集計用フラグを作成する。
4) 集計用フラグテーブルの作成
・SQL
WITH user_rank_table AS(
SELECT
user_id, recency, frequency, monetary,
CASE
WHEN recency < 19 THEN 3
WHEN recency < 26 THEN 2
ELSE 1
END AS r_rank,
CASE
WHEN frequency >= 10 THEN 3
WHEN frequency >= 5 THEN 2
WHEN frequency >= 1 THEN 1
END AS f_rank,
CASE
WHEN monetary >= 5000 THEN 3
WHEN monetary >= 1000 THEN 2
ELSE 1
END AS m_rank
FROM(
SELECT
user_id, DATEDIFF('2019-05-01', MAX(action_date)) AS recency,
COUNT(action_date) AS frequency, SUM(price) AS monetary
FROM
item_purchase_anlytics
GROUP BY
user_id
) AS user_rfm
),
index_table AS(
SELECT
1 AS rfm_index
UNION ALL
SELECT
2 AS rfm_index
UNION ALL
SELECT
3 AS rfm_index
)
SELECT
index_table.rfm_index,
CASE
WHEN index_table.rfm_index = user_rank_table.r_rank THEN 1
ELSE 0
END AS r_flag,
CASE
WHEN index_table.rfm_index = user_rank_table.f_rank THEN 1
ELSE 0
END AS f_flag,
CASE
WHEN index_table.rfm_index = user_rank_table.m_rank THEN 1
ELSE 0
END AS m_flag
FROM
index_table
CROSS JOIN
user_rank_table;
・結果
rfm_index|r_flag|f_flag|m_flag|
--------- |------ |------ |------ |
3| 1| 1| 1|
2| 0| 0| 0|
1| 0| 0| 0|
3| 0| 0| 0|
2| 0| 0| 0|
1| 1| 1| 1|
3| 1| 0| 0|
2| 0| 0| 1|
1| 0| 1| 0|
3| 0| 0| 0|
2| 1| 0| 0|
1| 0| 1| 1|
これで、ユーザーごとのr_rank, f_rank, m_rank がr_flag, f_flag, m_flagの0, 1フラグでわかるようになった。例えば、一番上の３行はuser_idがUID001のデータで、r_rank, f_rank, m_rankのすべてのランクが3であることがわかる。
5) 4のテーブルを集計してランクごとの集計値を求める
・SQL
WITH user_rank_table AS(
SELECT
user_id, recency, frequency, monetary,
CASE
WHEN recency < 19 THEN 3
WHEN recency < 26 THEN 2
ELSE 1
END AS r_rank,
CASE
WHEN frequency >= 10 THEN 3
WHEN frequency >= 5 THEN 2
WHEN frequency >= 1 THEN 1
END AS f_rank,
CASE
WHEN monetary >= 5000 THEN 3
WHEN monetary >= 1000 THEN 2
ELSE 1
END AS m_rank
FROM(
SELECT
user_id, DATEDIFF('2019-05-01', MAX(action_date)) AS recency,
COUNT(action_date) AS frequency, SUM(price) AS monetary
FROM
item_purchase_anlytics
GROUP BY
user_id
) AS user_rfm
),
index_table AS(
SELECT
1 AS rfm_index
UNION ALL
SELECT
2 AS rfm_index
UNION ALL
SELECT
3 AS rfm_index
),
rfm_flag AS(
SELECT
index_table.rfm_index,
CASE
WHEN index_table.rfm_index = user_rank_table.r_rank THEN 1
ELSE 0
END AS r_flag,
CASE
WHEN index_table.rfm_index = user_rank_table.f_rank THEN 1
ELSE 0
END AS f_flag,
CASE
WHEN index_table.rfm_index = user_rank_table.m_rank THEN 1
ELSE 0
END AS m_flag
FROM
index_table
CROSS JOIN
user_rank_table
)
SELECT
rfm_index AS rfm_rank,
SUM(r_flag) AS r_sum,
SUM(f_flag) AS f_sum,
SUM(m_flag) AS m_sum
FROM
rfm_flag
GROUP BY
rfm_index
ORDER BY
rfm_index DESC ;
・結果
rfm_rank|r_sum|f_sum|m_sum|
-------- |----- |----- |----- |
3| 2| 1| 1|
2| 1| 0| 1|
1| 1| 3| 2|
ランクごとの集計値が求まった。
0コメント