Search Console Report

Generate comprehensive SEO analysis reports from Google Search Console data with PDF export. Use when the user wants to analyze search performance, get SEO insights, view traffic trends, top pages, top keywords, country/device distribution, or generate a professional PDF report for one or more websites using Google Search Console API. Requires a Google Cloud Service Account JSON key with Search Console read access.

Search Console SEO Report Generator

Generate professional, chart-rich PDF reports from Google Search Console data. Covers traffic trends, top pages, top keywords, country/device distribution, growth analysis, and actionable SEO recommendations.

Prerequisites

Before running this skill, verify these requirements:

1. Service Account Key File

You need a Google Cloud Service Account JSON key file with access to the Search Console properties. The file looks like:

json

{
  "type": "service_account",
  "project_id": "...",
  "private_key_id": "...",
  "private_key": "-----BEGIN PRIVATE KEY-----\n...",
  "client_email": "[e-mail]",
  "token_uri": "https://oauth2.googleapis.com/token",
  ...
}

Ask the user for the path to their key file. Common locations: ~/Downloads/*.json, project directory.

If the user doesn't have one yet, guide them through:

1. Create a Service Account in Google Cloud Console (IAM & Admin > Service Accounts)
2. Create a JSON key for it (Keys tab > Add Key > JSON)
3. Add the service account email as a user in Search Console (Settings > Users and permissions > Add user, "Restricted" permission is sufficient)
4. Enable the "Google Search Console API" in the project's API Library

2. Python Environment

The script requires these packages: pyjwt, cryptography, requests, matplotlib, pandas, reportlab.

Set up a virtual environment to avoid system conflicts:

bash

python3 -m venv /tmp/sc-env
/tmp/sc-env/bin/pip install pyjwt cryptography requests matplotlib pandas reportlab

IMPORTANT: Always use /tmp/sc-env/bin/python to run scripts, not the system Python.

Timeout warning: Package installation and first matplotlib import can be slow (60-120s). Set bash timeout to 180000ms for these operations.

3. Chinese Font for PDF (macOS)

The PDF uses STHeiti for proper CJK + Latin + symbol rendering. Register it like this:

python

from reportlab.pdfbase import pdfmetrics
from reportlab.pdfbase.ttfonts import TTFont

pdfmetrics.registerFont(TTFont('CNFont', '/System/Library/Fonts/STHeiti Medium.ttc', subfontIndex=0))
pdfmetrics.registerFont(TTFont('CNFontLight', '/System/Library/Fonts/STHeiti Light.ttc', subfontIndex=0))
pdfmetrics.registerFontFamily('CNFont', normal='CNFontLight', bold='CNFont')

CRITICAL font rules:

• Do NOT use UnicodeCIDFont('STSong-Light') — it causes English letter spacing to be too narrow and Unicode symbols like • (U+2022) to render as garbage characters (e.g. "煉").
• Always use TrueType fonts registered via TTFont for proper mixed CJK/Latin rendering.
• On non-macOS systems, find an available CJK TTF font: fc-list :lang=zh file or look for Noto Sans CJK / WenQuanYi.

4. Matplotlib Font for Charts (macOS)

CRITICAL: STHeiti alone does NOT cover Korean glyphs — keywords or country names in Korean will show as hollow rectangles (□). Use Arial Unicode MS instead, which covers CJK + Korean + most other scripts:

python

import matplotlib.font_manager as fm

matplotlib.rcParams['font.family'] = 'sans-serif'
matplotlib.rcParams['axes.unicode_minus'] = False

_unicode_font_path = '/Library/Fonts/Arial Unicode.ttf'
if not os.path.exists(_unicode_font_path):
    _unicode_font_path = '/System/Library/Fonts/STHeiti Medium.ttc'  # fallback
fm.fontManager.addfont(_unicode_font_path)
_ufname = fm.FontProperties(fname=_unicode_font_path).get_name()
matplotlib.rcParams['font.sans-serif'] = [_ufname, 'DejaVu Sans']

Do NOT try to combine STHeiti + AppleSDGothicNeo via font.sans-serif list — matplotlib uses a single font per render pass and does not do per-glyph fallback, so the list only helps if glyphs exist in the first font.

Step-by-Step Instructions

Step 1: Gather Input from User

Ask for or determine:

• Key file path: Path to the Service Account JSON key file
• Site URLs: One or more Search Console property URLs (format: https://www.example.com/)
• Date range: Default to last 90 days. The user may request a custom range.
• Output path: Where to save the PDF and data files. Default to project directory.
• Language: Report can be in Chinese (default) or English — match the user's language.

Step 2: Authenticate with Google API

Use JWT-based Service Account authentication. Here is the exact authentication code:

python

import json, time, jwt, requests

def get_access_token(key_file):
    with open(key_file) as f:
        creds = json.load(f)
    
    now = int(time.time())
    payload = {
        "iss": creds["client_email"],
        "scope": "https://www.googleapis.com/auth/webmasters.readonly",
        "aud": creds["token_uri"],
        "iat": now,
        "exp": now + 3600,
    }
    signed_jwt = jwt.encode(payload, creds["private_key"], algorithm="RS256")
    
    resp = requests.post(creds["token_uri"], data={
        "grant_type": "urn:ietf:params:oauth:grant-type:jwt-bearer",
        "assertion": signed_jwt,
    })
    resp.raise_for_status()
    return resp.json()["access_token"]

Error handling: If authentication fails with 403, the API may not be enabled or the service account may not have Search Console access. Tell the user which to check.

Step 3: Fetch Data from Search Console API

Use the Search Analytics API endpoint for each site. The base query function:

python

import datetime

END_DATE = datetime.date.today() - datetime.timedelta(days=3)  # Data has ~3 day lag
START_DATE = END_DATE - datetime.timedelta(days=89)

def query_sc(token, site_url, dimensions, start=None, end=None, row_limit=100):
    """Query Search Console Search Analytics API.
    
    Args:
        token: OAuth2 access token
        site_url: Full property URL, e.g. "https://www.example.com/"
        dimensions: List of dimensions. Valid values:
            - "date"    — daily breakdown
            - "query"   — search keywords
            - "page"    — page URLs
            - "country" — ISO 3166-1 alpha-3 country codes (lowercase)
            - "device"  — "DESKTOP", "MOBILE", "TABLET"
            - "searchAppearance" — rich result types
            Can combine: ["query", "page"] for keyword-page matrix
        start: Start date (datetime.date). Defaults to START_DATE.
        end: End date (datetime.date). Defaults to END_DATE.
        row_limit: Max rows (max 25000).
    
    Returns:
        List of row dicts with keys: "keys" (list), "clicks", "impressions", "ctr", "position"
        Note: "ctr" is a decimal (0.05 = 5%), multiply by 100 for display.
    """
    url = f"https://www.googleapis.com/webmasters/v3/sites/{requests.utils.quote(site_url, safe='')}/searchAnalytics/query"
    body = {
        "startDate": (start or START_DATE).isoformat(),
        "endDate": (end or END_DATE).isoformat(),
        "dimensions": dimensions,
        "rowLimit": row_limit,
    }
    resp = requests.post(url, headers={"Authorization": f"Bearer {token}"}, json=body)
    resp.raise_for_status()
    return resp.json().get("rows", [])

For each site, fetch ALL of the following data (this is the complete list — do not skip any):

#	Query	Dimensions	row_limit	Purpose
1	Daily traffic trend	["date"]	25000	Time series for charts
2	Top pages	["page"]	50	Most visited pages
3	Top queries	["query"]	50	Most searched keywords
4	Country distribution	["country"]	30	Geographic breakdown
5	Device distribution	["device"]	10	Desktop/Mobile/Tablet split
6	Search appearance	["searchAppearance"]	20	Rich result types
7	Query-page combos	["query", "page"]	100	Which keywords drive which pages
8	Period comparison (first half)	["page"] with first-half dates	500	Growth analysis
9	Period comparison (second half)	["page"] with second-half dates	500	Growth analysis

Period comparison logic: Split the date range in half. For each page URL, compare clicks between the two halves. Categorize pages as:

• Growing: clicks increased (sort by change descending)
• Declining: clicks decreased (sort by change ascending)
• New: appeared only in the second half
• Lost: appeared only in the first half

Step 4: Calculate Summary Statistics

For each site, compute:

python

daily = site_data["daily_trend"]
total_clicks = sum(d["clicks"] for d in daily)
total_impressions = sum(d["impressions"] for d in daily)
avg_ctr = sum(d["ctr"] for d in daily) / len(daily)  # Already *100 if you stored it that way
avg_position = sum(d["position"] for d in daily) / len(daily)

# Trend: compare last 30 days vs first 30 days
if len(daily) >= 60:
    first_30_clicks = sum(d["clicks"] for d in daily[:30])
    last_30_clicks = sum(d["clicks"] for d in daily[-30:])
    click_trend_pct = ((last_30_clicks - first_30_clicks) / max(first_30_clicks, 1)) * 100
    # Same for impressions

Step 5: Save Raw Data as JSON

Save all fetched data to sc_detailed_data.json for reproducibility:

python

with open(f"{output_dir}/sc_detailed_data.json", "w") as f:
    json.dump(all_data, f, ensure_ascii=False, indent=2)

Step 6: Generate Charts with Matplotlib

IMPORTANT: Always set matplotlib.use('Agg') BEFORE importing pyplot (no display server available).

Generate these charts (save as PNG, dpi=150):

Chart 1: Combined Traffic Trend (all sites)

• 2-row subplot: top = daily clicks, bottom = daily impressions
• One line per site, color-coded
• X-axis: dates formatted as %m-%d, rotated 45 degrees
• Legend in upper-left

Chart 2: Per-site Detail (one per site with enough data)

• 2-row subplot: top = daily clicks with 7-day moving average, bottom = average position (inverted Y-axis — lower is better)
• Fill area under clicks line with alpha=0.3

python

# 7-day moving average calculation
if len(clicks) >= 7:
    ma7 = [sum(clicks[max(0,i-6):i+1]) / min(7, i+1) for i in range(len(clicks))]

Chart 3: Device Distribution

• 1-row, N-column pie charts (one per site)
• Show percentage labels

Chart 4: Country Distribution (pie chart per site)

• Use figsize=(10, 7) and radius=0.9 — smaller sizes make labels illegible
• Place on its own full-width row in the PDF (do NOT put side-by-side with keyword chart)
• In PDF, render at width=16*cm
• Top 8 countries by clicks
• Map country codes to readable names using this mapping:

python

COUNTRY_NAMES = {
    'idn': 'Indonesia', 'hkg': 'Hong Kong', 'mac': 'Macau', 'kor': 'South Korea',
    'usa': 'United States', 'jpn': 'Japan', 'sgp': 'Singapore', 'mys': 'Malaysia',
    'twn': 'Taiwan', 'tha': 'Thailand', 'phl': 'Philippines', 'ind': 'India',
    'vnm': 'Vietnam', 'gbr': 'United Kingdom', 'deu': 'Germany', 'fra': 'France',
    'aus': 'Australia', 'can': 'Canada', 'bra': 'Brazil', 'mex': 'Mexico',
    'chn': 'China', 'pak': 'Pakistan', 'bgd': 'Bangladesh', 'lka': 'Sri Lanka',
    'mmr': 'Myanmar', 'khm': 'Cambodia', 'npl': 'Nepal', 'are': 'UAE',
    'sau': 'Saudi Arabia', 'tur': 'Turkey', 'egy': 'Egypt', 'nga': 'Nigeria',
    'ken': 'Kenya', 'zaf': 'South Africa', 'col': 'Colombia', 'arg': 'Argentina',
    'per': 'Peru', 'chl': 'Chile', 'nzl': 'New Zealand', 'ita': 'Italy',
    'esp': 'Spain', 'nld': 'Netherlands', 'rus': 'Russia', 'pol': 'Poland',
}

Step 7: Generate PDF Report

Use reportlab with A4 page size. The report has 7 sections:

PDF Structure

text

Cover Page
  - Report title (in user's language)
  - Subtitle: "Google Search Console Data Analysis & Recommendations"
  - Report date, data range, data source, covered sites

Section 1: Executive Summary
  - Summary table (all sites: clicks, impressions, avg CTR, avg position, trends)
  - Key findings (5-6 bullet points highlighting most important insights)

Section 2: Traffic Trends
  - Combined traffic trend chart (all sites)
  - Per-site detail charts (clicks + position)

Section 3: Top Pages (TOP 10)
  - Table per site: rank, page path, clicks, impressions, CTR, position
  - Shorten long URLs: if > 45 chars, truncate with "..."

Section 4: Top Keywords (TOP 15)
  - Table per site: rank, keyword, clicks, impressions, CTR, position

Section 5: Country & Device Distribution
  - Device pie charts
  - Country bar charts (for major sites)
  - Country tables (all sites, top 10)

Section 6: Growth Analysis
  - Per site: growing pages table (green header), declining pages table (red header)
  - New page count, lost page count

Section 7: Recommendations & Action Plan
  - AI-generated recommendations based on the data (see analysis guidelines below)
  - Priority action table (P0/P1/P2)

PDF Style Configuration

Use 1.2cm margins (not the default 2cm) to maximize content area. Usable width = 21cm - 2×1.2cm = 18.6cm. Set all chart/table widths to 18.6cm accordingly.

python

from reportlab.lib.pagesizes import A4
from reportlab.lib.units import mm, cm
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib.enums import TA_CENTER
from reportlab.platypus import (SimpleDocTemplate, Paragraph, Spacer, Table,
                                 TableStyle, Image, PageBreak, HRFlowable)
from reportlab.lib import colors as rl_colors

doc = SimpleDocTemplate(pdf_path, pagesize=A4,
                        leftMargin=1.2*cm, rightMargin=1.2*cm,
                        topMargin=1.2*cm, bottomMargin=1.2*cm)

# Define styles — use 'CNFont' (the registered STHeiti font)
styles = getSampleStyleSheet()
styles.add(ParagraphStyle(name='CNTitle', fontName='CNFont', fontSize=22,
                          alignment=TA_CENTER, spaceAfter=6*mm, leading=28))
styles.add(ParagraphStyle(name='CNSubtitle', fontName='CNFont', fontSize=12,
                          alignment=TA_CENTER, textColor=rl_colors.grey, spaceAfter=10*mm))
styles.add(ParagraphStyle(name='CNHeading1', fontName='CNFont', fontSize=16,
                          spaceAfter=4*mm, spaceBefore=8*mm, leading=22,
                          textColor=rl_colors.HexColor('#1a73e8')))
styles.add(ParagraphStyle(name='CNHeading2', fontName='CNFont', fontSize=13,
                          spaceAfter=3*mm, spaceBefore=5*mm, leading=18,
                          textColor=rl_colors.HexColor('#333333')))
styles.add(ParagraphStyle(name='CNBody', fontName='CNFont', fontSize=10,
                          spaceAfter=2*mm, leading=16))
styles.add(ParagraphStyle(name='CNSmall', fontName='CNFont', fontSize=8,
                          textColor=rl_colors.grey, leading=12))
styles.add(ParagraphStyle(name='CNBullet', fontName='CNFont', fontSize=10,
                          spaceAfter=1.5*mm, leading=16, leftIndent=10*mm,
                          bulletIndent=5*mm))

Table Style Template

Use this consistent style for all data tables:

python

table_style = TableStyle([
    ('FONTNAME', (0,0), (-1,-1), 'CNFont'),
    ('FONTSIZE', (0,0), (-1,-1), 7),        # Small font for dense data
    ('BACKGROUND', (0,0), (-1,0), rl_colors.HexColor('#1a73e8')),  # Blue header
    ('TEXTCOLOR', (0,0), (-1,0), rl_colors.white),
    ('ALIGN', (2,0), (-1,-1), 'RIGHT'),     # Numbers right-aligned
    ('ALIGN', (0,0), (0,-1), 'CENTER'),     # Rank column centered
    ('GRID', (0,0), (-1,-1), 0.5, rl_colors.HexColor('#dddddd')),
    ('ROWBACKGROUNDS', (0,1), (-1,-1), [rl_colors.white, rl_colors.HexColor('#f8f9fa')]),
    ('TOPPADDING', (0,0), (-1,-1), 2),
    ('BOTTOMPADDING', (0,0), (-1,-1), 2),
])

Bullet Points

Use Unicode bullet character \u2022 (•) for list items:

python

story.append(Paragraph(f"\u2022 {text}", styles['CNBullet']))

This renders correctly with STHeiti font. Do NOT use other bullet approaches.

Step 8: Generate SEO Recommendations

Analyze the data and generate recommendations following these guidelines:

Analysis Framework

1. CTR Analysis: If average CTR < 5%, recommend Title/Description optimization.
2. Position Opportunities: Find keywords ranking 5-15 (page 1-2 boundary) — these are low-hanging fruit for optimization.
3. Country Focus: Identify the #1 traffic country and recommend localized content.
4. Growth Momentum: Sites with click growth > 100% are in "breakout" phase — recommend increasing content investment.
5. New Sites: Sites with < 30 days of data need basic SEO foundations (Sitemap submission, internal linking).
6. Declining Pages: If many pages have declining clicks, recommend content quality audit.
7. Device Split: If mobile > 60%, emphasize mobile optimization and Core Web Vitals.
8. Technical SEO: Always recommend hreflang for multi-region sites, 404 fixes, and page speed optimization.

Priority Classification

• P0 (Do immediately): CTR optimization, fixing unindexed pages
• P1 (Do this month): Content localization, keyword optimization for positions 5-15
• P2 (Plan for next quarter): hreflang implementation, Core Web Vitals, Sitemap tuning

Step 9: Present Results

After generating the PDF:

1. Confirm the PDF file path to the user
2. Provide a summary in chat covering:
   • Report structure (sections and page count)
   • Key highlights per site (1-2 sentences each)
   • Top 3 priority recommendations
3. Mention the raw data JSON file path for further analysis
4. Offer next steps (e.g., "Do you want me to analyze a specific page or keyword in more detail?")

Common Errors and Solutions

Error	Cause	Solution
403 Forbidden on API call	Service account not added to Search Console	Add the service account email as a user in Search Console settings
403 Google Search Console API has not been enabled	API not enabled	Enable it at https://console.cloud.google.com/apis/library/searchconsole.googleapis.com
Empty rows in response	No data for that site/date range	Check if the site URL exactly matches the Search Console property (trailing slash matters!)
jwt.encode error	Missing cryptography package	pip install cryptography
PDF shows garbled Chinese	Wrong font	Use TTFont with STHeiti, NOT UnicodeCIDFont with STSong-Light
Matplotlib timeout on first run	Building font cache	Set bash timeout to 180000ms; this only happens once
MPLCONFIGDIR warning	No write access to ~/.matplotlib	Harmless; matplotlib creates a temp cache automatically

Example Usage

User: "Help me generate an SEO report for my websites using Search Console" → Ask for key file path and site URLs, then run the full pipeline.

User: "Analyze search performance for example.com over the last 90 days and export to PDF" → Run with default 90-day range, generate full report.

User: "Compare search traffic between my 3 sites" → Run for all 3 sites, emphasize the comparison aspects in the summary table and trends chart.

Layout Rules (Lessons Learned)

Top Pages Table — Use Paragraph for Word Wrap

CRITICAL: The Page column contains long URLs that WILL overflow into adjacent columns if you use plain strings. Always wrap ALL table cells in Paragraph() objects:

python

style_cell = ParagraphStyle('Cell', fontName='CNFontLight', fontSize=9, leading=13, wordWrap='CJK')
style_cell_hd = ParagraphStyle('CellHd', fontName='CNFont', fontSize=9, leading=13, textColor=colors.white)

page_rows = [[Paragraph("Page", style_cell_hd), Paragraph("Clicks", style_cell_hd), ...]]
for r in rows:
    page_rows.append([Paragraph(url, style_cell), Paragraph(f"{clicks:,}", style_cell), ...])

Country Chart — Keep on Its Own Row

Do NOT put the country pie chart side-by-side with the keyword chart. The pie becomes too small to read. Always put it on a separate row at width=16*cm.

File Naming — Always Include Date

Output filename must include today's date to avoid overwriting previous reports:

python

OUTPUT = f"search_console_report_{datetime.date.today()}.pdf"

Per-Site Layout Order (One Page Per Site)

1. KPI metrics row (Clicks / Impressions / CTR / Avg. Position)
2. Top Keywords chart (full width, 18.6cm)
3. Country pie chart (own row, 16cm)
4. Top Pages table (with Paragraph cells)
5. PageBreak()

Reference Script

See gen_report.py in this skill directory for the complete, production-ready implementation with all fixes applied.

Output Files

File	Description
sc_detailed_data.json	Raw API data for all sites (reproducible)
report_charts/*.png	Generated chart images (temp, /tmp/)
project/reports/search_console_report_YYYY-MM-DD.pdf	Final PDF report (date-stamped, never overwritten)