SUPERCHARGE YOUR ONLINE VISIBILITY! CONTACT US AND LET’S ACHIEVE EXCELLENCE TOGETHER!
In the competitive digital marketing landscape, search engine optimization (SEO) remains one of the most effective tools for driving organic traffic and enhancing online visibility. And at the core of any successful SEO strategy lies one essential element: data. But here’s the catch — having access to large amounts of raw data isn’t enough. The real advantage comes when you can analyze that data and extract actionable insights. That’s where Google Search Console (GSC) comes into play.
GSC is a goldmine for SEO professionals, content strategists, and digital marketers. It provides a wealth of performance data — including queries, impressions, click-through rates (CTR), and average position — for every keyword for which your website ranks. However, far too often, marketers overlook the opportunity that is already in plain sight: keywords that are already generating some traffic but haven’t been fully optimized yet.
If you dig into GSC data smartly, you’ll start to uncover valuable search terms — ones where you’re already visible in search results but not ranking high enough to see substantial traffic. These are your low-hanging fruits — high-potential keywords that are easier to improve and can deliver fast SEO wins with minimal effort.
In this blog, we will walk you through a step-by-step process to identify these keyword opportunities using three months’ worth of Google Search Console (GSC) data. By following this method, you’ll be able to identify underperforming search terms, filter out branded traffic, and concentrate your efforts on high-impact, non-branded keywords that are well-suited for optimization.
Whether you’re working in-house as an SEO manager or running campaigns for clients, this process will give you an edge by helping you maximize the value of your existing search presence. Why waste time chasing new keywords from scratch when you already have hidden gems within your reach?
Why Keyword Opportunity Research Matters
Ask any seasoned SEO expert, and they’ll tell you — keyword research is the foundation of everything. But traditional keyword tools can only take you so far. They show what people are searching for, but not how you are currently performing. That’s why GSC is so potent — it gives you real-world data directly from Google, showing how your website is ranking for actual queries.
Many websites are sitting on untapped SEO opportunities. These are keywords that:
- You already rank for, but not in the top positions,
- Have decent impressions, suggesting there’s enough search volume,
- Suffer from a low CTR, meaning they’re not enticing enough to earn clicks,
- And aren’t tied to branded search, so you can freely optimize without hurting your brand identity.
Instead of blindly guessing which keywords to target next, this method allows you to focus on what’s already working — but could be working better. It’s about working smarter, not harder, and ensuring every keyword you prioritize is backed by real performance data.
Step 1: Prepare Your Google Search Console (GSC) Data
To get started, you’ll first need to export your performance data from Google Search Console. Here’s how to do it the right way:
- Log in to your GSC account.
- Navigate to the Performance tab and select Search Results.
- In the top-right corner, click “Export” and select the “CSV” option.
- You’ll receive a file named something like: Search-results-YYYY-MM-DD.csv.
Make sure this file includes the following critical columns:
- Query (the actual keyword typed by users),
- Clicks (how many times users clicked through to your site),
- Impressions (how often your site appeared in search results),
- CTR (click-through rate),
- Position (your average ranking position on Google).
This CSV file will serve as the foundation of your keyword analysis. It’s essentially your map of where you’re currently ranking, what’s performing well, and where your growth opportunities lie. Once you have this data in hand, you’re ready to begin the filtering and analysis process that will turn numbers into powerful insights.
Stay tuned as we guide you through the next steps of filtering branded terms, identifying top-performing queries, and uncovering the keywords with the highest potential to boost your organic reach.
Step 2: Open and Run the Colab Script
To make your life easier, we’ll use a ready-to-go Python script hosted on Google Colab.
Here’s the link to the script:
How to Run the Script:
- Click Connect in the top-right corner to start your Colab session.
- Then, either press Runtime > Run All or hit Shift+Enter cell-by-cell.
Before running, the script installs the necessary packages:
!pip install pandas openpyxl
Then it sets up the libraries and prepares for data processing:
import pandas as pd
from google.colab import files
Now, you’re ready to upload your file.
Step 3: Upload Your GSC CSV File
Once the script prompts:
uploaded = files.upload()
Click the “Choose Files” button, and upload your Search-results-YYYY-MM-DD.csv file.
Once uploaded, the script reads the contents of the Excel file:
file_name = list(uploaded.keys())[0]
xls = pd.ExcelFile(file_name)
queries_df = xls.parse(‘Queries’)
Next, it cleans column names to ensure they are properly formatted:
queries_df.columns = [col.strip() for col in queries_df.columns]
Step 4: Remove Branded Keywords
You want to focus on non-branded opportunities. Branded keywords are often already performing well and don’t necessarily offer much room for optimization. So, let’s filter them out.
In our case, we define branded terms like this:
branded_terms = [
‘mojarto’, ‘www.mojarto.com seller’, ‘mojarto login’,
‘mojarto art’, ‘mojarto painting’,’mojarto.com sellers’,
‘mojarto artist login’,’www.mojarto.com selling’,’www mojarto com seller’
]
Then we filter out queries containing these:
queries_df[‘Top queries lowercase’] = queries_df[‘Top queries’].str.lower()
non_branded_df = queries_df[
~queries_df[‘Top queries lowercase’].str.contains(‘|’.join(branded_terms))
].copy()
This gives us a clean dataset of unbranded queries.
Step 5: Add Click Growth
Let’s now compare the number of clicks over the past three months with the previous three months to find trending queries.
non_branded_df[‘Click Growth’] = (
non_branded_df[‘Last 3 months Clicks’] –
non_branded_df[‘Previous 3 months Clicks’]
)
This simple metric helps you spot growing keywords that are gaining traction.
Step 6: Apply Keyword Opportunity Filters
To refine our analysis, we now apply four sets of filters. These filters are designed to surface different types of keyword opportunities based on CTR and average position.
Filter 1 – Broad Opportunity (CTR ≥ 2%, Position 3–30):
This casts a wide net, capturing mid-performing terms with decent impressions.
filter_1 = non_branded_df[
(non_branded_df[‘Last 3 months Impressions’] >= 200) &
(non_branded_df[‘Last 3 months CTR’] >= 0.02) &
(non_branded_df[‘Last 3 months Position’] >= 3) &
(non_branded_df[‘Last 3 months Position’] <= 30)
]
Filter 2 – More Targeted (CTR ≥ 2%, Position 3–10):
This is a focused list of terms that rank well but still have room to improve CTR.
filter_2 = non_branded_df[
(non_branded_df[‘Last 3 months Impressions’] >= 200) &
(non_branded_df[‘Last 3 months CTR’] >= 0.02) &
(non_branded_df[‘Last 3 months Position’] >= 3) &
(non_branded_df[‘Last 3 months Position’] <= 10)
]
Filter 3 – Hot Leads (CTR ≥ 5%, Position 3–10):
These are your ready-to-convert keywords — highly relevant, ranking well, and converting decently.
filter_3 = non_branded_df[
(non_branded_df[‘Last 3 months Impressions’] >= 200) &
(non_branded_df[‘Last 3 months CTR’] >= 0.05) &
(non_branded_df[‘Last 3 months Position’] >= 3) &
(non_branded_df[‘Last 3 months Position’] <= 10)
]
Filter 4 – Duplicate of Filter 2 (Optional):
Some workflows keep a duplicate for labeling purposes or cross-comparison.
filter_4 = filter_2.copy()
Step 7: Sort, Label & Prepare Final Output
Now, let’s sort each filtered dataset based on impressions and CTR, then label them for clarity.
def prepare_output(df, label):
df_sorted = df.sort_values(
by=[‘Last 3 months Impressions’, ‘Last 3 months CTR’],
ascending=[False, False]
).copy()
df_sorted[‘Filter Label’] = label
return df_sorted[[ … ]]
We do this for each filter:
df1 = prepare_output(filter_1, ‘CTR≥2%, Pos 3–30’)
df2 = prepare_output(filter_2, ‘CTR≥2%, Pos 3–10’)
df3 = prepare_output(filter_3, ‘CTR≥5%, Pos 3–10’)
df4 = prepare_output(filter_4, ‘CTR≥2%, Pos 3–10 (Dup)’)
Step 8: Combine All Filtered Keyword Lists
Merge the refined datasets into a single comprehensive list — and remove duplicates:
combined_keywords = pd.concat([df1, df2, df3, df4]).drop_duplicates().reset_index(drop=True)
This final list includes keyword opportunities ranked by impressions and CTR — the sweet spot for SEO optimization.
Step 9: Export the Results
You’ve done the analysis. Now it’s time to download your goldmine of keywords.
combined_keywords.to_excel(“All_Keyword_Opportunities_NonBranded.xlsx”, index=False)
files.download(“All_Keyword_Opportunities_NonBranded.xlsx”)
Your final output file will contain high-potential, non-branded queries that you can now target in future blog posts, landing pages, or product updates.
Bonus: How the Script Works Behind the Scenes
While the Colab script may appear simple on the surface, there’s quite a bit happening under the hood to turn your raw Google Search Console data into a refined list of keyword opportunities. Let’s break it down so you understand exactly what’s going on behind the scenes.
Step 1: Reading Your GSC Export
The script begins by accepting your exported GSC file — typically a .csv or Excel file. It parses the content and reads the important columns like queries, impressions, clicks, CTR (click-through rate), and average position. This forms the foundation for everything that follows.
Step 2: Intelligent Filtering
Raw data often contains noise — especially branded search terms and low-impact keywords. To clean it up and make it actionable, the script filters out:
- Branded keywords (like “mojarto,” “mojarto login,” etc.) to focus only on non-branded, generic queries.
- Queries with fewer than 200 impressions, which likely don’t have enough volume to justify optimization effort.
- Queries with CTR below 2%, indicating weak performance or lack of compelling metadata.
- Queries not ranking between positions 3–30, as these are either already too low to matter or already top performers.
This filtering stage ensures you’re only working with relevant, scalable, and improvable keyword data.
Step 3: Adding Click Growth
One standout feature is the script’s ability to calculate Click Growth — the difference in clicks between the last 3 months and the previous 3 months. This metric highlights which keywords are gaining traction and deserve immediate attention. It’s an excellent way to spot rising trends before your competitors do.
Step 4: Labeling & Combining
The script then applies multiple filters based on CTR thresholds and position ranges to generate four categories of high-potential keywords. Each keyword is labeled with a tag (like “CTR ≥ 2%, Position 3–10”) so you can quickly understand the performance profile. All four filtered datasets are then merged and deduplicated to give you a clean, consolidated keyword list.
Step 5: Exporting to Excel
Finally, the polished output is automatically saved into an easy-to-download Excel file — complete with all relevant metrics and filter labels. It’s ready to use for keyword mapping, on-page SEO, content creation, and reporting.
How We Do it At ThatWare –
Open the Colab Script
Open the script: https://colab.research.google.com/drive/1Zb9yxRDTbTOIgxSmbdlGXMIqCybXYzCO?usp=sharing
Then:
- Click “Connect” in the top-right corner to start a Colab session.
- Click Runtime > Run all (or run each cell manually using Shift+Enter).
# Install required packages
!pip install pandas openpyxl
# Import libraries
import pandas as pd
from google.colab import files
# === Step 1: Upload the GSC Excel file ===
uploaded = files.upload()
file_name = list(uploaded.keys())[0]
# === Step 2: Load the Excel File ===
xls = pd.ExcelFile(file_name)
queries_df = xls.parse(‘Queries’)
# Clean column names
queries_df.columns = [col.strip() for col in queries_df.columns]
# === Step 3: Normalize and Remove Branded Terms ===
# Define branded terms
branded_terms = [
‘mojarto’, ‘www.mojarto.com seller’, ‘mojarto login’,
‘mojarto art’, ‘mojarto painting’,’mojarto.com sellers’,’mojarto artist login’,’www.mojarto.com selling’,’www mojarto com seller’
]
queries_df[‘Top queries lowercase’] = queries_df[‘Top queries’].str.lower()
non_branded_df = queries_df[
~queries_df[‘Top queries lowercase’].str.contains(‘|’.join(branded_terms))
].copy()
# Add Click Growth
non_branded_df[‘Click Growth’] = (
non_branded_df[‘Last 3 months Clicks’] –
non_branded_df[‘Previous 3 months Clicks’]
)
# === Step 4: Apply Multiple Filters ===
# Filter 1: CTR ≥ 2%, Position 3–30
filter_1 = non_branded_df[
(non_branded_df[‘Last 3 months Impressions’] >= 200) &
(non_branded_df[‘Last 3 months CTR’] >= 0.02) &
(non_branded_df[‘Last 3 months Position’] >= 3) &
(non_branded_df[‘Last 3 months Position’] <= 30)
]
# Filter 2: CTR ≥ 2%, Position 3–10
filter_2 = non_branded_df[
(non_branded_df[‘Last 3 months Impressions’] >= 200) &
(non_branded_df[‘Last 3 months CTR’] >= 0.02) &
(non_branded_df[‘Last 3 months Position’] >= 3) &
(non_branded_df[‘Last 3 months Position’] <= 10)
]
# Filter 3: CTR ≥ 5%, Position 3–10
filter_3 = non_branded_df[
(non_branded_df[‘Last 3 months Impressions’] >= 200) &
(non_branded_df[‘Last 3 months CTR’] >= 0.05) &
(non_branded_df[‘Last 3 months Position’] >= 3) &
(non_branded_df[‘Last 3 months Position’] <= 10)
]
# Filter 4: CTR ≥ 2%, Position 3–10 (duplicate of filter_2)
filter_4 = filter_2.copy()
# === Step 5: Combine and Label ===
def prepare_output(df, label):
df_sorted = df.sort_values(
by=[‘Last 3 months Impressions’, ‘Last 3 months CTR’],
ascending=[False, False]
).copy()
df_sorted[‘Filter Label’] = label
return df_sorted[[
‘Top queries’, ‘Last 3 months Clicks’, ‘Previous 3 months Clicks’,
‘Click Growth’, ‘Last 3 months Impressions’,
‘Last 3 months CTR’, ‘Last 3 months Position’, ‘Filter Label’
]]
df1 = prepare_output(filter_1, ‘CTR≥2%, Pos 3–30’)
df2 = prepare_output(filter_2, ‘CTR≥2%, Pos 3–10’)
df3 = prepare_output(filter_3, ‘CTR≥5%, Pos 3–10’)
df4 = prepare_output(filter_4, ‘CTR≥2%, Pos 3–10 (Dup)’)
# === Step 6: Combine All Results ===
combined_keywords = pd.concat([df1, df2, df3, df4]).drop_duplicates().reset_index(drop=True)
# === Step 7: Display Results ===
print(“\n Combined Keyword Opportunities (Non-Branded):\n”)
print(combined_keywords)
# === Step 8: Export to Excel ===
combined_keywords.to_excel(“All_Keyword_Opportunities_NonBranded.xlsx”, index=False)
files.download(“All_Keyword_Opportunities_NonBranded.xlsx”)
Step 3: Upload the GSC CSV File
- The script will prompt: files.upload()
- Upload your Search-results-YYYY-MM-DD.csv
Step 4: What the Script Does
The script will:
- Read the GSC data (Queries, Clicks, Impressions, CTR, Avg. Position)
- Filter out:
- Queries with impressions > threshold (default: 100)
- Queries with CTR < 1%
- Queries ranking in positions 8–20 (low-hanging opportunities)
- Create a list of “Opportunity Keywords” — queries that rank but are not well-optimized.
Step 5: Output & Download
- The script saves results as:
- potential_keywords.csv
- You’ll see a Download link or file icon to download the processed keyword list.
Final Thoughts: Turning Data into SEO Strategy
What makes this keyword research method powerful isn’t just the filtering — it’s the fact that it’s grounded in real performance data. Unlike traditional keyword research tools that rely on general trends, search volume estimates, or third-party data, this approach uses your own audience’s behavior to drive strategic decisions.
That means:
- The keywords in your list are already being searched by real users.
- You’re already ranking for them — just not high enough.
- You’re not starting from zero — you’re building on existing visibility.
And that’s what makes these opportunities so compelling. You’re not casting a wide net in the dark. You’re honing in on what’s working — and making it better.
For SEO consultants, in-house strategists, or content managers, this approach is a game-changer. It creates a repeatable, data-driven process that scales SEO without burning resources on low-return keyword hunting. You move away from chasing new ideas and instead, amplify what’s already blooming.
What Next? Turning Keywords into Action
Having a curated list of non-branded keyword opportunities is just the beginning. The real results come from implementation. Here’s what to do next:
1. Map Keywords to Existing Pages
Start by aligning each keyword with the most relevant page on your site. If a page already covers the topic but isn’t ranking well, that’s your cue to optimize it further.
2. Create New Content
If no page currently addresses a given keyword, it may be time to produce fresh content — blog posts, product pages, landing pages — built specifically around that search intent.
3. On-Page Optimization
Update meta titles, H1 tags, subheadings, and body content to incorporate the target keywords naturally. Pay close attention to search intent and user experience to keep the content compelling.
4. Track and Measure
After implementation, track changes in CTR, impressions, and average position over time. This helps you evaluate whether your optimizations are moving the needle.
Make It a Quarterly Ritual
SEO isn’t a one-and-done game. Search behavior shifts, competitors evolve, and algorithms change. That’s why it’s smart to repeat this keyword discovery process every quarter. With each pass, you’ll uncover fresh opportunities, spot trends early, and refine your strategy to stay ahead in the organic race.By combining automation, real data, and thoughtful execution, this system empowers you to turn GSC data into a living SEO roadmap — always evolving, always improving.
Thatware | Founder & CEO
Tuhin is recognized across the globe for his vision to revolutionize digital transformation industry with the help of cutting-edge technology. He won bronze for India at the Stevie Awards USA as well as winning the India Business Awards, India Technology Award, Top 100 influential tech leaders from Analytics Insights, Clutch Global Front runner in digital marketing, founder of the fastest growing company in Asia by The CEO Magazine and is a TEDx speaker and BrightonSEO speaker.
