Content Gap Analysis: Keyword Coverage, Search Coverage and Gap Searches per Cluster with Multi-Criteria COUNTIFS and SUMIFS
Lessons in this course
- Grouping Keywords into Topic Clusters: Keywords per Cluster, Cluster Search Demand, Share of Demand and Searches per KeywordFree
- Search Intent: Labelling Keywords by Modifier, Matching Intent to a Page Type and Measuring Demand per Intent Locked
- Cluster Metrics: Average vs Search-Weighted Difficulty, the Head Keyword with MAXIFS and Its Name with XLOOKUP Locked
- Traffic Potential: Clicks from Search Volume and a Position CTR Curve, Target Positions and the Click Gain of a Cluster Locked
- Pillar and Supporting Pages: Assigning Page Roles with MAXIFS, Counting Supporting Pages and Planning Hub-and-Spoke Links Locked
- Cannibalisation Check: Pages per Query with COUNTIFS, the Split of Clicks, Flags and a Distinct Count of Affected Queries Locked
- Resolving Cannibalisation: Primary URL per Query with MAXIFS and INDEX-MATCH, Primary Share and a Consolidate-or-Differentiate Rule Locked
- Prioritising Clusters: Click Gap, an Opportunity Score from Demand, Value and Difficulty, RANK.EQ and Now / Next / Later Bands Locked
- A Weighted Scoring Model: Min–Max Normalisation, an Inverted Ease Score, SUMPRODUCT Weights and a Ranked Bar Chart Locked
- Content Gap Analysis: Keyword Coverage, Search Coverage and Gap Searches per Cluster with Multi-Criteria COUNTIFS and SUMIFS Locked
- Brief Word Budgets: Median Length of the Top Results, MROUND, an Intent Cap with MIN and the Spread of the Results Page Locked
- Inside the Brief: Splitting a Word Budget Across Sections by Demand, MROUND with a Floor, the Budget Check and a Doughnut Chart Locked
- From Brief to Publish Date: Writing Days with ROUNDUP, Due Dates with WORKDAY, Review Time, Writing Cost and the Cluster Live Date Locked
- Forecasting Cluster Traffic: SLOPE, R², FORECAST.LINEAR for the Next Quarter and the Month the Trend Reaches a Target Locked
- The Business Case for a Cluster: a Ramp-Up Curve, Monthly Gross Profit, an Expanding SUM and the Payback Month Locked
- Quality Check: Auditing the Internal Links of a Cluster with AND, Multi-Criteria COUNTIFS, SUMIFS and a Formula Rule Locked
- Scenario Grid: Monthly Clicks by Coverage and Average CTR with Mixed References, a Heat Map and the CTR a Target Requires Locked
- Content Decay: Change in Clicks, a Refresh / Rewrite / Keep Rule with Nested IF and the Change of the Whole Portfolio Locked
- Brief QA Scorecard: Length Deviation with ABS, Question Coverage, a Capped Link Ratio, a Weighted Score and the Pass Rate Locked
- Final Project: Cluster Roadmap with Weighted Difficulty, Click Gap, Opportunity Score, a Highlighted Winner and a Sorted Chart Locked