BigQuery で、URL parameters の key と value の組み合わせの数を算出
BigQuery で、URL parameters の key と value の組み合わせの数を算出
BigQuery で、URL のパラメータを分析する機会があった。
やりたいこと
下記を算出したい。
URL parametersのkeyがどれくらいあるかURL parametersのkeyとvalueの組み合わせがどれくらいあるか
実行環境
BigQuery
算出
URL parameters の key がどれくらいあるか
算出したいカラム
- param_key:
URL parametersのkey - record_count:
keyの数
算出の流れ
regexp_extract_allにURLを渡して、URL parametersのkeyを取得- 1 を
unnestで 1 行ずつ展開 - 2 を
group byしてkeyの数を算出
クエリ
-- 仮のデータ
with hoge_referers as (
select
'https://hene.dev/about?utm_source=x&utm_medium=y&utm_campaign=z' as referer
union all
select
'https://hene.dev/about?utm_source=xx&utm_medium=yy' as referer
union all
select
'https://hene.dev/about?utm_source=x' as referer
)
-- URL parameters を展開
-- * 1 つの referer に 複数の URL parameters が含まれていた場合は、全て展開される
-- * regexp_extract_all は、ARRAY データ型(配列) を返す
, hoge_params as (
select
-- URL parameters の key を取得
regexp_extract_all(referer, r'(?:\?|&)(?:([^=]+)=(?:[^&]*))') as param_keys
from hoge_referers
)
-- unnest で、ARRAY データ型(配列) の各要素を 1 行ずつ展開
select
param_key
, count(*) as record_count
from hoge_params
, unnest(param_keys) as param_key
group by
param_key
order by
record_count desc
算出結果
URL parameters の key と value の組み合わせがどれくらいあるか
算出したいカラム
- param_key:
URL parametersのkey - param_value:
URL parametersのvalue - record_count:
keyとvalueの組み合わせの数
算出の流れ
regexp_extract_allにURLを渡して、URL parametersをARRAY データ型(配列)で取得- 1 を
unnestで 1 行ずつ展開 - 2 から
URL parametersのkeyとvalueを取得 - 3 を
group byしてkeyとvalueの組み合わせの数を算出
クエリ
-- 仮のデータ
with hoge_referers as (
select
'https://hene.dev/about?utm_source=x&utm_medium=y&utm_campaign=z' as referer
union all
select
'https://hene.dev/about?utm_source=xx&utm_medium=yy' as referer
union all
select
'https://hene.dev/about?utm_source=x' as referer
)
-- URL parameters を展開
-- * 1 つの referer に 複数の URL parameters が含まれていた場合は、全て展開される
-- * regexp_extract_all は、ARRAY データ型(配列) を返す
, hoge_params as (
select
-- URL parameters の params を取得
regexp_extract_all(referer, r'(?:\?|&)((?:[^=]+)=(?:[^&]*))') as params
from hoge_referers
)
-- unnest で、ARRAY データ型(配列) の各要素を 1 行ずつ展開
, unnested as (
select
-- URL parameters の key を取得
regexp_extract(param, r'(^.*)=.*$') as param_key
-- URL parameters の value を取得
, regexp_extract(param, r'^.*=(.*$)') as param_value
from hoge_params
, unnest(params) as param
)
select
param_key
, param_value
, count(*) as record_count
from unnested
group by
param_key
, param_value
order by
param_key desc
, record_count desc
算出結果
まとめ
URL parameters の key と value の組み合わせがどれくらいあるのかを確認することができた。
