Slide 1
Slide 1 text
How to categorize
keywords with Power
BI and ChatGPT
Christopher Hofman Laursen
INBOUNDCPH A/S
@hofmanlaursen
Slide 2
Slide 2 text
…
To show you how
to cluster keywords
with the AI tools
right at your
fingertips
– no APIs, no scripts, no coding
Slide 3
Slide 3 text
…
I’m agnostic when
it comes to tools.
It’s about the
output and the
skills to get me
there
Slide 4
Slide 4 text
What is keyword clustering?
Slide 5
Slide 5 text
Making sense of lots of
data points (keywords)
by grouping them (in
clusters) to get insights
to take action
Slide 6
Slide 6 text
The foundation for your website
structure
Danish
wine
Danish
vineyards
Grapes
Tastings
Pairings
with food
Red/White
/Sparkling.
..
Slide 7
Slide 7 text
Breaking categories further down into sub-categories
Slide 8
Slide 8 text
TEASER
How fast can you
cluster 1.000
keywords ?
Slide 9
Slide 9 text
In 2019-20 I consulted with the No. 1 global
brand for weighing instruments
Slide 10
Slide 10 text
MICHAEL JAROSZEWSKI
Head of Global SEO, Mettler Toledo
This is the best
keyword analysis I
have seen in my
20 years of
marketing
Slide 11
Slide 11 text
Each Monday morning 77,000 keyword
rankings are updated
Slide 12
Slide 12 text
However,
clustering
keywords was a
real pain
Slide 13
Slide 13 text
Try to cluster these B2B terms
Slide 14
Slide 14 text
Cluster in Japanese
Slide 15
Slide 15 text
Understand lexical keywords
Truck scale
Weighbridge
Slide 16
Slide 16 text
1.
Vlookups in Excel or Text filters in Power BI
2.
Cluster tools (Keyword Insights, Serpstat, SE ranking)
3.
Python script
The traditional methods to cluster keywords
Slide 17
Slide 17 text
ChatGPT can make me a cluster super hero
Slide 18
Slide 18 text
ChatGPT methods to do keyword clusters
Slide 19
Slide 19 text
The problem is that they are ‘no context’ methods
Slide 20
Slide 20 text
ChatGPT will go in any direction, and even invent
categories such as ‘Spelling errors’
Slide 21
Slide 21 text
33% of keywords categorized
Slide 22
Slide 22 text
IMPORTANT!
When there is no
context, ChatGPT
hallucinates
Slide 23
Slide 23 text
The problem is the
methodology of
‘Keyword
Categorization’
Slide 24
Slide 24 text
The key to not have
ChatGPT
hallucinate…
Slide 25
Slide 25 text
Are you ready?
Slide 26
Slide 26 text
‘Category Keywordization’
Slide 27
Slide 27 text
Context prompting - from 33% to 53% of the
keywords categorized
Slide 28
Slide 28 text
The 3 step Category Keywordization method
Business
context
Category
context
Cluster
keywords
Slide 29
Slide 29 text
1. ‘ChatGPT, ask me 10 strategic questions
about my business’
Slide 30
Slide 30 text
2. ‘ChatGPT, research my site to understand
the current categories’
Slide 31
Slide 31 text
Add any missing
categories
Slide 32
Slide 32 text
Test that ChatGPT understands each category
Slide 33
Slide 33 text
3. Have ChatGPT categorize each keyword in
one or more categories
SITUATION
You are tasked with categorizing a list of 1000 keywords for the Danish online pharmacy ’apopro.dk’. The goal is to enhance the website's SEO and improve user navigation by accurately assigning each
keyword to relevant categories. The keyword list is attached.
UNDERTAKING
Analyze each keyword to understand its meaning, usage, and context, which will inform the appropriate category or categories for assignment. Apply known terms or indicators within keywords to guide
categorization, e.g., ‘receptpligtig’ for Prescription or ‘hudpleje’ for Skin Care. For keywords fitting multiple categories, assign them accordingly to reflect their multifaceted relevance. Document the
reasoning for multi-category assignments for clarity and consistency.
PRESENTATION
Export the categorized list to an Excel sheet. The first column is the keywords, and each subsequent column represents a category. Mark with an X if a keyword belongs to a category. Conduct a final
review to confirm the accuracy and completeness of the categorization, making adjustments as needed. When done create a link to the Excel sheet. The categories are: Prescription, OTC (Over-The-
Counter),Skin Care,Personal Care,Mother & Child,Supplements & Vitamins,Leisure & Health,Symptoms,Diagnostics,Pharmacy Services,Brands,Generic,Brand
EXAMPLE
The keyword ‘hostesaft’ (cough medicine) could fit into multiple categories due to its nature and application. Here's how it could be categorized: OTC (Over-The-Counter): Since cough medicines are
often available without a prescription, this category is a clear fit. It's where customers would look for quick relief options that don't require a doctor's visit. Symptoms: "Hostesaft" directly addresses the
symptom of coughing. Customers searching for relief from specific symptoms like a cough would find ‘hostesaft’ relevant in this category. Mother & Child: Certain cough medicines are formulated
specifically for children, with milder ingredients and flavors to make them more palatable. Thus, ‘hostesaft’ could also be placed in this category when the product is child-friendly. Personal Care: Since
managing a cough is part of personal health maintenance, especially in colder months or allergy seasons, cough medicine could also be considered under personal care products. This example illustrates
how one keyword can span multiple categories, highlighting the importance of flexible categorization to cover the various ways customers might search for or think about a product like cough medicine
on the Apopro.dk website.
RESTRICTIONS
Ensure meticulous and iterative review of each keyword for accurate and comprehensive categorization.Utilize flexibility in assigning multiple categories to a single keyword when necessary. Maintain a
structured approach to document and review categorizations for consistency and clarity.
Slide 34
Slide 34 text
Do a sample test of the first 20 keywords and
then go on with the rest
Slide 35
Slide 35 text
ChatGPT has reduced performance since
January
Slide 36
Slide 36 text
Therefore, we use Claude 3 instead
Slide 37
Slide 37 text
First, have
ChatGPT brief
Claude 3
(Opus) about
your business
Slide 38
Slide 38 text
And then we
run the
prompt
Slide 39
Slide 39 text
Look how fast
Claude 3
categorizes
the keywords
Slide 40
Slide 40 text
As requested some keywords are listed in more
than one category
Slide 41
Slide 41 text
6. Have Claude 3 identify the most logical
category for each keyword
Slide 42
Slide 42 text
7. Run the list
of keywords
Slide 43
Slide 43 text
How fast did I
cluster 1.000
keywords ?
Slide 44
Slide 44 text
5:27 min.!
Slide 45
Slide 45 text
Let’s look at a sub category: Symptoms –
173 keywords
Slide 46
Slide 46 text
Symptoms – According to Claude+ChatGPT
Slide 47
Slide 47 text
Skin problems - the current menu structure
Slide 48
Slide 48 text
Symptoms – Skin problems (suggested structure)
Slide 49
Slide 49 text
How to use categorized
keywords for insights
with Power BI
Slide 50
Slide 50 text
What is Power BI?
•
Power BI is a business analytics tool developed by
Microsoft
•
interactive visualizations and business
intelligence capabilities
•
Users can create reports and dashboards
Slide 51
Slide 51 text
For SEOs Power BI is Excel on steroids
Slide 52
Slide 52 text
The advantage of Power BI when
doing a keyword analysis
✓
It is faster than Excel and doesn’t crash
✓
Merge data much easier than Google Sheets
✓
User friendly drag and drop
✓
No start up costs
✓
”Fresh data” via BigQuery (Advanced stuff)
Slide 53
Slide 53 text
My old method of clustering
Slide 54
Slide 54 text
The new method
Slide 55
Slide 55 text
Drag and drop for insights in a table
Slide 56
Slide 56 text
Drag and drop for insights in a bubble chart
Slide 57
Slide 57 text
The 3 step Category Keywordization method
Business
context
Category
context
Cluster
keywords
Slide 58
Slide 58 text
…
With context
prompting and
iterations you will
get the desired
results with AI
tools right at your
fingertips
– and Power BI will give you the insights to
take action
Slide 59
Slide 59 text
Ready to try
‘Category
Keywordization’?