Adobe Analytics

BigQuery: テーブルに格納されたURL文字列をKey-ValueのSTRUCT型で抽出する

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

今回は、BigQueryのテーブルに格納されているURL文字列をキー・バリュー形式のSTRUCT型にして抽出する方法について紹介します。

Web解析を行っていると、生のURLがデータウェアハウスに記録される事もあります。
URLそのままだと見づらい&扱いづらいので、これをキーと値に分けてみましょう。

REGEXP_EXTRACT_ALL関数と、STRUCT型を使います。

 

実際にクエリを試す

 
クエリはこうなります。

CREATE TEMPORARY FUNCTION
  decode(s string)
  RETURNS string
  LANGUAGE js AS """
var ret = '';
try {
  ret = decodeURIComponent(s);
}catch(e){}
return ret;
""";

SELECT
  raw_url,
  STRUCT(REGEXP_EXTRACT_ALL(log, r'(?:\?|&)(?:([^=]+)=(?:[^&]*))') AS keys,
    REGEXP_EXTRACT_ALL(decode(log), r'(?:\?|&)(?:(?:[^=]+)=([^&]*))') AS vals ) AS log
FROM
  adobe_beacon.hit_log
ORDER BY 1

 
結果はこうなります。

URLパラメータをkey-value形式に変換


 
REGEXP_EXTRACT_ALL関数を使って、正規表現でURLパラメータがキーと値に分かれてARRAY型で抽出してます。
そしてSTRUCT型のネストされたデータにしています。

生のURLよりも断然見やすくて扱いやすくなりました。

 
なお、ここで使ったURLログは以前Adobe AEP SDKが送るビーコンペイロードをリバースプロキシを使ってキャプチャする方法について紹介しましたが、その時取得したログをBigQueryにロードしたものです。
↓↓↓

Adobe AEP SDK: リバースプロキシを使ったアプリ計測検証方法

 
弊社では、Adobe Analytics認定エキスパート資格・Google Cloud認定プロフェショナルデータエンジニア資格・Linux Professional Institute認定資格を持ったエンジニア達が各種マーテックツールの導入実装コンサルティングサービスや、GCP/AWS/Azureなどのパブリッククラウドを使ったデータ分析基盤構築コンサルティングサービスを提供しております。

お問い合わせはこちらからどうぞ。

参考記事: Extract parameter from url in Google BigQuery using regex – Stack Overflow

 

関連記事

  1. Adobe Analytics

    続・SafariのITP2.x対策として別サーバー使ってクッキーを永続化する

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

  2. GA 360 Suite

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

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

  3. Adobe Analytics

    Adobe Analytics: レガシーs_code.jsのリミッターを解除してeVar76以上を…

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

  4. Adobe Analytics

    Adobe Analytics: BigQueryにロードしたデータフィードをDataStudioで…

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

  5. Adobe Analytics

    Adobe Summit 2020レポート: Customer Journey Analytics:…

    こんにちは。エクスチュアインターン生の畝川(せがわ)です。今回…

  6. Adobe Analytics

    AdobeAnalytics Datafeed: BigQueryのSIGN関数を使った小ワザ

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

最近の記事

  1. System Managerを利用したVPC内Redshif…
  2. Amazon Redshift ビルド168まとめ
  3. Tableauのテスト自動化を実現する Wiiisdom O…
  4. Databricksが買収した8080Labのbambool…
  5. databricksのnotebookを使ってみよう その2…
  1. Amazon Web Services

    パブリックサブネット vs プライベートサブネット
  2. Adobe Experience Cloud

    Adobe Summit 2020レポート: Building for a Co…
  3. Adobe Analytics

    Adobe新タグ登場って本当?AEP Web SDKって何だ?
  4. KARTE

    KARTE:簡単!アンケートの設定、アンケート結果に併せた接客配信
  5. Adobe Analytics

    Adobe Analytics: Webサイト内で発生したJavascriptエ…
PAGE TOP