Webサイトのデータ分析やSEO施策において、URLの階層構造を理解し分析することは非常に重要です。適切な階層構造は、ユーザビリティの向上とSEO効果の最適化に直接影響を与えます。
本記事では、Googleスプレッドシートを使用してURLの各階層を効率的に抽出・分析する方法を、実践的な例を交えて解説します。
REGEXEXTRACT関数によるURLの階層取得
REGEXEXTRACT関数は、正規表現を使用してURLから必要な部分のみを抽出できる便利な関数です。
以下、各階層の取得方法を順番に説明します。
1階層目の抽出
=IFERROR(REGEXEXTRACT(A1, "^https?://([^/]+)"), "ドメインが存在しません")
※ A1にURLが入力されている前提です。
2階層目の抽出
=IFERROR(REGEXEXTRACT(A1,"^https?://[^/]+/([^/]+)"),"階層が存在しません")
3階層目の抽出
=IFERROR(REGEXEXTRACT(A1,"^https?://[^/]+/[^/]+/([^/]+)"),"階層が存在しません")
4階層目の抽出
=IFERROR(REGEXEXTRACT(A1,"^https?://[^/]+/[^/]+/[^/]+/([^/]+)"),"階層が存在しません")
5階層目の抽出
=IFERROR(REGEXEXTRACT(A1,"^https?://[^/]+/[^/]+/[^/]+/[^/]+/([^/]+)"),"階層が存在しません")
6階層目の抽出
=IFERROR(REGEXEXTRACT(A1,"^https?://[^/]+/[^/]+/[^/]+/[^/]+/[^/]+/([^/]+)"),"階層が存在しません")
7階層目の抽出
=IFERROR(REGEXEXTRACT(A1,"^https?://[^/]+/[^/]+/[^/]+/[^/]+/[^/]+/[^/]+/([^/]+)"),"階層が存在しません")
FIND関数によるURLの階層取得
FIND関数は文字列内の特定の文字位置を検索する際に使用します。
REGEXEXTRACT関数と比べてシンプルですが、より直感的に理解しやすい方法です。
=FIND("/", A1, FIND("/", A1, FIND("/", A1) + 1) + 1)
文字列内の特定の文字位置を検索します。階層の区切り文字「/」の位置を特定するのに使用します。
REGEXMATCH関数による特定階層の一致確認
URLが特定の階層パターンと一致するかを確認する際に使用します。
1階層目のURLにマッチ
=REGEXMATCH(A1, "^https?://[^/]+/?$")
2階層目のURLにマッチ
=REGEXMATCH(A1, "^https?://[^/]+/[^/]+/?$")
3階層目のURLにマッチ
=REGEXMATCH(A1, "^https?://[^/]+/[^/]+/[^/]+/?$")
4階層目のURLにマッチ
=REGEXMATCH(A1, "^https?://[^/]+/[^/]+/[^/]+/[^/]+/?$")
5階層目のURLにマッチ
=REGEXMATCH(A1, "^https?://[^/]+/[^/]+/[^/]+/[^/]+/[^/]+/?$")
6階層目のURLにマッチ
=REGEXMATCH(A1, "^https?://[^/]+/[^/]+/[^/]+/[^/]+/[^/]+/[^/]+/?$")
URLデータ処理でよくあるトラブルと解決方法
URLクエリパラメータの除去方法
=REGEXEXTRACT(A1, "^([^?]+)")
解説：アクセス解析やSEO分析時に不要なクエリパラメータを除外し、基本URLのみを抽出できます。
スプレッドシートでのURL表記揺れの統一化
末尾スラッシュの一括統一
=REGEXREPLACE(A1, "/?$", "/")
解説：大量のURLデータを扱う際の表記揺れを防ぎ、正確な分析を可能にします。
URLの大文字小文字の統一化
=LOWER(REGEXEXTRACT(A1, "^https?://[^/]+/([^/]+)"))
解説：同一URLの大文字小文字違いによる重複を防ぎ、正確なデータ集計を実現します。
特殊文字を含むURLの処理方法
=REGEXREPLACE(A1, "%20", " ")
解説：URLエンコードされた特殊文字を適切に処理し、可読性の高いデータに変換します。
複数URLの一括処理テクニック
=ARRAYFORMULA(REGEXEXTRACT(A2:A1000, "^https?://([^/]+)"))
解説：大量のURLデータを効率的に処理する方法を提供し、作業時間を大幅に削減できます。
実践的な活用方法
カテゴリー別のURL分析
=QUERY({B2:B1000}, "SELECT Col1, COUNT(Col1) GROUP BY Col1 ORDER BY COUNT(Col1) DESC")
パンくずリスト生成
=B2 & " > " & C2 & " > " & D2
まとめ
URLの階層構造を適切に分析することで得られるメリット：
- サイト構造の問題点の早期発見と改善
- ユーザー導線の最適化
- 検索エンジンからの評価向上
- データドリブンなサイト改善の実現
補足：状況に応じた関数の使い分け
- REGEXEXTRACT：複雑なパターンの抽出に最適
- REGEXMATCH：パターンの一致確認に使用
- FIND：シンプルな文字列操作に適している
これらの機能を組み合わせることで、効率的なURL分析と改善が可能になります。
コメント