今回は、キャンペーンURLビルダーで生成したURLからパラメータの値を取得するSQLクエリを紹介する。 SQLクエリ概要 各種マーケティングキャンペーンの効果測定をおこなうために、Googleが提供しているキャンペーンURLビルダー（Campaign URL Builder)を利用して、URLに計測パラメータを仕込むケースがよく見られる。 そこで、上記のパラメータ付きURLから REGEXP_EXTRACT 関数を利用して utm_source , utm_medium , utm_campaign 部分を抽出することで、流入ソース別、媒体別、キャンペーン別のセッション数やユーザー数、コンバージョン数などを計測することが可能となる。 REGEXP_EXTRACT関数 正規表現 regexp と一致する value 内の部分文字列を返します。一致がない場合、NULL を返します。 (出典)
collect?v=1というお馴染みの文字列を含むあのビーコンURL、ペイロードとか呼んだりもしますけどアレが[protoPayload.resource]カラムに記録されてます。 ペイロード以外にも: protoPayload.ip (IPアドレス) protoPayload.userAgent (ユーザーエージェント) timestamp (タイムスタンプ) などがログに残ってます。 2.ビーコンログを分解 ビーコンのペイロードそのままだと全く何も分からないので、SQLで分解して別テーブルを作ります。 分解用SQLが長ったらしいけどキニシナイ！ CREATE TEMP FUNCTION decode(s string) returns string language js as """ var ret = ''; try { ret = decodeURIComponent(s); }
背景 BigQueryでURLを分解したい！そんな時、ありますよね。 仕事で使う都合上色々と調べていたのですが、自分が欲しい粒度でまとまっているものがなかったので自分で書いてみました。その結果を共有します！ まず、前提としてBigQueryにはURLを分解する関数が存在しないため、正規表現を用いて行う必要があります。 今回はBigQuery上でURLの下記部分の抽出方法を書いていきたいと思います。 FQDN(ホスト名+ドメイン名) path utmパラメータ各種 パラメータ部分全て 使用する関数 今回使用する関数は『 REGEXP_EXTRACT 』。 正規表現部分(regexp)と一致するvalue内の最初の文字列を返して、一致部分がないときはNULLを返す関数です。position部分に正の整数を入れると、最初の文字列以外を指定することもできます。 正規表現内にキャプチャグループ()が
こんにちは、エクスチュアの權泳東(権泳東/コン・ヨンドン)です。 今回は、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; """; SEL
早速後編です。実は筆者もBigQueryをガチで使うのは初めてです。使ってみてわかったのは、結構普通のSQL(私の普通はOracle基準、つまりMySQLから見たらコテコテの機能モリモリのSQL)が通るということです。UDFもPreview？で通るらしいですが、今回はUDFまでは行きませんでした。 BQ予備知識 BQの予備知識としては - SQLで問い合わせる - Updateが出来ない - Deleteが出来ない まずは、これだけ知ってれば十分だと思います。BQの紹介で gcpja-night でも言われてたことですが。 SQLがエンジニアだけのものだと言うのは間違いで、非エンジニアでもSQLぐらい叩けるひとはザラに居るので、1から10までエンジニアが面倒見なくていい。 とのことです。エンジニアのくせにSQLが出来ないという人は、、、反省して勉強して下さい。 それてしまいましたが、SQL
こんにちは。 使うSQLが200行を超えるのが当たり前になってきたデータチームの後藤です。 本記事では、VASILYデータチームで利用しているBigQueryによるデータの前処理のTipsを紹介します。 VASILYではサービスのマスタデータやログデータをGoogle BigQueryに集約して分析に活用しています。機械学習やデータ分析のための前処理を行う際、軽量なデータであれば抽出結果をPythonに渡して処理させることもできます。しかし、分析環境のメモリに載り切らないほど大きなデータを扱う場合、BigQuery内で前処理を済ませてしまうと時間と計算資源の節約になることが多いです。 今回はBigQueryからアクセスできるパブリックデータの1つ、hacker newsのデータを集計しながらTipsを紹介したいと思います。 欠落した日付を埋める 通常のGROUP BY句の場合 SQL Re
リリース、障害情報などのサービスのお知らせ
最新の人気エントリーの配信
j次のブックマーク
k前のブックマーク
lあとで読む
eコメント一覧を開く
oページを開く