[データ分析のためのBigQueryクエリ]SQLで年別RFM分析
この記事では、BigQueryを使用してデータ分析を行う方法を紹介します。
RFM分析はPythonなどで行うケースが多いですが、SQLでも非常なシンプルなクエリで算出することができます。
RFM分析を行うことで、優良顧客の定義や、購買意欲の高い顧客属性を発掘が可能なります。
概要
この記事では、BigQueryを使用してデータ分析を行う方法を紹介します。RFM分析はPythonなどで行うケースが多いですが、SQLでも非常なシンプルなクエリで算出することができます。RFM分析を行うことで、優良顧客の定義や、購買意欲の高い顧客属性を発掘が可能なります。
実装概要
まずRFM分析とは、下記の三つの指標から顧客行動を分析するための手法です。
- Recency（最新購入日）：顧客が最後に購買した日付からの日数
- Frequency（頻度）：顧客が期間内で購買した回数
- Monetary（金額）：顧客が期間内で利用した金額
詳しいRFM分析に関しては、外部リンクですが、こちらをご参照ください。
顧客の注文単位のレコードを持つテーブルから、顧客ごとに上記の指標を算出します。
実装コード
SELECT
customer_No,
count(customer_No) as Frequency,
datetime_diff("2022-12-31",max(order_datetime),month) as Recency,
sum(total_price) as Monetary
FROM `dataset.table`
WHERE order_datetime between "2022-01-01" AND "2022-12-31"
GROUP BY customer_No
手順
利用するテーブルのカラム
ECサイトであればシステムがデフォルトで持っているテーブルかと思います。なおオーダーされたプロダクトごとのテーブルの場合は、注文IDなどで括って下記のテーブルを作成してください。
クエリの説明
SELECT 文:Recencyは顧客の最近注文日から算出期間の終了日の差を月単位で算出しています。
WHERE句:算出したい期間のbetween句で指定します。
まとめ
上記のクエリによって、任意の年のRFMを算出できます。算出結果から、各指標が全て上位20%の顧客を抽出し優良顧客の属性を発掘したり、各指標が中央60%の顧客を抽出して上位20%に転換する施策の導出などに利用できます。
年別RFM分析を利用した年別優良顧客数推移の算出方法の記事はこちらです。
