Tutorial: Building a Keyword Research Dashboard with Google Search Console Data

Build a comprehensive keyword research dashboard using your own Google Search Console data to discover opportunities and track performance.

Prerequisites

Step 1: Connect to Search Console API

from google.oauth2 import service_account
from googleapiclient.discovery import build
import pandas as pd

# Set up credentials
credentials = service_account.Credentials.from_service_account_file(
    'credentials.json',
    scopes=['https://www.googleapis.com/auth/webmasters.readonly']
)

# Build service
service = build('searchconsole', 'v1', credentials=credentials)

def query_search_console(site_url, start_date, end_date, dimensions=['query']):
    request = {
        'startDate': start_date,
        'endDate': end_date,
        'dimensions': dimensions,
        'rowLimit': 25000,
    }
    response = service.searchanalytics().query(
        siteUrl=site_url, body=request
    ).execute()
    return pd.DataFrame(response.get('rows', []))

Step 2: Extract Comprehensive Keyword Data

# Get multi-dimensional data
df_query_page = query_search_console(
    'https://your-site.com',
    '2025-01-01', '2025-06-01',
    dimensions=['query', 'page']
)

df_query_country = query_search_console(
    'https://your-site.com',
    '2025-01-01', '2025-06-01',
    dimensions=['query', 'country']
)

df_query_device = query_search_console(
    'https://your-site.com',
    '2025-01-01', '2025-06-01',
    dimensions=['query', 'device']
)

Step 3: Identify Opportunity Keywords

# Find keywords with high impressions but low CTR
opportunities = df_query_page[
    (df_query_page['impressions'] > 100) &
    (df_query_page['ctr'] < 0.05) &
    (df_query_page['position'] > 5) &
    (df_query_page['position'] < 20)
].sort_values('impressions', ascending=False)

print(f"Found {len(opportunities)} opportunity keywords")
print(opportunities.head(20))

Step 4: Keyword Clustering

from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.cluster import KMeans

# Cluster keywords by semantic similarity
vectorizer = TfidfVectorizer(stop_words='english')
X = vectorizer.fit_transform(opportunities['keys'].apply(lambda x: x[0]))

# Determine optimal clusters
kmeans = KMeans(n_clusters=10, random_state=42)
opportunities['cluster'] = kmeans.fit_predict(X)

# View cluster themes
for cluster_id in range(10):
    cluster_keywords = opportunities[opportunities['cluster'] == cluster_id]
    print(f"\nCluster {cluster_id}:")
    print(cluster_keywords['keys'].apply(lambda x: x[0]).head(5).tolist())

Step 5: Build the Dashboard

# Create summary metrics
dashboard_data = {
    'total_keywords': len(df_query_page),
    'top_3_keywords': len(df_query_page[df_query_page['position'] <= 3]),
    'page_2_keywords': len(df_query_page[
        (df_query_page['position'] > 10) &
        (df_query_page['position'] <= 20)
    ]),
    'opportunity_keywords': len(opportunities),
    'total_impressions': df_query_page['impressions'].sum(),
    'total_clicks': df_query_page['clicks'].sum(),
    'average_ctr': df_query_page['ctr'].mean(),
}

# Export to CSV for visualization
opportunities.to_csv('keyword_opportunities.csv', index=False)
dashboard_data_df = pd.DataFrame([dashboard_data])
dashboard_data_df.to_csv('dashboard_summary.csv', index=False)

Step 6: Automated Reporting

# Set up weekly report generation
def generate_weekly_report():
    end_date = datetime.now().strftime('%Y-%m-%d')
    start_date = (datetime.now() - timedelta(days=7)).strftime('%Y-%m-%d')
    
    df = query_search_console(
        'https://your-site.com',
        start_date, end_date,
        dimensions=['query']
    )
    
    report = {
        'new_ranking_keywords': find_new_keywords(df),
        'improved_positions': find_improved_keywords(df),
        'declined_positions': find_declined_keywords(df),
        'top_opportunities': opportunities.head(10),
    }
    
    return report

Dashboard Checklist