キャンペーンURLビルダーで生成したURLからパラメータを取得するSQLクエリ

今回は、キャンペーンURLビルダーで生成したURLからパラメータの値を取得するSQLクエリを紹介する。

SQLクエリ概要

各種マーケティングキャンペーンの効果測定をおこなうために、Googleが提供しているキャンペーンURLビルダー（Campaign URL Builder)を利用して、URLに計測パラメータを仕込むケースがよく見られる。

そこで、上記のパラメータ付きURLから REGEXP_EXTRACT 関数を利用して utm_source , utm_medium , utm_campaign 部分を抽出することで、流入ソース別、媒体別、キャンペーン別のセッション数やユーザー数、コンバージョン数などを計測することが可能となる。

REGEXP_EXTRACT関数

正規表現 regexp と一致する value 内の部分文字列を返します。一致がない場合、NULL を返します。

(出典)

https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions?hl=ja#regexp_extract

SQLクエリサンプル

クエリサンプルは、以下のようになる。

WITH prep AS (
  SELECT
    "<https://www.technogram.co.jp?utm_source=google&utm_medium=cpc&utm_campaign=10pkg-lp>" AS url -- 計測パラメータ付きURLのサンプル
)

SELECT
  REGEXP_EXTRACT(url, 'https?://([^?]*)') AS url_domain,
  REGEXP_EXTRACT(url, 'utm_source=([^&]*)') AS url_source,
  REGEXP_EXTRACT(url, 'utm_medium=([^&]*)') AS url_medium,
  REGEXP_EXTRACT(url, 'utm_campaign=([^&]*)') AS url_campaign,
FROM
  prep

BigQueryでの実行結果

BigQueryでの実行結果は、以下のようになる。