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": "xxx@project.iam.gserviceaccount.com",
  "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):

#QueryDimensionsrow_limitPurpose1Daily traffic trend["date"]25000Time series for charts2Top pages["page"]50Most visited pages3Top queries["query"]50Most searched keywords4Country distribution["country"]30Geographic breakdown5Device distribution["device"]10Desktop/Mobile/Tablet split6Search appearance["searchAppearance"]20Rich result types7Query-page combos["query", "page"]100Which keywords drive which pages8Period comparison (first half)["page"] with first-half dates500Growth analysis9Period comparison (second half)["page"] with second-half dates500Growth 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#

ErrorCauseSolution403 Forbidden on API callService account not added to Search ConsoleAdd the service account email as a user in Search Console settings403 Google Search Console API has not been enabledAPI not enabledEnable it at https://console.cloud.google.com/apis/library/searchconsole.googleapis.comEmpty rows in responseNo data for that site/date rangeCheck if the site URL exactly matches the Search Console property (trailing slash matters!)jwt.encode errorMissing cryptography packagepip install cryptographyPDF shows garbled ChineseWrong fontUse TTFont with STHeiti, NOT UnicodeCIDFont with STSong-LightMatplotlib timeout on first runBuilding font cacheSet bash timeout to 180000ms; this only happens onceMPLCONFIGDIR warningNo write access to ~/.matplotlibHarmless; 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#

FileDescriptionsc_detailed_data.jsonRaw API data for all sites (reproducible)report_charts/*.pngGenerated chart images (temp, /tmp/)project/reports/search_console_report_YYYY-MM-DD.pdfFinal PDF report (date-stamped, never overwritten)