Detect Keyword Cannibalization from Ahrefs CSV

Auto-detect keyword cannibalization (multiple URLs ranking for the same keyword) from Ahrefs Organic keywords CSV. Aggregate cannibalized keyword count, URL count, total traffic, and average position by directory to identify areas needing URL consolidation.

Before
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
After
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

Drag & drop CSV files here, or browse files

.csv format (up to 10 files, 500 MB each)

Required Columns

KeywordVolumeCurrent_organic_trafficCurrent_positionCurrent_URL

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

How to Use This Template

Select Your File

Drag & drop a CSV file containing the required columns

Automatic Processing

Data is transformed and aggregated based on the template

View Results

Explore the aggregated results interactively on the Analyze page

Processing Details of This Template

  1. 1Extract from Current_URL via regex → column "directory"
  2. 2Rank by Keyword per Keyword (ROW_NUMBER) → column "url_count"
  3. 3Rank by Keyword per Keyword (ROW_NUMBER) → column "keyword_url_traffic"
  4. 4Pivot by Keyword, Current_URL
  5. 5Metrics: Avg(Volume), Avg(url_count), Avg(keyword_url_traffic), Sum(Current_organic_traffic), Avg(Current_position)
  6. 6Filters: url_count >= 2, Current_URL is not empty