【ITニュース解説】Data Cleaning and Analysis with SQL

ある交通予約プラットフォーム「SafariConnect」では、ケニア全土で数千件もの旅行予約を扱っていたが、その生データは非常に整理されていない状態であった。例えば、日付は「2024-01-08」「17/01/2024」「01-18-2024」のように複数の形式で記録されており、電話番号には「+2547…」「07…」「0745-…」といった異なるプレフィックスが含まれていた。乗客の名前や都市名も大文字と小文字が混在しており、座席クラスも「Economy」「eco」「BUSINESS CLASS」のように表記揺れが激しかった。支払い方法や評価にも不統一なデータや無効な値が見られた。このような「汚い」データでは、正確な分析やビジネス上の意思決定を行うことが困難だったため、データを整理し、適切なデータベースを設計した上で、SQLを使って具体的なビジネス上の疑問に答える必要があったのだ。

この問題に対処するため、まず「ステージングテーブル」と呼ばれる一時的なデータベーステーブルを作成した。この「bookings_staging」テーブルでは、すべての列のデータ型を「TEXT」（文字列型）に設定した。これは、形式がバラバラな「汚い」データを、一時的にすべて受け入れることができるようにするためである。生のデータはまずここに読み込まれる。

次に、SQLを使ってデータの「クリーンアップ」と「変換」という作業を行った。具体的には、乗客の名前は先頭だけ大文字にし、前後の不要なスペースを取り除くことで標準化された。電話番号は特定の地域の形式（例: 「07…」）に統一され、性別の表記は「Male」と「Female」に限定された。座席クラスも「Economy」と「Business」の二つに集約され、支払い方法も「M-Pesa」「Cash」「Card」のように統一された。日付データは、形式が不揃いだったものを「YYYY-MM-DD」というISO標準の形式に変換された。また、無効な範囲外の評価値（例: 0や6）は「NULL」（値がないことを示す特殊な値）に設定された。さらに、重複する予約データは、データベース内部の識別子を使って特定し、削除された。これらのクリーンアップ処理が完了した後、データは「プロダクションテーブル」と呼ばれる、最終的な目的のデータベーステーブル「bookings」に格納された。この「bookings」テーブルでは、各列に適切なデータ型（例: 文字列の「VARCHAR」、数値の「NUMERIC」、日付の「DATE」、整数の「INTEGER」など）が設定されており、さらに「PRIMARY KEY」（各行を一意に識別するための鍵で、重複が許されない）などの制約が設けられている。これにより、データの整合性が保たれ、より効率的で信頼性の高いデータ操作が可能となる。

クリーンアップされたデータを使って分析を行うために、「分析用ビュー」が作成された。「v_clean_trips」と名付けられたこのビューは、実際のテーブルから特定の条件（この場合は予約ステータスが「Completed」、つまり完了した旅行のみ）でデータを抽出し、さらに分析に役立つ新しい情報を計算して追加する「仮想のテーブル」のようなものだ。例えば、出発日から「旅行月」「月のラベル」「曜日」「月の数字」「曜日番号」といった情報を抽出し、算出している。また、乗客の評価に基づいて「満足度」（Satisfied, Neutral, Unsatisfied, No Rating）という新しいカテゴリを設けることで、より深い分析ができるようにデータが整形されている。このビューを利用することで、複雑なクエリを何度も書く手間が省け、誰でも簡単にクリーンで分析しやすいデータにアクセスできるようになった。

このクリーンで整理されたデータと分析用ビューを用いて、SafariConnectのビジネスに関する重要な分析が行われた。

まず、「ルートごとのパフォーマンス」を分析した。これにより、どのルートが最も多くの予約を受け、多くの収益を上げているのかが明らかになった。例えば、「ナイロビ → モンバサ」ルートが、総予約数、総座席数、総収益で他のルートを圧倒していることが判明した。これは、特定のルートがビジネスの「屋台骨」となっていることを示している。

次に、「ドライバーごとのパフォーマンス」を評価した。各ドライバーの運転したトリップ数、売上、そして乗客からの平均評価を算出し、売上に基づいてドライバーの順位付けを行った。これは、特定のドライバーが他のドライバーよりも高い評価を得ており、それが乗客の満足度向上に貢献している可能性を示唆している。

さらに、「月ごとの収益トレンド」を追跡した。これにより、月ごとの売上の推移と累積売上が可視化された。データからは、収益が着実に成長しており、特に4月から8月が収益のピーク月であることが明らかになった。これは、ビジネスの季節性を理解し、将来の計画を立てる上で非常に重要な情報となる。

「乗客のインサイト」では、どの都市の乗客が最も多いか、男女比、そしてどの座席クラス（エコノミーかビジネスか）が好まれているかといった傾向が分析された。ナイロビの乗客が最も多く、女性乗客が男性をわずかに上回り、予約の約75%がエコノミークラスであることが分かった。

最後に、「キャンセルと逸失収益」を分析した。完了した予約数、キャンセルされた予約数、そして乗客が現れなかった「ノーショー」の数を比較し、各ルートでのキャンセル率を算出した。これにより、全体で約10〜12%の予約がキャンセルやノーショーによって失われており、特定のルートではさらに高いキャンセル率が見られることが明らかになった。これは、ビジネスにとって無視できない損失であり、改善策を検討する必要があることを示している。

これらの分析から、いくつかの重要な発見があった。最も収益を上げているのは「ナイロビ → モンバサ」ルートであり、高い評価を持つドライバーは乗客の満足度が高い傾向にある。収益は着実に伸びており、4月から8月が特に好調な時期である。乗客はナイロビからの利用が多く、主にエコノミークラスを好む傾向がある。そして、キャンセルやノーショーによって全体の約10〜12%の予約が失われているという問題も浮上した。運航面では、金曜日と午前中が最も混雑する時間帯であることもわかった。

このプロジェクトで直面した主な課題としては、不揃いな日付形式、多様な電話番号の書式、重複するデータ、そして無効な評価値が挙げられる。これらの課題は、それぞれTO_DATE()関数による日付変換、正規表現（特定のパターンに合致する文字列を扱う機能）を用いた電話番号の正規化、データベース内部の識別子による重複データの削除、そして無効な評価値のNULL設定といったSQLの機能によって解決された。

この経験から得られた重要な学びは、まず「汚い」データは必ずステージングテーブルで一時的に扱うべきであるということだ。そして、データのクリーンアップ作業がデータ分析全体の大部分を占める（約70%）ほど重要であることも再認識された。さらに、RANKやLAGといった「ウィンドウ関数」と呼ばれる高度なSQLの機能が、データの深層にあるインサイトを引き出す上で非常に強力なツールであること、そしてデータベースの「インデックス」が、データ検索のパフォーマンス向上に不可欠であることも学んだ。このプロジェクトは、SQLが単にデータベースから情報を取得する「クエリ言語」であるだけでなく、ビジネス上の課題を解決するための強力な「ビジネスインテリジェンス」ツールであることを示している。

SafariConnectのこのエンドツーエンドのSQLプロジェクトは、混沌とした生データから実用的なインサイトを導き出すことに成功した。データのステージング、クリーンアップ、データベース設計、そして分析という一連のプロセスを通じて、データに基づいた意思決定のための強固な基盤が構築されたのだ。これは、いかにしてSQLが、一貫性のない生のデータをビジネスの成長に繋がる明確な答えへと変えることができるかを示す好例である。