【SQL】LAG関数とOVER関数を使い、購入周期や累積購入金額を計算する - 元エンジニアPMのプロダクトマネージャーお役立ち情報

toCサービスやECサービスでプロダクトやマーケティングの施策を考える際、いかにリピートして購入してもらえるかが重要になります。リピートする顧客を特定する際に、購入周期や過去累積で購入していたのかを分析する必要があると思います。

今回はそんな購入周期や累積購入金額を計算するクエリをご紹介します。

リピート化施策に向けたRFM分析

リピート顧客の打ち手を考えるにあたってRFM分析というものはよく使われます。

RFM分析は、リピート購入の可能性の高い顧客を特定するために使用されるマーケティングツールです。RFMとは、Recency（最近購入）、Frequency（購入頻度）、Monetary（購入金額）の頭文字をとったものです。

・最後購入したのか？

・どれくらいの頻度で購入しているのか？

・どれくらいお金をかけているのか？

これらをセグメントに分けてボリュームを可視化し、それぞれのセグメント別に施策を検討するために使います。

下記はRFM分析の表になります。表から、顧客001は最も優れたRFMスコアを持っていることがわかります。この顧客は最近購入があり、高い頻度で購入し、大きな金額を購入しています。顧客001に対しては、特別なプロモーションや割引を提供することで、リピート購入を促すことができます。

顧客ID	最近購入日	購入回数	購入金額
001	2023/03/01	5	50,000
002	2023/02/15	2	20,000
003	2022/03/10	1	5,000
004	2023/02/28	3	30,000
005	2022/01/15	4	40,000

顧客の決済データから購入周期や累積購入金額を計算する。

さて今回は実際にある決済情報テーブルを使って、購入回数や購入周期を算出します。施策を考えるにあたって、初回から2回目までどれくらいの期間が空くのか、ボリュームはどれくらいなのかを計算します。

今回例で使うテーブルはこちらです。レコードにユーザーIDと購入日・金額が入るものを想定します。

ユーザーID	購入日	購入金額
12345	2023/01/01	3000
12346	2023/01/02	2000
12347	2023/01/03	4000
12345	2023/01/07	5000
12346	2023/01/08	3000
12345	2023/01/14	1500
12347	2023/01/09	2000

まずは購入回数と周期を計算するクエリをご紹介します。

SELECT
  ユーザーID,
  COUNT(*) AS 累積の購入回数,
  DATEDIFF(購入日, LAG(購入日) OVER (PARTITION BY ユーザーID ORDER BY 購入日)) AS 前回購入からの日数,
  SUM(購入金額) OVER (PARTITION BY ユーザーID ORDER BY 購入日) AS それまでの購入金額
FROM
  決済情報テーブル
GROUP BY
  ユーザーID, 購入金額, 購入日
ORDER BY
  ユーザーID, 購入日

このクエリで得られる結果は下記になります。

ユーザーID	累積の購入回数	前回購入からの日数	それまでの購入金額
12345	1	NULL	3000
12345	2	6	8000
12345	3	7	9500
12346	1	NULL	2000
12346	2	6	5000
12347	1	NULL	4000
12347	2	6	6000

クエリのポイント

LAG関数を使い、前のデータと比較する

LAG() 関数は、現在の行の前にある行の値を取得する関数です。 LAG() 関数は、 OVER 句で指定された順序に従って、前の行の値を取得します。 LAG() 関数は、行のグループ化されたセットに対して使用されることが多く、データの前の行と現在の行との間の差を計算するのに役立ちます。

DATEDIFF(購入日, LAG(購入日) OVER (PARTITION BY ユーザーID ORDER BY 購入日))

LAGを使うことで、ユーザーIDごとに購入日別にソートした購入日の差分を比較することができます。1月7日と1月1日の差分を計算している形です。

SUM関数とOVER関数を使って、累積を計算する

SUM() OVER() 関数は、行のグループ化されたセットに対して使用され、指定された列の合計を計算します。 OVER() 句は、行の順序を指定し、どの行が集計に含まれるかを制御します。パーティション内の各行に、その時点までの合計が表示されます。この関数を使用することで、グループ化されたセットの各行に、その行までの集計値を含む新しい列を追加できます。

SUM(購入金額) OVER (PARTITION BY ユーザーID ORDER BY 購入日) AS それまでの購入金額

ここではユーザーIDごとに購入日別にソートした金額の合計を表示するようにしています。何回目でいくら使うのか？の特定などもできますね。