Imported from LeoYeAI/openclaw-master-skills (skills/search-console-report/SKILL.md). Install upstream with npx skills add LeoYeAI/openclaw-master-skills --skill search-console-report. Copyright stays with the author.

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:

{
  "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:

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:

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.

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:

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:

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):

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:

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:

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

# 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 (horizontal bar, for sites with significant traffic)

• Top 10 countries by clicks
• Map country codes to readable names using this mapping:

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

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

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=2*cm, rightMargin=2*cm,
                        topMargin=2*cm, bottomMargin=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:

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:

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

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.

Output Files