【永久保存版】BigQuery 実購買データ分析でよく使うSQL RFM分析編

BigQueryを用いたデータ分析で頻繁に出てくるSQL文をここにまとめます。
各自のデータベースに合わせてお使いください。

##前提

今回用いるテーブルのスキーマとデータ例です。

テーブル名：items

フィールド名	データ型	説明
user_id	STRING	ユーザ ID
order_id	STRING	受注番号
total_price	INTEGER	購入合計金額
item_id	STRING	商品 ID
item_name	STRING	商品名
category_name	STRING	カテゴリ名
purchased_at	DATE	購入日

行	user_id	order_id	total_price	item_id	item_name	category_name	purchased_at
1	62178	272324	2780	702671	ピカチュウのぬいぐるみ	ぬいぐるみ	2021-11-01
2	62178	272324	3830	423852	ピカチュウの帽子	帽子	2021-11-01
3	43532	272325	3830	940234	コダックのぬいぐるみ	ぬいぐるみ	2021-11-01
4	62178	272324	2780	702671	ピカチュウのぬいぐるみ	ぬいぐるみ	2021-11-03
5	28564	272327	1200	435666	ミュウのキーホルダー	キーホルダー	2021-11-04
・	・・・	・・・	・・・	・・・	・・・	・・・	・・・

##RFM分析

###R値
欲しい結果：今日の日付から購入日までの日数を算出

R値算出

DATE_DIFF(DATE (CURRENT_DATE("Asia/Tokyo")), CAST (購入日 AS DATE), DAY) AS Recently

例文

SELECT
*,
DATE_DIFF(DATE (CURRENT_DATE("Asia/Tokyo")), CAST (purchased_at AS DATE), DAY) AS Recently
FROM
 items

結果（本日が2021/11/11と仮定）

行	user_id	order_id	total_price	item_id	item_name	category_name	purchased_at	Recently
1	62178	272324	2780	702671	ピカチュウのぬいぐるみ	ぬいぐるみ	2021-11-01	10
2	62178	272324	3830	423852	ピカチュウの帽子	帽子	2021-11-01	10
3	43532	272325	3830	940234	コダックのぬいぐるみ	ぬいぐるみ	2021-11-01	10
4	62178	272326	2780	702671	ピカチュウのぬいぐるみ	ぬいぐるみ	2021-11-03	7
5	28564	272327	1200	435666	ミュウのキーホルダー	キーホルダー	2021-11-04	6
・	・・・	・・・	・・・	・・・	・・・	・・・	・・・	・・・

###F値
欲しい結果：今日までの購入回数を算出

F値算出

DENSE_RANK() OVER (PARTITION BY ユーザーID ORDER BY 購入日 ASC ) AS Frequency

例文

SELECT
*,
DENSE_RANK() OVER (PARTITION BY user_id ORDER BY purchased_at ASC ) AS Frequency
FROM
 items

結果

行	user_id	order_id	total_price	item_id	item_name	category_name	purchased_at	Frequency
1	62178	272324	2780	702671	ピカチュウのぬいぐるみ	ぬいぐるみ	2021-11-01	1
2	62178	272324	3830	423852	ピカチュウの帽子	帽子	2021-11-01	1
3	43532	272325	3830	940234	コダックのぬいぐるみ	ぬいぐるみ	2021-11-01	1
4	62178	272324	2780	702671	ピカチュウのぬいぐるみ	ぬいぐるみ	2021-11-03	2
5	28564	272327	1200	435666	ミュウのキーホルダー	キーホルダー	2021-11-04	1
・	・・・	・・・	・・・	・・・	・・・	・・・	・・・	・・・

###M値
例：今日の日付から購入日までの日数を算出

F値算出

SUM(購入額) OVER(PARTITION BY ユーザーID ORDER BY 注文ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Monetary

例文

SELECT
*,
SUM(total_price) OVER(PARTITION BY user_id ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Monetary
FROM
 items

結果

行	user_id	order_id	total_price	item_id	item_name	category_name	purchased_at	Monetary
1	62178	272324	2780	702671	ピカチュウのぬいぐるみ	ぬいぐるみ	2021-11-01	2780
2	62178	272324	3830	423852	ピカチュウの帽子	帽子	2021-11-01	6610
3	43532	272325	3830	940234	コダックのぬいぐるみ	ぬいぐるみ	2021-11-01	3830
4	62178	272324	2780	702671	ピカチュウのぬいぐるみ	ぬいぐるみ	2021-11-03	9390
・	・・・	・・・	・・・	・・・	・・・	・・・	・・・	・・・