BigQuery: Adobe Datafeed: event_listカラムの手軽な扱い方

こんにちは、エクスチュアの權泳東(権泳東/コン・ヨンドン)です。

今回は、AdobeAnalyticsのDatafeedをBigQueryに入れた時にevent_list(主にpost_event_list)をどう扱うかというTipsです。

※ヒットに含まれるs.events変数はevent_listが無加工のオリジナルで、post_event_listがProcessing RulesとVISTAなどの処理を経た最終的なインプットです。

event_listカラムの扱いがメンドクサイなと思う事はありませんか？
なければそれで結構なんですがｗ、私が見聞きした中ですと・・・

そのままカンマ区切りで格納
event_listをカンマでsplitしてか各event毎に別カラムに格納（横方向に広げる）
event_listをカンマでsplitして、別の行に分けて格納（縦方向に伸ばす）

というケースに別れました。

私は「そのまま派」です。

理由は単純で、
カラムを増やして横方向に伸ばすとクエリするカラムが増えて管理が煩雑なのと、
縦方向に行を増やすとデータ量が増えてしまうためです。

では、実際にクエリする時にどうしてるのかというと、「event_listの前後にカンマを付けて検索する」です。

event_listの前後にカンマを付けて検索する

例えば以下のようなevent_listが格納されている場合：

同じヒットの中にevent20(219), event23(222), event28(227), eVar46インスタンス(145）が同時に含まれてます。

ここで、「2017/11/1にevent20が発生した訪問者数を集計したい」という場合は、こんなクエリで抽出してます。
※post_event_listを対象としています。

#standardSQL
select
  format_timestamp('%Y-%m-%d', timestamp_seconds(hit_time_gmt), 'Asia/Tokyo') as hitdate,
  count(distinct concat(post_visid_high,  '-', post_visid_low)) as visitors
from
  hoge.datafeed
where 
  -- post_event_listの前後にカンマをつけて「%,219,%」を検索
  concat(',', post_event_list, ',') like '%,219,%'
  and hit_source = 1
  and exclude_hit = 0
  and format_timestamp('%Y-%m-%d', timestamp_seconds(hit_time_gmt), 'Asia/Tokyo') = '2017-11-01'
group by 
  hitdate

結果はこうなります。