How to categorize keywords with Power BI and ChatGPT

Keyword categorization is central when analysing data to find insights. But it does take a lot of time to handle hundreds or thousands of keywords.

In this talk Christopher will show you an alternative method to save time and get better results without being an Excel ninja or buy into expensive tools - the solution? 'Category keywordization' 😎

Transcript

1. How to categorize keywords with Power BI and ChatGPT Christopher Hofman Laursen INBOUNDCPH A/S @hofmanlaursen
2. … To show you how to cluster keywords with the AI tools right at your fingertips – no APIs, no scripts, no coding
3. … I’m agnostic when it comes to tools. It’s about the output and the skills to get me there
4. What is keyword clustering?
5. Making sense of lots of data points (keywords) by grouping them (in clusters) to get insights to take action
6. The foundation for your website structure Danish wine Danish vineyards Grapes Tastings Pairings with food Red/White /Sparkling. ..
7. Breaking categories further down into sub-categories
8. TEASER How fast can you cluster 1.000 keywords ?
9. In 2019-20 I consulted with the No. 1 global brand for weighing instruments
10. MICHAEL JAROSZEWSKI Head of Global SEO, Mettler Toledo This is the best keyword analysis I have seen in my 20 years of marketing
11. Each Monday morning 77,000 keyword rankings are updated
12. However, clustering keywords was a real pain
13. Try to cluster these B2B terms
14. Cluster in Japanese
15. Understand lexical keywords Truck scale Weighbridge
16. 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
17. ChatGPT can make me a cluster super hero
18. ChatGPT methods to do keyword clusters
19. The problem is that they are ‘no context’ methods
20. ChatGPT will go in any direction, and even invent categories such as ‘Spelling errors’
21. 33% of keywords categorized
22. IMPORTANT! When there is no context, ChatGPT hallucinates
23. The problem is the methodology of ‘Keyword Categorization’
24. The key to not have ChatGPT hallucinate…
25. Are you ready?
26. ‘Category Keywordization’
27. Context prompting - from 33% to 53% of the keywords categorized
28. The 3 step Category Keywordization method Business context Category context Cluster keywords
29. 1. ‘ChatGPT, ask me 10 strategic questions about my business’
30. 2. ‘ChatGPT, research my site to understand the current categories’
31. Add any missing categories
32. Test that ChatGPT understands each category
33. 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.
34. Do a sample test of the first 20 keywords and then go on with the rest
35. ChatGPT has reduced performance since January
36. Therefore, we use Claude 3 instead
37. First, have ChatGPT brief Claude 3 (Opus) about your business
38. And then we run the prompt
39. Look how fast Claude 3 categorizes the keywords
40. As requested some keywords are listed in more than one category
41. 6. Have Claude 3 identify the most logical category for each keyword
42. 7. Run the list of keywords
43. How fast did I cluster 1.000 keywords ?
44. 5:27 min.!
45. Let’s look at a sub category: Symptoms – 173 keywords
46. Symptoms – According to Claude+ChatGPT
47. Skin problems - the current menu structure
48. Symptoms – Skin problems (suggested structure)
49. How to use categorized keywords for insights with Power BI
50. 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
51. For SEOs Power BI is Excel on steroids
52. 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)
53. My old method of clustering
54. The new method
55. Drag and drop for insights in a table
56. Drag and drop for insights in a bubble chart
57. The 3 step Category Keywordization method Business context Category context Cluster keywords
58. … 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
59. Ready to try ‘Category Keywordization’?