前提
今回はDBに直接アクセスしてSQLを実行する方法で説明します。
リピーターとは
リピーターとは、同じサイトで1回以上購入をしているユーザのこと。
リピーターの定義をもとにデータの抽出条件を考えていきます。
集計条件
下記の条件を満たしていれば、リピーターの購入ということにしたいと思います。
- 初回購入日時が注文日時よりも前の取引
- 取引状態が購入完了後のステータスになっている取引
利用テーブルについて
EC-CUBE4のデフォルトのDB情報は下記のドキュメントに掲載されているので、参考にしながら進めていこうと思います。
初回購入日時が注文日時より前か判断する方法
dtb_customerテーブルのテーブル定義を確認すると、「first_buy_date」というカラムがあることがわかるので、このデータが注文日時より過去の日時になっていれば、リピーターということになります。
注文日時については、dtb_orderテーブルに「order_date」というカラムがあるので、これを使います。
取引状態を絞り込む方法
取引状態については、dtb_orderテーブルに「order_status_id」というカラムがあるのでこれを使います。「order_status_id」は、mtb_order_statusテーブルにマスターデータとして格納されています。
mtb_order_statusテーブルの説明を読んで、購入完了以降のidを選んでつかいます。今回は、1、4、5、6、7を使ってみようと思います。
リピーター取引抽出SQL
下記のSQLを実行することで、年月ごとにリピーター取引の数を集計できるようになります。
SELECT
DATE_FORMAT(o.order_date, '%Y-%m') AS ym,
COUNT(o.id) AS order_cnt
FROM
dtb_order AS o
INNER JOIN dtb_customer AS c ON c.id = o.customer_id
WHERE
o.order_date > c.first_buy_date
AND o.order_status_id IN (1,4,5,6,7)
AND o.order_date BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 23:59:59'
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
ORDER BY ym ASC
DATE_FORMAT(o.order_date, '%Y-%m') AS ym,
COUNT(o.id) AS order_cnt
FROM
dtb_order AS o
INNER JOIN dtb_customer AS c ON c.id = o.customer_id
WHERE
o.order_date > c.first_buy_date
AND o.order_status_id IN (1,4,5,6,7)
AND o.order_date BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 23:59:59'
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
ORDER BY ym ASC
