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

    AdobeAnalytics: スクロールで目標に到達したらカスタムリンク

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

  2. Adobe Analytics

    Adobe Analytics: データフィードをBigQueryで集計する

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

  3. Firebase Analytics

    Firebase Analyticsの新しいBigQueryスキーマを試す

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

  4. Adobe Analytics

    AdobeAnalytics: スマホのスワイプにカスタムリンクを実装する

    こんにちは、エクスチュアの権泳東/コン・ヨンドン(@exturekwo…

  5. GA 360 Suite

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

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

  6. Adobe Analytics

    再来訪頻度とは-Adobe Analyticsの指標説明

    今回は再来訪頻度について説明いたします。再来訪頻度とはサイトを訪問…

カテゴリ
最近の記事
  1. 【dbts25】Snowflake×PostgreSQLのニ…
  2. TROCCO dbt連携編
  3. KARTEの「フレックスエディタ(β)」登場!ノーコードでこ…
  4. dbt Projects on Snowflake使ってみた…
  5. Cortex Analystを使ってみた
  1. Mouseflow

    Mouseflow:ヒートマップ表示の仕様
  2. Adobe Analytics

    レポートビルダーを使ってデータを出す方法
  3. ブログ

    ②DMPについてーAdobe Audience Manager
  4. ヒートマップ

    ユーザビリティの検証で気を付けたいこと、やってはいけないこと④
  5. Ad Hoc Analysis

    Adobe Analytics: Ad Hoc Analysisでセグメントの条…
PAGE TOP