ユーザーの年齢・性別と購買履歴を活用する「属性」×「行動」分析のSQL

ビジネスデータ解析のためのSQL入門 第5回

　本シリーズではビジネスデータ解析でデータベースから情報を取得して活用する必要がある方を対象に、ビジネスデータ解析でよく使われる実践的なSQL例など、分析向けの活用方法を解説します。前回はオープンソースBIツール「Re:dash」について解説しました。今回はSQLによるユーザー属性・行動分析に挑戦します。ユーザー情報（年齢・性別）と購買履歴データを活用し、SQL分析のポイントを確認していきます。

対象読者

• SQLの基本は理解しているがより実践的な分析に取り組みたい方

サンプルの動作確認環境

• PostgreSQL 9.6
• CentOS 6.7

データの準備

　本稿で用いるサンプルデータとして、ユーザー管理・購買履歴のテーブルを準備しました。サンプルデータ作成のためのSQLを、以下に示します。

リスト1　ユーザー管理テーブルの作成（create_table_customer_birth.sql）

create table customer_with_birthday(
user_id char(008), （顧客コード）
sex char(008), （性別）
birthday char(010) （生年月日）
);

ユーザー管理データの挿入（insert_sales_history.sql）

insert into customer_with_birthday(user_id,sex,birthday) values
('UID0001','M','1937-10-17'),
('UID0002','M','1982-03-27'),
('UID0003','M','1982-03-27'),
('UID0004','W','1994-06-07'),
('UID0005','W','1994-06-07'),
('UID0006','W','1937-10-17'),
('UID0007','M','1937-10-17'),
('UID0008','W','1994-06-07'),
('UID0009','M','1994-06-07'),
('UID0010','W','1982-03-27'),
('UID0011','W','1994-06-07'),
('UID0012','W','1982-03-27')
;

リスト3　購買履歴テーブルの作成（create_action_history.sql）

create table sales_history(
item_name char(12), （商品名）
item_category char(12 ), （商品カテゴリ）
action_category char(16 ), （行動カテゴリ）
date date, （購買日）
place char(8),  （購買地域・場所）
user_id char(8), （顧客コード）
price int （料金）
);

リスト4　購買履歴 データの挿入（insert_action_history.sql）

insert into action_history values
('コーヒー豆','beverage','purchase','2017/5/29','EC Store','UID0001','250'),
('紅茶','food','purchase','2017/5/29','EC Store','UID0001','250'),
('紅茶','food','favorite','2017/5/30','EC Store','UID0004','200'),
('コーヒー豆','beverage','review','2017/5/30','EC Store','UID0004','350'),
('ハチミツ','food','review','2017/5/30','EC Store','UID00011','200'),
('コーヒー豆','beverage','purchase','2017/5/30','EC Store','UID00011','350'),
('シナモン','food','purchase','2017/6/1','EC Store','UID00011','450'),
('シナモン','food','purchase','2017/6/2','EC Store','UID00011','450'),
('マグカップ','zakka','favorite','2017/6/3','EC Store','UID0002','980'),
('ハチミツ','food','purchase','2017/6/4','EC Store','UID0006','250'),
('紅茶','food','purchase','2017/6/4','EC Store','UID0003','250'),
('ハチミツ','food','review','2017/6/5','EC Store','UID0006','250'),
('ハチミツ','food','purchase','2017/6/6','EC Store','UID0006','250'),
('コーヒー豆','beverage','purchase','2017/6/7','EC Store','UID0006','250'),
('コーヒー豆','beverage','purchase','2017/6/8','EC Store','UID0007','250'),
('ハチミツ','food','purchase','2017/6/9','EC Store','UID0007','250'),
('紅茶','food','purchase','2017/6/10','EC Store','UID0007','250'),
('ハチミツ','food','favorite','2017/6/10','EC Store','UID0008','250'),
('コーヒー豆','beverage','purchase','2017/6/10','EC Store','UID0004','250'),
('ハチミツ','food','review','2017/6/12','EC Store','UID0008','250'),
('ハチミツ','food','purchase','2017/6/12','EC Store','UID0009','250'),
('コーヒー豆','beverage','purchase','2017/6/13','EC Store','UID0005','250'),
('紅茶','food','purchase','2017/6/13','EC Store','UID00012','250'),
('マグカップ','zakka','purchase','2017/6/14','EC Store','UID0003','980'),
('マグカップ','zakka','purchase','2017/6/14','EC Store','UID0003','780')
;

ユーザーの「年齢＋性別」属性と「購買」行動分析

　何らかのユーザー情報を保持するサービスでは、どのようなユーザーに利用されているのかを把握し、ユーザーの利用実態がサービス提供側の意図通りであるか確認することが必要です。ユーザーの属性を定義して集計することで、広告やサービス利用状況といった、まざまなレポートが作成可能になります。

　本稿では視聴率やアンケートの分析に用いられるM1層、F1層（以降、年齢性別区分）と呼ばれる属性を、性別と年齢から導き出して設定し、その人数を集計する方法を紹介します。年齢性別区分は、以下の表のようにそれぞれの性別および年齢と対応します。

年齢性別区分

区分	年齢＋性別
M1層	男性20～34歳
M2層	男性35～49歳
M3層	男性50歳以上
F1層	女性20～34歳
F2層	女性35～49歳
F3層	女性50歳以上
C層	4～12歳の男女
T層	13～19歳の男女

　年齢と性別はユーザー管理テーブルから取得します。性別に関してはテーブル上のデータを取得するだけですが、年齢の場合は少々やっかいです。

　DBのテーブルに年齢そのものを保存した場合、登録日から日数が経過するとデータベースに保存している年齢と実際の年齢が合致しなくなります。そのため、通常は誕生日のみ保存し、年齢は表示時や集計時に計算して出力します。SQLによる詳しい方法は後ほど紹介します。