Identify High-Value Keywords with BigQuery

Using BigQuery and Looker Studio to Identify Keywords Close to Ranking Improvements

Identifying keywords close to ranking improvements helps SEO specialists focus on search terms with high potential for reaching top positions. Using Google Search Console, BigQuery, and Looker Studio, you can analyze keyword rankings and identify terms that are on the cusp of ranking higher with targeted optimization. This section guides you through using BigQuery and Looker Studio to track SEO rank improvements, allowing you to find and optimize for keywords that could bring in more traffic if their ranking improves.

1. Why Track Keywords Close to Ranking Improvements?

Targeted Optimization: Identifying keywords close to top positions (e.g., ranking on page 2 or positions 5-10 on page 1) lets you focus on high-impact optimizations.
Boosted Traffic Potential: Moving keywords from lower positions to top positions can significantly increase visibility and click-through rates.
Efficient Resource Allocation: Concentrating on near-top keywords allows you to achieve quick wins with relatively minimal effort.

2. Querying Keywords by Ranking in BigQuery

In BigQuery, you can set up a query to retrieve keywords with average positions just below top rankings. This will help you focus on keywords with potential for quick improvement.

Step 1: Query Your Google Search Console Data in BigQuery
- Open BigQuery, and create a new query in your dataset containing Google Search Console data (e.g., gsc_data).
Step 2: Define Parameters for Keywords Close to Ranking Improvements
- Use conditions to filter keywords with average positions between 5 and 20 (or your desired range). For example:
```
SELECT
  query,
  page,
  AVG(position) AS avg_position,
  SUM(clicks) AS total_clicks,
  SUM(impressions) AS total_impressions,
  (SUM(clicks) / NULLIF(SUM(impressions), 0)) * 100 AS ctr_percentage
FROM
  `your_project.your_dataset.gsc_data`
WHERE
  position BETWEEN 5 AND 20
GROUP BY
  query, page
ORDER BY
  avg_position ASC;
```
- This query provides a list of keywords with average positions close to potential top spots. You’ll also see click-through rate (CTR) and impressions, which can help prioritize keywords with high visibility.
Step 3: Review and Adjust Position Ranges for Relevance
- Modify the position range as needed. For example, you might want to focus on keywords in positions 11-15 if your goal is to move them to page 1.

3. Using Looker Studio to Visualize Keywords Close to Ranking Improvements

Visualizing this data in Looker Studio helps you track and prioritize keywords for ongoing optimization efforts.

Step 1: Connect BigQuery Data to Looker Studio
- Open Looker Studio, create a new report, and add BigQuery as your data source, selecting the table with the keyword ranking data.
Step 2: Create a Table to Display Keywords by Average Position
- Add a table visualization to your report. Include fields such as query, avg_position, total_clicks, total_impressions, and ctr_percentage.
- Sort the table by avg_position to prioritize keywords closest to the top rankings.
Step 3: Add Filters for Position and Clicks
- Add a filter control for position range (e.g., 5-20) to allow you to adjust the scope of keywords considered “close to ranking improvements.”
- Add a filter for total_clicks to focus on keywords already receiving engagement, highlighting those that could benefit most from a ranking boost.

Example: Filter for keywords with positions 5-15 and at least 100 clicks, helping you target high-potential keywords with existing visibility.

4. Prioritizing Keywords Based on CTR and Impressions

Click-through rate (CTR) and impressions indicate how much potential traffic a keyword has. High impressions with a low position suggest high potential for gains if the ranking improves.

Step 1: Sort Keywords by CTR and Impressions
- In Looker Studio, sort the table by ctr_percentage or total_impressions to see keywords with high visibility. Keywords with high impressions but low positions are strong candidates for optimization.
Step 2: Identify Keywords with High CTR but Low Position
- Keywords with good CTR but lower average positions may respond well to targeted optimization, such as on-page improvements or meta description adjustments.

Example: A keyword with 10,000 impressions, 3% CTR, and an average position of 10 is a strong candidate for optimization, as improved ranking could significantly increase clicks.

5. Creating a Dashboard to Track Ranking Improvements Over Time

A Looker Studio dashboard lets you track keywords over time, helping you monitor ranking improvements and refine your optimization strategy.

Step 1: Add a Date Range Control
- Add a date range control to your dashboard to view keyword data for specific time periods. This helps you track performance changes after implementing optimizations.
Step 2: Add Trend Charts for Key Metrics
- Create line charts to track trends in avg_position, ctr_percentage, and total_clicks for your high-priority keywords. These charts provide a visual representation of how rankings change over time.
Step 3: Highlight Top Opportunities
- Use scorecards to display total impressions, average position, and CTR for your most valuable keywords. This summary gives you a quick overview of the keywords that are closest to ranking improvements.

Example: A dashboard with monthly views of position trends and CTR changes for target keywords lets you see the impact of your SEO efforts.

6. Optimizing Pages for Keywords Close to Ranking Improvements

Once you’ve identified priority keywords, implement targeted optimizations on associated pages to improve rankings.

Step 1: Review On-Page Optimization for Targeted Keywords
- Ensure that keywords are included in key areas like titles, headers, and meta descriptions. Make sure content is relevant and provides high value to searchers.
Step 2: Enhance Content and Internal Links
- Improve the content quality on pages that rank close to the top, ensuring it thoroughly covers the topic and addresses user intent. Add internal links from other relevant pages to boost authority for target keywords.
Step 3: Monitor Results and Adjust as Needed
- Use the Looker Studio dashboard to track changes in average position and CTR for optimized keywords. Adjust your approach based on these metrics to continually improve rankings.

Example: For a keyword ranking in position 8, enhance the page’s meta description and add a few relevant internal links. Track CTR and position over time to gauge the effect of your optimizations.

Summary

Using BigQuery and Looker Studio to track keywords close to ranking improvements enables SEO professionals to focus on high-impact optimizations that drive traffic. By setting up targeted queries, visualizing data, and implementing strategic optimizations, you can efficiently improve rankings for keywords on the cusp of top positions. This approach maximizes the return on SEO efforts, helping you boost visibility and click-through rates where it matters most.

Published 11 Nov 2024