Treasure Data CDPを活用したOneID（統合ID）構築5 nullの落とし穴

● はじめに

　本シリーズでは、Treasure Data CDPを用いて様々なデータソースに点在する企業保有の顧客データを統合し共通のIDで管理する「OneID（統合ID）構築」の取り組みについてご紹介させていただきます。

　第五回目となる本記事では、OneID構築をするうえで、データの中にnullが存在する場合の落とし穴、注意点についてご共有させていただきます。

　〇第一回目の記事はこちら
　〇第二回目の記事はこちら
　〇第三回目の記事はこちら
　〇第四回目の記事はこちら

● 目次

・OneID構築におけるnullの扱い
・nullの落とし穴1：突合キーにnullが含まれる場合
・nullの落とし穴2：時系列で比較する際の時間にnullがある場合
・まとめ

● OneID構築におけるnullの扱い

　「OneID構築」の取り組みでは、複数システムにまたがる顧客情報を統合するために様々な情報を用いて各顧客情報を突合し、共通IDを採番したものを日々更新していきます。

　しかし、こうした一連の処理を行う上で、データの中に存在する「null」の存在に注意しなければなりません。

　データ同士の突合や、処理したデータの更新時に適切にnullを扱わなければ、意図通りのデータ処理結果を得られない場合があります。

　今回は、こういったデータの中にnullが存在する場合の「落とし穴」について、二つご紹介させていただきます。

● nullの落とし穴1：突合キーにnullが含まれる場合

　「OneID構築」の対象データにnullが含まれる場合、まず気を付けなければならないのはデータ同士の突合です。共通IDであるOneIDを振るために顧客情報のいくつかを突合させ同一人物かどうかを判別しますが、この時突合させる情報の中にnullがあるとうまく突合できない場合があります。

　例として、システムAとシステムBの顧客情報を、「姓名、電話番号、メールアドレスすべてが一致した場合に同一人物としてみなす」という定義のもと、下図のようなユーザー同士を突合させようとする場合を見てみます。

　この時、二つのユーザーを突合させようと下記SQLを実行してみます。

　結果としては、二つの顧客は結びつかず、結果なし、となってしまいます。この結果は、null同士だと「＝」の関係が成立しないことに起因しています。

　このように、見た目の上では明らかにすべての情報が一致しているのにもかかわらず、nullがあることによって突合できない、というケースがあるのです。

　このケースの対策としては、一つの例として、下記SQLのようにCOALESCE関数などを使って、突合に使っている各情報のnullを別の値に置き換えるなどの方法があります。（下記例では空白に置き換えをしています。）

　このSQLであれば、下記のように意図通り突合結果が得られます

● nullの落とし穴2：時系列で比較する際の時間にnullがある場合

　もう一つ、nullがある場合に気を付けなければならないのは、日付情報です。

　OneIDは、顧客を一元管理する都合上、一度採番されたIDは変わらないことが求められます。一方で、当初別々のユーザーとして認識され、別のOneIDが振られていたが、情報収集により同一人物だと判明した場合、一つのOneIDに統合する必要が生じます。

　その際の処理方法として、顧客情報は最新のものに更新しつつ、OneIDは一番最初に採番された最古のものを保持し続ける、といった方法があります。

　しかし、その処理の際、「最古」を判別するための日付の値にnullがあると、この「最古の」IDを適切に判別できないケースが出てきます。

　例として、システムＡとシステムＢで、連絡先が一致していないためにそれぞれ別のOneIDが採番されている顧客において、システムAで更新があった結果二つの顧客情報が紐づいたパターンを見てみます。「登録日」は顧客情報が登録された日付、「更新日」は顧客情報が更新された日付が入り、初回登録時は「更新日」がnullとなっています。