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

 

AdobeAnalytics: Adobe I/OのAPIを使ってSAINT分類データを取得する前のページ

サーバーサイドGTM: ウェブコンテナを使って1stパーティドメインからgtm.jsを配信してみた次のページ

ピックアップ記事

  1. 最速で理解したい人のためのIT用語集

関連記事

  1. Adobe Analytics

    レポートビルダーを使ってデータを出す方法

    こんにちは!インターンの藤本です。今日はレポートビルダーを使ってデー…

  2. Adobe Analytics

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

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

  3. Adobe Analytics

    Adobe Analytics Business Practitionerが変わりました。

    はじめにAdobe Analyticsの試験であるBusiness …

  4. Adobe Analytics

    Adobe Analytics-分析ワークスペースの使い方

    こんにちは!インターン生の藤本です。今回はフリーフォームを使用した分…

  5. Adobe Analytics

    Adobe Analytics: Legacy s_code.jsからAppMeasurement.…

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

  6. Adobe Analytics

    検索エンジン-自然とは-Adobe Analyticsの指標説明

    こんにちは。インターン生の市川です。今回は検索エンジン-自然について…

カテゴリ
最近の記事
  1. Adobe WebSDK FPIDでECIDの復元を検証
  2. dbt Projects on Snowflakeで作成した…
  3. Dataformでtype:’increment…
  4. dbt Projects on SnowflakeをTASK…
  5. AWS発のAIエージェントIDE「Kiro」を使用した仕様駆…
  1. ChatGPT

    LangChainのソースコードから実装を見てみる(ChatModelのinvo…
  2. Google Analytics 4

    GA4のスキーマ変更にご用心
  3. Data Clean Room

    セッション資料:Snowflakeで始めるData Clean …
  4. IT用語集

    シンクライアント(Thin Client)って何?
  5. ヒートマップ

    ユーザビリティの検証で気を付けたいこと、やってはいけないこと③
PAGE TOP