How to Build a Killer Content → Keyword Map for SEO - Whiteboard&nbspFriday

The author's views are entirely his or her own (excluding the unlikely event of hypnosis) and may not always reflect the views of Moz.

You've got content on your site that doesn't intentionally target any keyword. But how do you identify those opportunities and, most importantly, capitalize on them?

In today's Whiteboard Friday, Rand illustrates the process of creating your own content-to-keyword map to discover where to optimize, what content to build, and how to intelligently target keywords when you're auditing a site.

Click on the whiteboard image above to open a high-resolution version in a new tab!

Video Transcription

Howdy, Moz fans, and welcome to another edition of Whiteboard Friday. This week we're going to chat about building a killer content-to-keyword map. Now this is something that pretty much every SEO does when they do an audit of a site, whether that's in-house or as a contractor or an agency consultant.

What we're trying to get here is a picture of: Here are all the keywords that we're trying to target. Here's all the content that we have, and maybe we have keywords for all of that content. Maybe some of that content doesn't have a keyword, those kinds of things. We're trying to map these together so that we know what needs optimization, what new content needs to be built, what keywords need to be targeted to fit with which content, all of these types of things. This is how we identify the priorities and work that an SEO should be doing as they're attacking a new site, attacking a new campaign.

SEO Cartography

So what I've done here is build out a big one, but actually this is not as fully featured as you might imagine some content-to-keyword maps can be. I've seen them with double the number of columns of these, and I'm sure plenty of you who are watching are saying, "Oh man, Rand, I have even more in my map."

Usually this is done in Excel or it's done in Google Spreadsheets. Either one can work fine. Unfortunately, there's no great software to do this right now. You could use a tool like Moz or a tool, if you're on the enterprise side, like Searchmetrics or Conductor to get a lot of this data. You may pull the data from tons of different places, a Screaming Frog here or a Stat over there, whatever it is, OnPage.org.

Columns

But what you're trying to build here is essentially all my keywords mapped to all my URLs. Sometimes you might . . . in fact, if you're doing a comprehensive job, you should find places where you don't have a URL for some keywords because some keywords haven't been targeted yet, but you still want to rank for them. You should probably have some URLs for which you have no keyword. Essentially you haven't intentionally targeted a keyword with that page yet, and this might actually help you prioritize and try and do some of that.

Then you have things like: How much search volume does this get? You're going to try and estimate or use a tool to give you a grade around the title, the content, maybe the URL itself, load speed, and engagement. Engagement could be browse rate or time on site or pages per visit or some combination of all of those things.

You might be looking at internal and external links to the page. Internal links to say, "How well linked to is it internally? Do I have opportunity there?" External links to say, "Am I ranking or not ranking because I don't have external links pointing here?"

I might look at something like a page authority to try and roll those up, Google Desktop and Google Mobile rankings, and the organic visits that that page has received from search engines.

Now, there are a lot more columns that you might consider adding. You could add things like:

Anchor text if you want to analyze your internal and external anchor text.

Google Search Console click-through rates for some of the keywords here and add that data in. We all know Google Search Console, not phenomenal data, but sometimes can be useful.

You might have more keyword-driven metrics if you're trying to prioritize a big keyword research function, like the things in Keyword Explorer:

Keyword difficulty

Click-through rate opportunity

Importance Score, your custom Importance Score, your Potential. You might order these differently based on those kinds of things.

Page level conversion rate. How much does this contribute to content that converts on my site? How well does it convert directly? Those types of things.

Proceed to the route

So now you've got this big content-to-keyword map. "Rand, why am I building this?" Well, look, this map lets you do a bunch of incredibly important, critical things, like:

Identify keywords that have no content mapped to them. Essentially that's saying, "Hey, I better build some content if I want to target this keyword." That's work that you need to do.

Identify on-page opportunities to improve. So you might look at the content or title grade for something. You might go match that up to rankings and you might go, "Wait a minute. I'm ranking number 10. My content grade is only 40 out of 100. Damn, I could improve that real fast. I can make that page a lot better by investing in some on-page optimization."

Or for example, you might say, "Huh, I'm doing pretty well on a bunch of these metrics, but boy, that page load speed is really bad. Look, I can see that in the desktop versus mobile ranking. I'm ranking a lot better on desktop than I am on mobile. Maybe that load speed is one of the reasons why." I could look at other on-page improvements like, "Ooh, man, that low engagement. I bet that low engagement is dragging down my potential for rankings." All right, those two.

Identify content without intentional keyword targeting. So I might find in here that I have something like /flowers, which offers all of my different flower options here, but I have not intentionally chosen what I'm targeting. Am I selling flowers for delivery? Should I be targeting that? Am I selling flowers for greenhouses or for planting in your backyard? Should I be targeting that? What is my keyword here? Shoot, I have not built one in yet. Oftentimes, when you're auditing a site, you will find tons of URLs that are intentionally targeting no specific keyword but should be. All you need to do is some optimization work to help those URLs target the keywords that they should.

Identify link building needs. So I might look in here and I might say, "Huh, my external links, ooh, that's pretty bad, and my rankings kind of reflect that. I need to do some link building. Like, that page is not going to rank. Even though it's doing all right on on-page, it's not going to rank without this.

This whole exercise is designed to help you...

Prioritize and focus your work so that you can do the most important things after you do this audit and you can really move the needle with your SEO.

If you have great ideas or you've done great things inside your content-to-keyword maps, I would love to see them. Please, leave them in the comments. Feel free to link to things. Show off your maps if you feel like uploading them. I would love to see the see columns and the ways that you use this map. Hopefully, in the future, maybe I can convince the Moz Pro software guys to build this for you. ;-)

All right, everyone, take care. We'll see you again next week for another edition of Whiteboard Friday.

1) Do you use this methodology? If not, what do you use to prioritize which SEO work (for specific pages/keywords) to do next?

I do... a lot.

Actually, along with crawling the site and analyzing its log file, this is the third basic analysis I do, and I use it for then elaborating:

Content audit and therefore strategy, where I usually:

Consider how to improve the effectiveness of existing content (if not changing entirely its purposes; for instance changing the nature of the keywords it is targeting from informational to transactional or from top to middle of funnel or bottom of the funnel);

Try to individuate gaps and missed opportunities, so to suggest the creation of content (or even sections) that are missing in order to enlarge the keywords targeted by the site.

On Page Analysis and Optimization.

2) If you do build spreadsheets like this, what tools do you currently use to get the data? How much of it is manual?

Information retrieval is mostly based on tools, many of which you cited in the WBF. I'd add SISTRIX to the tools mix in the discovery phase and SEMRush and Similarweb, these two also for discovering what keywords competitors are targeting, which my site is not.

Another tools I use, then, is URL Profiler, which is great - thanks to its ability to connect to many tools via API - for retrieving URL based metrics.

Finally I consolidate all the collected data in a Excel file for working.

3) What columns or what types of data do you currently use (or what would you love to have in a tool that did this for you if Moz or another software provider built it)?

You listed almost all the columns I use too, both in the spreadsheet and the suggested columns below it.

Of the suggested ones, I would add the "Impressions" metric that GSC offers, because in combination with rankings and CTR, it can tell us if a Search Snippet is not offering the desired results.

Another column I add is the "Level" one, meaning with level in what level of the site the mapped URL is present. This data can be useful especially for understanding if the page is not ranking as well as its maybe good optimization would make us think of, hence considering if a better internal linking from higher levels of the site could give it that boost we are aiming for the URL in the SERPs.

In some cases, I also create a column where I specify what phase of the funnel the keyword and page are targeting (TOFU, MOFU, BOFU)

Finally, I usually create an Excel file with 3 spreadsheets' tabs:

1) All the keywords;

2) Branded keywords;

3) Non Branded keywords.

P.D.: setting reminders in my Calendar for asking you and the Moz Pro product team if you started working on a product like this every now and then :D.

You mention splitting top of funnel, middle of funnel and bottom of funnel in your research. This sounds interesting and I was hoping you could elaborate on this?

I'm familiar with categorizing keywords by 'intent' (e.g. Navigational vs Informational vs Transactional) but how do you decide where in the funnel a keyword belongs? I would guess that Branded terms, Action terms and some long-tail terms sit in the BOFU, but what's your methodology for segregating TOFU and MOFU keywords?

1) Yes, but not to prioritise SEO work. I tend to approach on-page targeting as one big sitewide task, and use methods like the balanced scorecard to prioritise work.

2) Stat (rankings, presence of knowledge box, paid results etc.), SEMRush, Screaming Frog, Majestic/Moz (for competitor and our own page strengths), Keyword Planner, GSC. I don't tend to rely on analytics data, for this, as sometimes that can result in confusing the cause with the symptom.

Depends on the project. Small sites that are never going to be bigger than 10 pages are the trickiest to map and require the cleverest writing. Larger sites get mapped on a spreadsheet, converted into lovely graphic reports for the client. I'd love a MOZ tool that does the bulk of the work.

Such an epic WBF that essentially gives SEOs a great example of a dashboard that everyone should start their day with. I know I'm going to start using this for myself and clients.

1) Do you use this methodology? If not, what do you use to prioritize which SEO work (for specific pages/keywords) to do next?

->I've used splintered parts of this methodology, and coincidentally have been mapping out something similar to this in the past few days - quite the coincidence! The most similar methodology I've used to prioritize SEO work has been looking at the Search Console search analytics keywords to find opportunities such as keywords ranking #11-20 that can get a boost with a little bit of work.

2) If you do build spreadsheets like this, what tools do you currently use to get the data? How much of it is manual?

->Screaming Frog is my go to for pulling a lot of this data for my own site, especially when you connect Search Console and Google Analytics. Then I'll use Moz, Ahrefs, and SEMrush metrics to build out more columns. I've found word count to be a good metric to take a look at as well.

3) What columns or what types of data do you currently use (or what would you love to have in a tool that did this for you if Moz or another software provider built it)?

Word count is a good proxy for how much attention someone has given to the page, although a crude metric as well. Obviously all the standard social and link signals.

One thing that might be hard to build but could be very interesting is what keywords the page is actively trying to rank for. The keywords a page ranks for vs. is trying to rank for are very different. Many pages just happen to be ranking for keywords and aren't even optimized for that. I'd love to know if the top result for a given keyword is actually trying to rank for that, although there would have to be a confidence interval since you'd be analyzing as a 3rd party.

This process will be very, very familiar to content strategists reading this.

Typically, I collaborate with an SEO for the competitive analysis, during which time we outline best-performing keywords and keyword opportunities. For this, the tools are typically Moz Pro, SEMRush and SpyFu or Searchmetrics.

I use this information as an overlay to inform the audience assessment, which informs the content audit.

Thanks to Tori Cushing's awesome tutorials, I've become a lot more facile in Excel and GDocs, so my doc looks a lot like yours. However, other categories I'm adding include content types, frequency, recency, etc. The key here is quick wins. Many brands have no idea what content they have or how well it's performing. Site health/rankings is not thought of in a granular fashion. So, if we start at what the competition is doing, and what we can do better, I can often gain the time needed to work on broader design, UI, UX issues that go well beyond simply "content."

Great template, thanks a lot. I have a really beginners question but I haven't been able to find a solution after researching. Is there a tool that let's me check Page Insights rankings for a list of URLs. That'd save me some time as doing the tests and manually data for Desktop & Mobile ratings would take too long. I don't think that data can be exported using Google Analytics right? Thanks a lot in advance.

I loved this edition of the whiteboard friday Rand! This is something I've been doing for almost a year now, but there are some challenges that I still face. Two main challenges:

All these metrics are good, but you should be tracking them each month to see the real change (growth or decline). Right now I have this super lengthy sheet where I track URLs, keywords, ranks, organic traffic from it and referrals to the main site. But after 12 months of tracking, the sheet is super wide and complicated. Imagine if I added all the metrics you have mentioned (which are obviously very important to track) in my sheet, how much more complicated and long my sheet will become. There has to be a better way.

Our blog is actually really big. We have almost 700 articles at the moment. I cannot track all of them every month. Even if I select the top posts that drive organic traffic or have the potential, that list changes every month. What is a solution to that?

SEOs - If you could give a glimpse or better yet, a snapshot, of how you track all of this data, it would be super useful. And Rand, do try to cover my challenges sometime in a whiteboard friday. I love these kind of episodes because they're so actionable. :)

I love this WBF! I've been a huge advocate of keyword mapping for years.

About a year ago, I spent a couple days building out a template that we use. In its current form, it covers current Google Rank (desktop only, need to add mobile), current high ranking URL, preferred URL, proposed new URL for new content, aggregate search volume for all keywords associated with a URL, average ranking, total keywords associated with URL, and match (whether or not preferred and high ranking URLs match). It's immensely helpful in prioritizing content needs and circling back to prioritize on-page and link needs once content has been created or updated. This serves as the foundation of every SEO campaign we run, I can't imagine how we'd operate without it!

I started doing some whiteboard videos and noticed the camera picks up the reflection of the lights no matter how I position them. Are you using whiteboard paint, and if so which brand are you using? I love that it seems to have some sort of anti-glare / satin finish to it. The lighting is great too, so any tips, or if you've posted about your setup before, would be great to learn more.

Great stuff as always! One thing I like to look at is compare organic visits to organic entry rate (entries/visits) by page.

Pages with lots of visits but low entry rates often means there's a good optimization opportunity because I'm making my visitors work hard to get to where they want (why not make it easier and have them land on that page?). Conversely, high entry rate pages with fewer visits can suggest the page is optimized pretty well and I'm closer to being "maxed out" with that keyword targeting.

1) We use a similar methodology but tend to look at groups/hubs of content and keywords rather than just individual URLs, so big focus on internal links. It's useful to find out how keywords are being targeted across the site and whether there are any possible quick wins from grouping content together, and then easily identify gaps/opportunities for new pages and hubs of content.

2) Most of the tools have already been mentioned, but I'd also add Ahrefs to the list. Their 'Top Pages' section is great for finding out the top keywords that a page is currently targeting, rather than vice versa (handy for competitor audits!)

3) Another mention for Google Search Console, impressions and clicks are a good indication of how content is performing and where improvements are needed.

We use URL Profiler to bulk-collect all the necessary data needed into a monster CSV; pulling in GA and Search Console Query & Page data, as well as things like referring domains, PA, DA, and also readability data.

Once we have this data, we drop it into a Google Sheet and use a variety of conditional formatting, which allows us quickly to highlight areas for improvement (Low position for target KW, high bounce rate, low number of sessions). This saves us a good chunk of manual time that would usually be spent collecting, analysing and interpreting the data.

Regarding manual work, we prefer to spend the majority of our time focusing on creating and prioritising the strategy needed for each URL to get it where it needs to be (Funnel stage, required content level, desired word count, additional assets needed). Doing it in this way also helps to spot where pages can be removed, consolidated, or indeed, created to capture additional targeted traffic based on the user journey.

One thing that we have not quite figured out how to collect, but would be quite neat to have, would be to pull in data for each URL on what the most common previous page destination and most common next page destination is (Similar to a mini-behaviour flow). This would help us to see how URLs work in unison, and whether an ideal user needs to be nudged to a different page.

Would be happy to share a link to one of our Content Strategy Dashboards if you want to take a look? Let me know as it is no trouble.

I use a technique called Ride The Wave technique that I learned from Eric Lancheres that involves figuring out which keywords will give you the biggest increase in traffic (and sales) based on their position in Google. Essentially, you figure out what Google already wants to rank your site for (but isn't) and then it requires a minimal push to get you substantial results. (ie: Using Google console, you can find keywords you're ranking for in position #11... and those are easy to push up to position #5-8)

Essentially, when I first learned about this technique, I went in and applied it... 2 months later my ecommerce store had gone up by 23% in organic traffic! These days, when I'm publishing, I MAKE the news instead of chasing it.

Good tip there Allan! Often do similar to this (but didn't have a name for it!) for quick wins when taking on clients with exisiting sites. I find with a big site, a combination of what you suggest above (low hanging fruit on p2 to 4 is what I aim for) and trimming 'waste pages' with 301 redirects where appropiate, often gets great results quickly (2 to 3 months) and is perfect for showing results fast for new but cautious clients :)

Not only is this a great WBF (simple and concise) but some of the comments are just as educational. Thanks to all!

I feel a little bad admitting that we don't use kw maps.This seems like an even easier way to see what is working and what is not at a glance. I usually create a report or sorts per page using most of the tools mentioned (screaming frog, ahrefs, moz). But this is something I will be implementing ASAP.

First-time SEO here! I've been watching your WBF for a few months now, but this was the first one that actually prompted me to sign up and interact.

I actually work as the director of Content at an SMM company, and have built the entire content dept myself. Of course, since content and SEO are married, I've found myself having to pick up some SEO skills, too. I built a KW map (as it's apparently called) about a month ago, though mine covered way fewer metrics – namely: ranked KW, URL, position, volume, and rank difficulty. I used this to prioritize which KWs we were ranking for, but the pages didn't meet searcher's intent, and which pages might be causing KW cannibalization (much appreciated recent WBF topic). Then I added a column for my own action: by breaking up the low-hanging fruit KWs into the intent in different stages of the Buyer's Journey, I've been using the insights to drive my Content strategy for the past month. And it's worked, I've seen positive growth, etc.

The next problem I'm facing is the same one @Madhav mentioned above: That these metrics are very important, but how do you turn this kind of analysis and action into a constant process? Short of monthly or bi-monthly KW map spot checks, the challenge is creating a process that constantly feeds itself – what's working, what's not, etc, so you can always have a temperature check.

I will be working on tackling this, and checking in with this community. It's awesome!

1) Do you use this methodology? If not, what do you use to prioritize which SEO work (for specific pages/keywords) to do next?

I use this on every campaign. I start with something similar and then expand focusing on content around each topic (e.g. a post on when to plant tulips). Doing so allows me to start seeing the funnel (from content to product/service page to purchase). Let's say tulips have the highest margins and there is little tulip-related content, then I need to develop a content strategy around tulips.

Generally, I prioritize my efforts on 3 criteria (1) most popular product/service, (2) product/service with the highest margins, and (3) ease of ranking. This is how I evaluate what will deliver the best ROI. Alternatively, I sometimes focus on what product/service will get my proverbial foot in the customer's door.

I'd also like to mention your point regarding quick improvements to a page ranking #10 is really important. With a new project it's crucial to focus on a quick win out of the gate. It helps to build trust in the new relationship and gets everyone excited about the work you're doing.

2) If you do build spreadsheets like this, what tools do you currently use to get the data? How much of it is manual?

I do use spreadsheets for this. The tools I use on a regular basis are Moz, SEMRush, AdWords Keyword Tool and Similarweb. A lot of it is manual or semi-manual using a scraper or export function and then combining data into a spreadsheet.

3) What columns or what types of data do you currently use (or what would you love to have in a tool that did this for you if Moz or another software provider built it)?

As mentioned in the WBF, page-specific conversion rate is super helpful in determining a strategy moving forward. If the tool is something one can use online (as opposed to just downloading into a spreadsheet) what would be super cool is the ability to add in your own columns. Then one could include profit margins, average time to close a sale, et cetera.

Is there anyway to determine keyword search volume by category. For example, I am trying to calculate monthly search volume for exact match proper names searches. Trying to do the same thing with cities and states in the US

As Always, this whiteboard Friday is awesome. I use this type of methodology (not exactly the same) but not always because of less time, compare to work. So I make variant keywords of main keywords (approx 5 for each), then I start work on it.

But after listening this WB Friday, I want to implement this methodology in my SEO works to create a great and unique content. And I surely, this technique helps me a lot.

A great WBF as always! As I am a beginner in SEO I pick up things all over the spectrum from complete basics to already somewhere on the way. This post gave me a great overview of where to start when you ave a new project on your hands!

Hi Antonio - you might want to check out https://moz.com/blog/can-seos-stop-worrying-keywor... on that exact topic, but long story short -- semantic searching isn't spreading; searchers are still using keywords. Topics and concepts are becoming more important for SEO and content creators as Google gets more sophisticated, but the keywords searchers use are still absolutely essential to the process, and more advanced text analysis isn't changing that.

We are quite new to this SEO world, i have seen some videos on MOZ academy,, they are really useful. I have one question, does, posting a link in the comment section like this "http://www.iciclesadventuretreks.com/packages/everest-base-camp-trek/" help our site in any ways? Please reply.

Well, regarding this keyword mapping topic, our website has one specific trip page named 'everest base camp trek' with detailed well written original content and information and with same targeted focus keyword. Along with this page we have also written plenty of everest related blogs with different related keywords like ' everest base camp tips/ meals/ packing info etc.' Does these blogs with different related keywords help our main trip page(EBC trek) rank better on Google? Your valuable suggestions would be greatly appreciated.

Wow, this breakout is quite impressive. I'm pretty sure I am implementing some of those strategies for my clients. We see great rankings and returns for the articles / content generated. I'll be sure to reference this video in the future. Thanks for the free content!

Really enjoyed the blending of traditional SEO thinking with the work we're seeing more and more as content strategy, SEO, and other disciplines become more and more integrated. While I personally don't go through this process in this way, I certainly like the approach and appreciate the thinking.

One of the methods we use that is quick(er) for identifying individual URL opportunities is detailed here. We've used this method and different variations of it combining some of the elements you've discussed to help efficiently find opportunities to move the needle--especially early on with clients when showing some success goes a long way to building a solid foundation for a long term relationship.

Thanks Rand, Its really helpful. But as you can see the comments most of us didn't understand how to grade page title and page contents. Is there any tool or we should do it manually and how can we grade page title/contents manually?

Hi Shah - you might check out https://moz.com/tools/onpage-grader and the on-page optimization analysis inside Moz Pro. I'm not actually aware of another great option right now, though I'm sure some do exist.

I have a lot of old content that I wrote without SEO intention. Now I'm reviewing old posts but previously I make a keyword research to optimize every post ant find new post themes. In fact, I'm targetting to at least 4-5 keywords and the results are really great. The method that you have presented has given me ideas to improve my revisions.

Awesome stuff! I am using this approach in my daily SEO work. I am, however, using a smaller version of it where I include the following parameters: Volume, Clicks, CTR & Position. I would love to expand it, but I am working for an agency and I simply lack the time keeping this map updated for every single client :( I have written an article about "how to build up a strategic SEO process in 7 steps". There I included your keyword map and explained it how to use it in the bigger picture of SEO. https://medium.com/@jodr/seo-process-7-steps-of-succesful-search-engine-optimization-23fc15e00b89#.1c76ry696Cheers!

Great WBF Rand as always!, we do document our content via spreadsheets with basics KPI's. But I might me a little off-topic by adding that we also use MediaMetric, but not necessarily for SEO, but content shares so that we can identify the influencers from the different social media platforms.

Thanks. Rand. I've been using a simple keyword map with only 4 parameters: Search Volume, CPC, Copmetition and Page's Position for a keyword.WIth simple formulas and comditional formatting it gives each keywor dthat your page ranks for a score and highlights everything with a corresponding color (green to red) based on whether you should pay attention to that keyword.Here it is: https://docs.google.com/spreadsheets/d/1ecjqotj-5Q...You guys feel free to make a copy and give it a go. You just need to paste data in the first 5 columns and it will do its job.

Now I'm feeling insipred to add more parameters to it and rework the formulas to make it better. :)

Hello Rand, Nice post.. One fact I would like to disclose that when I making the content I don't much use the tools which you being mentioned in the post but after reading your post.. I think I should implement this new method in my content making experience, might be, this can help me to kill off the competitor's content and could take me the #1 position.

Thanks for another great lesson. I usually use this kind of methodology and also add some information about keywords quantity on page content, title, description, headers, etc. Then i usually compare with other sites in top3/top5 to be sure that my clients have sufficient amount of content related to targeted keywords.

This is a great post and timely as I'm putting together for the first time a Google sheet with many of those categories (we're a small shop and SEO is one of my responsibilities). I work for a magazine with very niche content (Buddhism) and we have over 7k articles. As you can imagine many of our phrases or keywords have a very low search volume. Recently we've been prioritizing keyword phrases that we don't have a specific article about and creating 10x content. An example would be a "best of" or "introduction to" article of a popular author—rather than ranking for an archive list of that author's articles which may not give the reader the immediate information they are looking for.

I'm getting to the point now. I'm aware of big competitive keywords in our space and we're working on becoming competitive with those. But how much do I worry about keyword modeling for esoteric phrases for which there is little to no search volume data?

In other words, how deep do I go on my analysis if I have 7,000+ articles that are considered evergreen (teachings, persona stories, not time specific)?

At first when I started SEO content writing, I was far more concerned with keyword desity. Thanx, I am done with that. Now I end up spending time on copy as it is short sighted at its best. The search engines will get the creamiest content to rise on the top.

I use something like this, but a little different. I use something called a KOB (keyword opposition to benefit) Analysis. It uses keyword difficulty scores from Moz and benefit (searches per month * cpc) to rank my keywords in order to target low hanging fruit. I learned this from a course on simplilearn.com that I'm in. The idea is to target keywords with low competition and high benefit.

1) Do you use this methodology? If not, what do you use to prioritize which SEO work (for specific pages/keywords) to do next?

Similar! It depends on the task and the type of on-boarding to some extent (brand new site build with on-going SEO, or picking up the SEO torch on an aged site with thousands on pages). Often with older sites, clients tend to come onboard with a specific request or problem. Or rather, they often approach what they perceive to be the solution ("We've been told we need xyz"), so then there's a bit of a deep-dive into their business & site history and a sanity-check against the request.

So, sometimes a new client's specific request will contribute towards defining priorities for me. That said, even when engaged to tackle a specific issue, I like to try to do a content audit, which includes KW > LP reporting, as I prefer a wholistic approach no matter whether the request is very specific of more general.

2) If you do build spreadsheets like this, what tools do you currently use to get the data? How much of it is manual?

I am in love with the Screaming Frog crawler (It's so awesome I'm actually thinking of getting down on one knee & proposing to the tool, but just waiting for the right moment). Also use Moz/OSE/KW (of course!), sometimes mix in Majestic data, always GA stats & Search Console data. Oh! and Screaming Frog's stand alone log file analyser is handy too for tracking bots.

3) What columns or what types of data do you currently use (or what would you love to have in a tool that did this for you if Moz or another software provider built it)?

I like to record if there's image results/video results etc for the keyword. I find this helps in a similar way to Moz's Opportunity metric, but also for a potential standpoint too, as in, if there's 'video results' for example, how competitive are the video SERPs? Do we/does the client have enough info to create a video better than the one that's there now?

I also add what I call the 'live or die' column, where I simply put: Keep or delete/301 because I LOVE cutting through the trash and slimming non-essential pages down. Less dilution on the site, less crawl budget wasted, less low-engagement pages and more time to focus on the pages that matter.

In terms of what I'd like to see in a tool if it were built:

First off, it'd be an amazing tool!

I'd love to seem the ability to import log files into something like this though.

As mentioned, I like SF's log file analyser, but if a content tool of this nature were able to accept server 'access_log' files, match the URLs in the URL column of the tool and then display the number of Gooblebot visits to the URL...

...over time period the log file covered, that would be great. I think it'd be useful as if the log file covered enough days, it'd then be possible to highlight crawl issues, such as important pages/posts not being crawled often. That said, there may not be enough people who'd like this feature, I might just be a bit odd ;) :D