Valuing Keywords and Mapping Them to Pages

1From the gathered list to the keyword plan

Module 05 ended with a workbook of three tabs. Raw keywords holds one search term per row with a Source column saying where each came from (Search Console, a Syncro ticket, a salesperson, a technician, a client, Google autocomplete, People also ask, Google Trends). Topics holds the ten services. Keyword plan is empty, and it is where the judging happens. Nothing in Raw keywords has been judged yet: a term a technician suggested in passing sits beside one Search Console reported for months, and the sheet cannot tell them apart.

Give Keyword plan these columns: Keyword, Topic, Relevance, Priority, Impressions, Trend, Difficulty, Position, Page features, Opportunity and Page. Each column after Topic is one section of this module, in order.

De-duplication

Copy the Keyword column of Raw keywords into column A of Keyword plan. Then de-duplicate: a duplicate is a row whose words are the same, in the same order, as another row's, once capital letters and spaces at either end are ignored. "Backup and Disaster Recovery Richmond" and "backup and disaster recovery richmond" are one row. In Excel, wrap the column in LOWER and TRIM, paste the values back, then use Data, Remove Duplicates; Google Sheets has the same command under Data, Data cleanup. Write the row count before and after at the top of the sheet.

Near-duplicates stay. "backup and disaster recovery Richmond VA" and "Richmond VA backup and disaster recovery" are two rows, and so are "backup" and "backups", because Google treats each as its own search and Search Console reports each separately. They get the same Topic and, later, the same page. Sections 2 through 5 shorten the sheet.

The Topic column

Fill Topic for every row from the Topics tab: managed IT services, IT support, cybersecurity, backup and disaster recovery, VoIP (voice over internet protocol), Microsoft 365, phishing protection, vulnerability assessment, co-managed IT and IT consulting, plus Brand for searches for our name. A term that fits none of those eleven is either about something Hermetic does not sell, which section 2 handles, or a sign the service list is missing something, which Jeff should hear about before the page map is built.

2Relevance scoring

Relevance is how directly a page on www.hermeticnetworks.com can answer what the searcher typed, and how likely the click is to become a conversation with Hermetic. You score it by hand on a scale of 3, 2 and 1, because no tool knows which services Hermetic sells or which pages exist. The scale comes first, then the backup rows scored with it, then the rule for what to do with the scores.

Here are eight terms from the backup topic, scored.

That is the method: for each row, ask which page on the site, or on the page map, would answer the search, and whether the person typing it wants a provider. Score every row the same way. Then filter the Relevance column to 1, cut those rows, and paste them into a new tab named Later. They are kept because a question with many impressions is a blog post topic, and because a term about a service Hermetic does not sell becomes relevant the day Jeff adds that service.

The objection is that a question like "how often should a small business back up its data" brings a reader who may buy later, so moving it looks like discarding a future client. The Later sheet is where that reader is planned for: the keyword plan ranks pages that produce calls this quarter, and the blog, planned from Later, serves the readers who call next year. Paid tools show a column they also call relevance; theirs measures how often a term appears alongside the parent term you typed in and says nothing about what Hermetic sells, so score this column by hand.

3Priority for business objectives

Two rows can both score 3 for relevance and still be worth different amounts to Hermetic. "backup and disaster recovery services Richmond VA" is typed by someone who wants a company to look after their backups month after month; the conversation leads toward a managed services agreement. "recover files from a dead hard drive Richmond" is typed by someone with one broken disk; it leads toward a one-off repair, if Hermetic takes that work at all. Priority is the score for what the conversation produces. It is set per topic rather than per term, because the ten services differ far more from each other than the terms inside one service do.

The scale is again 3, 2 and 1. A 3 is a service whose search usually ends in a recurring contract. A 2 is a project or an add-on, often sold to a client already under contract or who becomes one through it. A 1 is a one-off job. The table is a provisional order, written so the arithmetic in section 7 has something to use.

4Filtering low-traffic terms

A term nobody types is a page nobody visits, however well it scores on relevance and priority. The usual measure of how often a term is typed is a monthly search volume figure from a paid tool, and this course does not assume one. Without it you have two free measurements, each answering a narrower question than volume does, and a rule for using them together.

Search Console impressions

Google Search Console is Google's free report on the site's own appearances in search. An impression is counted each time a link to the site appears on a results page a searcher loads, clicked or not. The Performance report, in its Queries tab, lists every search the site appeared for over a date range of up to 16 months, with impressions, clicks, click-through rate and average position for each. Impressions exist only for searches the site already appears for. A term with no row in the report has zero impressions, which says the site is absent from that term's results and says nothing about how often the term is typed.

Google Trends relative interest

Google Trends, at trends.google.com, reports interest in a term over time and by region on a scale where 100 is the term's own busiest point in the period you choose. It is a ratio with no counts behind it, so it answers two questions well: which of two phrasings is typed more ("backup and disaster recovery" against "data backup", region set to Virginia), and whether a term has a season. For a service-plus-place term inside one metro it often answers "not enough data", which is itself information: the term is typed too rarely for Google to chart.

The filter

Hide a row when all three are true: zero impressions over 16 months, no Trends data with the region set to Virginia, and no internal source, meaning no ticket, salesperson, technician or client produced it in module 05. Hide with the sheet's filter rather than delete, because a hidden term that gains impressions after launch comes back at the next review.

The exception is deliberate. A service-plus-place term with low numbers stays even when it fails the first two tests, because the Richmond market is small and the few people who type "co-managed IT Richmond VA" this month are the buyers. Low numbers on a provider search mean a short list of buyers, and that list is worth ranking for. The filter is for questions, product names and generic phrases that nobody in Virginia appears to type.

5Breaking high-difficulty terms into reachable ones

Difficulty is how hard it is to reach page one for a term, and you can read it without a tool by looking at who is on page one today. Search "cybersecurity" in a private browser window. Expect a definition from a federal agency, an encyclopedia entry and the pages of companies with thousands of staff. A ten-person MSP in Richmond will not displace them, and the people typing that one word are researchers, journalists and job seekers as often as they are buyers. That is difficulty 3.

The move for a difficulty 3 term is to add words until two things change: the competitors become companies Hermetic can beat, and the page becomes one Hermetic can write with something specific in it. "cybersecurity" becomes "cybersecurity services Richmond VA", and the page one competitors become Richmond MSPs and a local pack. That becomes "cybersecurity for law firms Richmond", and the competitors thin to whichever of those MSPs has written about law firms, while the page gains things a generic page cannot say: client confidentiality rules, the Virginia State Bar's guidance on technology, the practice management software a Richmond firm runs. Each step down loses searchers and gains the chance of being seen by the ones who remain.

The site has four industry pages in its sitemap today. Which industries they cover is recorded in the page map, and the industry terms you write here should match those four first, because a page already exists for each.

Two more kinds of row come out of this step. First, the spelling and word-order variants of each surviving term, each as its own row with the same topic: "cyber security" as two words, "VA" and "Virginia", singular and plural. Search Console reports them separately, so the rows show which phrasing the site is winning. Second, the parent term itself, kept as a hidden row with its difficulty noted. Its impressions tell you whether Google associates any page of ours with the topic at all.

6Current position, read from Search Console

Position is where the site's page sits in the organic results for a term, counting from 1 at the top. The temptation is to search the term in your own browser and count. The result you see is personal: Google adjusts it to your location, search history and signed-in account, so Hermetic's own pages appear higher for anyone who visits them daily. A private window removes the history and leaves the location. Search Console removes both and reports an average over every searcher who saw the page.

Open Search Console, then Performance, then Search results. Set the date range to 16 months, the longest it keeps. Switch on the Average position column, which is off by default. In the Queries tab, filter to a term; the number shown is the average position of the site's topmost result for that term across every impression in the range. With the query filter still on, open the Pages tab, and it names the URL that held the position. That URL goes in the Page column.

Because it is an average, a position of 20 can mean the page sat at 20 all period, or at 3 for searchers in Richmond and at 60 for searchers elsewhere. Set the Country filter to United States; Search Console offers no finer geography, so a Richmond-only position is not available from it. Record the average; the monthly review shows the direction.

For a term with no row in the report, write 101 in Position. 101 means the site is absent for that term. It is a number rather than a blank so the formula in section 7 can run, and so a later rise to 80 registers as progress.

Where two of the site's pages appear for one term, the average is for the topmost of them. Three managed IT pages exist today (/managed-services/, /managed-it-support-richmond-va/ and a third with a 73-character slug), so expect this for the managed IT topic. Write the URL Search Console names; which page survives is decided in the page map.

7The opportunity score

With five columns filled, one number can sort the sheet. The table shows four backup rows with invented values for impressions, difficulty and position, marked Model data, and then the formula that produced the last column. Relevance comes from section 2 and priority from the provisional table in section 3.

Row one: relevance 3 times priority 2 is 6; 6 times 120 impressions is 720. Difficulty 1 times position 14 is 14. 720 divided by 14 is 51.4. Row two: 2 times 2 is 4; 4 times 60 is 240; 2 times 38 is 76; 240 divided by 76 is 3.2. Row three: 2 times 2 is 4; 4 times 40 is 160; 3 times 85 is 255; 160 divided by 255 is 0.6. Row four: 3 times 2 is 6; 6 times 0 impressions is 0; 1 times 101 is 101; 0 divided by 101 is 0.

What was done each time: multiply relevance, priority and impressions, then divide by difficulty times position. Relevance and priority say how much a click is worth; impressions say how many clicks there are to be had; difficulty and position say how far away they are. In the sheet, with the columns in the order from section 1, the formula in the Opportunity cell of row 2 is =(C2*D2*E2)/(G2*H2), filled down. Sort by Topic, then by Opportunity, largest first.

The fourth row shows the score's main limit. A term where the site is absent has position 101 and, by the same fact, zero impressions, so its score is always 0. The score ranks only the searches the site already appears for. The rule for the rest: a row with relevance 3 and position 101 goes onto the page map regardless of score, as a new page or a new section, and the score starts to mean something a few months after the page is live. In the model table, "disaster recovery company Richmond" is such a row, and it is a better target than "cloud backup" despite scoring lower.

The second limit is that the scales are unequal. Position runs from 1 to 101 and relevance from 1 to 3, so position dominates. A reader comfortable with spreadsheets can rescale the columns to equal ranges before multiplying; for ten topics and a few hundred rows, sorting by score within each topic and reading the top five by eye is enough.

8SERP space

A search engine results page (SERP) for "backup and disaster recovery services Richmond VA" holds more than a list of ten links, and what else is on it decides how much the organic click is worth. Open the term in a private window on a phone and write down everything that sits above the first organic result. These are the features that appear for Hermetic's terms.

For a service-plus-place term the usual order is ads, then the local pack, then the first organic result, and on a phone the first organic result is often below the first screen. Only the profile can put Hermetic in the local pack, and whether Hermetic's GBP is claimed is not known yet. So for every service term, page one means two things: a page on the site, and a profile Google trusts enough to show. The keyword plan produces the first; the Local SEO (search engine optimization) module of either plugin course produces the second.

For a question term, a featured snippet or an AI Overview can answer the searcher without a click. A relevance 1 question with a snippet on its results page is a weak bet even at position 1, which is one more reason those rows sit in the Later sheet. Record what you find in the Page features column as short codes: ads, pack, paa, snip, ai, panel. Two rows with the same opportunity score and different features are different bets; the one with less above the organic results wins the tie.

9Rank thresholds

Position 11 and position 4 are seven places apart, and moving each one is a different kind of work. Read the Position column in bands, and let the band say what the next job is.

A page at 11 is one step from being seen, and the step is usually content you control. A page at 4 is seen already, and the next step depends partly on other people: who links to it, who reviews the business, and what Google shows above the organic results that month. Plan the 11s first, because they are the cheapest clicks in the sheet, and plan the 4s as the long work they are.

Clicks concentrate on page one and fall with each position down it. Google publishes no click rate per position, and this course quotes none. Once Search Console is connected, its click-through rate column shows Hermetic's own rate for each term, which is the only rate that matters for this site.

10Grouping by topic and the hand-off to the page map

The last filter is by topic rather than by term. Sort the keyword plan by Topic, then by Opportunity, largest first. For each of the ten services, read five things from its block of rows: the row count, the sum of impressions, the best score, the best position, and how many rows have relevance 3 and position 101. A topic with several relevance 3 rows and no positions is a page that does not exist yet, or exists and is not indexed. A topic whose impressions are spread over three URLs is the duplicate problem the crawl found, seen from Google's side.

Each surviving topic becomes a row in the page map. The page map is the sheet that lists one page per search, with the search it answers, its URL and its status, and it is built in Rank Math module 02, whichever plugin Jeff chooses. The top-scoring service-plus-place term in the topic becomes that row's Primary search. The variants that scored next become its Other searches. The industry terms from section 5 become sections on the page, or their own page when both of the map's tests pass: Hermetic has clients in that industry, and the page can say something the service page cannot. The map enforces one page per primary search; this module chooses the search.

For the backup topic, the row reads: Page, Backup and Disaster Recovery. Primary search, backup and disaster recovery services Richmond VA. Other searches, disaster recovery company Richmond; business data backup Richmond VA; Microsoft 365 backup for small business. Replaces, the three backup URLs the crawl found: /backup-disaster-recovery/, /backup-disaster-recovery-richmond/ and /backup-disaster-recovery-old/. Status, Merge.

A topic whose every row was hidden in section 4 still gets a page if Jeff sells the service, because the navigation needs one and a client checking on us expects one. It gets no page-one plan until evidence arrives, and its row carries the note "no search evidence yet".

Write the method for the other nine topics on a tab named Method: the three scales with their definitions, the filter rule and its exception, the 101 rule, the formula, the position bands and the hand-off. Someone who has not read this module should be able to score the VoIP topic from that tab alone. If they cannot, the tab is missing a definition.

11Do this now

1. Open the keyword workbook from module 05. Copy the Keyword column of Raw keywords into column A of Keyword plan, lowercase and trim it, and run Remove Duplicates. Write the row count before and after at the top of the sheet. Add the columns from section 1.
2. Fill Topic for every row from the Topics tab, plus Brand. Score Relevance on every row with the 3, 2, 1 scale. Filter to 1, cut those rows, and paste them into a tab named Later. Write the count moved.
3. Send Jeff the ten service names and ask him to mark each 3, 2 or 1 for priority. Fill the column from the provisional table in section 3 and shade it until his answer replaces it.
4. If a Search Console property exists and you have access, open Performance, Search results, set the date range to 16 months and Country to United States, switch on Average position, and export the Queries table as a comma-separated values (CSV) file. Paste it into a tab named Data Import and pull Impressions and Position into the plan with VLOOKUP on the keyword, filling 0 and 101 where a term has no match. If no property exists, fill 0 and 101 everywhere and write "Search Console not connected" at the top of the sheet.
5. For the backup topic only: search each row in a private browser window on your phone. Score Difficulty 1, 2 or 3 from who is on page one, and write the features above the first organic result in Page features as codes: ads, pack, paa, snip, ai, panel. Screenshot one results page for the record.
6. Enter the Opportunity formula from section 7 in row 2 and fill it down. Sort by Topic, then by Opportunity, largest first. Write the backup row from section 10 into the page map workbook.
7. Create the Method tab and write the method for the other nine topics in full. Hand the workbook to the other person who maintains the site and ask them to score the VoIP topic from the Method tab without opening this page. Fix whatever they had to ask about.

12Review questions

1. "co-managed IT services Richmond VA" scores relevance 3 and priority 3, and its opportunity score is 0. A colleague says the formula has spoken and the term should wait. Is the colleague right?

   A.Yes: the score combines every column into one number, so a 0 means the term has no value until its numbers change at a later review. B.No: a 0 with those scores means zero impressions and position 101, the site is absent, and a relevance 3 term at 101 goes onto the page map regardless. C.No, the formula has an error: a term with relevance 3 and priority 3 cannot score 0, so check the cell references in the Opportunity column first. D.Yes, for now: the term belongs in the Later sheet until Search Console shows impressions for it, and it comes back into the keyword plan once numbers arrive.
   Answer

   A score of 0 with relevance 3 and priority 3 means impressions are 0 and position is 101: the site is absent for the term. The formula can only rank searches the site already appears for. A relevance 3 term at 101 goes onto the page map regardless of score, as a new page or section, and the score becomes informative a few months after that page is live.

   Show the answer
2. "cybersecurity" has the most impressions of any row in the sheet. Should it be the primary search for the cybersecurity page?

   A.Yes: impressions are the demand evidence this module relies on, and the row with the most of them is the search the page should be written to answer. B.Yes, with the Richmond variants listed as Other searches, since the parent term brings the most searchers and the variants catch the rest. C.No: a difficulty 3 term has nothing to tell us, so hide the row, delete it from the plan and score the Richmond variants in its place. D.No: page one for the single word is agencies and national vendors, so the primary search is the service-plus-place term and the parent stays as a hidden reference row.
   Answer

   Page one for the single word is agencies, encyclopedias and national vendors, which is difficulty 3, and many of the people typing it are researchers and job seekers. The primary search is the service-plus-place term, "cybersecurity services Richmond VA", and the parent stays as a hidden reference row whose impressions show Google already associates a Hermetic page with the topic.

   Show the answer
3. Two backup terms: one at position 11 and one at position 4. The one at 4 has more impressions. Which gets next month's work?

   A.The one at 4: it already has more impressions, so each place it climbs is worth more clicks, and page one is where the clicks are arriving now. B.The one at 11: the step onto page one is content you control, and the move from 4 toward 1 depends on links, reviews and features and takes months. C.Both, split evenly: they are rows in the same topic with real positions, and the sheet sorts by opportunity score rather than by position band. D.Neither: the 4 is already seen and the 11 is page two, so the month goes to a relevance 3 row at 101, which is the band with the most to gain.
   Answer

   Usually the one at 11. Moving from 11 to page one is a step you control: compare the page with the ten above it and add what is missing. Moving from 4 toward 1 depends on links from other sites, reviews and what Google shows above the organic results, and takes months. The position 4 term is already producing clicks; the position 11 term is producing almost none and is closest to starting.

   Show the answer
4. A blog post sits at position 1 for "what is the 3-2-1 backup rule" and gets few clicks. What probably sits on that results page, and what should change in the plan?

   A.Nine competing posts with stronger titles below ours: rewrite the title and description so searchers choose our post, and keep the term in the keyword plan. B.Nothing unusual: position 1 is an average over every impression, so the post sits lower for most searchers, and a private-window check will show the real position. C.A featured snippet or an AI Overview answering above the results: the term is relevance 1 and belongs in Later, and the post's job is to link to the backup page. D.Proof of demand: a post at 1 shows the topic draws searchers, so promote the term to relevance 3 and make it the backup page's primary search.
   Answer

   A featured snippet or an AI Overview is answering the question above the results, and People also ask may take the rest. The term is relevance 1 and belongs in the Later sheet; its position is a monitoring number. The service-page plan is unchanged, and the post's job is to link to the backup page for the reader who does click.

   Show the answer
5. Two rows each have zero impressions and no Google Trends data. "co-managed IT Richmond VA" came from a salesperson; "what is an RPO" came from autocomplete. Which is hidden?

   A.Both: the filter is zero impressions over 16 months plus no Trends data for Virginia, and both rows fail those two tests, so both are hidden until numbers arrive. B.Neither: zero impressions says only that the site is absent for the term and says nothing about how often it is typed, so no row can be hidden without a volume tool. C."co-managed IT Richmond VA": a salesperson's recollection is weaker evidence than Google's own autocomplete, so it is hidden and the RPO question stays. D."what is an RPO": it fails all three tests and is a relevance 1 question anyway, while the co-managed row has an internal source and is a service-plus-place term.
   Answer

   "what is an RPO" is hidden: it fails all three tests, and it is a relevance 1 question anyway. "co-managed IT Richmond VA" stays for two reasons: it has an internal source, which alone defeats the filter, and it is a service-plus-place term, where low numbers mean a short list of buyers. The filter exists for questions and generic phrases; the exception exists for provider searches in a small market.

   Show the answer

13Glossary

Keyword plan

The tab with one row per search term and the columns Topic, Relevance, Priority, Impressions, Trend, Difficulty, Position, Page features, Opportunity and Page.

Later

The tab holding relevance 1 rows: questions and terms about things Hermetic does not sell. The blog is planned from it.

De-duplication

Removing rows whose words are identical in the same order, ignoring capitals and spaces at the ends. Word-order and plural variants stay.

Relevance

How directly a page on the site can answer the search, and whether the searcher wants a provider. Scored 3, 2 or 1 by hand.

Priority

What the conversation from a search produces for Hermetic: a recurring contract (3), a project or add-on (2), or a one-off (1). Set per service, by Jeff.

Impression

One appearance of a link to the site on a results page a searcher loaded, clicked or not. Reported per search in Search Console.

Relative interest

Google Trends' scale for a term over time or by region, where 100 is the term's own busiest point in the period chosen. A ratio with no counts behind it.

Difficulty

How hard page one is to reach for a term, read from who is on page one today. Scored 3, 2 or 1 by hand, or 1 to 100 by a paid tool.

Average position

Search Console's figure for where the site's topmost result sat for a term, averaged over every impression in the date range.

Position 101

The placeholder for a term the site does not appear for. Keeps the formula running and makes a later rise visible.

Opportunity score

Relevance times priority times impressions, divided by difficulty times position. A sorting aid for the searches the site already appears for.

SERP feature

Anything on a results page other than the organic results: sponsored results, the local pack, People also ask, a featured snippet, an AI Overview, a knowledge panel.

Local pack

The map with three business listings shown for a service-plus-place search. Built from Google Business Profile.

Page map

The sheet of one page per search, with its primary search, URL and status, built in module 02 of the Rank Math course. The keyword plan feeds it.

14Sources

• Google Search Console Help, Performance report (Search results): definitions of impressions, clicks, click-through rate and average position, the 16-month data range, and the Country filter.
• Google Search Central, Get started with Search Console: setting up and verifying a property.
• Google Search Central, In-depth guide to how Google Search works: crawling, indexing and serving, and why results vary by location and context.
• Google Search Central, Featured snippets and your website: what a featured snippet is and where it appears.
• Google Search Central, AI features and your website: AI Overviews and how pages appear in them.
• Google Search Central, SEO Starter Guide: writing pages for the words people search.
• Google Trends Help, FAQ about Google Trends data: relative interest, the 0 to 100 scale, and "not enough data".
• Google, Google Business Profile Help: the profile feeds the local pack and is managed at business.google.com.
• Stephan Spencer, Eric Enge and Jessie Stricchiola, The Art of SEO, 4th edition (O'Reilly, early release 2021), chapter 4.
• Site facts from a crawl of www.hermeticnetworks.com on 2026-09-29: the ten services in the navigation, the three managed IT URLs, the three backup URLs, and the four industry pages in page-sitemap.xml.

Documentation read 2026-10-01. Google changes often; where this page and the documentation disagree, the documentation is current.