Adobe Analytics

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

こんにちは、エクスチュアCTOの権です。

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

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

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

  • そのままカンマ区切りで格納
  • 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

結果はこうなります。

11/1にevent20が発生した訪問者の数は 1,879人 でした。

まとめ

前後にカンマをつけることによって、

  • event20が単独で発生している場合 ・・・ 219 -> ,219, に変換
  • event20よりも前のeventと同時発生している場合 ・・・ 218,219 -> ,218,219, に変換
  • event20よりも後のeventと同時発生している場合 ・・・ 219,220 -> ,219,220, に変換

という文字列に変わるため、 %,219,% の検索がマッチするようになります。
つまり、イチイチ正規表現でカンマの存在を考慮したり、splitして1個ずつ判定していかなくてもOKです。

通常のRDBでこんなSQL書いてると激重で怒られそうですが、ここは流石のBigQueryです。全く問題になりません。

なお、event1〜event100は 200〜299 という範囲の番号が付与されてますが、
event101〜event1000は 20100〜20999 という番号になっているのでご注意ください。

あと、purchaseIDとevent serializationを考慮すると、若干クエリの内容も変わりますが、だいたいこんな感じです。

弊社はAdobe AnalyticsのDatafeedとBiqQueryを活用したビッグデータ分析支援などを行なっております。
お問い合わせはこちからどうぞ。

関連記事

  1. Adobe Analytics

    Adobe Analytics: RSIDを間違えてしまったデータを正しいRSに入れ直す

    こんにちは、エクスチュアCTOの権です。今回はAdobe An…

  2. Adobe Analytics

    Adobe Analytics:IF関数を使用した計算指標

    Adobe Analyticsで1年以上前についた計算指標に関数が使え…

  3. Adobe Analytics

    Adobe Analytics: カスタムリンク計測を1行で書く

    こんにちは、CTOの権です。Adobe Analytics の…

  4. Adobe Analytics

    Adobe Analytics:Report Builderの小技:リクエストのあるシートを丸ごとコ…

    エクセルでの定期レポートを作る際に、一度データブロックを作っておけば以…

  5. Adobe Analytics

    Adobe Analytics: AppMeasurement.jsの実装開発作業に使うツール Ja…

    こんにちは、CTOの権です。今回はAdobe Analyticsの…

  6. Adobe Analytics

    Adobe Analytics:ワークスペース:セグメント比較機能の紹介

    Adobe Summitで発表されていたSegment IQ機能が実は…

最近の記事

  1. Adobe Target: at.jsの「チラつき」を手っ取…
  2. Tableau:LOD徹底解説 – FIXED編…
  3. BigQuery: Adobe Datafeed: even…
  4. Adobe Analytics: SegmentsAPIを使…
  5. Google Compute Engine のLinuxVM…
  1. Adobe Analytics

    Adobe Analytics: VisitorAPIでSafariでもクロスド…
  2. Adobe Analytics

    Adobe Analytics: データフィードをGoogle BigQuery…
  3. Adobe Analytics

    Adobe Analytics: BigQueryにロードしたデータフィードをD…
  4. Google BigQuery

    GCP: 今月のGCP課金額をslackに自動的に書き込む
  5. Adobe Experience Cloud

    Adobe Marketing Cloud:サインインで問題が起きたときの対処法…
PAGE TOP