OpenText Analytics Database 旧Vertica

技術情報サイト

Analytics Database

一連のパターンにマッチしたログを抽出する(Event Series Pattern Matching)

公開日：
更新日：
基本操作
#事例

はじめに

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

コマンド構文

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

SELECT 列名
FROM テーブル名
MATCH ( [ PARTITION BY 列名, ] ORDER BY 列名
... DEFINE event_name AS boolean_expr [, ...]
... PATTERN pattern_name AS ( regexp ) 
... [ ROWS MATCH { ALL EVENTS | FIRST EVENT } ] );

各パラメータの説明

パラメータ名内容
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)の定義

CREATE TABLE clickstream_log (
  uid INT,             --ユーザID
  sid INT,             --セッションID
  ts TIME,             --ページ滞在中に発生したタイムスタンプ
  refURL VARCHAR(20),  --クリック元ページのURL
  pageURL VARCHAR(20), --クリック先(滞在先)ページのURL
  action CHAR(1)       --ユーザがページにアクセス後に行ったアクション('P' = 購入, 'V' = 閲覧のみ) 
);

clickstream_logテーブル

dbadmin=> SELECT * FROM clickstream_log ORDER BY ts ;
 uid | sid |    ts    |        refURL        |       pageURL        | action
-----+-----+----------+----------------------+----------------------+--------
   1 | 100 | 12:00:00 | website1.com         | website3.com/home    | V
   1 | 100 | 12:01:00 | website3.com/home    | website3.com/floby   | V
   1 | 100 | 12:02:00 | website3.com/floby   | website3.com/shamwow | V
   1 | 100 | 12:03:00 | website3.com/shamwow | website3.com/buy     | P
   3 | 100 | 12:08:00 | website2.com         | website3.com/home    | V
   2 | 100 | 12:10:00 | website1.com         | website3.com/home    | V
   3 | 100 | 12:10:00 | website3.com/home    | website3.com/forks   | V
   2 | 100 | 12:11:00 | website3.com/home    | website3.com/forks   | V
   3 | 100 | 12:12:00 | website3.com/forks   | website3.com/buy     | P
   2 | 100 | 12:13:00 | website3.com/forks   | website3.com/buy     | P
(10 rows)

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

実行するSQL

dbadmin=> SELECT uid,
dbadmin->        sid,
dbadmin->        ts,
dbadmin->        refurl,
dbadmin->        pageurl,
dbadmin->        action,
dbadmin->        event_name(),
dbadmin->        pattern_id(),
dbadmin->        match_id()
dbadmin-> FROM clickstream_log
dbadmin-> MATCH
dbadmin->   (PARTITION BY uid, sid ORDER BY ts,uid
dbadmin(>    DEFINE
dbadmin(>      Entry    AS RefURL  ILIKE '%website1.com%' AND PageURL ILIKE '%website3.com%',
dbadmin(>      Onsite   AS PageURL ILIKE '%website3.com%' AND Action='V',
dbadmin(>      Purchase AS PageURL ILIKE '%website3.com%' AND Action = 'P'
dbadmin(>    PATTERN
dbadmin(>      a AS (Entry Onsite* Purchase )
dbadmin(>    ROWS MATCH FIRST EVENT);

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 |    ts    |        refurl        |       pageurl        | action | event_name | pattern_id | match_id
-----+-----+----------+----------------------+----------------------+--------+------------+------------+----------
   1 | 100 | 12:00:00 | website1.com         | website3.com/home    | V      | Entry      |          1 |        1
   1 | 100 | 12:01:00 | website3.com/home    | website3.com/floby   | V      | Onsite     |          1 |        2
   1 | 100 | 12:02:00 | website3.com/floby   | website3.com/shamwow | V      | Onsite     |          1 |        3
   1 | 100 | 12:03:00 | website3.com/shamwow | website3.com/buy     | P      | Purchase   |          1 |        4
   2 | 100 | 12:10:00 | website1.com         | website3.com/home    | V      | Entry      |          1 |        1
   2 | 100 | 12:11:00 | website3.com/home    | website3.com/forks   | V      | Onsite     |          1 |        2
   2 | 100 | 12:13:00 | website3.com/forks   | website3.com/buy     | P      | Purchase   |          1 |        3
(7 rows)

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

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

検証バージョンについて

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

更新履歴

2019/04/11　検証バージョンを9.2に変更
2017/3/6　　本記事を公開

この記事をシェアする