How to Build a Weekly SEO Report as a Claude Code Skill

import os, requests, json, sqlite3
from datetime import datetime, timedelta

API_KEY = os.environ['SCAVIO_API_KEY']
SH = {'x-api-key': API_KEY, 'Content-Type': 'application/json'}

KEYWORDS = ['serp api', 'search api python', 'web scraping api',
            'ai agent search tool', 'mcp search server']
MY_DOMAIN = 'scavio.dev'

db = sqlite3.connect('weekly_report.db')
db.execute('''CREATE TABLE IF NOT EXISTS weekly (
    keyword TEXT, position INTEGER, has_ao INTEGER,
    brand_cited INTEGER, checked_at TEXT
)''')
db.commit()

def check_keyword(keyword):
    data = requests.post('https://api.scavio.dev/api/v1/search',
        headers=SH, json={'query': keyword, 'country_code': 'us',
                          'include_ai_overview': True}).json()
    organic = data.get('organic_results', [])
    pos = next((r['position'] for r in organic if MY_DOMAIN in r.get('link', '')), None)
    has_ao = bool(data.get('ai_overview'))
    ao_text = json.dumps(data.get('ai_overview', {})).lower()
    cited = MY_DOMAIN in ao_text if has_ao else False
    db.execute('INSERT INTO weekly VALUES (?,?,?,?,?)',
        (keyword, pos or 0, int(has_ao), int(cited), datetime.now().isoformat()))
    db.commit()
    return {'keyword': keyword, 'position': pos, 'has_ao': has_ao, 'cited': cited}

print('Weekly report data model ready.')

def get_previous_week(keyword):
    week_ago = (datetime.now() - timedelta(days=7)).isoformat()
    row = db.execute(
        'SELECT position, has_ao, brand_cited FROM weekly WHERE keyword=? AND checked_at < ? ORDER BY checked_at DESC LIMIT 1',
        (keyword, week_ago)).fetchone()
    return {'position': row[0], 'has_ao': bool(row[1]), 'cited': bool(row[2])} if row else None

def weekly_report():
    results = []
    for kw in KEYWORDS:
        current = check_keyword(kw)
        previous = get_previous_week(kw)
        change = None
        if previous and current['position'] and previous['position']:
            change = previous['position'] - current['position']
        results.append({**current, 'change': change, 'previous': previous})
    return results

def format_report(results):
    now = datetime.now().strftime('%Y-%m-%d')
    lines = []
    lines.append(f'Weekly SEO Report - {now}')
    lines.append(f'Domain: {MY_DOMAIN}')
    lines.append(f'Keywords: {len(results)}')
    lines.append('')
    lines.append(f'{"Keyword":30} | {"Pos":>4} | {"Chg":>4} | {"AO":>3} | {"Cited":>5}')
    lines.append('-' * 60)
    for r in results:
        pos = f'#{r["position"]}' if r['position'] else '-'
        chg = f'{r["change"]:+d}' if r['change'] is not None else 'NEW'
        ao = 'Y' if r['has_ao'] else 'N'
        cited = 'Y' if r['cited'] else 'N'
        lines.append(f'{r["keyword"]:30} | {pos:>4} | {chg:>4} | {ao:>3} | {cited:>5}')
    return '\n'.join(lines)

results = weekly_report()
print(format_report(results))

def summary_metrics(results):
    ranked = [r for r in results if r['position']]
    improved = [r for r in results if r['change'] and r['change'] > 0]
    declined = [r for r in results if r['change'] and r['change'] < 0]
    ao_count = sum(1 for r in results if r['has_ao'])
    cited_count = sum(1 for r in results if r['cited'])
    avg_pos = sum(r['position'] for r in ranked) / len(ranked) if ranked else 0
    cost = len(results) * 0.005
    lines = []
    lines.append(f'\nSummary:')
    lines.append(f'  Ranked keywords: {len(ranked)}/{len(results)}')
    lines.append(f'  Average position: {avg_pos:.1f}')
    lines.append(f'  Improved: {len(improved)} | Declined: {len(declined)}')
    lines.append(f'  AI Overview coverage: {ao_count}/{len(results)} ({ao_count/len(results)*100:.0f}%)')
    lines.append(f'  Brand cited in AO: {cited_count}/{len(results)} ({cited_count/len(results)*100:.0f}%)')
    lines.append(f'  Report cost: ${cost:.3f}')
    return '\n'.join(lines)

print(summary_metrics(results))

def run_weekly_report():
    """Main entry point for /weekly-report skill."""
    print('Generating weekly SEO report...')
    print(f'Checking {len(KEYWORDS)} keywords for {MY_DOMAIN}\n')
    results = weekly_report()
    report = format_report(results)
    summary = summary_metrics(results)
    output = f'{report}{summary}'
    print(output)
    # Save to file for reference
    filename = f'report_{datetime.now().strftime("%Y%m%d")}.txt'
    with open(filename, 'w') as f:
        f.write(output)
    print(f'\nSaved to {filename}')
    return output

# Run: python weekly_report.py
# Or as Claude Code skill: /weekly-report
run_weekly_report()