0 はじめに
本稿では、ビッグデータ技術を活用したECサイトにおけるユーザー行動分析と可視化に関する卒業設計プロジェクトを紹介します。このプロジェクトでは、淘宝（タオバオ）のユーザーデータを分析し、購買行動のパターンや傾向を明らかにします。
- データセット概要
本プロジェクトで使用するデータセットは淘宝のユーザー行動データであり、期間は2017年11月25日から2017年12月3日までです。総レコード数は100,150,807件、データサイズは3.5GB、5つのフィールドで構成されています。
- データ処理
2.1 データ取り込み
データをHiveにロードし、Hiveを通じてデータ処理を行います。
-- テーブル作成
drop table if exists customer_actions;
create table customer_actions (
`user_id` string comment 'ユーザーID',
`product_id` string comment '商品ID',
`category_id` string comment '商品カテゴリID',
`action_type` string comment 'アクションタイプ（pv, buy, cart, favのいずれか）',
`timestamp` int comment 'アクションタイムスタンプ',
`action_time` string comment 'アクション日時')
row format delimited
fields terminated by ','
lines terminated by '\n';
-- データロード
LOAD DATA LOCAL INPATH '/home/getway/CustomerBehavior.csv'
OVERWRITE INTO TABLE customer_actions ;
2.2 データクリーニング
データクリーニングでは、重複値の削除、タイムスタンプのフォーマット変換、異常値の除去を行います。
-- 重複データの削除
insert overwrite table customer_actions
select user_id, product_id, category_id, action_type, timestamp, action_time
from customer_actions
group by user_id, product_id, category_id, action_type, timestamp, action_time;
-- タイムスタンプのフォーマット変換
insert overwrite table customer_actions
select user_id, product_id, category_id, action_type, timestamp, from_unixtime(timestamp, 'yyyy-MM-dd HH:mm:ss')
from customer_actions;
-- 日時の異常値チェック
select date(action_time) as day from customer_actions group by date(action_time) order by day;
-- 異常日時データの削除
insert overwrite table customer_actions
select user_id, product_id, category_id, action_type, timestamp, action_time
from customer_actions
where cast(action_time as date) between '2017-11-25' and '2017-12-03';
-- アクションタイプの異常値チェック
select action_type from customer_actions group by action_type;
- データ分析と可視化
3.1 トラフィックと購買状況の分析
-- 総PV（ページビュー）と総UV（ユニークユーザー）
select sum(case when action_type = 'pv' then 1 else 0 end) as total_pv,
count(distinct user_id) as total_uv
from customer_actions;
-- 日別PVとUV
select cast(action_time as date) as day,
sum(case when action_type = 'pv' then 1 else 0 end) as daily_pv,
count(distinct user_id) as daily_uv
from customer_actions
group by cast(action_time as date)
order by day;
-- ユーザー別アクション集計
create table user_action_stats as
select user_id,
sum(case when action_type = 'pv' then 1 else 0 end) as page_views, -- ページビュー数
sum(case when action_type = 'fav' then 1 else 0 end) as favorites, -- お気に入り数
sum(case when action_type = 'cart' then 1 else 0 end) as carts, -- カート数
sum(case when action_type = 'buy' then 1 else 0 end) as purchases -- 購入数
from customer_actions
group by user_id;
-- 再購入率：2回以上購入したユーザーの購入ユーザーに占める割合
select sum(case when purchases > 1 then 1 else 0 end) / sum(case when purchases > 0 then 1 else 0 end)
from user_action_stats;
- 考察：2017年11月25日から2017年12月3日までの期間において、総PV数は89,660,671、総UV数は987,991でした。日別PVのトレンドから、12月に入った後に明らかな増加が見られ、これは「双12」というECイベントによる集客効果が考えられます。また、2017年12月2日と3日は週末であり、平日に比べてユーザーのアクティビティが高いことも影響している可能性があります。全体の再購入率は66.01%であり、ユーザーのロイヤルティが比較的高いことが示唆されます。
3.2 ユーザー行動のコンバージョン率
-- PV/(カート+お気に入り)/購入 の各段階におけるコンバージョン率
select a.page_views,
a.favorites,
a.carts,
a.favorites + a.carts as `favorites+carts`,
a.purchases,
round((a.favorites + a.carts) / a.page_views, 4) as pv_to_fav_cart,
round(a.purchases / (a.favorites + a.carts), 4) as fav_cart_to_purchase,
round(a.purchases / a.page_views, 4) as pv_to_purchase
from(
select sum(page_views) as page_views, -- ページビュー数
sum(favorites) as favorites, -- お気に入り数
sum(carts) as carts, -- カート数
sum(purchases) as purchases -- 購入数
from user_action_stats
) as a;
- 考察：2017年11月25日から2017年12月3日までの期間において、ページビュー数は89,660,671、お気に入り数は2,888,258、カート数は5,530,446、購入数は2,015,807でした。全体のコンバージョン率は2.25%と、これは比較的低い値です。カート数から判断すると、一部のユーザーはECイベントを待ってから購入を計画している可能性があります。したがって、一般的にECイベント前の期間では通常時よりもコンバージョン率が低くなると合理的に推測できます。
3.3 ユーザー行動パターン
-- 一日のアクティブ時間帯分布
select hour(action_time) as hour_of_day,
sum(case when action_type = 'pv' then 1 else 0 end) as page_views, -- ページビュー数
sum(case when action_type = 'fav' then 1 else 0 end) as favorites, -- お気に入り数
sum(case when action_type = 'cart' then 1 else 0 end) as carts, -- カート数
sum(case when action_type = 'buy' then 1 else 0 end) as purchases -- 購入数
from customer_actions
group by hour(action_time)
order by hour_of_day;
-- 週間ユーザー活性分布
select pmod(datediff(action_time, '1920-01-01') - 3, 7) as day_of_week,
sum(case when action_type = 'pv' then 1 else 0 end) as page_views, -- ページビュー数
sum(case when action_type = 'fav' then 1 else 0 end) as favorites, -- お気に入り数
sum(case when action_type = 'cart' then 1 else 0 end) as carts, -- カート数
sum(case when action_type = 'buy' then 1 else 0 end) as purchases -- 購入数
from customer_actions
where date(action_time) between '2017-11-27' and '2017-12-03'
group by pmod(datediff(action_time, '1920-01-01') - 3, 7)
order by day_of_week;
- 考察：夜の21時から22時がユーザー最もアクティブな時間帯であり、一方で午前4時が最も非アクティブな時間帯です。週間では、平日の活性度はほぼ一定ですが、週末になると活性度が明らかに向上します。
3.4 RFMモデルに基づく高価値ユーザーの特定
RFMモデルは顧客価値と顧客収益性を評価するための重要なツールであり、3つの要素から最良の分析指標が構成されます：
- R-Recency（最終購入日）
- F-Frequency（購買頻度）
- M-Monetary（購買金額）
-- R-Recency（最終購入日）, R値が高いほどユーザーはアクティブ
select user_id,
datediff('2017-12-04', max(action_time)) as recency,
dense_rank() over(order by datediff('2017-12-04', max(action_time))) as r_rank
from customer_actions
where action_type = 'buy'
group by user_id
limit 10;
-- F-Frequency（購買頻度）, F値が高いほどユーザーはロイヤル
select user_id,
count(1) as frequency,
dense_rank() over(order by count(1) desc) as f_rank
from customer_actions
where action_type = 'buy'
group by user_id
limit 10;
-- M-Monetary（購買金額）, データセットに金額情報がないため分析不可
購入行動のあるユーザーをランキングに基づき5つのグループに分類します：
- 上位1/5のユーザーに5点
- 1/5から2/5のユーザーに4点
- 2/5から3/5のユーザーに3点
- 3/5から4/5のユーザーに2点
- 4/5から下位のユーザーに1点
このルールに基づき、ユーザーの時間間隔ランキングと購買頻度ランキングにそれぞれスコア付けし、最終的に2つのスコアを合算してユーザーの総合スコアとします。
with rfm_base as(
select user_id,
datediff('2017-12-04', max(action_time)) as recency,
dense_rank() over(order by datediff('2017-12-04', max(action_time))) as r_rank,
count(1) as frequency,
dense_rank() over(order by count(1) desc) as f_rank
from customer_actions
where action_type = 'buy'
group by user_id)
select user_id, recency, r_rank, r_score, frequency, f_rank, f_score, r_score + f_score as total_score
from(
select *,
case ntile(5) over(order by r_rank) when 1 then 5
when 2 then 4
when 3 then 3
when 4 then 2
when 5 then 1
end as r_score,
case ntile(5) over(order by f_rank) when 1 then 5
when 2 then 4
when 3 then 3
when 4 then 2
when 5 then 1
end as f_score
from rfm_base
) as scored_users
order by total_score desc
limit 20;
- 考察：ユーザーの価値スコアに基づき、パーソナライズされたマーケティング推奨を行うことができます。
3.5 商品次元の分析
-- 最も売れた商品
select product_id ,
sum(case when action_type = 'pv' then 1 else 0 end) as page_views, -- ページビュー数
sum(case when action_type = 'fav' then 1 else 0 end) as favorites, -- お気に入り数
sum(case when action_type = 'cart' then 1 else 0 end) as carts, -- カート数
sum(case when action_type = 'buy' then 1 else 0 end) as purchases -- 購入数
from customer_actions
group by product_id
order by purchases desc
limit 10;
-- 最も売れた商品カテゴリ
select category_id ,
sum(case when action_type = 'pv' then 1 else 0 end) as page_views, -- ページビュー数
sum(case when action_type = 'fav' then 1 else 0 end) as favorites, -- お気に入り数
sum(case when action_type = 'cart' then 1 else 0 end) as carts, -- カート数
sum(case when action_type = 'buy' then 1 else 0 end) as purchases -- 購入数
from customer_actions
group by category_id
order by purchases desc
limit 10;
- 考察：商品マスターテーブルがないため、分析価値は限定的です。もし商品マスターテーブルがあれば、業種や製品別のコンバージョン率分析、競合分析などをさらに展開できます。