はじめに

Vertica拡張SQLのPattern Matchingを使用すると、WEBサイトのクリックストリームデータなどから、一連の動きにマッチしたデータを抽出することができます。
例えば「広告をクリック→商品を見る→レジに行く→購入」というパターンにマッチしたデータのみ抽出してコンバージョン分析に活用することができます。

コマンド構文

Pattern MatchingはMATCH句を使用します。


各パラメータの説明

パラメータ名内容
PARTITION BY※オプション
PATTERN節で定義されたパターンが一致するウィンドウデータの範囲を定義します。
PATTERN節で定義された一致パターンによってデータを分割します。PARTITION句を省略すると、データセット全体が単一のパーティションとみなされます。
ORDER BYパーティション内でのソート順を指定します。
DEFINEイベントタイプを構成するブール式を正規表現で定義します。
event_name各行を評価するためのイベント名を指定します。
boolean_expr評価のための式(true or falseを返す式)を指定します。
PATTERN pattern_nameパターン名を指定します。
regexpパターンマッチングのルールを正規表現で指定します。
例えばEntry→0以上のOnsite→Purchaseにマッチするログのみを抽出する場合は以下のように指定します。

(Entry Onsite* Purchase)
ROWS MATCH ※オプション
同じ行に対して複数のイベントが同時にtrue(マッチする)と評価された場合の動作を指定します。

ROWS MATCH ALL EVENTS
各行に対してマッチするイベントが1つのみの場合に指定します。複数のイベントがマッチする場合はエラーが返ります。

ROWS MATCH FIRST EVENT
各行に対してマッチするイベントが複数ある場合、SQL内で最初に定義されたイベントが選択されます。

利用例

Webサイトのクリックストリームデータに対してPattern Matching関数を使用してコンバージョン分析を行います。

Webサイトのクリックストリームデータが格納されるテーブル(clickstream_log)の定義


clickstream_logテーブル

本例では、website1.comを広告ページのURL、website2.comを別ページのURL、website3.comを自社ECサイトのURLと仮定します。

実行するSQL


DEFINE節では各行のデータを条件に応じて「Entry」「Onsite」「Purchase」と定義しています。
・広告ページ(website1.com)からの訪問で、かつ自社ECサイトのwebsite3.com/homeにアクセスしている→「Entry」と定義
・自社ECサイトのwebsite3.com内の移動→「Onsite」と定義
・自社ECサイトのwebsite3.com内で商品を購入→「Purchase」と定義

PATTERN節では「Entryから始まり、1回以上のOnsiteを経由し、Purchaseに至る」という条件にマッチした行を抽出するよう定義しています。

実行結果


上記を実行すると、uid,sid単位で条件に合致したデータのみが抽出されます。
本例ではuid1、2のデータが条件に合致しました。uid3は商品を購入していますが、広告ページ(website1.com)からの訪問ではないため抽出されません。

Pattern Matchingで抽出した結果を使用することで、購入に至るまでにどのようなサイトを見たかといったコンバージョン分析を行うことが容易になります。


検証バージョンについて

この記事の内容はVertica 8.0で確認しています。