Looker Studioでサーチコンソールのデータをチェックするとき、1つだけ厄介な問題があります。
それは、日本語URLが下図赤枠のように文字化け(？)してしまい、何のページかさっぱり分からなくなることです。
（※下図の表では計算フィールドでドメインを除去しています）
日本語URLが文字化け(？)する原因と対策
これはサーチコンソールが、URL情報をAPI・BigQueryやLookerスタジオなどに送信するときに「URLエンコード」という処理がかけることが原因です。
実は先日リリースした「レポート自動生成システム」でも、Pythonで「デコード」という処理をかけて、この問題を解決しています。
ただし、Looker Studioではデコード処理することができない（2025年6月時点）ので、BigQueryにエクスポートしているデータにSQLでデコード処理をかけることにしました。
BigQuery上のサーチコンソールデータに「デコード」をかける方法
ここで登場するのが以前ご紹介した「カスタムクエリ」です（下図参照）。
上図のカスタムクエリ欄に下記のようなSQLクエリを入れてやれば、デコードされた日本語URLが表示されるようになります。
サンプルSQLクエリ
SELECT
-- 日付
data_date,
-- デコードしたページURL（ドメイン除去機能付）
-- ドメイン除去する場合は https?://your-domain\.com のところを変更、ドメイン除去しない場合は ^https?://your-domain\.com を丸ごと削除
REGEXP_REPLACE(`bigfunctions.us.url_decode`(url), r'^https?://your-domain\.com', '') AS decoded_page,
-- 検索クエリ
query AS query,
-- 指標
SUM(clicks) AS clicks,
SUM(impressions) AS impressions,
SUM(sum_position) AS sum_position
FROM
-- your-project-id のところにプロジェクトIDを入力
`your-project-id.searchconsole.searchdata_url_impression`
WHERE
-- 2年分のデータを取得（日付パーティション条件）
data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 YEAR)
AND data_date <= CURRENT_DATE()
-- 必要に応じて追加のフィルタ条件
-- AND clicks > 0 -- クリックがあるデータのみ
GROUP BY
data_date,
decoded_page,
query
ORDER BY
clicks DESC
-- 必要に応じてデータ量制限
LIMIT 10000サンプルSQLクエリについての解説
上記のSQLクエリでは、下記の行で、URLのデコードと、ついでにドメインの除去も行いました。
-- デコードしたページURL（ドメイン除去）
REGEXP_REPLACE(`bigfunctions.us.url_decode`(url), r'^https?://your-domain\.com', '') AS decoded_page,URLのデコードは `bigfunctions.us.url_decode`(url) で、ドメインの除去は REGEXP_REPLACE で行っています。
汎用性があるように、正規表現でドメイン除去するようにしてもよかったのですが、面倒くさかったので、文字列をそのまま置換するようにしましたw
※補足：
今回のSQLクエリに使われている「BigFunctions」とは、BigQueryの機能拡張のために作成された、サードパーティー製の無料ツールです。
Google公式ではないため、使用については自己責任となりますが、オープンソースで透明性も高いことから多くのエンジニアに使用されています。
SQLクエリによるURLデコードの結果
下図がURLデコードをかけた結果です。
赤枠部分のように、日本語URLが表示されるようになりました。
今回の、ドメイン除去とURLデコードは、GA4とサーチコンソールのデータをマージ（＝結合）するときに必須の処理ですので、ぜひご活用ください。
ちなみに BigQueryを使っていない場合は、
①スプレッドシートにデータをもってきて、GASでデコード
②Looker Studioにはスプレッドシートのデータを読み込ませる
という方法でも対応可能です。
お困りの方は、お気軽に【無料相談】まで！
■ 無料相談・お問合せ (弊社からの営業は一切しません)
「うちの場合は自動化・省力化できるのか？」
まずはお気軽に無料相談をご利用ください。
- オンライン全国対応
- 相談だけでもOK
- 営業メールなどは一切しません
- 技術者が直接ヒアリングして回答します
- 概算費用・工数をご提示します
