Updated for 2024 by Chima Mmeje

If you’ve ever labored for hours classifying keywords by topic and search intent only to end up with a ton of data you don’t know what to do with, then this post is for you.

I will share how to take all that sweet keyword data you’ve categorized, put it into a Power BI dashboard, and start slicing and dicing to uncover a ton of insights.

Every great search analysis starts with keyword research, and this one is no different. Moz Keyword Explorer offers some incredible features to help with keyword research:

• Ranking Keywords By Site: Start with your site's analytics to find search terms driving clicks and (hopefully) conversions.

• Keyword Suggestions: Discover a wealth of suggested terms, questions, and phrases related to your search terms.

• Keyword Lists: Expand your keywords by adding modifiers and combining terms directly within Moz, offering a simpler way to manage and grow your keyword strategy.

• SERP Analysis: Pull search volume, difficulty, and other vital metrics for expanded keyword Insights.

• Search intent: Classify keywords by their intent —whether informational, transactional, or navigational to understand your user’s needs.

• STAT: Ideal for handling large datasets. Upload your bulk keyword list and use STAT’s robust analytics to pull keyword data and SERP features.

Please note that while these tools are a great way to scale keyword collection, they require you to clean your data to ensure that all keywords are at least somewhat relevant to your business and audience.

Once I have built an initial keyword list, I’ll upload it to STAT and let it run for a few days to get an initial data pull. I can pull the ‘People Also Ask’ and ‘Related Searches’ reports in STAT to further refine my keyword list. I aim to get to at least 5,000 keywords, but the more, the merrier.

For this blog post, I have collected about 19,000 keywords for a client in the "window treatments space."

Bucketing keywords into categories is an age-old challenge for most digital marketers, but it’s a critical step in understanding the distribution of your data.

There are two ways to categorize keywords by topic:

Use Moz’s Keyword Explorer

If you’re doing keyword research, use the robust keyword grouping and filtering capabilities of Moz’s Keyword Explorer. This tool allows you to organize your keywords by categories based on shared lexical similarities or themes identified through Moz’s sophisticated algorithms.

Here’s how:

Start by inputting your initial seed keywords into Moz’s Keyword Explorer. This will generate a list of relevant keywords based on your input

Use the tool’s filtering options to narrow down your keywords based on metrics like search volume, difficulty, or relevancy. This step helps you identify the most impactful keywords for further categorization.

Moz offers the ability to explore keyword suggestions that are lexically similar to your seed keywords. You can group these suggestions by varying degrees of lexical similarity, making it easier to see which keywords naturally cluster together around specific topics or themes.

Once you’ve identified clusters of similar keywords, create and manage the lists within Moz. This is particularly useful for keeping your keyword research organized and focused on distinct categories relevant to your client’s business and audience.

Bucketing keywords into categories is an age-old challenge for most digital marketers but it’s a critical step in understanding the distribution of your data. One of the best ways to segment your keywords is by shared words. If you’re short on AI and machine learning capabilities, look no further than the trusted Ngram analyzer. I love to use this Ngram Tool from guidetodatamining.com — it ain’t much to look at, but it’s fast and trustworthy.

After dropping my 19,000 keywords into the tool and analyzing by unigram (or 1-word phrases), I manually select categories that fit with my client’s business and audience. I also make sure the unigram accounts for a decent amount of keywords (e.g. I wouldn’t pick a unigram that has a count of only 2 keywords).

Using this data, I then create a Category Mapping table and map a unigram, or “trigger word”, to a Category like the following:

You’ll notice that for “curtain” and “drapes” I mapped both to the Curtains category. For my client’s business, they treat these as the same product, and doing this allows me to account for variations in keywords but ultimately group them how I want for this analysis.

Using this method, I create a Trigger Word-Category mapping based on my entire dataset. It’s possible that not every keyword will fall into a category and that’s okay — it likely means that keyword is not relevant or significant enough to be accounted for.

Similar to identifying common topics by which to group your keywords, I’m going to follow a similar process but with the goal of grouping keywords by the intent modifier.

Search intent is the end goal of a person using a search engine. Digital marketers can leverage these terms and modifiers to infer what types of results or actions a consumer is aiming for.

For example, if a person searches for “white blinds near me,” it is safe to infer that this person is looking to buy white blinds as they are looking for a physical location that sells them. In this case, I would classify “near me” as a transactional modifier.

If, however, the person searched “living room blinds ideas,” I would infer their intent is to see images or read blog posts about living room blinds. I might classify this search term as being at the informational stage, where a person is still deciding what products they might be interested in and, therefore, isn’t quite ready to buy yet.

There are two ways you can group keywords by intent using Moz’s Keyword Explorer:

Enter a keyword in Keyword Suggestions, such as “Mountain bike.” Next, click filter at the top and enter “near me” to find transactional near me searches ideal for bike shops with a physical location.

Alternatively, you can filter by search intent and drill down to view all transactional keywords related to “Mountain bike”

I followed the same process as building out categories to build out my intent mapping, and the result is a table of intent triggers and their corresponding intent stage.

There are tons of resources on how to get started with the free tool Power BI, one of which is from Seer's founder Will Reynold’s video series on using Power BI for Digital Marketing. This is a great place to start if you’re new to the tool and its capabilities.

Note: It’s not necessarily about the tool (although Power BI is a super powerful one). It’s more about being able to look at all of this data in one place and pull insights from it at speeds that Excel just won’t give you. If you’re still skeptical about trying a new tool like Power BI at the end of this post, I urge you to get the free download from Microsoft and give it a try.

Power BI’s power comes from linking multiple datasets together based on common “keys." Think back to your Microsoft Access days, and this should all start to sound familiar.

Step 1: Upload your data sources

First, open Power BI and you’ll see a button called “Get Data” in the top ribbon. Click that and then select the data format you want to upload. All of my data for this analysis is in CSV format, so I will select the Text/CSV option for all of my data sources. You have to follow these steps for each data source. Click “Load” for each data source.

Step 2: Clean your data

In the Power BI ribbon menu, click the button called “Edit Queries." This will open the Query Editor, where we will make all of our data transformations.

The main things you’ll want to do in the Query Editor are the following:

• Make sure all data formats make sense (e.g., keywords are formatted as text, numbers are formatted as decimals or whole numbers).

• Rename columns as needed.

• Create a domain column in your Top 20 report based on the URL column.

Close and apply your changes by hitting the "Edit Queries" button, as seen above.

Step 3: Create relationships between data sources

On the left side of Power BI is a vertical bar with icons for different views. Click the third one to see your relationships view.

In this view, we are going to connect all data sources to our ‘Keywords Bridge’ table by clicking and dragging a line from the field ‘Keyword’ in each table and to ‘Keyword’ in the ‘Keywords Bridge’ table (note that for the PPC Data, I have connected ‘Search Term’ as this is the PPC equivalent of a keyword, as we’re using here).

The last thing we need to do for our relationships is double-click on each line to ensure the following options are selected for each so that our dashboard works properly:

• The cardinality is Many to 1

• The relationship is “active”

• The cross filter direction is set to “both”

We are now ready to start building our Intent Dashboard and analyzing our data.

In this section, I’ll walk you through each visual in the Search Intent Dashboard (as seen below):

Top domains by count of keywords

Visual type: Stacked Bar Chart visual

Axis: I’ve nested the URL under Domain so I can drill down to see this same breakdown by URL for a specific Domain

Value: Distinct count of keywords

Legend: Result Types

Filter: Top 10 filter on Domains by count of distinct keywords

Keyword breakdown by result type

Visual type: Donut chart

Legend: Result Types

Value: Count of distinct keywords, shown as a Percent of the grand total

Metric cards

Sum of Distinct MSV

Because the Top 20 report shows each keyword 20 times, we need to create a calculated measure in Power BI to only sum MSV for the unique list of keywords. Use this formula for that calculated measure:

Keywords

This is just a distinct count of keywords

Slicer: PPC Conversions

Visual type: Slicer

Drop your PPC Conversions field into a slicer and set the format to “Between” to get this nifty slider visual.

Tables

Visual type: Table or Matrix (a matrix allows for drilling down similar to a pivot table in Excel)

Values: Here, I have Category or Intent Stage and then the distinct count of keywords.

This dashboard is now a Swiss Army knife of data that allows you to slice and dice to your heart’s content. Below are a couple of examples of how I use this dashboard to uncover opportunities and insights for my clients.

Where are competitors winning?

With this data, we can quickly identify the top competing domains, but what’s more valuable is identifying the competitors for a particular intent stage and category.

I start by filtering to the “Informational” stage since it represents the most keywords in our dataset. I also filter to the top category for this intent stage which is “Blinds.” Looking at my Keyword Count card, I can now see that I’m looking at a subset of 641 keywords.

Note: To filter multiple visuals in Power BI, you need to press and hold the “Ctrl” button each time you click a new visual to maintain all the filters you clicked previously.

The top competing subdomain here is videos.blinds.com with visibility in the top 20 for over 250 keywords, most of which are for video results. I hit ctrl+click on the Video results portion of videos.blinds.com to update the keywords table to only keywords where videos.blinds.com is ranking in the top 20 with a video result.

From all this, I can now say that videos.blinds.com is ranking in the top 20 positions for about 30 percent of keywords that fall into the “Blinds” category and the “Informational” intent stage. I can also see that most of the keywords here start with “how to,” which tells me that most likely, people searching for blinds in an informational stage are looking for how-to instructions and that video may be a desired content format.

Where should I focus my time?

Whether you’re in-house or at an agency, time is always a hit commodity. You can use this dashboard to quickly identify opportunities that you should be prioritizing first — opportunities that can guarantee you’ll deliver bottom-line results.

To find these bottom-line results, we’ll filter our data using the PPC conversions slicer so that it only includes keywords that have converted at least once in our PPC campaigns.

Once I do that, I can see I’m working with a pretty limited set of keywords that have been bucketed into intent stages. However, I can continue by drilling into the “Transactional” intent stage because I want to target queries linked to a possible purchase.

Note: Not every keyword will fall into an intent stage if it doesn’t meet the criteria we set. These keywords will still appear in the data, but this is the reason why your total keyword count might not always match the total keyword count in the intent stages or category tables.

From there, I want to focus on those “Transactional” keywords that are triggering answer boxes to make sure I have good visibility since they are converting for me on PPC. To do that, I filter to only show keywords triggering answer boxes. Based on these filters, I can look at my keyword table and see most (if not all) of the keywords are “installation” keywords, and I don’t see my client’s domain in the top list of competitors. This is now an area of focus for me to start driving organic conversions.

I’ve only just scratched the surface — there’s tons that can be done with this data inside a tool like Power BI. Having a solid data set of keywords and visuals that I can revisit repeatedly for a client and continuously pull out opportunities to help fuel our strategy is, for me, invaluable. I can work efficiently without having to go back to keyword tools whenever I need an idea. Hopefully, you find this makes building an intent-based strategy more efficient and sound for your business or clients.