Webサイトのデータ分析やSEO施策において、URLの階層構造を理解し分析することは非常に重要です。適切な階層構造は、ユーザビリティの向上とSEO効果の最適化に直接影響を与えます。
本記事では、Googleスプレッドシートを使用してURLの各階層を効率的に抽出・分析する方法を、実践的な例を交えて解説します。
REGEXEXTRACT関数によるURLの階層取得
REGEXEXTRACT関数は、正規表現を使用してURLから必要な部分のみを抽出できる便利な関数です。
以下、各階層の取得方法を順番に説明します。
1階層目（ドメイン）の抽出
=IFERROR(REGEXEXTRACT(A1, "^https?://([^/]+)"), "ドメインが存在しません")
※ A1にURLが入力されている前提です。
解説: URLの `https://` または `http://` に続く、最初の `/` までの部分（ドメイン名）を抽出します。
2階層目の抽出
=IFERROR(REGEXEXTRACT(A1,"^https?://[^/]+/([^/]+)"),"階層が存在しません")
※ A1にURLが入力されている前提です。
解説: ドメインの次の階層（例: `example.com/category/` の `category`）を抽出します。
3階層目の抽出
=IFERROR(REGEXEXTRACT(A1,"^https?://[^/]+/[^/]+/([^/]+)"),"階層が存在しません")
※ A1にURLが入力されている前提です。
解説: 2階層目の次の階層（例: `example.com/category/subcategory/` の `subcategory`）を抽出します。
4階層目の抽出
=IFERROR(REGEXEXTRACT(A1,"^https?://[^/]+/[^/]+/[^/]+/([^/]+)"),"階層が存在しません")
※ A1にURLが入力されている前提です。
5階層目の抽出
=IFERROR(REGEXEXTRACT(A1,"^https?://[^/]+/[^/]+/[^/]+/[^/]+/([^/]+)"),"階層が存在しません")
※ A1にURLが入力されている前提です。
6階層目の抽出
=IFERROR(REGEXEXTRACT(A1,"^https?://[^/]+/[^/]+/[^/]+/[^/]+/[^/]+/([^/]+)"),"階層が存在しません")
※ A1にURLが入力されている前提です。
7階層目の抽出
=IFERROR(REGEXEXTRACT(A1,"^https?://[^/]+/[^/]+/[^/]+/[^/]+/[^/]+/[^/]+/([^/]+)"),"階層が存在しません")
※ A1にURLが入力されている前提です。
FIND関数によるURL内の文字位置検索
FIND関数は、文字列内から特定の文字（ここでは `/`）が何番目にあるかを検索する関数です。単体で階層を直接抽出するわけではありませんが、`MID`関数など他の関数と組み合わせることで、特定の階層文字列を抽出するための位置情報として利用できます。
例えば、URL (`A1`セル) 内の3番目の `/` の位置は以下の式で見つけられます。
=FIND("/", A1, FIND("/", A1, FIND("/", A1, 9) + 1) + 1)
解説: 最初の `/` は `https://` の中のものであるため、`FIND(“/”, A1, 9)` で9文字目以降（`https://` の後）から `/` を探し始めます。これにより、パス部分の区切り文字 `/` の位置を特定するのに役立ちます。
REGEXMATCH関数による特定階層の一致確認
URLが特定の階層数を持つパターンと一致するかどうかを確認する際に使用します。TRUEまたはFALSEを返します。
1階層（ドメインのみ）のURLにマッチ
=REGEXMATCH(A1, "^https?://[^/]+/?$")
※ A1にURLが入力されている前提です。
解説: `example.com` や `example.com/` のようなURLに一致します。
2階層のURLにマッチ
=REGEXMATCH(A1, "^https?://[^/]+/[^/]+/?$")
※ A1にURLが入力されている前提です。
解説: `example.com/category` や `example.com/category/` のようなURLに一致します。
3階層のURLにマッチ
=REGEXMATCH(A1, "^https?://[^/]+/[^/]+/[^/]+/?$")
※ A1にURLが入力されている前提です。
4階層のURLにマッチ
=REGEXMATCH(A1, "^https?://[^/]+/[^/]+/[^/]+/[^/]+/?$")
※ A1にURLが入力されている前提です。
5階層のURLにマッチ
=REGEXMATCH(A1, "^https?://[^/]+/[^/]+/[^/]+/[^/]+/[^/]+/?$")
※ A1にURLが入力されている前提です。
6階層のURLにマッチ
=REGEXMATCH(A1, "^https?://[^/]+/[^/]+/[^/]+/[^/]+/[^/]+/[^/]+/?$")
※ A1にURLが入力されている前提です。
URLデータ処理でよくあるトラブルと解決方法
URLクエリパラメータの除去方法
アクセス解析やSEO分析時に不要なクエリパラメータ（`?`以降の部分）を除外し、基本URLのみを抽出したい場合があります。
=REGEXEXTRACT(A1, "^([^?]+)")
※ A1にURLが入力されている前提です。
解説: URLの先頭から、`?`が現れる直前までの部分を抽出します。`?`がない場合はURL全体が返されます。
スプレッドシートでのURL表記揺れの統一化
大量のURLデータを扱う際、末尾のスラッシュの有無や大文字小文字の違いなど、表記揺れが発生することがあります。これらを統一することで、正確な分析が可能になります。
末尾スラッシュの一括統一（有りへ統一）
=REGEXREPLACE(A1, "/?$", "/")
※ A1にURLが入力されている前提です。
解説: URLの末尾にスラッシュ`/`がない場合は追加し、既にある場合はそのままにします。
URLパス部分の大文字小文字の統一化（小文字へ統一）
一般的にドメイン名以外（パス部分）は、大文字と小文字が区別されることがあります。分析のために小文字に統一します。
=REGEXEXTRACT(A1,"^https?://[^/]+")&LOWER(REGEXREPLACE(A1,"^https?://[^/]+",""))
※ A1にURLが入力されている前提です。
解説: ドメイン部分とパス部分を分け、パス部分のみを`LOWER`関数で小文字に変換し、再度結合します。
特殊文字を含むURLの処理方法（デコード例）
URLエンコードされた特殊文字（例: `%20` はスペース）が含まれている場合、分析しやすいように元の文字に戻したい（デコードしたい）ことがあります。ただし、スプレッドシートの標準関数だけでは完全なURLデコードは難しい場合があります。ここではよくあるスペース（`%20`）を例として示します。
=REGEXREPLACE(A1, "%20", " ")
※ A1にURLが入力されている前提です。
解説: URL内の `%20` を半角スペースに置換します。他の特殊文字も同様に `REGEXREPLACE` や `SUBSTITUTE` を使って置換できます。
複数URLの一括処理テクニック (ARRAYFORMULA)
A列の複数のURL（例: A2からA1000）に対して、同じ処理を一括で行いたい場合、`ARRAYFORMULA` を使うと便利です。各行に関数をコピー＆ペーストする必要がなくなります。
例：A2:A1000 の各URLからドメイン名を抽出する場合
=ARRAYFORMULA(IF(A2:A1000="",,IFERROR(REGEXEXTRACT(A2:A1000, "^https?://([^/]+)"), "ドメインが存在しません")))
解説: `ARRAYFORMULA` で囲むことで、`A2:A1000` の範囲に対して `REGEXEXTRACT` を適用します。`IF(A2:A1000=””,, …)` は、元のセルが空欄の場合は結果も空欄にするための処理です。
実践的な活用方法
カテゴリー別（例: 2階層目）のURL分析
`REGEXEXTRACT` で抽出した階層データ（例: B列に2階層目を抽出済みとする）を使って、どのカテゴリーにどれくらいのページが存在するかを集計します。
=QUERY({B2:B1000}, "SELECT Col1, COUNT(Col1) WHERE Col1 IS NOT NULL GROUP BY Col1 ORDER BY COUNT(Col1) DESC LABEL COUNT(Col1) 'ページ数'")
解説: `QUERY`関数を使い、B列のデータをグループ化してカウントし、ページ数が多い順に並べて表示します。
パンくずリスト生成
抽出した各階層のデータを結合して、簡易的なパンくずリストを生成します。サイト構造の可視化に役立ちます。（例: B列に1階層目、C列に2階層目、D列に3階層目を抽出済みとする）
=FILTER(B2:B & " > " & C2:C & " > " & D2:D, B2:B<>"", C2:C<>"", D2:D<>"")
解説: `&` 演算子で各階層の文字列を結合します。ここでは `FILTER` 関数を使い、3階層目まで存在するURLのみを対象としています。（`ARRAYFORMULA` を使っても同様のことが可能です）
まとめ
Googleスプレッドシートの関数を活用してURLの階層構造を適切に分析・処理することで、以下のようなメリットが得られます。
- サイト構造の問題点の早期発見と改善
- ユーザー導線の最適化やコンテンツ分類の見直し
- 検索エンジンからの評価向上（SEO効果）への寄与
- データに基づいた客観的なサイト改善の実現
補足：状況に応じた関数の使い分け
本記事で紹介した主な関数は、目的に応じて使い分けることが重要です。
- REGEXEXTRACT: 正規表現を使って、URLから特定のパターン（階層、パラメータなど）を「抽出」したい場合に最適です。複雑なルールの抽出も可能です。
- REGEXMATCH: 正規表現を使って、URLが特定のパターン（階層数、特定の文字列を含むかなど）に「一致するかどうか」を確認したい場合（TRUE/FALSE）に使用します。
- REGEXREPLACE: 正規表現を使って、URL内の一部を別の文字列に「置換」したい場合（末尾スラッシュ統一、特殊文字置換など）に使用します。
- FIND: 特定の文字（例: `/`）がURL内の「どの位置にあるか」を知りたい場合に使用します。`MID`関数などと組み合わせて部分文字列を抽出する際に役立ちます。
- QUERY, ARRAYFORMULA, LOWER, IFERROR など: これらを組み合わせることで、より高度で効率的なURLデータの集計、整形、一括処理が可能になります。
これらの機能を理解し組み合わせることで、Googleスプレッドシートを使ったURL分析とサイト改善の可能性が大きく広がります。
コメント