MySQLで初回購入後の顧客データ - 情弱エンジニアのなかのblog

SQLで初回購入の年月と初回流入の媒体を指定して顧客を分析してその顧客の月毎かつ媒体ごとの売上とユニークな顧客数を出すという処理を制作したので手順を記載いたします。今回は初回購入月と初回流入媒体を指定することを前提とします。

まずは以下のようにデータがあるものとします。

customersテーブル
     顧客名  
+---+-------+
|id |name   |
+---+-------+
|1  |yamada |
|2  |tanaka |
|3  |satou  |
|4  |katou  |
|5  |ito    |
+---+-------+


customer_historiesテーブル
      顧客id         注文日          購入回数         注文id      媒体id
+---+------------+----------------+------------------+---------+---------+
|id |customer_id |date_of_purchase|number_of_purchase|order_id |media_id |
+---+------------+----------------+------------------+---------+---------+
|1  | 5          |2016-12-01      | 1                |1        |1        |
|2  | 1          |2017-01-01      | 1                |2        |1        |
|3  | 2          |2017-01-01      | 1                |3        |1        |
|4  | 3          |2017-01-01      | 1                |4        |1        |
|5  | 4          |2017-01-01      | 1                |5        |2        |
|6  | 5          |2017-02-01      | 2                |6        |1        |
|7  | 1          |2017-02-01      | 2                |7        |2        |
|8  | 2          |2017-02-01      | 2                |8        |2        |
|9  | 4          |2017-02-01      | 2                |9        |2        |
|10 | 1          |2017-03-01      | 3                |10       |3        |
|11 | 1          |2017-03-01      | 4                |11       |         |
|12 | 3          |2017-03-01      | 2                |12       |3        |
|13 | 3          |2017-03-01      | 3                |13       |         |
|14 | 4          |2017-03-01      | 3                |14       |2        |
|15 | 3          |2017-03-01      | 4                |15       |3        |
+---+------------+----------------+------------------+---------+---------+


ordersテーブル
      顧客id         注文日          金額
+---+------------+--------------+--------------+
|id |customer_id |order_date    |  amount      |
+---+------------+--------------+--------------+
|1  | 5          |2016-12-01    | 100          |
|2  | 1          |2017-01-01    | 100          |
|3  | 2          |2017-01-01    | 100          |
|4  | 3          |2017-01-01    | 100          |
|5  | 4          |2017-01-01    | 100          |
|6  | 5          |2017-02-01    | 100          |
|7  | 1          |2017-02-01    | 100          |
|8  | 2          |2017-02-01    | 100          |
|9  | 4          |2017-02-01    | 100          |
|10 | 1          |2017-03-01    | 100          |
|11 | 1          |2017-03-01    | 100          |
|12 | 3          |2017-03-01    | 100          |
|13 | 3          |2017-03-01    | 100          |
|14 | 4          |2017-03-01    | 100          |
|15 | 3          |2017-03-01    | 100          |
+---+------------+--------------+--------------+

mediaテーブル

+---+-------+
|id |name   |
+---+-------+
|1  |aaa    |
|2  |bbb    |
|3  |ccc    |
+---+-------+

ではSQLの制作に入っていきますいくつかに分割して説明いたします。

1.サブクエリ

まずは初回購入の年月と初回流入の媒体を指定して顧客を絞り込むためのサブクエリを作ります

FROM
    orders
INNER JOIN
    (
    SELECT
        customer_histories.customer_id
    FROM
        customer_histories
    INNER JOIN
        media
    ON
        customer_histories.media_id = media.id
    WHERE
       --2017年の1月に初回購入した顧客で絞ります
       customer_histories.date like '2017-01-%'
       AND
       --初回流入媒体がaaaの顧客に絞ります
       media.name = 'aaa'
       AND
       --初回購入に絞るためにnumber_of_purchase = 1にします
       customer_histories.number_of_purchase = 1
    ) AS ch
ON
orders.customer_id = ch.customer_id

2.表示条件

続いては絞り込んだ顧客の月毎かつ直接流入媒体ごとに出力する条件を作ります

LEFT JOIN
     --媒体なしの場合も含めるので外部結合にいたします。
     media
ON
     orders.media_id = media.id
GROUP BY
     --年月ごと
     DATE_FORMAT(orders.date ,'%Y%m'),
     --媒体ごと
     media.id

3.表示項目

最後に出力する項目（カラム）を指定します。

SELECT
    DATE_FORMAT(orders.date,"%Y-%m") AS '購入年月',
    media2.id AS '媒体id',
    media2.name AS '媒体名',
    --ユニークな顧客の人数を出すためにdistinctを使います。
    FORMAT(count(distinct orders.customer_id),0) AS '顧客数',
    FORMAT(sum(orders.amount),0) AS '総売上'

こちらすべてつなげると以下のようになります。

SELECT
    DATE_FORMAT(orders.date,"%Y-%m") AS '購入年月',
    media2.id AS '媒体id',
    media2.name AS '媒体名',
    FORMAT(count(distinct orders.customer_id),0) AS '顧客数',
    FORMAT(sum(orders.amount),0) AS '総売上'
FROM
    orders
INNER JOIN
    (
    SELECT
        customer_histories.customer_id
    FROM
        customer_histories
    LEFT JOIN
        media
    ON
        customer_histories.media_id = media.id
    WHERE
       customer_histories.date like '2017-01-%'
       AND
       media.name = 'aaa'
       AND
       customer_histories.number_of_purchase = 1
    ) AS ch
ON
orders.customer_id = ch.customer_id
LEFT JOIN
     media
ON
     orders.media_id = media.id
GROUP BY
     DATE_FORMAT(orders.date ,'%Y%m'),
     media.id

結果の方は以下になります。

購入年月 媒体id    媒体名   顧客数   総売上
2017-1      1       aaa     3       300
2017-2      2       bbb     2       200
2017-3                      2       200
2017-3      3       ccc     2       300

以上となります。