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

    GTMのdataLayerをAdobeAnalyticsの「s」オブジェクトにコピーする

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

  2. Adobe Analytics

    購入とは-Adobe Analyticsの指標説明

    今回は購入について説明します。「購入」の下には「購入回数」「購入点数…

  3. Adobe Analytics

    AdobeAnalyticsでReactNativeアプリを計測する

    この記事は2018年12月7日現在の情報を元にしているのと、レガシーA…

  4. Adobe Analytics

    Adobe Analytics 3つの主要指標

    こんにちは!インターンの藤本です。今日はAdobe Analytic…

  5. Adobe Analytics

    AdobeAnalytics:WordPressやHTML5内の動画を計測する

    こんにちは、エクスチュアの鈴木です。Adobe Analyti…

  6. Adobe Analytics

    出口とは-Adobe Analyticsの指標説明

    今回は出口について説明いたします。出口とはサイトを訪れたユーザの、…

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

    クリック・ヒートマップの使い方
  2. Mouseflow

    Mouseflow: MouseflowタグをWebサイトに導入する
  3. ブログ

    ②DMPについてーAdobe Audience Manager
  4. ブログ

    「Power BI」を使ってみよう!
  5. Adobe Summit

    Adobe Summit 2019 レポート
PAGE TOP