EC-CUBE4でリピーターの取引を集計する方法

EC-CUBE4でデータを集計することが増えてきたので、今回はリピーターの取引がどのくらいあるかを集計する方法をまとめてみようと思います。

前提

今回はDBに直接アクセスしてSQLを実行する方法で説明します。

リピーターとは

リピーターとは、同じサイトで1回以上購入をしているユーザのこと。

リピーターの定義をもとにデータの抽出条件を考えていきます。

集計条件

下記の条件を満たしていれば、リピーターの購入ということにしたいと思います。

• 初回購入日時が注文日時よりも前の取引
• 取引状態が購入完了後のステータスになっている取引

利用テーブルについて

EC-CUBE4のデフォルトのDB情報は下記のドキュメントに掲載されているので、参考にしながら進めていこうと思います。

EC-CUBE4 開発リファレンス

初回購入日時が注文日時より前か判断する方法

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

集計対象の期間を変更したい場合は、「o.order_date BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 23:59:59'」の日付を変更することで、集計対象の期間を変更することができます。

PostgreSQLでMySQLの\Gと同じことをしたい！

PHPをやっていたからか、今までMySQLを使うことが多かったのですが、ひょんなことからPostgreSQLを使うことになりました。

今のところ、簡単なSELECT文を流すくらいしかしていないので、そんなに困ったことはないのですが…

カラムの多いテーブルから1行抽出したときに見にくくて、MySQLの\Gを同じかなと思ってPostgreSQLでやったら怒られた。。

当たり前か。ということで調べた結果、「\x」を単体で打ってモードを切り替える必要があることがわかりました！

psql=> \x
拡張表示は on です。

psql=> select * from demo;
------+------
id | 1
name | demo

\xを打つと「拡張表示が on です。」という表示がされ、縦に展開されます。戻す時は、もう一度打ちます。

psql=> \x
拡張表示は off です。

psql=> select * from demo;
id | name
----+------
1 | demo

\xを打つたびに切り替わる！

クエリーによって変えたいから、MySQLの「\G」の方が楽な気がしてしまいました〜。

MySQLで抽出したデータをcsvに保存する方法

MySQLで抽出したデータをcsvに保存する方法を忘れないようにメモ！

SELECT * FROM table
INTO OUTFILE "/path/to/save/dir/output.csv"
FIELDS TERMINATED BY ',';


不意にcsvに出力する必要が出てくることもあるので、しっかり覚えておこうと思います。