Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

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

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

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2018/01/18 14:00

 本シリーズではビジネスデータ解析でデータベースから情報を取得して活用する必要がある方を対象に、ビジネスデータ解析でよく使われる実践的な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による詳しい方法は後ほど紹介します。


  • LINEで送る
  • このエントリーをはてなブックマークに追加

著者プロフィール

  • WINGSプロジェクト 西 潤史郎(ニシ ジュンシロウ)

    <WINGSプロジェクトについて> 有限会社 WINGSプロジェクトが運営する、テクニカル執筆コミュニティ(代表 山田祥寛)。主にWeb開発分野の書籍/記事執筆、翻訳、講演等を幅広く手がける。2017年5月時点での登録メンバは52名で、現在も執筆メンバを募集中。興味のある方は、どしどし応募頂きたい...

  • 山田 祥寛(ヤマダ ヨシヒロ)

    静岡県榛原町生まれ。一橋大学経済学部卒業後、NECにてシステム企画業務に携わるが、2003年4月に念願かなってフリーライターに転身。Microsoft MVP for ASP/ASP.NET。執筆コミュニティ「WINGSプロジェクト」代表。 主な著書に「入門シリーズ(サーバサイドAjax/XMLD...

バックナンバー

連載:ビジネスデータ解析のためのSQL入門
All contents copyright © 2005-2018 Shoeisha Co., Ltd. All rights reserved. ver.1.5