データクレンジング作業の一つに名寄せというものがあります。ウェブやアプリを問わず、会員IDを導入しているサービスであればほぼ必須のプロセスになります。
名寄せとは？
一言で説明すると、データをユーザ単位にまとめ上げる作業のことです。例えば、Aさんが朝にスマートフォンのブラウザからアクセスし、昼にパソコンのブラウザからアクセスし、夜はスマートフォンのアプリからアクセスしたとします。これらはAさんのログとして一つにまとめられるはずですが、ログをデバイス単位（例：PC、SPウェブ、SPアプリ）で分けている場合は別々のユーザのログと見なされる可能性があります。unique_idはCookieやデバイス情報から作成されるID、member_idは会員IDを想定しています。
- pc
| unique_id | pagename | member_id | timestamp |
|---|---|---|---|
| 123 | top | 100001 | 2016-07-25 08:00:00 |
| 123 | list | 100001 | 2016-07-25 08:01:00 |
| 123 | item | 100001 | 2016-07-25 08:02:00 |
- spweb
| unique_id | pagename | member_id | timestamp |
|---|---|---|---|
| 456 | top | 100001 | 2016-07-25 12:00:00 |
| 456 | list | 100001 | 2016-07-25 12:01:00 |
| 456 | item | 100001 | 2016-07-25 12:02:00 |
- spapp
| unique_id | pagename | member_id | timestamp |
|---|---|---|---|
| 789 | top | 100001 | 2016-07-25 20:00:00 |
| 789 | list | 100001 | 2016-07-25 20:01:00 |
| 789 | item | 100001 | 2016-07-25 20:02:00 |
また、ブラウザやデバイス間のデータの名寄せだけでなく、同じブラウザやデバイス内のデータの名寄せもあります。ログイン状態と非ログイン状態のデータをまとめる場合がそれに当たります。
- table1
| unique_id | pagename | member_id | timestamp |
|---|---|---|---|
| 321 | top | 2016-07-25 15:00:00 | |
| 321 | login | 2016-07-25 15:01:00 | |
| 321 | top | 100001 | 2016-07-25 15:02:00 |
名寄せをすると何が嬉しいのか？
例えば、記事の閲覧時間が○○秒以上になったユーザにレコメンドメールを送るなどの、ユーザのログに基づいた施策を実施している場合は精度が上がることが考えられます。また、ユーザのログ分析もより精緻な情報を得ることが可能になります。
名寄せの手順
上記のtable1の場合、まずはunique_idとmember_idの組み合わせ表を作成しておきます。そして、その組み合わせ表に基いてmember_idを補完するという手順になります。SQLで書くと下記のようなSQLになります。
SELECT
a.unique_id
, a.pagename
, b.member_id
, a.timestamp
FROM table1 a
JOIN
(
SELECT DISTINCT
unique_id
, member_id
FROM
table1
) b
ON a.unique_id = b.unique_id
上記のSQLを実行すると、見事member_idが補完されたテーブルが出来上がります。
- table2
| unique_id | pagename | member_id | timestamp |
|---|---|---|---|
| 321 | top | 100001 | 2016-07-25 15:00:00 |
| 321 | login | 100001 | 2016-07-25 15:01:00 |
| 321 | top | 100001 | 2016-07-25 15:02:00 |
名寄せで注意する事
一点だけ名寄せで注意しなければならない事があります。unique_idとmember_idは常に1対1になるとは限らないという事です。N対1の場合もあれば１対N、さらにはN対Nの場合もあります。N対1の場合はあまり問題にならないのですが、1対NやN対Nの場合は問題になる可能性が高いです。家族でPCを共有している場合などに起こり、そのまま名寄せすると違う人のログが混じってしまいます。そのような場合、対象のunique_idは名寄せ対象から外すなどの措置が必要です。