SQLサンプルと解説
ここからは、ユーザー属性・行動分析のSQLの例を示しつつ、解説を進めます。
まず、ユーザー属性として年齢性別区分、ユーザー行動として購買した商品カテゴリを集計し、分析する方法を説明します。欲しい結果は以下の通りです。
product_category | user_category | purchase_count ------------------+---------------+---------------- beverage | M3 | 2 beverage | W1 | 2 beverage | W3 | 1 food | M1 | 2 food | M2 | 2 food | M3 | 8 food | W1 | 2 food | W3 | 5 zakka | M2 | 4
上図はSQLクエリ結果から作成したグラフです。このグラフから以下の分析ができます。
- 「食品（food）」はM3層およびW1・W2層が多く購入している。
- 「雑貨（zakka）」を購入しているのはM2層のみ。
- W2層は購入がない（少ない）。
こうした分析結果をもとに、広告の手法やサービスの改善を施策として実施していくことになります。
なお、グラフは可視化ツールであるRe:dashで作成しています。Re:dashについては連載の前回の記事で解説しています。
上の結果を得るためのSQLは以下の通りです。
with/* （1） */
sq_users_int_birthday as (/* （2）-（a） */
select
*,
20171001 as benchmark_date/* （2）-（b） */
,cast(replace(birthday,'-','' ) as integer) as int_birth_date
from customer_with_birthday
),
sq_users_age as (/* （3） */
select
*,
floor((benchmark_date - int_birth_date )/ 10000 ) as age/* （3）-（a） */
from sq_users_int_birthday
),
sq_users_category as (/* （4） */
select
user_id
,sex
,age
,concat(/* （4）-（a） */
case
when 20 <= age then sex else ''/* （4）-（b） */
end
,case
when age >= 4 and age <= 12 then 'C'/* （4）-（c） */
when age >= 13 and age <= 19 then 'T'
when age >= 20 and age <= 34 then '1'
when age >= 35 and age <= 49 then '2'
when age >= 50 then '3'
end
)as age_sex_category/* （4）-（d） */
from
sq_users_age
)
select
a.item_category
,u.age_sex_category
,count(*) as purchase_count/* （5） */
from action_history as a/* （6） */
join sq_users_category as u
ON a.user_id = u.user_id
where a.action_category = 'purchase'
group by a.item_category, u.age_sex_category/* （7） */
order by a.item_category, u.age_sex_category
;
内側のサブクエリの内容から順に確認していきます。
（1）With句
With句はサブクエリに名前をつけ、SQLの中ではなく前で記述します。サブクエリを複数利用するケースでは、SQLの中で記述する場合にくらべ視認性を高めることができます。
以下の通り記述します。
WITH <クエリ名> AS ( select <列> ～ )
（2）ユーザーの誕生日を10桁の整数型に加工する
年齢を計算するための前準備として、日付型の誕生日を整数型に加工します。
また、年齢算出したい日付――基準日（benchmark_date）として、ここでは2017年10月1日と設定しています。
このサブクエリによって得られる途中結果は以下の通りです。
user_id | sex | birthday | benchmark_date | int_birth_date ----------+-----+------------+----------------+---------------- UID0001 | M | 1937-10-17 | 20171001 | 19371017 UID0002 | M | 1982-03-27 | 20171001 | 19820327 UID0003 | M | 1982-03-27 | 20171001 | 19820327 UID0004 | W | 1994-06-07 | 20171001 | 19940607 UID0005 | W | 1994-06-07 | 20171001 | 19940607 UID0006 | W | 1937-10-17 | 20171001 | 19371017 UID0007 | M | 1937-10-17 | 20171001 | 19371017 UID0008 | W | 1994-06-07 | 20171001 | 19940607 UID0009 | M | 1994-06-07 | 20171001 | 19940607 UID0010 | W | 1982-03-27 | 20171001 | 19820327 UID0011 | W | 1994-06-07 | 20171001 | 19940607 UID0012 | W | 1982-03-27 | 20171001 | 19820327
（3）年齢を計算する
（a）整数型に加工した誕生日から年齢を算出します。年齢をSQLで算出する方法はいくつかありますが、誕生日と特定の日付を整数型で表現し、その差の値を10000で割る方法が最もスマートです。（2）で準備した10桁整数型の日付の差を算出し、10000で割ると年齢が算出されます。
例えば1990年1月1日生まれの場合、2017年10月1日時点での年齢は以下のように「27歳」と算出されます。
20171001-19900101=270900 270900／10000＝27 （小数点以下切り捨て）
このサブクエリによって得られる途中結果は以下の通りです。
user_id | sex | birthday | age ----------+-----+------------+----- UID0001 | M | 1937-10-17 | 79 UID0002 | M | 1982-03-27 | 35 UID0003 | M | 1982-03-27 | 35 UID0004 | W | 1994-06-07 | 23 UID0005 | W | 1994-06-07 | 23 UID0006 | W | 1937-10-17 | 79 UID0007 | M | 1937-10-17 | 79 UID0008 | W | 1994-06-07 | 23 UID0009 | M | 1994-06-07 | 23 UID0010 | W | 1982-03-27 | 35 UID0011 | W | 1994-06-07 | 23 UID0012 | W | 1982-03-27 | 35
（4）性別と年齢からカテゴリを作成
（a）concat関数で文字列を連結します。concat関数はすべての引数を結合します（ただし、NULLは無視されます）。
concat('abc', 2, NULL, 22) → abc222
（b）CASE式を使って20歳以上の場合は性別の文字を使用し、9歳以下は使用しないためNULLにします。
CASE式の構文は以下の通りです。
CASE WHEN <項目> = <値1> THEN <値4>
WHEN <項目> = <値2> THEN <値5>
ELSE <値3> END
（c）「age >= 4 and age <= 12」で年齢ageが4から12までの指定になります。
（d）concat関数によって連結されたテキストにage_sex_categoryという別名をつけます。
このサブクエリによって得られる途中結果は以下の通りです。
user_id | sex | age | age_sex_category ----------+-----+-----+------------------ UID0001 | M | 79 | M3 UID0002 | M | 35 | M2 UID0003 | M | 35 | M2 UID0004 | W | 23 | W1 UID0005 | W | 23 | W1 UID0006 | W | 79 | W3 UID0007 | M | 79 | M3 UID0008 | W | 23 | W1 UID0009 | M | 23 | M1 UID0010 | W | 35 | W2 UID0011 | W | 23 | W1 UID0012 | W | 35 | W2
（5）カテゴリごとのユーザー数集計
group by句でcategoryを指定し、count(*)で行数をカウントすることで、categoryごとのUserID数が集計されます。
（6）購買履歴テーブルと年齢性別区分テーブルを結合
join句で購買履歴テーブルである「action_history」と年齢性別区分テーブルの「mst_users_with_category」の内部結合を行います。ONで示すように両テーブルのuser_idをもとに結合します。テーブルにはそれぞれ簡略化のため「a」「u」の別名をつけます。
（7）集計のためのグループ指定
購買履歴テーブルの商品カテゴリとユーザー管理テーブルの年齢性別区分ごとに、集計を行うよう指定します。
以上で、年齢性別区分別・商品カテゴリ別の分析のための集計ができました。
まとめ
今回はユーザー分析における属性と行動のデータを活用した分析を取り上げました。
サンプルとしてECサイトのデータを例としましたが、ユーザー管理データと購買履歴さえあればSQLで分析を実施できることがおわかりいただけたと思います。これらのデータはほとんどの場合データベースにあるので、ぜひSQLを使って分析をしてみてください。
