Adobe Analytics

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カラムの扱いがメンドクサイなと思う事はありませんか?
なければそれで結構なんですが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. GA 360 Suite

    BigQuery: Google Analytics 360のネストされたデータをフラット変換するS…

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

  2. Adobe Analytics

    Adobe Analytics: VisitorAPIでSafariでもクロスドメイン計測をする

    この情報は2016年9月時点のものです。こんにちは、エクスチュ…

  3. Adobe Analytics

    Adobe Analyticsを学ぶ

    こんにちは、エクスチュアの津村です。今回はAdobe Analyti…

  4. Adobe Analytics

    Looker: Sankey Diagramを使ってサイト内フローを可視化する

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

  5. Adobe Analytics

    Adobe Analytics + Google BigQueryでよく使うSQL例 6選

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

  6. Adobe Analytics

    Adobe Analytics:自動で分析してくれる貢献度分析(異常値検出)機能

    こんにちは、CEOの原田です。今回は随分前から公開されてるのに…

最近の記事

  1. エクセルで「テーブル」を使うメリット① 「BIツールで使いや…
  2. GTMで複数のコンテナに同じ実装をしたい
  3. イベントレポート Qualtrics XM on tour …
  4. イベントレポート Qualtrics XM on tour …
  5. ObservePoint:2022年デジタル・ガバナンスレポ…
  1. Tableau

    TableauでTreasure Data上のデータへ接続する方法(2019/1…
  2. Adobe Summit

    Adobe Summit 2019 レポート
  3. Adobe Analytics

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

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

    CXツール「KARTE」ってどんなツール?主な機能や魅力をざっくり紹介!
PAGE TOP