ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Thank you for attenting my SMX Berlin session!
2
This sheet details each stage of my local keyword research process and includes fornulas and pivot tables I use to aggregate data.
3
ItemTool usedNotes
4
Expanded keyword listGoogle Keyword Planner
5
Initial Volume refinement < 500 monthly searchesGoogle Keyword Planner
6
Initial Volume refinement < 500 monthly searchesaHrefsused as a more precise double-check of GKP volume
7
Intent report SEMrush(used by VLOOKUP in subsequent sheet)
8
Relevance & Intent refinement (terms excluded)SEMrush
9
Localization refinement (organic results)DataforSEOused by COUNTUNIQUE Pivot Table (very few terms in this set appear to be localized beyond Packs)
10
GroupingBardList of terms provided to Bard. Prompt = "Group these 289 terms into topical clusters:"
11
Full keyword list, groupedaHrefs
12
Volume Prioritization analysis by GroupGoogle Sheets Pivot TableSample of prioritization based purely on localized volume
13
Local Keyword Difficulty (sample scrape)DataforSEOGiven time, pull all domains for all keywords; this is a sample of 3-5 head terms within each group.

Note: This includes formula for calculating Pack Position -- columns G,H,I,J
14
Local Keyword Difficulty (domain taxonomy & domain rating)DataforSEO; aHrefsmanual process
15
Domain taxonomy analysisGoogle Sheets Pivot Table
16
Full calculation of group & subgroup priority by keyword groupApply pack position (PP/7) coefficient if time/effort available
17
Site architecture: Internal link dataaHrefs
18
Site architecture: Internal link analysisGoogle Sheets Pivot Table
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100