Every decent SEO knows that Google Search Console is not a web analytics tool, it’s an eavesdropping device.But most practitioners stop at the surface-level metrics: impressions, clicks, position.
Building Simple Dashboards with Free Software
Deep-Dive: Assembling a Keyword Cannibalization Detection Dashboard Using Google Sheets and Looker Studio
Keyword cannibalization is the silent efficiency killer that undermines your site’s authority without triggering an immediate crisis alert. When two or more pages compete for the same query, your ensemble of content becomes an echo chamber of diluted signals, and Google’s ranking algorithm starts shuffling the deck rather than consolidating relevance. Manual audits through CSV exports and pivot tables work, but they are static snapshots of a fluid problem. What you need is a living, breathing dashboard that surfaces cannibalization patterns on a cadence that matches your crawl budget cycle. Fortunately, the entire stack can be built with free software, no paid dashboards required.
Your data pipeline begins with Google Search Console. You already have access to the API—either through the native Search Console add-on for Google Sheets or by writing a simple Apps Script that pulls query‑level performance data for the last 90 days. If you prefer a lighter touch, export the full CSV from the Search Console UI and import it into a dedicated sheet. The critical dimensions are `query`, `page`, `clicks`, `impressions`, and `position`. Do not aggregate at the query level yet; you need the per‑page granularity to detect duplication.
Inside Google Sheets, the heavy lifting happens in a separate “Analysis” tab. Use the `QUERY` function—your SQL‑lite workhorse—to group by query and count the distinct `page` values. Filter to queries where that count is greater than one. To avoid false positives from exact‑match noise, apply a condition that the average position across all pages for that query falls between 5 and 20. Queries ranking outside that band are either already dominating or so far gone that cannibalization isn’t the primary concern. Now calculate the standard deviation of positions across the competing pages; a high variance suggests one page is clearly outranking the others, which is a consolidation opportunity, while low variance indicates a true deadlock.
Normalization is non‑negotiable. Use `REGEXREPLACE` to strip trailing slashes, remove `www`, and standardize protocol strings. Without that step, `/product/` and `/product` will appear as separate pages, and your dashboard will scream about cannibalization that doesn’t exist. Add a helper column that extracts the “path root”—the first directory segment—using `REGEXEXTRACT(A2, “^https?://[^/]+(/[^/])“)`. This groups subpages under a common topic, letting you detect broader cannibalization clusters that span a category.
Now push this data into Looker Studio (formerly Google Data Studio). Connect your Google Sheet as a data source. Build a table with `query` as the dimension, showing the number of competing pages and their average position. Add a scorecard to display the total count of queries that are currently cannibalized. For the visual centerpiece, use a scatter chart with `number of competing pages` on the X‑axis, `average position` on the Y‑axis, and color‑code each point by the standard deviation of positions. This instantly reveals clusters: points in the upper‑left quadrant (few competing pages, high positions) are low‑priority, while points in the lower‑right (many pages, low positions) demand immediate consolidation.
Set up a date‑range parameter in Looker Studio and map it to the raw data column so you can toggle between the last 7, 28, or 90 days. This is critical because cannibalization signals decay over time; a seasonally driven query may show temporary overlap that resolves itself. To keep the dashboard fresh, schedule an Apps Script trigger to run your data pull daily at 2 AM. The script can append new Search Console rows to a history sheet, and Looker Studio will pick up the changes without a manual refresh.
The final touch is a lightweight alert system. Add a calculated field in Looker Studio that flags any query where the number of competing pages jumped by more than one in the last week. Use a conditional filter to highlight those rows in red. You now have a real‑time early warning system that tells you exactly which topics are becoming fragmented before your organic visibility craters.
This entire setup uses zero paid sub‑licenses, no third‑party SEO platforms, and zero vendor lock‑in. It requires a willingness to wrestle with regular expressions and a passing familiarity with the Google ecosystem’s APIs, but the payoff is a bespoke diagnostic tool that adapts to your site’s unique structure. Your SEO stack just developed a custom neural interface to the search engine’s black box—and it’s free.
