SEO Topic Clusters & Content Briefs: Keyword Clustering, Search Intent, Pillar Pages, Cannibalisation Checks, Opportunity Scores & Word Budgets
Turn a raw keyword list into a content plan you can defend. You will group keywords into topic clusters and size their demand, label search intent from modifiers, compare simple and search-weighted difficulty, estimate click potential with a position CTR curve and assign pillar and supporting pages. Then you find cannibalised queries and choose a primary URL, rank clusters with an opportunity score and a weighted scoring model, measure content gaps, set brief word budgets from the top results, split them across sections, schedule writers with WORKDAY, forecast cluster traffic, model ramp-up and payback, audit internal links, build a what-if grid, review decaying pages, score drafts against their briefs and finish with a cluster roadmap. 20 hands-on labs, fictional garden retailer Mossgate Garden Supply, every cell checked as you go.
- One-time payment
- Instant access right after payment
- Secure checkout with Stripe
About this course
This course teaches the planning side of SEO content as a spreadsheet workflow. In 20 interactive labs you work on the keyword exports, search performance reports and content calendars of Mossgate Garden Supply, a fictional online garden shop. You group keywords into topic clusters with COUNTIFS and SUMIFS, label search intent, weigh keyword difficulty by demand, estimate click potential with a CTR curve and decide which keyword gets the pillar page. You detect cannibalisation, pick a primary URL, rank clusters with an opportunity score and a weighted model, and size content gaps. For the briefs you derive word budgets from the top results, split them across sections and schedule writers with WORKDAY. After launch you forecast traffic, model payback, audit internal links, review decaying pages and score drafts against their briefs, before a final cluster roadmap. Every formula is checked as you type. All companies, keywords and numbers are fictional and for illustration only.
Lessons
- 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
Learner reviews
Be the first to review this course.