Tabellab
Marketing Analytics Intermediate

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.

20 lessons 20 labs Certificate

  • 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

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

Learner reviews

Be the first to review this course.

Write a review

Your rating

Reviews are checked before they appear. By submitting you confirm the images are yours and may be shown on this page. Privacy