Posted by scott.taft
We’ve been talking a lot about search intent this
week, and if you’ve been following along, you’re likely already
aware of how “search intent” is essential for a robust SEO
strategy. If, however, you’ve ever laboured for hours classifying
keywords by topic and search intent, only to end up with a ton of
data you don’t really know what to do with, then this post is for
I’m going to 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 insights — faster than you
ever could before.
Every great search analysis starts with keyword research and
this one is no different. I’m not going to go into excruciating
detail about how to build your keyword list. However, I will
mention a few of my favorite tools that I’m sure most of you are
Search Query Report — What better place to
look first than the search terms already driving clicks and
(hopefully) conversions to your site.
Public — Great for pulling a ton of suggested terms,
questions and phrases related to a single search term.
Like Answer The Public, but faster and allows you to build based on
a continuous list of seed keywords.
MergeWords — Quickly expand
your keywords by adding modifiers upon modifiers.
— A suite of keyword tools for expanding, pulling search volume
Please note that these tools are a great way to scale your
keyword collecting but each will come with the need to comb through
and clean your data to ensure all keywords are at least somewhat
relevant to your business and audience.
Once I have an initial keyword list built, I’ll upload it to
STAT and let it run for a couple days to get an initial data pull.
This allows me to pull the ‘People Also Ask’ and ‘Related
Searches’ reports in STAT to further build out my keyword list.
All in all, I’m aiming to get to at least 5,000 keywords, but the
more the merrier.
For the purposes of this blog post I have about 19,000 keywords
I collected 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. 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 a trusty 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
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
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 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 on the topic of living room blinds. I
might classify this search term as being at the “Inspirational”
stage, where a person is still deciding what products they might be
interested and, therefore, isn’t quite ready to buy yet.
There is a lot of research on some generally accepted intent
modifiers in search and I don’t intent to reinvent the wheel.
handy guide (originally published in STAT) provides a good
review of intent modifiers you can start with.
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 own 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
Note: it’s not about the tool necessarily (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
which Excel just won’t give you. If you’re still skeptical of
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
- Make sure all data formats make sense (e.g. keywords are
formatted as text, numbers are formatted as decimals or whole
- Rename columns as needed.
- Create a domain column in your Top 20 report based on the URL
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
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 URL under Domain so I can
drill down to see this same breakdown by URL for a specific
Value: Distinct count of keywords
Legend: Result Types
Filter: Top 10 filter on Domains by count of
Keyword breakdown by result type
Visual type: Donut chart
Legend: Result Types
Value: Count of distinct keywords, shown as
Percent of grand total
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
Sum Distinct MSV = SUMX(DISTINCT('Table'[Keywords]), FIRSTNONBLANK('Table'[MSV], 0))
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.
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
examples of how I use this dashboard to pull out opportunities and
insights for my clients.
Where are competitors winning?
With this data we can quickly see who the top competing domains
are, but what’s more valuable is seeing who the competitors are
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
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
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
To find these bottom-line results, we’re going to filter our
data using the PPC conversions slicer so that our data only
includes keywords that have converted at least once in our PPC
Once I do that, I can see I’m working with a pretty limited
set of keywords that have been bucketed into intent stages, but I
can continue by drilling into the “Transactional” intent stage
because I want to target queries that are linked to a possible
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
I’ve only just scratched the surface — there’s tons that
can 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
Sign up for The Moz Top
10, a semimonthly mailer updating you on the top ten hottest
pieces of SEO news, tips, and rad links uncovered by the Moz team.
Think of it as your exclusive digest of stuff you don’t have time
to hunt down but want to read!