Building Custom SEO Dashboards with Python and Google Sheets

custom SEO dashboard

Why Custom Dashboards Matter for SEO

Tracking SEO performance can get messy with multiple platforms like Google Search Console, Google Analytics, Ahrefs, and SEMrush. Custom dashboards simplify your workflow by pulling key data into one view, saving time and improving decision-making.

Tools You’ll Need

• Python: For data automation and APIs

• Google Sheets: For easy visualization and sharing

• Google Search Console API

• Google Analytics API

• Optional: Ahrefs or SEMrush API

Step-by-Step: Connect Python to Google Sheets

1. Set Up Google Sheets API

• Go to Google Cloud Console

• Enable the Google Sheets API

• Create credentials and download the credentials.json file

2. Install Python Libraries

pip install gspread oauth2client pandas

3. Authenticate and Connect

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']

creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
client = gspread.authorize(creds)
sheet = client.open('SEO Dashboard').sheet1

Pulling Data from Google Search Console

Use the searchconsole or google-api-python-client library to access keyword rankings, clicks, and impressions. Example:

# Example only – full setup needed for auth and request building
from googleapiclient.discovery import build

service = build('webmasters', 'v3', credentials=your_creds)
response = service.searchanalytics().query(
    siteUrl='https://your-site.com',
    body={
        "startDate": "2024-03-01",
        "endDate": "2024-03-30",
        "dimensions": ["query"],
        "rowLimit": 1000
    }).execute()

Automating Your Dashboard Updates

Once your script works, schedule it to run daily or weekly using:

• macOS/Linux: cron jobs

• Windows: Task Scheduler

• Cloud: Google Cloud Functions or AWS Lambda

Final Touch: Visualize Your Data

Google Sheets allows for charts, pivot tables, and conditional formatting to help you track:

• Keyword movement

• Total traffic

• CTR by page

• Device performance