[データ分析のためのBigQueryクエリ]　SQLで年別RFM分析

2023年6月23日2023年8月9日

この記事では、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句で指定します。