AhrefsのCSVからキーワードカニバリゼーションを検出する

AhrefsのOrganic keywords CSVから、同一キーワードに複数のURLがランクインしている「カニバリゼーション（共食い）」を自動検出。ディレクトリ別にカニバリ発生キーワード数・URL数・トラフィック合計・平均順位を集計し、URL統合やnoindex等の対策が必要な領域を一目で把握できます。

変換前
KeywordCurrent_URLCurrent_positionCurrent_organic_trafficVolume
project management toolexample.com/tools/pm372014800
project management toolexample.com/blog/best-pm-tools718014800
project management toolexample.com/compare/pm124514800
task management appexample.com/tools/tasks29509900
変換後
KeywordCurrent_URLvolumeurl_countkeyword_url_trafficurl_trafficavg_position
project management toolexample.com/tools/pm1480039457203
project management toolexample.com/blog/best-pm-tools1480039451807
project management toolexample.com/compare/pm1480039454512

CSVファイルをドラッグ＆ドロップ、または ファイルを選択

.csv 形式（最大10ファイル、各500 MBまで）

必要なカラム

KeywordVolumeCurrent_organic_trafficCurrent_positionCurrent_URL

Your data stays in your browser. By using this tool, you agree to our Terms and Privacy Policy.

このテンプレートの使い方

ファイルを選択

必要なカラムを含むCSVファイルをドラッグ＆ドロップ

自動データ処理

テンプレートに沿ってデータを自動変換・集計

結果を確認

Analyzeページで集計結果をインタラクティブに分析

このテンプレートで行われる処理の詳細

  1. 1Current_URLから正規表現でデータを抽出 → 列「directory」
  2. 2KeywordごとにKeywordで順位を付与（ROW_NUMBER）→ 列「url_count」
  3. 3KeywordごとにKeywordで順位を付与（ROW_NUMBER）→ 列「keyword_url_traffic」
  4. 4Keyword, Current_URL でピボット集計
  5. 5集計: 平均(Volume), 平均(url_count), 平均(keyword_url_traffic), 合計(Current_organic_traffic), 平均(Current_position)
  6. 6フィルタ: url_count ≥ 2, Current_URL が空でない