春やな~
どうも、hosigakiです!春が近づいてきましたね!
今日は、SQLでよくあるデータ分析の落とし穴について説明します!
こんなクエリは落とし穴にハマってる!?
今が2022年5月だとします。
例えば、以下二つのデータを算出して、前年と今年との差を分析するとします。
- 2021年3月の登録日別の①登録者数、②購入者数
- 2022年3月の登録日別の①登録者数、②購入者数
使えるテーブルは、以下二つです。
id | name | user_created_date |
---|---|---|
1 | 太郎 | 2019-04-06 |
2 | よしお | 2019-04-06 |
3 | SAKI | 2019-04-06 |
... | ... | ... |
22300 | ジム | 2022-05-26 |
id | item | user_id | purchase_created_date |
---|---|---|---|
1 | 干し柿 | 3 | 2019-04-06 |
2 | 干し柿 | 14 | 2019-04-06 |
3 | 干し柿まんじゅう | 5 | 2019-04-07 |
... | ... | ... | ... |
50638 | 幻の干し柿 | 19780 | 2022-5-26 |
なんや、単にユーザー登録日でグルーピングして、登録者数と購入数をカウントすればええだけやろ?こんなの朝飯前や!
なんて感じで、以下のようなクエリを書いた方は要注意!
既にその方はデータ分析の落とし穴にはまっています...!
さて一体、どんな落とし穴だと思いますか?
SELECT
u.user_created_date,
COUNT(DISTINCT u.id) AS user_count, /*その日の登録したユーザー数を算出しています。*/
COUNT(p.id) AS purchase_count /*その日に登録したユーザーの、登録日以降の購入数を算出しています*/
FROM
users AS u
LEFT OUTER JOIN purchases AS p
ON u.id = p.user_id
WHERE
u.user_created_date BETWEEN "2021-3-1" AND "2021-3-31" /*ユーザーの登録日を、2021年3月に絞っています。*/
GROUP BY
user_created_date /*登録日ごとにレコードをグループ分けしています*/
ORDER BY
user_created_date ASC
/*上記クエリを、WHERE句の日時のみ変えて、2021年3月と2022年3月の二つのデータを別々に算出します*/
直近のユーザーほど不利になる
さて、先ほどのクエリによって、以下二つのデータを出しました。
- 2021年3月の登録日別の①登録者数、②購入者数
- 2022年3月の登録日別の①登録者数、②購入者数
実はこの場合、2022年3月のデータにて、②購入者数 が低く出やすくなるんです。
なぜなら、
2022年のユーザーの方が、2021年のユーザーに比べて、購入できる期間が短いから
という理由だからです。
2021年3月の登録ユーザーは、現時点(2022年5月)までで登録から1年以上経過しています。それに対して、2022年3月の登録ユーザーは、まだ登録から2, 3ヶ月しか経過していません。
普通に考えると、前者のユーザーの方が、長い時間を過ごしているため、現時点まででたくさんの商品を購入している可能性が高いですよね。
条件を揃えて、落とし穴を回避しよう!
先ほどのような落とし穴にハマらないための秘訣。
それは、次に尽きます!
分析対象の条件を揃える
今回で言えば、例えば、ユーザーの登録日から何日目までの購入を分析対象にするか、を決めておくことで、先ほどの落とし穴は回避できます!
以下はそれを踏まえたクエリの一例です。
SELECT
u.user_created_date,
COUNT(DISTINCT u.id) AS user_count, /*その日の登録したユーザー数を算出しています。*/
COUNT(p.id) AS purchase_count /*その日に登録したユーザーの、登録日以降の購入数を算出しています*/
FROM
users AS u
LEFT OUTER JOIN purchases AS p
ON u.id = p.user_id
WHERE
u.user_created_date BETWEEN "2021-3-1" AND "2021-3-31" /*ユーザーの登録日を、2021年3月に絞っています。*/
AND
DATE_DIFF(p.purchase_created_date, u.user_created_date, MONTH) <= 1 /*ユーザーの登録から1ヶ月以内の購入レコードのみに絞り込んでいます。*/
GROUP BY
user_created_date /*登録日ごとにレコードをグループ分けしています*/
ORDER BY
user_created_date ASC
先ほどのクエリとは異なり、WHERE句にもう一つ、以下の条件を追加しました。
DATE_DIFF(purchase_created_date, user_created_date, MONTH) <= 1
これによって、2021年3月のデータも、2022年3月のデータも、どちらも登録してから1ヶ月以内の購入者数をカウントしているため、直近のデータが不利な状態を回避できました!
SQLでデータ分析する際は、このように直近のデータほど不利に出やすいことがあるため、条件を揃えて分析することを心がけましょう!
ここまで読んでいただき、ありがとうございました!
また次の記事でお会いしましょう(^^)/